GNU Info

(mysql.info)Server parameters

---

Next: Compile and link options Prev: System Up: Optimizing the Server

---

Enter node , (file) or (file)node

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.