MySQL5.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 서버를 튜닝하는 경우 구성하는 가장 중요한 두 가지 변수는 key_buffer_size 과 table_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 테이블 최적화" 를 참조하십시오.