High Memory Usage on One Percona XtraDB Cluster Node (Node A) Post Restart

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

Hi Percona Team,

We are currently running a 4-node Percona XtraDB Cluster (Nodes A, B, C, and D) along with a slave node (Node E) replicating from master Node D. We also have two application nodes connecting to the cluster.

Recently, we performed a simultaneous MySQL service restart on all four DB nodes. Post-restart, we observed unusually high memory consumption specifically on Node A compared to the other nodes. Below is a snapshot of the memory usage across all nodes:

NodeA:
total used free shared buff/cache available
Mem: 62 56 5 0 0 5
Swap: 31 0 31

Node B:
total used free shared buff/cache available
Mem: 62 45 17 0 0 16
Swap: 31 0 31

Node C:
total used free shared buff/cache available
Mem: 62 51 10 0 0 10
Swap: 31 0 31

Node D:
total used free shared buff/cache available
Mem: 62 46 15 0 0 15
Swap: 31 10 21

Slave Node E:
total used free shared buff/cache available
Mem: 62 43 18 0 0 18
Swap: 31 0 31

We did not observe any connection threshold, long-running query, or unusually high query volume alerts specific to Node A during this time.

Additionally, we’ve noticed a recurring pattern: memory consumption steadily grows over 2–3 weeks until one of the nodes (not always the same) experiences low available memory, which is temporarily resolved by restarting the MySQL service.

We are planning to implement a load balancer in the near future. Could you please advise:
(1)What might be causing the persistent high memory usage on a specific node post-restart?
(2)Could the issue be related to uneven query routing from the application layer?
(3)Would introducing a load balancer help evenly distribute load and potentially resolve this issue?
(4)Are there any memory-related configurations or diagnostics you recommend reviewing?

Your guidance will be highly appreciated to help us address this anomaly and prevent recurrence.

Please refer below my.cnf file which we are using currently in our production environment.
Note: We have 62GB RAM memory on our server.

Template my.cnf for PXC

Edit to your requirements.

[mysqld]
server-id=1
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin
log_slave_updates
expire_logs_days=7
skip-name-resolve

Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

innodb perf settings

innodb_file_per_table=ON
innodb_stats_on_metadata = OFF
innodb_read_io_threads=16
innodb_write_io_threads=16
innodb_buffer_pool_instances=32
innodb_buffer_pool_size=40GB
innodb_buffer_pool_load_at_startup=ON
innodb_log_file_size = 8G # equal to 1/4 of buffer pool size
innodb_log_files_in_group=2
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency = 0
max_connections=1000
max_connect_errors=100
max_allowed_packet= 32M
max_heap_table_size = 128M
tmp_table_size = 128M
thread_cache_size = 2000
open_files_limit = 65535

Time out setting

#wait_timeout=300
#interactive_timeout = 300

Thanks and Regards,
Rahul Ambekar
MySQL DBA

2

Be sure to make it so WRITEs only go to one node. You can spread READs evenly to other nodes.

Yes. If you have PMM setup, you should be able to compare QPS to CPU/Memory quite easily.

I would install jemalloc library as a preload library to mysql. jemalloc does a better job of managing memory than the standard libc.

3

image
image1179×360 33.1 KB

Hi Matthew,

We really appreciate your quick response.
Answering to your response:

(1) Be sure to make it so WRITEs only go to one node. You can spread READs evenly to other nodes.
– Yes we are using only one write node.

(2)Yes. If you have PMM setup, you should be able to compare QPS to CPU/Memory quite easily.
– We do not have PMM but we have Zabbix monitoring tool through which I got QPS and uploaded the same.
We will try installing PMM for better MySQL performance overview.

(3)I would install jemalloc library as a preload library to mysql. jemalloc does a better job of managing memory than the standard libc.
– We will try installing jemalloc library.

(4) Installing load balancer like HAproxy will that be helpful ?
so that random traffic is not targeting one db node.

Thank you in advance !

Best Regards,
Rahul Ambekar
MySQL DBA