I’ve tried out Galera on my workstation with a few VMs on VirtualBox and it has worked okey now that I overwrite the wsrep_sst_rsync to keep lsof from running and by extension using 100% of CPU capacity and not completing. I tried the latest version of MariaDB 11.0.3 to see if that removed the need to skip lsof but no, the behavior is still there. I tried Bitnami’s compilation of MariaDB but the docker but there were more than the acceptable level of error/warning messages when I started it up.
Below is an example of me turning off galera03, galera02 and galera01 in sequence. galera03 does an IST synchronization whereas galera02 and galera01 removed the local directory for MariaDB to force SST with rsync. In reality my MariaDB storage is more like 9GB and not 600MB like this toy setup but I think it will work.
The bottom three graph indicate that status of MySQL on the three VMs and you can see from the top graph that writes are made uninterrupted. The second graph indicates that number of bytes sent from each node. You only see a difference when galera03 is down and galera02 takes over thanks to keepalived.
I’m going try to let Zabbix use ProxySQL next because as it stands I rely on Keepalived to move an IP around as mariadbgalera does up and down. Then my plan is to set up new backend nodes – now with MariaDB Galera – and no virtual IP. The new Galera cluster will replicate from the current MariaDB master and then I will do a switchover. Maybe I should keep keepalived and the VIP so Grafana can access it easily. (thinking)
Well anyway, I’ll include the log of galera02 doing SST for general edification.
Oct 21 14:23:26 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:26 0 [Note] WSREP: Joiner monitor thread started to monitor
Oct 21 14:23:26 galera02.incandescent.tech docker[1565]: WSREP_SST: [INFO] rsync SST started on joiner (20231021 12:23:26.298)
Oct 21 14:23:26 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:26 1 [Note] WSREP: ####### IST uuid:00000000-0000-0000-0000-000000000000 f: 0, l: 113995, STRv: 3
Oct 21 14:23:26 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:26 1 [Note] WSREP: IST receiver addr using tcp://192.168.2.162:4568
Oct 21 14:23:26 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:26 1 [Note] WSREP: IST receiver bind using tcp://0.0.0.0:4568
Oct 21 14:23:26 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:26 1 [Note] WSREP: Prepared IST receiver for 0-113995, listening at: tcp://0.0.0.0:4568
Oct 21 14:23:26 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:26 0 [Note] WSREP: Member 2.0 (11a0c1b70499) requested state transfer from 'any'. Selected 0.0 (e541a05fe7a3)(SYNCED) as donor.
Oct 21 14:23:26 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:26 0 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 113996)
Oct 21 14:23:26 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:26 1 [Note] WSREP: Requesting state transfer: success, donor: 0
Oct 21 14:23:26 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:26 1 [Note] WSREP: Resetting GCache seqno map due to different histories.
Oct 21 14:23:26 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:26 1 [Note] WSREP: GCache history reset: 00000000-0000-0000-0000-000000000000:0 -> 16442fed-6f68-11ee-ae8d-9b68eafda4eb:113995
Oct 21 14:23:28 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:28 0 [Note] WSREP: (a1d6cfa1-90b2, 'tcp://0.0.0.0:4567') turning message relay requesting off
Oct 21 14:23:47 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:47 0 [Note] WSREP: 0.0 (e541a05fe7a3): State transfer to 2.0 (11a0c1b70499) complete.
Oct 21 14:23:47 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:47 0 [Note] WSREP: Member 0.0 (e541a05fe7a3) synced with group.
Oct 21 14:23:47 galera02.incandescent.tech docker[1565]: WSREP_SST: [INFO] Extracting binlog files: (20231021 12:23:47.818)
Oct 21 14:23:47 galera02.incandescent.tech docker[1565]: galera03-bin.000004
Oct 21 14:23:47 galera02.incandescent.tech docker[1565]: WSREP_SST: [INFO] Galera co-ords from recovery: 16442fed-6f68-11ee-ae8d-9b68eafda4eb:113997 0 (20231021 12:23:47.848)
Oct 21 14:23:47 galera02.incandescent.tech docker[1565]: WSREP_SST: [INFO] rsync SST completed on joiner (20231021 12:23:47.850)
Oct 21 14:23:47 galera02.incandescent.tech docker[1565]: WSREP_SST: [INFO] Joiner cleanup: rsync PID=255, stunnel PID=0 (20231021 12:23:47.852)
Oct 21 14:23:48 galera02.incandescent.tech docker[1565]: WSREP_SST: [INFO] Joiner cleanup done. (20231021 12:23:48.360)
Oct 21 14:23:48 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:48 3 [Note] WSREP: SST received
Oct 21 14:23:48 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:48 3 [Note] WSREP: Server status change joiner -> initializing
Oct 21 14:23:48 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:48 3 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
Oct 21 14:23:48 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:48 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
Oct 21 14:23:48 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:48 0 [Note] InnoDB: Number of transaction pools: 1
Oct 21 14:23:48 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:48 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
Oct 21 14:23:48 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:48 0 [Note] InnoDB: Initializing buffer pool, total size = 1.000GiB, chunk size = 16.000MiB
Oct 21 14:23:48 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:48 0 [Note] InnoDB: Completed initialization of buffer pool
Oct 21 14:23:48 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:48 0 [Note] InnoDB: File system buffers for log disabled (block size=512 bytes)
Oct 21 14:23:48 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:48 0 [Note] InnoDB: End of log at LSN=306743910
Oct 21 14:23:49 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:49 0 [Note] InnoDB: Opened 3 undo tablespaces
Oct 21 14:23:49 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:49 0 [Note] InnoDB: 128 rollback segments in 3 undo tablespaces are active.
Oct 21 14:23:49 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:49 0 [Note] InnoDB: Setting file './ibtmp1' size to 12.000MiB. Physically writing the file full; Please wait …
Oct 21 14:23:49 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:49 0 [Note] InnoDB: File './ibtmp1' size is now 12.000MiB.
Oct 21 14:23:49 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:49 0 [Note] InnoDB: log sequence number 306743910; transaction id 305297
Oct 21 14:23:49 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:49 0 [Note] Plugin 'FEEDBACK' is disabled.
Oct 21 14:23:49 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:49 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
Oct 21 14:23:49 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:49 0 [Note] Recovering after a crash using galera02-bin
Oct 21 14:23:49 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:49 0 [Note] Starting table crash recovery…
Oct 21 14:23:49 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:49 0 [Note] Crash table recovery finished.
Oct 21 14:23:49 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:49 0 [Note] InnoDB: Buffer pool(s) load completed at 231021 12:23:49
Oct 21 14:23:49 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:49 0 [Note] Server socket created on IP: '0.0.0.0'.
Oct 21 14:23:49 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:49 0 [Note] WSREP: wsrep_init_schema_and_SR (nil)
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 0 [Note] WSREP: Server initialized
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 0 [Note] WSREP: Server status change initializing -> initialized
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 0 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 3 [Note] WSREP: Recovered position from storage: 16442fed-6f68-11ee-ae8d-9b68eafda4eb:113997
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 3 [Note] WSREP: Server status change initialized -> joined
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 3 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 3 [Note] WSREP: Recovered view from SST:
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: id: 16442fed-6f68-11ee-ae8d-9b68eafda4eb:113995
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: status: primary
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: protocol_version: 4
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: capabilities: MULTI-MASTER, CERTIFICATION, PARALLEL_APPLYING, REPLAY, ISOLATION, PAUSE, CAUSAL_READ, INCREMENTAL_WS, UNORDERED, PREORDERED, STREAMING, NBO
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: final: no
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: own_index: 2
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: members(3):
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 0: 27fcd22a-7009-11ee-9a2b-5647f27ea5ae, e541a05fe7a3
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 1: 860d53c3-6f83-11ee-88b1-1309baa24770, 780dc59b17db
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2: a1d6cfa1-700c-11ee-90b2-176fa6c70192, 11a0c1b70499
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 3 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 6 [Note] WSREP: Recovered cluster id 16442fed-6f68-11ee-ae8d-9b68eafda4eb
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 3 [Note] WSREP: SST received: 16442fed-6f68-11ee-ae8d-9b68eafda4eb:113997
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 3 [Note] WSREP: SST succeeded for position 16442fed-6f68-11ee-ae8d-9b68eafda4eb:113997
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 0 [Note] WSREP: Joiner monitor thread ended with total time 24 sec
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 1 [Note] WSREP: Installed new state from SST: 16442fed-6f68-11ee-ae8d-9b68eafda4eb:113997
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 0 [Note] mariadbd: ready for connections.
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: Version: '11.0.3-MariaDB-1:11.0.3+maria~ubu2204-log' socket: '/run/mysqld/mysqld.sock' port: 3306 mariadb.org binary distribution
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 1 [Note] WSREP: Cert. index preload up to 113997
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 0 [Note] WSREP: ####### IST applying starts with 113998
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 0 [Note] WSREP: ####### IST current seqno initialized to 112986
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 0 [Note] WSREP: Receiving IST… 0.0% ( 0/1010 events) complete.
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 0 [Note] WSREP: IST preload starting at 112986
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 0 [Note] WSREP: Service thread queue flushed.
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 0 [Note] WSREP: ####### Assign initial position for certification: 00000000-0000-0000-0000-000000000000:112985, protocol version: 5
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 0 [Note] WSREP: REPL Protocols: 10 (5)
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 0 [Note] WSREP: ####### Adjusting cert position: 113015 -> 113016
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 0 [Note] WSREP: Service thread queue flushed.
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 0 [Note] WSREP: Lowest cert index boundary for CC from preload: 112986
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 0 [Note] WSREP: Min available from gcache for CC from preload: 112986
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 0 [Note] WSREP: REPL Protocols: 10 (5)
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 0 [Note] WSREP: ####### Adjusting cert position: 113994 -> 113995
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 0 [Note] WSREP: Service thread queue flushed.
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 0 [Note] WSREP: Lowest cert index boundary for CC from preload: 112986
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 0 [Note] WSREP: Min available from gcache for CC from preload: 112986
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 0 [Note] WSREP: Receiving IST…100.0% (1010/1010 events) complete.
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 1 [Note] WSREP: Cert. index preloaded up to 113995
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 1 [Note] WSREP: Lowest cert index boundary for CC from sst: 112986
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 1 [Note] WSREP: Min available from gcache for CC from sst: 112986
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 0 [Note] WSREP: 2.0 (11a0c1b70499): State transfer from 0.0 (e541a05fe7a3) complete.
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 0 [Note] WSREP: Shifting JOINER -> JOINED (TO: 114023)
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 0 [Note] WSREP: Processing event queue:… 0.0% ( 0/25 events) complete.
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 0 [Note] WSREP: Member 2.0 (11a0c1b70499) synced with group.
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 0 [Note] WSREP: Processing event queue:…100.0% (26/26 events) complete.
Oct 21 14:23:50 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:50 0 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 114023)
Oct 21 14:23:52 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:52 1 [Note] WSREP: Server 11a0c1b70499 synced with group
Oct 21 14:23:52 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:52 1 [Note] WSREP: Server status change joined -> synced
Oct 21 14:23:52 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:52 1 [Note] WSREP: Synchronized with group, ready for connections
Oct 21 14:23:52 galera02.incandescent.tech docker[1565]: 2023-10-21 12:23:52 1 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.