Strangely high amount on memory usage

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

Hey Guys, Hoping someone can give me some insight. I’m trialing a percona cluster server and at the moment there is only 1 node which was the slave of an existing master. Everything was going fine with it (besides some delete issues related to no primary key) up until i made this slave the new master. Now i cant keep the server up for more than a few hours before it consumes all of the memory available on the server (20gb). I’m hoping what i have is a simple stupid configuration issue and that someone can point out my ignorance but it may be something deeper. I tried disabling all of the galera functions and the issue still prevails. See config:

[mysqld]

# -----------------------
# MySQL Settings 
# -----------------------

user=mysql
max_connections=8000
max_connect_errors=10000
max_heap_table_size=6G
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
max_allowed_packet = 256MB
thread_cache_size=12
server-id=3
expire-logs-days=7
binlog_format=ROW
table_cache=4000

# -----------------------
# InnoDB Settings 
# -----------------------

innodb_buffer_pool_size=6G
innodb_write_io_threads=12
innodb_read_io_threads=8
innodb_file_per_table=1
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
innodb_additional_mem_pool_size = 1G
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1
innodb_doublewrite = 0

# -----------------------
# XtraDB Cluster Settings 
# -----------------------

#wsrep_provider=/usr/lib64/libgalera_smm.so

# Only boot with this setting blank to bootstrap a NEW cluster. When joining a running cluster ALWAYS have this pointing to AT LEAST 1 node
#wsrep_cluster_address=gcomm://xx.xx.xx.xx;xx.xx.xx.xx; #Future 2nd and 3rd node addresses

#wsrep_node_address=xx.xx.xx.xx
#wsrep_cluster_name=ClusterNodeMaster

#wsrep_slave_threads=12
#wsrep_provider_options = "gcache.size = 12G; gcache.page_size = 12G; gcs.fc_limit = 512; gcs.fc_factor = 1.0; gcs.fc_master_slave=YES; evs.keepalive_period = PT3S; evs.inactive_check_period = PT10S; evs.suspect_timeout = PT30S; evs.inactive_timeout = PT1M; evs.install_timeout = PT1M; gcs.recv_q_soft_limit = 0.5; gcs.recv_q_hard_limit = 6442450944"
#wsrep_retry_autocommit=3

# SST Settings (the way new node and current running nodes will join / re-join
#wsrep_sst_method=xtrabackup

# Authentication for SST method
#wsrep_sst_auth="BackupAdmin:passwprd"

#[sst]
#streamfmt=xbstream

#[xtrabackup]
#compress
#compact
#parallel=2
#compress-threads=2
#rebuild-threads=2

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

At the moment the serve goes from about 14% memory usage before starting the server to 90%+ in about 15min.

Hope someone can help :frowning:

Steve

2

max_connections=8000, max_heap_table_size=6G - sounds a lot. Note that each single, even idle connection needs additional memory. Also, you allow very large memory tables, any reason for that? Imagine if your clients create several such large memory tables.

Is the server now part of cluster or not, I can see all wsrep related vars are commented out. Note that for each large transaction additional memory is needed. So it’s crucial to avoid very big transactions in PXC Cluster.