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
bdb_cache_size current value: 1048540
binlog_cache_size current_value: 32768
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
interactive_timeout current value: 28800
join_buffer_size current value: 131072
key_buffer_size current value: 1048540
lower_case_table_names current value: 0
long_query_time current value: 10
max_allowed_packet current value: 1048576
max_binlog_cache_size current_value: 4294967295
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
max_write_lock_count current value: 4294967295
myisam_sort_buffer_size current value: 8388608
net_buffer_length current value: 16384
net_retry_count current value: 10
net_read_timeout current value: 30
net_write_timeout current value: 60
query_buffer_size current value: 0
record_buffer current value: 131072
record_rnd_buffer current value: 131072
slow_launch_time current value: 2
sort_buffer current value: 2097116
table_cache current value: 64
thread_concurrency current value: 10
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
You can find a full description for all variables in the `SHOW
VARIABLES' section in this manual. Note:SHOW VARIABLES.
You can also see some statistics from a running server by issuing the
command `SHOW STATUS'. Note:SHOW STATUS.
MySQL uses algorithms that are very scalable, so you can usually run
with very little memory. If you, however, give MySQL more memory, you
will normally also get better performance.
When tuning a MySQL server, the two most important variables to use are
`key_buffer_size' and `table_cache'. You should first feel confident
that you have these right before trying to change any of the other
variables.
If you have much memory (>=256M) 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=64M -O table_cache=256 \
-O sort_buffer=4M -O record_buffer=1M &
If you have only 128M and only a few tables, but you still do a lot of
sorting, you can use something like:
shell> safe_mysqld -O key_buffer=16M -O sort_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 you are doing a `GROUP BY' or `ORDER BY' on files that are much
bigger than your available memory you should increase the value of
`record_rnd_buffer' to speed up the reading of rows after the sorting
is done.
When you have installed MySQL, the `support-files' directory will
contain some different `my.cnf' example files, `my-huge.cnf',
`my-large.cnf', `my-medium.cnf', and `my-small.cnf', you can use as a
base to optimize your system.
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.