MySQLのチューニング調査依頼で時短で終わるように
バージョン
- CentOS7
- MySQL5.6
もくじ
DB, テーブルのDBエンジンの確認
データベースの確認
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | exampledb | +--------------------+ 4 rows in set (0.00 sec)
デフォルトのエンジンの確認
mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec)
DBエンジンの確認 例) exampledbの各テーブルのエンジンの確認
mysql> use information_schema; mysql> select table_name, engine from tables where table_schema = "exampledb"; +------------------------+--------+ | table_name | engine | +------------------------+--------+ | wp_commentmeta | InnoDB | | wp_comments | InnoDB | | wp_cptch_images | InnoDB | | wp_cptch_packages | InnoDB | | wp_cptch_whitelist | InnoDB | | wp_links | InnoDB | | wp_options | InnoDB | | wp_popularpostsdata | InnoDB | | wp_popularpostssummary | InnoDB | | wp_postmeta | InnoDB | | wp_posts | InnoDB | | wp_term_relationships | InnoDB | | wp_term_taxonomy | InnoDB | | wp_termmeta | InnoDB | | wp_terms | InnoDB | | wp_user_login_log | InnoDB | | wp_usermeta | InnoDB | | wp_users | InnoDB | | wp_wp_rp_tags | InnoDB | +------------------------+--------+ 19 rows in set (0.00 sec)
InnoDBなのか、MyISAMなのか・・・それ以外なのかによってチューニングパラメータは異なる。1つのサーバに異なるエンジンが混在していないのが望ましい。
グローバルパラメータの確認
実際に有効になっているパラメータを確認する
グローバルパラメータの確認
mysql> SHOW GLOBAL VARIABLES;
+--------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+--------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |
| automatic_sp_privileges | ON |
| avoid_temporal_upgrade | OFF |
| back_log | 92 |
(略)
|
| updatable_views_with_limit | YES |
| version | 5.6.37-log |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
| wait_timeout | 60 |
+--------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
440 rows in set (0.00 sec)
ステータスの確認
mysql> SHOW GLOBAL STATUS; +-----------------------------------------------+-------------+ | Variable_name | Value | +-----------------------------------------------+-------------+ | Aborted_clients | 0 | | Aborted_connects | 1 | | Binlog_cache_disk_use | 1461 | | Binlog_cache_use | 453258 | (略) | Tc_log_page_size | 0 | | Tc_log_page_waits | 0 | | Threads_cached | 22 | | Threads_connected | 1 | | Threads_created | 23 | | Threads_running | 1 | | Uptime | 4309136 | | Uptime_since_flush_status | 4309136 | +-----------------------------------------------+-------------+ 341 rows in set (0.00 sec)
パフォーマンススキーマ
mysql> SHOW GLOBAL VARIABLES LIKE 'performance_schema%'; +--------------------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------------------+-------+ | performance_schema | ON | | performance_schema_accounts_size | 100 | | performance_schema_digests_size | 5000 | | performance_schema_events_stages_history_long_size | 1000 | | performance_schema_events_stages_history_size | 10 | | performance_schema_events_statements_history_long_size | 1000 | | performance_schema_events_statements_history_size | 10 | | performance_schema_events_waits_history_long_size | 1000 | | performance_schema_events_waits_history_size | 10 | | performance_schema_hosts_size | 100 | | performance_schema_max_cond_classes | 80 | | performance_schema_max_cond_instances | 1255 | | performance_schema_max_digest_length | 1024 | | performance_schema_max_file_classes | 50 | | performance_schema_max_file_handles | 32768 | | performance_schema_max_file_instances | 1750 | | performance_schema_max_mutex_classes | 200 | | performance_schema_max_mutex_instances | 4489 | | performance_schema_max_rwlock_classes | 40 | | performance_schema_max_rwlock_instances | 2306 | | performance_schema_max_socket_classes | 10 | | performance_schema_max_socket_instances | 320 | | performance_schema_max_stage_classes | 150 | | performance_schema_max_statement_classes | 168 | | performance_schema_max_table_handles | 572 | | performance_schema_max_table_instances | 556 | | performance_schema_max_thread_classes | 50 | | performance_schema_max_thread_instances | 378 | | performance_schema_session_connect_attrs_size | 512 | | performance_schema_setup_actors_size | 100 | | performance_schema_setup_objects_size | 100 | | performance_schema_users_size | 100 | +--------------------------------------------------------+-------+ 32 rows in set (0.00 sec)
オープンになっているテーブルの状況が見れる
mysql> SHOW OPEN TABLES;
プロシージャの確認
mysql> SHOW PROCEDURE STATUS;
権限の確認
mysql> SHOW GRANTS;
エラー状況を確認
WARNINGクエリ確認
mysql> SHOW WARNINGS;
ERRORクエリの確認
mysql> SHOW ERRORS;
障害発生時にログインして、MySQLサービスが再起動したかを確認する
mysql> SHOW GLOBAL STATUS LIKE 'uptime';
クエリの実行状況が見れる
mysql> SHOW PROCESSLIST; +--------+------+-----------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +--------+------+-----------+------+---------+------+-------+------------------+ | 303072 | root | localhost | NULL | Query | 0 | init | SHOW PROCESSLIST | +--------+------+-----------+------+---------+------+-------+------------------+ 1 row in set (0.00 sec)
デッドロック ロック待ちの確認
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 303072
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: init
Info: SHOW PROCESSLIST
1 row in set (0.00 sec)
これはOK
mysql> SHOW PROCESSLIST\G *************************** 1. row *************************** ・・・・・・・・ Time: 55 State: Locked ←！！！！ ・・・・・・・・
こうなっていたらまずい
ロック時にSHOW ENGINE INNODB STATUSコマンドでロック原因を特定できるようにする
# vi /etc/my.cnf # デッドロック関連のログをエラーログに出力させる innodb_print_all_deadlocks=ON # ロックモニターの有効化：SHOW ENGINE INNODB STATUSでロック原因を特定できるようにする innodb_status_output=ON innodb_status_output_locks=ON
デッドロック発生時にタイムアウトを設定する
# vi /etc/my.cnf ## デッドロック ==================================== #テーブルロックタイムアウト時間 必須 初期値50秒 innodb_lock_wait_timeout = 5
クエリキャッシュ
注意
- INSERTが多いシステムでは逆にパフォーマンスが悪くなり向かない
クエリキャッシュを利用する為に広範囲でロックを取るのでロック競合する - SELECT主体の参照が多いサイトでは向いている
- MySQL8.0では廃止された
設定にクエリキャッシュ関連のディレクティブがあると起動しない
クエリキャッシュを削除する場合
mysql> RESET QUERY CACHE;
クエリキャッシュヒット状況の確認
mysql> SHOW STATUS LIKE 'Qcache%'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | Qcache_free_blocks | 3 | | Qcache_free_memory | 132631016 | | Qcache_hits | 110 | | Qcache_inserts | 179 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 31 | | Qcache_queries_in_cache | 97 | | Qcache_total_blocks | 211 | +-------------------------+-----------+ 8 rows in set (0.00 sec)
- Qcache_free_blocks
キャッシュに割り当てが出来るフリーのブロック数
1SQLキャッシュするのに1ブロック最低消費される - Qcache_free_memory
クエリキャッシュに割り当てができるフリーのメモリ - Qcache_hits
クエリのヒット数 - Qcache_lowmem_prunes
メモリがないので削除されたクエリキャッシュ
すべてのキャッシュがメモリに載っていればここは0になる - Qcache_not_cached
キャッシュに入れられなかったクエリの数 - Qcache_queries_in_cache
キャッシュ内のクエリの数 - Qcache_total_blocks
ブロック領域の合計
クエリキャッシュ？
- クエリキャッシュはSELECTクエリの結果をキャッシュしたものです。
- まったく同じSELECTクエリが来た場合にキャッシュを返します。
クエリキャッシュヒット率の確認
mysql> SELECT (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'QCACHE_HITS')/(SELECT SUM(VARIABLE_VALUE) FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME IN ('QCACHE_HITS','QCACHE_INSERTS','QCACHE_NOT_CACHED'))*100 AS CACHE_HIT_RATE;
+--------------------+
| CACHE_HIT_RATE |
+--------------------+
| 49.033816425120776 |
+--------------------+
1 row in set (0.00 sec)
キャッシュがどれだけヒットしているか
query_cache_limitの確認
mysql> SHOW GLOBAL VARIABLES LIKE 'query_cache_limit'; +-------------------+---------+ | Variable_name | Value | +-------------------+---------+ | query_cache_limit | 2097152 | +-------------------+---------+ 1 row in set (0.00 sec)
2MBに設定済。
2MB～4MBにする
query_cache_sizeの確認
mysql> SHOW GLOBAL VARIABLES LIKE 'query_cache_size'; +------------------+-----------+ | Variable_name | Value | +------------------+-----------+ | query_cache_size | 134217728 | +------------------+-----------+ 1 row in set (0.00 sec)
128MBに設定済。1年後のDBサイズの20%程度が目安とされる。
128MB～256MB程度まで。クエリキャッシュが多すぎてもキャッシュ保守にリソースが回されて逆に遅くなる。大きくし過ぎてもいけない。
スロークエリ対応
スロークエリ有効化 1秒以上かかるクエリを出力する
mysql> SET GLOBAL slow_query_log=ON mysql> SET GLOBAL slow_query_log_file=/var/lib/mysql/mysql-slow.log mysql> SET GLOBAL long_query_time=1
遅い順にソートする
$ mysqldumpslow -s t /var/lib/mysql/mysql-slow.log
EXPLAIN EXTENDEDによるクエリの解析
mysql> EXPLAIN EXTENDED <SQLクエリ>\G
typeに注目する
良いtype
- const
PRIMARY KEYまたはUNIQUEインデックスによる検索で最速 - eq_ref
JOINにてPRIARY KEYまたはUNIQUE KEYが利用される時 - ref
ユニークでないインデックスによる検索。WHERE name = valueのような時 - range
ユニークを用いた範囲検索
あまり良くないtype
- index
フルインデックススキャン。インデックス全体を見るので遅い - ALL
フルテーブルスキャン。インデックスが利用されていない！
スロークエリでなければ問題ない。
mysql> EXPLAIN EXTENDED <SQL文>\G *********************** 1. row *********************** ・・・ type: index ・・・ rows: 1000 ・・・ *********************** 2. row *********************** ・・・ type: index_subquery ・・・ rows: 200 ・・・
1000 × 200行が対象になっている。減らせないか？
mysql> EXPLAIN EXTENDED SELECT post_title, post_date, post_status FROM wp_posts \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: wp_posts
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 7799
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
ALLはフルテーブルスキャン。インデックスが利用されていないことを示す
mysql> EXPLAIN EXTENDED SELECT * FROM wp_posts WHERE post_status = "publish" AND post_type = "post" \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: wp_posts
type: ref
possible_keys: type_status_date
key: type_status_date
key_len: 164
ref: const,const
rows: 634
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
ref。ユニーク（PRIMARY or UNIQUE）でないインデックスを使って等価検索を行った時に利用されるアクセスタイプ。
WHEREで検索されるキーとなるカラムがインデックスされていなければインデックスを張る
mysql> ALTER TABLE <テーブル名> ADD INDEX(<カラム名>)
対象カラムがユニークになっているようにする
mysql> ALTER TABLE <テーブル名> ADD UNIQUE(<カラム名>)
インデックス関連
チューニングツールの利用
ファイルディスクリプタ
ジェネラルログを出力する
フレームワークでORMのクエリを出力する時など、すべてのクエリを見たい時に
mysql> SET GLOBAL general_log='on' mysql> SET GLOBAL general_log_file=/var/lib/mysql/general.log
優技録のコンフィグ 4CPU 4GB
※2019年01月25日 2CPU 2GBにスケールダウン ボトルネックの改善による省リソースハック成功
# cat /etc/my.cnf
[mysqld]
##レプリケーション Master ==================================
binlog-do-db = exampledb # レプリケーション対象DB
binlog-ignore-db = mysql # レプリケーションしないDB
server-id = 1
log-bin = mysql-bin
relay-log = relay-log
#bind-address = 0.0.0.0
expire_logs_days = 10
max_binlog_size = 128M
binlog_format = mixed
#マスタートランザクション
sync_binlog = 1
innodb_support_xa = 1
innodb_flush_log_at_trx_commit = 1
## 準同期マスター側設定
#plugin-load=rpl_semi_sync_master=semisync_master.so
#rpl_semi_sync_master_enabled=1
#rpl_semi_sync_master_timeout=5000
####################### ==================================
## MySQLデータディレクトリの指定場所
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
pid-file=/var/run/mysqld/mysqld.pid
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
character-set-server = utf8
skip-character-set-client-handshake
lower_case_table_names
skip-name-resolve
default-storage-engine=InnoDB
sql_mode=''
##タイムスタンプ 5.6～
explicit_defaults_for_timestamp = true
#リカバリ どうしても起動しない時に1ずつ数値をあげて再起動させる。
#innodb_force_recovery = 1
#####=================================================================================
#グローバルバッファ mysqlデーモン全体で一つだけ確保されるバッファ
#スレッドバッファ mysqlのスレッド(コネクション)単位で確保されるバッファ
#公式 グローバルバッファ + (スレッドバッファ × コネクション数) = メモリ使用量
#####=================================================================================
## 最大接続数
max_connections = 1024
### グローバルバッファ ##################=============================================
innodb_buffer_pool_size = 2G ##重要 全物理メモリの70～80%が目安 or 2年後のDB最大容量Xの120%以上 設定容量分だけディスクに書き込まれている分メモリに
##5.6から廃止 innodb_additional_mem_pool_size = 16M
innodb_use_sys_malloc = 1 ##InnoDBの内部データなどを保持するための領域
innodb_file_per_table ##テーブル毎にテーブルスペースが作成される
innodb_autoextend_increment = 64M ##InnoDB自動拡張サイズ
#無効化#innodb_data_file_path=ibdata1:10G:autoextend:max:100G ## 安全弁
innodb_log_files_in_group = 4 ##ロググループのログファイル数
innodb_log_file_size = 128M ##Innodbロググループの各ログファイルのサイズ 初期値:48M
#無効化#innodb_flush_log_at_trx_commit = 1 ##1:トランザクション単位でログ出力し安全
##2:1秒間に1回ログファイルに出力。冗長構成時は危険
innodb_thread_concurrency = 16 ##InnoDBが同時に処理することのできるスレッド数の上限
innodb_commit_concurrency = 10 ##InnoDBが同時にコミットする事ができるスレッドの数
#無効化#innodb_flush_method = O_DIRECT ##ハードウェアRAIDを使用している場合のみ可 初期値:fdatasync
innodb_strict_mode ##エラーが発生時に警告のみでパフォーマンスには影響しない
innodb_write_io_threads = 8 ##デフォルト値4 非同期IOの書き込みバックグラウンドスレッド数
innodb_read_io_threads = 8 ##デフォルト値4 非同期IOの読み込みバックグラウンドスレッド数
#無効化#innodb_fast_shutdown = 0 ##デフォルト値1 シャットダウン時にWALに残っていたデータを書き込み
### グローバルバッファ ここまで ##########=============================================
### スレッドバッファ ######################
key_buffer_size = 16M
read_buffer_size = 256k
read_rnd_buffer_size = 512k
join_buffer_size = 256k
sort_buffer_size = 512K
###########################################
## キャッシュ ##########################
thread_cache_size = 128
table_open_cache = 4000
table_definition_cache = 400
##クエリキャッシュ
query_cache_size = 128M ## 重要 2年後のDB最大容量Xの10%以上が目安
query_cache_limit = 2M
query_cache_min_res_unit = 4k
query_cache_type = 1
#######################################
## 接続最大時間
wait_timeout = 60
##最大取扱いファイル数
open_files_limit = 5500
## デッドロック ====================================
#テーブルロックタイムアウト時間 必須 初期値50秒
innodb_lock_wait_timeout = 5
#デッドロック関連のログをエラーログに出力させる
innodb_print_all_deadlocks=ON
# ロックモニターの有効化：ロック時にSHOW ENGINE INNODB STATUSでロック原因を特定できるようにする
innodb_status_output=ON
innodb_status_output_locks=ON
##==================================================
##この2つは同じ値にする事
tmp_table_size = 64M
max_heap_table_size = 64M
## クライアントからサーバーに送信できるパケットの最大長 リストア出来るDB最大サイズ
#無効化#max_allowed_packet = 8M
max_allowed_packet = 4000M
## 重要 クエリキャッシュ １年後のデータ総量Xの20%
query_cache_size = 64M
query_cache_limit = 2M
query_cache_min_res_unit = 4k
query_cache_type = 1
## デバッグ =================================
## スロークエリ
slow_query_log = 1
long_query_time = 3
slow_query_log_file = /var/log/mysqld-slow.log
##無効化#log_queries_not_using_indexes
##無効化#log-slow-admin-statements
## 全てのクエリを記録
#general_log=1
#general_log_file=/var/log/general-query.log
##エラーログの確認
log-error=/var/log/mysqld-error.log
#Aborted connection..のような警告をerror Logに書く
log_warnings = 1
##============================================
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Windows7, 2008以上のMySQLの場合
デフォルトでIPv6優先になっているので、IPv6優先かつlocalhostで接続になっている場合は遅くなる。
- IPv4優先
- 127.0.0.1で接続
// アプリサーバとDBサーバが別でIPによる接続しているなら問題ない - 特に強い理由がなければミニマルなLinux版MySQLにした方が速い
関連記事 - More from my site -
Laravel Redisを用いたキューイングのお勉強
Git 複数人開発で注意が必要なコマンド
CentOS7 ClamAV 手動でスキャン
Cisco よく使うshow系コマンド
YAMAHA大規模構成 まとめ
Azureアカウント作成手順
CentOS7+Apache+PHP+Redisセッションストア- Laravel5 画像アップロード
- Laravel 年月での集計処理 クエリビルダサンプル
- 【工事中】Laravel クエリビルダ ->where()まとめ
Nginx WordPress用パーマリンク
ベイズの定理
旅人算、流水算- Laravel Parse error composer autoload_static.php
Amazonおすすめ
iPad 9世代 2021年最新作
iPad 9世代出たから買い替え。安いぞ！🐱 初めてならiPad。Kindleを外で見るならiPad mini。ほとんどの人には通常のiPadをおすすめします＞＜