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/

Comments are closed.

Post Navigation