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                                                   80
basedir                                                    /home/jon/bin/mysql-5.6/
big-tables                                                 FALSE
bind-address                                               *
binlog-cache-size                                          32768
binlog-checksum                                            CRC32
binlog-direct-non-transactional-updates                    FALSE
binlog-format                                              STATEMENT
binlog-max-flush-queue-time                                0
binlog-order-commits                                       TRUE
binlog-row-event-max-size                                  8192
binlog-row-image                                           FULL
binlog-rows-query-log-events                               FALSE
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.6/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-tmp-storage-engine                                 InnoDB
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-on-expired-password                             TRUE
disconnect-slave-event-count                               0
div-precision-increment                                    4
end-markers-in-json                                        FALSE
enforce-gtid-consistency                                   FALSE
eq-range-index-dive-limit                                  10
event-scheduler                                            OFF
expire-logs-days                                           0
explicit-defaults-for-timestamp                            FALSE
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.6/data/havskatt.log
group-concat-max-len                                       1024
gtid-mode                                                  OFF
help                                                       TRUE
host-cache-size                                            279
ignore-builtin-innodb                                      FALSE
init-connect                                               
init-file                                                  (No default value)
init-slave                                                 
innodb                                                     ON
innodb-adaptive-flushing                                   TRUE
innodb-adaptive-flushing-lwm                               10
innodb-adaptive-hash-index                                 TRUE
innodb-adaptive-max-sleep-delay                            150000
innodb-additional-mem-pool-size                            8388608
innodb-api-bk-commit-interval                              5
innodb-api-disable-rowlock                                 FALSE
innodb-api-enable-binlog                                   FALSE
innodb-api-enable-mdl                                      FALSE
innodb-api-trx-level                                       0
innodb-autoextend-increment                                64
innodb-autoinc-lock-mode                                   1
innodb-buffer-page                                         ON
innodb-buffer-page-lru                                     ON
innodb-buffer-pool-dump-at-shutdown                        FALSE
innodb-buffer-pool-dump-now                                FALSE
innodb-buffer-pool-filename                                ib_buffer_pool
innodb-buffer-pool-instances                               0
innodb-buffer-pool-load-abort                              FALSE
innodb-buffer-pool-load-at-startup                         FALSE
innodb-buffer-pool-load-now                                FALSE
innodb-buffer-pool-size                                    134217728
innodb-buffer-pool-stats                                   ON
innodb-change-buffer-max-size                              25
innodb-change-buffering                                    all
innodb-checksum-algorithm                                  innodb
innodb-checksums                                           TRUE
innodb-cmp                                                 ON
innodb-cmp-per-index                                       ON
innodb-cmp-per-index-enabled                               FALSE
innodb-cmp-per-index-reset                                 ON
innodb-cmp-reset                                           ON
innodb-cmpmem                                              ON
innodb-cmpmem-reset                                        ON
innodb-commit-concurrency                                  0
innodb-compression-failure-threshold-pct                   5
innodb-compression-level                                   6
innodb-compression-pad-pct-max                             50
innodb-concurrency-tickets                                 5000
innodb-data-file-path                                      (No default value)
innodb-data-home-dir                                       (No default value)
innodb-disable-sort-file-cache                             FALSE
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                                      TRUE
innodb-flush-log-at-timeout                                1
innodb-flush-log-at-trx-commit                             1
innodb-flush-method                                        (No default value)
innodb-flush-neighbors                                     1
innodb-flushing-avg-loops                                  30
innodb-force-load-corrupted                                FALSE
innodb-force-recovery                                      0
innodb-ft-aux-table                                        (No default value)
innodb-ft-being-deleted                                    ON
innodb-ft-cache-size                                       8000000
innodb-ft-config                                           ON
innodb-ft-default-stopword                                 ON
innodb-ft-deleted                                          ON
innodb-ft-enable-diag-print                                FALSE
innodb-ft-enable-stopword                                  TRUE
innodb-ft-index-cache                                      ON
innodb-ft-index-table                                      ON
innodb-ft-inserted                                         ON
innodb-ft-max-token-size                                   84
innodb-ft-min-token-size                                   3
innodb-ft-num-word-optimize                                2000
innodb-ft-server-stopword-table                            (No default value)
innodb-ft-sort-pll-degree                                  2
innodb-ft-user-stopword-table                              (No default value)
innodb-io-capacity                                         200
innodb-io-capacity-max                                     18446744073709551615
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-compressed-pages                                TRUE
innodb-log-file-size                                       50331648
innodb-log-files-in-group                                  2
innodb-log-group-home-dir                                  (No default value)
innodb-lru-scan-depth                                      1024
innodb-max-dirty-pages-pct                                 75
innodb-max-dirty-pages-pct-lwm                             0
innodb-max-purge-lag                                       0
innodb-max-purge-lag-delay                                 0
innodb-metrics                                             ON
innodb-mirrored-log-groups                                 1
innodb-monitor-disable                                     (No default value)
innodb-monitor-enable                                      (No default value)
innodb-monitor-reset                                       (No default value)
innodb-monitor-reset-all                                   (No default value)
innodb-old-blocks-pct                                      37
innodb-old-blocks-time                                     1000
innodb-online-alter-log-max-size                           134217728
innodb-open-files                                          0
innodb-optimize-fulltext-only                              FALSE
innodb-page-size                                           16384
innodb-print-all-deadlocks                                 FALSE
innodb-purge-batch-size                                    300
innodb-purge-threads                                       1
innodb-random-read-ahead                                   FALSE
innodb-read-ahead-threshold                                56
innodb-read-io-threads                                     4
innodb-read-only                                           FALSE
innodb-replication-delay                                   0
innodb-rollback-on-timeout                                 FALSE
innodb-rollback-segments                                   128
innodb-sort-buffer-size                                    1048576
innodb-spin-wait-delay                                     6
innodb-stats-auto-recalc                                   TRUE
innodb-stats-method                                        nulls_equal
innodb-stats-on-metadata                                   FALSE
innodb-stats-persistent                                    TRUE
innodb-stats-persistent-sample-pages                       20
innodb-stats-sample-pages                                  8
innodb-stats-transient-sample-pages                        8
innodb-status-file                                         FALSE
innodb-strict-mode                                         FALSE
innodb-support-xa                                          TRUE
innodb-sync-array-size                                     1
innodb-sync-spin-loops                                     30
innodb-sys-columns                                         ON
innodb-sys-datafiles                                       ON
innodb-sys-fields                                          ON
innodb-sys-foreign                                         ON
innodb-sys-foreign-cols                                    ON
innodb-sys-indexes                                         ON
innodb-sys-tables                                          ON
innodb-sys-tablespaces                                     ON
innodb-sys-tablestats                                      ON
innodb-table-locks                                         TRUE
innodb-thread-concurrency                                  0
innodb-thread-sleep-delay                                  10000
innodb-trx                                                 ON
innodb-undo-directory                                      .
innodb-undo-logs                                           128
innodb-undo-tablespaces                                    0
innodb-use-native-aio                                      TRUE
innodb-use-sys-malloc                                      TRUE
innodb-write-io-threads                                    4
interactive-timeout                                        28800
join-buffer-size                                           262144
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.6/share/
large-pages                                                FALSE
lc-messages                                                en_US
lc-messages-dir                                            /home/jon/bin/mysql-5.6/share/
lc-time-names                                              en_US
local-infile                                               TRUE
lock-wait-timeout                                          31536000
log-bin                                                    (No default value)
log-bin-index                                              (No default value)
log-bin-trust-function-creators                            FALSE
log-bin-use-v1-row-events                                  FALSE
log-error                                                  
log-isam                                                   myisam.log
log-output                                                 FILE
log-queries-not-using-indexes                              FALSE
log-raw                                                    FALSE
log-short-format                                           FALSE
log-slave-updates                                          FALSE
log-slow-admin-statements                                  FALSE
log-slow-slave-statements                                  FALSE
log-tc                                                     tc.log
log-tc-size                                                24576
log-throttle-queries-not-using-indexes                     0
log-warnings                                               1
long-query-time                                            10
low-priority-updates                                       FALSE
lower-case-table-names                                     0
master-info-file                                           master.info
master-info-repository                                     FILE
master-retry-count                                         86400
master-verify-checksum                                     FALSE
max-allowed-packet                                         4194304
max-binlog-cache-size                                      18446744073709547520
max-binlog-dump-events                                     0
max-binlog-size                                            1073741824
max-binlog-stmt-cache-size                                 18446744073709547520
max-connect-errors                                         100
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-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
metadata-locks-cache-size                                  1024
metadata-locks-hash-instances                              8
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
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                                              0
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,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
optimizer-trace                                            
optimizer-trace-features                                   greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
optimizer-trace-limit                                      1
optimizer-trace-max-mem-size                               16384
optimizer-trace-offset                                     -1
partition                                                  ON
performance-schema                                         TRUE
performance-schema-accounts-size                           -1
performance-schema-consumer-events-stages-current          FALSE
performance-schema-consumer-events-stages-history          FALSE
performance-schema-consumer-events-stages-history-long     FALSE
performance-schema-consumer-events-statements-current      TRUE
performance-schema-consumer-events-statements-history      FALSE
performance-schema-consumer-events-statements-history-long FALSE
performance-schema-consumer-events-waits-current           FALSE
performance-schema-consumer-events-waits-history           FALSE
performance-schema-consumer-events-waits-history-long      FALSE
performance-schema-consumer-global-instrumentation         TRUE
performance-schema-consumer-statements-digest              TRUE
performance-schema-consumer-thread-instrumentation         TRUE
performance-schema-digests-size                            -1
performance-schema-events-stages-history-long-size         -1
performance-schema-events-stages-history-size              -1
performance-schema-events-statements-history-long-size     -1
performance-schema-events-statements-history-size          -1
performance-schema-events-waits-history-long-size          -1
performance-schema-events-waits-history-size               -1
performance-schema-hosts-size                              -1
performance-schema-instrument                              
performance-schema-max-cond-classes                        80
performance-schema-max-cond-instances                      -1
performance-schema-max-file-classes                        50
performance-schema-max-file-handles                        32768
performance-schema-max-file-instances                      -1
performance-schema-max-mutex-classes                       200
performance-schema-max-mutex-instances                     -1
performance-schema-max-rwlock-classes                      30
performance-schema-max-rwlock-instances                    -1
performance-schema-max-socket-classes                      10
performance-schema-max-socket-instances                    -1
performance-schema-max-stage-classes                       150
performance-schema-max-statement-classes                   167
performance-schema-max-table-handles                       -1
performance-schema-max-table-instances                     -1
performance-schema-max-thread-classes                      50
performance-schema-max-thread-instances                    -1
performance-schema-session-connect-attrs-size              -1
performance-schema-setup-actors-size                       100
performance-schema-setup-objects-size                      100
performance-schema-users-size                              -1
pid-file                                                   /home/jon/bin/mysql-5.6/data/havskatt.pid
plugin-dir                                                 /home/jon/bin/mysql-5.6/lib/plugin/
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                                           1048576
query-cache-type                                           OFF
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-info-repository                                  FILE
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                                                0
report-user                                                (No default value)
safe-user-create                                           FALSE
secure-auth                                                TRUE
secure-file-priv                                           (No default value)
server-id                                                  0
server-id-bits                                             32
sha256-password-private-key-path                           private_key.pem
sha256-password-public-key-path                            public_key.pem
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-checkpoint-group                                     512
slave-checkpoint-period                                    300
slave-compressed-protocol                                  FALSE
slave-exec-mode                                            STRICT
slave-load-tmpdir                                          /tmp
slave-max-allowed-packet                                   1073741824
slave-net-timeout                                          3600
slave-parallel-workers                                     0
slave-pending-jobs-size-max                                16777216
slave-rows-search-algorithms                               TABLE_SCAN,INDEX_SCAN
slave-skip-errors                                          (No default value)
slave-sql-verify-checksum                                  TRUE
slave-transaction-retries                                  10
slave-type-conversions                                     
slow-launch-time                                           2
slow-query-log                                             FALSE
slow-query-log-file                                        /home/jon/bin/mysql-5.6/data/havskatt-slow.log
socket                                                     /tmp/mysql.sock
sort-buffer-size                                           262144
sporadic-binlog-dump-fail                                  FALSE
sql-mode                                                   NO_ENGINE_SUBSTITUTION
ssl                                                        FALSE
ssl-ca                                                     (No default value)
ssl-capath                                                 (No default value)
ssl-cert                                                   (No default value)
ssl-cipher                                                 (No default value)
ssl-crl                                                    (No default value)
ssl-crlpath                                                (No default value)
ssl-key                                                    (No default value)
stored-program-cache                                       256
super-large-pages                                          FALSE
symbolic-links                                             TRUE
sync-binlog                                                0
sync-frm                                                   TRUE
sync-master-info                                           10000
sync-relay-log                                             10000
sync-relay-log-info                                        10000
sysdate-is-now                                             FALSE
table-definition-cache                                     615
table-open-cache                                           431
table-open-cache-instances                                 1
tc-heuristic-recover                                       COMMIT
temp-pool                                                  TRUE
thread-cache-size                                          9
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
transaction-read-only                                      FALSE
updatable-views-with-limit                                 YES
verbose                                                    TRUE
wait-timeout

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 8.5, “Optimizing for InnoDB Tables”.