3

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.

Improve this question
2
  • Additional information request, please. RAM size, # cores, any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: A) SELECT COUNT(*) FROM information_schema.tables; B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; AND very helpful OS information, includes - htop OR top for most active apps, ulimit -a for list of limits, iostat -xm 5 3 for IOPS by device & core/cpu count, for server workload tuning analysis to provide suggestions.
    – Wilson Hauck
    Commented May 25, 2022 at 13:30
  • Flushing the buffer pool (with your high usage) will cause a flurry of READ activity from your tables until the active rows are once again in the buffer pool. You should expect several minutes or an hour until all the heavily used tables have data back into the pool. If you monitor in the same manner and 'paper log' the usage every 5 minutes, you will see the effect of filling the buffer pool back up. If you also track SHOW GLOBAL STATUS LIKE 'innodb%reads%'; each 5 minutes, you will see the slowing of cumulative reads as time goes on which is a good thing.
    – Wilson Hauck
    Commented May 25, 2022 at 14:04

1 Answer 1

Reset to default
2

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.

Improve this answer
1
  • It could "crash" if the Linux out-of-memory killer 86's the mysqld process due to memory pressure. That is, if other processes on the same server are consuming memory, and the OS detects that memory is overallocated, it will pick the process with the largest memory (often this is mysqld) and end it. Ironically, allocating more IBP to mysqld makes it that much more likely to be the process killed.
    – Bill Karwin
    Commented May 24, 2022 at 22:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.