![variable.jp [データベース,パフォーマンス,運用]](https://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
MySQL Cluster7.5からPK参照以外の参照性能が向上している。
テーブル作成時にREAD_BACKUPオプションを利用するか、ndb_read_backupオプションを設定しておくとPRIMARYレプリカだけでなく、SECONDARYレプリカからもデータが参照する事が出来るようになる。
MySQL7.4までは常に、PRIMARYレプリカからデータを参照していたので、ローカルにPRIMARYデータが無い場合は、PRIMARYデータを持っているノードまで取得しにいく必要があった。
イメージ
参照：
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/