InnoDB buffer getting higher every day. How to flush the buffer pool?



  • I'm managing a db server for a lot of people connecting to it and one of the tools we are using is not well optimized so it uses a lot of JOIN queries without indexes.

    The problem is that lately we've being experimenting some crashes because the InnoDB buffer pool reached 90~95%, so I had to check if MySQL needed more pool size so I ran:

    SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
    (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
    FROM information_schema.tables WHERE engine='InnoDB') A;
    

    and it returned me:

    +-------+
    | RIBPS |
    +-------+
    |    33 |
    +-------+
    1 row in set, 48 warnings (0.19 sec)
    

    so I need innodb_buffer_pool_size to be 33G and this has to be around a 60~70% of my total RAM. Ok, it seems a pretty high value but, I have no problem with this so I set the RAM in that VM to 64G.

    QUESTION

    I'm seeing and checking every day the InnoDB buffer usage value and now, after 16 days running it sits at 39%, but it's still getting higher everyday and it won't decrease. It will reach the >90%? It will crash again?

    These are some of the variables I've set in the mysql.cnf file:

    innodb_buffer_pool_instances = 44
    innodb_buffer_pool_size     = 42G
    innodb_flush_method         = O_DIRECT
    innodb_log_file_size        = 5G
    innodb_page_cleaners        = 4
    innodb_purge_threads        = 4
    innodb_read_io_threads      = 64
    innodb_thread_concurrency   = 0
    innodb_write_io_threads     = 64
    max_connections             = 512
    open_files_limit            = 262144
    table_open_cache            = 131072
    innodb_io_capacity          = 1900
    thread_cache_size           = 100
    read_rnd_buffer_size        = 128K
    read_buffer_size            = 128K
    

    I need to know which variables should I adjust or tune to keep it stable and if it's necessary to flush or clear that buffer pool.



  • That's normal.

    The buffer_pool is a "cache". Blocks (16KB each) are loaded as needed from disk. Modified blocks are eventually written back to disk and left in the buffer_pool. A typical application will gradually touch all the blocks; what you see is the buffer_pool growing over time until it hits some limit.

    But, there are other things in the buffer_pool, so it would be nice for it to be about 50% bigger than the data. Hence, it will continue growing pass your 33GB of data until somewhere around 50GB.

    You should set innodb_buffer_pool_size to about 70% of available RAM. That is, after accounting for other products that run on the same machine.

    If you have a lot more data than buffer_pool; it will still work, but with extra I/O.

    Crashes I don't see any reason for it crashing.

    Do you have any swap space allocated for the OS? If not, then exceeding 100% of RAM would cause a crash. With some swap space, things would get very slow. In either of these cases, low innodb_buffer_pool_size to avoid any swapping. (It's better to allow I/O for caching than for swapping.)

    The buffer_pool deliberately leaves about 5% "headroom". When it gets close, it will bump things out of the cache -- not crash.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2