You can determine the default buffer sizes used by the
mysqld server with this command (prior to
MySQL 4.1, omit --verbose
):
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:
back_log current value: 5 bdb_cache_size current value: 1048540 binlog_cache_size current value: 32768 connect_timeout current value: 5 delayed_insert_limit current value: 100 delayed_insert_timeout current value: 300 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 long_query_time current value: 10 lower_case_table_names current value: 0 max_allowed_packet current value: 1048576 max_binlog_cache_size current value: 4294967295 max_connect_errors current value: 10 max_connections current value: 100 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_read_timeout current value: 30 net_retry_count current value: 10 net_write_timeout current value: 60 read_buffer_size current value: 131072 read_rnd_buffer_size current value: 262144 slow_launch_time current value: 2 sort_buffer current value: 2097116 table_cache current value: 64 thread_concurrency current value: 10 thread_stack current value: 131072 tmp_table_size current value: 1048576 wait_timeout current value: 28800
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.3, “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 you get better performance by 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. The examples use the
mysqld_safe script and use
--
syntax to set the variable var_name
=value
var_name
to the value value
. This syntax is
available as of MySQL 4.0. For older versions of MySQL, take the
following differences into account:
Use safe_mysqld rather than mysqld_safe.
Set variables using
--set-variable=
or var_name
=value
-O
syntax.
var_name
=value
For variable names that end in _size
, you
may need to specify them without _size
.
For example, the old name for
sort_buffer_size
is
sort_buffer
. The old name for
read_buffer_size
is
record_buffer
. To see which variables
your version of the server recognizes, use mysqld
--help.
If you have at least 256MB of memory and many tables and want maximum performance with a moderate number of clients, you should use something like this:
shell>mysqld_safe --key_buffer_size=64M --table_cache=256 \
--sort_buffer_size=4M --read_buffer_size=1M &
If you have only 128MB 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=16M --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, you should 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 4.2.3.3.2, “Preconfigured Option Files”.
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
(prior to MySQL 4.1, omit
--verbose
):
shell> mysqld --key_buffer_size=32M --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 13.2.14.1, “InnoDB
Performance Tuning Tips”.
MySQL Enterprise. For expert advice on tuning system parameters, subscribe to the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.
User Comments
You should mention more about setting the max_allowed_packet server/client side requirements, problems with compression and about the loose- prefix for "all?" variables?
http://bugs.mysql.com/bug.php?id=1011
Please incorporate good explanation from Monty.
[client]
loose-max_allowed_packet=1G
[mysqld]
loose-max_allowed_packet=1G
options.
Please add specific information on how to maximize buffer settings on servers with excess memory (> 2GB)
Add your own comment.