728x90
반응형
노드 장애 테스트
현재 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 |
반응형