Getting maximum performance from MySQL

1 Changing the size of MySQL buffers

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
join_buffer           current value: 131072
key_buffer            current value: 1048540
long_query_time       current value: 10
max_allowed_packet    current value: 1048576
max_connections       current value: 90
max_connect_errors    current value: 10
max_join_size         current value: 4294967295
max_sort_length       current value: 1024
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.

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 (but very short) 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 system call listen(2) 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.

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.

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

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 16.4 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.6 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 and max_connections 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.

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 dynamicly enlarged up to max_allowed_packet when needed. When a query is running a copy of the current query string is also alloced.
• 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 16.4 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.6 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.

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.

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 (Compatibility function).
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 decides which index will find fewer rows and uses 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.
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 */

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.
• Each table index is queried and the best index that spans less 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,...

6 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.

6.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.

7 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 at once. 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.

8 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))

9 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 or HIGH_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 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 18.4.49 How can I get the unique ID for the last inserted row?.
• 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.15 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.
• 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 /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 Using isamchk for table maintenance and crash recovery.

11 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-inserts. This will give all statements that update 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.

12 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.

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

14 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 Changing the size of MySQL buffers.
• 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.10 How to arrange a table to be as fast/small as possible.
• The --skip-locking option disables 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.
• 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. 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.

15 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.

SOURCE