Go to the first, previous, next, last section, table of contents.

10 Getting maximum performance from MySQL

10.1 Tuning server parameters

You can get the default buffer sizes used by the mysqld server with this command:

shell> mysqld --help

This command produces a list of all mysqld options and configurable variables. The output includes the default values and looks something like this:

Possible variables for option --set-variable (-O) are:
back_log              current value: 5
connect_timeout       current value: 5
delayed_insert_timeout  current value: 300
delayed_insert_limit  current value: 100
delayed_queue_size    current value: 1000
flush_time            current value: 0
join_buffer_size      current value: 131072
key_buffer_size       current value: 1048540
long_query_time       current value: 10
max_allowed_packet    current value: 1048576
max_connections       current value: 100
max_connect_errors    current value: 10
max_delayed_threads   current value: 20
max_heap_table_size   current value: 16777216
max_join_size         current value: 4294967295
max_sort_length       current value: 1024
max_tmp_tables        current value: 32
net_buffer_length     current value: 16384
record_buffer         current value: 131072
sort_buffer           current value: 2097116
table_cache           current value: 64
tmp_table_size        current value: 1048576
thread_stack          current value: 131072
wait_timeout          current value: 28800

If there is a mysqld server currently running, you can see what values it actually is using for the variables by executing this command:

shell> mysqladmin variables

Each option is described below. Values for buffer sizes, lengths and stack sizes are given in bytes. You can specify values with a suffix of `K' or `M' to indicate kilobytes or megabytes. For example, 16M indicates 16 megabytes. Case of suffix letters does not matter; 16M and 16m are equivalent.

You can also see some statistics from a running server by the command SHOW STATUS. See section 7.20 SHOW syntax (Get information about tables, columns,...).

The number of outstanding connection requests MySQL can have. This comes into play when the main MySQL thread gets VERY many connection requests in a very short time. It then takes some time (although very little) for the main thread to check the connection and start a new thread. The back_log value indicates how many requests can be stacked during this short time before MySQL momentarily stops answering new requests. You need to increase this only if you expect a large number of connections in a short period of time. In other words, this value is the size of the listen queue for incoming TCP/IP connections. Your operating system has its own limit on the size of this queue. The manual page for the Unix listen(2) system call should have more details. Check your OS documentation for the maximum value for this variable. Attempting to set back_log higher than this maximum will be ineffective.
The number of seconds the mysqld server is waiting for a connect packet before responding with Bad handshake.
How long a INSERT DELAYED thread should wait for INSERT statements before terminating.
After inserting delayed_insert_limit rows, the INSERT DELAYED handler will check if there are any SELECT statements pending. If so, it allows these to execute before continuing.
How big a queue (in rows) should be allocated for handling INSERT DELAYED. If the queue becomes full, any client that does INSERT DELAYED will wait until there is room in the queue again.
If this is set to a non-zero value, then every flush_time seconds all tables will be closed (to free up resources and sync things to disk).
The size of the buffer that is used for full joins (joins that do not use indexes). The buffer is allocated one time for each full join between two tables. Increase this value to get a faster full join when adding indexes is not possible. (Normally the best way to get fast joins is to add indexes.)
Index blocks are buffered and are shared by all threads. key_buffer_size is the size of the buffer used for index blocks. You might want to increase this value when doing many DELETE or INSERT operations on a table with lots of indexes. To get even more speed, use LOCK TABLES. See section 7.23 LOCK TABLES/UNLOCK TABLES syntax.
If a query takes longer than this (in seconds), the Slow_queries counter will be incremented.
The maximum size of one packet. The message buffer is initialized to net_buffer_length bytes, but can grow up to max_allowed_packet bytes when needed. This value by default is small to catch big (possibly wrong) packets. You must increase this value if you are using big BLOB columns. It should be as big as the biggest BLOB you want to use.
The number of simultaneous clients allowed. Increasing this value increases the number of file descriptors that mysqld requires. See below for comments on file descriptor limits.
If there is more than this number of interrupted connections from a host this host will be blocked for further connections. You can unblock a host with the command FLUSH HOSTS.
Don't start more than this number of threads to handle INSERT DELAYED statements. If you try to insert data in a new table after all INSERT DELAYED threads are in use, the row will be inserted as if the DELAYED attribute wasn't specified.
Joins that are probably going to read more than max_join_size records return an error. Set this value if your users tend to perform joins without a WHERE clause that take a long time and return millions of rows.
The number of bytes to use when sorting BLOB or TEXT values (only the first max_sort_length bytes of each value are used; the rest are ignored).
(This option doesn't yet do anything). Maximum number of temporary tables a client can keep open at the same time.
The communication buffer is reset to this size between queries. This should not normally be changed, but if you have very little memory, you can set it to the expected size of a query. (That is, the expected length of SQL statements sent by clients. If statements exceed this length, the buffer is automatically enlarged, up to max_allowed_packet bytes.)
Each thread that does a sequential scan allocates a buffer of this size for each table it scans. If you do many sequential scans, you may want to increase this value.
Each thread that needs to do a sort allocates a buffer of this size. Increase this value for faster ORDER BY or GROUP BY operations. See section 18.5 Where MySQL stores temporary files.
The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. MySQL needs two file descriptors for each unique open table. See below for comments on file descriptor limits. For information about how the table cache works, see section 10.8 How MySQL opens and closes tables.
If a temporary table exceeds this size, MySQL generates an error of the form The table tbl_name is full. Increase the value of tmp_table_size if you do many advanced GROUP BY queries.
The stack size for each thread. Many of the limits detected by the crash-me test are dependent on this value. The default is normally large enough. See section 11 The MySQL benchmark suite.
The number of seconds the server waits for activity on a connection before closing it.

table_cache, max_connections and max_tmp_tables affect the maximum number of files the server keeps open. If you increase one or both of these values, you may run up against a limit imposed by your operating system on the per-process number of open file descriptors. However, you can increase the limit on many systems. Consult your OS documentation to find out how to do this, because the method for changing the limit varies widely from system to system.

table_cache is related to max_connections. For example, for 200 open connections, you should have a table cache of at least 200 * n, where n is the maximum number of tables in a join.

MySQL uses algorithms that are very scalable, so you can usually run with very little memory or give MySQL more memory to get better performance.

If you have much memory and many tables and want maximum performance with a moderate number of clients, you should use something like this:

shell> safe_mysqld -O key_buffer=16M -O table_cache=128 \
           -O sort_buffer=4M -O record_buffer=1M &

If you have little memory and lots of connections, use something like this:

shell> safe_mysqld -O key_buffer=512k -O sort_buffer=100k \
           -O record_buffer=100k &

or even:

shell> safe_mysqld -O key_buffer=512k -O sort_buffer=16k \
           -O table_cache=32 -O record_buffer=8k -O net_buffer=1K &

If there are very many connections, ``swapping problems'' may occur unless mysqld has been configured to use very little memory for each connection. mysqld performs better if you have enough memory for all connections, of course.

Note that if you change an option to mysqld, it remains in effect only for that instance of the server.

To see the effects of a parameter change, do something like this:

shell> mysqld -O key_buffer=32m --help

Make sure that the --help option is last; otherwise, the effect of any options listed after it on the command line will not be reflected in the output.

10.2 How MySQL uses memory

The list below indicates some of the ways that the mysqld server uses memory. Where applicable, the name of the server variable relevant to the memory use is given.

ps and other system status programs may report that mysqld uses a lot of memory. This may be caused by thread-stacks on different memory addresses. For example, the Solaris version of ps counts the unused memory between stacks as used memory. You can verify this by checking available swap with swap -s. We have tested mysqld with commercial memory-leakage detectors, so there should be no memory leaks.

10.3 How compiling and linking affects the speed of MySQL

Most of the following tests are done on Linux and with the MySQL benchmarks, but they should give some indication for other operating systems.

You get the fastest executable when you link with -static. Using Unix sockets rather than TCP/IP to connect to a database also gives better performance.

On Linux, you will get the fastest code when compiling with pgcc and -O6. To compile `sql_yacc.cc' with these options, you need 180M memory because gcc/pgcc needs a lot of memory to make all functions inline. You should also set CXX=gcc when configuring MySQL to avoid inclusion of the libstdc++ library.

The MySQL-Linux distribution provided by TcX is compiled with pgcc and linked statically.

10.4 How MySQL uses indexes

All indexes (PRIMARY, UNIQUE and INDEX()) are stored in B-trees. Strings are automatically prefix- and end-space compressed. See section 7.26 CREATE INDEX syntax.

Indexes are used to:

Suppose you issue the following SELECT statement:

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly. If separate single-column indexes exist on col1 and col2, the optimizer tries to find the most restrictive index by deciding which index will find fewer rows and using that index to fetch the rows.

If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows. For example, if you have a three-column index on (col1,col2,col3), you have indexed search capabilities on (col1), (col1,col2) and (col1,col2,col3).

MySQL can't use a partial index if the columns don't form a leftmost prefix of the index. Suppose you have the SELECT statements shown below:

mysql> SELECT * FROM tbl_name WHERE col1=val1;
mysql> SELECT * FROM tbl_name WHERE col2=val2;
mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

If an index exists on (col1,col2,col3), only the first query shown above uses the index. The second and third queries do involve indexed columns, but (col2) and (col2,col3) are not leftmost prefixes of (col1,col2,col3).

MySQL also uses indexes for LIKE comparisons if the argument to LIKE is a constant string that doesn't start with a wildcard character. For example, the following SELECT statements use indexes:

mysql> select * from tbl_name where key_col LIKE "Patrick%";
mysql> select * from tbl_name where key_col LIKE "Pat%_ck%";

In the first statement, only rows with "Patrick" <= key_col < "Patricl" are considered. In the second statement, only rows with "Pat" <= key_col < "Pau" are considered.

The following SELECT statements will not use indexes:

mysql> select * from tbl_name where key_col LIKE "%Patrick%";
mysql> select * from tbl_name where key_col LIKE other_col;

In the first statement, the LIKE value begins with a wildcard character. In the second statement, the LIKE value is not a constant.

Searching using column_name IS NULL will use indexes if column_name is a index.

MySQL normally uses the index that finds least number of rows. An index is used for columns that you compare with the following operators: =, >, >=, <, <=, BETWEEN and a LIKE with a non-wildcard prefix like 'something%'.

Any index that doesn't span all AND levels in the WHERE clause is not used to optimize the query.

The following WHERE clauses use indexes:

... WHERE index_part1=1 AND index_part2=2
... WHERE index=1 OR A=10 AND index=2      /* index = 1 OR index = 2 */
... WHERE index_part1='hello' AND index_part_3=5
          /* optimized like "index_part1='hello'" */

These WHERE clauses do NOT use indexes:

... WHERE index_part2=1 AND index_part3=2  /* index_part_1 is not used */
... WHERE index=1 OR A=10                  /* No index */
... WHERE index_part1=1 OR index_part2=10  /* No index spans all rows */

10.5 How MySQL optimizes WHERE clauses

(This section is incomplete; MySQL does many optimizations.)

In general, when you want to make a slow SELECT ... WHERE faster, the first thing to check is whether or not you can add an index. All references between different tables should usually be done with indexes. You can use the EXPLAIN command to determine which indexes are used for a SELECT. See section 7.21 EXPLAIN syntax (Get information about a SELECT).

Some of the optimizations performed by MySQL are listed below:

Some examples of queries that are very fast:

mysql> SELECT COUNT(*) FROM tbl_name;
mysql> SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;
mysql> SELECT MAX(key_part2) FROM tbl_name
           WHERE key_part_1=constant;
mysql> SELECT ... FROM tbl_name
           ORDER BY key_part1,key_part2,... LIMIT 10;
mysql> SELECT ... FROM tbl_name
           ORDER BY key_part1 DESC,key_part2 DESC,... LIMIT 10;

The following queries are resolved using only the index tree (assuming the indexed columns are numeric):

mysql> SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;
mysql> SELECT COUNT(*) FROM tbl_name
           WHERE key_part1=val1 AND key_part2=val2;
mysql> SELECT key_part2 FROM tbl_name GROUP BY key_part1;

The following queries use indexing to retrieve the rows in sorted order without a separate sorting pass:

mysql> SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,...
mysql> SELECT ... FROM tbl_name ORDER BY key_part1 DESC,key_part2 DESC,...

10.6 How MySQL optimizes LEFT JOIN

A LEFT JOIN B is in MySQL implemented as follows

10.7 How MySQL optimizes LIMIT

In some cases MySQL will handle the query differently when you are using LIMIT # and not using HAVING:

10.8 How MySQL opens and closes tables

The cache of open tables can grow to a maximum of table_cache (default 64; this can be changed with with the -O table_cache=# option to mysqld). A table is never closed, except when the cache is full and another thread tries to open a table or if you use mysqladmin refresh or mysqladmin flush-tables.

When the table cache fills up, the server uses the following procedure to locate a cache entry to use:

A table is opened for each concurrent access. This means that if you have two threads accessing the same table or access the table twice in the same query (with AS) the table needs to be opened twice. The first open of any table takes two file descriptors; each additional use of the table takes only one file descriptor. The extra descriptor for the first open is used for the index file; this descriptor is shared among all threads.

10.8.1 Drawbacks of creating large numbers of tables in a database

If you have many files in a directory, open, close and create operations will be slow. If you execute SELECT statements on many different tables, there will be a little overhead when the table cache is full, because for every table that has to be opened, another must be closed. You can reduce this overhead by making the table cache larger.

10.9 Why so many open tables?

When you run mysqladmin status, you'll see something like this:

Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12

This can be somewhat perplexing if you only have 6 tables.

MySQL is multithreaded, so it may have many queries on the same table simultaneously. To minimize the problem with two threads having different states on the same file, the table is opened independently by each concurrent thread. This takes some memory and one extra file descriptor for the data file. The index file descriptor is shared between all threads.

10.10 Using symbolic links for databases and tables

You can move tables and databases from the database directory to other locations and replace them with symbolic links to the new locations. You might want to do this, for example, to move a database to a file system with more free space.

If MySQL notices that a table is a symbolically-linked, it will resolve the symlink and use the table it points to instead. This works on all systems that support the realpath() call (at least Linux and Solaris support realpath())! On systems that don't support realpath(), you should not access the table through the real path and through the symlink at the same time! If you do, the table will be inconsistent after any update.

MySQL doesn't support linking of databases by default. Things will work fine as long as you don't make a symbolic link between databases. Suppose you have a database db1 under the MySQL data directory, and then make a symlink db2 that points to db1:

shell> cd /path/to/datadir
shell> ln -s db1 db2

Now, for any table tbl_a in db1, there also appears to be a table tbl_a in db2. If one thread updates db1.tbl_a and another thread updates db2.tbl_a, there will be problems.

If you really need this, you must change the following code in `mysys/mf_format.c':

if (!lstat(to,&stat_buff))  /* Check if it's a symbolic link */
    if (S_ISLNK(stat_buff.st_mode) && realpath(to,buff))

Change the code to this:

if (realpath(to,buff))

10.11 How MySQL locks tables

All locking in MySQL is deadlock-free. This is managed by always requesting all needed locks at once at the beginning of a query and always locking the tables in the same order.

The locking method MySQL uses for WRITE locks works as follows:

The locking method MySQL uses for READ locks works as follows:

When a lock is released, the lock is made available to the threads in the write lock queue, then to the threads in the read lock queue.

This means that if you have many updates on a table, SELECT statements will wait until there are no more updates.

To work around this for the case where you want to do many INSERT and SELECT operations on a table, you can insert rows in a temporary table and update the real table with the records from the temporary table once in a while.

This can be done with the following code:

mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
mysql> insert into real_table select * from insert_table;
mysql> delete from insert_table;

You can use the LOW_PRIORITY options with INSERT if you want to prioritize retrieval in some specific cases. See section 7.13 INSERT syntax.

You could also change the locking code in `mysys/thr_lock.c' to use a single queue. In this case, write locks and read locks would have the same priority, which might help some applications.

10.12 Table locking issues

The table locking code in MySQL is deadlock free.

MySQL uses table locking (instead of row locking or column locking) to achieve a very high lock speed. For large tables, table locking is MUCH better than row locking, but there are of course some pitfalls.

Table locking enables many threads to read from a table at the same time, but if a thread wants to write to a table, it must first get exclusive access. During the update all others threads that want to access this particular table will wait until the update is ready.

As updates of databases normally are considered to be more important than SELECT, all statements that update a table have higher priority than statements that retrieve information from a table. This should ensure that updates are not 'starved' because one issues a lot of heavy queries against a specific table.

One main problem with this is the following:

Some possible solutions to this problem are:

10.13 How to arrange a table to be as fast/small as possible

You can get better performance on a table and minimize storage space using the techniques listed below:

To check how fragmented your tables are, run isamchk -evi on the `.ISM' file. See section 13 Maintaining a MySQL installation.

10.14 Factors affecting the speed of INSERT statements

The time to insert a record consists of:

Where (number) is proportional time. This does not take into consideration the initial overhead to open tables (which is done once for each concurrently-running query).

The size of the table slows down the insertion of indexes by N log N (B-trees).

You can speed up insertions by locking your table and/or using multiple value lists with INSERT statements. Using multiple value lists can be up to 5 times faster than using separate inserts.

mysql> INSERT INTO a VALUES (1,23),(2,34),(4,33);
mysql> INSERT INTO a VALUES (8,26),(6,29);

The main speed difference is that the index buffer is flushed to disk only once, after all INSERT statements have completed. Normally there would be as many index buffer flushes as there are different INSERT statements. Locking is not needed if you can insert all rows with a single statement.

Locking will also lower the total time of multi-connection tests, but the maximum wait time for some threads will go up (because they wait for locks). For example:

thread 1 does 1000 inserts
thread 2, 3, and 4 does 1 insert
thread 5 does 1000 inserts

If you don't use locking, 2, 3 and 4 will finish before 1 and 5. If you use locking, 2, 3 and 4 probably will not finish before 1 or 5, but the total time should be about 40% faster.

As INSERT, UPDATE and DELETE operations are very fast in MySQL, you will obtain better overall performance by adding locks around everything that does more than about 5 inserts or updates in a row. If you do very many inserts in a row, you could do a LOCK TABLES followed by a UNLOCK TABLES once in a while (about each 1000 rows) to allow other threads access to the table. This would still result in a nice performance gain.

Of course, LOAD DATA INFILE is much faster still.

If you are inserting a lot of rows from different clients, you can get higher speed by using the INSERT DELAYED statement. See section 7.13 INSERT syntax.

10.15 Factors affecting the speed of DELETE statements

The time to delete a record is exactly proportional to the number of indexes. To delete records more quickly, you can increase the size of the index cache. The default index cache is 1M; to get faster deletes, it should be increased by several factors (try 16M if you have enough memory).

10.16 How do I get MySQL to run at full speed?

Start by benchmarking your problem! You can take any program from the MySQL benchmark suite (normally found in the `sql-bench' directory) and modify it for your needs. By doing this, you can try different solutions to your problem and test which is really the fastest solution for you.

If your problem is with some explicit MySQL function, you can always time this in the MySQL client:

mysql> select benchmark(1000000,1+1);
| benchmark(1000000,1+1) |
|                      0 |
1 row in set (0.32 sec)

The above shows that MySQL can execute 1,000,000 + expressions in 0.32 seconds on a simple PentiumII 400MHz.

All MySQL functions should be very optimized, but there may be some exceptions and the benchmark(loop_count,expression) is a great tool to find if this is a problem with your query.

10.17 What are the different row formats? Or, when should VARCHAR/CHAR be used?

MySQL dosen't have true SQL VARCHAR types.

Instead, MySQL has three different ways to store records and uses these to emulate VARCHAR.

If a table doesn't have any VARCHAR, BLOB or TEXT columns, a fixed row size is used. Otherwise a dynamic row size is used. CHAR and VARCHAR columns are treated identically from the application's point of view; both have trailing spaces removed when the columns are retrieved.

You can check the format used in a table with isamchk -d (-d means ``describe the table'').

MySQL has three different table formats: fixed-length, dynamic and compressed. These are compared below.

Fixed-length tables

Dynamic tables

Compressed tables

MySQL can support different index types, but the normal type is NISAM. This is a B-tree index and you can roughly calculate the size for the index file as (key_length+4)*0.67, summed over all keys. (This is for the worst case when all keys are inserted in sorted order.)

String indexes are space compressed. If the first index part is a string, it will also be prefix compressed. Space compression makes the index file smaller if the string column has a lot of trailing space or is a VARCHAR column that is not always used to the full length. Prefix compression helps if there are many strings with an identical prefix.

10.18 MySQL table types.

Table types are introduced in MySQL 3.23!

When you create a new table, you can tell MySQL which table type it should use for the table. MySQL will always create a .frm file to hold the table and column definitions. Depending on the table type the index and data will be stored in other files.

You can convert tables between different types with the ALTER TABLE statement. See section 7.7 ALTER TABLE syntax.

Go to the first, previous, next, last section, table of contents.