MySQL 5.6 Reference Manual :: 8 Optimization :: 8.11 Optimizing the MySQL Server :: 8.11.2 Tuning Server Parameters
Section Navigation [Toggle]
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 variablesshell>mysqladmin extended-status
For a full description of all system and status variables, see Section 5.1.4, “Server System Variables”, and Section 5.1.6, “Server Status Variables”.
MySQL uses algorithms that are very scalable, so you can usually run with very little memory. However, normally you get better performance by giving MySQL more memory.
When tuning a MySQL server, the two most important variables to
configure are key_buffer_size
and table_open_cache. You
should first feel confident that you have these set
appropriately before trying to change any other variables.
The following examples indicate some typical variable values for different runtime configurations.
If you have at least 256MB of memory and many tables and want maximum performance with a moderate number of clients, use something like this:
shell>
mysqld_safe --key_buffer_size=64M --table_open_cache=256 \--sort_buffer_size=4M --read_buffer_size=1M &If you have only 128MB of memory and only a few tables, but you still do a lot of sorting, you can use something like this:
shell>
mysqld_safe --key_buffer_size=16M --sort_buffer_size=1MIf there are very many simultaneous connections, swapping problems may occur unless mysqld has been configured to use very little memory for each connection. mysqld performs better if you have enough memory for all connections.
With little memory and lots of connections, use something like this:
shell>
mysqld_safe --key_buffer_size=512K --sort_buffer_size=100K \--read_buffer_size=100K &Or even this:
shell>
mysqld_safe --key_buffer_size=512K --sort_buffer_size=16K \--table_open_cache=32 --read_buffer_size=8K \--net_buffer_length=1K &
If you are performing GROUP BY or
ORDER BY operations on tables that are much
larger than your available memory, increase the value of
read_rnd_buffer_size to speed
up the reading of rows following sorting operations.
You can make use of the example option files included with your MySQL distribution; see Section 5.1.2, “Server Configuration Defaults”.
If you specify an option on the command line for mysqld or mysqld_safe, it remains in effect only for that invocation of the server. To use the option every time the server runs, put it in an option file.
To see the effects of a parameter change, do something like this:
shell> mysqld --key_buffer_size=32M --verbose --help
The variable values are listed near the end of the output. Make
sure that the --verbose and
--help options are last.
Otherwise, the effect of any options listed after them on the
command line are not reflected in the output.
For information on tuning the InnoDB storage
engine, see Section 14.2.4.1, “InnoDB Performance Tuning Tips”.
Copyright © 1997, 2013, Oracle and/or its affiliates. All
rights reserved.
Legal Notices
User Comments
Add your own comment.