반응형

노드 장애 테스트

 

현재 MaxScale (1대) + Xpand Node(4대)로 구성된 상태에서 , HammerDB를 이용하여 tpcc 데이터를 만들고,

HammerDB를 이용하여 40세션을 붙여서 부하를 주고 있는 상황에서 , xpand3 Server가 Down되는 시나리오.

시나리오에서는 xpand3 서버를 종료 했다가, 일정 시간이 지난 후 다시 기동함.

만약 진짜 xpand3 서버의 O/S가 문제가 있어서 xpand node를 신규로 구성해야 한다면 "[MariaDB] Xpand + MaxScale #5" 포스팅을 확인 하여 추가를 시켜주면 된다.

 

현 상태 확인

 

-- maxscale 에서 확인

40개 세션이 xpand1 ~ xpand4까지 고르게 분산 되어 있음을 확인 할 수 있다.



 

-- Xpand Node에서 확인

mysql --user=root --password --no-auto-rehash

use system

MySQL [system]> select nodeid , session_id , source_ip , user , trx_state , statement_state , substr(last_statement,1,20)
    -> from  sessions where database = 'tpcc' ;
+--------+------------+--------------+---------------------+-----------+-----------------+-----------------------------+
| nodeid | session_id | source_ip    | user                | trx_state | statement_state | substr(last_statement,1,20) |
+--------+------------+--------------+---------------------+-----------+-----------------+-----------------------------+
|      1 |      10241 | 10.70.101.20 | 7069997881645258753 | open      | finished        | select @p_c_id,@p_c_        |
|      1 |      19457 | 10.70.101.20 | 7069997881645258753 | open      | finished        | select @p_c_id,@p_c_        |
|      1 |      11265 | 10.70.101.20 | 7069997881645258753 | open      | finished        | select @disc,@last,@        |
|      1 |      15361 | 10.70.101.20 | 7069997881645258753 | open      | finished        | select @p_c_id,@p_c_        |
|      1 |      13313 | 10.70.101.20 | 7069997881645258753 | open      | finished        | select @p_c_id,@p_c_        |
|      1 |      14337 | 10.70.101.20 | 7069997881645258753 | open      | finished        | select @disc,@last,@        |
|      1 |      12289 | 10.70.101.20 | 7069997881645258753 | open      | executing       | SELECT i_price, i_na        |
|      1 |      16385 | 10.70.101.20 | 7069997881645258753 | open      | finished        | select @p_c_id,@p_c_        |
|      1 |       9217 | 10.70.101.20 | 7069997881645258753 | open      | finished        | select @os_c_id,@os_        |
|      1 |      18433 | 10.70.101.20 | 7069997881645258753 | open      | finished        | select @p_c_id,@p_c_        |
|      4 |      30724 | 10.70.101.20 | 7069997881645258753 | open      | finished        | select @os_c_id,@os_        |
|      4 |      28676 | 10.70.101.20 | 7069997881645258753 | open      | finished        | select @disc,@last,@        |
|      4 |      23556 | 10.70.101.20 | 7069997881645258753 | open      | finished        | select @p_c_id,@p_c_        |
|      4 |      29700 | 10.70.101.20 | 7069997881645258753 | open      | finished        | select @p_c_id,@p_c_        |
|      4 |      31748 | 10.70.101.20 | 7069997881645258753 | open      | finished        | CALL SLEV(7,2,16)           |
|      4 |      25604 | 10.70.101.20 | 7069997881645258753 | open      | finished        | select @disc,@last,@        |
|      4 |      27652 | 10.70.101.20 | 7069997881645258753 | open      | finished        | select @os_c_id,@os_        |
|      4 |      26628 | 10.70.101.20 | 7069997881645258753 | open      | finished        | select @p_c_id,@p_c_        |
|      4 |      32772 | 10.70.101.20 | 7069997881645258753 | open      | finished        | select @disc,@last,@        |
|      4 |      24580 | 10.70.101.20 | 7069997881645258753 | open      | finished        | select @p_c_id,@p_c_        |
|      3 |      21507 | 10.70.101.20 | 7069997881645258753 | open      | finished        | select @disc,@last,@        |
|      3 |      19459 | 10.70.101.20 | 7069997881645258753 | open      | finished        | select @p_c_id,@p_c_        |
|      3 |      24579 | 10.70.101.20 | 7069997881645258753 | open      | finished        | select @disc,@last,@        |
|      3 |      22531 | 10.70.101.20 | 7069997881645258753 | open      | finished        | select @disc,@last,@        |
|      3 |      17411 | 10.70.101.20 | 7069997881645258753 | open      | finished        | set @p_c_id = 2758,         |
|      3 |      23555 | 10.70.101.20 | 7069997881645258753 | open      | finished        | select @os_c_id,@os_        |
|      2 |      75778 | 10.70.101.20 | 7069997881645258753 | open      | executing       | SET no_s_quantity =         |
|      3 |      16387 | 10.70.101.20 | 7069997881645258753 | open      | finished        | select @disc,@last,@        |
|      2 |      69634 | 10.70.101.20 | 7069997881645258753 | open      | finished        | select @disc,@last,@        |
|      3 |      25603 | 10.70.101.20 | 7069997881645258753 | open      | finished        | CALL DELIVERY(7,9,20        |
|      2 |      76802 | 10.70.101.20 | 7069997881645258753 | open      | finished        | select @disc,@last,@        |
|      3 |      20483 | 10.70.101.20 | 7069997881645258753 | open      | finished        | select @disc,@last,@        |
|      2 |      71682 | 10.70.101.20 | 7069997881645258753 | open      | finished        | CALL DELIVERY(9,2,20        |
|      3 |      18435 | 10.70.101.20 | 7069997881645258753 | open      | finished        | select @p_c_id,@p_c_        |
|      2 |      77826 | 10.70.101.20 | 7069997881645258753 | open      | finished        | select @p_c_id,@p_c_        |
|      2 |      73730 | 10.70.101.20 | 7069997881645258753 | open      | finished        | CALL SLEV(4,2,18)           |
|      2 |      74754 | 10.70.101.20 | 7069997881645258753 | open      | finished        | select @p_c_id,@p_c_        |
|      2 |      68610 | 10.70.101.20 | 7069997881645258753 | open      | finished        | select @disc,@last,@        |
|      2 |      70658 | 10.70.101.20 | 7069997881645258753 | open      | finished        | select @disc,@last,@        |
|      2 |      72706 | 10.70.101.20 | 7069997881645258753 | open      | finished        | select @disc,@last,@        |
+--------+------------+--------------+---------------------+-----------+-----------------+-----------------------------+
40 rows in set (0.00 sec)

MySQL [system]> select username , host , user from users ;

+------------------+--------------+---------------------+
| username         | host         | user                |
+------------------+--------------+---------------------+
| app              | %            | 7069997881645258753 |
| clx_maint        | 127.0.0.1    |                4110 |
| clx_view_definer | 127.0.0.1    |                4112 |
| max              | 10.70.101.20 | 7068584483821799425 |
| mysql_slave      |              |                4100 |
| root             | %            | 7069555885833806849 |
| root             | 127.0.0.1    |                4099 |
| xpand            | localhost    | 7068526298150719489 |
| xpandm           | localhost    | 7068526299347743745 |
+------------------+--------------+---------------------+
9 rows in set (0.00 sec)

MySQL [system]> select main.`table` , main.`index` , main.slice , main.nodeid , main.rank , repl.nodeid as replica_node , repl.rank as replca_rank , main.bytes , (main.bytes + repl.bytes)/1024/1024 as slice_sum_mb

    -> from   (select * from table_replicas where database = 'tpcc' and rank = 0 ) main ,
    ->        (select * from table_replicas where database = 'tpcc' and rank = 1 ) repl
    -> where  main.`table` = repl.`table`
    -> and    main.`index` = repl.`index`
    -> and    main.slice = repl.slice
    -> order by main.`table`, main.`index` , main.nodeid ;
+------------+---------------------------+---------------------+--------+------+--------------+-------------+-----------+--------------+
| table      | index                     | slice               | nodeid | rank | replica_node | replca_rank | bytes     | slice_sum_mb |
+------------+---------------------------+---------------------+--------+------+--------------+-------------+-----------+--------------+
| customer   | c_w_id                    | 7070001415015847937 |      1 |    0 |            4 |           1 |   8814592 |  16.93750000 |
| customer   | c_w_id                    | 7070001415015848961 |      2 |    0 |            3 |           1 |   6225920 |  16.96875000 |
| customer   | c_w_id                    | 7070001415015851009 |      3 |    0 |            2 |           1 |  10354688 |  15.31250000 |
| customer   | c_w_id                    | 7070001415015849985 |      4 |    0 |            1 |           1 |   9994240 |  19.06250000 |
| customer   | __idx_customer__PRIMARY   | 7070001415015835649 |      1 |    0 |            3 |           1 |  37027840 |  71.65625000 |
| customer   | __idx_customer__PRIMARY   | 7070001415015836673 |      2 |    0 |            4 |           1 |  53510144 | 109.81250000 |
| customer   | __idx_customer__PRIMARY   | 7070001415015837697 |      3 |    0 |            1 |           1 |  40271872 |  76.75000000 |
| customer   | __idx_customer__PRIMARY   | 7070001415015834625 |      4 |    0 |            2 |           1 |  58720256 | 112.00000000 |
| district   | __idx_district__PRIMARY   | 7070001415239200769 |      1 |    0 |            3 |           1 |     65536 |   0.12500000 |
| district   | __idx_district__PRIMARY   | 7070001415239199745 |      2 |    0 |            4 |           1 |     65536 |   0.12500000 |
| district   | __idx_district__PRIMARY   | 7070001415239202817 |      3 |    0 |            1 |           1 |     65536 |   0.12500000 |
| district   | __idx_district__PRIMARY   | 7070001415239201793 |      4 |    0 |            2 |           1 |     65536 |   0.12500000 |
| history    | __base_history            | 7070001415325110273 |      1 |    0 |            4 |           1 |   7798784 |  15.06250000 |
| history    | __base_history            | 7070001415325107201 |      2 |    0 |            3 |           1 |   7766016 |  20.65625000 |
| history    | __base_history            | 7070001415325109249 |      3 |    0 |            2 |           1 |  13828096 |  20.62500000 |
| history    | __base_history            | 7070001415325108225 |      4 |    0 |            1 |           1 |   7995392 |  15.00000000 |
| item       | __idx_item__PRIMARY       | 7070001415402424321 |      1 |    0 |            2 |           1 |   5013504 |   7.43750000 |
| item       | __idx_item__PRIMARY       | 7070001415402426369 |      2 |    0 |            1 |           1 |   2785280 |   7.43750000 |
| item       | __idx_item__PRIMARY       | 7070001415402425345 |      3 |    0 |            4 |           1 |   5144576 |   7.56250000 |
| item       | __idx_item__PRIMARY       | 7070001415402427393 |      4 |    0 |            3 |           1 |   2752512 |   7.50000000 |
| new_order  | __idx_new_order__PRIMARY  | 7070001415479744513 |      1 |    0 |            2 |           1 |   2260992 |   3.75000000 |
| new_order  | __idx_new_order__PRIMARY  | 7070001415479742465 |      2 |    0 |            1 |           1 |   1114112 |   2.75000000 |
| new_order  | __idx_new_order__PRIMARY  | 7070001415479743489 |      3 |    0 |            4 |           1 |   4390912 |   6.93750000 |
| new_order  | __idx_new_order__PRIMARY  | 7070001415479741441 |      4 |    0 |            3 |           1 |   2195456 |   5.21875000 |
| orders     | o_w_id                    | 7070001415557074945 |      1 |    0 |            3 |           1 |   7110656 |  14.09375000 |
| orders     | o_w_id                    | 7070001415557071873 |      2 |    0 |            4 |           1 |   6356992 |  12.12500000 |
| orders     | o_w_id                    | 7070001415557072897 |      3 |    0 |            1 |           1 |   7143424 |  13.21875000 |
| orders     | o_w_id                    | 7070001415557073921 |      4 |    0 |            2 |           1 |   8388608 |  16.00000000 |
| orders     | __idx_orders__PRIMARY     | 7070001415557059585 |      1 |    0 |            3 |           1 |   7733248 |  15.25000000 |
| orders     | __idx_orders__PRIMARY     | 7070001415557058561 |      2 |    0 |            4 |           1 |   8093696 |  15.43750000 |
| orders     | __idx_orders__PRIMARY     | 7070001415557061633 |      3 |    0 |            1 |           1 |   9732096 |  17.93750000 |
| orders     | __idx_orders__PRIMARY     | 7070001415557060609 |      4 |    0 |            2 |           1 |   9863168 |  18.81250000 |
| order_line | __idx_order_line__PRIMARY | 7070001415647263745 |      1 |    0 |            4 |           1 |  48889856 |  93.34375000 |
| order_line | __idx_order_line__PRIMARY | 7070001415647260673 |      2 |    0 |            3 |           1 |  73465856 | 140.12500000 |
| order_line | __idx_order_line__PRIMARY | 7070001415647262721 |      3 |    0 |            2 |           1 |  72515584 | 139.06250000 |
| order_line | __idx_order_line__PRIMARY | 7070001415647261697 |      4 |    0 |            1 |           1 |  46202880 |  88.12500000 |
| stock      | __idx_stock__PRIMARY      | 7070001415724580865 |      1 |    0 |            3 |           1 |  73629696 | 140.43750000 |
| stock      | __idx_stock__PRIMARY      | 7070001415724579841 |      2 |    0 |            4 |           1 | 107806720 | 221.62500000 |
| stock      | __idx_stock__PRIMARY      | 7070001415724578817 |      3 |    0 |            1 |           1 |  77561856 | 147.93750000 |
| stock      | __idx_stock__PRIMARY      | 7070001415724577793 |      4 |    0 |            2 |           1 |  99418112 | 195.65625000 |
| warehouse  | __idx_warehouse__PRIMARY  | 7070001415810488321 |      1 |    0 |            3 |           1 |     65536 |   0.12500000 |
| warehouse  | __idx_warehouse__PRIMARY  | 7070001415810487297 |      2 |    0 |            4 |           1 |     65536 |   0.12500000 |
| warehouse  | __idx_warehouse__PRIMARY  | 7070001415810486273 |      3 |    0 |            1 |           1 |     65536 |   0.12500000 |
| warehouse  | __idx_warehouse__PRIMARY  | 7070001415810485249 |      4 |    0 |            2 |           1 |     65536 |   0.12500000 |
+------------+---------------------------+---------------------+--------+------+--------------+-------------+-----------+--------------+

 

Xpand3 Server down

 

HammerDB로 부하를 주고 있는 상황에서 xpand3 System 종료 진행

 

-- maxscale 에서 확인

 xpand3 서버 다운 발생


 

-- xpand node에서 확인

다운 전

[xpand@xpand2 ~]$ clx space
nid | Hostname | Status |       Undo      |       Perm      |    Temp    |       Used      | DB Total | FS Free
----+----------+--------+-----------------+-----------------+------------+-----------------+----------+--------
  1 |   xpand1 |    OK  |  512.0K (0.00%) |  393.1M (2.58%) |  0 (0.00%) |  394.0M (2.59%) |    14.9G |    3.8G
  2 |   xpand2 |    OK  |  864.0K (0.01%) |  561.1M (3.68%) |  0 (0.00%) |  562.5M (3.69%) |    14.9G |    3.8G
  3 |   xpand3 |    OK  |  640.0K (0.00%) |  443.5M (2.91%) |  0 (0.00%) |  444.6M (2.92%) |    14.9G |    3.8G
  4 |   xpand4 |    OK  |  800.0K (0.01%) |  503.3M (3.30%) |  0 (0.00%) |  504.6M (3.31%) |    14.9G |    3.8G
----+----------+--------+-----------------+-----------------+------------+-----------------+----------+--------
                             2.8M (0.00%) |    1.9G (3.12%) |  0 (0.00%) |    1.9G (3.13%) |    59.5G |   15.2G

다운 후


[xpand@xpand2 ~]$ clx status
Cluster Name:    cl90c9aff68a607030
Cluster Version: 5.3.14
Cluster Status:   ATTN
Cluster Size:    4 nodes - 8 CPUs per Node
Current Node:    xpand2 - nid 2

nid | Hostname |  Status |   IP Address  | Zone | TPS  |       Used      | Total
----+----------+---------+---------------+------+------+-----------------+-------
  1 |   xpand1 |     OK  |  10.70.101.16 |    0 |  123 |  432.3M (2.84%) |  14.9G
  2 |   xpand2 |     OK  |  10.70.101.17 |    0 |   74 |  582.0M (3.82%) |  14.9G
  3 |   xpand3 |   DOWN  |  10.70.101.18 |    0 |    0 |       0 (0.00%) |      0
  4 |   xpand4 |     OK  |  10.70.101.19 |    0 |   51 |  546.5M (3.59%) |  14.9G
----+----------+---------+---------------+------+------+-----------------+-------
                                                   248 |    1.5G (3.42%) |  44.6G

[xpand@xpand2 ~]$ clx space
nid | Hostname |  Status |       Undo      |       Perm      |      Temp      |       Used      | DB Total | FS Free
----+----------+---------+-----------------+-----------------+----------------+-----------------+----------+--------
  1 |   xpand1 |     OK  |  448.0K (0.00%) |  438.4M (2.88%) |      0 (0.00%) |  439.3M (2.88%) |    14.9G |    3.8G
  2 |   xpand2 |     OK  |    4.2M (0.03%) |  647.7M (4.25%) |      0 (0.00%) |  652.4M (4.28%) |    14.9G |    3.8G
  3 |   xpand3 |   DOWN  |                 |                 |                |                 |          |       
  4 |   xpand4 |     OK  |  864.0K (0.01%) |  544.1M (3.57%) |  32.0K (0.00%) |  545.4M (3.58%) |    14.9G |    3.8G
----+----------+---------+-----------------+-----------------+----------------+-----------------+----------+--------
                              5.4M (0.01%) |    1.6G (3.57%) |  32.0K (0.00%) |    1.6G (3.58%) |    44.6G |   11.4G

xpand1 , xpand2 , xpand4의 사용량이 증가 하고 있음을 확인 할 수 있다.


-- 쿼리로 확인

mysql --user=root --password --no-auto-rehash

use system

MySQL [system]> select nodeid , hostname , iface_ip , mysql_port from nodeinfo ;

+--------+----------+--------------+------------+
| nodeid | hostname | iface_ip     | mysql_port |
+--------+----------+--------------+------------+
|      1 | xpand1   | 10.70.101.16 |       3306 |
|      2 | xpand2   | 10.70.101.17 |       3306 |
|      4 | xpand4   | 10.70.101.19 |       3306 |
+--------+----------+--------------+------------+

서버 종료 되고 초기에는 xpand3이 가지고 있던 데이터가 사라진 상태이기 때문에 replica가 없는 슬라이스 들이 존재 한다.
 
MySQL [system]> select * from table_replicas where `index` = 'c_w_id' ;
+----------+----------+--------+---------------------+---------------------+--------+------+--------+---------+
| Database | Table    | Index  | slice               | replica             | nodeid | rank | status | bytes   |
+----------+----------+--------+---------------------+---------------------+--------+------+--------+---------+
| tpcc     | customer | c_w_id | 7070001415015849985 | 7070001415015841793 |      4 |    0 |      1 | 9994240 |
| tpcc     | customer | c_w_id | 7070001415015849985 | 7070001415015842817 |      1 |    1 |      1 | 9994240 |
| tpcc     | customer | c_w_id | 7070001415015847937 | 7070001415015846913 |      1 |    0 |      1 | 8814592 |
| tpcc     | customer | c_w_id | 7070001415015847937 | 7070001415015845889 |      4 |    1 |      1 | 8945664 |
| tpcc     | customer | c_w_id | 7070001415015851009 | 7070001415015840769 |      2 |    1 |      1 | 5701632 |
| tpcc     | customer | c_w_id | 7070001415015848961 | 7070001415015844865 |      2 |    0 |      1 | 6225920 |
+----------+----------+--------+---------------------+---------------------+--------+------+--------+---------+

7070001415015849985   node4   node1
7070001415015847937   node1   node4
7070001415015851009                 node2
7070001415015848961   node2

위의 쿼리를 보면 슬라이스 7070001415015851009, 7070001415015848961 는 아직 replica가 없는 1개씩 만 존재 하는 슬라이스들이다.

조금 시간이 지난 후 다시 쿼리를 해 보면

MySQL [system]> select * from table_replicas where `index` = 'c_w_id' ;
+----------+----------+--------+---------------------+---------------------+--------+------+--------+---------+
| Database | Table    | Index  | slice               | replica             | nodeid | rank | status | bytes   |
+----------+----------+--------+---------------------+---------------------+--------+------+--------+---------+
| tpcc     | customer | c_w_id | 7070001415015849985 | 7070001415015841793 |      4 |    0 |      1 | 9994240 |
| tpcc     | customer | c_w_id | 7070001415015849985 | 7070001415015842817 |      1 |    1 |      1 | 9994240 |
| tpcc     | customer | c_w_id | 7070001415015847937 | 7070001415015846913 |      1 |    0 |      1 | 8814592 |
| tpcc     | customer | c_w_id | 7070001415015847937 | 7070001415015845889 |      4 |    1 |      1 | 8945664 |
| tpcc     | customer | c_w_id | 7070001415015851009 | 7070001415015840769 |      2 |    1 |      1 | 5701632 |
| tpcc     | customer | c_w_id | 7070001415015851009 | 7070045848110571524 |      4 |    2 |      1 | 2719744 |
| tpcc     | customer | c_w_id | 7070001415015848961 | 7070045791243413508 |      1 |    0 |      1 | 2719744 |
| tpcc     | customer | c_w_id | 7070001415015848961 | 7070001415015844865 |      2 |    1 |      1 | 6225920 |
+----------+----------+--------+---------------------+---------------------+--------+------+--------+---------+

7070001415015849985  node4  node1
7070001415015847937  node1  node4
7070001415015851009               node2  node4
7070001415015848961  node1  node2

MySQL [system]> select * from rebalancer_replicas where slice in ('7070001415015851009' , '7070001415015848961');
+---------------------+--------+-------+------+---------+
| slice               | nodeid | devid | rank | bytes   |
+---------------------+--------+-------+------+---------+
| 7070001415015851009 |      2 |     1 |    0 | 5701632 |
| 7070001415015851009 |      4 |     1 |    1 | 2719744 |
| 7070001415015848961 |      1 |     1 |    0 | 2719744 |
| 7070001415015848961 |      2 |     1 |    1 | 6225920 |
+---------------------+--------+-------+------+---------+

MySQL [system]> select *
    -> from   rebalancer_activity_log
    -> where  representation = 'c_w_id' 
    -> order by started desc  ;
+---------------------+------+----------------+----------+----------+----------------+---------+-----------------------+-----------------------+-------+
| id                  | op   | reason         | database | relation | representation | bytes   | started               | finished              | error |
+---------------------+------+----------------+----------+----------+----------------+---------+-----------------------+-----------------------+-------+
| 7070051227670267905 | move | load imbalance | tpcc     | customer | c_w_id         | 8814592 | 2022-03-01 08:49:38.0 | 2022-03-01 08:49:43.6 | NULL  |
| 7070045847554371586 | copy | reprotect      | tpcc     | customer | c_w_id         | 5701632 | 2022-03-01 08:28:45.3 | 2022-03-01 08:28:46.4 | NULL  |
| 7070045790321948674 | copy | reprotect      | tpcc     | customer | c_w_id         | 6225920 | 2022-03-01 08:28:32.0 | 2022-03-01 08:28:32.9 | NULL  |
+---------------------+------+----------------+----------+----------+----------------+---------+-----------------------+-----------------------+-------+

위의 결과 처럼 기존에 4개 슬라이스 8개 replica가 존재하던 것을 3개 서버로 나눠야 하니, 3개+2개로 서버가 데이터를 나눠 가지게 된다.

node1, node4는 3개의 슬라이스, node2는 2개의 슬라이스 씩 데이터를 나눠 가짐

현재 더 진행 중인 작업이 있는지 확인


MySQL [system]> select *
    -> from   rebalancer_activity_log
    -> where  started > date_add(now() , interval -5 minute)
    -> order by started desc ;
+---------------------+------+----------------+----------+----------+----------------------+----------+-----------------------+-----------------------+-------+
| id                  | op   | reason         | database | relation | representation       | bytes    | started               | finished              | error |
+---------------------+------+----------------+----------+----------+----------------------+----------+-----------------------+-----------------------+-------+
| 7070054448998790145 | move | load imbalance | tpcc     | stock    | __idx_stock__PRIMARY | 76513280 | 2022-03-01 09:02:08.0 | NULL                  | NULL  |
| 7070054320144704513 | move | load imbalance | tpcc     | stock    | __idx_stock__PRIMARY | 76283904 | 2022-03-01 09:01:38.0 | 2022-03-01 09:01:45.6 | NULL  |
| 7070054191288836097 | move | load imbalance | tpcc     | stock    | __idx_stock__PRIMARY | 75759616 | 2022-03-01 09:01:08.0 | 2022-03-01 09:01:17.2 | NULL  |
| 7070054062442878977 | move | load imbalance | tpcc     | stock    | __idx_stock__PRIMARY | 75431936 | 2022-03-01 09:00:38.0 | 2022-03-01 09:00:44.4 | NULL  |
| 7070053933584769025 | move | load imbalance | tpcc     | stock    | __idx_stock__PRIMARY | 74907648 | 2022-03-01 09:00:08.0 | 2022-03-01 09:00:15.9 | NULL  |
| 7070053804737675265 | move | load imbalance | tpcc     | stock    | __idx_stock__PRIMARY | 74579968 | 2022-03-01 08:59:38.0 | 2022-03-01 08:59:44.9 | NULL  |
| 7070053675881125889 | move | load imbalance | tpcc     | stock    | __idx_stock__PRIMARY | 74317824 | 2022-03-01 08:59:08.0 | 2022-03-01 08:59:15.4 | NULL  |
| 7070053547022120961 | move | load imbalance | tpcc     | stock    | __idx_stock__PRIMARY | 73990144 | 2022-03-01 08:58:38.0 | 2022-03-01 08:58:45.3 | NULL  |
+---------------------+------+----------------+----------+----------+----------------------+----------+-----------------------+-----------------------+-------+

아직도 계속 진행 중인 것을 확인 할 수 있다.

[xpand@xpand2 ~]$ clx space

nid | Hostname |  Status |       Undo      |       Perm      |    Temp    |       Used      | DB Total | FS Free
----+----------+---------+-----------------+-----------------+------------+-----------------+----------+--------
  1 |   xpand1 |     OK  |  576.0K (0.00%) |  691.3M (4.54%) |  0 (0.00%) |  692.3M (4.55%) |    14.9G |    3.8G
  2 |   xpand2 |     OK  |  800.0K (0.01%) |  696.3M (4.57%) |  0 (0.00%) |  697.6M (4.58%) |    14.9G |    3.8G
  3 |   xpand3 |   DOWN  |                 |                 |            |                 |          |       
  4 |   xpand4 |     OK  |  672.0K (0.00%) |  696.7M (4.57%) |  0 (0.00%) |  697.8M (4.58%) |    14.9G |    3.8G
----+----------+---------+-----------------+-----------------+------------+-----------------+----------+--------
                              2.0M (0.00%) |    2.0G (4.56%) |  0 (0.00%) |    2.0G (4.57%) |    44.6G |   11.4G

 

Xpand3 Server 기동

 

HammerDB는 아직도 계속해서 부하 주는 중 ...

 

-- xpand3 에서 확인

[xpand@xpand3 ~]$ clx status
Cluster Name:    cl90c9aff68a607030
Cluster Version: 5.3.14
Cluster Status:   OK
Cluster Size:    4 nodes - 8 CPUs per Node
Current Node:    xpand3 - nid 3

nid | Hostname | Status |   IP Address  | TPS |       Used      | Total
----+----------+--------+---------------+-----+-----------------+-------
  1 |   xpand1 |    OK  |  10.70.101.16 |   0 |  521.8M (3.43%) |  14.9G
  2 |   xpand2 |    OK  |  10.70.101.17 |   0 |  631.3M (4.14%) |  14.9G
  3 |   xpand3 |    OK  |  10.70.101.18 |   0 |  454.3M (2.98%) |  14.9G
  4 |   xpand4 |    OK  |  10.70.101.19 |   0 |  681.2M (4.47%) |  14.9G
----+----------+--------+---------------+-----+-----------------+-------
                                            0 |    2.2G (3.76%) |  59.5G

mysql --user=root --password --no-auto-rehash
use system

MySQL [system]> select nodeid , hostname , iface_ip , mysql_port from nodeinfo ;
+--------+----------+--------------+------------+
| nodeid | hostname | iface_ip     | mysql_port |
+--------+----------+--------------+------------+
|      3 | xpand3   | 10.70.101.18 |       3306 |
|      1 | xpand1   | 10.70.101.16 |       3306 |
|      4 | xpand4   | 10.70.101.19 |       3306 |
|      2 | xpand2   | 10.70.101.17 |       3306 |
+--------+----------+--------------+------------+

 

-- maxscale에서 확인



 

-- 리발란서 체크

MySQL [system]> select *
    -> from   rebalancer_activity_log
    -> where  started > date_add(now() , interval -5 minute)
    -> order by started desc ;
+---------------------+--------+-----------------------------+----------------+-----------------+--------------------------------+----------+-----------------------+-----------------------+-------------------+
| id                  | op     | reason                      | database       | relation        | representation                 | bytes    | started               | finished              | error             |
+---------------------+--------+-----------------------------+----------------+-----------------+--------------------------------+----------+-----------------------+-----------------------+-------------------+
| 7070057224240590852 | move   | load imbalance              | clustrix_statd | hotness_history | __idx_hotness_history__PRIMARY |   688128 | 2022-03-01 09:12:54.2 | 2022-03-01 09:12:59.4 | NULL              |
| 7070057201853034500 | move   | load imbalance              | clustrix_statd | hotness_history | timestamp_2                    |   557056 | 2022-03-01 09:12:49.0 | 2022-03-01 09:12:54.2 | NULL              |
| 7070057178865233922 | move   | distribution load imbalance | clustrix_statd | statd_history   | __idx_statd_history__PRIMARY   |  1310720 | 2022-03-01 09:12:43.6 | 2022-03-01 09:12:48.9 | NULL              |
| 7070057178855275521 | move   | distribution load imbalance | tpcc           | new_order       | __idx_new_order__PRIMARY       |  2785280 | 2022-03-01 09:12:43.6 | 2022-03-01 09:12:48.9 | NULL              |
| 7070057123086937089 | move   | load imbalance              | tpcc           | new_order       | __idx_new_order__PRIMARY       |  2785280 | 2022-03-01 09:12:30.6 | 2022-03-01 09:12:36.0 | NULL              |
| 7070057098480262145 | move   | load imbalance              | clustrix_statd | qpc_history     | __idx_qpc_history__PRIMARY     |  4161536 | 2022-03-01 09:12:24.9 | 2022-03-01 09:12:30.6 | NULL              |
| 7070057076009735172 | move   | distribution load imbalance | tpcc           | new_order       | __idx_new_order__PRIMARY       |  1015808 | 2022-03-01 09:12:19.7 | 2022-03-01 09:12:24.9 | NULL              |
| 7070057050019202052 | move   | distribution load imbalance | clustrix_statd | qpc_history     | __idx_qpc_history__PRIMARY     |  4227072 | 2022-03-01 09:12:13.6 | 2022-03-01 09:12:19.6 | NULL              |
| 7070056944761846787 | move   | load imbalance              | tpcc           | item            | __idx_item__PRIMARY            |  1966080 | 2022-03-01 09:11:49.1 | 2022-03-01 09:11:49.1 | No source replica |
| 7070056921902192643 | move   | distribution load imbalance | tpcc           | item            | __idx_item__PRIMARY            |  1966080 | 2022-03-01 09:11:43.8 | 2022-03-01 09:11:49.1 | NULL              |
| 7070056921474142209 | move   | distribution load imbalance | tpcc           | item            | __idx_item__PRIMARY            |  1998848 | 2022-03-01 09:11:43.7 | 2022-03-01 09:11:49.0 | NULL              |
| 7070056921366020098 | rerank | read imbalance              | tpcc           | history         | __base_history                 |  9732096 | 2022-03-01 09:11:43.7 | 2022-03-01 09:11:43.7 | NULL              |
| 7070056921645957121 | rerank | read imbalance              | tpcc           | orders          | o_w_id                         |  8159232 | 2022-03-01 09:11:43.7 | 2022-03-01 09:11:43.8 | NULL              |
| 7070056921130296324 | rerank | distribution read imbalance | tpcc           | customer        | __idx_customer__PRIMARY        | 54919168 | 2022-03-01 09:11:43.6 | 2022-03-01 09:11:43.7 | NULL              |
| 7070056921130888193 | rerank | read imbalance              | tpcc           | orders          | __idx_orders__PRIMARY          | 10485760 | 2022-03-01 09:11:43.6 | 2022-03-01 09:11:43.7 | NULL              |
| 7070056792276204547 | move   | load imbalance              | clustrix_statd | statd_history   | timestamp                      |   983040 | 2022-03-01 09:11:13.6 | 2022-03-01 09:11:19.0 | NULL              |
| 7070056792301891588 | move   | distribution load imbalance | tpcc           | item            | __idx_item__PRIMARY            |  1966080 | 2022-03-01 09:11:13.6 | 2022-03-01 09:11:19.1 | NULL              |
| 7070056688169977858 | move   | distribution load imbalance | clustrix_statd | statd_history   | timestamp                      |  1179648 | 2022-03-01 09:10:49.4 | 2022-03-01 09:10:54.6 | NULL              |
| 7070056663427659780 | move   | distribution load imbalance | tpcc           | customer        | c_w_id                         |  9994240 | 2022-03-01 09:10:43.6 | 2022-03-01 09:10:49.4 | NULL              |
| 7070056663446284289 | move   | load imbalance              | clustrix_statd | statd_history   | __idx_statd_history__PRIMARY   |  2719744 | 2022-03-01 09:10:43.6 | 2022-03-01 09:10:49.1 | NULL              |
| 7070056559896604674 | move   | distribution load imbalance | clustrix_statd | statd_history   | __idx_statd_history__PRIMARY   |  2686976 | 2022-03-01 09:10:19.5 | 2022-03-01 09:10:25.0 | NULL              |
| 7070056534583032836 | move   | distribution load imbalance | tpcc           | orders          | o_w_id                         |  6619136 | 2022-03-01 09:10:13.6 | 2022-03-01 09:10:19.5 | NULL              |
| 7070056491813785604 | rerank | read imbalance              | tpcc           | customer        | __idx_customer__PRIMARY        | 39878656 | 2022-03-01 09:10:03.7 | 2022-03-01 09:10:03.7 | NULL              |
| 7070056491614640129 | rerank | distribution read imbalance | tpcc           | customer        | __idx_customer__PRIMARY        | 39616512 | 2022-03-01 09:10:03.6 | 2022-03-01 09:10:03.6 | NULL              |
| 7070056455896086530 | move   | load imbalance              | tpcc           | customer        | c_w_id                         |  4325376 | 2022-03-01 09:09:55.3 | 2022-03-01 09:10:01.3 | NULL              |
| 7070056431216841732 | move   | load imbalance              | tpcc           | orders          | o_w_id                         |  6881280 | 2022-03-01 09:09:49.5 | 2022-03-01 09:09:55.3 | NULL              |
| 7070056431216709635 | move   | distribution load imbalance | tpcc           | orders          | __idx_orders__PRIMARY          |  5013504 | 2022-03-01 09:09:49.5 | 2022-03-01 09:09:55.4 | NULL              |
| 7070056405749147652 | move   | distribution load imbalance | tpcc           | orders          | __idx_orders__PRIMARY          |  8912896 | 2022-03-01 09:09:43.6 | 2022-03-01 09:09:49.5 | NULL              |
| 7070056302358391809 | move   | load imbalance              | tpcc           | history         | __base_history                 |  7012352 | 2022-03-01 09:09:19.5 | 2022-03-01 09:09:19.6 | No source replica |
| 7070056276881671171 | move   | distribution load imbalance | tpcc           | customer        | __idx_customer__PRIMARY        | 51937280 | 2022-03-01 09:09:13.6 | 2022-03-01 09:09:20.5 | NULL              |
| 7070056276897830913 | move   | distribution load imbalance | tpcc           | history         | __base_history                 |  7012352 | 2022-03-01 09:09:13.6 | 2022-03-01 09:09:19.5 | NULL              |
| 7070056239674573826 | move   | load imbalance              | tpcc           | orders          | __idx_orders__PRIMARY          | 13959168 | 2022-03-01 09:09:05.0 | 2022-03-01 09:09:10.9 | NULL              |
| 7070056211155335172 | move   | load imbalance              | tpcc           | customer        | __idx_customer__PRIMARY        | 54919168 | 2022-03-01 09:08:58.3 | 2022-03-01 09:09:04.9 | NULL              |
| 7070056180744865796 | move   | distribution load imbalance | tpcc           | history         | __base_history                 |  9961472 | 2022-03-01 09:08:51.2 | 2022-03-01 09:08:58.3 | NULL              |
| 7070056148016963586 | move   | distribution load imbalance | tpcc           | customer        | __idx_customer__PRIMARY        | 42303488 | 2022-03-01 09:08:43.6 | 2022-03-01 09:08:51.2 | NULL              |
| 7070056064602365955 | move   | load imbalance              | tpcc           | customer        | __idx_customer__PRIMARY        | 51937280 | 2022-03-01 09:08:24.2 | 2022-03-01 09:08:24.2 | No source replica |
| 7070056062335256580 | rerank | read imbalance              | tpcc           | customer        | __idx_customer__PRIMARY        | 54919168 | 2022-03-01 09:08:23.7 | 2022-03-01 09:08:23.7 | NULL              |
| 7070056062109624322 | rerank | distribution read imbalance | tpcc           | customer        | __idx_customer__PRIMARY        | 64159744 | 2022-03-01 09:08:23.6 | 2022-03-01 09:08:23.6 | NULL              |
| 7070056035998943236 | move   | distribution load imbalance | tpcc           | customer        | __idx_customer__PRIMARY        | 59604992 | 2022-03-01 09:08:17.5 | 2022-03-01 09:08:24.2 | NULL              |
| 7070056019161892867 | move   | distribution load imbalance | tpcc           | stock           | __idx_stock__PRIMARY           | 97943552 | 2022-03-01 09:08:13.6 | 2022-03-01 09:08:22.8 | NULL              |
+---------------------+--------+-----------------------------+----------------+-----------------+--------------------------------+----------+-----------------------+-----------------------+-------------------+

바쁘게 일을 하네 ..

아까 확인해 봤던 c_w_id 인덱스를 다시 한번 확인해 보자

MySQL [system]> select * from table_replicas where `index` = 'c_w_id' ;
+----------+----------+--------+---------------------+---------------------+--------+------+--------+---------+
| Database | Table    | Index  | slice               | replica             | nodeid | rank | status | bytes   |
+----------+----------+--------+---------------------+---------------------+--------+------+--------+---------+
| tpcc     | customer | c_w_id | 7070001415015849985 | 7070056663513872387 |      3 |    0 |      1 | 4063232 |
| tpcc     | customer | c_w_id | 7070001415015849985 | 7070001415015842817 |      1 |    2 |      1 |   98304 |
| tpcc     | customer | c_w_id | 7070001415015847937 | 7070056456244686851 |      3 |    0 |      1 | 4063232 |
| tpcc     | customer | c_w_id | 7070001415015847937 | 7070001415015845889 |      4 |    3 |      1 | 8945664 |
| tpcc     | customer | c_w_id | 7070001415015851009 | 7070001415015840769 |      2 |    1 |      1 | 5701632 |
| tpcc     | customer | c_w_id | 7070001415015851009 | 7070045848110571524 |      4 |    2 |      1 | 2719744 |
| tpcc     | customer | c_w_id | 7070001415015848961 | 7070045791243413508 |      1 |    0 |      1 | 2719744 |
| tpcc     | customer | c_w_id | 7070001415015848961 | 7070001415015844865 |      2 |    1 |      1 | 6225920 |
+----------+----------+--------+---------------------+---------------------+--------+------+--------+---------+

7070001415015849985  node3                 node1
7070001415015847937  node3                                 node4
7070001415015851009                 node2  node4
7070001415015848961  node1  node2

이상하다... node3에 메인 슬라이스가 2개나 위치하고 있다 .....
조금 시간을 두고 다시 쿼리

MySQL [system]> select * from table_replicas where `index` = 'c_w_id' ;
+----------+----------+--------+---------------------+---------------------+--------+------+--------+---------+
| Database | Table    | Index  | slice               | replica             | nodeid | rank | status | bytes   |
+----------+----------+--------+---------------------+---------------------+--------+------+--------+---------+
| tpcc     | customer | c_w_id | 7070001415015849985 | 7070056663513872387 |      3 |    0 |      1 | 4063232 |
| tpcc     | customer | c_w_id | 7070001415015849985 | 7070001415015842817 |      1 |    2 |      1 |   98304 |
| tpcc     | customer | c_w_id | 7070001415015847937 | 7070001415015845889 |      4 |    0 |      1 | 8945664 |
| tpcc     | customer | c_w_id | 7070001415015847937 | 7070056456244686851 |      3 |    3 |      1 | 4063232 |
| tpcc     | customer | c_w_id | 7070001415015851009 | 7070045848110571524 |      4 |    0 |      1 | 2719744 |
| tpcc     | customer | c_w_id | 7070001415015851009 | 7070001415015840769 |      2 |    1 |      1 | 5701632 |
| tpcc     | customer | c_w_id | 7070001415015848961 | 7070045791243413508 |      1 |    0 |      1 | 2719744 |
| tpcc     | customer | c_w_id | 7070001415015848961 | 7070001415015844865 |      2 |    1 |      1 | 6225920 |
+----------+----------+--------+---------------------+---------------------+--------+------+--------+---------+

7070001415015849985  node3                node1
7070001415015847937  node4                             node3
7070001415015851009               node4   node2
7070001415015848961  node1  node2

이번에는  node4에 메인 슬라이스가 2개 위치 ...
조금 시간을 두고 다시 쿼리

MySQL [system]> select * from table_replicas where `index` = 'c_w_id' ;
+----------+----------+--------+---------------------+---------------------+--------+------+--------+---------+
| Database | Table    | Index  | slice               | replica             | nodeid | rank | status | bytes   |
+----------+----------+--------+---------------------+---------------------+--------+------+--------+---------+
| tpcc     | customer | c_w_id | 7070001415015849985 | 7070056663513872387 |      3 |    0 |      1 | 4063232 |
| tpcc     | customer | c_w_id | 7070001415015849985 | 7070001415015842817 |      1 |    2 |      1 |   98304 |
| tpcc     | customer | c_w_id | 7070001415015847937 | 7070001415015845889 |      4 |    0 |      1 | 8945664 |
| tpcc     | customer | c_w_id | 7070001415015847937 | 7070056456244686851 |      3 |    3 |      1 | 4063232 |
| tpcc     | customer | c_w_id | 7070001415015851009 | 7070001415015840769 |      2 |    0 |      1 | 5701632 |
| tpcc     | customer | c_w_id | 7070001415015851009 | 7070045848110571524 |      4 |    1 |      1 | 2719744 |
| tpcc     | customer | c_w_id | 7070001415015848961 | 7070045791243413508 |      1 |    0 |      1 | 2719744 |
| tpcc     | customer | c_w_id | 7070001415015848961 | 7070001415015844865 |      2 |    1 |      1 | 6225920 |
+----------+----------+--------+---------------------+---------------------+--------+------+--------+---------+

7070001415015849985  node3              node1
7070001415015847937  node4                          node3
7070001415015851009  node2  node4
7070001415015848961  node1  node2

각각의 노드 마다 메인 슬라이스가 1개씩만 위치하는 것을 확인 할 수 있다.

-- 리발란서 동작 확인

MySQL [system]> select * from rebalancer_activity_targets ;

Empty set (0.00 sec)

MySQL [system]> select * from rebalancer_copy_activity ;
Empty set (0.01 sec)


-- 상태 확인

[xpand@xpand3 ~]$ clx space

nid | Hostname | Status |       Undo      |       Perm      |    Temp    |       Used      | DB Total | FS Free
----+----------+--------+-----------------+-----------------+------------+-----------------+----------+--------
  1 |   xpand1 |    OK  |  256.0K (0.00%) |  457.6M (3.00%) |  0 (0.00%) |  458.2M (3.01%) |    14.9G |    3.8G
  2 |   xpand2 |    OK  |  256.0K (0.00%) |  541.6M (3.56%) |  0 (0.00%) |  542.4M (3.56%) |    14.9G |    3.8G
  3 |   xpand3 |    OK  |  256.0K (0.00%) |  454.4M (2.98%) |  0 (0.00%) |  454.9M (2.99%) |    14.9G |    3.8G
  4 |   xpand4 |    OK  |  256.0K (0.00%) |  450.7M (2.96%) |  0 (0.00%) |  451.4M (2.96%) |    14.9G |    3.8G
----+----------+--------+-----------------+-----------------+------------+-----------------+----------+--------
                             1.0M (0.00%) |    1.9G (3.13%) |  0 (0.00%) |    1.9G (3.13%) |    59.5G |   15.2G
반응형

+ Recent posts