GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

MySQL never release memory

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

  • MySQL never release memory

    We are using Percona Server 5.5.33-31.1 on Debian 6 64bit (dedicated server 4-core CPU, 16GB ).
    We are running php based site with small loading. All related software installed on same server, but everything except MySQL do not use even 1GB.

    DB is InnoDB based, less than 100 tables, approximately 300Mb size.

    All is fine and all is fast except the fact that MySQL simple never release memory and slowly growing up to maximum available size, despite all possible limitations in configuration file:
    Code:
    [client]
    port = 3306
    socket = /var/run/mysqld/mysqld.sock
    default-character-set = utf8
    
    [mysqld]
    #
    # * Basic Settings
    #
    pid-file = /var/run/mysqld/mysqld.pid
    socket = /var/run/mysqld/mysqld.sock
    port = 3306
    bind-address = 127.0.0.1
    #skip-external-locking
    character-set-server = utf8
    collation-server = utf8_general_ci
    default-storage-engine = InnoDB
    
    #
    # * MyISAM
    #
    key_buffer_size = 32M
    myisam-recover = FORCE,BACKUP
    
    #
    # * SAFETY
    #
    max_allowed_packet = 16M
    max-connect-errors = 1000000
    innodb_strict_mode = 1
    #sql-mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
    
    #
    # * DATA STORAGE
    #
    #datadir = /var/lib/mysql/
    tmpdir = /var/spool/mysql/
    
    #
    # * CACHES AND LIMITS
    #
    thread_cache_size = 4
    query-cache-type = 0
    query_cache_size = 0
    #query_cache_min_res_unit = 2K
    max_heap_table_size = 64M
    tmp_table_size = 64M
    open-files-limit = 65535
    table-definition-cache = 4096
    table-open-cache = 4096
    max_connections = 200
    
    #
    # * INNODB
    #
    innodb_file_per_table = 1
    innodb_file_format = barracuda
    innodb_flush_method = O_DIRECT
    innodb_buffer_pool_size = 2G
    innodb_read_io_threads = 8
    innodb_thread_concurrency = 0
    innodb_write_io_threads = 16
    innodb-log-files-in-group = 2
    innodb-log-file-size = 128M
    innodb-flush-log-at-trx-commit = 2
    
    #
    # * LOGGING
    #
    log-error = /var/lib/mysql/mysql-error.log
    log-queries-not-using-indexes = 0
    long-query-time = 10
    slow-query-log = 1
    slow-query-log-file = /var/lib/mysql/mysql-slow.log
    We tried a lot with different configuration options, and turn off query-cache also - no any effect.
    We have no idea why it need 10Gb of memory for 300Mb DB with "Historic max_used_connections = 8"

    Thanks in advance for any help...

  • #2
    Can you paste the output of "SHOW ENGINE INNODB STATUS\G" from the time MySQL uses that much of memory? Also "ps aux |grep mysqld".
    Do you have large blob type columns maybe in any tables? Also any reason table cache is 4k when there are less then 100 of them?

    Comment


    • #3
      Thanx a lot for the response

      We restarted MySQL couple of days ago, so it not reached "maximum memory usage" level yet, but it already use much more, than expected. Thus :

      ps aux |grep mysqld :
      Code:
      root      4196  0.0  0.0   3956   672 ?        S    Oct07   0:00 /bin/sh /usr/bin/mysqld_safe
      mysql     4714  0.4 35.9 7663028 5813656 ?     Sl   Oct07  12:12 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/mysql-error.log --open-files-limit=65535 --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
      SHOW ENGINE INNODB STATUS\G :
      Code:
      *************************** 1. row ***************************
        Type: InnoDB
        Name:
      Status:
      =====================================
      131009 20:42:06 INNODB MONITOR OUTPUT
      =====================================
      Per second averages calculated from the last 61 seconds
      -----------------
      BACKGROUND THREAD
      -----------------
      srv_master_thread loops: 116769 1_second, 116756 sleeps, 10894 10_second, 7974 background, 7974 flush
      srv_master_thread log flush and writes: 160562
      ----------
      SEMAPHORES
      ----------
      OS WAIT ARRAY INFO: reservation count 9310, signal count 9332
      Mutex spin waits 16251, rounds 44952, OS waits 809
      RW-shared spins 8010, rounds 239120, OS waits 7963
      RW-excl spins 23, rounds 16181, OS waits 523
      Spin rounds per wait: 2.77 mutex, 29.85 RW-shared, 703.52 RW-excl
      --------
      FILE I/O
      --------
      I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
      I/O thread 1 state: waiting for completed aio requests (log thread)
      I/O thread 2 state: waiting for completed aio requests (read thread)
      I/O thread 3 state: waiting for completed aio requests (read thread)
      I/O thread 4 state: waiting for completed aio requests (read thread)
      I/O thread 5 state: waiting for completed aio requests (read thread)
      I/O thread 6 state: waiting for completed aio requests (read thread)
      I/O thread 7 state: waiting for completed aio requests (read thread)
      I/O thread 8 state: waiting for completed aio requests (read thread)
      I/O thread 9 state: waiting for completed aio requests (read thread)
      I/O thread 10 state: waiting for completed aio requests (write thread)
      I/O thread 11 state: waiting for completed aio requests (write thread)
      I/O thread 12 state: waiting for completed aio requests (write thread)
      I/O thread 13 state: waiting for completed aio requests (write thread)
      I/O thread 14 state: waiting for completed aio requests (write thread)
      I/O thread 15 state: waiting for completed aio requests (write thread)
      I/O thread 16 state: waiting for completed aio requests (write thread)
      I/O thread 17 state: waiting for completed aio requests (write thread)
      I/O thread 18 state: waiting for completed aio requests (write thread)
      I/O thread 19 state: waiting for completed aio requests (write thread)
      I/O thread 20 state: waiting for completed aio requests (write thread)
      I/O thread 21 state: waiting for completed aio requests (write thread)
      I/O thread 22 state: waiting for completed aio requests (write thread)
      I/O thread 23 state: waiting for completed aio requests (write thread)
      I/O thread 24 state: waiting for completed aio requests (write thread)
      I/O thread 25 state: waiting for completed aio requests (write thread)
      Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] ,
       ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
      Pending flushes (fsync) log: 0; buffer pool: 0
      10212 OS file reads, 235493 OS file writes, 70791 OS fsyncs
      0.00 reads/s, 0 avg bytes/read, 2.70 writes/s, 0.70 fsyncs/s
      -------------------------------------
      INSERT BUFFER AND ADAPTIVE HASH INDEX
      -------------------------------------
      Ibuf: size 1, free list len 0, seg size 2, 252 merges
      merged operations:
       insert 268, delete mark 1, delete 0
      discarded operations:
       insert 0, delete mark 0, delete 0
      Hash table size 4425293, node heap has 923 buffer(s)
      298.62 hash searches/s, 66.21 non-hash searches/s
      ---
      LOG
      ---
      Log sequence number 824252591
      Log flushed up to   824252348
      Last checkpoint at  824251628
      Max checkpoint age    216721613
      Checkpoint age target 209949063
      Modified age          963
      Checkpoint age        963
      0 pending log writes, 0 pending chkp writes
      65814 log i/o's done, 0.72 log i/o's/second
      ----------------------
      BUFFER POOL AND MEMORY
      ----------------------
      Total memory allocated 2205155328; in additional pool allocated 0
      Total memory allocated by read views 200
      Internal hash tables (constant factor + variable factor)
          Adaptive hash index 50528992        (35402344 + 15126648)
          Page hash           2213656 (buffer pool 0 only)
          Dictionary cache    9743324         (8851984 + 891340)
          File system         121120  (82672 + 38448)
          Lock system         5314168         (5313416 + 752)
          Recovery system     0       (0 + 0)
      Dictionary memory allocated 891340
      Buffer pool size        131071
      Buffer pool size, bytes 2147467264
      Free buffers            119742
      Database pages          10406
      Old database pages      3822
      Modified db pages       10
      Pending reads 0
      Pending writes: LRU 0, flush list 0, single page 0
      Pages made young 30, not young 0
      0.00 youngs/s, 0.00 non-youngs/s
      Pages read 10114, created 292, written 160504
      0.00 reads/s, 0.00 creates/s, 1.90 writes/s
      Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
      Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
      LRU len: 10406, unzip_LRU len: 0
      I/O sum[0]:cur[0], unzip sum[0]:cur[0]
      --------------
      ROW OPERATIONS
      --------------
      0 queries inside InnoDB, 0 queries in queue
      1 read views open inside InnoDB
      0 transactions active inside InnoDB
      0 out of 1000 descriptors used
      ---OLDEST VIEW---
      Normal read view
      Read view low limit trx n:o 1BBD7AC
      Read view up limit trx id 1BBD7AC
      Read view low limit trx id 1BBD7AC
      Read view individually stored trx ids:
      -----------------
      Main thread process no. 4714, id 140382332544768, state: flushing log
      Number of rows inserted 16030, updated 25429, deleted 2103, read 161400366
      0.07 inserts/s, 0.39 updates/s, 0.00 deletes/s, 505.29 reads/s
      ------------
      TRANSACTIONS
      ------------
      Trx id counter 1BBD7C0
      Purge done for trx's n:o < 1BBD7A5 undo n:o < 0
      History list length 2784
      LIST OF TRANSACTIONS FOR EACH SESSION:
      ---TRANSACTION 0, not started
      MySQL thread id 57179, OS thread handle 0x7fad4e041700, query id 1846813 localhost root
      SHOW ENGINE INNODB STATUS
      ---TRANSACTION 1BB96CA, not started
      MySQL thread id 56150, OS thread handle 0x7fad4e000700, query id 1822848 localhost 127.0.0.1 XXXXXXXXXXXX
      ----------------------------
      END OF INNODB MONITOR OUTPUT
      ============================
      Do you have large blob type columns maybe in any tables?
      PHP application actually is typical CMS system. text and longtext columns are present in several tables. Such column can contain relative big peace of text. maximum - near 50000 characters.

      Also any reason table cache is 4k when there are less then 100 of them?
      No any special reason. Simple, it is one of the trace of our fight

      Comment


      • #4
        Thank you for details. So InnoDB uses 2GB as it was assigned.
        Right now I can suspect this bug occurence: http://bugs.mysql.com/bug.php?id=65074
        Can you check if after you do "FLUSH TABLES" - the mysqld size in memory changes?

        Also, you may be interested in this post, and use some ideas to investigate: http://www.mysqlperformanceblog.com/...-memory-usage/

        Comment


        • #5
          Thanx for the suggestions.

          We already did "FLUSH TABLES" before - it bring no any effect.
          And this nice article we also already read, and try suggestions from it - also found nothing.

          But now, in output from SHOW ENGINE INNODB (INSERT BUFFER AND ADAPTIVE HASH INDEX) I see "Hash Table size: 4425293". Which is more then 4Gb, right? Could it be the reason of the problem?

          Comment


          • #6
            No, it's in bytes, so the hash table size is only 4.2 MB.
            Can you try if lowering the table_open_cache would help? Say try with 2048.
            You may also try to profile memory usage with valgrind, but don't do that under high production load. Here are details: http://www.mysqlperformanceblog.com/...lgrind-massif/

            Comment


            • #7
              Sorry for long delay with answer.

              We actually revised MySQL configuration and change it a lot. It bring us visible perfomance boost, but nothing changed with memory problem table_open_cache at the moment = 250.

              We also upgrade PHP from 5.3 to 5.4, because they said that 5.4 has a lot of fixes in memory allocation area, and we had small hope that it can fix our problem - unfortunatelly it gave no any effect.

              After MySQL allocate all available memmory and swap - It crash and restarted by OS.

              We will try Massif. As far as we get results I will post it here. Thanx for the advice.

              Comment

              Working...
              X