MySQLとPostgreSQLにおけるログの確認方法

TPCCなどをダウンロードして参照のみ、更新と参照、参照と更新の比率変更等して様々な負荷をかけてベンチマークする方が良いと思うが、カジュアルにベンチマークしたいケースもあると思います。毎回、同じ方法でベンチマークする事で、ある程度サーバーの構成変更時のパフォーマンスの変化を把握できるのでMySQLやPostgreSQLにバンドルされているベンチマークツールも便利なベンチマークオプションとして使える。取得したログを簡単に確認したり、実際に運用中のシステムのログも定期的に確認出来れば尚便利。そんなニーズに対して、カジュアルにメモしておきました。

PostgreSQL


postgres@ubuntu:~$ pgbench -i scaffold
NOTICE: table "pgbench_history" does not exist, skipping
NOTICE: table "pgbench_tellers" does not exist, skipping
NOTICE: table "pgbench_accounts" does not exist, skipping
NOTICE: table "pgbench_branches" does not exist, skipping
creating tables...
100000 of 100000 tuples (100%) done (elapsed 0.28 s, remaining 0.00 s)
vacuum...
set primary keys...
done.

postgres@ubuntu:~$ psql scaffold -c "\d"
List of relations
Schema | Name | Type | Owner
--------+------------------+-------+----------
public | pgbench_accounts | table | postgres
public | pgbench_branches | table | postgres
public | pgbench_history | table | postgres
public | pgbench_tellers | table | postgres
(4 rows)

postgres@ubuntu:~$

postgres@ubuntu:~$ pgbench -c 30 -t 1000 scaffold
starting vacuum...end.
transaction type:
scaling factor: 1
query mode: simple
number of clients: 30
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 30000/30000
latency average = 41.333 ms
tps = 725.808779 (including connections establishing)
tps = 726.545616 (excluding connections establishing)
postgres@ubuntu:~$

root@ubuntu:/var/log/postgresql# pgbadger postgresql-10-main.log
[========================>] Parsed 96663 bytes of 96663 (100.00%), queries: 0, events: 301
LOG: Ok, generating html report...
root@ubuntu:/var/log/postgresql# ls -l
total 1392
-rw-r--r-- 1 root root 1324353 May 24 10:34 out.html
-rw-r----- 1 postgres adm 96663 May 24 10:23 postgresql-10-main.log

pgbadger
*メモ:それぞれのパッケージは、aptもしくはyumでインストールしています。

pgbadgerのアウトプット
pgbadger

MySQL

負荷をかけてログを記録


[root@GA02 admin]# /usr/local/mysql/bin/mysqlslap --no-defaults --create-schema=SQLSLAP --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=InnoDB --number-int-cols=3 --number-char-cols=5 --concurrency=30 --auto-generate-sql-write-number=1000 --auto-generate-sql-execute-number=1000 --auto-generate-sql-load-type=mixed -u root -p
Enter password:
Benchmark
Running for engine InnoDB
Average number of seconds to run all queries: 19.724 seconds
Minimum number of seconds to run all queries: 19.724 seconds
Maximum number of seconds to run all queries: 19.724 seconds
Number of clients running queries: 30
Average number of queries per client: 1000

[root@GA02 admin]#

mysqldumpslow (MySQL Default)でログ解析


[root@GA02 data]# mysqldumpslow -s  c /usr/local/mysql/data/mysql-slow.log 

Reading mysql slow query log from /usr/local/mysql/data/mysql-slow.log
Count: 17349  Time=0.03s (463s)  Lock=0.00s (5s)  Rows=0.0 (0), root[root]@localhost
  INSERT INTO t1 VALUES (NULL,N,N,N,'S','S','S','S','S')

Count: 13375  Time=0.00s (2s)  Lock=0.00s (1s)  Rows=1.0 (13375), root[root]@localhost
  SELECT intcol1,intcol2,intcol3,charcol1,charcol2,charcol3,charcol4,charcol5 FROM t1 WHERE id =  'S'

Count: 31  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (30), root[root]@localhost
  #

Count: 31  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  administrator command: Quit

Percona Tool Kitでログ解析


[root@GA02 admin]# wget https://repo.percona.com/yum/percona-release-latest.noarch.rpm
--2020-05-24 13:51:20--  https://repo.percona.com/yum/percona-release-latest.noarch.rpm
repo.percona.com (repo.percona.com) をDNSに問いあわせています... 167.71.118.3, 157.245.119.64, 167.99.233.229
repo.percona.com (repo.percona.com)|167.71.118.3|:443 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 17684 (17K) [application/x-redhat-package-manager]
`percona-release-latest.noarch.rpm' に保存中

100%[==============================================================================================================================================================================================>] 17,684      --.-K/s 時間 0s      

2020-05-24 13:51:21 (51.5 GB/s) - `percona-release-latest.noarch.rpm' へ保存完了 [17684/17684]

[root@GA02 admin]# yum install percona-release-latest.noarch.rpm 
読み込んだプラグイン:fastestmirror, priorities
percona-release-latest.noarch.rpm を調べています: percona-release-1.0-18.noarch
次のリポジトリーへの更新として percona-release-latest.noarch.rpm を設定します: percona-release-0.1-4.noarch
依存性の解決をしています
--> トランザクションの確認を実行しています。
---> パッケージ percona-release.noarch 0:0.1-4 を 更新
---> パッケージ percona-release.noarch 0:1.0-18 を アップデート
--> 依存性解決を終了しました。

依存性を解決しました

========================================================================================================================================================================================================================================
 Package                                                  アーキテクチャー                                バージョン                                      リポジトリー                                                             容量
========================================================================================================================================================================================================================================
更新します:
 percona-release                                          noarch                                          1.0-18                                          /percona-release-latest.noarch                                           22 k

トランザクションの要約
========================================================================================================================================================================================================================================
更新  1 パッケージ

合計容量: 22 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  更新します              : percona-release-1.0-18.noarch                                                                                                                                                                           1/2 
* Enabling the Percona Original repository
<*> All done!
The percona-release package now contains a percona-release script that can enable additional repositories for our newer products.
For example, to enable the Percona Server 8.0 repository use:
  percona-release setup ps80
Note: To avoid conflicts with older product versions, the percona-release setup command may disable our original repository for some products.
For more information, please visit:
  https://www.percona.com/doc/percona-repo-config/percona-release.html
  整理中                  : percona-release-0.1-4.noarch                                                                                                                                                                            2/2 
  検証中                  : percona-release-1.0-18.noarch                                                                                                                                                                           1/2 
  検証中                  : percona-release-0.1-4.noarch                                                                                                                                                                            2/2 
更新:
  percona-release.noarch 0:1.0-18                                                                                                                                                                                                       
完了しました!
[root@GA02 admin]# 

[root@GA02 admin]# yum install percona-toolkit
読み込んだプラグイン:fastestmirror, priorities
percona-release-noarch                                                                                                                                                                                           | 2.9 kB  00:00:00     
percona-release-x86_64                                                                                                                                                                                           | 2.9 kB  00:00:00     
Loading mirror speeds from cached hostfile
 * base: mirrors.cat.net
 * epel: ftp.jaist.ac.jp
 * extras: mirrors.cat.net
 * updates: mirrors.cat.net
依存性の解決をしています
--> トランザクションの確認を実行しています。
---> パッケージ percona-toolkit.x86_64 0:3.2.0-1.el7 を インストール
--> 依存性解決を終了しました。

依存性を解決しました

========================================================================================================================================================================================================================================
 Package                                                  アーキテクチャー                                バージョン                                              リポジトリー                                                     容量
========================================================================================================================================================================================================================================
インストール中:
 percona-toolkit                                          x86_64                                          3.2.0-1.el7                                             percona-release-x86_64                                           12 M

トランザクションの要約
========================================================================================================================================================================================================================================
インストール  1 パッケージ

総ダウンロード容量: 12 M
インストール容量: 12 M
Is this ok [y/d/N]: y
Downloading packages:
警告: /var/cache/yum/x86_64/7/percona-release-x86_64/packages/percona-toolkit-3.2.0-1.el7.x86_64.rpm: ヘッダー V4 RSA/SHA256 Signature、鍵 ID 8507efa5: NOKEY========================================-] 184 kB/s |  12 MB  00:00:00 ETA 
percona-toolkit-3.2.0-1.el7.x86_64.rpm の公開鍵がインストールされていません
percona-toolkit-3.2.0-1.el7.x86_64.rpm                                                                                                                                                                           |  12 MB  00:00:50     
file:///etc/pki/rpm-gpg/PERCONA-PACKAGING-KEY から鍵を取得中です。
Importing GPG key 0x8507EFA5:
 Userid     : "Percona MySQL Development Team (Packaging key) <mysql-dev@percona.com>"
 Fingerprint: 4d1b b29d 63d9 8e42 2b21 13b1 9334 a25f 8507 efa5
 Package    : percona-release-1.0-18.noarch (@/percona-release-latest.noarch)
 From       : /etc/pki/rpm-gpg/PERCONA-PACKAGING-KEY
上記の処理を行います。よろしいでしょうか? [y/N]y
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  インストール中          : percona-toolkit-3.2.0-1.el7.x86_64                                                                                                                                                                      1/1 
  検証中                  : percona-toolkit-3.2.0-1.el7.x86_64                                                                                                                                                                      1/1 
インストール:
  percona-toolkit.x86_64 0:3.2.0-1.el7                                                                                                                                                                                                  
完了しました!
[root@GA02 admin]# 

pt-query-digestのアウトプット


[root@GA02 data]# pt-query-digest /usr/local/mysql/data/mysql-slow.log --no-version-check

# 20.9s user time, 50ms system time, 31.58M rss, 232.37M vsz
# Current date: Sun May 24 14:19:42 2020
# Hostname: GA02
# Files: /usr/local/mysql/data/mysql-slow.log
# Overall: 30.76k total, 9 unique, 242.22 QPS, 3.73x concurrency _________
# Time range: 2020-05-24T05:07:09 to 2020-05-24T05:09:16
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           474s     1us   221ms    15ms    34ms    15ms    20ms
# Lock time             7s       0   179ms   230us   332us     4ms   103us
# Rows sent         14.04k       0     999    0.47    0.99    5.52       0
# Rows examine      14.04k       0     999    0.47    0.99    5.52       0
# Query size        12.96M      17     707  441.72  685.39  291.70  685.39

# Profile
# Rank Query ID                        Response time  Calls R/Call V/M   I
# ==== =============================== ============== ===== ====== ===== =
#    1 0x76F7DD6D2A72EB7BCAEC22E79B... 468.9853 99.0% 17349 0.0270  0.00 INSERT t?
# MISC 0xMISC                            4.5256  1.0% 13413 0.0003   0.0 <8 ITEMS>

# Query 1: 667.27 QPS, 18.04x concurrency, ID 0x76F7DD6D2A72EB7BCAEC22E79B0EFD3B at byte 988485
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2020-05-24T05:08:50 to 2020-05-24T05:09:16
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         56   17349
# Exec time     99    469s     1ms   221ms    27ms    38ms    11ms    26ms
# Lock time     75      5s       0   179ms   309us   384us     5ms   108us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size    90  11.67M     704     707  705.36  685.39       0  685.39
# String:
# Databases    SQLSLAP
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ###
#  10ms  ################################################################
# 100ms  #
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `SQLSLAP` LIKE 't1'\G
#    SHOW CREATE TABLE `SQLSLAP`.`t1`\G
INSERT INTO t1 VALUES (NULL,633998215,1598162325,1839677468,'l4dgQpQ2FFmHegccMlq0FmNIrmjthL1IPWzrCHuRYgk2KDWaqKb57qNyczrAKlSl7JcJcMe8uN26SYhItGF0yXybWhdqQxRQHvltIGuCfwJzTs9M0OEyv4PHl274sQ','W9M7ugNokudvtyn2CnJQmEMBO76N3yqZzCZtkwaSrBGc93XDjqfLwtOK0WkwKaLJeu5MrfiaiyAjuZwdNRyKKMYClG85iMHKGMaztHPR7SA11yWPPKddj9pKQqH8CQ','LKgvKZ4d0C52570cOHw1uXAkjSQ1TeJqy0lAPqoQ2lKzsLR8WG9PnC1NYRG1xbrvbdy1ReJLNfC8sgGFwIgbMhyu0EwxinsHGiAbKLoct1klhs0d2Y4O53J5972run','13L3eiPRAISuv1eoWhKaxQTGNfFIMA5F5QJj0k2A2WwxNRlt8yS5GpEfSTxjXuycD9CDtEGnd4d0XEufCEdS3j0O4p7ckx4x7G2sNAg0FH0mXmIc0nwwWoKaeJ2E87','3FFx0e8YWNpN1C9JgA6T6SHI3vdx1lw4tC2liAH5PySQ2dejfcCd5Lv0HOxA2uFnyHYYJhdcf52ZbYGE9KkEifErw31qx8dnH1KSax4DvzmWXvcY7KmhssOowIWlQz')\G
[root@GA02 data]# 

MySQLのエラーログ含めてGUIで確認するのであればこんなツールもあります。
Percona Monitoring and Management
MySQL Enterprise Monitor


MySQLとPostgreSQLにおける重い処理をシステムテーブル(view)から確認する方法

MySQL, PostgreSQLそれぞれ運用するなかで、遅い処理を確認する為にlog_min_duration_statementやslow_query_logを利用して該当のSQLをログに出力して適宜SQLをチューニングする等の対応されていると思います。ここでは、その他にMySQLとPostgreSQLでそれぞれ提供されている、管理用のVIEWを利用して実行されたSQLとその処理の詳細を確認しています。

log_min_duration_statement@PostgreSQL

このパラメータに設定した時間(ミリ秒)以上の処理時間を要したSQLは、そのSQL文とその所要時間をログに出力します。
例えば 10秒以上かかるクエリを特定するには、設定ファイルpostgresql.conf にて以下のように設定を行います。

log_min_duration_statement=10000

long_query_time@MySQL

slow_query_logとslow_query_log_fileシステム変数を使用します。slow_query_logログを無効にするには0に設定し有効にするには1に設定します。設定ファイル my.cnf にて以下のようにlong_query_timeにて所要時間(秒)の設定を行います。また、slow_query_log_fileログファイルの名前を指定するように設定します。これらは、動的パラメータなので稼働中もSETコマンドで設定する事が可能です。

slow_query_log = 1
slow_query_log_file = /usr/local/mysql/data/host-ip-slow.log
long_query_time = 10

sys.statement_analysis@MySQL

これらのビューは統計を集計し正規化したステートメントをリストします。デフォルトで行は合計レイテンシを降順にソートされています。

27.4.3.35 The statement_analysis and x$statement_analysis Views


[sys]> select db,query,full_scan,exec_count,err_count,warn_count,total_latency,max_latency,avg_latency,lock_latency,rows_sent,tmp_disk_tables,sort_merge_passes from statement_analysis limit 10;
+-------+-------------------------------------------------------------------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+--------------
| db    | query                                                             | full_scan | exec_count | err_count | warn_count | total_latency | max_latency | avg_latency | lock_latency | rows_sent | tmp_disk_tabl
+-------+-------------------------------------------------------------------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+--------------
| APP   | SELECT `WORD` FROM `INFORMATIO ... INNODB_FT_INDEX_TABLE` LIMIT ? | *         |          1 |         0 |          0 | 11.84 min     | 11.84 min   | 11.84 min   | 7.95 ms      |        10 |              
| APP   | SELECT `WORD` , COUNT ( `WORD` ...  ORDER BY `total` DESC LIMIT ? | *         |          1 |         1 |          0 | 9.24 min      | 9.24 min    | 9.24 min    | 153.00 us    |         0 |              
| APP   | COMMIT                                                            |           |      14300 |         0 |          0 | 3.02 min      | 850.07 ms   | 12.66 ms    |   0 ps       |         0 |              
| APP   | SELECT SQL_NO_CACHE * FROM `FTS_dbdata`                           | *         |          3 |         0 |          3 | 29.54 s       | 11.67 s     | 9.85 s      | 6.14 ms      |    833154 |              
| myosm | SELECT SQL_NO_CACHE * FROM `nodes`                                | *         |          3 |         0 |          3 | 23.93 s       | 15.15 s     | 7.98 s      | 137.00 us    |   1486554 |              
| APP   | INSERT IGNORE INTO `FTS_dbdata ... TEM_USER , TEXT ) VALUES (...) |           |      14300 |         0 |          0 | 20.06 s       | 895.94 ms   | 1.40 ms     | 5.07 s       |         0 |              
| myosm | SELECT SQL_NO_CACHE * FROM `nodetags`                             | *         |          3 |         0 |          3 | 19.41 s       | 6.57 s      | 6.47 s      | 127.00 us    |   8332701 |              
| APP   | SELECT SQL_NO_CACHE * FROM `FTS_dbda`                             | *         |          3 |         0 |          3 | 17.62 s       | 6.21 s      | 5.87 s      | 130.00 us    |    852759 |              
| APP   | SELECT SQL_NO_CACHE * FROM `FTS_dbdaa`                            | *         |          3 |         0 |          3 | 9.96 s        | 4.23 s      | 3.32 s      | 131.00 us    |    455574 |              
| APP   | SET NAMES ? COLLATE ?                                             |           |      14300 |         0 |          0 | 7.77 s        | 198.99 ms   | 543.57 us   |   0 ps       |         0 |              
+-------+-------------------------------------------------------------------+-----------+------------+-----------+------------+---------------+-------------+-------------+--------------+-----------+--------------
10 rows in set (0.00 sec)

statement_analysisはevents_statements_summary_by_digestを参照しているので、テーブルに格納されるステートメントは以下のサイズに依存しています。


[sys]> show global variables like 'performance_schema_digests_size';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| performance_schema_digests_size | 10000 |
+---------------------------------+-------+

補足: MySQL SYS Schema


pg_stat_statements@PostgreSQL

pg_stat_statementsモジュールはサーバで実行されたすべてのSQL文の実行時の統計情報を記録する手段を提供します。このモジュールは追加の共有メモリを必要とするため、postgresql.confのshared_preload_librariesにpg_stat_statementsを追加してモジュールをロードしなければなりません。 このことは、このモジュールを追加もしくは削除するにはサーバを再起動する必要があるということを意味しています。

pg_stat_statementsはロードされると、サーバのデータベース全体に渡って統計情報を記録します。 この統計情報にアクセスしたり操作したりするために、このモジュールはビューpg_stat_statementsとユーティリティ関数pg_stat_statements_reset、pg_stat_statementsを提供します。 これらはサーバー全体で利用可能ではなく、CREATE EXTENSION pg_stat_statementsで特定のデータベースで利用可能になります。

参照: F.29. pg_stat_statements

postgresql.confの設定変更(サービスの再起動が必要)


shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 1000
pg_stat_statements.track = top
pg_stat_statements.save = on

設定と確認

app=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION

app=# \dx
                                        インストール済みの拡張一覧
        名前        | バージョン |  スキーマ  |                           説明                            
--------------------+------------+------------+-----------------------------------------------------------
 pg_freespacemap    | 1.2        | public     | examine the free space map (FSM)
 pg_stat_statements | 1.7        | public     | track execution statistics of all SQL statements executed
 plpgsql            | 1.0        | pg_catalog | PL/pgSQL procedural language
(3 行)

app=# SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';
        name        | default_version | installed_version |                          comment                          
--------------------+-----------------+-------------------+-----------------------------------------------------------
 pg_stat_statements | 1.7             | 1.7               | track execution statistics of all SQL statements executed
(1 行)


app=# select name, setting, unit from pg_settings where name like 'pg_stat_statements%';
               name               | setting | unit 
----------------------------------+---------+------
 pg_stat_statements.max           | 5000    | 
 pg_stat_statements.save          | on      | 
 pg_stat_statements.track         | top     | 
 pg_stat_statements.track_utility | on      | 
(4 行)

実際に記録されたステートメントの確認


app=# \pset pager
「ページャーを使う」は off です。
app=# select userid,dbid,queryid,left(query,30),calls,total_time,min_time,max_time,mean_time,stddev_time,shared_blks_hit,shared_blks_read from pg_stat_statements order by total_time desc;
 userid | dbid  |       queryid        |              left              | calls |      total_time      |       min_time        |       max_time       |      mean_time       |      stddev_time       | shared_blks_hit | shared_blks_read 
--------+-------+----------------------+--------------------------------+-------+----------------------+-----------------------+----------------------+----------------------+------------------------+-----------------+------------------
     10 | 49596 |  5910544648824246769 | select * from memo where id =  |    16 |   352.84530299999994 |              0.016142 |   351.54439399999995 |   22.052831437500004 |      85.07473611928185 |              44 |                4
     10 | 49596 |  6711476591887504002 | explain select * from memo     |     2 |           105.592792 |              0.074625 |           105.518167 |            52.796396 |     52.721771000000004 |              40 |               12
     10 | 49596 |  1258245615633632122 | explain select * from memo whe |     6 |    61.98016300000001 |              0.084408 |            61.497362 |   10.330027166666667 |      22.88272889325042 |               4 |                9
     10 | 49596 |  5761671877250351757 | select name,setting,category f |     3 |   5.1763959999999996 |              1.102193 |             2.606261 |   1.7254653333333332 |     0.6404653712024995 |               0 |                0
     10 | 49596 |    -3158048787087772 | select userid,dbid,queryid,lef |     3 |              0.41186 |   0.13373200000000002 |  0.14129799999999998 |  0.13728666666666667 |  0.0031056397444362608 |               0 |                0
     10 | 49596 |   956172393795458702 | select * from pg_stat_statemen |     3 |  0.26237699999999997 |  0.009907000000000001 |             0.129361 |             0.087459 |   0.054896911898575866 |               0 |                0
     10 | 49596 | -9208585654704159892 | explain select * from memo ord |     3 |             0.250689 |              0.073775 |             0.097503 |             0.083563 |   0.010122049331368954 |               4 |                0
     10 | 49596 |  3231151743520748149 | select userid,dbid,queryid,que |     2 |  0.21030100000000002 |              0.102633 |             0.107668 |  0.10515050000000001 |  0.0025174999999999954 |               0 |                0
     10 | 49596 |  8685157009571086729 | select userid,dbid,queryid,lef |     1 |  0.15841100000000002 |   0.15841100000000002 |  0.15841100000000002 |  0.15841100000000002 |                      0 |               3 |                0
     10 | 49596 |  4048041203833672862 | LOAD 'auto_explain'            |     1 |             0.125106 |              0.125106 |             0.125106 |             0.125106 |                      0 |               0 |                0
     10 | 49596 | -7547750407391136812 | explain select * from memo ord |     1 |             0.098115 |              0.098115 |             0.098115 |             0.098115 |                      0 |               0 |                0
     10 | 49596 |  6755220269130009187 | explain select * from memo ord |     1 |             0.087065 |              0.087065 |             0.087065 |             0.087065 |                      0 |               0 |                0
     10 | 49596 | -1920304080562092233 | SET auto_explain.log_min_durat |     3 |             0.064131 |              0.006951 |             0.049859 |             0.021377 |   0.020140381790489142 |               0 |                0
     10 | 49596 |  9022527725580902277 | select relname,relpages,reltup |     3 |             0.062013 |  0.017379000000000002 |             0.026977 | 0.020671000000000002 |   0.004460459468111628 |               9 |                0
     10 | 49596 | -7365002070969254732 | SELECT tablename,attname,corre |     1 | 0.051623999999999996 |  0.051623999999999996 | 0.051623999999999996 | 0.051623999999999996 |                      0 |              15 |                0
     10 | 49596 | -2189995260333419544 | select ($1 * $2) + ($3 * $4) " |     3 |             0.028727 | 0.0046240000000000005 |             0.019399 | 0.009575666666666666 |    0.00694622239462253 |               0 |                0
     10 | 49596 | -6203021216765832473 | SET auto_explain.log_min_durat |     3 | 0.022913999999999997 |              0.007163 |             0.007885 |             0.007638 | 0.00033596527598349574 |               0 |                0
     10 | 49596 |  2065125607359684573 | SET auto_explain.log_min_durat |     2 |             0.015098 |              0.007356 |             0.007742 |             0.007549 |  0.0001929999999999998 |               0 |                0
     10 | 49596 |  7413539732739831561 | SET auto_explain.log_min_durat |     1 |             0.007563 |              0.007563 |             0.007563 |             0.007563 |                      0 |               0 |                0
     10 | 49596 |  6161394367587035080 | SET auto_explain.log_analyze = |     1 |             0.007052 |              0.007052 |             0.007052 |             0.007052 |                      0 |               0 |                0
     10 | 49596 |  8733460176246631674 | select ($1 * $2) + ($3 * $4) + |     1 |             0.005196 |              0.005196 |             0.005196 |             0.005196 |                      0 |               0 |                0
(21 行)

app=# 


pg_stat_statements.maxは、このモジュールによって記録されるSQL文の最大数(すなわち、pg_stat_statementsビューにおける行の最大数)です。
これを超えて異なるSQL文を検出した場合は、最も実行回数の低いSQL文の情報が捨てられます。 デフォルトは5000です。 このパラメータはサーバの起動時にのみ指定できます。

app=# select name,setting,unit,context,category,short_desc from pg_settings where name like 'pg_stat_statements%';
               name               | setting | unit |  context   |    category    |                              short_desc                              
----------------------------------+---------+------+------------+----------------+----------------------------------------------------------------------
 pg_stat_statements.max           | 5000    |      | postmaster | 独自オプション | Sets the maximum number of statements tracked by pg_stat_statements.
 pg_stat_statements.save          | on      |      | sighup     | 独自オプション | Save pg_stat_statements statistics across server shutdowns.
 pg_stat_statements.track         | top     |      | superuser  | 独自オプション | Selects which statements are tracked by pg_stat_statements.
 pg_stat_statements.track_utility | on      |      | superuser  | 独自オプション | Selects whether utility commands are tracked by pg_stat_statements.
(4 行)


MySQLにしてもPostgreSQLにしてもある程度のデータを扱うのであれば、インデックスが利用出来るようなwhere句を書いて処理の最適化を行うと思いますので、MySQLとPostgreSQLのインデックスのメンテナンスをメモとして記録。

インデックスメンテナンス@MySQL

フラグメンテーションの確認

SELECT 
table_schema as `Database`, 
table_name AS `Table`, 
round(((data_length + index_length) / 1024 / 1024), 2) `Size_in_MB`,
round((DATA_FREE/1024/1024),2) 'Free_in_MB',
(round((DATA_FREE/1024/1024),2)/round(((data_length + index_length) / 1024 / 1024), 2)) * 100 as 'Fragment(%)'
FROM information_schema.TABLES where table_schema = 'test' ORDER BY (data_length + index_length) limit 10;

ONLINEで処理するのであればALTERコマンドで良いでしょう。

root@localhost [APP]> ALTER TABLE T_scripts engine = InnoDB, ALGORITHM=INPLACE,LOCK=NONE;
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [APP]> 

FTSを設定している場合はALGORITHM=INPLACE,LOCK=NONEオプションは利用できません。

OPTIMIZEでも可能ですが、5.6.17までは参照ロックがかかるので更新処理が停止してしまいます。

For InnoDB tables prior to 5.6.17 and other table types, MySQL locks the table during the time. OPTIMIZE TABLE is running. As of MySQL 5.6.17, OPTIMIZE TABLE is performed online for regular and partitioned InnoDB tables.


root@localhost [APP]> optimize table T_scripts;
+---------------+----------+----------+----------+
| Table         | Op       | Msg_type | Msg_text |
+---------------+----------+----------+----------+
| APP.T_scripts | optimize | status   | OK       |
+---------------+----------+----------+----------+
1 row in set (0.00 sec)

root@localhost [APP]> 

データベース全体


[ec2-user]$ mysqlcheck --optimize APP -u root -p
Enter password: 
APP.FTS_Tweets                                     OK
APP.FTS_Tweets2                                    OK
APP.FTS_Tweets3                                    OK
APP.T_Article                                      OK
APP.T_Articles                                     OK
APP.users                                          OK
[ec2-user]$ 

インデックスメンテナンス@PostgreSQL

### 特定のINDEXのメンテンナンス

tasks_db=# reindex index index_tasks_on_user_id;
REINDEX

特定のテーブルのインデックスメンテンナンス

tasks_db=# reindex table tasks;
REINDEX
tasks_db=# 

特定データベースのINDEXメンテンナンス

tasks_db=# reindex database tasks_db;
NOTICE:  table "pg_catalog.pg_class" was reindexed
NOTICE:  table "pg_catalog.pg_statistic" was reindexed
NOTICE:  table "pg_catalog.pg_type" was reindexed
NOTICE:  table "public.schema_migrations" was reindexed
<SNIP>
NOTICE:  table "public.active_storage_attachments" was reindexed
NOTICE:  table "pg_catalog.pg_cast" was reindexed
NOTICE:  table "pg_catalog.pg_foreign_table" was reindexed
NOTICE:  table "information_schema.sql_features" was reindexed
REINDEX
tasks_db=# 

特定データベースのシステムカタログのINDEXメンテンナンス

tasks_db=# reindex system tasks_db;
NOTICE:  table "pg_catalog.pg_class" was reindexed
NOTICE:  table "pg_catalog.pg_statistic" was reindexed
NOTICE:  table "pg_catalog.pg_type" was reindexed
<SNIP>
NOTICE:  table "pg_catalog.pg_range" was reindexed
NOTICE:  table "pg_catalog.pg_largeobject" was reindexed
NOTICE:  table "pg_catalog.pg_attrdef" was reindexed
NOTICE:  table "pg_catalog.pg_index" was reindexed
NOTICE:  table "pg_catalog.pg_cast" was reindexed
NOTICE:  table "pg_catalog.pg_foreign_table" was reindexed
REINDEX
tasks_db=# 

verboseオプションによるステータス表示
メモ:Clusterコマンドでは物理配列の復活

app=# reindex(verbose) system memo;
ERROR:  現在オープンしているデータベースのみをインデックス再構築することができます
app=# reindex(verbose) table memo;
INFO:  インデックス"idx_memo_id"のインデックス再構築が完了しました
DETAIL:  CPU: ユーザ: 0.00秒、システム: 0.00秒、経過時間: 0.00秒
INFO:  インデックス"pg_toast_49764_index"のインデックス再構築が完了しました
DETAIL:  CPU: ユーザ: 0.00秒、システム: 0.00秒、経過時間: 0.00秒
REINDEX
app=# cluster memo using idx_memo_id;
CLUSTER
app=# 

REINDEX中はテーブルがロックされるので、更新処理はできません。ロックの影響を避けたい場合は、CONCURRENTLYオプションを付けると対象テーブルのロックを取得しないため、実行中も対象テーブルの更新が可能です。
メンテナンスしたいINDEXと同じインデックスをCONCURRENTLYで作成して作成後に古いINDEXを削除する。(オンラインメンテナンス)

app=# \d
         List of relations
 Schema |  Name  | Type  |  Owner   
--------+--------+-------+----------
 public | t_post | table | app_user
(1 row)

app=# \d t_post;
            Table "public.t_post"
 Column |          Type          | Modifiers 
--------+------------------------+-----------
 note   | character varying(100) | 

app=# create index concurrently idx_note on t_post(note);
CREATE INDEX
app=# 

<運用してインデックスのフラグメンテーション発生>

app=# create index concurrently idx_note_new on t_post(note);
CREATE INDEX
app=# \d t_post;
            Table "public.t_post"
 Column |          Type          | Modifiers 
--------+------------------------+-----------
 note   | character varying(100) | 
Indexes:
    "idx_note" btree (note)
    "idx_note_new" btree (note)

app=# drop index idx_note;
DROP INDEX
app=# 

Clustered Indexについて

MySQLではClusterインデックスとしてPrimary KeyやUnique Index(NOT NULL)がクラスター化されクラスター性を保ちますが、
PostgreSQLではClusterを実施した直後のテーブルはCluster化されテーブルデータの並び順はインデックスの順番になりますが、
そのあと運用していく中でクラスター性が無くなっていきます。

CLUSTERインデックス@MySQL
すべての InnoDB テーブルは、行のデータが格納されているクラスタ化されたインデックスと呼ばれる特別なインデックスを持っています。一般に、クラスタ化されたインデックスは主キーのシノニムです。クエリー、挿入、およびその他のデータベース操作で最適なパフォーマンスを実現するには、InnoDB がクラスタ化されたインデックスを使用して、テーブルごとにもっとも一般的な検索と DML 操作を最適化する方法について理解する必要があります。テーブル上で PRIMARY KEY を定義すると、InnoDB ではそれがクラスタ化されたインデックスとして使用されます。作成するテーブルごとに主キーを定義します。論理的に一意で、Null 以外のカラムまたはカラムのセットが存在しない場合は、自動的に値が入力される新しい自動インクリメントカラムを追加します。

テーブルに PRIMARY KEY が定義されていない場合、MySQL はすべてのキーカラムが NOT NULL の UNIQUE インデックスを最初に検索し、InnoDB はそれをクラスタ化されたインデックスとして使用します。テーブルに PRIMARY KEY も適切な UNIQUE インデックスも存在しない場合には、InnoDB の内部で、行 ID 値を含む合成カラム上に非表示のクラスタ化されたインデックスが生成されます。そのようなテーブルでは、InnoDB が行に割り当てる ID に基づいて行の順序付けが行われます。行 ID は、新しい行が挿入されると単調に増加する 6 バイトのフィールドです。したがって、行 ID で順序付けられた行が物理的な挿入順になります。

CLUSTERインデックス@PostgreSQL

テーブルがクラスタ化されると、それぞれのテーブルはインデックス情報に基づいて物理的に並べ直されます。クラスタ化は、1回限りの操作です。 クラスタ化後にテーブルが更新されても、その変更はクラスタ化されません。 つまり、新規に追加された行や更新された行は、インデックス順には保管されません。 (インデックス順に保管したい場合は、コマンドを再度入力し、定期的に再クラスタ化を行います。 また、更新される行は十分な領域が利用可能ならば同一ページ内に保持されますので、テーブルのFILLFACTOR格納パラメータを100%より小さく設定することで、更新処理中のクラスタ順序付けを保護するという点で役に立ちます。)

テーブルがクラスタ化されると、PostgreSQLはクラスタ化に使用されたインデックスを記録します。 CLUSTER table_nameという構文によって、以前と同じインデックスを使用してテーブルを再クラスタ化します。 またALTER TABLEのCLUSTERもしくはSET WITHOUT CLUSTER構文を使用して、将来のクラスタ化操作で使用するインデックスを設定したり、任意の過去の設定を取り消すことができます。

Clusterコマンドを実行すると、Reindexも実行されるので断片化とクラスター性の改善を同時に実施可能です。(Vacuum Fullと同等)
注意:一時的に対象テーブル・インデックスと同等のディスク容量が必要 (MySQLのAlter table algorithm=CPOPYと同等)
   Clusterコマンド実行時には排他ロックがかかります。(大きなテーブルの場合は事前に検証が必要)

app=# reindex(verbose) system memo;
ERROR:  現在オープンしているデータベースのみをインデックス再構築することができます
app=# reindex(verbose) table memo;
INFO:  インデックス"idx_memo_id"のインデックス再構築が完了しました
DETAIL:  CPU: ユーザ: 0.00秒、システム: 0.00秒、経過時間: 0.00秒
INFO:  インデックス"pg_toast_49764_index"のインデックス再構築が完了しました
DETAIL:  CPU: ユーザ: 0.00秒、システム: 0.00秒、経過時間: 0.00秒
REINDEX
app=# cluster memo using idx_memo_id;
CLUSTER
app=# cluster memo;
CLUSTER
app=# \d memo;
                         テーブル"public.memo"
  列   |          型           | 照合順序 | Null 値を許容 | デフォルト 
-------+-----------------------+----------+---------------+------------
 id    | integer               |          |               | 
 data  | text                  |          |               | 
 data2 | character varying(20) |          |               | 
インデックス:
    "idx_memo_id" btree (id) CLUSTER

app=# select relname from pg_class where oid in (select indexrelid from pg_class c,pg_index i where c.oid = i.indrelid and i.indisclustered = 't');
   relname   
-------------
 idx_memo_id
(1 行)

app=# \d memo;
                         テーブル"public.memo"
  列   |          型           | 照合順序 | Null 値を許容 | デフォルト 
-------+-----------------------+----------+---------------+------------
 id    | integer               |          |               | 
 data  | text                  |          |               | 
 data2 | character varying(20) |          |               | 
インデックス:
    "idx_memo_id" btree (id) CLUSTER

app=# alter table memo set without cluster;
ALTER TABLE
app=# \d memo;
                         テーブル"public.memo"
  列   |          型           | 照合順序 | Null 値を許容 | デフォルト 
-------+-----------------------+----------+---------------+------------
 id    | integer               |          |               | 
 data  | text                  |          |               | 
 data2 | character varying(20) |          |               | 
インデックス:
    "idx_memo_id" btree (id)

app=# alter table memo cluster on idx_memo_id;
ALTER TABLE
app=# \d memo;
                         テーブル"public.memo"
  列   |          型           | 照合順序 | Null 値を許容 | デフォルト 
-------+-----------------------+----------+---------------+------------
 id    | integer               |          |               | 
 data  | text                  |          |               | 
 data2 | character varying(20) |          |               | 
インデックス:
    "idx_memo_id" btree (id) CLUSTER

app=# 

その他:
WL#6747: InnoDB: make fill factor settable
innodb_fill_factor
第三回 HOTの上手な使い方


DB Tech Showcase 2018 Tokyo

DB Tech Showcase 2018 TokyoにてInnoDB Clusterを用いた、MySQLの高可用性構成への取り組みに関して紹介させて頂きました。プロダクションのデータベースなので、これまでの機能紹介やシンプルなデモとは異なり、様々な確認や調整が必要で、DBのアップグレード以上に時間を割いている感じです。移行後は、シンプルにマスターを切り替える事で、MySQL Routerが自動的に接続を切り替えてくれるので非常に楽になります。
また、作業工数も大幅に削減出来るので、会社にとってもダウンタイムによる機会損失削減といったメリットだけでなく、エンジニアの工数削減によりサービス開発に専念が出来るようになり非常に良いソリューションだと考えています。

これからもMySQLだけでは無く、様々な高可用性構成をコストと運用の観点から考察して、順次導入していきたいと思っています。
システム移行と同時に様々な、改善も並行して出来るので1年を目途に最適化が出来ると良いかなと思ってます。

“MySQL5.6から5.7へ、そして更なるサービスの可用性を目指して ~急成長する靴の通販 LOCONDO.jp を支えるサービス安定化への取り組み”

補足:
資料には明記してませんが、シングルプライマリーモードで構成しているので、グループレプリケーション構成直後に以下の値を全て1に変更しています。


group_replication_auto_increment_increment 
auto_increment_increment
auto_increment_offset

Group Replicationのマスター、スレーブ間のレプリケーションラグの確認方法

select @last_exec:=SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX( @@global.GTID_EXECUTED,':',-1),':',1),'-',-1) last_executed;select @last_rec:=SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX( Received_transaction_set,':',-1),':',1),'-',-1) last_received FROM performance_schema.replication_connection_status WHERE Channel_name = 'group_replication_applier';select (@last_rec - @last_exec) as real_lag;

補足:RECOVERY中は値が正確に取得出来ない様子
https://bugs.mysql.com/bug.php?id=92219


MySQL8.0 SYSスキーマの確認。 
基本的には、MySQL5.7と変わりは無いですが、ロックを取得するVIEW内部のテーブルがInformation_schemaからPerformance_Schemaに変更になったりしている。但し、VIEW内部のスクリプト変更なので基本的に問題無く、これまで通り利用する事が可能です。

オフィシャルマニュアル: Chapter 26 MySQL sys Schema

確認したバージョン


root@localhost [mysql]> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.11    |
+-----------+
1 row in set (0.00 sec)

root@localhost [mysql]> 

ドキュメント


MySQL5.7までのSHOW SLAVE STATUSだけでは分からない事が多かったけど、MySQL8.0のSHOW SLAVE STATUSは少し改善されていた。
マスター側で負荷をかけて、スレーブの状態を確認した時にスレーブ側で”Systetm lock”という状態になっていて、詳細を確認する為にPerformance Schemaを確認してみた。
MySQL8.0からはPerformance_Schemaを確認しなくても”Slave_SQL_Running_State”で状態が確認出来るようになっている。
以下、MySQL8.0で確認したログですが、MySQL5.7では”System lock”だった状態が、MySQL8.0では”Applying batch of row changes (update)”になっています。

[admin@misc02 ~]$ cat repli_log | grep Slave_SQL_Running_State
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Slave_SQL_Running_State: Reading event from the relay log
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Slave_SQL_Running_State: Reading event from the relay log
Slave_SQL_Running_State: Reading event from the relay log
Slave_SQL_Running_State: Reading event from the relay log
Slave_SQL_Running_State: Applying batch of row changes (update)
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
[admin@misc02 ~]$

詳細: WL#7364: RBR: Enhanced Applier Thread Progress Details
https://dev.mysql.com/worklog/task/?id=7364

以下、MySQL5.7のケース
マスター側でmysqlslapで負荷をかけると、スレーブ側がsystem lockになっている。
スレーブ側でトランザクション実行して、UPLOCKかけて参照しても、同じsystem lockになる。
これだと、I/O Threadに遅延が無いことは分かるが、Applier Threadの状態が良く分からない。

[ON MASTER] – MySQL5.7

root@localhost [REPLI]> show create table T_SLAP01\G
*************************** 1. row ***************************
       Table: T_SLAP01
Create Table: CREATE TABLE `T_SLAP01` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `n_time` varchar(30) DEFAULT NULL,
  `s_time` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

root@localhost [REPLI]> 

[root@misc01 admin]# cat mysqlslap_exsisting_table.sh 
/usr/local/mysql/bin/mysqlslap -u root -p -S /tmp/mysql.sock --create-schema=REPLI --no-drop -c 10 -i 1000 -q 'INSERT INTO REPLI.T_SLAP01(n_time,s_time) values(now(3),sysdate(6))' 
[root@misc01 admin]# 

[root@misc01 admin]# ./mysqlslap_exsisting_table.sh 
Enter password: 
Benchmark
        Average number of seconds to run all queries: 0.046 seconds
        Minimum number of seconds to run all queries: 0.018 seconds
        Maximum number of seconds to run all queries: 0.135 seconds
        Number of clients running queries: 10
        Average number of queries per client: 1

[root@misc01 admin]# 

[ON SLAVE]
上記マスターで負荷をかけた場合の、スレーブ側の状態。 “System lock”という状態になっている。ただし、この状態はApplier Threadが止まっている訳では無く、何らかの処理を行っている状態の場合もある。


root@localhost [REPLI]> show processlist; show engine innodb status\G show slave status\G
+-----+-----------------+--------------+-------+---------+------+----------------------------------+------------------+
| Id  | User            | Host         | db    | Command | Time | State                            | Info             |
+-----+-----------------+--------------+-------+---------+------+----------------------------------+------------------+
|   1 | system user     |              | NULL  | Connect |  888 | Waiting for master to send event | NULL             |
|   2 | system user     |              | NULL  | Connect |   -1 | System lock                      | NULL             |
|   3 | event_scheduler | localhost    | NULL  | Daemon  |  887 | Waiting on empty queue           | NULL             |
|  13 | root            | localhost    | REPLI | Query   |    0 | starting                         | show processlist |
|  29 | admin           | Labs01:44555 | mysql | Sleep   |    3 |                                  | NULL             |
| 763 | admin           | Labs01:47755 | mysql | Sleep   |   55 |                                  | NULL             |
+-----+-----------------+--------------+-------+---------+------+----------------------------------+------------------+
6 rows in set (0.00 sec)

*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2018-03-23 15:32:58 0x7fcdb65f0700 INNODB MONITOR OUTPUT
=====================================
<SNIP>
------------
TRANSACTIONS
------------
Trx id counter 756832
Purge done for trx's n:o < 756831 undo n:o < 0 state: running but idle
History list length 11
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421997346109728, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421997346107904, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421997346106992, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421997346106080, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 756831, ACTIVE (PREPARED) 0 sec
3 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2
MySQL thread id 2, OS thread handle 140521468139264, query id 69415 System lock

<SNIP>

--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=8340, Main thread ID=140522077779712, state: sleeping
Number of rows inserted 31953, updated 30756, deleted 0, read 32044
72.99 inserts/s, 72.99 updates/s, 0.00 deletes/s, 72.99 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.01 sec)

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.113
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.002848
          Read_Master_Log_Pos: 59249068
               Relay_Log_File: misc02-relay-bin.000766
                Relay_Log_Pos: 12937379
        Relay_Master_Log_File: mysql-bin.002848
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
<SNIP>
             Master_Server_Id: 1
                  Master_UUID: 2dde009f-d4dc-11e4-b437-0800279cea3c
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: System lock
           Master_Retry_Count: 86400
                  Master_Bind: 
<SNIP>
1 row in set (0.00 sec)

root@localhost [REPLI]> 

SlaveのPerfomance_Schemaの状態。
Replication SlaveのIDで確認してみると、statement/sql/beginという状態である事が分かる。

root@localhost [REPLI]> SELECT  event_id, event_name, timer_wait, lock_time FROM performance_schema.events_statements_history_long WHERE thread_id =  @sql_thread;
+----------+---------------------+------------+-----------+
| event_id | event_name          | timer_wait | lock_time |
+----------+---------------------+------------+-----------+
|    10003 | statement/sql/begin |  175076000 |         0 |
+----------+---------------------+------------+-----------+
1 row in set (0.01 sec)


root@localhost [REPLI]> SELECT event_name, count_star, sum_timer_wait FROM performance_schema.events_waits_summary_by_thread_by_event_name WHERE thread_id =  @sql_thread AND sum_timer_wait > 0 ORDER BY sum_timer_wait DESC;
+--------------------------------------+------------+----------------+
| event_name                           | count_star | sum_timer_wait |
+--------------------------------------+------------+----------------+
| wait/io/table/sql/handler            |      10002 | 52235951612404 |
| wait/io/file/innodb/innodb_log_file  |      20012 | 24423316510586 |
| wait/io/file/sql/binlog              |      10014 |   372515866246 |
| wait/io/file/innodb/innodb_data_file |         29 |   158702658238 |
| wait/io/file/sql/relaylog            |      12641 |   144454610798 |
| wait/io/file/sql/binlog_index        |         30 |    62816598186 |
| wait/lock/table/sql/handler          |      10003 |    51676533966 |
| wait/io/file/sql/misc                |          3 |      116404090 |
+--------------------------------------+------------+----------------+

SLAVE側のSystem Lockの状態はRelay Logの適用から発生するトランザクションでした。

備考:以下のように、スレーブ側で参照ロックをかけた場合の状況もSystem Lock。
補足:トランザクションでロックをかけたセッションで、SYSスキーマを利用した為、sys.innodb_lock_waitsの値がおかしい。

違うセッションであれば、sys.innodb_lock_waitsも問題無い。

上記はバグでは無いですが、分かり難いのでスクリプト変更しました。(MySQL8.0では利用出来ません)
https://bugs.mysql.com/file.php?id=26694&bug_id=72131

Please Enjoy Replication with MySQL8.0 soon…..


5.6.1で既に実装されていてDefaultでONになっているので,5.6や5.7では普段殆ど気にしてませんでしたが、質問頂いたのでindex_condition_pushdownの条件を再確認。
DefaultはONになっています。あえて、OFFにするメリットはあまり無いかと思います。

Index Condition Pushdown(ICP): ストレージエンジンからフェッチしたレコードをMySQLが評価してWHERE区の条件による絞り込みを行っていたが、
インデックスが貼られたカラムを用いた評価については、ストレージエンジンへ条件式を渡し(プッシュダウン)、ストレージエンジン側で評価を行わせることによってオーバーヘッドの低減させる。

ICPの目標は、完全なレコードの読み取りの回数を減らし、それによって I/O 操作を減らすことです。InnoDB のクラスタ化されたインデックスの場合、完全なレコードはすでに InnoDB バッファーに読み込まれています。この場合に ICP を使用しても IO は削減されません。 その為、ICP はセカンダリインデックスにのみ使用されます。

インデックス条件をプッシュダウンしようとするのは以下の条件の場合:
0. テーブルには選択条件がある。
1. ストレージエンジンがICPをサポートする。
2. index_condition_pushdownスイッチがオンで、ICPの使用がNO_ICPヒントによって無効にされていない。
3. クエリーは、複数表の更新または削除ステートメントでは無い。この要件の理由は、選択/結合と更新の両方に同じハンドラが使用されるためです。プッシュされたインデックス条件は、更新部分を実行するときにストレージエンジンによって適用され、誤ったレコードを更新または更新するためのレコードを見つけられない結果になることがあります。
4. JOIN_TABは、”NULLキーをフル・スキャン”実行中にオンまたはオフにできる条件を保護しているサブクエリの一部ではありません。

       @see Item_in_optimizer :: val_int()
       @see subselect_single_select_engine :: exec()
       @see TABLE_REF :: cond_guards
       @see setup_join_buffering

5.結合タイプはCONSTまたはSYSTEMではありません。これらの結合タイプを除外する理由は、これらの結合タイプが、ストレージエンジンから一度だけレコードを読み取り、後でそれを再利用するように最適化されているためです。プッシュされたインデックス条件が結合シーケンスの前のテーブルからのフィールドを評価する結合では、プッシュされた条件は、レコード値が最初に必要とされたときにのみ評価されます。
6.インデックスはクラスタ化インデックスではありません。クラスタ化されたキーでインデックス条件をプッシュすると、クラスタ化されていないキーよりも大幅に低くなります。WL#6061が実装されている場合、この制限は再評価する必要があります。
7.仮想生成列の索引は、ICPではサポートされていません。

Ref Source:
https://github.com/mysql/mysql-server/blob/5.7/sql/sql_select.cc#L1723

index_condition_pushdown=offを意図的にOFFにした場合

root@localhost [world]> SET optimizer_switch='index_condition_pushdown=off';
Query OK, 0 rows affected (0.00 sec)

root@localhost [world]> explain select * from City3 where CountryCode ='JPN' and District like '%o%';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | City3 | NULL       | ref  | idx_City3     | idx_City3 | 9       | const |  605 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

root@localhost [world]> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 605   |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.00 sec)

root@localhost [world]> 

Defaultの挙動 (index_condition_pushdown=on)

root@localhost [world]> SET optimizer_switch='index_condition_pushdown=on';
Query OK, 0 rows affected (0.00 sec)

root@localhost [world]> explain select * from City3 where CountryCode ='JPN' and District like '%o%';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | City3 | NULL       | ref  | idx_City3     | idx_City3 | 9       | const |  605 |    11.11 | Using index condition |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

root@localhost [world]> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 440   |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.01 sec)

参照:
http://nippondanji.blogspot.jp/2011/04/mysql-56.html
https://www.percona.com/blog/2014/01/03/multiple-column-index-vs-multiple-indexes-with-mysql-56/


MySQL8.0から、Descending Indexがサポートされる。
これによりMySQL5.7と比較して、大きなテーブルでの降順(DESC)のデータ参照処理を、高速に実行する事が出来るようになる。小さいテーブルに関しては、既存のMySQL5.7でもインデックスが利用出来るので、それ程差は出ないかと思います。

MySQL8.0.1で、Sakila Sampleデータベースのrentalテーブルを参照した場合 (INDEXはASCで作成されている)


mysql> select @@version;                                              
+-----------+
| @@version |
+-----------+
| 8.0.1-dmr |
+-----------+
1 row in set (0.00 sec)

mysql> show create table rental\G                                     
*************************** 1. row ***************************
       Table: rental
Create Table: CREATE TABLE `rental` (
  `rental_id` int(11) NOT NULL AUTO_INCREMENT,
  `rental_date` datetime NOT NULL,
  `inventory_id` mediumint(8) unsigned NOT NULL,
  `customer_id` smallint(5) unsigned NOT NULL,
  `return_date` datetime DEFAULT NULL,
  `staff_id` tinyint(3) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`rental_id`),
  UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
  KEY `idx_fk_inventory_id` (`inventory_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `idx_fk_staff_id` (`staff_id`),
  CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from rental order by rental_date asc limit 3; 
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date         | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
|         1 | 2005-05-24 22:53:30 |          367 |         130 | 2005-05-26 22:04:30 |        1 | 2006-02-15 21:30:53 |
|         2 | 2005-05-24 22:54:33 |         1525 |         459 | 2005-05-28 19:40:33 |        1 | 2006-02-15 21:30:53 |
|         3 | 2005-05-24 23:03:39 |         1711 |         408 | 2005-06-01 22:12:39 |        1 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental order by rental_date asc limit 3; 
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | rental | NULL       | index | NULL          | rental_date | 10      | NULL |    3 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from rental order by rental_date desc limit 3;
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
|     11739 | 2006-02-14 15:16:03 |         4568 |         373 | NULL        |        2 | 2006-02-15 21:30:53 |
|     14616 | 2006-02-14 15:16:03 |         4537 |         532 | NULL        |        1 | 2006-02-15 21:30:53 |
|     11676 | 2006-02-14 15:16:03 |         4496 |         216 | NULL        |        2 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental order by rental_date desc limit 3;
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+
| id | select_type | table  | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra               |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+
|  1 | SIMPLE      | rental | NULL       | index | NULL          | rental_date | 10      | NULL |    3 |   100.00 | Backward index scan |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+
1 row in set, 1 warning (0.00 sec)

mysql> 

MySQL8.0.1で、Sakila Sampleデータベースのrentalテーブルを参照した場合 (INDEXをDESCで作成し直した場合)
インデックスを降順に作成したので、先程とはEXTRAが反対になっている事が確認出来る。

mysql> alter table rental add unique key rental_date_desc (`rental_date` desc,`inventory_id` desc,`customer_id` desc);
Query OK, 0 rows affected (0.30 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from rental order by rental_date asc limit 3; 
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date         | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
|         1 | 2005-05-24 22:53:30 |          367 |         130 | 2005-05-26 22:04:30 |        1 | 2006-02-15 21:30:53 |
|         2 | 2005-05-24 22:54:33 |         1525 |         459 | 2005-05-28 19:40:33 |        1 | 2006-02-15 21:30:53 |
|         3 | 2005-05-24 23:03:39 |         1711 |         408 | 2005-06-01 22:12:39 |        1 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental order by rental_date asc limit 3;                                                
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+---------------------+
| id | select_type | table  | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra               |
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+---------------------+
|  1 | SIMPLE      | rental | NULL       | index | NULL          | rental_date_desc | 10      | NULL |    3 |   100.00 | Backward index scan |
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+---------------------+
1 row in set, 1 warning (0.01 sec)

mysql> select * from rental order by rental_date desc limit 3;
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
|     11739 | 2006-02-14 15:16:03 |         4568 |         373 | NULL        |        2 | 2006-02-15 21:30:53 |
|     14616 | 2006-02-14 15:16:03 |         4537 |         532 | NULL        |        1 | 2006-02-15 21:30:53 |
|     11676 | 2006-02-14 15:16:03 |         4496 |         216 | NULL        |        2 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental order by rental_date desc limit 3;                                               
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | rental | NULL       | index | NULL          | rental_date_desc | 10      | NULL |    3 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

mysql> 

詳細は、以下のMySQL Server Teamのブログを確認下さい。
MySQL 8.0 Labs – Descending Indexes in MySQL
http://mysqlserverteam.com/mysql-8-0-labs-descending-indexes-in-mysql/

MySQL5.5 ~ MySQL5.7までは昇順(ASC)で格納されたインデックスデータを利用してASC, DESC共に参照処理を行う
MySQL 5.7までは、インデックスのデータはASCで格納されています。こちらは、DESCで格納したインデックスと比べて、降順のデータの参照パフォーマンスは遅いが、
クエリーにDESCオプションを付けて、直近のデータを参照するとASCで作成したINDEXを利用してデータを参照する為、インデックスを利用した後方参照処理になる為、
インデックスが無い場合と比べても高速なレスポンスで後方参照処理を行う事が出来る。以下のマニュアルは、若干分かり難いがASC、DESCを付けてインデックスを作成しても、
MySQL5.5以降のMySQLであれば昇順(ASC)で作成されたINDEXでASCもDESCも処理出来る為、高速に参照する事が可能です。MySQL8.0で処理がより高速になる。

5.7 英語マニュアル
https://dev.mysql.com/doc/refman/5.7/en/create-index.html
An index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.

5.6 日本語マニュアル
https://dev.mysql.com/doc/refman/5.6/ja/create-index.html
index_col_name の指定を ASC または DESC で終了させることができます。これらのキーワードは、インデックス値の昇順または降順での格納を指定する将来の拡張のために許可されています。現在、これらは解析されますが、無視されます。インデックス値は、常に昇順で格納されます。

MySQL5.7.18の実行プランは以下のような感じです。インデックスを利用して降順(DESC)参照が行われている事が確認出来る。
MySQL8.0RCがリリースされた頃に、大きなテーブルでレスポンスの差を確認して見たいと思います。


mysql> select @@version;
+-------------------------------------------+
| @@version                                 |
+-------------------------------------------+
| 5.7.18-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table rental\G
*************************** 1. row ***************************
       Table: rental
Create Table: CREATE TABLE `rental` (
  `rental_id` int(11) NOT NULL AUTO_INCREMENT,
  `rental_date` datetime NOT NULL,
  `inventory_id` mediumint(8) unsigned NOT NULL,
  `customer_id` smallint(5) unsigned NOT NULL,
  `return_date` datetime DEFAULT NULL,
  `staff_id` tinyint(3) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`rental_id`),
  UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
  KEY `idx_fk_inventory_id` (`inventory_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `idx_fk_staff_id` (`staff_id`),
  CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8
1 row in set (0.02 sec)

mysql> select * from rental limit 3;
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date         | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
|         1 | 2005-05-24 22:53:30 |          367 |         130 | 2005-05-26 22:04:30 |        1 | 2006-02-15 21:30:53 |
|         2 | 2005-05-24 22:54:33 |         1525 |         459 | 2005-05-28 19:40:33 |        1 | 2006-02-15 21:30:53 |
|         3 | 2005-05-24 23:03:39 |         1711 |         408 | 2005-06-01 22:12:39 |        1 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental limit 3;
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | SIMPLE      | rental | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 16005 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0.02 sec)

Note (Code 1003): /* select#1 */ select `sakila`.`rental`.`rental_id` AS `rental_id`,`sakila`.`rental`.`rental_date` AS `rental_date`,`sakila`.`rental`.`inventory_id` AS `inventory_id`,`sakila`.`rental`.`customer_id` AS `customer_id`,`sakila`.`rental`.`return_date` AS `return_date`,`sakila`.`rental`.`staff_id` AS `staff_id`,`sakila`.`rental`.`last_update` AS `last_update` from `sakila`.`rental` limit 3

mysql> select * from rental order by rental_date asc limit 3;
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date         | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
|         1 | 2005-05-24 22:53:30 |          367 |         130 | 2005-05-26 22:04:30 |        1 | 2006-02-15 21:30:53 |
|         2 | 2005-05-24 22:54:33 |         1525 |         459 | 2005-05-28 19:40:33 |        1 | 2006-02-15 21:30:53 |
|         3 | 2005-05-24 23:03:39 |         1711 |         408 | 2005-06-01 22:12:39 |        1 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental order by rental_date asc limit 3;
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | rental | NULL       | index | NULL          | rental_date | 10      | NULL |    3 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.02 sec)

Note (Code 1003): /* select#1 */ select `sakila`.`rental`.`rental_id` AS `rental_id`,`sakila`.`rental`.`rental_date` AS `rental_date`,`sakila`.`rental`.`inventory_id` AS `inventory_id`,`sakila`.`rental`.`customer_id` AS `customer_id`,`sakila`.`rental`.`return_date` AS `return_date`,`sakila`.`rental`.`staff_id` AS `staff_id`,`sakila`.`rental`.`last_update` AS `last_update` from `sakila`.`rental` order by `sakila`.`rental`.`rental_date` limit 3

mysql> select * from rental order by rental_date desc limit 3;
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
|     11739 | 2006-02-14 15:16:03 |         4568 |         373 | NULL        |        2 | 2006-02-15 21:30:53 |
|     14616 | 2006-02-14 15:16:03 |         4537 |         532 | NULL        |        1 | 2006-02-15 21:30:53 |
|     11676 | 2006-02-14 15:16:03 |         4496 |         216 | NULL        |        2 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental order by rental_date desc limit 3;
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | rental | NULL       | index | NULL          | rental_date | 10      | NULL |    3 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

Note (Code 1003): /* select#1 */ select `sakila`.`rental`.`rental_id` AS `rental_id`,`sakila`.`rental`.`rental_date` AS `rental_date`,`sakila`.`rental`.`inventory_id` AS `inventory_id`,`sakila`.`rental`.`customer_id` AS `customer_id`,`sakila`.`rental`.`return_date` AS `return_date`,`sakila`.`rental`.`staff_id` AS `staff_id`,`sakila`.`rental`.`last_update` AS `last_update` from `sakila`.`rental` order by `sakila`.`rental`.`rental_date` desc limit 3
mysql>  


MySQL5.6以降で標準準拠のGET DIAGNOSTICSが実装され、その中でMySQLのエラーコードを取得することが可能になります。
MySQL5.7(5.7.2)以降でSTACKEDが追加されています。エラーの詳細を記録するのに使えるかと思います。詳細については次のマニュアルをご参照ください。

GET DIAGNOSTICS Syntax
http://dev.mysql.com/doc/refman/5.6/en/get-diagnostics.html
http://dev.mysql.com/doc/refman/5.7/en/get-diagnostics.html
Worklog: http://dev.mysql.com/worklog/task/?id=2111

MySQL5.6
GET [CURRENT] DIAGNOSTICS

MySQL5.7
GET [CURRENT | STACKED] DIAGNOSTICS

SQL statements produce diagnostic information that populates the diagnostics area. Standard SQL has a diagnostics area stack, containing a diagnostics area for each nested execution context. Standard SQL also supports GET STACKED DIAGNOSTICS syntax for referring to the second diagnostics area during condition handler execution. MySQL supports the STACKED keyword as of MySQL 5.7. Before that, MySQL does not support STACKED; there is a single diagnostics area containing information from the most recent statement that wrote to it.
標準SQLは条件ハンドラの実行中に、第2の診断領域を参照するためのSTACKED診断構文をGETサポートしています。

■ GET CURRENTの挙動
The GET DIAGNOSTICS statement is typically used in a handler within a stored program.
It is a MySQL extension that GET [CURRENT] DIAGNOSTICS is permitted outside handler context to check the execution of any SQL statement.


root@localhost [CONFIRM]> DROP TABLE IF EXISTS t334;
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@localhost [CONFIRM]> GET DIAGNOSTICS CONDITION 1 @p1 = RETURNED_SQLSTATE, @p2 = MYSQL_ERRNO, @p3 = MESSAGE_TEXT;
Query OK, 0 rows affected (0.00 sec)

root@localhost [CONFIRM]> SELECT @p1, @p2, @p3;
+-------+------+------------------------------+
| @p1   | @p2  | @p3                          |
+-------+------+------------------------------+
| 42S02 | 1051 | Unknown table 'CONFIRM.t334' |
+-------+------+------------------------------+
1 row in set (0.00 sec)

root@localhost [CONFIRM]> 

■ GET STACKEDの挙動
The GET DIAGNOSTICS statement is typically used in a handler within a stored program.
It is a MySQL extension that GET [CURRENT] DIAGNOSTICS is permitted outside handler context to check the execution of any SQL statement.
プロシジャー外部での利用の為、エラーになっています。

root@localhost [CONFIRM]> DROP TABLE IF EXISTS t334;
Query OK, 0 rows affected, 1 warning (0.01 sec)

root@localhost [CONFIRM]> GET STACKED DIAGNOSTICS CONDITION 1 @p1 = RETURNED_SQLSTATE, @p2 = MYSQL_ERRNO, @p3 = MESSAGE_TEXT;
ERROR 3004 (0Z002): GET STACKED DIAGNOSTICS when handler not active
root@localhost [CONFIRM]> 

■ CURRENTとSTACKEDの挙動

root@localhost [USER01]> delimiter //
root@localhost [USER01]> CREATE PROCEDURE p ()
    -> BEGIN
    ->   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
    ->     SIGNAL SQLSTATE '01000';
    ->     GET CURRENT DIAGNOSTICS CONDITION 1 @current = RETURNED_SQLSTATE;
    ->     GET STACKED DIAGNOSTICS CONDITION 1 @stacked = RETURNED_SQLSTATE;
    ->     END;
    ->   SIGNAL SQLSTATE '03000';
    ->   END//
Query OK, 0 rows affected (0.00 sec)

root@localhost [USER01]> CALL p()//
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@localhost [USER01]> SELECT @current, @stacked//
+----------+----------+
| @current | @stacked |
+----------+----------+
| 01000    | 03000    |
+----------+----------+
1 row in set (0.00 sec)

root@localhost [USER01]> 

■応用:エラーが発生した原因をテーブルに格納して後で確認

root@localhost [CONFIRM]> CREATE TABLE ids(id INT NOT NULL, UNIQUE(id));
Query OK, 0 rows affected (0.03 sec)

root@localhost [CONFIRM]> CREATE TABLE errors(id INT NOT NULL,RS VARCHAR(16),ME varchar(16),MT VARCHAR(1000));
Query OK, 0 rows affected (0.03 sec)

root@localhost [CONFIRM]> delimiter //
root@localhost [CONFIRM]> CREATE PROCEDURE insert_id(id INT)
    -> BEGIN
    -> DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    -> BEGIN
    -> GET STACKED DIAGNOSTICS CONDITION 1 @p1 = RETURNED_SQLSTATE, @p2 = MYSQL_ERRNO, @p3 = MESSAGE_TEXT;
    -> INSERT INTO errors VALUES(id, @p1,@p2,@p3);
    -> IF @errno = 1213 THEN
    -> RESIGNAL;
    -> END IF;
    -> END;
    -> INSERT INTO ids VALUES(id);
    -> END;
    -> //
Query OK, 0 rows affected (0.01 sec)

root@localhost [CONFIRM]> delimiter ;
root@localhost [CONFIRM]> CALL insert_id(0);CALL insert_id(1);CALL insert_id(2);CALL insert_id(2);
Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.01 sec)

root@localhost [CONFIRM]> select * from ids;
+----+
| id |
+----+
|  0 |
|  1 |
|  2 |
+----+
3 rows in set (0.00 sec)

root@localhost [CONFIRM]> select * from errors;
+----+-------+------+----------------------------------+
| id | RS    | ME   | MT                               |
+----+-------+------+----------------------------------+
|  2 | 23000 | 1062 | Duplicate entry '2' for key 'id' |
+----+-------+------+----------------------------------+
1 row in set (0.00 sec)

root@localhost [CONFIRM]> 

参考)
http://dev.mysql.com/doc/refman/5.6/ja/get-diagnostics.html
http://dev.mysql.com/doc/refman/5.6/ja/diagnostics-area.html
http://dev.mysql.com/doc/refman/5.7/en/get-diagnostics.html
http://dev.mysql.com/doc/refman/5.7/en/diagnostics-area.html


先日、パフォーマンス検証したMySQL Cluster(7.5.3 RC)ですが、MySQL Cluster7.5.4でついにGAになりました。高可用性、インメモリー処理、トランザクション対応したNoSQL/SQL処理、オンライン拡張性が求められるシステムを探されている場合は、是非試してみて下さい。

7.5では、パフォーマンス最適化オプションだけでなく、ndbinfoから様々な構成や稼働情報を簡単に確認する事が出来るようになっています。これまで、クラスターコンフィグファイル等で確認していた内容もクエリーで即座に複数ノードを跨った設定を確認出来るようになってますので、これまで以上に運用がし易くなるかと思います。

Changes in MySQL Cluster NDB 7.5.4 (5.7.16-ndb-7.5.4) (2016-10-18, General Availability)
http://dev.mysql.com/doc/relnotes/mysql-cluster/7.5/en/mysql-cluster-news-7-5-4.html

MySQL Cluster 7.5 パフォーマンス検証

確認:バージョン

mysql> select @@version;
+----------------------------------------------+
| @@version                                    |
+----------------------------------------------+
| 5.7.16-ndb-7.5.4-cluster-commercial-advanced |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> 

config_paramsとconfig_valuesを利用したMySQL Clusterパラメータの容易な確認

mysql> SELECT  p.param_name AS Name,
    ->         v.node_id AS Node,
    ->         p.param_type AS Type,
    ->         p.param_default AS 'Default',
    ->         p.param_min AS Minimum,
    ->         p.param_max AS Maximum,
    ->         CASE p.param_mandatory WHEN 1 THEN 'Y' ELSE 'N' END AS 'Required',
    ->         v.config_value AS Current
    -> FROM    config_params p
    -> JOIN    config_values v
    -> ON      p.param_number = v.config_param
    -> WHERE   p. param_name IN ('NodeId', 'HostName','DataMemory', 'IndexMemory');
+-------------+------+----------+-----------+---------+---------------+----------+----------------+
| Name        | Node | Type     | Default   | Minimum | Maximum       | Required | Current        |
+-------------+------+----------+-----------+---------+---------------+----------+----------------+
| NodeId      |    1 | unsigned |           | 1       | 48            | Y        | 1              |
| HostName    |    1 | string   | localhost |         |               | N        | 192.168.56.114 |
| DataMemory  |    1 | unsigned | 83886080  | 1048576 | 1099511627776 | N        | 134217728      |
| IndexMemory |    1 | unsigned | 18874368  | 1048576 | 1099511627776 | N        | 68157440       |
| NodeId      |    2 | unsigned |           | 1       | 48            | Y        | 2              |
| HostName    |    2 | string   | localhost |         |               | N        | 192.168.56.115 |
| DataMemory  |    2 | unsigned | 83886080  | 1048576 | 1099511627776 | N        | 134217728      |
| IndexMemory |    2 | unsigned | 18874368  | 1048576 | 1099511627776 | N        | 68157440       |
+-------------+------+----------+-----------+---------+---------------+----------+----------------+
8 rows in set (0.03 sec)

mysql> 

mysql> SELECT  p.param_name AS Name,
    ->         v.node_id AS Node,
    ->         p.param_type AS Type,
    ->         p.param_default AS 'Default',
    ->         p.param_min AS Minimum,
    ->         p.param_max AS Maximum,
    ->         CASE p.param_mandatory WHEN 1 THEN 'Y' ELSE 'N' END AS 'Required',
    ->         v.config_value AS Current
    -> FROM    config_params p
    -> JOIN    config_values v
    -> ON      p.param_number = v.config_param
    -> WHERE   p. param_name like 'max%';
+-----------------------------------+------+----------+------------+----------+---------------+----------+------------+
| Name                              | Node | Type     | Default    | Minimum  | Maximum       | Required | Current    |
+-----------------------------------+------+----------+------------+----------+---------------+----------+------------+
| MaxNoOfSavedMessages              |    1 | unsigned | 25         | 0        | 4294967039    | N        | 25         |
| MaxNoOfTables                     |    1 | unsigned | 128        | 8        | 20320         | N        | 130        |
| MaxNoOfAttributes                 |    1 | unsigned | 1000       | 32       | 4294967039    | N        | 1009       |
| MaxNoOfTriggers                   |    1 | unsigned | 768        | 0        | 4294967039    | N        | 1784       |
| MaxNoOfConcurrentTransactions     |    1 | unsigned | 4096       | 32       | 4294967039    | N        | 4096       |
| MaxNoOfConcurrentOperations       |    1 | unsigned | 32768      | 32       | 4294967039    | N        | 10000      |
| MaxNoOfConcurrentScans            |    1 | unsigned | 256        | 2        | 500           | N        | 256        |
| MaxNoOfFiredTriggers              |    1 | unsigned | 4000       | 0        | 4294967039    | N        | 4000       |
| MaxNoOfConcurrentIndexOperations  |    1 | unsigned | 8192       | 0        | 4294967039    | N        | 8192       |
| MaxNoOfOrderedIndexes             |    1 | unsigned | 128        | 0        | 4294967039    | N        | 512        |
| MaxNoOfUniqueHashIndexes          |    1 | unsigned | 64         | 0        | 4294967039    | N        | 64         |
| MaxAllocate                       |    1 | unsigned | 33554432   | 1048576  | 1073741824    | N        | 33554432   |
| MaxNoOfSubscriptions              |    1 | unsigned | 0          | 0        | 4294967039    | N        | 0          |
| MaxNoOfSubscribers                |    1 | unsigned | 0          | 0        | 4294967039    | N        | 0          |
| MaxNoOfConcurrentSubOperations    |    1 | unsigned | 256        | 0        | 4294967039    | N        | 256        |
| MaxBufferedEpochs                 |    1 | unsigned | 100        | 1        | 100000        | N        | 100        |
| MaxNoOfExecutionThreads           |    1 | unsigned | 0          | 2        | 72            | N        | 2          |
| MaxBufferedEpochBytes             |    1 | unsigned | 26214400   | 26214400 | 4294967039    | N        | 26214400   |
| MaxLCPStartDelay                  |    1 | unsigned | 0          | 0        | 600           | N        | 0          |
| MaxStartFailRetries               |    1 | unsigned | 3          | 0        | 4294967039    | N        | 3          |
| MaxParallelScansPerFragment       |    1 | unsigned | 256        | 1        | 4294967039    | N        | 256        |
| MaxDMLOperationsPerTransaction    |    1 | unsigned | 4294967295 | 32       | 4294967295    | N        | 4294967295 |
| MaxDiskWriteSpeed                 |    1 | unsigned | 20971520   | 1048576  | 1099511627776 | N        | 20971520   |
| MaxDiskWriteSpeedOtherNodeRestart |    1 | unsigned | 52428800   | 1048576  | 1099511627776 | N        | 52428800   |
| MaxDiskWriteSpeedOwnRestart       |    1 | unsigned | 209715200  | 1048576  | 1099511627776 | N        | 209715200  |
| MaxParallelCopyInstances          |    1 | unsigned | 0          | 0        | 64            | N        | 0          |
| MaxSendDelay                      |    1 | unsigned | 0          | 0        | 11000         | N        | 0          |
| MaxNoOfSavedMessages              |    2 | unsigned | 25         | 0        | 4294967039    | N        | 25         |
| MaxNoOfTables                     |    2 | unsigned | 128        | 8        | 20320         | N        | 130        |
| MaxNoOfAttributes                 |    2 | unsigned | 1000       | 32       | 4294967039    | N        | 1009       |
| MaxNoOfTriggers                   |    2 | unsigned | 768        | 0        | 4294967039    | N        | 1784       |
| MaxNoOfConcurrentTransactions     |    2 | unsigned | 4096       | 32       | 4294967039    | N        | 4096       |
| MaxNoOfConcurrentOperations       |    2 | unsigned | 32768      | 32       | 4294967039    | N        | 10000      |
| MaxNoOfConcurrentScans            |    2 | unsigned | 256        | 2        | 500           | N        | 256        |
| MaxNoOfFiredTriggers              |    2 | unsigned | 4000       | 0        | 4294967039    | N        | 4000       |
| MaxNoOfConcurrentIndexOperations  |    2 | unsigned | 8192       | 0        | 4294967039    | N        | 8192       |
| MaxNoOfOrderedIndexes             |    2 | unsigned | 128        | 0        | 4294967039    | N        | 512        |
| MaxNoOfUniqueHashIndexes          |    2 | unsigned | 64         | 0        | 4294967039    | N        | 64         |
| MaxAllocate                       |    2 | unsigned | 33554432   | 1048576  | 1073741824    | N        | 33554432   |
| MaxNoOfSubscriptions              |    2 | unsigned | 0          | 0        | 4294967039    | N        | 0          |
| MaxNoOfSubscribers                |    2 | unsigned | 0          | 0        | 4294967039    | N        | 0          |
| MaxNoOfConcurrentSubOperations    |    2 | unsigned | 256        | 0        | 4294967039    | N        | 256        |
| MaxBufferedEpochs                 |    2 | unsigned | 100        | 1        | 100000        | N        | 100        |
| MaxNoOfExecutionThreads           |    2 | unsigned | 0          | 2        | 72            | N        | 2          |
| MaxBufferedEpochBytes             |    2 | unsigned | 26214400   | 26214400 | 4294967039    | N        | 26214400   |
| MaxLCPStartDelay                  |    2 | unsigned | 0          | 0        | 600           | N        | 0          |
| MaxStartFailRetries               |    2 | unsigned | 3          | 0        | 4294967039    | N        | 3          |
| MaxParallelScansPerFragment       |    2 | unsigned | 256        | 1        | 4294967039    | N        | 256        |
| MaxDMLOperationsPerTransaction    |    2 | unsigned | 4294967295 | 32       | 4294967295    | N        | 4294967295 |
| MaxDiskWriteSpeed                 |    2 | unsigned | 20971520   | 1048576  | 1099511627776 | N        | 20971520   |
| MaxDiskWriteSpeedOtherNodeRestart |    2 | unsigned | 52428800   | 1048576  | 1099511627776 | N        | 52428800   |
| MaxDiskWriteSpeedOwnRestart       |    2 | unsigned | 209715200  | 1048576  | 1099511627776 | N        | 209715200  |
| MaxParallelCopyInstances          |    2 | unsigned | 0          | 0        | 64            | N        | 0          |
| MaxSendDelay                      |    2 | unsigned | 0          | 0        | 11000         | N        | 0          |
+-----------------------------------+------+----------+------------+----------+---------------+----------+------------+
54 rows in set (0.01 sec)

mysql> 

dict_obj_info
The dict_obj_info table provides information about
NDB data dictionary (DICT) objects such as tables and indexes.
こちらのテーブルはNDBデータディクショナリーの情報を確認する事が可能です。


mysql> select * from dict_obj_info limit 5;
+------+------+----------+-------+-----------------+---------------+----------------------------+
| type | id   | version  | state | parent_obj_type | parent_obj_id | fq_name                    |
+------+------+----------+-------+-----------------+---------------+----------------------------+
|    6 |   89 |        1 |     4 |               2 |            88 | sys/def/88/PRIMARY         |
|   18 |   44 |        1 |     4 |               6 |            98 | NDB$INDEX_98_CUSTOM        |
|    2 |   31 | 16777218 |     4 |               0 |             0 | TEST_DB_InnoDB/def/Country |
|    6 |   59 |        1 |     4 |               2 |            33 | sys/def/33/PRIMARY         |
|   18 |    2 |        1 |     4 |               6 |            37 | NDB$INDEX_37_CUSTOM        |
+------+------+----------+-------+-----------------+---------------+----------------------------+
5 rows in set (0.00 sec)

mysql> 

table_distribution_status
The table_distribution_status table provides information about the progress of table distribution for NDB tables.
こちらのテーブルでは、NDBテーブルのディストリビューションステータスを確認する事が可能です。

mysql> select * from table_distribution_status limit 5;
+---------+----------+-----------------+-------------------+----------------+------------+-------------+----------------+---------------+--------------------+-----------------+------------------+
| node_id | table_id | tab_copy_status | tab_update_status | tab_lcp_status | tab_status | tab_storage | tab_partitions | tab_fragments | current_scan_count | scan_count_wait | is_reorg_ongoing |
+---------+----------+-----------------+-------------------+----------------+------------+-------------+----------------+---------------+--------------------+-----------------+------------------+
|       1 |        2 | IDLE            | IDLE              | COMPLETED      | ACTIVE     | NORMAL      |              2 |             2 |                  0 |               0 |                0 |
|       1 |        3 | IDLE            | IDLE              | COMPLETED      | ACTIVE     | NORMAL      |              2 |             2 |                  0 |               0 |                0 |
|       1 |       11 | IDLE            | IDLE              | COMPLETED      | ACTIVE     | NORMAL      |              2 |             2 |                  0 |               0 |                0 |
|       1 |       12 | IDLE            | IDLE              | COMPLETED      | ACTIVE     | NORMAL      |              2 |             2 |                  0 |               0 |                0 |
|       1 |       13 | IDLE            | IDLE              | COMPLETED      | ACTIVE     | NORMAL      |              2 |             2 |                  0 |               0 |                0 |
+---------+----------+-----------------+-------------------+----------------+------------+-------------+----------------+---------------+--------------------+-----------------+------------------+
5 rows in set (0.00 sec)

mysql> 

table_fragments
The table_fragments table provides information about the fragmentation, partitioning, distribution, and (internal) replication of NDB tables.
此方のテーブルでは、NDBテーブルのフラグメンテーション、パーティション、ディストリビューション、レプリケーションを確認する事が可能です。

mysql> select * from table_fragments limit 5;
+---------+----------+--------------+-------------+-----------------+-------------+----------------+-----------------+-------------------+----------------------+-----------------------+----------------------+--------------------+-------------------+------------------+
| node_id | table_id | partition_id | fragment_id | partition_order | log_part_id | no_of_replicas | current_primary | preferred_primary | current_first_backup | current_second_backup | current_third_backup | num_alive_replicas | num_dead_replicas | num_lcp_replicas |
+---------+----------+--------------+-------------+-----------------+-------------+----------------+-----------------+-------------------+----------------------+-----------------------+----------------------+--------------------+-------------------+------------------+
|       1 |        2 |   4294967295 |           0 |               0 |           0 |              2 |               1 |                 1 |                    2 |                     0 |                    0 |                  2 |                 0 |                0 |
|       1 |        2 |   4294967295 |           1 |               0 |           0 |              2 |               2 |                 2 |                    1 |                     0 |                    0 |                  2 |                 0 |                0 |
|       1 |        3 |   4294967295 |           0 |               0 |           1 |              2 |               1 |                 1 |                    2 |                     0 |                    0 |                  2 |                 0 |                0 |
|       1 |        3 |   4294967295 |           1 |               0 |           1 |              2 |               2 |                 2 |                    1 |                     0 |                    0 |                  2 |                 0 |                0 |
|       1 |       11 |   4294967295 |           0 |               0 |           3 |              2 |               1 |                 1 |                    2 |                     0 |                    0 |                  2 |                 0 |                0 |
+---------+----------+--------------+-------------+-----------------+-------------+----------------+-----------------+-------------------+----------------------+-----------------------+----------------------+--------------------+-------------------+------------------+
5 rows in set (0.00 sec)

mysql> 

table_info
The table_info table provides information about logging, checkpointing, distribution, and storage options in effect for individual NDB tables.
こちらのテーブルでは、各NDBテーブルのログ設定、チェックポイント、ディストリビューション、ストレージオプション等を確認する事が可能です。

mysql> select * from  table_info limit 5;
+----------+--------------+------------------+-----------------------+-------------+------------------+--------------+------------+-------------------+------------+
| table_id | logged_table | row_contains_gci | row_contains_checksum | read_backup | fully_replicated | storage_type | hashmap_id | partition_balance | create_gci |
+----------+--------------+------------------+-----------------------+-------------+------------------+--------------+------------+-------------------+------------+
|       31 |            1 |                1 |                     1 |           0 |                0 | MEMORY       |          1 | FOR_RP_BY_LDM     |          0 |
|       12 |            1 |                1 |                     1 |           0 |                0 | DISK         |          1 | FOR_RP_BY_LDM     |          0 |
|       82 |            1 |                1 |                     1 |           0 |                0 | MEMORY       |          1 | FOR_RP_BY_LDM     |          0 |
|       13 |            1 |                1 |                     1 |           0 |                0 | MEMORY       |          1 | FOR_RP_BY_LDM     |          0 |
|        3 |            1 |                1 |                     1 |           0 |                0 | MEMORY       |          1 | FOR_RP_BY_LDM     |          0 |
+----------+--------------+------------------+-----------------------+-------------+------------------+--------------+------------+-------------------+------------+
5 rows in set (0.01 sec)

mysql> 

table_replicas
The table_replicas table provides information about the copying, distribution, and checkpointing of NDB table fragments and fragment replicas.
こちらのテーブルでは、NDBテーブルフラグメントとレプリカのコピー、ディストリビューション、チェックポイントの情報を確認する事が可能です。

mysql> select * from table_replicas limit 5;
+---------+----------+-------------+-------------+-----------------+----------------+----------------------+----------------------+------------------------+-------------+-------------+----------------------+------------------------+-----------------+------------------+------------------+
| node_id | table_id | fragment_id | initial_gci | replica_node_id | is_lcp_ongoing | num_crashed_replicas | last_max_gci_started | last_max_gci_completed | last_lcp_id | prev_lcp_id | prev_max_gci_started | prev_max_gci_completed | last_create_gci | last_replica_gci | is_replica_alive |
+---------+----------+-------------+-------------+-----------------+----------------+----------------------+----------------------+------------------------+-------------+-------------+----------------------+------------------------+-----------------+------------------+------------------+
|       1 |        2 |           0 |           2 |               1 |              0 |                    0 |                60743 |                  60743 |          35 |       57834 |                57834 |                     34 |           57834 |       4294967295 |                1 |
|       1 |        2 |           0 |           2 |               2 |              0 |                    0 |                60743 |                  60743 |          35 |       57834 |                57834 |                     34 |           57834 |       4294967295 |                1 |
|       1 |        2 |           1 |           2 |               1 |              0 |                    0 |                60743 |                  60743 |          35 |       57834 |                57834 |                     34 |           57834 |       4294967295 |                1 |
|       1 |        2 |           1 |           2 |               2 |              0 |                    0 |                60743 |                  60743 |          35 |       57834 |                57834 |                     34 |           57834 |       4294967295 |                1 |
|       1 |        3 |           0 |           2 |               1 |              0 |                    0 |                60743 |                  60743 |          35 |       57834 |                57834 |                     34 |           57834 |       4294967295 |                1 |
+---------+----------+-------------+-------------+-----------------+----------------+----------------------+----------------------+------------------------+-------------+-------------+----------------------+------------------------+-----------------+------------------+------------------+
5 rows in set (0.00 sec)

mysql> 

これらの追加機能やテーブルを活用して、更にMSQL Clusterを活用するケースが増えればと思います。

参考: https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-what-is-new.html

補足:アップグレードに再度パフォーマンス検証(MySQL Cluster 7.5.4 + Sysbench 0.5)
READ_BACKUP=0
185.78 per sec

ALTER TABLE sbtest1 COMMENT="NDB_TABLE=READ_BACKUP=0 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
ALTER TABLE sbtest2 COMMENT="NDB_TABLE=READ_BACKUP=0 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
ALTER TABLE sbtest3 COMMENT="NDB_TABLE=READ_BACKUP=0 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
ALTER TABLE sbtest4 COMMENT="NDB_TABLE=READ_BACKUP=0 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
[root@CL-SLAVE01 sys03]# ../oltp_test_read_only 
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            156310
        write:                           0
        other:                           22330
        total:                           178640
    transactions:                        11165  (185.78 per sec.)
    read/write requests:                 156310 (2600.95 per sec.)
    other operations:                    22330  (371.56 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0973s
    total number of events:              11165
    total time taken by event execution: 1921.4885s
    response time:
         min:                                 38.13ms
         avg:                                172.10ms
         max:                                390.91ms
         approx.  95 percentile:             243.58ms

Threads fairness:
    events (avg/stddev):           348.9062/3.30
    execution time (avg/stddev):   60.0465/0.02

READ_BACKUP=1
240.78 per sec.

ALTER TABLE sbtest1 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
ALTER TABLE sbtest2 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
ALTER TABLE sbtest3 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
ALTER TABLE sbtest4 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
[root@CL-SLAVE01 sys03]# ../oltp_test_read_only 
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32
Initializing random number generator from timer.

Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            202482
        write:                           0
        other:                           28926
        total:                           231408
    transactions:                        14463  (240.78 per sec.)
    read/write requests:                 202482 (3370.96 per sec.)
    other operations:                    28926  (481.57 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0666s
    total number of events:              14463
    total time taken by event execution: 1919.6420s
    response time:
         min:                                 66.34ms
         avg:                                132.73ms
         max:                                500.21ms
         approx.  95 percentile:             183.90ms

Threads fairness:
    events (avg/stddev):           451.9688/3.86
    execution time (avg/stddev):   59.9888/0.04

[root@CL-SLAVE01 sys03]#