Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 33.8Mb
PDF (A4) - 34.0Mb
PDF (RPM) - 33.2Mb
EPUB - 8.5Mb
HTML Download (TGZ) - 8.2Mb
HTML Download (Zip) - 8.2Mb
HTML Download (RPM) - 7.1Mb
Eclipse Doc Plugin (TGZ) - 9.0Mb
Eclipse Doc Plugin (Zip) - 11.1Mb
Man Pages (TGZ) - 219.4Kb
Man Pages (Zip) - 322.3Kb
Info (Gzip) - 3.2Mb
Info (Zip) - 3.2Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Tuning Server Parameters

8.12.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
avoid-temporal-upgrade                                       FALSE
back-log                                                     80
basedir                                                      /home/jon/bin/mysql-5.7/
big-tables                                                   FALSE
bind-address                                                 *
binlog-cache-size                                            32768
binlog-checksum                                              CRC32
binlog-direct-non-transactional-updates                      FALSE
binlog-error-action                                          ABORT_SERVER
binlog-format                                                ROW
binlog-group-commit-sync-delay                               0
binlog-group-commit-sync-no-delay-count                      0
binlog-gtid-simple-recovery                                  TRUE
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
block-encryption-mode                                        aes-128-ecb
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.7/share/charsets/
check-proxy-users                                            FALSE
chroot                                                       (No default value)
collation-server                                             latin1_swedish_ci
completion-type                                              NO_CHAIN
concurrent-insert                                            AUTO
connect-timeout                                              10
console                                                      FALSE
daemonize                                                    FALSE
datadir                                                      /home/jon/bin/mysql-5.7/data/
date-format                                                  %Y-%m-%d
datetime-format                                              %Y-%m-%d %H:%i:%s
default-authentication-plugin                                mysql_native_password
default-password-lifetime                                    360
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)
disabled-storage-engines                                     
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                                    200
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.7/data/valhaj.log
group-concat-max-len                                         1024
gtid-executed-compression-period                             1000
gtid-mode                                                    OFF
help                                                         TRUE
host-cache-size                                              279
ignore-builtin-innodb                                        FALSE
init-connect                                                 
init-file                                                    (No default value)
init-slave                                                   
initialize                                                   FALSE
initialize-insecure                                          FALSE
innodb-adaptive-flushing                                     TRUE
innodb-adaptive-flushing-lwm                                 10
innodb-adaptive-hash-index                                   TRUE
innodb-adaptive-hash-index-parts                             8
innodb-adaptive-max-sleep-delay                              150000
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-pool-chunk-size                                134217728
innodb-buffer-pool-dump-at-shutdown                          TRUE
innodb-buffer-pool-dump-now                                  FALSE
innodb-buffer-pool-dump-pct                                  25
innodb-buffer-pool-filename                                  ib_buffer_pool
innodb-buffer-pool-instances                                 0
innodb-buffer-pool-load-abort                                FALSE
innodb-buffer-pool-load-at-startup                           TRUE
innodb-buffer-pool-load-now                                  FALSE
innodb-buffer-pool-size                                      134217728
innodb-change-buffer-max-size                                25
innodb-change-buffering                                      all
innodb-checksum-algorithm                                    crc32
innodb-checksums                                             TRUE
innodb-cmp-per-index-enabled                                 FALSE
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-default-row-format                                    dynamic
innodb-disable-sort-file-cache                               FALSE
innodb-doublewrite                                           TRUE
innodb-fast-shutdown                                         1
innodb-file-format                                           Barracuda
innodb-file-format-check                                     TRUE
innodb-file-format-max                                       Antelope
innodb-file-per-table                                        TRUE
innodb-fill-factor                                           100
innodb-flush-log-at-timeout                                  1
innodb-flush-log-at-trx-commit                               1
innodb-flush-method                                          (No default value)
innodb-flush-neighbors                                       1
innodb-flush-sync                                            TRUE
innodb-flushing-avg-loops                                    30
innodb-force-load-corrupted                                  FALSE
innodb-force-recovery                                        0
innodb-ft-aux-table                                          (No default value)
innodb-ft-cache-size                                         8000000
innodb-ft-enable-diag-print                                  FALSE
innodb-ft-enable-stopword                                    TRUE
innodb-ft-max-token-size                                     84
innodb-ft-min-token-size                                     3
innodb-ft-num-word-optimize                                  2000
innodb-ft-result-cache-limit                                 2000000000
innodb-ft-server-stopword-table                              (No default value)
innodb-ft-sort-pll-degree                                    2
innodb-ft-total-cache-size                                   640000000
innodb-ft-user-stopword-table                                (No default value)
innodb-io-capacity                                           200
innodb-io-capacity-max                                       18446744073709551615
innodb-large-prefix                                          TRUE
innodb-lock-wait-timeout                                     50
innodb-locks-unsafe-for-binlog                               FALSE
innodb-log-buffer-size                                       16777216
innodb-log-checksums                                         TRUE
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-log-write-ahead-size                                  8192
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-max-undo-log-size                                     1073741824
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-cleaners                                         4
innodb-page-size                                             16384
innodb-print-all-deadlocks                                   FALSE
innodb-purge-batch-size                                      300
innodb-purge-rseg-truncate-frequency                         128
innodb-purge-threads                                         4
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-status-output                                         FALSE
innodb-status-output-locks                                   FALSE
innodb-strict-mode                                           TRUE
innodb-support-xa                                            TRUE
innodb-sync-array-size                                       1
innodb-sync-spin-loops                                       30
innodb-table-locks                                           TRUE
innodb-temp-data-file-path                                   (No default value)
innodb-thread-concurrency                                    0
innodb-thread-sleep-delay                                    10000
innodb-undo-directory                                        (No default value)
innodb-undo-log-truncate                                     FALSE
innodb-undo-logs                                             128
innodb-undo-tablespaces                                      0
innodb-use-native-aio                                        TRUE
innodb-write-io-threads                                      4
interactive-timeout                                          28800
internal-tmp-disk-storage-engine                             InnoDB
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.7/share/
large-pages                                                  FALSE
lc-messages                                                  en_US
lc-messages-dir                                              /home/jon/bin/mysql-5.7/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-builtin-as-identified-by-password                        FALSE
log-error                                                    stderr
log-error-verbosity                                          1
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-syslog                                                   FALSE
log-syslog-facility                                          daemon
log-syslog-include-pid                                       TRUE
log-syslog-tag                                               
log-tc                                                       tc.log
log-tc-size                                                  24576
log-throttle-queries-not-using-indexes                       0
log-timestamps                                               UTC
log-warnings                                                 0
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-digest-length                                            1024
max-error-count                                              64
max-execution-time                                           0
max-heap-table-size                                          16777216
max-join-size                                                18446744073709551615
max-length-for-sort-data                                     1024
max-points-in-geometry                                       65536
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
mysql-native-password-proxy-users                            FALSE
ndb-allow-copying-alter-table                                TRUE
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-clear-apply-status                                       TRUE
ndb-cluster-connection-pool                                  1
ndb-cluster-connection-pool-nodeids                          (No default value)
ndb-connectstring                                            (No default value)
ndb-deferred-constraints                                     0
ndb-distribution                                             KEYHASH
ndb-eventbuffer-free-percent                                 20
ndb-eventbuffer-max-alloc                                    0
ndb-extra-logging                                            1
ndb-force-send                                               TRUE
ndb-index-stat-enable                                        TRUE
ndb-index-stat-option                                        loop_enable=1000ms,loop_idle=1000ms,loop_busy=100ms,update_batch=1,read_batch=4,idle_batch=32,check_batch=8,check_delay=10m,delete_batch=8,clean_delay=1m,error_batch=4,error_delay=1m,evict_batch=8,evict_delay=1m,cache_limit=32M,cache_lowpct=90,zero_total=0
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-empty-update                                         FALSE
ndb-log-exclusive-reads                                      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-recv-thread-activation-threshold                         8
ndb-recv-thread-cpu-mask                                     
ndb-report-thresh-binlog-epoch-slip                          3
ndb-report-thresh-binlog-mem-usage                           10
ndb-show-foreign-key-mock-tables                             FALSE
ndb-slave-conflict-role                                      NONE
ndb-table-no-logging                                         FALSE
ndb-table-temporary                                          FALSE
ndb-transid-mysql-connection-map                             ON
ndb-use-copying-alter-table                                  FALSE
ndb-use-exact-count                                          FALSE
ndb-use-transactions                                         TRUE
ndb-wait-connected                                           30
ndb-wait-setup                                               30
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
ngram                                                        ON
ngram-token-size                                             2
offline-mode                                                 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,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=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        TRUE
performance-schema-consumer-events-statements-history-long   FALSE
performance-schema-consumer-events-transactions-current      FALSE
performance-schema-consumer-events-transactions-history      FALSE
performance-schema-consumer-events-transactions-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-transactions-history-long-size     -1
performance-schema-events-transactions-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-digest-length                         1024
performance-schema-max-file-classes                          80
performance-schema-max-file-handles                          32768
performance-schema-max-file-instances                        -1
performance-schema-max-index-stat                            -1
performance-schema-max-memory-classes                        320
performance-schema-max-metadata-locks                        -1
performance-schema-max-mutex-classes                         200
performance-schema-max-mutex-instances                       -1
performance-schema-max-prepared-statements-instances         -1
performance-schema-max-program-instances                     -1
performance-schema-max-rwlock-classes                        40
performance-schema-max-rwlock-instances                      -1
performance-schema-max-socket-classes                        10
performance-schema-max-socket-instances                      -1
performance-schema-max-sql-text-length                       1024
performance-schema-max-stage-classes                         150
performance-schema-max-statement-classes                     192
performance-schema-max-statement-stack                       10
performance-schema-max-table-handles                         -1
performance-schema-max-table-instances                       -1
performance-schema-max-table-lock-stat                       -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                         -1
performance-schema-setup-objects-size                        -1
performance-schema-users-size                                -1
pid-file                                                     /home/jon/bin/mysql-5.7/data/valhaj.pid
plugin-dir                                                   /home/jon/bin/mysql-5.7/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
range-optimizer-max-mem-size                                 1536000
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)
require-secure-transport                                     FALSE
rpl-stop-slave-timeout                                       31536000
safe-user-create                                             FALSE
secure-auth                                                  TRUE
secure-file-priv                                             
server-id                                                    0
server-id-bits                                               32
session-track-gtids                                          OFF
session-track-schema                                         TRUE
session-track-state-change                                   FALSE
session-track-system-variables                               time_zone,autocommit,character_set_client,character_set_results,character_set_connection
session-track-transaction-info                               OFF
sha256-password-proxy-users                                  FALSE
show-compatibility-56                                        FALSE
show-old-temporals                                           FALSE
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                                            60
slave-parallel-type                                          DATABASE
slave-parallel-workers                                       0
slave-pending-jobs-size-max                                  16777216
slave-preserve-commit-order                                  FALSE
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.7/data/valhaj-slow.log
socket                                                       /tmp/mysql.sock
sort-buffer-size                                             262144
sporadic-binlog-dump-fail                                    FALSE
sql-mode                                                     ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
ssl                                                          TRUE
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
super-read-only                                              FALSE
symbolic-links                                               TRUE
sync-binlog                                                  1
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                                   16
tc-heuristic-recover                                         OFF
temp-pool                                                    TRUE
thread-cache-size                                            9
thread-handling                                              one-thread-per-connection
thread-stack                                                 262144
time-format                                                  %H:%i:%s
tls-version                                                  TLSv1,TLSv1.1
tmp-table-size                                               16777216
tmpdir                                                       /tmp
transaction-alloc-block-size                                 8192
transaction-isolation                                        REPEATABLE-READ
transaction-prealloc-size                                    4096
transaction-read-only                                        FALSE
transaction-write-set-extraction                             OFF
updatable-views-with-limit                                   YES
validate-user-plugins                                        TRUE
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 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 optimizing the InnoDB storage engine performance, see Section 8.5, “Optimizing for InnoDB Tables”.


User Comments
Sign Up Login You must be logged in to post a comment.