GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

MySQL memory issue

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

  • MySQL memory issue

    Hi Team,

    One of my MySQL server process is using more memory (around 75% resident memory) from RAM.


    PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
    7622 mysql 15 0 32.8g 27g 5510 S 10.0 76.6 55493:27 mysqld


    Server configuration
    ---------------------
    36GB RAM, 64 bit Linux OS, 16 CPU.

    MySQL configuration and status
    ------------------------------
    innodb_buffer_pool_size = 6 GB
    innodb_log_buffer_size=8MB
    join_buffer_size=126KB
    key_buffer_size=8MB
    read_buffer_size=126KB
    read_rnd_buffer_size=256KB
    sort_buffer_size= 2MB
    Connections at a time = 500 (approx)
    Query per Second = 4500 (approx)
    Data size = 32 GB (approx)

    Most of the tables are in InnoDB engine and the threads are accessing InnoDB tables. When I was looking Innodb status there is lack of free pages in buffer pool.

    Is that the reason for using more memory from for MySQL Daemon ?
    If I re-size innodb_buffer_pool_size to 16GB, do I get performance improvement ?



    ----
    ----
    Total memory allocated 6593445888; in additional pool allocated 0
    Dictionary memory allocated 2092044
    Buffer pool size 393215
    Free buffers 1
    Database pages 388677
    Old database pages 143456
    Modified db pages 42657
    ----
    ----



    Please suggest how to resolve this memory issue..

    Let me know if there is any extra info required.


    regards
    ramesh

  • #2
    Hi Ramesh,

    I don't see exactly a memory problem there. InnoDB is using all the available innodb buffer pool and that's good. buffer pool is used for caching pages, adaptative hash, change buffer and so on. so, if you give mysql X gb of ram it will try to use it all, that variable and the innodb log file size are the two most important parameters for innodb. The usual advice for the buffer pool is to use 80% of the memory, but only if the server is dedicated.

    Increasing the buffer pool should increase the performance, but you might see a much larger use of memory on the top output but it should be okay. The most important thing is, it should not swap.

    If you are searching reason to use more memory by MySQL it can be because of other sessions variables as you have 500 connections at a time. sort_buffer_size= 2MB and Query per Second = 4500. imagine that only 50% of those queries needs the sort buffer. that's 5GB more of ram needed.

    So if possible provide output of pt-mysql-summary utility. http://www.percona.com/doc/percona-toolkit/2.1/pt-mysql-summ ary.html. It can help us to troubleshoot this issue.

    Comment


    • #3
      Hi Nil,

      Thanks for the quick update on this issue.

      PFA pt-mysql-summary output.

      The maximum possible mysql memory usage in this server is 11.4G.

      Calculation as follows

      # Per-thread memory

      per_thread_buffers = read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size

      max_total_per_thread_buffers = per_thread_buffers * Max_used_connections


      # Server-wide memory

      server_buffers = key_buffer_size + tmp_table_size + innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + query_cache_size

      # Global memory

      max_used_memory = server_buffers + max_total_per_thread_buffers

      but in TOP mysqld RES is 27G

      PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
      7622 mysql 15 0 32.8g 27g 5510 S 10.0 76.6 55493:27 mysqld


      I am bit confused, how RES (Resident memory) is showing 27g in TOP whereas mysql total used memory is 12GB (around)


      --
      regards
      Ramesh

      Comment


      • #4
        Hi Nil,

        PFA INNODB engine status also.

        regards
        Ramesh

        Comment


        • #5
          Hi Ramesh,

          It looks like there is some kind of memory leak here. The per session buffers couldn't be taking so much more memory too. Are you running ORDER BY and GROUP BY queries that do not use indexes and need to create implicit temp tables on that server? Can you try to run "FLUSH TABLES" during non-peak hours to see if that reduces memory footprint?

          Comment


          • #6
            Hi Nil,

            As you said I have flushed the table and it got released 2G memory.


            [bash]# toptop - 05:46:43 up 132 days, 19:48, 2 users, load average: 1.06, 0.98, 0.97Tasks: 332 total, 1 running, 331 sleeping, 0 stopped, 0 zombieCpu(s): 4.5%us, 0.1%sy, 0.0%ni, 94.1%id, 1.2%wa, 0.0%hi, 0.1%si, 0.0%stMem: 37037520k total, 36926040k used, 111480k free, 29996k buffersSwap: 4194288k total, 4194288k used, 0k free, 5692304k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 7352 mysql 15 0 38.6g 29g 4748 S 73.7 82.8 56184:45 mysqld[root@mbsdb-m34 logs]#mysql> \s--------------mysql Ver 14.14 Distrib 5.1.31, for unknown-linux-gnu (x86_64) using readline 5.2Connection id: 875842------Uptime: 132 days 19 hours 42 min 48 secThreads: 506 Questions: 41354707469 Slow queries: 29295 Opens: 1831624 Flush tables: 3 Open tables: 526 Queries per second avg: 3603.654--------------mysql> flush tables;Query OK, 0 rows affected (5.40 sec)mysql> \s--------------mysql Ver 14.14 Distrib 5.1.31, for unknown-linux-gnu (x86_64) using readline 5.2Connection id: 875842-------Uptime: 132 days 19 hours 43 min 56 secThreads: 506 Questions: 41354827599 Slow queries: 29356 Opens: 1831756 Flush tables: 4 Open tables: 84 Queries per second avg: 3603.644--------------mysql> \qBye[bash]# toptop - 05:49:29 up 132 days, 19:51, 2 users, load average: 0.53, 0.75, 0.88Tasks: 332 total, 1 running, 331 sleeping, 0 stopped, 0 zombieCpu(s): 1.8%us, 0.1%sy, 0.0%ni, 97.7%id, 0.3%wa, 0.0%hi, 0.1%si, 0.0%stMem: 37037520k total, 34618152k used, 2419368k free, 30208k buffersSwap: 4194288k total, 3334484k used, 859804k free, 5710452k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 7352 mysql 15 0 35.8g 27g 4872 S 29.6 76.5 56185:55 mysqld




            Apart from that we are using stored procedures (large one) frequently by all the threads(500 threads at a time). Will this effect the memory usage from outside the total mysql memory allocation ?

            Comment


            • #7
              Hi Ramesh,

              Stored procedures are after all bunch of queries so it can affect to the memory usage. (It depends on queries)
              If you want to check memory utilization, I guess below link will help you to figure out.

              http://www.mysqlperformanceblog.com/2013/01/09/profiling-mys ql-memory-usage-with-valgrind-massif/

              Comment

              Working...
              X