"There are 10 types of people in the world -- those who understand binary, and those who don't."The term "binary" in MySQL has many different meanings. How many can you come up with? I have 6, but I am willing to believe there are more!0) "Binary distribution" is the name for the package that contains a binary. Another use is "binary installation" but that's pretty much the same usage pattern as "binary distribution", so I won't count "binary installation" as

Last week I had to confront one of those situations where you can't really tell what is going on with a piece of software, and the final conclusion would sound completely crazy if postulated as the initial hypothesis. The regular MySQL commands and utilities fall short in these cases, so I had to resort to the three tools reviewed in this article.The problem we were diagnosing was this: at some point in time, a number of queries that use to take less than one minute to execute, started to take between five to 15 minutes. We needed to get an insight into what was going on inside the MySQL server.MySQL TunerAt some point in a long diagnosis process, MySQL's "SHOW [GLOBAL] VARIABLES" and "SHOW [GLOBAL] STATUS" are nothing more than a long list of numbers. Going through a team mate's notes on another issue, I came across MySQL Tuner. This is an extremely simple tool that takes the information from the  []

A quiz, in 4 parts:Given the following table definition in the sakila database:

Today I've been checking out a new client environment. My mission is to figure out (cold) some of the characteristics of the queries being run, and particularly if they're "good" or "bad". In my arsenal of "tools I really want to check out" has been Ma'atkit's Query Profiler.They're very different tools. Ma'atkit's query profiler profiles a batch of queries, without granularity (at least not the way I ran it) to see what query is doing what. So I ran this against a production machine:(I ran the query profiler for a while and then hit "enter" (apparently after about 17 minutes))> ./mk-query-profiler --external --host localhost --user <user> --password <password> Press <enter> when the external program is finished +----------------------------------------------------------+ | 1 (1028.2091 sec) |  []

I had so much to say in response to a recent post asking about virtualization from Jennifer Glore that I realized it was long enough to be a blog post.It really depends on what you're looking to do. Many companies don't have the money and staff to have an in-house data center with proper power and network redundancy; others don't want the depreciati

I was doing some research over the weekend on how transactions work "under the hood" in MySQL. I thought it might be enlightening if I wrote about what I found.The database system must resolve the situation where, for whatever reason, the database server stops working in the middle of a transaction.  Perhaps the power goes out, the hard drive crashes, or the CPU disappears in a cloud of smoke. Maybe the MySQL Server itself crashes for some reason. What does the MySQL Server do when operation resumes? Potentially, there are transactions in an inconsistent state. How are inconsistent transactions resolved? They are resolved through a recovery process involving log files that are called transactional logs. There are two kinds of transactional logs: redo logs and undo logs.

The maximum length of a "VARCHAR" is only restricted by the maximum row length. In most storage engines, the maximum row length is the maximum allowed by MySQL, which is 65,535 bytes. Only the NDB storage engine has a different maximum value.The "VARCHAR" data type has some overhead. If the length of "VARCHAR" is less than 255, one byte per row is used to store the actual length of the string. If the length of "VARCHAR" is greater than 255, the

Hello everyone,Reading PlanetMySQL today, I discovered that Alex Gorbachev's announcement that he has released the first public beta of his Oracle Grid Control plugin for MySQL was not aggregated! This is probably because Alex is primarily working on our Oracle space and so his feed isn't on planet.This p

At the 2008 MySQL Conference and Expo, The Pythian Group gave away "EXPLAIN" cheatsheets. They were very nice, printed in full color and laminated to ensure you can spill your coffee* on it and it will survive.For those not at the conference, or those that want to make more, the file is downloadable as a 136Kb PDF at explain-diagram.pdf* or tea, for

Hi all,I was asked this question in an e-mail -- feel free to ask your questions in the comments, as I will point the original author to this post to answer those questions. There is not a lot of data here, so instead of me asking questions in an e-mail I figured I would open it up to the (MySQL) world. Without further ado, here's the question:Basically when we open more than one connection- we're opening 5, and we do processing in mysql (innodb tables) the server cpu and m

I was asked this question recently, and I thought it was a great little tidbit of knowledge to pass along. The short answer is "no". The slightly longer answer was written up by Jan Kneschke when dealing with a forum post about proxy + connection pooling.From http://forums.mysql.com/read.php?146,169265,169700The clear-text password is _never_ transfered in the authentication phase.On the network we have:

The answers to the last pop quiz are up: http://www.pythian.com/blogs/868/pop-quiz-mysql-clusterSo here's another pop quiz. Given the following:Welcome to the MySQL monitor. Commands end with ; or \\g.Your MySQL connection id is 16450949 to server version: 4.1.14-standard-logType 'help;' or '\\h' for help. Type '\\c' to clear the buffer.mysql> select count(*),length(password) from mysql.user group by l

While doing a standard audit for a new client, I recommended a few changes to get better performance. Because I had several changes, I used the documentation at http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.htmland found that "innodb_flush_log_at_trx_commit" is a dynamic variable. I was surprised, because most operations dealing with file sizes and operations are not dynamic.

Recently while we were building a slave with a newer version of MySQL 5.1 from an InnoDB Hot backup, the following error occurred when we ran "mysql_upgrade":mysql.time_zone OKmysql.time_zone_leap_second OKmysql.time_zone_name OKmysql.time_zone_transition OKmysql.time_zone_transition_type OKmysql.user OKRunning 'mysql_fix_privilege_tables'...ERROR 13 (HY000) at line 311: Can't get stat of './mysql/general_log.CSV' (Errcode: 2)ERROR 13 (HY000) at line 316: Can't get stat of './mysql/slow_log.CSV' (Errcode: 2)FATAL ERROR: Upgrade failedThe problem is that in MySQL 5.1, it is possible to log the slow query log and general log to tables in the "mysql" schema (source: Selecting General Query and Slow Query Log Output Destinations). These tables are  []

MySQL Full Text Search LimitationsSuppose you have a MyISAM table containing a column with a full text index. This table starts to grow to a significant size (millions of rows) and gets updated fairly frequently. Chances are that you'll start to see some bottlenecks when accessing this table, since without row level locking, the reading and writing operations will be blocking each other.A solution that many people would suggest right away is to use the master for writes and a slave for reads, but this only masks the problem, and it won't take long before enough read traffic on the slave starts causing slave lags.Why Sphinx?

A broken VIEW was caused by the view's definer being non-existent. In this particular system, backups are done by replicating all the machines (production, development, test, etc) to one server and doing cold physical backups off that server, which currently has 12 instances running.So in order to find on what machine the user might still be defined, I went to the backup server. All the data directories are in one path, ie:instance 1 has a datadir of /data/mysql/instance1instance 2 has a datadir of /data/mysql/instance2Now, the unix tool "strings" can be used against many types of files. In particular, though, you can use "strings" on the mysql/user.MYD file to see the username, host, and password hash.

I was writing up some notes about a client's status variables, and I'd had to remind myself of Handler_read_first. Handler_read_first is a counter that increments when the first entry in the index is read, which may indicate that a full index scan is being used. With this particular client, I ran a SHOW STATUS, waited 100 seconds, and ran it again. Handler_read_first jumped by about 53. So I wrote down in my notes:first in the index...every other second. Lots of table scans?As s

I just filed a very annoying bug when trying to compile with plugin engines using the 5.1.xx source tarball.DescriptionI am trying to test SphinxSE as a plugin instead of getting it statically linked and came across an annoying bug. When using the configure "--with-plugins" option only once, the engine is statically linked. When using it twice, the first engine is created as a plugin, and the 2nd one is linked statically. Here are a couple of examples:

This tip is to overcome a type of expensive query that I see all the time. In doing this, I uncovered a nasty bug in MySQL v5.1.ScenarioI've seen queries like these several times:SELECT COUNT(*) FROM sample WHERE status = n;Normally, this is OK, but if you have hundreds of millions of rows, only a handful of possible statuses and execute this query dozens of times per minute on an active InnoDB table, it quickly becomes a bottleneck.One SuggestionEstablis

Today's trivial MySQL system variable:old_alter_tableThe interesting bit is that this is a system variable, and shows up in SHOW GLOBAL VARIABLES, but is not documented on the Server System Variables manual page.Instead, it is documented on the manual page for Server options.Unfortunately, that documentation is v