![variable.jp [データベース,パフォーマンス,運用]](https://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
MYSQL5.6も近い将来リリースされるので再度基本レビューしておく。
確認したMYSQLバージョン
mysql> select @@version; +------------+ | @@version | +------------+ | 5.5.29-log | +------------+ 1 row in set (0.00 sec)
MYSQLファイルパスについて
basedir MYSQLインストールディレクトリー
datadir データディレクトリー(mysql_install_dbスクリプト実行時に–datadir=/dataなどで指定可能）
/etc/my.cnfにて変更可能
[root@HOME001 mysql]# cat /etc/my.cnf | grep innodb #innodb_data_home_dir = /usr/local/mysql/data #innodb_data_file_path = ibdata1:10M:autoextend #innodb_log_group_home_dir = /usr/local/mysql/data innodb_buffer_pool_size = 32M innodb_additional_mem_pool_size = 2M innodb_log_file_size = 8M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [root@HOME001 mysql]#
mysql> show variables like '%dir%'; +-----------------------------------------+-------------------------------------------------------+ | Variable_name | Value | +-----------------------------------------+-------------------------------------------------------+ | basedir | /usr/local/mysql | | binlog_direct_non_transactional_updates | OFF | | character_sets_dir | /usr/local/mysql-5.5.29-linux2.6-i686/share/charsets/ | | datadir | /usr/local/mysql/data/ | | innodb_data_home_dir | | | innodb_log_group_home_dir | ./ | | innodb_max_dirty_pages_pct | 75 | | lc_messages_dir | /usr/local/mysql-5.5.29-linux2.6-i686/share/ | | plugin_dir | /usr/local/mysql/lib/plugin/ | | slave_load_tmpdir | /tmp | | tmpdir | /tmp | +-----------------------------------------+-------------------------------------------------------+ 11 rows in set (0.00 sec) mysql>
TCP/IP PORT・Socket接続について。
複数インスタンスを立ち上げる場合はPort、Socket、Datadirが被らないようにする必要あり。
mysql> show variables like '%socket%'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | socket | /tmp/mysql.sock | +---------------+-----------------+ 1 row in set (0.00 sec) mysql> show variables like '%port%'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | innodb_support_xa | ON | | large_files_support | ON | | port | 3306 | | report_host | | | report_password | | | report_port | 3306 | | report_user | | +---------------------+-------+ 7 rows in set (0.01 sec) mysql>
コネクションに関しては、Default値がバージョンによっても異なるので、
確認して不足している場合は適宜値を変更する必要があります。
Java EE, RubyなどのようにコネクションPoolする場合とPHPなどのように
都度接続する場合など調整方法も変更する必要があると思います。
MSSQLなどを利用している場合も同様に.Netやサーバー側のPool設定
またはLoad Balancerなどの調整なども環境によっては必要になると思います。
mysql> show variables like '%connection%'; +--------------------------+-----------------+ | Variable_name | Value | +--------------------------+-----------------+ | character_set_connection | utf8 | | collation_connection | utf8_general_ci | | max_connections | 151 | | max_user_connections | 0 | +--------------------------+-----------------+ 4 rows in set (0.00 sec) mysql>
既定のストレージエンジンについて
CREATE TABLE文実行時にENGINE=MyISAMなどで指定する事も可能。
mysql> show variables like '%storage%'; +------------------------+--------+ | Variable_name | Value | +------------------------+--------+ | default_storage_engine | InnoDB | | storage_engine | InnoDB | +------------------------+--------+ 2 rows in set (0.00 sec) mysql>
ログファイルについて。
log_binに関しては、リカバリーやレプリケーションで必須なので設定。
mysql> show variables like '%log%'; +-----------------------------------------+-----------------------------------------------+ | Variable_name | Value | +-----------------------------------------+-----------------------------------------------+ | back_log | 50 | | binlog_cache_size | 32768 | | binlog_direct_non_transactional_updates | OFF | | binlog_format | MIXED | | binlog_stmt_cache_size | 32768 | | expire_logs_days | 7 | | general_log | OFF | | general_log_file | /usr/local/mysql/data/HOME001.log | | innodb_flush_log_at_trx_commit | 1 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_buffer_size | 8388608 | | innodb_log_file_size | 8388608 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_mirrored_log_groups | 1 | | log | OFF | | log_bin | ON | | log_bin_trust_function_creators | OFF | | log_error | /usr/local/mysql/data/HOME001.localdomain.err | | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_queries | OFF | | log_warnings | 1 | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | max_relay_log_size | 0 | | relay_log | | | relay_log_index | | | relay_log_info_file | relay-log.info | | relay_log_purge | ON | | relay_log_recovery | OFF | | relay_log_space_limit | 0 | | slow_query_log | OFF | | slow_query_log_file | /usr/local/mysql/data/HOME001-slow.log | | sql_log_bin | ON | | sql_log_off | OFF | | sync_binlog | 0 | | sync_relay_log | 0 | | sync_relay_log_info | 0 | +-----------------------------------------+-----------------------------------------------+ 41 rows in set (0.00 sec) mysql> mysql> show variables like '%bin%'; +-----------------------------------------+----------------------+ | Variable_name | Value | +-----------------------------------------+----------------------+ | binlog_cache_size | 32768 | | binlog_direct_non_transactional_updates | OFF | | binlog_format | MIXED | | binlog_stmt_cache_size | 32768 | | innodb_locks_unsafe_for_binlog | OFF | | log_bin | ON | | log_bin_trust_function_creators | OFF | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | sql_log_bin | ON | | sync_binlog | 0 | +-----------------------------------------+----------------------+ 12 rows in set (0.00 sec) mysql>
遅いQueryを早期発見してDBを安定稼動させ続ける為に必要です。
設定を有効にしておきましょう。
オンラインでも設定可能ですが再起動に備えて/etc/my.cnfに設定入れておくと良い。
mysql> show variables like '%slow%'; +---------------------+----------------------------------------+ | Variable_name | Value | +---------------------+----------------------------------------+ | log_slow_queries | OFF | | slow_launch_time | 2 | | slow_query_log | OFF | | slow_query_log_file | /usr/local/mysql/data/HOME001-slow.log | +---------------------+----------------------------------------+ 4 rows in set (0.00 sec) mysql> SET GLOBAL log_slow_queries = 1; Query OK, 0 rows affected, 1 warning (0.39 sec) mysql> show variables like '%slow%'; +---------------------+----------------------------------------+ | Variable_name | Value | +---------------------+----------------------------------------+ | log_slow_queries | ON | | slow_launch_time | 2 | | slow_query_log | ON | | slow_query_log_file | /usr/local/mysql/data/HOME001-slow.log | +---------------------+----------------------------------------+ 4 rows in set (0.00 sec) mysql>
設定は反映されるかWarningに以下のメッセージが出るので、
以降は”SET GLOBAL slow_query_log = 1;”コマンドで有効に設定する。
mysql> show warnings; +---------+------+-------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------------------------------------------------------------+ | Warning | 1287 | '@@log_slow_queries' is deprecated and will be removed in a future release. Please use '@@slow_query_log' instead | +---------+------+-------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql>
SLOWログの閾値を設定する。
Default10秒なので1秒に変更するが再起動が必要。
規定値として1秒に設定しておいて、I/O負荷状況や必要に応じて
”log_slow_queries”でON・OFFを切り替えるのが良いかと思う。
mysql> show variables like '%long%'; +---------------------------------------------------+-----------+ | Variable_name | Value | +---------------------------------------------------+-----------+ | long_query_time | 10.000000 | | max_long_data_size | 1048576 | | performance_schema_events_waits_history_long_size | 10000 | +---------------------------------------------------+-----------+ 3 rows in set (0.00 sec) mysql> set global long_query_time = 1; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%long%'; +---------------------------------------------------+-----------+ | Variable_name | Value | +---------------------------------------------------+-----------+ | long_query_time | 10.000000 | | max_long_data_size | 1048576 | | performance_schema_events_waits_history_long_size | 10000 | +---------------------------------------------------+-----------+ 3 rows in set (0.00 sec) mysql>
SLOWログの設定を設定ファイルに入れて再起動。
long_query_timeはマイクロ秒単位で設定可能（例：long_query_time=0.5）
[root@HOME001 mysql]# vi /etc/my.cnf [root@HOME001 mysql]# cat /etc/my.cnf | egrep -i "slow|long" # In this file, you can use all long options that a program supports. slow_query_log=ON slow_query_log_file=home001-mysql-slow.log long_query_time=1 [root@HOME001 mysql]# mysql> show variables like '%slow%'; +---------------------+------------------------+ | Variable_name | Value | +---------------------+------------------------+ | log_slow_queries | ON | | slow_launch_time | 2 | | slow_query_log | ON | | slow_query_log_file | home001-mysql-slow.log | +---------------------+------------------------+ 4 rows in set (0.00 sec) mysql> mysql> show variables like '%long%'; +---------------------------------------------------+----------+ | Variable_name | Value | +---------------------------------------------------+----------+ | long_query_time | 1.000000 | | max_long_data_size | 1048576 | | performance_schema_events_waits_history_long_size | 10000 | +---------------------------------------------------+----------+ 3 rows in set (0.00 sec) mysql>
Fusion-IOなどの高性能のディスクも出てきましたが、
メモリーの方が高速である事は変わりないのでパフォーマンスに影響ある
innodb_buffer_pool_sizeを適切に設定しInnoDB内のデータ領域を
メモリー内に保持する必要があります。
サーバーを1つのMYSQLインスタンス専用として利用している場合は、
物理メモリーサイズの60％～80％程度を割り当てて様子を見て調整すれば良いようです。
Linuxの古いカーネルなどを利用している場合は、InnoDBログサイズなどが大きく
ファイルシステムのキャッシュから圧迫される事でSwapが頻発してしまう可能性があるそうです。
残りのメモリーは、OSやアプリケーション接続スレッドなどに利用される。
※ 参考：unmap_mysql_logs https://github.com/yoshinorim/unmap_mysql_logs
※ Swapの確認に関しては下に追記しておきます。
mysql> show variables like '%buffer_pool%'; +------------------------------+----------+ | Variable_name | Value | +------------------------------+----------+ | innodb_buffer_pool_instances | 1 | | innodb_buffer_pool_size | 33554432 | +------------------------------+----------+ 2 rows in set (0.01 sec) mysql>
MySQL innodb_flush_method = O_DIRECTに関してネットで確認してみると
検証されている方も沢山いて非常に参考になります。
全ての環境でパフォーマンスが上がる訳では無さそうなので検証してしてみる必要あり。
MySQL innodb_flush_method = O_DIRECTを設定するとダイレクトI/OというOSの機能が有効になります。
ダイレクトI/Oを利用するとInnoDBのメモリー領域とディスク間のデータのやりとりに（InnoDB Buffer<->Disk)、
OSのファイルシステムキャッシュを利用しないI/O方式になるとの事。
※ オーバーヘット増：Disk -> OS Page Cache -> InnoDB Buffer
※ オーバーヘット減：Disk -> InnoDB Buffer
※ メモリーが多い環境で有効との事。
mysql> show variables like '%flush%'; +--------------------------------+-------+ | Variable_name | Value | +--------------------------------+-------+ | flush | OFF | | flush_time | 0 | | innodb_adaptive_flushing | ON | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | +--------------------------------+-------+ 5 rows in set (0.00 sec) mysql>
innodb_data_file_pathパラメーターに関しては、
データファイルの初期サイズやディレクトリーを指定する為に利用。
データファイルの拡張が頻繁に発生する環境では予めファイルサイズを大きめに取って、
ユーザーがサイトを利用している間にファイル拡張が発生して
パフォーマンスが落ちないようにしておく方が良いかと思います。
mysql> show variables like '%innodb_data%'; +-----------------------+------------------------+ | Variable_name | Value | +-----------------------+------------------------+ | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_data_home_dir | | +-----------------------+------------------------+ 2 rows in set (0.00 sec)
innodb_autoextend_incrementがautoextendで拡張する拡張単位を指定します。
Defaultでは8MB単位になっているので必要に応じて変更。
mysql> show variables like '%innodb_autoextend%'; +-----------------------------+-------+ | Variable_name | Value | +-----------------------------+-------+ | innodb_autoextend_increment | 8 | +-----------------------------+-------+ 1 row in set (0.00 sec) mysql> set global innodb_autoextend_increment = 10; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%innodb_autoextend%'; +-----------------------------+-------+ | Variable_name | Value | +-----------------------------+-------+ | innodb_autoextend_increment | 10 | +-----------------------------+-------+ 1 row in set (0.00 sec) mysql>
再起動で変更が消えてしまうので、my.cnfに設定を追記しておくこと。
[root@HOME001 mysql]# vi /etc/my.cnf [root@HOME001 mysql]# cat /etc/my.cnf | grep innodb_autoextend_increment innodb_autoextend_increment = 10 [root@HOME001 mysql]# /etc/init.d/mysql restart Shutting down MySQL.. [ OK ] Starting MySQL.. [ OK ] [root@HOME001 mysql]#
innodb_log_file_sizeはInnoDBのログファイルサイズを指定出来ます。
OracleのREDOログやMS SQLのT-Logと同じなのでクラッシュリカバリーでも利用されます。
大きいとログスイッチの頻度やチェックポイント処理を下げる事が出来ますが、
多くのトランザクションログが一つのファイルに含まれて障害発生時にログの破損やリカバリー処理に
時間がかかり困る場合もあるので、パフォーマンスとリカバリーの観点から適切なサイズを選択する必要があります。
innodb_log_file_size
小さい： リカバリー高速だが更新処理が遅くなる。
大きい： 高速だがリカバリーが遅い。ログファイルの破損の注意。
mysql> show variables like '%innodb_log%'; +---------------------------+---------+ | Variable_name | Value | +---------------------------+---------+ | innodb_log_buffer_size | 8388608 | | innodb_log_file_size | 8388608 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | +---------------------------+---------+ 4 rows in set (0.00 sec) mysql>
安定性からMYSQLコンパイル済みのバイナリーを利用してますが、
コンパイル済みMYSQLのConfigオプションを確認したい場合は以下のファイルを確認。
[root@HOME001 docs]# tail -n 100 /usr/local/mysql/docs/INFO_BIN ===== Information about the build process: ===== Build was run at 2012-12-10 07:35:23 on host 'loki02' Build was done on Linux-2.6.9-89.ELsmp using i686 Build was done using cmake 2.8.5 ===== Compiler flags used (from the 'sql/' subdirectory): ===== # compile C with /usr/local/gcc-4.3.4/bin/gcc # compile CXX with /usr/local/gcc-4.3.4/bin/gcc C_FLAGS = -fPIC -Wall -O3 -g -static-libgcc -fno-omit-frame-pointer -fno-strict-aliasing -DDBUG_OFF -DMY_PTHREAD_FASTMUTEX=1 -I/export/home/pb2/build/sb_0-7814961-1355120555.52/release/include -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/include -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/sql -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/regex -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/zlib -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/extra/yassl/include -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/extra/yassl/taocrypt/include -I/export/home/pb2/build/sb_0-7814961-1355120555.52/release/sql -DHAVE_YASSL -DYASSL_PURE_C -DYASSL_PREFIX -DHAVE_OPENSSL -DMULTI_THREADED C_DEFINES = -DHAVE_CONFIG_H -DMYSQL_SERVER -DHAVE_EVENT_SCHEDULER CXX_FLAGS = -fno-exceptions -fPIC -Wall -Wno-unused-parameter -fno-implicit-templates -fno-exceptions -fno-rtti -O3 -g -static-libgcc -fno-omit-frame-pointer -fno-strict-aliasing -DDBUG_OFF -DMY_PTHREAD_FASTMUTEX=1 -I/export/home/pb2/build/sb_0-7814961-1355120555.52/release/include -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/include -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/sql -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/regex -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/zlib -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/extra/yassl/include -I/export/home/pb2/build/sb_0-7814961-1355120555.52/mysql-5.5.29/extra/yassl/taocrypt/include -I/export/home/pb2/build/sb_0-7814961-1355120555.52/release/sql -DHAVE_YASSL -DYASSL_PURE_C -DYASSL_PREFIX -DHAVE_OPENSSL -DMULTI_THREADED CXX_DEFINES = -DHAVE_CONFIG_H -DMYSQL_SERVER -DHAVE_EVENT_SCHEDULER Pointer size: 4 ===== Feature flags used: ===== -- Cache values CMAKE_BUILD_TYPE:STRING=RelWithDebInfo CMAKE_INSTALL_PREFIX:PATH=/usr/local/mysql COMMUNITY_BUILD:BOOL=ON ENABLED_PROFILING:BOOL=ON ENABLE_DEBUG_SYNC:BOOL=ON ENABLE_GCOV:BOOL=OFF FEATURE_SET:STRING=community INSTALL_LAYOUT:STRING=STANDALONE MYSQL_DATADIR:PATH=/usr/local/mysql/data MYSQL_MAINTAINER_MODE:BOOL=OFF WITH_ARCHIVE_STORAGE_ENGINE:BOOL=ON WITH_BLACKHOLE_STORAGE_ENGINE:BOOL=ON WITH_DEBUG:BOOL=OFF WITH_EMBEDDED_SERVER:BOOL=ON WITH_EXAMPLE_STORAGE_ENGINE:BOOL=OFF WITH_EXTRA_CHARSETS:STRING=all WITH_FEDERATED_STORAGE_ENGINE:BOOL=ON WITH_INNOBASE_STORAGE_ENGINE:BOOL=ON WITH_LIBEDIT:BOOL=OFF WITH_LIBWRAP:BOOL=OFF WITH_PARTITION_STORAGE_ENGINE:BOOL=ON WITH_PERFSCHEMA_STORAGE_ENGINE:BOOL=ON WITH_PIC:BOOL=ON WITH_READLINE:BOOL=ON WITH_SSL:STRING=bundled WITH_UNIT_TESTS:BOOL=ON WITH_VALGRIND:BOOL=OFF WITH_ZLIB:STRING=bundled ===== EOF ===== [root@HOME001 docs]#
参考：
MySQL 5.1のスロークエリログ
MySQL 5.1のmysqldumpslowで快速チューニング
MySQL innodb_flush_method = O_DIRECTの検討
非同期I/Oの謎
初期化パラメータ filesystemio_options の値によってI/Oに関するシステムコールがどのように変化するか
Oracleの同期IOと非同期IOについて
その他パフォーマンス関連調整項目：
MySQL InnoDBストレージエンジンのチューニング（前編）
MySQL InnoDBストレージエンジンのチューニング（後編）
mysql> show variables like '%innodb_io_capacity%'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | innodb_io_capacity | 200 | +--------------------+-------+ 1 row in set (0.00 sec) mysql> mysql> show variables like '%innodb_doublewrite'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | innodb_doublewrite | ON | +--------------------+-------+ 1 row in set (0.00 sec) mysql> show variables like '%_io_threads'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | innodb_read_io_threads | 4 | | innodb_write_io_threads | 4 | +-------------------------+-------+ 2 rows in set (0.00 sec) mysql>
Swapに関しての確認メモ
以下のようなコマンドで確認する事が出来る。
[root@HOME001 ~]# /sbin/swapon -s
Filename Type Size Used Priority
/dev/dm-1 partition 4161528 0 -1
[root@HOME001 ~]#
[root@HOME001 ~]# cat /proc/swaps
Filename Type Size Used Priority
/dev/dm-1 partition 4161528 0 -1
[root@HOME001 ~]#
[root@HOME001 ~]# cat /proc/meminfo
MemTotal: 1938948 kB
MemFree: 1679616 kB
Buffers: 10584 kB
Cached: 149116 kB
SwapCached: 0 kB
Active: 55040 kB
Inactive: 140456 kB
Active(anon): 36056 kB
Inactive(anon): 4 kB
Active(file): 18984 kB
Inactive(file): 140452 kB
Unevictable: 0 kB
Mlocked: 0 kB
HighTotal: 1189716 kB
HighFree: 995844 kB
LowTotal: 749232 kB
LowFree: 683772 kB
SwapTotal: 4161528 kB
SwapFree: 4161528 kB
Dirty: 8 kB
Writeback: 0 kB
AnonPages: 35816 kB
Mapped: 13712 kB
Shmem: 264 kB
Slab: 37544 kB
SReclaimable: 7596 kB
SUnreclaim: 29948 kB
KernelStack: 1056 kB
PageTables: 1688 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
WritebackTmp: 0 kB
CommitLimit: 5131000 kB
Committed_AS: 341716 kB
VmallocTotal: 122880 kB
VmallocUsed: 13716 kB
VmallocChunk: 90828 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
DirectMap4k: 10232 kB
DirectMap2M: 897024 kB
[root@HOME001 ~]#
[root@HOME001 ~]# free -kt
total used free shared buffers cached
Mem: 1938948 259580 1679368 0 10836 149104
-/+ buffers/cache: 99640 1839308
Swap: 4161528 0 4161528
Total: 6100476 259580 5840896
[root@HOME001 ~]#
[root@HOME001 ~]# free -mt
total used free shared buffers cached
Mem: 1893 253 1640 0 10 145
-/+ buffers/cache: 97 1796
Swap: 4063 0 4063
Total: 5957 253 5704
[root@HOME001 ~]#
[root@HOME001 ~]# df -h /dev/mapper/vg_home001-lv_swap
Filesystem Size Used Avail Use% マウント位置
- 940M 260K 940M 1% /dev
[root@HOME001 ~]#
[root@HOME001 ~]# vmstat 10 -S M
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st
0 0 0 1639 10 145 0 0 21 10 27 64 0 0 98 1 0
0 0 0 1639 10 145 0 0 0 0 10 41 0 0 100 0 0