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

[Nykeriab]

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.

[gionna]

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.