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. 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. LOCK TABLES.

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