GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Memory usage problems (5.5.27 Percona XtraDB Cluster)

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Memory usage problems (5.5.27 Percona XtraDB Cluster)

    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 || wsrep_provider_version | 23.2.2(r137) |+------------------------+-----------------------------------+


    my.cnf:

    [mysqld_safe]
    wsrep_urls=gcomm://10.0.0.101:4567,gcomm://10.0...: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(http://bugs.mysql.com/bug.php?id=67259) 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.

    Comment


    • #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

      Comment

      Working...
      X