MySQL 5.6 リファレンスマニュアル  /  ...  /  サーバーパラメータのチューニング

8.11.2 サーバーパラメータのチューニング

次のコマンドを使用して、mysqld サーバーで使用されるデフォルトのバッファーサイズを判断できます。

shell> mysqld --verbose --help

このコマンドによって、すべての mysqld オプションと構成可能なシステム変数のリストが生成されます。この出力には、デフォルトの変数値も含まれ、次のように見えます。

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

現在実行中の mysqld サーバーの場合、それに接続し、次のステートメントを発行することで、そのシステム変数の現在の値を確認できます。

mysql> SHOW VARIABLES;

また、次のステートメントを発行して、実行中のサーバーの統計やステータスインジケータの一部を表示することもできます。

mysql> SHOW STATUS;

システム変数とステータス情報は、mysqladmin を使用して取得することもできます。

shell> mysqladmin variables
shell> mysqladmin extended-status

すべてのシステムおよびステータス変数の完全な説明については、セクション5.1.4「サーバーシステム変数」およびセクション5.1.6「サーバーステータス変数」を参照してください。

MySQL はきわめてスケーラブルなアルゴリズムを使用しているため、通常ごくわずかなメモリーで実行できます。ただし、通常 MySQL に多くのメモリーを割り当てることによって、パフォーマンスが向上します。

MySQL サーバーをチューニングする場合、構成するもっとも重要な 2 つの変数は key_buffer_sizetable_open_cache です。ほかの変数の変更を試みる前に、まずこれらの変数が適切に設定されていることを確信しておくべきです。

次の例に、さまざまな実行時構成の一般的な変数値を示します。

  • 少なくとも 256M バイトのメモリーと多くのテーブルがあり、中程度のクライアント数で最大のパフォーマンスを必要とする場合、次のようなものを使用します。

    shell> mysqld_safe --key_buffer_size=64M --table_open_cache=256 \
               --sort_buffer_size=4M --read_buffer_size=1M &
    
  • メモリーが 128M バイトで、少数のテーブルしかないが、大量のソートを実行する場合、次のようなものを使用できます。

    shell> mysqld_safe --key_buffer_size=16M --sort_buffer_size=1M
    

    著しく多くの同時接続がある場合、mysqld が接続ごとにごく少量のメモリーを使用するように構成されていないかぎり、スワップの問題が発生する可能性があります。すべての接続に十分なメモリーがある場合に、mysqld は効率的に実行します。

  • メモリーがほとんどなく大量の接続がある場合は、次のようなものを使用します。

    shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=100K \
               --read_buffer_size=100K &
    

    これでもかまいません。

    shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=16K \
               --table_open_cache=32 --read_buffer_size=8K \
               --net_buffer_length=1K &
    

使用可能なメモリーよりはるかに大きいテーブルに対して、GROUP BY または ORDER BY 操作を実行する場合、read_rnd_buffer_size の値を増やして、行の読み取りとそれに続くソート操作を高速化します。

MySQL 配布に付属するサンプルオプションファイルを使用できます。セクション5.1.2「サーバー構成のデフォルト値」を参照してください。

コマンド行で mysqld または mysqld_safe のオプションを指定する場合、そのサーバーの呼び出しに対してのみ有効です。サーバーの実行のたびにオプションを使用するには、それをオプションファイルに入れます。

パラメータの変更の効果を確認するには、次のようなものを実行します。

shell> mysqld --key_buffer_size=32M --verbose --help

変数値は出力の最後近くに一覧表示されます。--verbose および --help オプションが最後になるようにしてください。そうでないと、コマンド行でそれらのあとに挙げられているすべてのオプションの効果が出力に反映されません。

InnoDB ストレージエンジンのチューニングについては、セクション8.5「InnoDB テーブルの最適化」を参照してください。


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