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,...).

back_log: 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.
connect_timeout: The number of seconds the mysqld server is waiting for a connect packet before responding with Bad handshake.
delayed_insert_timeout: How long a INSERT DELAYED thread should wait for INSERT statements before terminating.
delayed_insert_limit: 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.
delayed_queue_size: 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.
flush_time: 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).
join_buffer: 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.)
key_buffer: Index blocks are buffered and are shared by all threads. key_buffer 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.
long_query_time: If a query takes longer than this (in seconds), the Slow_queries counter will be incremented.
max_allowed_packet: 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.
max_connections: 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.
max_connect_errors: 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.
max_delayed_threads: 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.
max_join_size: 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.
max_sort_length: 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).
max_tmp_tables: (This option doesn't yet do anything). Maximum number of temporary tables a client can keep open at the same time.
net_buffer_length: 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.)
record_buffer: 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.
sort_buffer: 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.
table_cache: 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.
tmp_table_size: 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.
thread_stack: 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.
wait_timeout: 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.

The key buffer (variable key_buffer) is shared by all threads; Other buffers used by the server are allocated as needed.
Each connection uses some thread specific space; A stack (64K, variable thread_stack) a connection buffer (variable net_buffer_length), and a result buffer (variable net_buffer_length). The connection buffer and result buffer are dynamically enlarged up to max_allowed_packet when needed. When a query is running a copy of the current query string is also allocated.
All threads share the same base memory.
Nothing is memory-mapped yet (except compressed tables, but that's another story). This is because the 32-bit memory space of 4GB is not large enough for most large tables. When we get a system with a 64-bit address space, we may add general support for memory-mapping.
Each request doing a sequential scan over a table allocates a read buffer (variable record_buffer).
All joins are done in one pass and most joins can be done without even using a temporary table. Most temporary tables are memory-based (HEAP) tables. Temporary tables with a big record length (calculated as the sum of all column lengths) or that contain BLOB columns are stored on disk. One current problem is that if a HEAP table exceeds the size of tmp_table_size, you get the error The table tbl_name is full. In the future, we will fix this by automatically changing the in-memory (HEAP) table to a disk-based (NISAM) table as necessary. To work around this problem, you can increase the temporary table size by setting the tmp_table_size option to mysqld, or by setting the SQL option SQL_BIG_TABLES in the client program. See section 7.24 SET OPTION syntax. In MySQL 3.20, the maximum size of the temporary table was record_buffer*16, so if you are using this version, you have to increase the value of record_buffer. You can also start mysqld with the --big-tables option to always store temporary tables on disk, however, this will affect the speed of all complicated queries.
Most requests doing a sort allocate a sort buffer and one or two temporary files. See section 18.5 Where MySQL stores temporary files.
Almost all parsing and calculating is done in a local memory store. No memory overhead is needed for small items and the normal slow memory allocation and freeing is avoided. Memory is allocated only for unexpectedly large strings (this is done with malloc() and free()).
Each index file is opened once and the data file is opened once for each concurrently-running thread. For each concurrent thread, a table structure, column structures for each column, and a buffer of size 3 * n is allocated (where n is the maximum row length, not counting BLOB columns). A BLOB uses 5 to 8 bytes plus the length of the BLOB data.
For each table having BLOB columns, a buffer is enlarged dynamically to read in larger BLOB values. If you scan a table, a buffer as large as the largest BLOB value is allocated.
Table handlers for all in-use tables are saved in a cache and managed as a FIFO. Normally the cache has 64 entries. If a table has been used by two running threads at the same time, the cache contains two entries for the table. See section 10.8 How MySQL opens and closes tables.
A mysqladmin flush-tables command closes all tables that are not in use and marks all in-use tables to be closed when the currently executing thread finishes. This will effectively free most in-use memory.

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.

If you use pgcc and compile everything with -O6, the mysqld server is 11% faster than with gcc.
If you link dynamically (without -static), the result is 13% slower.
If you connect using TCP/IP rather than Unix sockets, the result is 7.5% slower.
On a Sun SPARCstation 10, gcc 2.7.3 is 13% faster than Sun Pro C++ 4.2.
On Solaris 2.5.1, MIT-pthreads is 8-12% slower than Solaris native threads.

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:

Quickly find the rows that match a WHERE clause.
Retrieve rows from other tables when performing joins.
Find the MAX() or MIN() value for a specific key.
Sort or group a table if the sorting or grouping is done on a leftmost prefix of a usable key (e.g., ORDER BY key_part_1,key_part_2 ). The key is read in reverse order if all key parts are followed by DESC.
Retrieve values without consulting the data file, in some cases. If all used columns for some table are numeric and form a leftmost prefix for some key, the values may be retrieved from the index tree for greater speed.

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:

Removal of unnecessary parentheses:

   ((a AND b) AND c OR (((a AND b) AND (c AND d))))
-> (a AND b AND c) OR (a AND b AND c AND d)

Constant folding:

   (a<b AND b=c) AND a=5
-> b>5 AND b=c AND a=5

Constant condition removal (needed because of constant folding):

   (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
-> B=5 OR B=6

Constant expressions used by indexes are evaluated only once.
COUNT(*) on a single table without a WHERE is retrieved directly from the table information. This is also done for any NOT NULL expression when used with only one table.
Early detection of invalid constant expressions. MySQL quickly detects that some SELECT statements are impossible and returns no rows.
HAVING is merged with WHERE if you don't use GROUP BY or group functions (COUNT(), MIN()...)
For each sub join, a simpler WHERE is constructed to get a fast WHERE evaluation for each sub join and also to skip records as soon as possible.
All constant tables are read first, before any other tables in the query. A constant table is:
- An empty table or a table with 1 row.
- A table that is used with a WHERE clause on a UNIQUE index or a PRIMARY KEY, where all index parts are used with constant expressions.
All the following tables are used as constant tables:
```
mysql> SELECT * FROM t WHERE primary_key=1;
mysql> SELECT * FROM t1,t2
           WHERE t1.primary_key=1 AND t2.primary_key=t1.id;
```
The best join combination to join the tables is found by trying all possibilities :(. If all columns in ORDER BY and in GROUP BY come from the same table, then this table is preferred first when joining.
If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.
If you use SQL_SMALL_RESULT, MySQL will use an in-memory temporary table.
As DISTINCT is converted to a GROUP BY on all columns, DISTINCT combined with ORDER BY will in many cases also need a temporary table.
Each table index is queried and the best index that spans fewer than 30% of the rows is used. If no such index can be found, a quick table scan is used.
In some cases, MySQL can read rows from the index without even consulting the data file. If all columns used from the index are numeric, then only the index tree is used to resolve the query.
Before each record is output, those that do not match the HAVING clause are skipped.

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

The table B is set to be dependent on table A.
The table A is set to be dependent on all tables (except B) that are used in the LEFT JOIN condition.
All LEFT JOIN conditions are moved to the WHERE clause.
All standard join optimizations are done, with the exception that a table is always read after all tables it is dependent on. If there is a circular dependence then MySQL will issue an error.
All standard WHERE optimzations are done.
If there is a row in A that matches the WHERE clause, but there wasn't any row in B that matched the LEFT JOIN condition, then an extra B row is generated with all columns set to NULL.
If you use LEFT JOIN to find rows that doesn't exist in some table and you have the following test: column_name IS NULL in the WHERE part, where column_name is a column that is declared as NOT NULL, then MySQL will stop searching after more rows (for a particular key combination) after it has found one row that matches the LEFT JOIN condition.

10.7 How MySQL optimizes `LIMIT`

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

If you are selecting only a few rows with LIMIT, MySQL will use indexes in some cases when it normally would prefer to do a full table scan.
If you use LIMIT # with ORDER BY, MySQL will end the sorting as soon as it has found the first # lines instead of sorting the whole table.
When combinating LIMIT # with DISTINCT, MySQL will stop as soon as it finds # unique rows.
In some cases a GROUP BY can be resolved by reading the key in order (or do a sort on the key) and then calculate summaries until the key value changes. In this case LIMIT # will not calculate any unnecessary GROUP's.
As soon as MySQL has sent the first # rows to the client, it will abort the query.
LIMIT 0 will always quickly return an empty set. This is useful to check the query and to get the column types of the result columns.
The size of temporary tables uses the LIMIT # to calculate how much space is needed to resolve the query.

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:

Tables that are not currently in use are released, in least-recently-used order.
If the cache is full and no tables can be released, but a new table needs to be opened, the cache is temporarily extended as necessary.
If the cache is in a temporarily-extended state and a table goes from in-use to not-in-use state, it is closed and released from the cache.

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:

If there are no locks on the table, put a write lock on it.
Otherwise, put the lock request in the write lock queue.

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

If there are no write locks on the table, put a read lock on it.
Otherwise, put the lock request in the read lock queue.

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;
mysql> UNLOCK TABLES;

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:

A client issues a SELECT that takes a long time to run.
Another client then issues an INSERT on a used table; This client will wait until the SELECT is finished..
Another client issues another SELECT statement on the same table; As INSERT has higher priority than SELECT, this SELECT will wait for the INSERT to finish. It will also wait for the first SELECT to finish!

Some possible solutions to this problem are:

Try to get the SELECT statements to run faster; You may have to create some summary tables to do this.
Start mysqld with --low-priority-updates. This will give all statements that update (modify) a table lower priority than a SELECT statement. In this case the last SELECT statement in the previous scenario would execute before the INSERT statement.
You can give a specific INSERT,UPDATE or DELETE statement lower priority with the LOW_PRIORITY attribute.
You can specify that all updates from a specific thread should be done with low priority by using the SQL command: SET SQL_LOW_PRIORITY_UPDATES=1. See section 7.24 SET OPTION syntax.
You can specify that a specific SELECT is very important with the HIGH_PRIORITY attribute. See section 7.11 SELECT syntax.
If you mainly mix INSERT and SELECT statements, the DELAYED attribute to INSERT will probably solve your problems. See section 7.13 INSERT syntax.
If you have problems with SELECT and DELETE, the LIMIT option to DELETE may help. See section 7.10 DELETE syntax.

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:

Declare columns to be NOT NULL if possible. It makes everything faster and you save one bit per column.
Take advantage of the fact that all columns have default values. Insert values explicitly only when the value to be inserted differs from the default. You don't have to insert a value into the first TIMESTAMP column or into an AUTO_INCREMENT column in an INSERT statement. See section 20.4.29 mysql_insert_id().
Use the smaller integer types if possible to get smaller tables. For example, MEDIUMINT is often better than INT.
If you don't have any variable-length columns (VARCHAR, TEXT or BLOB columns), a fixed-size record format is used. This is much faster but unfortunately may waste some space. See section 10.17 What are the different row formats? Or, when should VARCHAR/CHAR be used?.
To help MySQL optimize queries better, run isamchk --analyze on a table after it has been loaded with relevant data. This updates a value for each index that indicates the average number of rows that have the same value. (For unique indexes, this is always 1, of course.)
To sort an index and data according to an index, use isamchk --sort-index --sort-records=1 (if you want to sort on index 1). If you have a unique index from which you want to read all records in order according to that index, this is a good way to make that faster. Note however that this sorting isn't written optimally and will take a long time for a large table!
For INSERT statements, use multiple value lists if possible. This is much faster than using separate INSERT statements.
When loading a table with data, use LOAD DATA INFILE. This is usually 20 times faster than using a lot of INSERT statements. See section 7.15 LOAD DATA INFILE syntax. You can even get more speed when loading data into a table with many indexes using the following procedure:
1. Create the table in mysql or Perl with CREATE TABLE.
2. Execute mysqladmin flush-tables.
3. Use isamchk --keys-used=0 -rq /path/to/db/tbl_name. This will remove all usage of all indexes from the table.
4. Insert data into the table with LOAD DATA INFILE.
5. If you have pack_isam and want to compress the table, run pack_isam on it.
6. Recreate the indexes with isamchk -r -q /path/to/db/tbl_name.
7. Execute mysqladmin flush-tables.
To get some more speed for both LOAD DATA INFILE and INSERT, enlarge the key buffer. This can be done with the -O key_buffer=# option to mysqld or safe_mysqld. For example, 16M should be a good value if you have much RAM. :)
When dumping data as text files for use by other programs, use SELECT ... INTO OUTFILE. See section 7.15 LOAD DATA INFILE syntax.
When doing many successive inserts or updates, you can get more speed by locking your tables using LOCK TABLES. LOAD DATA INFILE and SELECT ...INTO OUTFILE are atomic, so you don't have to use LOCK TABLES when using them. See section 7.23 LOCK TABLES/UNLOCK TABLES syntax.

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:

Connect: (3)
Sending query to server: (2)
Parsing query: (2)
Inserting record: (1 x size of record)
Inserting indexes: (1 x indexes)
Close: (1)

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> LOCK TABLES a WRITE;
mysql> INSERT INTO a VALUES (1,23),(2,34),(4,33);
mysql> INSERT INTO a VALUES (8,26),(6,29);
mysql> UNLOCK TABLES;

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.

Start mysqld with the correct options. More memory gives more speed if you have it. See section 10.1 Tuning server parameters.
Create indexes to make your SELECT statements faster. See section 10.4 How MySQL uses indexes.
Optimize your column types to be as efficient as possible. For example, declere columns to be NOT NULL if possible. See section 10.13 How to arrange a table to be as fast/small as possible.
MySQL has two different levels of locks: Internal locks and external locks. The internal locks ensures that all update/retrieve operations are atomic (run without conflicts from other clients). The external locks allows multiple MySQL servers to run on the same data and allows you to use isamchk to check tables without taking down mysqld. The --skip-locking option disables external locking (file locking) between SQL requests. This gives greater speed but has the following consequences:
- You MUST flush all tables with mysqladmin flush-tables before you try to check or repair tables with isamchk. (isamchk -d tbl_name is always allowed, since that simply displays table information.)
- You can't run two MySQL servers on the same data files, if both are going to update the same tables.
The --skip-locking option is on by default when compiling with MIT-pthreads, because flock() isn't fully supported by MIT-pthreads on all platforms. The only case when you can't use --skip-locking is if you run multiple MySQL SERVERS (not clients) on the same data (or run isamchk on the table without first flushing the mysqld server tables first). You can still use LOCK TABLES / UNLOCK TABLES even if you are using --skip-locking
If updates are a problem, you can delay updates and then do many updates in a row later. Doing many updates in a row is much quicker than doing one at a time.
On FreeBSD systems, if the problem is with MIT-pthreads, upgrading to FreeBSD 3.0 (or higher) should help, because you can use native threads. This makes it possible to use Unix sockets (with FreeBSD, this is quicker than connecting using TCP/IP with MIT-pthreads) and the threads package is much more integrated.
GRANT checking on the table or column level will decrease performance.

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

This is the default format. It's used when the table contains no VARCHAR, BLOB or TEXT columns.
All CHAR, NUMERIC and DECIMAL columns are space-padded to the column width.
Very quick.
Easy to cache.
Easy to reconstruct after a crash, because records are located in fixed positions.
Doesn't have to be reorganized (with isamchk) unless a huge number of records are deleted and you want to return free disk space to the operating system.
Usually requires more disk space than dynamic tables.

Dynamic tables

This format is used if the table contains any VARCHAR, BLOB or TEXT columns.
All string columns are dynamic (except those with a length less than 4).
Each record is preceded by a bitmap indicating which columns are empty ('') for string columns, or zero for numeric columns (this isn't the same as columns containing NULL values). If a string column has a length of zero after removal of trailing spaces, or a numeric column has a value of zero, it is marked in the bit map and not saved to disk. Non-empty strings are saved as a length byte plus the string contents.
Usually takes much less disk space than fixed-length tables.
Each record uses only as much space as is required. If a record becomes larger, it is split into as many pieces as required. This results in record fragmentation.
If you update a row with information that extends the row length, the row will be fragmented. In this case, you may have to run isamchk -r from time to time to get better performance. Use isamchk -ei tbl_name for some statistics.
Not as easy to reconstruct after a crash, because a record may be fragmented into many pieces and a link (fragment) may be missing.
The expected row length for dynamic sized records is:
```
3
+ (number of columns + 7) / 8
+ (number of char columns)
+ packed size of numeric columns
+ length of strings
+ (number of NULL columns + 7) / 8
```
There is a penalty of 6 bytes for each link. A dynamic record is linked whenever an update causes an enlargement of the record. Each new link will be at least 20 bytes, so the next enlargement will probably go in the same link. If not, there will be another link. You may check how many links there are with isamchk -ed. All links may be removed with isamchk -r.

Compressed tables

A read-only table made with the pack_isam utility. All customers with extended MySQL email support are entitled to a copy of pack_isam for their internal usage.
The uncompress code exists in all MySQL distributions so that even customers who don't have pack_isam can read tables that were compressed with pack_isam (as long as the table was compressed on the same platform).
Takes very little disk space. Minimises disk usage.
Each record is compressed separately (very little access overhead). The header for a record is fixed (1-3 bytes) depending on the biggest record in the table. Each column is compressed differently. Some of the compression types are:
- There is usually a different Huffman table for each column.
- Suffix space compression.
- Prefix space compression.
- Numbers with value 0 are stored using 1 bit.
- If values in an integer column have a small range, the column is stored using the smallest possible type. For example, a BIGINT column (8 bytes) may be stored as a TINYINT column (1 byte) if all values are in the range 0 to 255.
- If a column has only a small set of possible values, the column type is converted to ENUM.
- A column may use a combination of the above compressions.
Can handle fixed or dynamic length records, but not BLOB or TEXT columns.
Can be uncompressed with isamchk.

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.

ISAM This is the original MySQL table type. This uses a B-tree index. The index is stored in a file with the .ISM extension and the data is stored in file with the .ISD extension. You can check/repair ISAM tables with the isamchk utility. See section 13.4 Using isamchk for crash recovery ISAM tables are not binary portable across OS/Platforms. ISAM has the following features/properties:
- Compressed and fixed length keys
- Fixed and dynamic record length
- 16 keys with 16 key parts / key
- Max key length 256 (default)
- Data is stored in machine format; Fast but is machine/OS dependent.
MyISAM MyISAM is the default table type in MySQL 3.23. It's based on the ISAM code and has a lot of useful extensions. The index is stored in a file with the .MYI extension and the data is stored in file with the .MYD extension. You can check/repair ISAM tables with the myisamchk utility. See section 13.4 Using isamchk for crash recovery The following is new in MyISAM:
- Supports for big files (63 bit) on OSes that support big files.
- All data are stored with low byte first. This makes the data machine and OS independent.
- All number keys are stored with high byte first to give better compression.
- Internal handling of one AUTO_INCREMENT column. MyISAM will automatically update this on INSERT/UPDATE. The AUTO_INCREMENT value can be reset with myisamchk. This will make AUTO_INCREMENT columns faster and old numbers will not be reused.
- BLOB and TEXT columns can be indexed.
- NULL values are allowed in indexed columns. This takes 0-1 bytes/key.
- Maximum key length is now 500 by default. In cases of keys longer than 250, a bigger key block size than the default of 1024 bytes is used for this key.
- Maximum number of keys/table enlarged to 32 as default. This can be enlarged to 64 without having to recompile myisamchk.
- There is a flag in the MyISAM file that indicates whether or not the table was closed correctly. This will soon be used for automatic repair in the MySQL server.
- myisamchk will now mark tables as checked. myisamchk --fast will only check those tables that don't have this mark.
- myisamchk -a stores statistics for key parts (and not only for whole keys as in NISAM).
- Dynamic size rows will now be much less fragmented when mixing deletes with updates and inserts. This is done by automatically combining adjacent deleted blocks and by extending blocks if the next block is deleted.
- myisampack (called pack_isam in NISAM) can pack BLOB and VARCHAR columns.
MyISAM also supports the following things, which MySQL will be able to use in the near future.
- Support for a true VARCHAR type; A VARCHAR column starts with a length stored in 2 bytes.
- Tables with VARCHAR may have fixed or dynamic record length.
- VARCHAR and CHAR may be up to 64K. All key segments have their own language definition. This will enable MySQL to have different language definitions per column.
- A hashed computed index can be used for UNIQUE; This will allow you to have UNIQUE on any combination of columns in a table. (You can't search on a UNIQUE computed index, however.)
HEAP HEAP tables use a hashed index and are stored in memory. This makes them very fast, but if MySQL crashes you will lose all data stored in them. HEAP is very usable as temporary tables!
```
CREATE TABLE test TYPE=HEAP SELECT ip,SUM(downloads) as down FROM log_table GROUP BY ip;
SELECT COUNT(ip),AVG(down) from test;
drop table test;
```
Here are some things you should consider when you use HEAP tables:
- You should always use specify MAX_ROWS in the CREATE statement to ensure that you accidently do not use all memory.
- Indexes will only be used with = and <=> (but are VERY fast).
- HEAP tables uses a fixed record length format.
- HEAP doesn't support BLOB/TEXT columns.
- HEAP doesn't support AUTO_INCREMENT columns.
- HEAP doesn't support an index on a NULL column.
- You can have non-unique keys in a HEAP table (not that normal with hashed tables).
- HEAP tables are shared between all clients (just like any other table).
- Data for HEAP tables are allocated in small blocks. The tables are 100% dynamic (on inserting). No overflow areas and no extra key space is needed. Deleted rows are put in a linked list and will be reused when you insert new data into the table.
- To free memory, you should execute DELETE FROM heap_table or DROP TABLE heap_table.
- To ensure that you accidentally don't do anything stupid, you can't create HEAP tables bigger than max_heap_table_size.

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