8.11.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:

abort-slave-event-count           0allow-suspicious-udfs             FALSEauto-increment-increment          1auto-increment-offset             1automatic-sp-privileges           TRUEback_log                          50basedir                           /home/jon/bin/mysql-5.7/bind-address                      (No default value)binlog-row-event-max-size         1024binlog_cache_size                 32768binlog_format                     (No default value)bulk_insert_buffer_size           8388608character-set-client-handshake    TRUEcharacter-set-filesystem          binarycharacter-set-server              latin1character-sets-dir                /home/jon/bin/mysql-5.7/share/mysql/charsets/chroot                            (No default value)collation-server                  latin1_swedish_cicompletion-type                   0concurrent-insert                 1connect_timeout                   10console                           FALSEdatadir                           .datetime_format                   %Y-%m-%d %H:%i:%sdate_format                       %Y-%m-%ddefault-storage-engine            MyISAMdefault-time-zone                 (No default value)default_week_format               0delayed_insert_limit              100delayed_insert_timeout            300delayed_queue_size                1000disconnect-slave-event-count      0div_precision_increment           4engine-condition-pushdown         TRUEexpire_logs_days                  0external-locking                  FALSEflush_time                        0ft_max_word_len                   84ft_min_word_len                   4ft_query_expansion_limit          20ft_stopword_file                  (No default value)gdb                               FALSEgeneral_log                       FALSEgeneral_log_file                  (No default value)group_concat_max_len              1024help                              TRUEinit-connect                      (No default value)init-file                         (No default value)init-slave                        (No default value)innodb                            TRUEinnodb-adaptive-hash-index        TRUEinnodb-additional-mem-pool-size   1048576innodb-autoextend-increment       8innodb-autoinc-lock-mode          1innodb-buffer-pool-size           8388608innodb-checksums                  TRUEinnodb-commit-concurrency         0innodb-concurrency-tickets        500innodb-data-file-path             (No default value)innodb-data-home-dir              (No default value)innodb-doublewrite                TRUEinnodb-fast-shutdown              1innodb-file-io-threads            4innodb-file-per-table             FALSEinnodb-flush-log-at-trx-commit    1innodb-flush-method               (No default value)innodb-force-recovery             0innodb-lock-wait-timeout          50innodb-locks-unsafe-for-binlog    FALSEinnodb-log-buffer-size            1048576innodb-log-file-size              5242880innodb-log-files-in-group         2innodb-log-group-home-dir         (No default value)innodb-max-dirty-pages-pct        90innodb-max-purge-lag              0innodb-mirrored-log-groups        1innodb-open-files                 300innodb-rollback-on-timeout        FALSEinnodb-stats-on-metadata          TRUEinnodb-status-file                FALSEinnodb-support-xa                 TRUEinnodb-sync-spin-loops            20innodb-table-locks                TRUEinnodb-thread-concurrency         8innodb-thread-sleep-delay         10000interactive_timeout               28800join_buffer_size                  131072keep_files_on_create              FALSEkey_buffer_size                   8384512key_cache_age_threshold           300key_cache_block_size              1024key_cache_division_limit          100language                          /home/jon/bin/mysql-5.7/share/mysql/english/large-pages                       FALSElc-time-names                     en_USlocal-infile                      TRUElog                               (No default value)log-bin                           (No default value)log-bin-index                     (No default value)log-bin-trust-function-creators   FALSElog-errorlog-isam                          myisam.loglog-output                        FILElog-queries-not-using-indexes     FALSElog-short-format                  FALSElog-slave-updates                 FALSElog-slow-admin-statements         FALSElog-slow-slave-statements         FALSElog-tc                            tc.loglog-tc-size                       24576log-warnings                      1log_slow_queries                  (No default value)long_query_time                   10low-priority-updates              FALSElower_case_table_names            0master-retry-count                86400max-binlog-dump-events            0max_allowed_packet                1048576max_binlog_cache_size             18446744073709547520max_binlog_size                   1073741824max_connections                   151max_connect_errors                10max_delayed_threads               20max_error_count                   64max_heap_table_size               16777216max_join_size                     18446744073709551615max_length_for_sort_data          1024max_prepared_stmt_count           16382max_relay_log_size                0max_seeks_for_key                 18446744073709551615max_sort_length                   1024max_sp_recursion_depth            0max_tmp_tables                    32max_user_connections              0max_write_lock_count              18446744073709551615memlock                           FALSEmin_examined_row_limit            0multi_range_count                 256myisam-recover-options            OFFmyisam_block_size                 1024myisam_data_pointer_size          6myisam_max_sort_file_size         9223372036853727232myisam_repair_threads             1myisam_sort_buffer_size           8388608myisam_stats_method               nulls_unequalmyisam_use_mmap                   FALSEndb-autoincrement-prefetch-sz     1ndb-cache-check-time              0ndb-connectstring                 (No default value)ndb-extra-logging                 0ndb-force-send                    TRUEndb-index-stat-enable             FALSEndb-mgmd-host                     (No default value)ndb-nodeid                        0ndb-optimized-node-selection      TRUEndb-report-thresh-binlog-epoch-slip 3ndb-report-thresh-binlog-mem-usage 10ndb-shm                           FALSEndb-use-copying-alter-table       FALSEndb-use-exact-count               TRUEndb-use-transactions              TRUEndb_force_send                    TRUEndb_use_exact_count               TRUEndb_use_transactions              TRUEnet_buffer_length                 16384net_read_timeout                  30net_retry_count                   10net_write_timeout                 60new                               FALSEold                               FALSEold-alter-table                   FALSEold-passwords                     FALSEold-style-user-limits             FALSEopen_files_limit                  1024optimizer_prune_level             1optimizer_search_depth            62pid-file                          /home/jon/bin/mysql-5.7/var/tonfisk.pidplugin_dir                        /home/jon/bin/mysql-5.7/lib/mysql/pluginport                              3306port-open-timeout                 0preload_buffer_size               32768profiling_history_size            15query_alloc_block_size            8192query_cache_limit                 1048576query_cache_min_res_unit          4096query_cache_size                  0query_cache_type                  1query_cache_wlock_invalidate      FALSEquery_prealloc_size               8192range_alloc_block_size            4096read_buffer_size                  131072read_only                         FALSEread_rnd_buffer_size              262144relay-log                         (No default value)relay-log-index                   (No default value)relay-log-info-file               relay-log.inforelay_log_purge                   TRUErelay_log_space_limit             0replicate-same-server-id          FALSEreport-host                       (No default value)report-password                   (No default value)report-port                       3306report-user                       (No default value)safe-user-create                  FALSEsecure-auth                       TRUEsecure-file-priv                  (No default value)server-id                         0show-slave-auth-info              FALSEskip-grant-tables                 FALSEskip-slave-start                  FALSEslave-exec-mode                   STRICTslave-load-tmpdir                 /tmpslave_compressed_protocol         FALSEslave_net_timeout                 3600slave_transaction_retries         10slow-query-log                    FALSEslow_launch_time                  2slow_query_log_file               (No default value)socket                            /tmp/mysql.socksort_buffer_size                  2097144sporadic-binlog-dump-fail         FALSEsql-mode                          OFFsymbolic-links                    TRUEsync-binlog                       0sync-frm                          TRUEsysdate-is-now                    FALSEtable_definition_cache            256table_open_cache                  400tc-heuristic-recover              (No default value)temp-pool                         TRUEthread_cache_size                 0thread_concurrency                10thread_stack                      262144timed_mutexes                     FALSEtime_format                       %H:%i:%stmpdir                            (No default value)tmp_table_size                    16777216transaction_alloc_block_size      8192transaction_prealloc_size         4096updatable_views_with_limit        1verbose                           TRUEwait_timeout                      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 variablesshell> 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 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_open_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 256MB 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=64M
                    --table_open_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_open_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=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 14.2.4.1, "InnoDB Performance Tuning Tips".