Memory usage problems (5.5.27 Percona XtraDB Cluster)

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

I’m evaluating Percona XtraDB cluster in our Test environment (Amazon EC2 with 2 Small instances). Installation went easily but only problem seems to be that even under light utilization mysqld process size grows all the time without releasing the memory. I’m not familiar in debugging Mysql memory usage, so please advice if I need to provide some additional information.

OS: Centos 6.3

With following packages installed:

Percona-Server-shared-51-5.1.66-rel14.1.495.rhel6.x86_64
percona-release-0.0-1.x86_64
percona-xtrabackup-2.0.3-470.rhel6.x86_64
Percona-XtraDB-Cluster-client-5.5.27-23.6.356.rhel6.x86_64
Percona-XtraDB-Cluster-server-5.5.27-23.6.356.rhel6.x86_64

Updated Galera to the latest available:
galera-23.2.2-1.rhel5.x86_64

mysql> show status like ‘wsrep_provider%’;±-----------------------±----------------------------------+| Variable_name | Value |±-----------------------±----------------------------------+| wsrep_provider_name | Galera || wsrep_provider_vendor | Codership Oy <info@codership.com> || wsrep_provider_version | 23.2.2(r137) |±-----------------------±----------------------------------+

my.cnf:

[mysqld_safe]
wsrep_urls=gcomm://10.0.0.101:4567,gcomm://10.0.0.102:4567,gcomm://

[mysqld]
datadir=/var/lib/mysql/mysql_data
user=mysql
log_slave_updates = 1
binlog_format=ROW
max_allowed_packet = 200M
default_storage_engine=InnoDB

#wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_slave_threads=2
wsrep_cluster_name=mycluster
wsrep_sst_method=xtrabackup
wsrep_sst_auth=root:
wsrep_node_name=test-web01

innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 4M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1

Here’s some tests I have done:

after starting Mysql top -command shows following:

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND14103 mysql 20 0 1165m 84m 7268 S 0.0 5.1 0:00.31 mysqld

Mysql Status:

mysql> status;--------------mysql Ver 14.14 Distrib 5.5.27, for Linux (x86_64) using readline 5.1Connection id: 4Current database:Current user: root@localhostSSL: Not in useCurrent pager: stdoutUsing outfile: ''Using delimiter: ;Server version: 5.5.27 Percona XtraDB Cluster (GPL), wsrep_23.6.r356Protocol version: 10Connection: Localhost via UNIX socketServer characterset: latin1Db characterset: latin1Client characterset: utf8Conn. characterset: utf8UNIX socket: /var/lib/mysql/mysql.sockUptime: 22 secThreads: 4 Questions: 12 Slow queries: 0 Opens: 33 Flush tables: 1 Open tables: 26 Queries per second avg: 0.545--------------

After some random queries (used our Mysql based application):

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND15505 mysql 20 0 1559m 657m 135m S 0.0 39.7 1:46.74 mysqld

mysql> status;--------------mysql Ver 14.14 Distrib 5.5.27, for Linux (x86_64) using readline 5.1Connection id: 258Current database:Current user: root@localhostSSL: Not in useCurrent pager: stdoutUsing outfile: ''Using delimiter: ;Server version: 5.5.27 Percona XtraDB Cluster (GPL), wsrep_23.6.r356Protocol version: 10Connection: Localhost via UNIX socketServer characterset: latin1Db characterset: latin1Client characterset: utf8Conn. characterset: utf8UNIX socket: /var/lib/mysql/mysql.sockUptime: 11 min 0 secThreads: 4 Questions: 62190 Slow queries: 0 Opens: 64 Flush tables: 1 Open tables: 57 Queries per second avg: 94.227--------------

Running Flush tables doesen’t release memory at all. Anyone else having these issue ? Or is there some misconfiguration ? Any help greatly appreciated.

2

I’ve made some comparison between Standalone Percona Mysql Server 5.5.28 and XtraDB cluster mensioned above.

Standalone server has following packages installed:
Percona-Server-server-55-5.5.28-rel29.1.335.rhel6.x86_64
Percona-Server-shared-55-5.5.28-rel29.1.335.rhel6.x86_64
Percona-Server-client-55-5.5.28-rel29.1.335.rhel6.x86_64
percona-release-0.0-1.x86_64

With standalone installation I don’t see any memory leak. I compared with identical test and with XtraDB cluster memory usage grows rapidly w/o never releasing it. We just can’t consider using this in production if there’s no natural explanation for this.

I ran this test([URL]MySQL Bugs: #67259: MySQL 5.5.x uses more memory than 5.1.x when temporary tables are used) on both servers with same InnoDB memory settings:

create database ptest;use ptest;create table ti2(c1 int auto_increment primary key, c2 char(255)) engine=InnoDB;insert into ti2(c2) values(‘abc’);insert into ti2(c2) select c2 from ti2;insert into ti2(c2) select c2 from ti2;insert into ti2(c2) select c2 from ti2;insert into ti2(c2) select c2 from ti2;insert into ti2(c2) select c2 from ti2;insert into ti2(c2) select c2 from ti2;insert into ti2(c2) select c2 from ti2;insert into ti2(c2) select c2 from ti2;insert into ti2(c2) select c2 from ti2;insert into ti2(c2) select c2 from ti2;insert into ti2(c2) select c2 from ti2;insert into ti2(c2) select c2 from ti2;insert into ti2(c2) select c2 from ti2;insert into ti2(c2) select c2 from ti2;insert into ti2(c2) select c2 from ti2;insert into ti2(c2) select c2 from ti2;insert into ti2(c2) select c2 from ti2;insert into ti2(c2) select c2 from ti2;insert into ti2(c2) select c2 from ti2;insert into ti2(c2) select c2 from ti2;insert into ti2(c2) select c2 from ti2;

Results of Percona-Server-server-55-5.5.28-rel29.1.335.rhel6.x86_64

Before

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 7077 mysql 20 0 756m 55m 5540 S 0.0 5.5 0:00.12 mysqld

After

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 7077 mysql 20 0 756m 308m 5788 S 0.3 31.0 0:25.45 mysqld

Results of Percona-XtraDB-Cluster-server-5.5.27-23.6.356.rhel6.x86_64

Before

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 8951 mysql 20 0 1101m 83m 7160 S 0.0 5.1 0:00.29 mysqld

After

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 8951 mysql 20 0 1741m 846m 2508 S 0.0 51.1 1:19.67 mysqld

Running more of these XtraDB cluster server will eventually run out memory.

3

Thanks karileh looks like what I am seeing as well. We will look at testing it and capturing the memory usage.

We also have the galera cache set to 4G… this is going to be a page file in the main data_dir as well as something in memory.

gcache.dir = /var/lib/mysql/;
gcache.keep_pages_size = 0;
gcache.mem_size = 0;
gcache.name = /var/lib/mysql//galera.cache;
gcache.page_size = 128M;
gcache.size = 4G;

For those in the know should I be setting the gcache.page_size, gcache.mem_size too to limit these as well as I have set gcache.size? Is this in any way related to what karileh is seeing as well ? Should he have a configuration using the defaults (128MB, 128MB and 0)?

Dom