日々の覚書: 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 件のコメント :
コメントを投稿