Spec-Zone .ru
спецификации, руководства, описания, API

8.11.2. Tuning Server Parameters

You can determine the default buffer sizes used by the mysqld server using this command:

shell> mysqld --verbose --help

This command produces a list of all mysqld options and configurable system variables. The output includes the default variable values and looks something like this:

abort-slave-event-count                                    0allow-suspicious-udfs                                      FALSEarchive                                                    ONauto-increment-increment                                   1auto-increment-offset                                      1autocommit                                                 TRUEautomatic-sp-privileges                                    TRUEback-log                                                   80basedir                                                    /home/jon/bin/mysql-5.6/big-tables                                                 FALSEbind-address                                               *binlog-cache-size                                          32768binlog-checksum                                            CRC32binlog-direct-non-transactional-updates                    FALSEbinlog-format                                              STATEMENTbinlog-max-flush-queue-time                                0binlog-order-commits                                       TRUEbinlog-row-event-max-size                                  8192binlog-row-image                                           FULLbinlog-rows-query-log-events                               FALSEbinlog-stmt-cache-size                                     32768blackhole                                                  ONbulk-insert-buffer-size                                    8388608character-set-client-handshake                             TRUEcharacter-set-filesystem                                   binarycharacter-set-server                                       latin1character-sets-dir                                         /home/jon/bin/mysql-5.6/share/charsets/chroot                                                     (No default value)collation-server                                           latin1_swedish_cicompletion-type                                            NO_CHAINconcurrent-insert                                          AUTOconnect-timeout                                            10console                                                    FALSEdatadir                                                    (No default value)date-format                                                %Y-%m-%ddatetime-format                                            %Y-%m-%d %H:%i:%sdefault-storage-engine                                     InnoDBdefault-time-zone                                          (No default value)default-tmp-storage-engine                                 InnoDBdefault-week-format                                        0delay-key-write                                            ONdelayed-insert-limit                                       100delayed-insert-timeout                                     300delayed-queue-size                                         1000des-key-file                                               (No default value)disconnect-on-expired-password                             TRUEdisconnect-slave-event-count                               0div-precision-increment                                    4end-markers-in-json                                        FALSEenforce-gtid-consistency                                   FALSEeq-range-index-dive-limit                                  10event-scheduler                                            OFFexpire-logs-days                                           0explicit-defaults-for-timestamp                            FALSEexternal-locking                                           FALSEflush                                                      FALSEflush-time                                                 0ft-boolean-syntax                                          + -><()~*:""&|ft-max-word-len                                            84ft-min-word-len                                            4ft-query-expansion-limit                                   20ft-stopword-file                                           (No default value)gdb                                                        FALSEgeneral-log                                                FALSEgeneral-log-file                                           /home/jon/bin/mysql-5.6/data/havskatt.loggroup-concat-max-len                                       1024gtid-mode                                                  OFFhelp                                                       TRUEhost-cache-size                                            279ignore-builtin-innodb                                      FALSEinit-connect                                               init-file                                                  (No default value)init-slave                                                 innodb                                                     ONinnodb-adaptive-flushing                                   TRUEinnodb-adaptive-flushing-lwm                               10innodb-adaptive-hash-index                                 TRUEinnodb-adaptive-max-sleep-delay                            150000innodb-additional-mem-pool-size                            8388608innodb-api-bk-commit-interval                              5innodb-api-disable-rowlock                                 FALSEinnodb-api-enable-binlog                                   FALSEinnodb-api-enable-mdl                                      FALSEinnodb-api-trx-level                                       0innodb-autoextend-increment                                64innodb-autoinc-lock-mode                                   1innodb-buffer-page                                         ONinnodb-buffer-page-lru                                     ONinnodb-buffer-pool-dump-at-shutdown                        FALSEinnodb-buffer-pool-dump-now                                FALSEinnodb-buffer-pool-filename                                ib_buffer_poolinnodb-buffer-pool-instances                               0innodb-buffer-pool-load-abort                              FALSEinnodb-buffer-pool-load-at-startup                         FALSEinnodb-buffer-pool-load-now                                FALSEinnodb-buffer-pool-size                                    134217728innodb-buffer-pool-stats                                   ONinnodb-change-buffer-max-size                              25innodb-change-buffering                                    allinnodb-checksum-algorithm                                  innodbinnodb-checksums                                           TRUEinnodb-cmp                                                 ONinnodb-cmp-per-index                                       ONinnodb-cmp-per-index-enabled                               FALSEinnodb-cmp-per-index-reset                                 ONinnodb-cmp-reset                                           ONinnodb-cmpmem                                              ONinnodb-cmpmem-reset                                        ONinnodb-commit-concurrency                                  0innodb-compression-failure-threshold-pct                   5innodb-compression-level                                   6innodb-compression-pad-pct-max                             50innodb-concurrency-tickets                                 5000innodb-data-file-path                                      (No default value)innodb-data-home-dir                                       (No default value)innodb-disable-sort-file-cache                             FALSEinnodb-doublewrite                                         TRUEinnodb-fast-shutdown                                       1innodb-file-format                                         Antelopeinnodb-file-format-check                                   TRUEinnodb-file-format-max                                     Antelopeinnodb-file-io-threads                                     4innodb-file-per-table                                      TRUEinnodb-flush-log-at-timeout                                1innodb-flush-log-at-trx-commit                             1innodb-flush-method                                        (No default value)innodb-flush-neighbors                                     1innodb-flushing-avg-loops                                  30innodb-force-load-corrupted                                FALSEinnodb-force-recovery                                      0innodb-ft-aux-table                                        (No default value)innodb-ft-being-deleted                                    ONinnodb-ft-cache-size                                       8000000innodb-ft-config                                           ONinnodb-ft-default-stopword                                 ONinnodb-ft-deleted                                          ONinnodb-ft-enable-diag-print                                FALSEinnodb-ft-enable-stopword                                  TRUEinnodb-ft-index-cache                                      ONinnodb-ft-index-table                                      ONinnodb-ft-inserted                                         ONinnodb-ft-max-token-size                                   84innodb-ft-min-token-size                                   3innodb-ft-num-word-optimize                                2000innodb-ft-server-stopword-table                            (No default value)innodb-ft-sort-pll-degree                                  2innodb-ft-user-stopword-table                              (No default value)innodb-io-capacity                                         200innodb-io-capacity-max                                     18446744073709551615innodb-large-prefix                                        FALSEinnodb-lock-wait-timeout                                   50innodb-lock-waits                                          ONinnodb-locks                                               ONinnodb-locks-unsafe-for-binlog                             FALSEinnodb-log-buffer-size                                     8388608innodb-log-compressed-pages                                TRUEinnodb-log-file-size                                       50331648innodb-log-files-in-group                                  2innodb-log-group-home-dir                                  (No default value)innodb-lru-scan-depth                                      1024innodb-max-dirty-pages-pct                                 75innodb-max-dirty-pages-pct-lwm                             0innodb-max-purge-lag                                       0innodb-max-purge-lag-delay                                 0innodb-metrics                                             ONinnodb-mirrored-log-groups                                 1innodb-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                                      37innodb-old-blocks-time                                     1000innodb-online-alter-log-max-size                           134217728innodb-open-files                                          0innodb-optimize-fulltext-only                              FALSEinnodb-page-size                                           16384innodb-print-all-deadlocks                                 FALSEinnodb-purge-batch-size                                    300innodb-purge-threads                                       1innodb-random-read-ahead                                   FALSEinnodb-read-ahead-threshold                                56innodb-read-io-threads                                     4innodb-read-only                                           FALSEinnodb-replication-delay                                   0innodb-rollback-on-timeout                                 FALSEinnodb-rollback-segments                                   128innodb-sort-buffer-size                                    1048576innodb-spin-wait-delay                                     6innodb-stats-auto-recalc                                   TRUEinnodb-stats-method                                        nulls_equalinnodb-stats-on-metadata                                   FALSEinnodb-stats-persistent                                    TRUEinnodb-stats-persistent-sample-pages                       20innodb-stats-sample-pages                                  8innodb-stats-transient-sample-pages                        8innodb-status-file                                         FALSEinnodb-strict-mode                                         FALSEinnodb-support-xa                                          TRUEinnodb-sync-array-size                                     1innodb-sync-spin-loops                                     30innodb-sys-columns                                         ONinnodb-sys-datafiles                                       ONinnodb-sys-fields                                          ONinnodb-sys-foreign                                         ONinnodb-sys-foreign-cols                                    ONinnodb-sys-indexes                                         ONinnodb-sys-tables                                          ONinnodb-sys-tablespaces                                     ONinnodb-sys-tablestats                                      ONinnodb-table-locks                                         TRUEinnodb-thread-concurrency                                  0innodb-thread-sleep-delay                                  10000innodb-trx                                                 ONinnodb-undo-directory                                      .innodb-undo-logs                                           128innodb-undo-tablespaces                                    0innodb-use-native-aio                                      TRUEinnodb-use-sys-malloc                                      TRUEinnodb-write-io-threads                                    4interactive-timeout                                        28800join-buffer-size                                           262144keep-files-on-create                                       FALSEkey-buffer-size                                            8388608key-cache-age-threshold                                    300key-cache-block-size                                       1024key-cache-division-limit                                   100language                                                   /home/jon/bin/mysql-5.6/share/large-pages                                                FALSElc-messages                                                en_USlc-messages-dir                                            /home/jon/bin/mysql-5.6/share/lc-time-names                                              en_USlocal-infile                                               TRUElock-wait-timeout                                          31536000log-bin                                                    (No default value)log-bin-index                                              (No default value)log-bin-trust-function-creators                            FALSElog-bin-use-v1-row-events                                  FALSElog-error                                                  log-isam                                                   myisam.loglog-output                                                 FILElog-queries-not-using-indexes                              FALSElog-raw                                                    FALSElog-short-format                                           FALSElog-slave-updates                                          FALSElog-slow-admin-statements                                  FALSElog-slow-slave-statements                                  FALSElog-tc                                                     tc.loglog-tc-size                                                24576log-throttle-queries-not-using-indexes                     0log-warnings                                               1long-query-time                                            10low-priority-updates                                       FALSElower-case-table-names                                     0master-info-file                                           master.infomaster-info-repository                                     FILEmaster-retry-count                                         86400master-verify-checksum                                     FALSEmax-allowed-packet                                         4194304max-binlog-cache-size                                      18446744073709547520max-binlog-dump-events                                     0max-binlog-size                                            1073741824max-binlog-stmt-cache-size                                 18446744073709547520max-connect-errors                                         100max-connections                                            151max-delayed-threads                                        20max-error-count                                            64max-heap-table-size                                        16777216max-join-size                                              18446744073709551615max-length-for-sort-data                                   1024max-prepared-stmt-count                                    16382max-relay-log-size                                         0max-seeks-for-key                                          18446744073709551615max-sort-length                                            1024max-sp-recursion-depth                                     0max-tmp-tables                                             32max-user-connections                                       0max-write-lock-count                                       18446744073709551615memlock                                                    FALSEmetadata-locks-cache-size                                  1024metadata-locks-hash-instances                              8min-examined-row-limit                                     0multi-range-count                                          256myisam-block-size                                          1024myisam-data-pointer-size                                   6myisam-max-sort-file-size                                  9223372036853727232myisam-mmap-size                                           18446744073709551615myisam-recover-options                                     OFFmyisam-repair-threads                                      1myisam-sort-buffer-size                                    8388608myisam-stats-method                                        nulls_unequalmyisam-use-mmap                                            FALSEnet-buffer-length                                          16384net-read-timeout                                           30net-retry-count                                            10net-write-timeout                                          60new                                                        FALSEold                                                        FALSEold-alter-table                                            FALSEold-passwords                                              0old-style-user-limits                                      FALSEopen-files-limit                                           1024optimizer-prune-level                                      1optimizer-search-depth                                     62optimizer-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=onoptimizer-trace                                            optimizer-trace-features                                   greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=onoptimizer-trace-limit                                      1optimizer-trace-max-mem-size                               16384optimizer-trace-offset                                     -1partition                                                  ONperformance-schema                                         TRUEperformance-schema-accounts-size                           -1performance-schema-consumer-events-stages-current          FALSEperformance-schema-consumer-events-stages-history          FALSEperformance-schema-consumer-events-stages-history-long     FALSEperformance-schema-consumer-events-statements-current      TRUEperformance-schema-consumer-events-statements-history      FALSEperformance-schema-consumer-events-statements-history-long FALSEperformance-schema-consumer-events-waits-current           FALSEperformance-schema-consumer-events-waits-history           FALSEperformance-schema-consumer-events-waits-history-long      FALSEperformance-schema-consumer-global-instrumentation         TRUEperformance-schema-consumer-statements-digest              TRUEperformance-schema-consumer-thread-instrumentation         TRUEperformance-schema-digests-size                            -1performance-schema-events-stages-history-long-size         -1performance-schema-events-stages-history-size              -1performance-schema-events-statements-history-long-size     -1performance-schema-events-statements-history-size          -1performance-schema-events-waits-history-long-size          -1performance-schema-events-waits-history-size               -1performance-schema-hosts-size                              -1performance-schema-instrument                              performance-schema-max-cond-classes                        80performance-schema-max-cond-instances                      -1performance-schema-max-file-classes                        50performance-schema-max-file-handles                        32768performance-schema-max-file-instances                      -1performance-schema-max-mutex-classes                       200performance-schema-max-mutex-instances                     -1performance-schema-max-rwlock-classes                      30performance-schema-max-rwlock-instances                    -1performance-schema-max-socket-classes                      10performance-schema-max-socket-instances                    -1performance-schema-max-stage-classes                       150performance-schema-max-statement-classes                   167performance-schema-max-table-handles                       -1performance-schema-max-table-instances                     -1performance-schema-max-thread-classes                      50performance-schema-max-thread-instances                    -1performance-schema-session-connect-attrs-size              -1performance-schema-setup-actors-size                       100performance-schema-setup-objects-size                      100performance-schema-users-size                              -1pid-file                                                   /home/jon/bin/mysql-5.6/data/havskatt.pidplugin-dir                                                 /home/jon/bin/mysql-5.6/lib/plugin/port                                                       3306port-open-timeout                                          0preload-buffer-size                                        32768profiling-history-size                                     15query-alloc-block-size                                     8192query-cache-limit                                          1048576query-cache-min-res-unit                                   4096query-cache-size                                           1048576query-cache-type                                           OFFquery-cache-wlock-invalidate                               FALSEquery-prealloc-size                                        8192range-alloc-block-size                                     4096read-buffer-size                                           131072read-only                                                  FALSEread-rnd-buffer-size                                       262144relay-log                                                  (No default value)relay-log-index                                            (No default value)relay-log-info-file                                        relay-log.inforelay-log-info-repository                                  FILErelay-log-purge                                            TRUErelay-log-recovery                                         FALSErelay-log-space-limit                                      0replicate-same-server-id                                   FALSEreport-host                                                (No default value)report-password                                            (No default value)report-port                                                0report-user                                                (No default value)safe-user-create                                           FALSEsecure-auth                                                TRUEsecure-file-priv                                           (No default value)server-id                                                  0server-id-bits                                             32sha256-password-private-key-path                           private_key.pemsha256-password-public-key-path                            public_key.pemshow-slave-auth-info                                       FALSEskip-grant-tables                                          FALSEskip-name-resolve                                          FALSEskip-networking                                            FALSEskip-show-database                                         FALSEskip-slave-start                                           FALSEslave-allow-batching                                       FALSEslave-checkpoint-group                                     512slave-checkpoint-period                                    300slave-compressed-protocol                                  FALSEslave-exec-mode                                            STRICTslave-load-tmpdir                                          /tmpslave-max-allowed-packet                                   1073741824slave-net-timeout                                          3600slave-parallel-workers                                     0slave-pending-jobs-size-max                                16777216slave-rows-search-algorithms                               TABLE_SCAN,INDEX_SCANslave-skip-errors                                          (No default value)slave-sql-verify-checksum                                  TRUEslave-transaction-retries                                  10slave-type-conversions                                     slow-launch-time                                           2slow-query-log                                             FALSEslow-query-log-file                                        /home/jon/bin/mysql-5.6/data/havskatt-slow.logsocket                                                     /tmp/mysql.socksort-buffer-size                                           262144sporadic-binlog-dump-fail                                  FALSEsql-mode                                                   NO_ENGINE_SUBSTITUTIONssl                                                        FALSEssl-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                                       256super-large-pages                                          FALSEsymbolic-links                                             TRUEsync-binlog                                                0sync-frm                                                   TRUEsync-master-info                                           10000sync-relay-log                                             10000sync-relay-log-info                                        10000sysdate-is-now                                             FALSEtable-definition-cache                                     615table-open-cache                                           431table-open-cache-instances                                 1tc-heuristic-recover                                       COMMITtemp-pool                                                  TRUEthread-cache-size                                          9thread-concurrency                                         10thread-handling                                            one-thread-per-connectionthread-stack                                               262144time-format                                                %H:%i:%stimed-mutexes                                              FALSEtmp-table-size                                             16777216tmpdir                                                     /tmptransaction-alloc-block-size                               8192transaction-isolation                                      REPEATABLE-READtransaction-prealloc-size                                  4096transaction-read-only                                      FALSEupdatable-views-with-limit                                 YESverbose                                                    TRUEwait-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 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".