You can get the default buffer sizes used by the
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
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,
indicates 16 megabytes. Case of suffix letters does not matter;
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,...).
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
call should have more details. Check your OS documentation for the
maximum value for this variable. Attempting to set
higher than this maximum will be
The number of seconds the
mysqld server is waiting for a connect
packet before responding with
How long a
INSERT DELAYED thread should wait for
delayed_insert_limit rows, the
DELAYED handler will check if there are any
pending. If so, it allows these to execute before continuing.
How big a queue (in rows) should be allocated for handling
If the queue becomes full, any client that does
INSERT DELAYED will wait until
there is room in the queue again.
If this is set to a non-zero value, then every
flush_time seconds all
tables will be closed (to free up resources and sync things to disk).
The size of the buffer that is used for full joins (joins that do not
use indexes). The buffer is allocated one time for each full join
between two tables. Increase this value to get a faster full join when
adding indexes is not possible. (Normally the best way to get fast joins
is to add indexes.)
Index blocks are buffered and are shared by all threads.
the size of the buffer used for index blocks. You might want to increase this
value when doing many
INSERT operations on a table
with lots of indexes. To get even more speed, use
If a query takes longer than this (in seconds), the
will be incremented.
The maximum size of one packet. The message buffer is initialized to
net_buffer_length bytes, but can grow up to
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
The number of simultaneous clients allowed. Increasing this value increases
the number of file descriptors that
mysqld requires. See below for
comments on file descriptor limits.
If there is more than this number of interrupted connections from a host
this host will be blocked for further connections. You can unblock a host
with the command
Don't start more than this number of threads to handle
statements. If you try to insert data in a new table after all
DELAYED threads are in use, the row will be inserted as if the
DELAYED attribute wasn't specified.
Joins that are probably going to read more than
records return an error. Set this value if your users tend to perform joins
WHERE clause that take a long time and return
millions of rows.
The number of bytes to use when sorting
values (only the first
max_sort_length bytes of each value
are used; the rest are ignored).
(This option doesn't yet do anything).
Maximum number of temporary tables a client can keep open at the same time.
The communication buffer is reset to this size between queries. This
should not normally be changed, but if you have very little memory, you
can set it to the expected size of a query. (That is, the expected length of
SQL statements sent by clients. If statements exceed this length, the buffer
is automatically enlarged, up to
Each thread that does a sequential scan allocates a buffer of this
size for each table it scans. If you do many sequential scans, you may
want to increase this value.
Each thread that needs to do a sort allocates a buffer of this
size. Increase this value for faster
ORDER BY or
18.5 Where MySQL stores temporary files.
The number of open tables for all threads. Increasing this value increases
the number of file descriptors that
mysqld requires. MySQL
needs two file descriptors for each unique open table. See below for
comments on file descriptor limits. For information about how the table
cache works, see 10.8 How MySQL opens and closes tables.
If a temporary table exceeds this size, MySQL generates an error of
The table tbl_name is full. Increase the value of
tmp_table_size if you do many advanced
GROUP BY queries.
The stack size for each thread. Many of the limits detected by the
crash-me test are dependent on this value. The default is normally
11 The MySQL benchmark suite.
The number of seconds the server waits for activity on a connection before
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
For example, for 200 open connections, you should have a table cache of
200 * n, where
n is the maximum number of tables in
MySQL uses algorithms that are very scalable, so you can usually
run with very little memory or give MySQL more memory to get
If you have much memory and many tables and want maximum
performance with a moderate number of clients, you should use something
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 &
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
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