MySQL 5.0 Reference Manual  /  ...  /  Tuning Server Parameters

8.12.2 Tuning Server Parameters

You can determine the default buffer sizes used by the mysqld server using this command:

shell> mysqld --verbose --help

This command produces a list of all mysqld options and configurable system variables. The output includes the default variable values and looks something like this:

help                              TRUE
abort-slave-event-count           0
allow-suspicious-udfs             FALSE
auto-increment-increment          1
auto-increment-offset             1
automatic-sp-privileges           TRUE
basedir                           /home/jon/bin/mysql-5.0/
...
tmpdir                            (No default value)
transaction_alloc_block_size      8192
transaction_prealloc_size         4096
updatable_views_with_limit        1
use-symbolic-links                TRUE
verbose                           TRUE
wait_timeout                      28800
warnings                          1

For a mysqld server that is currently running, you can see the current values of its system variables by connecting to it and issuing this statement:

mysql> SHOW VARIABLES;

You can also see some statistical and status indicators for a running server by issuing this statement:

mysql> SHOW STATUS;

System variable and status information also can be obtained using mysqladmin:

shell> mysqladmin variables
shell> mysqladmin extended-status

For a full description of all system and status variables, see Section 5.1.4, “Server System Variables”, and Section 5.1.6, “Server Status Variables”.

MySQL uses algorithms that are very scalable, so you can usually run with very little memory. However, normally better performance results from giving MySQL more memory.

When tuning a MySQL server, the two most important variables to configure are key_buffer_size and table_cache. You should first feel confident that you have these set appropriately before trying to change any other variables.

The following examples indicate some typical variable values for different runtime configurations.

  • If you have at least 1-2GB of memory and many tables and want maximum performance with a moderate number of clients, use something like this:

    shell> mysqld_safe --key_buffer_size=384M --table_open_cache=4000 \
               --sort_buffer_size=4M --read_buffer_size=1M &
    
  • If you have only 256MB of memory and only a few tables, but you still do a lot of sorting, you can use something like this:

    shell> mysqld_safe --key_buffer_size=64M --sort_buffer_size=1M
    

    If there are very many simultaneous 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.

  • With little memory and lots of connections, use something like this:

    shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=100K \
               --read_buffer_size=100K &
    

    Or even this:

    shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=16K \
               --table_cache=32 --read_buffer_size=8K \
               --net_buffer_length=1K &
    

If you are performing GROUP BY or ORDER BY operations on tables that are much larger than your available memory, increase the value of read_rnd_buffer_size to speed up the reading of rows following sorting operations.

You can make use of the example option files included with your MySQL distribution; see Section 5.1.2, “Server Configuration Defaults”.

If you specify an option on the command line for mysqld or mysqld_safe, it remains in effect only for that invocation of the server. To use the option every time the server runs, put it in an option file.

To see the effects of a parameter change, do something like this:

shell> mysqld --key_buffer_size=128M --verbose --help

The variable values are listed near the end of the output. Make sure that the --verbose and --help options are last. Otherwise, the effect of any options listed after them on the command line are not reflected in the output.

For information on tuning the InnoDB storage engine, see Section 8.6, “Optimizing for InnoDB Tables”.


User Comments
Sign Up Login You must be logged in to post a comment.