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                           0
allow-suspicious-udfs                             FALSE
archive                                           ON
auto-increment-increment                          1
auto-increment-offset                             1
autocommit                                        TRUE
automatic-sp-privileges                           TRUE
back-log                                          50
basedir                                           /home/jon/bin/mysql-5.5-cluster/
big-tables                                        FALSE
bind-address                                      (No default value)
binlog-cache-size                                 32768
binlog-direct-non-transactional-updates           FALSE
binlog-format                                     STATEMENT
binlog-row-event-max-size                         1024
binlog-stmt-cache-size                            32768
blackhole                                         ON
bulk-insert-buffer-size                           8388608
character-set-client-handshake                    TRUE
character-set-filesystem                          binary
character-set-server                              latin1
character-sets-dir                                /home/jon/bin/mysql-5.5-cluster/share/charsets/
chroot                                            (No default value)
collation-server                                  latin1_swedish_ci
completion-type                                   NO_CHAIN
concurrent-insert                                 AUTO
connect-timeout                                   10
console                                           FALSE
datadir                                           (No default value)
date-format                                       %Y-%m-%d
datetime-format                                   %Y-%m-%d %H:%i:%s
default-storage-engine                            InnoDB
default-time-zone                                 (No default value)
default-week-format                               0
delay-key-write                                   ON
delayed-insert-limit                              100
delayed-insert-timeout                            300
delayed-queue-size                                1000
des-key-file                                      (No default value)
disconnect-slave-event-count                      0
div-precision-increment                           4
engine-condition-pushdown                         TRUE
event-scheduler                                   OFF
expire-logs-days                                  0
external-locking                                  FALSE
flush                                             FALSE
flush-time                                        0
ft-boolean-syntax                                 + -><()~*:""&|
ft-max-word-len                                   84
ft-min-word-len                                   4
ft-query-expansion-limit                          20
ft-stopword-file                                  (No default value)
gdb                                               FALSE
general-log                                       FALSE
general-log-file                                  /home/jon/bin/mysql-5.5-cluster/data/torsk.log
group-concat-max-len                              1024
help                                              TRUE
ignore-builtin-innodb                             FALSE
init-connect
init-file                                         (No default value)
init-rpl-role                                     MASTER
init-slave
innodb                                            ON
innodb-adaptive-flushing                          TRUE
innodb-adaptive-hash-index                        TRUE
innodb-additional-mem-pool-size                   8388608
innodb-autoextend-increment                       8
innodb-autoinc-lock-mode                          1
innodb-buffer-pool-instances                      1
innodb-buffer-pool-size                           134217728
innodb-change-buffering                           all
innodb-checksums                                  TRUE
innodb-cmp                                        ON
innodb-cmp-reset                                  ON
innodb-cmpmem                                     ON
innodb-cmpmem-reset                               ON
innodb-commit-concurrency                         0
innodb-concurrency-tickets                        500
innodb-data-file-path                             (No default value)
innodb-data-home-dir                              (No default value)
innodb-doublewrite                                TRUE
innodb-fast-shutdown                              1
innodb-file-format                                Antelope
innodb-file-format-check                          TRUE
innodb-file-format-max                            Antelope
innodb-file-io-threads                            4
innodb-file-per-table                             FALSE
innodb-flush-log-at-trx-commit                    1
innodb-flush-method                               (No default value)
innodb-force-recovery                             0
innodb-io-capacity                                200
innodb-large-prefix                               FALSE
innodb-lock-wait-timeout                          50
innodb-lock-waits                                 ON
innodb-locks                                      ON
innodb-locks-unsafe-for-binlog                    FALSE
innodb-log-buffer-size                            8388608
innodb-log-file-size                              5242880
innodb-log-files-in-group                         2
innodb-log-group-home-dir                         (No default value)
innodb-max-dirty-pages-pct                        75
innodb-max-purge-lag                              0
innodb-mirrored-log-groups                        1
innodb-old-blocks-pct                             37
innodb-old-blocks-time                            0
innodb-open-files                                 300
innodb-purge-batch-size                           20
innodb-purge-threads                              0
innodb-read-ahead-threshold                       56
innodb-read-io-threads                            4
innodb-replication-delay                          0
innodb-rollback-on-timeout                        FALSE
innodb-rollback-segments                          128
innodb-spin-wait-delay                            6
innodb-stats-method                               nulls_equal
innodb-stats-on-metadata                          TRUE
innodb-stats-sample-pages                         8
innodb-status-file                                FALSE
innodb-strict-mode                                FALSE
innodb-support-xa                                 TRUE
innodb-sync-spin-loops                            30
innodb-table-locks                                TRUE
innodb-thread-concurrency                         0
innodb-thread-sleep-delay                         10000
innodb-trx                                        ON
innodb-use-native-aio                             TRUE
innodb-use-sys-malloc                             TRUE
innodb-write-io-threads                           4
interactive-timeout                               28800
join-buffer-size                                  131072
keep-files-on-create                              FALSE
key-buffer-size                                   8388608
key-cache-age-threshold                           300
key-cache-block-size                              1024
key-cache-division-limit                          100
language                                          /home/jon/bin/mysql-5.5-cluster/share/
large-pages                                       FALSE
lc-messages                                       en_US
lc-messages-dir                                   /home/jon/bin/mysql-5.5-cluster/share/
lc-time-names                                     en_US
local-infile                                      TRUE
lock-wait-timeout                                 31536000
log                                               /home/jon/bin/mysql-5.5-cluster/data/torsk.log
log-bin                                           mister-pibb
log-bin-index                                     (No default value)
log-bin-trust-function-creators                   FALSE
log-error
log-isam                                          myisam.log
log-output                                        FILE
log-queries-not-using-indexes                     FALSE
log-short-format                                  FALSE
log-slave-updates                                 FALSE
log-slow-admin-statements                         FALSE
log-slow-queries                                  /home/jon/bin/mysql-5.5-cluster/data/torsk-slow.log
log-slow-slave-statements                         FALSE
log-tc                                            tc.log
log-tc-size                                       24576
log-warnings                                      1
long-query-time                                   10
low-priority-updates                              FALSE
lower-case-table-names                            0
master-info-file                                  master.info
master-retry-count                                86400
max-allowed-packet                                1048576
max-binlog-cache-size                             18446744073709547520
max-binlog-dump-events                            0
max-binlog-size                                   1073741824
max-binlog-stmt-cache-size                        18446744073709547520
max-connect-errors                                10
max-connections                                   151
max-delayed-threads                               20
max-error-count                                   64
max-heap-table-size                               16777216
max-join-size                                     18446744073709551615
max-length-for-sort-data                          1024
max-long-data-size                                1048576
max-prepared-stmt-count                           16382
max-relay-log-size                                0
max-seeks-for-key                                 18446744073709551615
max-sort-length                                   1024
max-sp-recursion-depth                            0
max-tmp-tables                                    32
max-user-connections                              0
max-write-lock-count                              18446744073709551615
memlock                                           FALSE
min-examined-row-limit                            0
multi-range-count                                 256
myisam-block-size                                 1024
myisam-data-pointer-size                          6
myisam-max-sort-file-size                         9223372036853727232
myisam-mmap-size                                  18446744073709551615
myisam-recover-options                            OFF
myisam-repair-threads                             1
myisam-sort-buffer-size                           8388608
myisam-stats-method                               nulls_unequal
myisam-use-mmap                                   FALSE
ndb-autoincrement-prefetch-sz                     1
ndb-batch-size                                    32768
ndb-blob-read-batch-bytes                         65536
ndb-blob-write-batch-bytes                        65536
ndb-cache-check-time                              0
ndb-cluster-connection-pool                       1
ndb-connectstring                                 (No default value)
ndb-deferred-constraints                          0
ndb-distribution                                  KEYHASH
ndb-extra-logging                                 1
ndb-force-send                                    TRUE
ndb-index-stat-cache-entries                      32
ndb-index-stat-enable                             TRUE
ndb-index-stat-option                             loop_checkon=1000ms,loop_idle=1000ms,loop_busy=100ms,update_batch=1,read_batch=4,idle_batch=32,check_batch=32,check_delay=1m,delete_batch=8,clean_delay=0,error_batch=4,error_delay=1m,evict_batch=8,evict_delay=1m,cache_limit=32M,cache_lowpct=90
ndb-index-stat-update-freq                        20
ndb-join-pushdown                                 TRUE
ndb-log-apply-status                              FALSE
ndb-log-bin                                       TRUE
ndb-log-binlog-index                              TRUE
ndb-log-empty-epochs                              FALSE
ndb-log-orig                                      FALSE
ndb-log-transaction-id                            FALSE
ndb-log-update-as-write                           TRUE
ndb-log-updated-only                              TRUE
ndb-mgmd-host                                     (No default value)
ndb-nodeid                                        0
ndb-optimization-delay                            10
ndb-optimized-node-selection                      3
ndb-report-thresh-binlog-epoch-slip               3
ndb-report-thresh-binlog-mem-usage                10
ndb-table-no-logging                              FALSE
ndb-table-temporary                               FALSE
ndb-use-copying-alter-table                       FALSE
ndb-use-exact-count                               FALSE
ndb-use-transactions                              TRUE
ndb-wait-connected                                0
ndb-wait-setup                                    15
ndbcluster                                        ON
ndbinfo                                           ON
ndbinfo-database                                  ndbinfo
ndbinfo-max-bytes                                 0
ndbinfo-max-rows                                  10
ndbinfo-show-hidden                               FALSE
ndbinfo-table-prefix                              ndb$
net-buffer-length                                 16384
net-read-timeout                                  30
net-retry-count                                   10
net-write-timeout                                 60
new                                               FALSE
old                                               FALSE
old-alter-table                                   FALSE
old-passwords                                     FALSE
old-style-user-limits                             FALSE
open-files-limit                                  1024
optimizer-prune-level                             1
optimizer-search-depth                            62
optimizer-switch                                  index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on
partition                                         ON
performance-schema                                FALSE
performance-schema-events-waits-history-long-size 10000
performance-schema-events-waits-history-size      10
performance-schema-max-cond-classes               80
performance-schema-max-cond-instances             1000
performance-schema-max-file-classes               50
performance-schema-max-file-handles               32768
performance-schema-max-file-instances             10000
performance-schema-max-mutex-classes              200
performance-schema-max-mutex-instances            1000000
performance-schema-max-rwlock-classes             30
performance-schema-max-rwlock-instances           1000000
performance-schema-max-table-handles              100000
performance-schema-max-table-instances            50000
performance-schema-max-thread-classes             50
performance-schema-max-thread-instances           1000
pid-file                                          /home/jon/bin/mysql-5.5-cluster/data/torsk.pid
plugin-dir                                        /home/jon/bin/mysql-5.5-cluster/lib/plugin
plugin-load                                       (No default value)
port                                              3306
port-open-timeout                                 0
preload-buffer-size                               32768
profiling-history-size                            15
query-alloc-block-size                            8192
query-cache-limit                                 1048576
query-cache-min-res-unit                          4096
query-cache-size                                  0
query-cache-type                                  ON
query-cache-wlock-invalidate                      FALSE
query-prealloc-size                               8192
range-alloc-block-size                            4096
read-buffer-size                                  131072
read-only                                         FALSE
read-rnd-buffer-size                              262144
relay-log                                         (No default value)
relay-log-index                                   (No default value)
relay-log-info-file                               relay-log.info
relay-log-purge                                   TRUE
relay-log-recovery                                FALSE
relay-log-space-limit                             0
replicate-same-server-id                          FALSE
report-host                                       (No default value)
report-password                                   (No default value)
report-port                                       3306
report-user                                       (No default value)
rpl-recovery-rank                                 0
safe-user-create                                  FALSE
secure-auth                                       FALSE
secure-file-priv                                  (No default value)
server-id                                         1
server-id-bits                                    32
show-slave-auth-info                              FALSE
skip-grant-tables                                 FALSE
skip-name-resolve                                 FALSE
skip-networking                                   FALSE
skip-show-database                                FALSE
skip-slave-start                                  FALSE
slave-allow-batching                              FALSE
slave-compressed-protocol                         FALSE
slave-exec-mode                                   STRICT
slave-load-tmpdir                                 /tmp
slave-net-timeout                                 3600
slave-skip-errors                                 (No default value)
slave-transaction-retries                         10
slave-type-conversions
slow-launch-time                                  2
slow-query-log                                    FALSE
slow-query-log-file                               /home/jon/bin/mysql-5.5-cluster/data/torsk-slow.log
socket                                            /tmp/mysql.sock
sort-buffer-size                                  2097152
sporadic-binlog-dump-fail                         FALSE
sql-mode
ssl                                               FALSE
ssl-ca                                            (No default value)
ssl-capath                                        (No default value)
ssl-cert                                          (No default value)
ssl-cipher                                        (No default value)
ssl-key                                           (No default value)
super-large-pages                                 FALSE
symbolic-links                                    TRUE
sync-binlog                                       0
sync-frm                                          TRUE
sync-master-info                                  0
sync-relay-log                                    0
sync-relay-log-info                               0
sysdate-is-now                                    FALSE
table-cache                                       400
table-definition-cache                            400
table-open-cache                                  400
tc-heuristic-recover                              COMMIT
temp-pool                                         TRUE
thread-cache-size                                 0
thread-concurrency                                10
thread-handling                                   one-thread-per-connection
thread-stack                                      262144
time-format                                       %H:%i:%s
timed-mutexes                                     FALSE
tmp-table-size                                    16777216
tmpdir                                            /tmp
transaction-alloc-block-size                      8192
transaction-isolation                             REPEATABLE-READ
transaction-prealloc-size                         4096
updatable-views-with-limit                        YES
verbose                                           TRUE
wait-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 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 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 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.3.15.1, “InnoDB Performance Tuning Tips”.