Not the answer you need?
Register and ask your own question!

mysql keeps using memory and swap , never releases memory

jason_asiajason_asia ContributorInactive User Role Beginner
[problem]
mysql keep using ram and swap , but it never releasing the memory until it out of memory
[environment]
physical machine: dell r710,32G RAM,16G SWAP,raid10,146G*6 disks
[mysql version]
Server version: 5.0.67-percona-highperf-log Source distribution ( we have another 4 mysql servers, only the 5th one has this problem)

[mysql basic configuration]
> show variables like '%buffer%';
+
+
+
| Variable_name | Value |
+
+
+
| bulk_insert_buffer_size | 67108864 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 12884901888 |
| innodb_log_buffer_size | 16777216 |
| join_buffer_size | 2097152 |
| key_buffer_size | 67108864 |
| myisam_sort_buffer_size | 134217728 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 1048576 |
| read_rnd_buffer_size | 16777216 |
| sort_buffer_size | 2097152 |
+
+
+

# free -mt
total used free shared buffers cached
Mem: 24094 24018 75 0 7 1959
-/+ buffers/cache: 22051 2042
Swap: 16386 11089 5296
Total: 40480 35108 5372

# ps aux |grep mysql
root 19820 0.0 0.0 78484 1788 pts/0 S+ 22:41 0:00 mysql
root 22785 0.0 0.0 61156 664 pts/1 S+ 23:03 0:00 grep mysql
root 29703 0.0 0.0 65928 852 ? S Mar28 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/home/mysql --pid-file=/home/mysql/mysql.pid
mysql 29740 11.9 89.8 43209456 22163564 ? Sl Mar28 364:06 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/home/mysql --user=mysql --pid-file=/home/mysql/mysql.pid --skip-external-locking --port=3306 --socket=/home/mysql/mysql.sock

# top
top - 23:04:09 up 3 days, 10:56, 2 users, load average: 0.07, 0.16, 0.17
Tasks: 136 total, 1 running, 135 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.3%us, 0.1%sy, 0.0%ni, 99.4%id, 0.0%wa, 0.0%hi, 0.2%si, 0.0%st
Mem: 24672344k total, 24592592k used, 79752k free, 7320k buffers
Swap: 16779884k total, 11361564k used, 5418320k free, 2007552k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
29740 mysql 15 0 41.2g 21g 42m S 3.0 89.8 364:06.35 mysqld
18325 root 16 0 87028 3308 2572 S 0.0 0.0 0:00.02 sshd
22532 root 15 0 86156 3300 2576 S 0.0 0.0 0:00.03 sshd
19820 root 16 0 78484 1788 1296 S 0.0 0.0 0:00.00 mysql
22534 root 15 0 68152 1652 1232 S 0.0 0.0 0:00.00 bash
18327 root 15 0 68152 1648 1232 S 0.0 0.0 0:00.02 bash
22795 root 15 0 12736 1100 816 R 0.0 0.0 0:00.01 top
29703 root 21 0 65928 852 848 S 0.0 0.0 0:00.00 mysqld_safe
29644 root 15 0 60672 696 568 S 0.0 0.0 0:01.77 sshd
28292 root 15 0 21640 684 588 S 0.0 0.0 0:02.29 xinetd
28743 root 16 0 74840 552 484 S 0.0 0.0 0:00.17 crond

thank u very much

Comments

  • niljoshiniljoshi MySQL Sage Inactive User Role Beginner
    Hi,

    I found some bugs related to memory leak in 5.0.67 but not sure, its related to your issue too.
    http://bugs.mysql.com/bug.php?id=45002
    http://bugs.mysql.com/bug.php?id=33807

    Even Percona server has some memory leak issue so for that we have launched some patches.
    http://www.mysqlperformanceblog.com/...rcona-build10/

    Is there any specific reason to stick with 5.0? I would suggest to upgrade to latest version (5.5 OR 5.6) for better performance, also many bugs are resolved in these versions.

    I would also suggest to check this post to find out where the memory is allocated. OS cache also can be cause of this.
    http://www.mysqlperformanceblog.com/2014/01/24/mysql-server-memory-usage-2/
  • jason_asiajason_asia Contributor Inactive User Role Beginner
    hi, niljoshi

    i'm reading your article "MySQL server memory usage troubleshooting tips", i'm trying to find out the reason.

    We have another 4 mysql servers , they are all using mysql 5.0.67-percona-highperf-log Source distribution , so the new one we use 5.0.67 , too

    The mysql server will use all of the memory and swap when it runs for about 48 hous, if we haven't find out the reason and resolve it , we have to upgrade to mysql 5.5.20.

    If we find any new informations , i'll reply ASAP.

    thank u very much.
  • niljoshiniljoshi MySQL Sage Inactive User Role Beginner
    Hi, Thanks for your feedback. I'll wait for new information.
  • jason_asiajason_asia Contributor Inactive User Role Beginner
    Hi, niljoshi

    I compute the max memory size that mysql will use :

    SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
    -- 12G
    SHOW VARIABLES LIKE 'innodb_additional_mem_pool_size';
    -- 16M
    SHOW VARIABLES LIKE 'innodb_log_buffer_size';
    -- 16M
    SHOW VARIABLES LIKE 'thread_stack';
    -- 192K
    SHOW VARIABLES LIKE 'max_connections';
    -- 200
    show variables like 'tmp_table_size';
    -- 100663296 , 96M

    SET @kilo_bytes = 1024;
    SET @mega_bytes = @kilo_bytes * 1024;
    SET @giga_bytes = @mega_bytes * 1024;
    SET @innodb_buffer_pool_size = 12 * @giga_bytes;
    SET @innodb_additional_mem_pool_size = 16 * @mega_bytes;
    SET @innodb_log_buffer_size = 16 * @mega_bytes;
    SET @thread_stack = 192 * @kilo_bytes;

    SELECT
    ( @@key_buffer_size + @@query_cache_size + @@tmp_table_size
    + @innodb_buffer_pool_size + @innodb_additional_mem_pool_size
    + @innodb_log_buffer_size
    + @@max_connections * (
    @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size
    + @@join_buffer_size + @@binlog_cache_size + @thread_stack
    ) ) / @giga_bytes AS MAX_MEMORY_GB;

    -- 17581.5000M
    -- 17.1694G

    But the running environment is like this:

    top - 16:33:14 up 1 day, 15:45, 2 users, load average: 0.05, 0.12, 0.09
    Tasks: 137 total, 1 running, 135 sleeping, 1 stopped, 0 zombie
    Cpu(s): 2.2%us, 0.4%sy, 0.0%ni, 96.3%id, 0.7%wa, 0.0%hi, 0.3%si, 0.0%st
    Mem: 24672344k total, 24550744k used, 121600k free, 55452k buffers
    Swap: 16779884k total, 156k used, 16779728k free, 4564380k cached

    PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
    3154 mysql 15 0 30.7g 18g 7752 S 21.3 79.5 198:21.45 mysqld Call
    Send SMS
    Add to Skype
    You'll need Skype CreditFree via Skype
  • jason_asiajason_asia Contributor Inactive User Role Beginner
    Hi, niljoshi

    this the tps and qps info:

    # values are from " show global status"

    # TPS
    | Com_commit | 8361540 |
    | Com_rollback | 0 |
    | Uptime | 140760 |

    TPS = (Com_commit + Com_rollback ) / Uptime = (8361540 + 0 ) / 140760 = 60 T/s

    # QPS
    | Questions | 147005204 |
    | Uptime | 140760 |

    QPS = Questions / Uptime = 147005204 / 140760 = 1045 Q/s
  • jason_asiajason_asia Contributor Inactive User Role Beginner
    Hi, niljoshi

    This is the query cache info:

    # check query status
    > show variables like ' have_query_cache ';
    +
    +
    +
    | Variable_name | Value |
    +
    +
    +
    | have_query_cache | YES |
    +
    +
    +

    # some values get from "show global variables like 'query%'":
    | query_alloc_block_size | 8192 |
    | query_cache_limit | 2097152 | # 2M
    | query_cache_min_res_unit | 2048 | #2048byte
    | query_cache_size | 67108864 | # 64M
    | query_cache_type | ON | # on
    | query_cache_wlock_invalidate | OFF |
    | query_prealloc_size | 8192 |

    # Qcache values in "show global status":
    | Qcache_free_blocks | 179 | # not too many
    | Qcache_free_memory | 66697000 | # 63.6M free
    | Qcache_hits | 32494 |
    | Qcache_inserts | 174917 |
    | Qcache_lowmem_prunes | 0 | # zero
    | Qcache_not_cached | 7294692 |
    | Qcache_queries_in_cache | 317 |
    | Qcache_total_blocks | 819 | # count of blocks

    # show global values
    Com_delete | 551886
    Com_insert | 4306455
    Com_update | 53076201
    Com_truncate | 3
    Com_select | 7501372 |

    # cache hit percentage
    Qcache_hists/(Qcache_hits+Com_select) = 32494/(32494+7501372) = 4%
    Qcache_hists/( Qcache_hists +Qcache_inserts) =32494/(32494+174917) = 15.7%

    #
    Qcache_lowmem_prunes | 0

    # average cache used for per query
    query_cache_size | 67108864
    Qcache_free_memory | 62965144
    Qcache_queries_in_cache | 3168

    (query_cache_size - Qcache_free_memory)/ Qcache_queries_in_cache = (67108864-62965144)/ 3168=1308

    # query cache memory block gaps
    Qcache_total_blocks | 6839
    Qcache_free_blocks | 485

    Qcache_total_blocks/2 > Qcache_free_blocks
  • jason_asiajason_asia Contributor Inactive User Role Beginner
    Hi,

    This is the memory usage info:

    the straight line means the mysql server was restarted again;
    the red line means the "-/+buffer cache used" ;
    the back line means the "Swap used"
  • jason_asiajason_asia Contributor Inactive User Role Beginner
  • jason_asiajason_asia Contributor Inactive User Role Beginner
    Hi,niljoshi

    i upload the picture of the memory usage info failed.

    it's like this:

    the usage of memory keeps going up , when it's about 90%, the usage of swap start going up until about 80% percent of swap has been used , then the system's iowait will go up to 10~30%
  • jason_asiajason_asia Contributor Inactive User Role Beginner
    Hi,niljoshi

    i upload the picture of the memory usage info failed.

    it's like this:

    the usage of memory keeps going up , when it's about 90%, the usage of swap start going up until about 80% percent of swap has been used , then the system's iowait will go up to 10~30%
  • jason_asiajason_asia Contributor Inactive User Role Beginner
    Finally we found out the reason , it's a bug of mysql 5.0.67.
    MySQL5.0.67 write the client dynamic information into information_schema.CLIENT_STATISTICS , client_statistics is in memory.
    Normally, one client will have only one record , but this time, we set "client_ip client_hostname" in /etc/hosts , and the length of client_hostname longer than 16 character , which is longer than length of information_schema.client_statistics.client column varchar(16), then mysql will keep update the record about client_hostname in client_statistics, but this is a bug, every time mysql update client_statistics to update client_hostname's record , it will insert a new record with wrong values. As the time going , there will be more and more records in client_statistics , which is in the memory , it won't be released until you restart mysql server.
    So, after we change the "client_ip client_hostname" in /etc/hosts, restarting mysql server, everything becomes ok.

    Thanks all.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.