All we need is an easy explanation of the problem, so here it is.
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.
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.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂