MySQL Cluster7.5からPK参照以外の参照性能が向上している。
テーブル作成時にREAD_BACKUPオプションを利用するか、ndb_read_backupオプションを設定しておくとPRIMARYレプリカだけでなく、SECONDARYレプリカからもデータが参照する事が出来るようになる。
MySQL7.4までは常に、PRIMARYレプリカからデータを参照していたので、ローカルにPRIMARYデータが無い場合は、PRIMARYデータを持っているノードまで取得しにいく必要があった。

イメージ

cluster

参照:
14.1.18.7 Setting NDB_TABLE options in table comments

抜粋: 
READ_BACKUP: Setting this option to 1 has the same effect as though ndb_read_backup were enabled; enables reading from any replica.
Setting this option to 1 automatically sets FRAGMENT_COUNT_TYPE to ONE_PER_LDM_PER_NODE_GROUP (see next item).
Starting with MySQL Cluster NDB 7.5.3, you can set READ_BACKUP for an existing table online,
using an ALTER TABLE statement similar to one of those shown here:


ALTER TABLE ... ALGORITHM=INPLACE, COMMENT="NDB_TABLE=READ_BACKUP=1";
ALTER TABLE ... ALGORITHM=INPLACE, COMMENT="NDB_TABLE=READ_BACKUP=0";

【結論】
結果としては、READ_BACKUPをONにした方が、3割程パフォーマンスが向上していました。
非常に非力な、仮想環境での検証なので皆さんの環境でも是非検証してみて下さい。
大幅にパフォーマンスが改善する可能性があります。

READ_BACKUP=0 (OFF)
transactions: 5650 (93.91 per sec.)
transactions: 5718 (94.91 per sec.)
transactions: 5698 (94.71 per sec.)
transactions: 5490 (91.24 per sec.)

READ_BACKUP=1 (ON)
transactions: 7234 (120.17 per sec.)
transactions: 7403 (123.00 per sec.)
transactions: 7419 (123.32 per sec.)
transactions: 7264 (120.77 per sec.)

※ マシーン自体は非力なのでTPSは少ないですが、どちらも全く同じ状況で検証しています。

■ 簡単にSYSBENCH0.5のOLTP READで、参照パフォーマンスの確認を行ってみました。
メモリーが無いので、データは1万件しかいれてません。(1万 X 4テーブル)


mysql> select @@version;
+----------------------------------+
| @@version                        |
+----------------------------------+
| 5.7.13-ndb-7.5.3-cluster-gpl-log |
+----------------------------------+
1 row in set (0.00 sec)

mysql> show tables;
+--------------------+
| Tables_in_sysbench |
+--------------------+
| sbtest1            |
| sbtest2            |
| sbtest3            |
| sbtest4            |
+--------------------+
4 rows in set (0.00 sec)

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.00 sec)

mysql> 

SYSBENCHのスレッド数と実行しているQUERY
‐ 32スレッドで60秒程実行しています。 
‐ Queryの殆どは、BETWEEN等を利用しているのでPKで特定のデータをピンポイントで持ってくる処理ではありません。


/bin/sysbench \
  --test=../lua/db/oltp.lua \
  --rand-init=on \
  --db-driver=mysql \
  --oltp-table-size=${TSIZE} \
  --rand-type=uniform \
  --oltp-read-only=on \
  --oltp-tables-count=${NTBLE}\
  --mysql-db=sysbench \
  --mysql-host=${ENDPOINT} \
  --mysql-user=bench_user \
  --mysql-password=password \
  --max-time=60 \
  --max-requests=0 \
  --num-threads=32 \




[root@CL-SLAVE01 sys03]# cat ../lua/db/oltp.lua | egrep -i select 
   for i=1, oltp_point_selects do
      rs = db_query("SELECT c FROM ".. table_name .." WHERE id=" .. sb_rand(1, oltp_table_size))
      rs = db_query("SELECT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1)
      rs = db_query("SELECT SUM(K) FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1)
      rs = db_query("SELECT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1 .. " ORDER BY c")
      rs = db_query("SELECT DISTINCT c FROM ".. table_name .." WHERE id BETWEEN " .. range_start .. " AND " .. range_start .. "+" .. oltp_range_size - 1 .. " ORDER BY c")
[root@CL-SLAVE01 sys03]# 

上記のテーブルとデータでDEFAULTの状態(READ_BACKUPをOFF)で検証してみます。

[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:                            79100
        write:                           0
        other:                           11300
        total:                           90400
    transactions:                        5650   (93.91 per sec.)
    read/write requests:                 79100  (1314.76 per sec.)
    other operations:                    11300  (187.82 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.1630s
    total number of events:              5650
    total time taken by event execution: 1919.4297s
    response time:
         min:                                 89.72ms
         avg:                                339.72ms
         max:                                617.21ms
         approx.  95 percentile:             442.32ms

Threads fairness:
    events (avg/stddev):           176.5625/2.45
    execution time (avg/stddev):   59.9822/0.11

[root@CL-SLAVE01 sys03]# 




[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:                            80052
        write:                           0
        other:                           11436
        total:                           91488
    transactions:                        5718   (94.91 per sec.)
    read/write requests:                 80052  (1328.77 per sec.)
    other operations:                    11436  (189.82 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.2452s
    total number of events:              5718
    total time taken by event execution: 1924.6034s
    response time:
         min:                                161.28ms
         avg:                                336.59ms
         max:                                646.96ms
         approx.  95 percentile:             441.26ms

Threads fairness:
    events (avg/stddev):           178.6875/2.28
    execution time (avg/stddev):   60.1439/0.07

[root@CL-SLAVE01 sys03]# 

READ_BACKUPをONにしています。ALTERテーブルでテーブル毎に設定変更可能です。


mysql> ALTER TABLE sbtest1 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest2 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest3 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest4 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 

READ_BACKUPオプションをONにしたので、同じテストを実行してみます。


[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:                            101276
        write:                           0
        other:                           14468
        total:                           115744
    transactions:                        7234   (120.17 per sec.)
    read/write requests:                 101276 (1682.33 per sec.)
    other operations:                    14468  (240.33 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.1997s
    total number of events:              7234
    total time taken by event execution: 1919.6510s
    response time:
         min:                                 61.99ms
         avg:                                265.37ms
         max:                                515.43ms
         approx.  95 percentile:             350.01ms

Threads fairness:
    events (avg/stddev):           226.0625/0.97
    execution time (avg/stddev):   59.9891/0.10

[root@CL-SLAVE01 sys03]# 



[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:                            103642
        write:                           0
        other:                           14806
        total:                           118448
    transactions:                        7403   (123.00 per sec.)
    read/write requests:                 103642 (1722.05 per sec.)
    other operations:                    14806  (246.01 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.1852s
    total number of events:              7403
    total time taken by event execution: 1923.2176s
    response time:
         min:                                143.96ms
         avg:                                259.79ms
         max:                                421.40ms
         approx.  95 percentile:             328.68ms

Threads fairness:
    events (avg/stddev):           231.3438/0.59
    execution time (avg/stddev):   60.1006/0.05

[root@CL-SLAVE01 sys03]# 

■ 再テストでREAD_BACKUPをOFFにしてみます。


mysql> ALTER TABLE sbtest1 COMMENT="NDB_TABLE=READ_BACKUP=0 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest2 COMMENT="NDB_TABLE=READ_BACKUP=0 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest3 COMMENT="NDB_TABLE=READ_BACKUP=0 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest4 COMMENT="NDB_TABLE=READ_BACKUP=0 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 

READ_BACKUPをOFFにした状態で再度検証してみると、TPSは落ちています。

[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:                            79772
        write:                           0
        other:                           11396
        total:                           91168
    transactions:                        5698   (94.71 per sec.)
    read/write requests:                 79772  (1325.97 per sec.)
    other operations:                    11396  (189.42 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.1611s
    total number of events:              5698
    total time taken by event execution: 1920.7135s
    response time:
         min:                                118.55ms
         avg:                                337.09ms
         max:                                600.45ms
         approx.  95 percentile:             440.47ms

Threads fairness:
    events (avg/stddev):           178.0625/1.71
    execution time (avg/stddev):   60.0223/0.09

[root@CL-SLAVE01 sys03]# 


[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:                            76860
        write:                           0
        other:                           10980
        total:                           87840
    transactions:                        5490   (91.24 per sec.)
    read/write requests:                 76860  (1277.31 per sec.)
    other operations:                    10980  (182.47 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.1734s
    total number of events:              5490
    total time taken by event execution: 1922.5091s
    response time:
         min:                                171.24ms
         avg:                                350.18ms
         max:                                801.92ms
         approx.  95 percentile:             478.98ms

Threads fairness:
    events (avg/stddev):           171.5625/1.32
    execution time (avg/stddev):   60.0784/0.06

[root@CL-SLAVE01 sys03]# 

READ_BACKUP設定を再度ONにしています。


mysql> ALTER TABLE sbtest1 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest2 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest3 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE sbtest4 COMMENT="NDB_TABLE=READ_BACKUP=1 NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_LDM";
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 

もう一度、READ_BACKUPをONにしてパフォーマンスを確認してみました。
やはり、ONにした方が今回の環境ではパフォーマンスが良いようです。

[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:                            103866
        write:                           0
        other:                           14838
        total:                           118704
    transactions:                        7419   (123.32 per sec.)
    read/write requests:                 103866 (1726.47 per sec.)
    other operations:                    14838  (246.64 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.1609s
    total number of events:              7419
    total time taken by event execution: 1920.0195s
    response time:
         min:                                 91.51ms
         avg:                                258.80ms
         max:                                463.14ms
         approx.  95 percentile:             351.79ms

Threads fairness:
    events (avg/stddev):           231.8438/0.83
    execution time (avg/stddev):   60.0006/0.13

[root@CL-SLAVE01 sys03]#


[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:                            101696
        write:                           0
        other:                           14528
        total:                           116224
    transactions:                        7264   (120.77 per sec.)
    read/write requests:                 101696 (1690.77 per sec.)
    other operations:                    14528  (241.54 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.1476s
    total number of events:              7264
    total time taken by event execution: 1921.1800s
    response time:
         min:                                 77.60ms
         avg:                                264.48ms
         max:                                454.03ms
         approx.  95 percentile:             343.88ms

Threads fairness:
    events (avg/stddev):           227.0000/0.79
    execution time (avg/stddev):   60.0369/0.06

[root@CL-SLAVE01 sys03]# 

MySQL Cluster 7.5はMySQL5.7ベースのMySQLが利用可能なので、JSONが利用出来たり色々な改善が加わっています。
これまでより、多くのケースで活用出来る場面が増えてくるかと思います。詳細は以下のURLを参照下さい。
19.1.4 What is New in MySQL Cluster NDB 7.5

例) MySQL Cluster7.5のndbinfoから,こちらのようにテーブルの情報も確認出来るようになってます。


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.09 sec)

mysql> 

MySQL Clusterに関しても、セミナーで紹介していたりするので都合が付けば是非参加下さい。
https://www-jp.mysql.com/news-and-events/events/


SYS Schema on MySQLの再確認
SYS Schemaは元々ps_helperという名前だったが、Oracle、MS SQLと同じように
分かり易くする為にSYSにしたとの事。

The MySQL SYS Schema

インストール方法
以下の2つのうちどちらか選択
1) Workbench6.1以上~
2) GitからScriptをダウンロード

root@localhost [sys]>select * from sys.version;
+-------------+-------------------------------------------+
| sys_version | mysql_version                             |
+-------------+-------------------------------------------+
| 1.1.0       | 5.6.22-enterprise-commercial-advanced-log |
+-------------+-------------------------------------------+
1 row in set (0.00 sec)

root@localhost [sys]>select * from sys.schema_object_overview where db = 'sys';
+-----+-------------+-------+
| db  | object_type | count |
+-----+-------------+-------+
| sys | FUNCTION    |    11 |
| sys | VIEW        |    78 |
| sys | PROCEDURE   |    22 |
+-----+-------------+-------+
3 rows in set, 3 warnings (0.40 sec)

root@localhost [sys]>

x$はraw view (for tools to poll)
formatted viewはhumans and command lineの為に準備してある。(成形してある)

■■ user / Host Summary View
IO Usage
Stages
Statement Details

root@localhost [sys]>select * from user_summary\G
*************************** 1. row ***************************
                 user: background
           statements: NULL
    statement_latency: NULL
statement_avg_latency: 0 ps
          table_scans: NULL
             file_ios: 1728
      file_io_latency: 2.10 s
  current_connections: 18
    total_connections: 21
         unique_hosts: 0
*************************** 2. row ***************************
                 user: root
           statements: 90
    statement_latency: 1.37 s
statement_avg_latency: 15.22 ms
          table_scans: 6
             file_ios: 72340
      file_io_latency: 2.59 s
  current_connections: 1
    total_connections: 1
         unique_hosts: 1
2 rows in set (0.01 sec)

root@localhost [sys]>

■5.7では、メモリーの確認も出来る。

root@localhost [sys]>select * from sys.version;
+-------------+---------------------+
| sys_version | mysql_version       |
+-------------+---------------------+
| 1.3.0       | 5.7.5-labs-http-log |
+-------------+---------------------+
1 row in set (0.00 sec)

root@localhost [sys]>select * from user_summary\G
*************************** 1. row ***************************
                  user: root
            statements: 171
     statement_latency: 569.90 ms
 statement_avg_latency: 3.33 ms
           table_scans: 5
              file_ios: 1987
       file_io_latency: 00:03:05.67
   current_connections: 1
     total_connections: 1
          unique_hosts: 1
        current_memory: 0 bytes
total_memory_allocated: 0 bytes
*************************** 2. row ***************************
                  user: background
            statements: 0
     statement_latency: 0 ps
 statement_avg_latency: 0 ps
           table_scans: 0
              file_ios: 1940
       file_io_latency: 1.59 s
   current_connections: 21
     total_connections: 23
          unique_hosts: 0
        current_memory: 0 bytes <--- 5.7.5以上で追加された。
total_memory_allocated: 0 bytes <--- 5.7.5以上で追加された。
2 rows in set (0.01 sec)

root@localhost &#91;sys&#93;>

■ユーザ毎にIOを確認出来る

root@localhost [sys]>select * from user_summary_by_file_io_type;
+------------+--------------------------------------+-------+-----------+-------------+
| user       | event_name                           | total | latency   | max_latency |
+------------+--------------------------------------+-------+-----------+-------------+
| background | wait/io/file/innodb/innodb_data_file |   608 | 798.30 ms | 37.70 ms    |
| background | wait/io/file/sql/FRM                 |   910 | 789.77 ms | 195.80 ms   |
| background | wait/io/file/innodb/innodb_log_file  |    18 | 304.28 ms | 172.62 ms   |
| background | wait/io/file/sql/slow_log            |     4 | 65.20 ms  | 65.18 ms    |
| background | wait/io/file/sql/binlog              |    32 | 45.77 ms  | 19.03 ms    |
| background | wait/io/file/myisam/kfile            |    67 | 27.08 ms  | 7.97 ms     |
| background | wait/io/file/mysys/cnf               |     5 | 21.57 ms  | 21.51 ms    |
| background | wait/io/file/sql/binlog_index        |    15 | 20.14 ms  | 14.04 ms    |
| background | wait/io/file/mysys/charset           |     3 | 13.95 ms  | 13.87 ms    |
| background | wait/io/file/sql/ERRMSG              |     5 | 10.38 ms  | 7.82 ms     |
| background | wait/io/file/myisam/dfile            |    42 | 4.91 ms   | 1.40 ms     |
| background | wait/io/file/sql/query_log           |     4 | 1.30 ms   | 1.26 ms     |
| background | wait/io/file/sql/casetest            |    10 | 473.97 us | 275.72 us   |
| background | wait/io/file/sql/pid                 |     3 | 197.44 us | 167.97 us   |
| background | wait/io/file/sql/global_ddl_log      |     2 | 13.67 us  | 11.35 us    |
| root       | wait/io/file/csv/data                | 69443 | 1.35 s    | 277.94 ms   |
| root       | wait/io/file/csv/metadata            |    63 | 535.14 ms | 414.93 ms   |
| root       | wait/io/file/sql/FRM                 |   676 | 379.57 ms | 69.12 ms    |
| root       | wait/io/file/myisam/kfile            |  1458 | 188.04 ms | 49.70 ms    |
| root       | wait/io/file/myisam/dfile            |   532 | 137.41 ms | 66.26 ms    |
| root       | wait/io/file/sql/file_parser         |   156 | 3.52 ms   | 117.48 us   |
| root       | wait/io/file/sql/dbopt               |    16 | 494.32 us | 132.02 us   |
| root       | wait/io/file/innodb/innodb_data_file |     2 | 24.08 us  | 13.14 us    |
+------------+--------------------------------------+-------+-----------+-------------+
23 rows in set (0.00 sec)

root@localhost [sys]>

■STATEMENT_LATENCYなどを確認出来る。FULL_SCANなども確認可能。

root@localhost [sys]>select * from user_summary_by_statement_latency\G
*************************** 1. row ***************************
         user: root
        total: 92
total_latency: 1.37 s
  max_latency: 952.59 ms
 lock_latency: 934.38 ms
    rows_sent: 203
rows_examined: 2968
rows_affected: 0
   full_scans: 8
1 row in set (0.00 sec)

root@localhost [sys]>

■ステートメントのタイプ毎に状況が確認出来る

root@localhost [sys]>select * from user_summary_by_statement_type\G
*************************** 1. row ***************************
         user: root
    statement: select
        total: 10
total_latency: 839.20 ms
  max_latency: 766.46 ms
 lock_latency: 803.75 ms
    rows_sent: 36
rows_examined: 3133
rows_affected: 0
   full_scans: 5
*************************** 2. row ***************************
         user: root
    statement: Field List
        total: 78
total_latency: 463.95 ms
  max_latency: 114.47 ms
 lock_latency: 130.78 ms
    rows_sent: 0
rows_examined: 0
rows_affected: 0
   full_scans: 0

■IO処理とProcesslist_idが確認出来る。(Current)

root@localhost [sys]>select * from io_by_thread_by_latency\G
*************************** 1. row ***************************
          user: root@localhost
         total: 72370
 total_latency: 2.59 s
   min_latency: 4.68 ns
   avg_latency: 1.19 ms
   max_latency: 414.93 ms
     thread_id: 21
processlist_id: 1
*************************** 2. row ***************************
          user: main
         total: 1710
 total_latency: 1.85 s
   min_latency: 24.96 ns
   avg_latency: 2.71 ms
   max_latency: 195.80 ms
     thread_id: 1
processlist_id: NULL

■どのファイル処理に時間がかかっているか確認出来る

root@localhost [sys]>select * from io_global_by_file_by_latency limit 2\G
*************************** 1. row ***************************
         file: /usr/local/mysql-advanced-5.6.22-linux-glibc2.5-x86_64/data/mysql/general_log.CSV
        total: 64611
total_latency: 1.21 s
   count_read: 32253
 read_latency: 1.14 s
  count_write: 101
write_latency: 1.18 ms
   count_misc: 32257
 misc_latency: 66.27 ms
*************************** 2. row ***************************
         file: /usr/local/mysql-advanced-5.6.22-linux-glibc2.5-x86_64/data/ibdata1
        total: 532
total_latency: 706.08 ms
   count_read: 525
 read_latency: 696.01 ms
  count_write: 2
write_latency: 87.71 us
   count_misc: 5
 misc_latency: 9.98 ms
2 rows in set (0.00 sec)

root@localhost [sys]>

■■ Analysis Views
Object Overview
Table Usage
Index Usage
Show users only
schema_*

root@localhost [sys]>select * from schema_table_statistics limit 1\G
*************************** 1. row ***************************
     table_schema: mysql
       table_name: plugin
    total_latency: 0 ps
     rows_fetched: 0
    fetch_latency: 0 ps
    rows_inserted: 0
   insert_latency: 0 ps
     rows_updated: 0
   update_latency: 0 ps
     rows_deleted: 0
   delete_latency: 0 ps
 io_read_requests: 10     <---↓ IOの状況も確認可能
          io_read: 1.46 KiB
  io_read_latency: 33.01 ms
io_write_requests: 0
         io_write: 0 bytes
 io_write_latency: 0 ps
 io_misc_requests: 14
  io_misc_latency: 101.93 us
1 row in set (0.02 sec)

root@localhost &#91;sys&#93;>

root@localhost [sys]>select * from schema_index_statistics limit 1\G
*************************** 1. row ***************************
  table_schema: sys
    table_name: sys_config
    index_name: PRIMARY
 rows_selected: 0
select_latency: 0 ps
 rows_inserted: 0
insert_latency: 0 ps
  rows_updated: 0
update_latency: 0 ps
  rows_deleted: 0
delete_latency: 0 ps
1 row in set (0.00 sec)

root@localhost [sys]>


root@localhost [sys]>select * from schema_tables_with_full_table_scans;
+---------------+-------------+-------------------+
| object_schema | object_name | rows_full_scanned |
+---------------+-------------+-------------------+
| test          | T01_BKUP    |                 5 |
| test          | demo_test   |                 5 |
+---------------+-------------+-------------------+
2 rows in set (0.00 sec)

root@localhost [sys]>

InnoDBはインスタンス、MyISAM I/Oはtemporary table IOでの待ちを示している

root@localhost [sys]>select * from waits_global_by_latency;
+--------------------------------------+-------+---------------+-------------+-------------+
| events                               | total | total_latency | avg_latency | max_latency |
+--------------------------------------+-------+---------------+-------------+-------------+
| wait/io/file/csv/data                | 69530 | 1.35 s        | 19.37 us    | 277.94 ms   |
| wait/io/file/sql/FRM                 |  1664 | 1.17 s        | 704.45 us   | 195.80 ms   |
| wait/io/file/innodb/innodb_data_file |   625 | 855.73 ms     | 1.37 ms     | 37.70 ms    |
| wait/io/file/csv/metadata            |    63 | 535.14 ms     | 8.49 ms     | 414.93 ms   |
| wait/io/file/innodb/innodb_log_file  |    24 | 327.86 ms     | 13.66 ms    | 172.62 ms   |
| wait/io/file/myisam/kfile            |  1591 | 215.86 ms     | 135.68 us   | 49.70 ms    |
| wait/io/file/myisam/dfile            |   604 | 143.02 ms     | 236.79 us   | 66.26 ms    |
| wait/io/file/sql/slow_log            |    10 | 68.33 ms      | 6.83 ms     | 65.18 ms    |
| wait/io/file/sql/binlog              |    46 | 65.30 ms      | 1.42 ms     | 19.03 ms    |
| wait/io/table/sql/handler            |    11 | 30.13 ms      | 2.74 ms     | 29.94 ms    |
| wait/io/file/sql/binlog_index        |    31 | 29.84 ms      | 962.71 us   | 14.04 ms    |
| wait/io/file/mysys/cnf               |     5 | 21.57 ms      | 4.31 ms     | 21.51 ms    |
| wait/io/file/mysys/charset           |     3 | 13.95 ms      | 4.65 ms     | 13.87 ms    |
| wait/io/file/sql/ERRMSG              |     5 | 10.38 ms      | 2.08 ms     | 7.82 ms     |
| wait/io/file/sql/query_log           |    10 | 2.13 ms       | 213.12 us   | 1.26 ms     |
| wait/io/file/sql/file_parser         |    78 | 653.66 us     | 8.38 us     | 21.38 us    |
| wait/io/file/sql/dbopt               |    16 | 494.32 us     | 30.89 us    | 132.02 us   |
| wait/io/file/sql/casetest            |    10 | 473.97 us     | 47.40 us    | 275.72 us   |
| wait/io/file/sql/pid                 |     3 | 197.44 us     | 65.81 us    | 167.97 us   |
| wait/io/file/sql/global_ddl_log      |     2 | 13.67 us      | 6.84 us     | 11.35 us    |
| wait/lock/table/sql/handler          |     8 | 4.71 us       | 588.12 ns   | 1.13 us     |
+--------------------------------------+-------+---------------+-------------+-------------+
21 rows in set (0.01 sec)

root@localhost [sys]>

ユーザー毎のファイルIOが確認出来る

root@localhost [sys]>root@localhost [sys]>select * from waits_by_user_by_latency;
+------+--------------------------------------+-------+---------------+-------------+-------------+
| user | event                                | total | total_latency | avg_latency | max_latency |
+------+--------------------------------------+-------+---------------+-------------+-------------+
| root | wait/io/file/csv/data                | 69536 | 1.35 s        | 19.37 us    | 277.94 ms   |
| root | wait/io/file/csv/metadata            |    63 | 535.14 ms     | 8.49 ms     | 414.93 ms   |
| root | wait/io/file/sql/FRM                 |   676 | 379.57 ms     | 561.49 us   | 69.12 ms    |
| root | wait/io/file/myisam/kfile            |  1524 | 188.78 ms     | 123.87 us   | 49.70 ms    |
| root | wait/io/file/myisam/dfile            |   562 | 138.12 ms     | 245.76 us   | 66.26 ms    |
| root | wait/io/table/sql/handler            |    11 | 30.13 ms      | 2.74 ms     | 29.94 ms    |
| root | wait/io/file/innodb/innodb_data_file |    10 | 29.85 ms      | 2.99 ms     | 29.62 ms    |
| root | wait/io/file/sql/binlog              |    14 | 19.53 ms      | 1.40 ms     | 12.48 ms    |
| root | wait/io/file/sql/binlog_index        |    16 | 9.70 ms       | 606.37 us   | 6.12 ms     |
| root | wait/io/file/innodb/innodb_log_file  |     2 | 3.96 ms       | 1.98 ms     | 3.94 ms     |
| root | wait/io/file/sql/file_parser         |   156 | 3.52 ms       | 22.54 us    | 117.48 us   |
| root | wait/io/file/sql/slow_log            |     6 | 3.13 ms       | 521.60 us   | 3.05 ms     |
| root | wait/io/file/sql/query_log           |     6 | 830.11 us     | 138.35 us   | 793.00 us   |
| root | wait/io/file/sql/dbopt               |    16 | 494.32 us     | 30.89 us    | 132.02 us   |
| root | wait/lock/table/sql/handler          |     8 | 4.71 us       | 588.12 ns   | 1.13 us     |
+------+--------------------------------------+-------+---------------+-------------+-------------+
15 rows in set (0.00 sec)

root@localhost [sys]>


root@localhost [sys]>select * from statement_analysis limit 2\G
*************************** 1. row ***************************
            query: SELECT * FROM `sys` . `schema_ ... ATISTICS` . `TABLE_SCHEMA` ...
               db: sys
        full_scan: *
       exec_count: 1
        err_count: 0
       warn_count: 3
    total_latency: 766.46 ms
      max_latency: 766.46 ms
      avg_latency: 766.46 ms
     lock_latency: 760.44 ms
        rows_sent: 3
    rows_sent_avg: 3
    rows_examined: 374
rows_examined_avg: 374
       tmp_tables: 189 <--- Temp Tableの利用が確認出来る
  tmp_disk_tables: 33  <--- Temp Disk Tableの利用が確認出来る
      rows_sorted: 22
sort_merge_passes: 0
           digest: 5170dc5f93bc1119d5ae4e33c35b9a9a
       first_seen: 2015-02-07 22:01:36
        last_seen: 2015-02-07 22:01:36
*************************** 2. row ***************************
            query: SELECT `performance_schema` .  ... a` . `accounts` . `HOST` = ...
               db: sys
        full_scan: 
       exec_count: 1
        err_count: 0
       warn_count: 0
    total_latency: 114.47 ms
      max_latency: 114.47 ms
      avg_latency: 114.47 ms
     lock_latency: 106.12 ms
        rows_sent: 0
    rows_sent_avg: 0
    rows_examined: 0
rows_examined_avg: 0
       tmp_tables: 5
  tmp_disk_tables: 0
      rows_sorted: 0
sort_merge_passes: 0
           digest: 46f73fffb370a0c58fe74b1f0c71f85b
       first_seen: 2015-02-07 21:55:46
        last_seen: 2015-02-07 21:55:46
2 rows in set (0.00 sec)

root@localhost &#91;sys&#93;>

■SQLステートメントのエラー回数などが確認出来る

root@localhost [sys]>select * from statements_with_errors_or_warnings limit 3\G
*************************** 1. row ***************************
      query: `switch` 
         db: sys
 exec_count: 1
     errors: 1
  error_pct: 100.0000
   warnings: 0
warning_pct: 0.0000
 first_seen: 2015-02-07 22:30:18
  last_seen: 2015-02-07 22:30:18
     digest: 1a34fb3cdd7b61d8f9a688cff4d8ef1b
*************************** 2. row ***************************
      query: FLUSH `logsl` 
         db: sys
 exec_count: 1
     errors: 1
  error_pct: 100.0000
   warnings: 0
warning_pct: 0.0000
 first_seen: 2015-02-07 22:30:43
  last_seen: 2015-02-07 22:30:43
     digest: ff6a6729c6f04b36d5f57c0cf1bacfd0
*************************** 3. row ***************************
      query: SELECT * FROM `statemanet_analysis` LIMIT ? 
         db: sys
 exec_count: 1
     errors: 1
  error_pct: 100.0000
   warnings: 0
warning_pct: 0.0000
 first_seen: 2015-02-07 22:42:18
  last_seen: 2015-02-07 22:42:18
     digest: 577ed5b5d68f1e1cad11c030187cc58c
3 rows in set (0.00 sec)

root@localhost [sys]>

■ SQL StatementのTemp Tableの利用状況が確認出来る。

root@localhost [sys]>select * from statements_with_temp_tables limit 1\G
*************************** 1. row ***************************
                   query: SELECT * FROM `sys` . `schema_ ... ATISTICS` . `TABLE_SCHEMA` ...
                      db: sys
              exec_count: 1
           total_latency: 766.46 ms
       memory_tmp_tables: 189
         disk_tmp_tables: 33
avg_tmp_tables_per_query: 189
  tmp_tables_to_disk_pct: 17
              first_seen: 2015-02-07 22:01:36
               last_seen: 2015-02-07 22:01:36
                  digest: 5170dc5f93bc1119d5ae4e33c35b9a9a
1 row in set (0.00 sec)

root@localhost [sys]>

■SQLステートメントのFULLスキャンが確認出来る。

root@localhost [sys]>select * from statements_with_full_table_scans limit 1\G
*************************** 1. row ***************************
                   query: SELECT * FROM `sys` . `schema_ ... ATISTICS` . `TABLE_SCHEMA` ...
                      db: sys
              exec_count: 1
           total_latency: 766.46 ms
     no_index_used_count: 1
no_good_index_used_count: 0
       no_index_used_pct: 100
               rows_sent: 3
           rows_examined: 374
           rows_sent_avg: 3
       rows_examined_avg: 374
              first_seen: 2015-02-07 22:01:36
               last_seen: 2015-02-07 22:01:36
                  digest: 5170dc5f93bc1119d5ae4e33c35b9a9a
1 row in set (0.00 sec)

root@localhost [sys]>

■SHOW PROCESSLISTより多くの情報を確認出来る。

root@localhost [sys]>select * from processlist limit 2\G
*************************** 1. row ***************************
                thd_id: 1
               conn_id: NULL
                  user: sql/main
                    db: NULL
               command: NULL
                 state: System lock
                  time: 3410
     current_statement: INTERNAL DDL LOG RECOVER IN PROGRESS
          lock_latency: NULL
         rows_examined: NULL
             rows_sent: NULL
         rows_affected: NULL
            tmp_tables: NULL
       tmp_disk_tables: NULL
             full_scan: NO
        last_statement: NULL
last_statement_latency: NULL
             last_wait: NULL
     last_wait_latency: NULL
                source: NULL
*************************** 2. row ***************************
                thd_id: 21
               conn_id: 1
                  user: root@localhost
                    db: sys
               command: Query
                 state: Sending data
                  time: 0
     current_statement: select * from processlist limit 2
          lock_latency: 283.00 us
         rows_examined: 0
             rows_sent: 0
         rows_affected: 0
            tmp_tables: 2
       tmp_disk_tables: 0
             full_scan: YES
        last_statement: NULL
last_statement_latency: NULL
             last_wait: NULL
     last_wait_latency: NULL
                source: NULL
2 rows in set (0.00 sec)

root@localhost [sys]>

■ Functionsでデータを自動的に読みやすく変換してくれる。

root@localhost [sys]>select format_time(232222345) as time
    -> union select format_time(8323232323)
    -> union select format_time(8323232323234566);
+-----------+
| time      |
+-----------+
| 232.22 us |
| 8.32 ms   |
| 2.31h     |
+-----------+
3 rows in set (0.00 sec)

root@localhost [sys]>

root@localhost [sys]>select format_bytes(23456) as bytes
    -> union select format_bytes(23456789);
+-----------+
| bytes     |
+-----------+
| 22.91 KiB |
| 22.37 MiB |
+-----------+
2 rows in set (0.00 sec)

root@localhost [sys]>

Theadの情報をDUMPする事が可能

root@localhost [sys]>call ps_trace_thread(1,'/tmp/stack_1.dot',60,0.1,true,true,true);
+-------------------+
| summary           |
+-------------------+
| Disabled 1 thread |
+-------------------+
1 row in set (0.01 sec)

+--------------------------------------------+
| Info                                       |
+--------------------------------------------+
| Data collection starting for THREAD_ID = 1 |
+--------------------------------------------+
+-----------------------------------------+
| Info                                    |
+-----------------------------------------+
| Stack trace written to /tmp/stack_1.dot |
+-----------------------------------------+
1 row in set (4.07 sec)

+------------------------------------------------+
| Convert to PDF                                 |
+------------------------------------------------+
| dot -Tpdf -o /tmp/stack_1.pdf /tmp/stack_1.dot |
+------------------------------------------------+
1 row in set (4.07 sec)

+------------------------------------------------+
| Convert to PNG                                 |
+------------------------------------------------+
| dot -Tpng -o /tmp/stack_1.png /tmp/stack_1.dot |
+------------------------------------------------+
1 row in set (4.07 sec)

+------------------+
| summary          |
+------------------+
| Enabled 1 thread |
+------------------+
1 row in set (4.19 sec)

Query OK, 0 rows affected (4.19 sec)

root@localhost [sys]>

——メモ: GraphやPDF化するには以下のソフトが必要—–
PDF作成

[root@misc admin]# dot -Tpdf -o /tmp/stack_2.pdf /tmp/stack_2.dot
[root@misc admin]# ls -l /tmp/
合計 12
srwxrwxrwx. 1 mysql mysql   0  2月  7 21:55 mysql.sock
-rw-rw-rw-. 1 mysql mysql 293  2月  7 23:15 stack_1.dot
-rw-rw-rw-. 1 mysql mysql 293  2月  7 23:21 stack_2.dot
-rw-r--r--. 1 root  root  968  2月  7 23:30 stack_2.pdf
[root@misc admin]# 

以下、インストールログ


[root@misc admin]# wget http://www.graphviz.org/graphviz-rhel.repo
--2015-02-07 23:26:22--  http://www.graphviz.org/graphviz-rhel.repo
www.graphviz.org (www.graphviz.org) をDNSに問いあわせています... 204.178.9.49
www.graphviz.org (www.graphviz.org)|204.178.9.49|:80 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 1138 (1.1K) [text/plain]
`graphviz-rhel.repo' に保存中

100%[==============================================================================================================>] 1,138       --.-K/s 時間 0.003s  

2015-02-07 23:26:25 (321 KB/s) - `graphviz-rhel.repo' へ保存完了 [1138/1138]

[root@misc admin]# ls -l
合計 320
drwx------. 4 root  root     92  1月 24 22:59 backup
drwx------. 4 root  root     92  1月 25 21:15 backup0125manual
-rwxr-xr-x. 1 root  root 299008  1月  5 20:32 cpanm
-rw-r--r--. 1 root  root   1138  2月 17  2012 graphviz-rhel.repo
-rwxr-xr-x. 1 root  root    642  1月  5 14:18 memcached_fast.pl
-rwxr-xr-x. 1 root  root    316  1月  5 21:03 memcached_mysql.pl
drwxrwxrwx. 4 mysql root     92  1月 24 23:37 mysqlbackup
-rw-r--r--. 1 root  root  12582  3月 21  2013 rpmforge-release-0.5.3-1.el5.rf.x86_64.rpm

[root@misc admin]# yum install 'graphviz*'
読み込んだプラグイン:fastestmirror
Loading mirror speeds from cached hostfile
 * base: ftp.riken.jp
 * extras: ftp.riken.jp
 * rpmforge: ftp.kddilabs.jp
 * updates: ftp.riken.jp
依存性の解決をしています
--> トランザクションの確認を実行しています。
---> パッケージ graphviz.x86_64 0:2.30.1-18.el7 を インストール
--> 依存性の処理をしています: urw-fonts のパッケージ: graphviz-2.30.1-18.el7.x86_64
--> 依存性の処理をしています: librsvg-2.so.2()(64bit) のパッケージ: graphviz-2.30.1-18.el7.x86_64
--> 依存性の処理をしています: libpng15.so.15()(64bit) のパッケージ: graphviz-2.30.1-18.el7.x86_64
--> 依存性の処理をしています: libpangoft2-1.0.so.0()(64bit) のパッケージ: graphviz-2.30.1-18.el7.x86_64


[root@misc admin]# rpm -qa |grep -i graphviz
graphviz-2.30.1-18.el7.x86_64
graphviz-perl-2.30.1-18.el7.x86_64
graphviz-ocaml-2.30.1-18.el7.x86_64
graphviz-gd-2.30.1-18.el7.x86_64
graphviz-php-2.30.1-18.el7.x86_64
graphviz-guile-2.30.1-18.el7.x86_64
graphviz-graphs-2.30.1-18.el7.x86_64
graphviz-devel-2.30.1-18.el7.x86_64
graphviz-lua-2.30.1-18.el7.x86_64
graphviz-ruby-2.30.1-18.el7.x86_64
graphviz-doc-2.30.1-18.el7.x86_64
graphviz-tcl-2.30.1-18.el7.x86_64
graphviz-python-2.30.1-18.el7.x86_64
graphviz-java-2.30.1-18.el7.x86_64
[root@misc admin]# 

Sys Schema for MySQL 5.6 and MySQL 5.7

http://wiki.ducca.org/wiki/graphviz_%E3%81%AE%E3%82%A4%E3%83%B3%E3%82%B9%E3%83%88%E3%83%BC%E3%83%AB


MYSQL5.6がリリースされてから2週間経過して色々と検証してくださる方がいて、
MYSQL5.6からはPerformance_SchemaがDefaultになっているので少しオーバーヘッドが高いのでは?
などVariableの値が増えているなど色々と出てきました。

Review of MySQL 5.6 Defaults Changes
MySQL 5.5 and 5.6 default variable values differences
Is MySQL 5.6 slower than MySQL 5.5 ?
Performance Schema overhead
MySQL5.6で増えたexplicit_defaults_for_timestamp

Performance検証は今後時間をとって行いますが、先ずはVariableの変更部分だけ調べてみました。

variableの値は”331 rows in set”が返ってくる。

mysql> select @@hostname;
+---------------------+
| @@hostname          |
+---------------------+
| HOME001.localdomain |
+---------------------+
1 row in set (0.00 sec)

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

[root@HOME001 ~]# mysql -u root -e "show variables" -p |  awk '{ print $1}' > /home/mysql/5.5.29.txt
Enter password:
[root@HOME001 ~]#

variableの値は”435 rows in set”が返ってくる。

mysql> select @@hostname;
+---------------------+
| @@hostname          |
+---------------------+
| HOME002.localdomain |
+---------------------+
1 row in set (0.00 sec)

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

mysql>

[root@HOME002 ~]# mysql -u root -e "show variables" -p |  awk '{ print $1}' > 5.6.10.txt
Enter password:
[root@HOME002 ~]#

上記で取得した値をdiffコマンドで、増加・変更のあった値を調査してみる。

[root@HOME002 mysql]# diff -y -y --suppress-common-lines 5.5.29.txt 5.6.10.txt
                                                              > bind_address
                                                              > binlog_checksum
                                                              > binlog_max_flush_queue_time
                                                              > binlog_order_commits
                                                              > binlog_row_image
                                                              > binlog_rows_query_log_events
                                                              > core_file
                                                              > default_tmp_storage_engine
                                                              > disconnect_on_expired_password
engine_condition_pushdown                                     | end_markers_in_json
                                                              > enforce_gtid_consistency
                                                              > eq_range_index_dive_limit
                                                              > explicit_defaults_for_timestamp
                                                              > gtid_executed
                                                              > gtid_mode
                                                              > gtid_next
                                                              > gtid_owned
                                                              > gtid_purged
have_csv                                                      <
have_innodb                                                   <
have_ndbcluster                                               <
have_partitioning                                             <
                                                              > host_cache_size
                                                              > ignore_db_dirs
                                                              > innodb_adaptive_flushing_lwm
                                                              > innodb_api_bk_commit_interval
                                                              > innodb_api_disable_rowlock
                                                              > innodb_api_enable_binlog
                                                              > innodb_api_enable_mdl
                                                              > innodb_api_trx_level
                                                              > innodb_buffer_pool_dump_at_shutdown
                                                              > innodb_buffer_pool_dump_now
                                                              > innodb_buffer_pool_filename
                                                              > innodb_buffer_pool_load_abort
                                                              > innodb_buffer_pool_load_at_startup
                                                              > innodb_buffer_pool_load_now
                                                              > innodb_change_buffer_max_size
                                                              > innodb_checksum_algorithm
                                                              > innodb_cmp_per_index_enabled
                                                              > innodb_compression_failure_threshold_pct
                                                              > innodb_compression_level
                                                              > innodb_compression_pad_pct_max
                                                              > innodb_disable_sort_file_cache
                                                              > innodb_flush_log_at_timeout
                                                              > innodb_flush_neighbors
                                                              > innodb_flushing_avg_loops
                                                              > innodb_ft_aux_table
                                                              > innodb_ft_cache_size
                                                              > innodb_ft_enable_diag_print
                                                              > innodb_ft_enable_stopword
                                                              > innodb_ft_max_token_size
                                                              > innodb_ft_min_token_size
                                                              > innodb_ft_num_word_optimize
                                                              > innodb_ft_server_stopword_table
                                                              > innodb_ft_sort_pll_degree
                                                              > innodb_ft_user_stopword_table
                                                              > innodb_io_capacity_max
                                                              > innodb_lru_scan_depth
                                                              > innodb_max_dirty_pages_pct_lwm
                                                              > innodb_max_purge_lag_delay
                                                              > innodb_monitor_disable
                                                              > innodb_monitor_enable
                                                              > innodb_monitor_reset
                                                              > innodb_monitor_reset_all
                                                              > innodb_online_alter_log_max_size
                                                              > innodb_optimize_fulltext_only
                                                              > innodb_page_size
                                                              > innodb_print_all_deadlocks
                                                              > innodb_read_only
                                                              > innodb_sort_buffer_size
                                                              > innodb_stats_auto_recalc
                                                              > innodb_stats_persistent
                                                              > innodb_stats_persistent_sample_pages
                                                              > innodb_stats_transient_sample_pages
                                                              > innodb_sync_array_size
                                                              > innodb_undo_directory
                                                              > innodb_undo_logs
                                                              > innodb_undo_tablespaces
log                                                           <
                                                              > log_bin_basename
                                                              > log_bin_index
                                                              > log_bin_use_v1_row_events
log_slow_queries                                              | log_throttle_queries_not_using_indexes
                                                              > master_info_repository
                                                              > master_verify_checksum
max_long_data_size                                            <
                                                              > metadata_locks_hash_instances
                                                              > optimizer_trace
                                                              > optimizer_trace_features
                                                              > optimizer_trace_limit
                                                              > optimizer_trace_max_mem_size
                                                              > optimizer_trace_offset
                                                              > performance_schema_accounts_size
                                                              > performance_schema_digests_size
                                                              > performance_schema_events_stages_history_long_size
                                                              > performance_schema_events_stages_history_size
                                                              > performance_schema_events_statements_history_long_size
                                                              > performance_schema_events_statements_history_size
                                                              > performance_schema_hosts_size
                                                              > performance_schema_max_socket_classes
                                                              > performance_schema_max_socket_instances
                                                              > performance_schema_max_stage_classes
                                                              > performance_schema_max_statement_classes
                                                              > performance_schema_session_connect_attrs_size
                                                              > performance_schema_setup_actors_size
                                                              > performance_schema_setup_objects_size
                                                              > performance_schema_users_size
                                                              > pseudo_slave_mode
                                                              > relay_log_basename
                                                              > relay_log_info_repository
rpl_recovery_rank                                             | rpl_semi_sync_slave_enabled
rpl_semi_sync_master_enabled                                  | rpl_semi_sync_slave_trace_level
rpl_semi_sync_master_timeout                                  <
rpl_semi_sync_master_trace_level                              <
rpl_semi_sync_master_wait_no_slave                            <
                                                              > server_id_bits
                                                              > server_uuid
                                                              > slave_allow_batching
                                                              > slave_checkpoint_group
                                                              > slave_checkpoint_period
                                                              > slave_parallel_workers
                                                              > slave_pending_jobs_size_max
                                                              > slave_rows_search_algorithms
                                                              > slave_sql_verify_checksum
sql_big_tables                                                <
sql_low_priority_updates                                      <
sql_max_join_size                                             <
                                                              > ssl_crl
                                                              > ssl_crlpath
                                                              > table_open_cache_instances
                                                              > transaction_allow_batching
                                                              > tx_read_only
[root@HOME002 mysql]#

variables


SanDisk Extreme USB3.0 高速フラッシュメモリ SDCZ80 32GBのパフォーマンスが良いと聞いたので、
普段使いのUSBとして使えると思い購入してみました。

実際にパフォーマンスを確認してみたら、以前パフォーマンスを計測したときと同じマシーンで
ローカルドライブやSDカードと比較してもかなり良い数値が計測出来ました。

OLD PC
crystaldisk

NEW PC
latest-pc
昨年購入したノートPCのローカルドライブはやはり5年前に購入したノートPCと比較してもローカルドライブの処理スピードが大幅に違う様です。

前回の検証
CrystalDiskMarkによるi/o性能確認

製品
SanDisk Extreme USB3.0 高速フラッシュメモリ SDCZ80 32GB サンディスク 海外リテール[並行輸入品]


システムのボトルネック発見の為に、参考になるサイトを確認していたらGDB,AWK,SORTなどの
普段の運用で利用するコマンドを利用してボトルネックを確認するシェルを作成している人がいたので
参考にさせていただきました。Profilerとして利用するのに良さそうです。

GDB
GDB, the GNU Project debugger, allows you to see what is going on `inside’ another
program while it executes — or what another program was doing at the moment it crashed.

#!/bin/bash
nsamples=1
sleeptime=0
pid=$(pidof mysqld)

for x in $(seq 1 $nsamples)
  do
    gdb -ex "set pagination 0" -ex "thread apply all bt" -batch -p $pid
    sleep $sleeptime
  done | \
awk '
  BEGIN { s = ""; } 
  /Thread/ { print s; s = ""; } 
  /^\#/ { if (s != "" ) { s = s "," $4} else { s = $4 } } 
  END { print s }' | \
sort | uniq -c | sort -r -n -k 1,1

GDBがインストールされて無かったのでインストール

[root@CentOS64VM tools]# yum install gdb
Loaded plugins: fastestmirror, presto
Loading mirror speeds from cached hostfile
* base: ftp.nara.wide.ad.jp
* extras: ftp.nara.wide.ad.jp
* updates: ftp.nara.wide.ad.jp
Setting up Install Process
Resolving Dependencies
–> Running transaction check
—> Package gdb.x86_64 0:7.2-56.el6 will be installed
–> Finished Dependency Resolution

Dependencies Resolved

=====================================================================================
Package Arch Version Repository  Size
=====================================================================================
Installing:
gdb    x86_64 7.2-56.el6   base   2.3 M

Transaction Summary
=====================================================================================
Install 1 Package(s)

Total download size: 2.3 M
Installed size: 5.2 M
Is this ok [y/N]: y
Downloading Packages:
Setting up and reading Presto delta metadata
Processing delta metadata
Package(s) data still to download: 2.3 M

[省略]

Trying other mirror.
gdb-7.2-56.el6.x86_64.rpm           | 2.3 MB 00:13
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Warning: RPMDB altered outside of yum.
Installing : gdb-7.2-56.el6.x86_64       1/1
Verifying : gdb-7.2-56.el6.x86_64 1/1

Installed:
gdb.x86_64 0:7.2-56.el6

Complete!
[root@CentOS64VM tools]#

GDBとスクリプトの準備が出来たので実行
Profiler

TPCC-MYSQLを実行して負荷をかけた状態でシステム状態を確認してみる。

[root@CentOS64VM tools]# ./poor_mans_profiler.sh
20
2 pthread_cond_wait@@GLIBC_2.3.2,os_cond_wait,reset_sig_count=2912),log_write_up_to,trx_commit_complete_for_mysql,innobase_commit,ha_commit_one_phase,he_command,mysql_parse,dispatch_command,do_handle_one_connection,handle_one_connection,pfs_spawn_thread,start_thread,clone
1 sigwait,signal_hand,pfs_spawn_thread,start_thread,clone
1 select,os_thread_sleep,srv_master_thread,start_thread,clone
1 pthread_cond_timedwait@@GLIBC_2.3.2,os_cond_wait_timed,time_in_usec=

こちらも合わせて確認:gdb -p `pidof mysqld`
How to debug lock (hang)

参考サイト
poor man’s profiler
漢(オトコ)のコンピュータ道
poormans-profiler
GDB: The GNU Project Debugger
ファイヤープロジェクト


FREE、TOPなどでもシステム状況を把握して問題解決やリソースのプランニングが可能だが、
他にもいくつか参考になるコマンドがあるのでメモ。

CPU使用率TOP10
ps auxw | sort -k3 -nr | head -n 10
cpu利用率

MEM使用率TOP10
ps auxw | sort -k4 -nr | head -n 10
mem利用率

パフォーマンス確認に便利なdstat
Usage: dstat [-afv] [options..] [delay [count]]

インストール
[root@ip-xx-xxx-xx-xxx ec2-user]# yum install dstat
Loaded plugins: fastestmirror, priorities, security, update-motd
Loading mirror speeds from cached hostfile
* amzn-main: packages.ap-northeast-1.amazonaws.com
* amzn-updates: packages.ap-northeast-1.amazonaws.com
amzn-main | 2.1 kB 00:00
amzn-updates | 2.3 kB 00:00
Setting up Install Process
Resolving Dependencies
–> Running transaction check
—> Package dstat.noarch 0:0.7.0-1.5.amzn1 will be installed
–> Finished Dependency Resolution

Dependencies Resolved

=======================================================================================
Package Arch Version Repository Size
=======================================================================================
Installing:
dstat noarch 0.7.0-1.5.amzn1 amzn-main 182 k

Transaction Summary
=======================================================================================
Install 1 Package(s)

Total download size: 182 k
Installed size: 660 k
Is this ok [y/N]: y
Downloading Packages:
dstat-0.7.0-1.5.amzn1.noarch.rpm | 182 kB 00:00
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : dstat-0.7.0-1.5.amzn1.noarch 1/1

Installed:
dstat.noarch 0:0.7.0-1.5.amzn1

Complete!
[root@ip-xx-xxx-xx-xxx ec2-user]#

dstat –top-bioで最もブロックI/Oしているプロセス確認。
dstat –top-ioで最もI/Oが多いプロセスを確認。

[root@ip-xx-xxx-xx-xxx ec2-user]# dstat --top-bio
—-most-expensive—-
block i/o process
init 66k 726B

[root@ip-xx-xxx-xx-xxx ec2-user]# dstat --top-io
—-most-expensive—-
i/o process
bash 82k 1351B
sshd: ec2-u 146B 248B
sshd: ec2-u 78B 168B
sshd: ec2-u 78B 168B
sshd: ec2-u 78B 168B
sshd: ec2-u 78B 168B
sshd: ec2-u 78B 168B

dstat –tcp でTCPコネクション確認
dstat –net でネットワーク確認

[ip-xx-xxx-xx-xxx]#dstat --tcp --net

net状態

[root@ip-xx-xxx-xx-xxx ec2-user]# dstat --top-cpu
-most-expensive-
cpu process
httpd 0.1
mysqld 1.0

[root@ip-xx-xxx-xx-xxx ec2-user]# dstat --net
-net/total-
recv send
0 0
80B 308B
40B 260B
40B 260B
40B 260B
40B 260B
5925B 35k
1710B 24k
4120B 37k
256B 298B
40B 154B
[root@ip-xx-xxx-xx-xxx ec2-user]#

CPUとネットの状況を同時に確認

[root@ip-xx-xxx-xx-xxx ec2-user]# dstat -a
—-total-cpu-usage—- -dsk/total- -net/total- —paging– —system–
usr sys idl wai hiq siq| read writ| recv send| in out | int csw
1 0 98 0 0 0| 92k 12k| 0 0 | 0 0 | 36 66
0 0 100 0 0 0| 0 0 | 80B 916B| 0 0 | 20 46
0 0 100 0 0 0| 0 0 | 40B 452B| 0 0 | 18 44
0 0 100 0 0 0| 0 0 | 40B 452B| 0 0 | 18 46
0 0 100 0 0 0| 0 0 | 40B 452B| 0 0 | 20 46
0 0 100 0 0 0| 0 0 | 40B 452B| 0 0 | 19 47
0 0 100 0 0 0| 0 0 | 40B 452B| 0 0 | 16 42
0 0 100 0 0 0| 0 0 | 40B 452B| 0 0 | 19 46
1 0 99 0 0 0| 0 0 | 40B 452B| 0 0 | 18 43
[root@ip-xx-xxx-xx-xxx ec2-user]#

-a, --all equals -cdngy (default)
all

dtatオプション

[ip-xx-xxx-xx-xxx]#dstat –help
Usage: dstat [-afv] [options..] [delay [count]]
Versatile tool for generating system resource statistics

Dstat options:
-c, –cpu enable cpu stats
-C 0,3,total include cpu0, cpu3 and total
-d, –disk enable disk stats
-D total,hda include hda and total
-g, –page enable page stats
-i, –int enable interrupt stats
-I 5,eth2 include int5 and interrupt used by eth2
-l, –load enable load stats
-m, –mem enable memory stats
-n, –net enable network stats
-N eth1,total include eth1 and total
-p, –proc enable process stats
-r, –io enable io stats (I/O requests completed)
-s, –swap enable swap stats
-S swap1,total include swap1 and total
-t, –time enable time/date output
-T, –epoch enable time counter (seconds since epoch)
-y, –sys enable system stats

–aio enable aio stats
–fs, –filesystem enable fs stats
–ipc enable ipc stats
–lock enable lock stats
–raw enable raw stats
–socket enable socket stats
–tcp enable tcp stats
–udp enable udp stats
–unix enable unix stats
–vm enable vm stats

–plugin-name enable plugins by plugin name (see manual)
–list list all available plugins

-a, –all equals -cdngy (default)
-f, –full automatically expand -C, -D, -I, -N and -S lists
-v, –vmstat equals -pmgdsc -D total

–bw, –blackonwhite change colors for white background terminal
–float force float values on screen
–integer force integer values on screen
–nocolor disable colors (implies –noupdate)
–noheaders disable repetitive headers
–noupdate disable intermediate updates
–output file write CSV output to file

delay is the delay in seconds between each update (default: 1)
count is the number of updates to display before exiting (default: unlimited)

[ip-xx-xxx-xx-xxx]#

上記オプションを適切に使い分けて、最適なシステム状況診断と
対応が出来るようにしておくと良さそうです。

dstat --cpu --mem --disk --page --int --load --net
general

dstat -N eth0
interface

dstat --time --proc --swap --sys --ipc
date

dstat --full
full

dstat --vmstat
vmstat


--integer 整数値を表示します
--nocolor カラー表示を無効にします
--noheaders ヘッダー表示を無効にします
--noupdate 仲介更新を無効にします
--output file 出力結果を CSV ファイルに書き出します

参考
Dstat: Versatile resource statistics tool


サーバーを購入したり、ディスク交換や追加した場合に、
単純にI/O性能を測る事が出来るツールを確認。
前回は、Linuxのhdparmを検証しました。

CrystalDiskMark

ダウンロードページ
2012年2月12日[7/2008/Vista/2003/XP/2000, x86/x64, i18n]
http://crystalmark.info/download/index.html#CrystalDiskMark

インストールしないでもベンチマーク出来る方が、
何処でも利用出来て敷居が低いのでポータブル版をダウンロードして利用。

ダウンロードして展開
展開

実行ファイルをクリック(32bit/64bit版)
実行

対象ディスクの選択

ローカルディスクで実行
ローカル

外部SDカードで実行

それぞれのテストは100MBのファイル設定で2回ずつ実行。(Defaultは5回実行)
テストループ回数は多い程、サンプリングデータが増えてより正確なデータを得る
事が出来ると考える。


TOPコマンドでパフォーマンスを確認する事で問題ないのですが、
少しだけビジュアライズされてキーボードやマウスで簡単に操作出来るように
なっているHTOPでパフォーマンス確認をより身近にする事で気付ける事も
あるかもしれません。

ダウンロード

[root@colinux htop]# wget http://citylan.dl.sourceforge.net/project/htop/htop/0.8.3/htop-0.8.3.tar.gz
–2012-06-15 09:58:08– http://citylan.dl.sourceforge.net/project/htop/htop/0.8.3/htop-0.8.3.tar.gz
citylan.dl.sourceforge.net をDNSに問いあわせています… 212.118.44.106, 2a03:1800:1:7::2
citylan.dl.sourceforge.net|212.118.44.106|:80 に接続しています… 接続しました。
HTTP による接続要求を送信しました、応答を待っています… 200 OK
長さ: 428061 (418K) [application/x-gzip]
`htop-0.8.3.tar.gz’ に保存中

100%[====================================================================================>] 428,061 71.5K/s 時間 5.9s

2012-06-15 09:58:15 (71.5 KB/s) – `htop-0.8.3.tar.gz’ へ保存完了 [428061/428061]

[root@colinux htop]#

展開

[root@colinux htop]# tar xzvf htop-0.8.3.tar.gz
htop-0.8.3/
htop-0.8.3/Makefile.am
htop-0.8.3/ClockMeter.h
htop-0.8.3/depcomp
htop-0.8.3/Meter.c
htop-0.8.3/htop.1.in
htop-0.8.3/SignalItem.c
htop-0.8.3/plpa-1.1/
htop-0.8.3/plpa-1.1/Makefile.am
htop-0.8.3/plpa-1.1/src/
htop-0.8.3/plpa-1.1/src/Makefile.am
htop-0.8.3/plpa-1.1/src/plpa.h.in
htop-0.8.3/plpa-1.1/src/plpa_dispatch.c
[…省略]
htop-0.8.3/acinclude.m4
htop-0.8.3/Object.c
htop-0.8.3/htop.c
htop-0.8.3/autogen.sh
htop-0.8.3/AvailableMetersPanel.c
[root@colinux htop]#

インストール [configure -> make -> make install]

[root@colinux htop-0.8.3]# ./configure
checking for a BSD-compatible install… /usr/bin/install -c
checking whether build environment is sane… yes
checking for a thread-safe mkdir -p… /bin/mkdir -p
checking for gawk… gawk
checking whether make sets $(MAKE)… yes
checking for gcc… gcc
checking for C compiler default output file name… a.out
checking whether the C compiler works… yes
checking whether we are cross compiling… no
checking for suffix of executables…
checking for suffix of object files… o
checking whether we are using the GNU C compiler… yes
[…省略]
checking for unistd.h… (cached) yes
checking curses.h usability… no
checking curses.h presence… no
checking for curses.h… no
configure: error: missing headers: curses.h
[root@colinux htop-0.8.3]#

configureでエラーになったので必要なパッケージをインストール
エラー内容:configure: error: missing headers: curses.h

[root@colinux htop-0.8.3]# yum install ncurses-devel
fedora 100% |=========================| 2.1 kB 00:00
updates 100% |=========================| 2.3 kB 00:00
Setting up Install Process
Parsing package install arguments

今回は無事にconfigureが完了したので、
makeとmake installしてインストールを完了。

configure: creating ./config.status
config.status: creating plpa-1.1/Makefile
config.status: creating plpa-1.1/src/Makefile
config.status: creating Makefile
config.status: creating htop.1
config.status: creating config.h
config.status: config.h is unchanged
config.status: creating plpa-1.1/src/plpa_config.h
config.status: creating plpa-1.1/src/plpa.h
config.status: executing depfiles commands
[root@colinux htop-0.8.3]#
[root@colinux htop-0.8.3]# make
[…省略]
[root@colinux htop-0.8.3]# make install
[…省略]

htopを起動してみる。
パスを指定しなければ以下のパスにインストールされている。

[root@colinux htop-0.8.3]# whereis htop
htop: /usr/local/bin/htop
[root@colinux htop-0.8.3]#
[root@colinux htop-0.8.3]# htop

起動後はF1~F10キーでNICEやTREEなども操作出来る。

通常起動後

htop

htop


mysqlslapはMySQLサーバのクライアント負荷をエミュレートし、各ステージのタイミングを
報告する診断プログラムです。サーバにたいして複数のクライアントがアクセスしているかのように
作動します。mysqlslapはMySQL 5.1.4.から提供されています。

[root@study01 bin]# pwd
/usr/local/mysql/bin
[root@study01 bin]#./mysqlslap 
--no-defaults --auto-generate-sql --auto-generate-sql-guid-primary --engine=myisam 
--number-int-cols=3 --number-char-cols=5 --concurrency=3 --auto-generate-sql-write-number=100 
--auto-generate-sql-execute-number=1000 --auto-generate-sql-load-type=mixed  -u root -p

Enter password:
Benchmark
        Running for engine myisam
        Average number of seconds to run all queries: 0.813 seconds
        Minimum number of seconds to run all queries: 0.813 seconds
        Maximum number of seconds to run all queries: 0.813 seconds
        Number of clients running queries: 3
        Average number of queries per client: 1000

[root@study01 bin]#./mysqlslap 
--no-defaults --auto-generate-sql --auto-generate-sql-guid-primary --engine=myisam 
--number-int-cols=3 --number-char-cols=5 --concurrency=5 --auto-generate-sql-write-number=100 
--auto-generate-sql-execute-number=1000 --auto-generate-sql-load-type=mixed  -u root -p

Enter password:
Benchmark
        Running for engine myisam
        Average number of seconds to run all queries: 1.448 seconds
        Minimum number of seconds to run all queries: 1.448 seconds
        Maximum number of seconds to run all queries: 1.448 seconds
        Number of clients running queries: 5
        Average number of queries per client: 1000
[root@study01 bin]#

3つの同時接続結果

con4

5つの同時接続結果

con5

mysqlslap実行中に他のターミナルからProcessを確認してみると以下のような感じで
SQL文が実行されていた。

query

[参考URL]

7.16. mysqlslap — クライアント負荷エミュレーション


[MySQLウォッチ]第42回 性能検査ツールmysqlslapとMySQLコンファレンス報告


SELECTのパフォーマンスに関係ある3つのシステム変数

sort_buffer_size ——> Order by , Grop byのパフォーマンス
join_buffer_size ——> JOINのパフォーマンス …. MYSQLのパフォーマンス
key_buffer_size ——> index関連するステートメント(MYISAM固有)

buffer_size

———————————————————-
おまけ: テイキメンテナンスを忘れずに
———————————————————-
※ mysqlcheck -u root -p –auto-repair –check –optimize –all-databases

※ Query Cache はSelectのスピードアップに大切です。

mysql> show variables like ‘query_cache%’;
+——————————+———+
| Variable_name | Value |
+——————————+———+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+——————————+———+
5 rows in set (0.01 sec)

mysql>

——————————————————————————————————-
Key Cache Efficiency = 1 – (KEY_READS / KEY_READ_REQUESTS)
——————————————————————————————————-

select 1 – ((select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = ‘KEY_READS’)
/ (select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = ‘KEY_READ_REQUESTS’))
as ‘Key cache efficiency’;

key_cache_efficiency

もしkey_buffer_sizeが小さくて、メモリーがあまっていたら以下のようにメモリーを
key_bufferに与えて下さい。

mysql> set global key_buffer_size = 5 * 1024 * 1024;

key_buffer_size