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_sizeto 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.
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
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.
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_sizeto 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_sizeto 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.