GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

mysql keeps using memory and swap , never releases memory

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

  • mysql keeps using memory and swap , never releases memory

    [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

  • #2
    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/...emory-usage-2/
    Last edited by niljoshi; 03-31-2014, 08:40 AM.

    Comment


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

      Comment


      • #4
        Hi, Thanks for your feedback. I'll wait for new information.

        Comment


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

          Comment


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

            Comment


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

              Comment


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

                Comment


                • #9

                  Comment


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

                    Comment


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

                      Comment


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

                        Comment

                        Working...
                        X