GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

mysql memory consumption, memory leak ?

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

  • mysql memory consumption, memory leak ?

    Hi,

    I'm running mysql 5.0.32 (debian Etch for what matters) on x86_64.
    The server has 4GB or RAM, using 90% of innodb tables, and running only mysql.

    Mysql config is:
    key_buffer = 64M
    max_allowed_packet = 16M
    thread_stack = 128K
    table_cache = 512
    sort_buffer_size = 2M
    join_buffer_size = 1M
    read_buffer_size = 1M
    read_rnd_buffer_size = 1M
    thread_cache = 8
    query_cache_size = 48M
    tmp_table_size = 48M
    query_cache_limit = 2M
    myisam_sort_buffer_size = 64M
    myisam_max_sort_file_size = 10G
    myisam_max_extra_sort_file_size = 10G
    innodb_buffer_pool_size = 2800M
    innodb_file_per_table = true
    innodb_data_file_path= ibdata1:2G;ibdata2:1G:autoextend
    innodb_data_home_dir = /data/mysql
    innodb_flush_method = O_DIRECT
    innodb_log_buffer_size = 16M
    innodb_log_file_size = 512M
    innodb_log_group_home_dir = /data/mysql
    innodb_additional_mem_pool_size=20M
    innodb_thread_concurrency = 12
    innodb_flush_log_at_trx_commit = 1
    sync_binlog=1
    innodb_support_xa=0

    the rest is default.

    Now to the issue:
    Memory usage is constantly (but slowly) growing, and now the server sometimes swaps out (and in) lots of memory, especially when there is high traffic.

    Normally it should consume about: 2800+20+16*2+(0.128+2+1+1+1)*nb_of_threads

    The max number of threads is the defautl 100, which gives: 3364MB (3.4GB), so well beyond the RAM limit.

    Moreover, in normal usage, there is only 10 to 20 threads connected, but it seems that buffers allocated by old running threads (like sort_buffer and such) are still allocated and not freed.

    I don't think it is normal behavior.
    Does anybody experienced the same issue ?
    Is there something I didn't get about per threads buffers ?
    Should I lower the innodb_buffer_pool_size ?

    Thanks
    Brice

  • #2
    I understand this is a dedicated MySQL database server you've set up, but it's no suprise your OS is resorting to using swap space when MySQLD itself is consuming 3.4GB of RAM.

    Comment


    • #3
      Sorry my previous post was not that clear.

      The problem is not the OS is using swap, it is more that mysql is using more than 4.5GB (at the moment, still slowly growing from time to time) where it should only use as per my calculation only 3.4GB...

      This is no surprise the OS is swapping mysql out in this case (even with 1% swappinness).

      I took a pmap snapshot a month ago, and while analysing the differences since now, shows that there are lots of 1016k (around 1MB) of anonymous memory allocated.

      Is there a way to track mysql allocations (kind of show mutex but with buffers ?).

      Does it mean anything to anyone ?

      Comment


      • #4
        If one of the 16M that you have there is the max_allowed_packet it should be a per client basis.
        But at the same time it should never stay so big. It should always go back to net_buffer_length as soon as possible.

        But no it is very hard to find out what MySQL is doing internally.
        I myself have a lot of wishes with regard of performance tuning that I would like to be able to see internally in mysql. So you are not alone.

        Comment


        • #5
          I've seen the first thread is quite dated, but let's try.

          I'm experiencing the same problem with Debian Etch and mysql5.

          I've seen some improvement, reducing the expire_log_days parameter for the binary logging. But even if the expire log is of 2 days only, the memory consumption is anyway too large!

          8Gb ram machine 2 dual core CPU.

          InnoDB pool size 4Gb as suggested by mysql-huge.cnf

          After some days the machine is swapping!!! mad:

          Let me know if there is something else to investigate...

          Hope this helps

          Diego

          Comment


          • #6
            diegobelotti wrote on Mon, 17 September 2007 10:29

            I've seen the first thread is quite dated, but let's try.

            I'm experiencing the same problem with Debian Etch and mysql5.


            Yes, I do still have the same issue, and have to restart the mysql daemon every 2 to 3 weeks.
            I tried to no avail using version compiled by MySQL but it didn't change anything.

            I also tried to "see" what was giving this by trying to reproduce the workload on another machine but I couldn't isolate the culprit.

            I tried to use a several memleak finder (valgrind, Google perf tools, etc...) on a test machine but I never could reproduce the issue I'm seeing under real load.

            The server:
            • is serving request for a webserver running PHP, with mysql extension. The connections are not persistent.
            • is serving requests for a few online games running java
            • has a slave for backup purposes (hence binlog)
            • is backup with LVM and mysqldump
            • uses almost only innodb
            • is monitored every 5 minutes by a perl script running DBI, issueing some "show variables like"
            • has 1 Archive tableis using triggers to populate this archive table

            What is doing yours ?


            diegobelotti wrote on Mon, 17 September 2007 10:29

            I've seen some improvement, reducing the expire_log_days parameter for the binary logging. But even if the expire log is of 2 days only, the memory consumption is anyway too large!


            Did you try to completely remove the binlog, does it help ?
            I never tried myself but that's something I didn't think about.


            diegobelotti wrote on Mon, 17 September 2007 10:29

            8Gb ram machine 2 dual core CPU.

            InnoDB pool size 4Gb as suggested by mysql-huge.cnf

            After some days the machine is swapping!!! mad:


            I've about the same config (4GB RAM, innodb pool at 3GB).

            I open a MySQL bug (http://bugs.mysql.com/bug.php?id=27732).

            I hope that helps,

            Comment


            • #7
              I can't stop binlog, needed for replication.

              About your question: the server is serving two apache2 server with php mysql module.

              Calls are frequent and avg load of mysql server is 0.30...

              I'll let you know.

              thanks

              Comment


              • #8
                You've provided a lot of information but not not the general one.

                How much RSS and VSZ do you see for MySQL in PS output
                What memory allocation Innodb shows in "SHOW INNODB STATUS" ?

                We need to see if it is MySQL takes too much memory or OS just thinks it is better to swap things out to accomodate more cache.

                Comment


                • #9
                  Hi Peter,

                  the problem is not the swap itself, but the memory consumption.

                  After a normal boot up, used memory start regularly to grow up to the total size and then start to swap.

                  It seems that memory is not freed after the usage. OS memory, without mysqld process is stable around a value of 300/ 600 MB on a 8Gb ram machine in my case.

                  This is the result for innodb status:

                  mysql> SHOW INNODB STATUS;

                  [...]

                  SEMAPHORES
                  ----------
                  OS WAIT ARRAY INFO: reservation count 183550, signal count 140961
                  Mutex spin waits 0, rounds 1666011, OS waits 15168
                  RW-shared spins 28223, OS waits 22021; RW-excl spins 151516, OS waits 145000

                  [...]

                  ----------------------
                  BUFFER POOL AND MEMORY
                  ----------------------
                  Total memory allocated 4805805016; in additional pool allocated 14203648
                  Buffer pool size 262144
                  Free buffers 172716
                  Database pages 82243
                  Modified db pages 261
                  Pending reads 0
                  Pending writes: LRU 0, flush list 0, single page 0
                  Pages read 81583, created 660, written 293546
                  0.00 reads/s, 0.03 creates/s, 11.44 writes/s
                  Buffer pool hit rate 1000 / 1000
                  [...]





                  and these the main settings:

                  skip-external-locking
                  key_buffer = 384M
                  max_allowed_packet = 16M
                  table_cache = 1280
                  sort_buffer_size = 4M
                  read_buffer_size = 4M
                  read_rnd_buffer_size = 8M
                  myisam_sort_buffer_size = 64M
                  thread_cache_size = 8
                  query_cache_size = 32M
                  query_cache_type = 1
                  thread_concurrency = 8

                  expire_logs_days = 5
                  max_binlog_size = 104857600
                  binlog-ignore-db=mysql
                  binlog-ignore-db=information_schema

                  skip-bdb

                  innodb_data_home_dir = /var/lib/mysql/
                  innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
                  innodb_log_group_home_dir = /var/lib/mysql/
                  innodb_log_arch_dir = /var/lib/mysql/
                  innodb_buffer_pool_size = 4G
                  innodb_additional_mem_pool_size = 20M
                  innodb_log_file_size = 100M
                  innodb_log_buffer_size = 8M
                  innodb_flush_log_at_trx_commit = 0
                  innodb_lock_wait_timeout = 50


                  [isamchk]
                  key_buffer = 16M

                  !includedir /etc/mysql/conf.d/

                  Comment


                  • #10
                    Again you have not provided me with PS output I've asked )

                    What you're describing is normal. When MySQL starts even if it allocates memory that memory is not really "commited" until it is first really used - which happens over time as buffer pool key buffer etc are populated.

                    With your current settings MySQL should grow to some 6GB and when stop growing

                    Comment


                    • #11
                      Hi Peter,

                      Peter wrote on Tue, 18 September 2007 10:15

                      You've provided a lot of information but not not the general one.

                      How much RSS and VSZ do you see for MySQL in PS output
                      What memory allocation Innodb shows in "SHOW INNODB STATUS" ?

                      We need to see if it is MySQL takes too much memory or OS just thinks it is better to swap things out to accomodate more cache.



                      In my case (I'm the OP of the thread), I see mysql (almost the only process of the server) consumming more VSZ every day, up to the point where the machine is swapping when there is not enough physical memory.

                      When I restart mysql (every 15 days or so), the server returns back to normal (not swapping) and mysql start to grow (

                      Some figures to back my assertions:
                      Right now:

                      USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMANDmysql 6275 16.8 88.9 3683212 3604356 ? SLl Sep10 2028:22 /usr/sbin/mysqld --defaults-file=/etc/mysql/my.cnf --basedir=/usr --datadir=/data/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --open-files-limit=8192 --port=3306 --socket=/var/run/mysqld/mysqld.sock


                      Two days ago VSZ was at 3681916.
                      I restarted the server about a week ago so it doesn't swap yet.

                      I tried every method I know to find memleaks on a development machine (including valgrind and Google Perf tools), but I couldn't reproduce the problem I'm seeing in production.

                      The OS right now is happy:

                      total used free shared buffers cachedMem: 4052420 3918900 133520 0 46912 105828-/+ buffers/cache: 3766160 286260Swap: 11863960 215680 11648280

                      and vmstat output show no swap yet

                      procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu---- r b swpd free buff cache si so bi bo in cs us sy id wa3 0 215680 128872 48424 108412 0 0 8 28 334 857 1 0 98 1 1 0 215680 128932 48472 108452 0 0 4 124 662 1263 1 0 99 0 3 0 215680 128932 48496 108428 0 0 0 88 358 910 0 0 100 0 1 0 215680 128932 48512 108464 0 0 4 40 358 899 0 0 100 0 3 0 215680 128932 48572 108404 0 0 0 172 359 926 0 0 100 0


                      What else to check??

                      Comment


                      • #12
                        As you see in your case MySQL VSZ size remains more or less static so it is not memory leak.

                        It is however likely to bee too much - MySQL uses some 90% of memory, which is what can cause OS to swap.

                        You can either reduce MySQL buffers or use other methods, such as O_DIRECT to decrease IO pressure or configure OS to be less agressive with swapping.

                        Comment


                        • #13
                          Yes you are right! I forgot something

                          Here you are (the master has just been rebooted so I take the data from the slave from a "top" screen):

                          Mem: 8180112k total, 7697260k used, 482852k free, 170568k buffersSwap: 2963832k total, 48k used, 2963784k free, 4734008k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 2102 mysql 15 0 5452m 2.5g 6324 S 9 31.6 342:07.98 mysqld 1 root 15 0 6120 684 564 S 0 0.0 0:02.26 init 2 root RT 0 0 0 0 S 0 0.0 0:00.03 migration/0 3 root 34 19 0 0 0 S 0 0.0 0:00.00 ksoftirqd/0



                          and still it seems to grow slowly...

                          Comment


                          • #14
                            Peter wrote on Tue, 18 September 2007 12:41

                            As you see in your case MySQL VSZ size remains more or less static so it is not memory leak.


                            Definitely not.
                            VSZ is quietly increasing from days to days. It is now at 3752116 (was 3683212 yesterday when I posted).

                            I just added a script to record VSZ, I'll post the result in about a week.

                            Peter wrote on Tue, 18 September 2007 12:41

                            It is however likely to bee too much - MySQL uses some 90% of memory, which is what can cause OS to swap.


                            The server is not swapping (vmstat si and so are 0). Mysql is not swapped (VSZ is almost equals to RSS).
                            The OS swapped pages of unused process (Dell OMSA for instance) a while ago, but as long as it is unused the server is not swapping.
                            The MySQL configuration has been carefully crafted to run at 85% of physical RAM.

                            Peter wrote on Tue, 18 September 2007 12:41

                            You can either reduce MySQL buffers or use other methods, such as O_DIRECT to decrease IO pressure or configure OS to be less agressive with swapping.

                            I'm already using O_DIRECT (as long as all your other advice in your slides or blog posts )).
                            /proc/sys/vm/swappiness is at 2 to reduce OS cache to the minimum.

                            My problem is not that the box is swapping, it is that Mysql is using more and more memory (unlimitied) up to the point the box starts to swap.
                            In a few previous run, I saw mysqld's VSZ of more than 5GB!

                            Also I see a pattern between my problem and diegobelotti's one. We are both running debian (not the same version), and I'm not aware of any other report of the same issue (if that was a known issue I think lots of other people would have complained, right?).

                            Anyway, thanks for your help, it is appreciated )
                            Keep the good work with this site

                            Brice

                            Comment


                            • #15
                              diegobelotti wrote on Wed, 19 September 2007 03:59

                              Yes you are right! I forgot something

                              Here you are (the master has just been rebooted so I take the data from the slave from a "top" screen):

                              Mem: 8180112k total, 7697260k used, 482852k free, 170568k buffersSwap: 2963832k total, 48k used, 2963784k free, 4734008k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 2102 mysql 15 0 5452m 2.5g 6324 S 9 31.6 342:07.98 mysqld 1 root 15 0 6120 684 564 S 0 0.0 0:02.26 init 2 root RT 0 0 0 0 S 0 0.0 0:00.03 migration/0 3 root 34 19 0 0 0 S 0 0.0 0:00.00 ksoftirqd/0



                              and still it seems to grow slowly...


                              Beside the issue we both have, I suggest you decrease /proc/sys/vm/swappiness. You have more than 4GB of pagecache, and I'm not sure that's what you want (except if you have large MyISAM tables).

                              Comment

                              Working...
                              X