MySQL SHOWによる解析とチューニング

MySQL

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

RDS MySQL, PostgreSQL スロークエリ関連ログの出力

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(<カラム名>)

インデックス関連

MySQLのクエリチューニング、アーキテクチャとかメモ

SQL インデックスが効かない検索

チューニングツールの利用

MySQLTuner CentOS7

ファイルディスクリプタ

カーネルチューニングファイルディスクプリタ, TIME_WAIT CentOS7

ジェネラルログを出力する

フレームワークで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にした方が速い

優技録

MySQL SHOWによる解析とチューニング

DB, テーブルのDBエンジンの確認

グローバルパラメータの確認

エラー状況を確認

デッドロックロック待ちの確認

クエリキャッシュ

クエリキャッシュヒット状況の確認

クエリキャッシュ？

クエリキャッシュヒット率の確認

スロークエリ対応

EXPLAIN EXTENDEDによるクエリの解析

typeに注目する

良いtype

あまり良くないtype

インデックス関連

チューニングツールの利用

ファイルディスクリプタ

ジェネラルログを出力する

優技録のコンフィグ 4CPU 4GB

Windows7, 2008以上のMySQLの場合

関連記事 - More from my site -

コメントを残すコメントをキャンセル

DB, テーブルのDBエンジンの確認

グローバルパラメータの確認

エラー状況を確認

デッドロック ロック待ちの確認

クエリキャッシュ

クエリキャッシュヒット状況の確認

クエリキャッシュ？

クエリキャッシュヒット率の確認

スロークエリ対応

EXPLAIN EXTENDEDによるクエリの解析

typeに注目する

良いtype

あまり良くないtype

インデックス関連

チューニングツールの利用

ファイルディスクリプタ

ジェネラルログを出力する

優技録のコンフィグ 4CPU 4GB

Windows7, 2008以上のMySQLの場合

関連記事 - More from my site -

コメントを残す コメントをキャンセル

デッドロックロック待ちの確認

コメントを残すコメントをキャンセル