Galera cluster goes down every 5-10 days
77 views
Skip to first unread message
François Delpierre
Oct 10, 2022, 2:59:20 AM10/10/22
to codership
Hi,
I have a problem for several months I need to understand. Config:
- 3 nodes (galera1,galera2,galera3)
- Server version: 10.6.7-MariaDB-2ubuntu1.1-log Ubuntu 22.04
- Running in LXC / Proxmox VE
The load balancer is ipvs TCP with higher weight on first node, and properly removed the failing node:
root@burns2:~# ipvsadm --list --tcp-service "192.168.232.1:3306"
Prot LocalAddress:Port Scheduler Flags
-> RemoteAddress:Port Forward Weight ActiveConn InActConn
TCP burns2:mysql rr persistent 180
-> 192.168.10.51:mysql Masq 4 0 34
-> 192.168.10.53:mysql Masq 1 0 32
Prot LocalAddress:Port Scheduler Flags
-> RemoteAddress:Port Forward Weight ActiveConn InActConn
TCP burns2:mysql rr persistent 180
-> 192.168.10.51:mysql Masq 4 0 34
-> 192.168.10.53:mysql Masq 1 0 32
Problem, about every 5-10 days:
The second nodes gangs (galera2), but galera does not evict it. Instead, the entire cluster switch to Read-Only. More details:
- The galera2 instance becomes unresponsive (hangs).
- The LB in front of the cluster properly remove galera2 from the backends. But on the clients connecting to the 2 remaining nodes, logs shows:
WordPress database error Lock wait timeout exceeded; try restarting transaction for query UPDATE `wp_options` SET `option_value` = ... - I can connect from localhost via socket on galera2, but even a show status hangs.
- I cannot connect from remote network on the failing node ( Access denied for user... )
- A client from localhost on galera2 (network) gets a more explicit error:
ERROR 1226 (42000): User 'exporter' has exceeded the 'max_user_connections' resource (current value: 5) - I can connect on the 2 other nodes via network. But they are "Read Only".
On the failing node (galera2):
MariaDB [(none)]> show processlist;
+--------+-------------+------------------------------+------------+---------+--------+-----------------------+------------------------------------------------------------------------------------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+--------+-------------+------------------------------+------------+---------+--------+-----------------------+------------------------------------------------------------------------------------------------------+----------+
| 1 | system user | | NULL | Sleep | 18007 | innobase_commit_low() | NULL | 0.000 |
| 2 | system user | | NULL | Sleep | 315543 | wsrep aborter idle | NULL | 0.000 |
| 151176 | exporter | localhost:42238 | NULL | Query | 18002 | Filling schema table | SHOW GLOBAL STATUS | 0.000 |
| 151177 | wppivert | swarm2.home.pivert.org:44738 | pivertwpdb | Query | 18002 | Statistics | SELECT option_value FROM wp_options WHERE option_name = 'omgf_cache_is_stale' LIMIT 1 | 0.000 |
| 151178 | beamwp | swarm2.home.pivert.org:48630 | beamdb | Query | 17949 | Updating | UPDATE `wp_options` SET `option_value` = '1665115917.9702680110931396484375' WHERE `option_name` = ' | 0.000 |
| 151179 | exporter | localhost:58652 | NULL | Query | 17998 | Filling schema table | SHOW GLOBAL STATUS | 0.000 |
| 151181 | exporter | localhost:41478 | NULL | Query | 17983 | Filling schema table | SHOW GLOBAL STATUS | 0.000 |
| 151183 | exporter | localhost:43818 | NULL | Query | 17968 | Filling schema table | SHOW GLOBAL STATUS | 0.000 |
| 151184 | exporter | localhost:36450 | NULL | Query | 17953 | Filling schema table | SHOW GLOBAL STATUS | 0.000 |
| 152933 | root | localhost | NULL | Killed | 2958 | Filling schema table | SHOW GLOBAL STATUS LIKE 'wsrep_%' | 0.000 |
| 153004 | root | localhost | NULL | Killed | 2308 | Filling schema table | SHOW GLOBAL STATUS LIKE 'wsrep_%' | 0.000 |
| 153094 | root | localhost | NULL | Killed | 1495 | Filling schema table | show status | 0.000 |
| 153242 | root | localhost | NULL | Killed | 51 | Filling schema table | show status | 0.000 |
| 153245 | root | localhost | NULL | Query | 0 | starting | show processlist | 0.000 |
+--------+-------------+------------------------------+------------+---------+--------+-----------------------+------------------------------------------------------------------------------------------------------+----------+
14 rows in set (0.000 sec)
On a healthy node (galera1):
+--------+-------------+------------------------------+------------+---------+--------+-----------------------+------------------------------------------------------------------------------------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+--------+-------------+------------------------------+------------+---------+--------+-----------------------+------------------------------------------------------------------------------------------------------+----------+
| 1 | system user | | NULL | Sleep | 18007 | innobase_commit_low() | NULL | 0.000 |
| 2 | system user | | NULL | Sleep | 315543 | wsrep aborter idle | NULL | 0.000 |
| 151176 | exporter | localhost:42238 | NULL | Query | 18002 | Filling schema table | SHOW GLOBAL STATUS | 0.000 |
| 151177 | wppivert | swarm2.home.pivert.org:44738 | pivertwpdb | Query | 18002 | Statistics | SELECT option_value FROM wp_options WHERE option_name = 'omgf_cache_is_stale' LIMIT 1 | 0.000 |
| 151178 | beamwp | swarm2.home.pivert.org:48630 | beamdb | Query | 17949 | Updating | UPDATE `wp_options` SET `option_value` = '1665115917.9702680110931396484375' WHERE `option_name` = ' | 0.000 |
| 151179 | exporter | localhost:58652 | NULL | Query | 17998 | Filling schema table | SHOW GLOBAL STATUS | 0.000 |
| 151181 | exporter | localhost:41478 | NULL | Query | 17983 | Filling schema table | SHOW GLOBAL STATUS | 0.000 |
| 151183 | exporter | localhost:43818 | NULL | Query | 17968 | Filling schema table | SHOW GLOBAL STATUS | 0.000 |
| 151184 | exporter | localhost:36450 | NULL | Query | 17953 | Filling schema table | SHOW GLOBAL STATUS | 0.000 |
| 152933 | root | localhost | NULL | Killed | 2958 | Filling schema table | SHOW GLOBAL STATUS LIKE 'wsrep_%' | 0.000 |
| 153004 | root | localhost | NULL | Killed | 2308 | Filling schema table | SHOW GLOBAL STATUS LIKE 'wsrep_%' | 0.000 |
| 153094 | root | localhost | NULL | Killed | 1495 | Filling schema table | show status | 0.000 |
| 153242 | root | localhost | NULL | Killed | 51 | Filling schema table | show status | 0.000 |
| 153245 | root | localhost | NULL | Query | 0 | starting | show processlist | 0.000 |
+--------+-------------+------------------------------+------------+---------+--------+-----------------------+------------------------------------------------------------------------------------------------------+----------+
14 rows in set (0.000 sec)
On a healthy node (galera1):
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 49057
Server version: 10.6.7-MariaDB-2ubuntu1.1-log Ubuntu 22.04
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [pivertwpdb]> show status like 'wsrep%';
+-------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+-------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| wsrep_local_state_uuid | 40832eb8-5063-11ec-9230-324eac1e5285 |
| wsrep_protocol_version | 10 |
| wsrep_last_committed | 3506053 |
| wsrep_replicated | 16640 |
| wsrep_replicated_bytes | 86054688 |
| wsrep_repl_keys | 65556 |
| wsrep_repl_keys_bytes | 923808 |
| wsrep_repl_data_bytes | 83996428 |
| wsrep_repl_other_bytes | 0 |
| wsrep_received | 40166 |
| wsrep_received_bytes | 619481960 |
| wsrep_local_commits | 16626 |
| wsrep_local_cert_failures | 2 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 10 |
| wsrep_local_send_queue_max | 10 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_send_queue_avg | 0.00263374 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_max | 7 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_recv_queue_avg | 0.0114027 |
| wsrep_local_cached_downto | 3496374 |
| wsrep_flow_control_paused_ns | 18114612046312 |
| wsrep_flow_control_paused | 0.147389 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 1 |
| wsrep_flow_control_active | true |
| wsrep_flow_control_requested | false |
| wsrep_cert_deps_distance | 22.4782 |
| wsrep_apply_oooe | 0.00154852 |
| wsrep_apply_oool | 0 |
| wsrep_apply_window | 1.00158 |
| wsrep_apply_waits | 12 |
| wsrep_commit_oooe | 0 |
| wsrep_commit_oool | 0 |
| wsrep_commit_window | 1.00043 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cert_index_size | 63 |
| wsrep_causal_reads | 14 |
| wsrep_cert_interval | 3478.97 |
| wsrep_open_transactions | 10 |
| wsrep_open_connections | 0 |
| wsrep_incoming_addresses | AUTO,AUTO,AUTO |
| wsrep_cluster_weight | 3 |
| wsrep_desync_count | 0 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 0/0/0/0/0 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_gcomm_uuid | 46375e08-4502-11ed-a695-d33dcc175ce6 |
| wsrep_gmcast_segment | 0 |
| wsrep_applier_thread_count | 1 |
| wsrep_cluster_capabilities | |
| wsrep_cluster_conf_id | 39 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | 40832eb8-5063-11ec-9230-324eac1e5285 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_bf_aborts | 14 |
| wsrep_local_index | 1 |
| wsrep_provider_capabilities | :MULTI_MASTER:CERTIFICATION:PARALLEL_APPLYING:TRX_REPLAY:ISOLATION:PAUSE:CAUSAL_READS:INCREMENTAL_WRITESET:UNORDERED:PREORDERED:STREAMING:NBO: |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <in...@codership.com> |
| wsrep_provider_version | 4.9(rcece3ba2) |
| wsrep_ready | ON |
| wsrep_rollbacker_thread_count | 1 |
| wsrep_thread_count | 2 |
+-------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+
69 rows in set (0.002 sec)
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 49057
Server version: 10.6.7-MariaDB-2ubuntu1.1-log Ubuntu 22.04
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [pivertwpdb]> show status like 'wsrep%';
+-------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+-------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| wsrep_local_state_uuid | 40832eb8-5063-11ec-9230-324eac1e5285 |
| wsrep_protocol_version | 10 |
| wsrep_last_committed | 3506053 |
| wsrep_replicated | 16640 |
| wsrep_replicated_bytes | 86054688 |
| wsrep_repl_keys | 65556 |
| wsrep_repl_keys_bytes | 923808 |
| wsrep_repl_data_bytes | 83996428 |
| wsrep_repl_other_bytes | 0 |
| wsrep_received | 40166 |
| wsrep_received_bytes | 619481960 |
| wsrep_local_commits | 16626 |
| wsrep_local_cert_failures | 2 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 10 |
| wsrep_local_send_queue_max | 10 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_send_queue_avg | 0.00263374 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_max | 7 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_recv_queue_avg | 0.0114027 |
| wsrep_local_cached_downto | 3496374 |
| wsrep_flow_control_paused_ns | 18114612046312 |
| wsrep_flow_control_paused | 0.147389 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 1 |
| wsrep_flow_control_active | true |
| wsrep_flow_control_requested | false |
| wsrep_cert_deps_distance | 22.4782 |
| wsrep_apply_oooe | 0.00154852 |
| wsrep_apply_oool | 0 |
| wsrep_apply_window | 1.00158 |
| wsrep_apply_waits | 12 |
| wsrep_commit_oooe | 0 |
| wsrep_commit_oool | 0 |
| wsrep_commit_window | 1.00043 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cert_index_size | 63 |
| wsrep_causal_reads | 14 |
| wsrep_cert_interval | 3478.97 |
| wsrep_open_transactions | 10 |
| wsrep_open_connections | 0 |
| wsrep_incoming_addresses | AUTO,AUTO,AUTO |
| wsrep_cluster_weight | 3 |
| wsrep_desync_count | 0 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 0/0/0/0/0 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_gcomm_uuid | 46375e08-4502-11ed-a695-d33dcc175ce6 |
| wsrep_gmcast_segment | 0 |
| wsrep_applier_thread_count | 1 |
| wsrep_cluster_capabilities | |
| wsrep_cluster_conf_id | 39 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | 40832eb8-5063-11ec-9230-324eac1e5285 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_bf_aborts | 14 |
| wsrep_local_index | 1 |
| wsrep_provider_capabilities | :MULTI_MASTER:CERTIFICATION:PARALLEL_APPLYING:TRX_REPLAY:ISOLATION:PAUSE:CAUSAL_READS:INCREMENTAL_WRITESET:UNORDERED:PREORDERED:STREAMING:NBO: |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <in...@codership.com> |
| wsrep_provider_version | 4.9(rcece3ba2) |
| wsrep_ready | ON |
| wsrep_rollbacker_thread_count | 1 |
| wsrep_thread_count | 2 |
+-------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+
69 rows in set (0.002 sec)
The /var/log/mysql/mysql.log.1 only shows regular
221007 9:32:39 153380 Connect exporter@localhost on using TCP/IP
To recover galera2:
- The DB won't stop (systemctl restart or stop hangs)
- I need to SigKill it (-9) the mariadbd process and `systemctl restart mariadb`
- Then the cluster immediately goes back online, until next crash...
I need to figure out:
- Why Galera does not evict (fencing) the failing galera2 node ?
- Why does MariaDB on galera2 hangs ?
François Delpierre
Oct 14, 2022, 12:47:11 PM10/14/22
to codership
Got similar freeze / Read-Only cluster this morning.
Just shutting down the failing node (always the same node #2) fixes the problem. Cluster goes back to RW, and performs normally. Galera is just not evicting the failing node.
I still have logs for investigations, but decided to reinstall this node #2 from scratch... Let's wait and see...
François Delpierre
Oct 21, 2022, 6:31:56 AM10/21/22
to codership
Reinstallation of the node did not fix the problem. Sequence of events:
- A snapshot backup freezes the server for about 50s.
- The cluster continues to run, for some 4-5 hours.
- After 4-5 hours, the mariaDB seems to partily freeze : and we see this message appearing on all 3 nodes : "Got an error reading communication packets" when a user connects to it.
- The other nodes says up, and turns into read-only mode instead of evicting the faulty node.
- When I notice the problem, I just isolate the network of the faulty node, it gets quickly evicted : WSREP: forgetting 0e02d3a3-8df4 (tcp://192.168.10.52:4567)
- Then 2 remaining nodes are going back to normal operation (RW mode).
- Then 2 remaining nodes are going back to normal operation (RW mode).
- The reboot of the faulty node takes a lot of time, likely the MariaDB needs to be killed after timeout.
Conclusion:
- There might be some queue/log stacking for some 4-5 hours before the cluster completely goes down, rejecting connections with a "Got an error reading communication packets"
- Galera fails to evict a faulty node if it still answers. The
prometheus mysql exporter however well identify the node down (it can't
connect to it with a regular user). Some improvements are needed in
Galera node failure detection.
- Galera/mariaDB does not support properly snapshot backups, even if it last less than 1 minute.
- I'll switch to power-off backups, and definitely forget about snapshot backups on a galera cluster.
Regards,
Reply all
Reply to author
Forward
0 new messages