RAM overflow in MYSQL

Question

I have a server with a database in MYSQL 5.7.34 MySQL Community Server (GPL) on weekends has a very high tractability so it has been responding very slowly, I saw the logs and it gave me the following errors:

'% COMPANY_Name%' user: '% User%' host: 'localhost' (Got an error reading communication packets)

This led me to modify the following field in my my.cnf

max_allowed_packet = 512M

Once this was done, the first problem was solved

The second mistake is this

InnoDB: page_cleaner: 1000ms intended loop took 4194ms. The settings might not be optimal. (flushed = 21 and evicted = 102, during the time.)

Investigating it told me to configure the innodb buffer and configure it as follows

innodb_buffer_pool_size = 7G

Also in the investigation I used a query to tell me how much memory is recommended for the buffer size and the result was the following: 86G

So I set my server to 12GB of RAM and assigned it to 7GB but the problem is that it leads to 7 and does not stop going up until the memory collapses.

Here I have several concerns.

1.- The hard disk is 88% busy, I am going to upload 100GB more, this would solve the problem without the need to use innodb_buffer_pool_size?

2.- If I put 32 GB of RAM, the innodb_buffer_pool_size will always make the RAM overflow? If so, what should I implement so that there is no memory overflow and I can have an optimal database?.

Answer 1

When uploading a lot of data, it must, sooner or later, go to disk. So 88% disk busy probably means that it is busy writing stuff.

If there is a lot of reading in that 88%, then perhaps indexes are being updated. This involves bringing index blocks into RAM (in the buffer_pool), updating them, and (eventually) writing the blocks back to disk. Increasing innodb_buffer_pool_size will not necessarily help performance.

Some "load" mechanisms load the table, then [re]build the indexes. This can be faster in some situations.

"Make the RAM overflow" -- If you mean "swap", that is a bad situation, and it involves a lot of I/O. If you find that there is swapping, it is better to lower innodb_buffer_pool_size and other settings that you might have raised. MySQL is designed with the assumption that everything it allocates is in RAM; then it explicitly knows when I/O is needed. With swapping, it comes unexpectedly, often to the worst possible parts of the data.

Buffer_pool = 7G out of 12G of RAM is good. A Rule of Thumb is 70% of RAM. I would consider raising it to 8G or 9G, but _no higher, certainly not 32G!

For further discussion, please describe how the data is being "loaded".

Since you got the error message about innodb_page_cleaner, lower the setting to 200. Also lower innodb_lru_scan_depth to 100 - to conserve CPU cycles every second.

Meanwhile, innodb_buffer_pool_instances should be no more than innodb_buffer_pool_size / 1G.

max_allowed_packet should be big enough for the biggest BLOB/TEXT you have. The space allocated starts small (net_buffer_length) and grows to no more than that max.