GA

2025/04/16

HeatWave MySQLがどれくらいVanilla MySQL GPL版と同じくらいか考える旅 / 読み取りレプリカ編その2

日々の覚書: HeatWave MySQLがどれくらいVanilla MySQL GPL版と同じくらいか考える旅 / 読み取りレプリカ編 の続き。

読み取りレプリカを1つ足してみた。ついでなので “MySQL.8.HA” にしてみたんだけれどこれは単に無視されるのかしら…(クラスタトータルのECPUも8ECPUしか増えなかったので、たぶんこれは関係なく1台だけ足されるんだと思う)

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier |           |             |        NULL | OFFLINE      |             |                |                            |
+---------------------------+-----------+-------------+-------------+--------------+-------------+----------------+----------------------------+
1 row in set (0.00 sec)

前回気が付かなかったけど、読み取りレプリカは bind_address の設定が増えてた。ロードバランサ側のネームスペースのIPアドレスはWEB画面から見られるIPのいずれでもない。

+---------------+-------------------------------------------+
| Variable_name | Value                                     |
+---------------+-------------------------------------------+
| bind_address  | 10.0.0.195/mysql,10.5.195.88/loadbalancer |
+---------------+-------------------------------------------+
1 row in set (0.01 sec)

ロードバランサ側のIPはちゃんと分散するようになった。単純なラウンドロビンじゃないかしら。

$ time for n in {1..100} ; do mysql -h 10.0.0.118 -uadmin -sse "SELECT @@hostname" ;  done | sort | uniq -c
     58 eiewhuzpjiihavkp
     42 fuis3dpz4ueksuzu

real    0m2.259s
user    0m1.027s
sys     0m0.496s

1台クラッシュさせてみると、少なくとも10秒程度でロードバランサから外している気配はなくて、再起動してくるのに合わせてロードバランサに戻っている。これSQL_Threadだけ止めたりしたらどうなるんだろう。

$ while true ; do for n in {1..100} ; do mysql -h 10.0.0.118 -uadmin -sse "SELECT @@hostname" 2> >(sort -u);  done | sort | u
niq -c ; date; sleep 1; done
     50 eiewhuzpjiihavkp
     50 fuis3dpz4ueksuzu
Wed Apr 16 04:59:49 GMT 2025
     56 eiewhuzpjiihavkp
     44 fuis3dpz4ueksuzu
Wed Apr 16 04:59:52 GMT 2025
     50 eiewhuzpjiihavkp
     50 fuis3dpz4ueksuzu
Wed Apr 16 04:59:55 GMT 2025
     51 eiewhuzpjiihavkp
     49 fuis3dpz4ueksuzu
Wed Apr 16 04:59:58 GMT 2025
     43 eiewhuzpjiihavkp
     57 fuis3dpz4ueksuzu
Wed Apr 16 05:00:02 GMT 2025
      6 ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.118:3306' (111)
      1 ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 104
     42 eiewhuzpjiihavkp
     51 fuis3dpz4ueksuzu
Wed Apr 16 05:00:05 GMT 2025
     45 ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.118:3306' (111)
     55 fuis3dpz4ueksuzu
Wed Apr 16 05:00:08 GMT 2025
     47 ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.118:3306' (111)
     53 fuis3dpz4ueksuzu
Wed Apr 16 05:00:10 GMT 2025
     52 ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.118:3306' (111)
     48 fuis3dpz4ueksuzu
Wed Apr 16 05:00:13 GMT 2025
     50 ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.118:3306' (111)
     50 fuis3dpz4ueksuzu
Wed Apr 16 05:00:16 GMT 2025
     54 eiewhuzpjiihavkp
     46 fuis3dpz4ueksuzu
Wed Apr 16 05:00:19 GMT 2025

↓エラーログ的にはこのへん。

mysql> SELECT * FROM performance_schema.error_log WHERE error_code <> 'MY-011071'\G

..
*************************** 386. row ***************************
    LOGGED: 2025-04-16 05:00:05.999056
 THREAD_ID: 0
      PRIO: Note
ERROR_CODE: MY-013930
 SUBSYSTEM: Server
      DATA: systemd notify: STATUS=Server startup in progress
*************************** 387. row ***************************
    LOGGED: 2025-04-16 05:00:05.999924
 THREAD_ID: 0
      PRIO: System
ERROR_CODE: MY-015015
 SUBSYSTEM: Server
      DATA: MySQL Server - start.

..
*************************** 604. row ***************************
    LOGGED: 2025-04-16 05:00:16.789157
 THREAD_ID: 0
      PRIO: System
ERROR_CODE: MY-010931
 SUBSYSTEM: Server
      DATA: /usr/sbin/mysqld: ready for connections. Version: '8.4.4-u5-cloud'  socket: '/var/run/mysqld/mysql.sock'  port: 3306  MySQL Enterprise - Cloud.
*************************** 605. row ***************************
    LOGGED: 2025-04-16 05:00:16.789172
 THREAD_ID: 0
      PRIO: System
ERROR_CODE: MY-013292
 SUBSYSTEM: Server
      DATA: Admin interface ready for connections, address: '127.0.0.1'  port: 7306
*************************** 606. row ***************************
    LOGGED: 2025-04-16 05:00:16.789529
 THREAD_ID: 0
      PRIO: Note
ERROR_CODE: MY-013930
 SUBSYSTEM: Server
      DATA: systemd notify: READY=1
STATUS=Server is operational
MAIN_PID=144141

..

エラーログで思い出したついでに読み取りレプリカを追加した時のエラーログ。

*************************** 1408. row ***************************
    LOGGED: 2025-04-16 05:31:42.525733
 THREAD_ID: 0
      PRIO: Note
ERROR_CODE: MY-013930
 SUBSYSTEM: Server
      DATA: systemd notify: READY=1
STATUS=Server is operational
MAIN_PID=35573

*************************** 1409. row ***************************
    LOGGED: 2025-04-16 05:34:50.412149
 THREAD_ID: 44
      PRIO: System
ERROR_CODE: MY-010597
 SUBSYSTEM: Repl
      DATA: 'CHANGE REPLICATION SOURCE TO FOR CHANNEL 'oci_managed_read_replica' executed'. Previous state source_host='', source_port= 3306, source_log_file='', source_log_pos= 4, source_bind=''. New state source_host='10.0.114.60', source_port= 7306, source_log_file='', source_log_pos= 4, source_bind=''.
*************************** 1410. row ***************************
    LOGGED: 2025-04-16 05:34:50.624034
 THREAD_ID: 47
      PRIO: Note
ERROR_CODE: MY-010581
 SUBSYSTEM: Repl
      DATA: Replica SQL thread for channel 'oci_managed_read_replica' initialized, starting replication in log 'FIRST' at position 0, relay log '/db/replication/relay-log-oci_managed_read_replica.000001' position: 4
*************************** 1411. row ***************************
    LOGGED: 2025-04-16 05:34:50.625468
 THREAD_ID: 46
      PRIO: System
ERROR_CODE: MY-014002
 SUBSYSTEM: Repl
      DATA: Replica receiver thread for channel 'oci_managed_read_replica': connected to source 'ocirpl@10.0.114.60:7306' with server_uuid=07c65c46-18f9-11f0-89e5-020017067068, server_id=-1253503313. Starting GTID-based replication.

0 件のコメント :

コメントを投稿