(mysql.info.gz) Server parameters
Info Catalog
(mysql.info.gz) System
(mysql.info.gz) Optimizing the Server
(mysql.info.gz) Controlling optimizer
7.5.2 Tuning Server Parameters
------------------------------
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
If there is a `mysqld' server currently running, you can see what
values it actually is using for the 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
You can find a full description for all system and status variables in
Server system variables and Server status variables.
MySQL uses algorithms that are very scalable, so you can usually run
with very little memory. However, normally you will 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 `--VAR_NAME=VALUE' syntax to set the variable 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=VAR_NAME=VALUE' or `-O
VAR_NAME=VALUE' syntax.
* 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 doing `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 after sorting
operations.
When you have installed MySQL, the `support-files' directory will
contain some different `my.cnf' sample files: `my-huge.cnf',
`my-large.cnf', `my-medium.cnf', and `my-small.cnf'. You can use these
as a basis for optimizing your system.
Note that 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 will not be
reflected in the output.
For information on tuning the `InnoDB' storage engine, see
`InnoDB' tuning InnoDB tuning.
Info Catalog
(mysql.info.gz) System
(mysql.info.gz) Optimizing the Server
(mysql.info.gz) Controlling optimizer
automatically generated byinfo2html