( Server parameters

Info Catalog ( System ( Optimizing the Server ( 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
      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
 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 ( System ( Optimizing the Server ( Controlling optimizer
automatically generated byinfo2html