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

  • This question is about database operations, not about programming, therefore it is off topic here on SO. DBA sister site of SO offers help with such questions.
    – Shadow
    Sep 1 '21 at 15:49
  • See if this Q&A helps.
    – mustaccio
    Sep 1 '21 at 17:06
  • See that answer too.
    – Kondybas
    Sep 1 '21 at 17:43
  • Your practical experience indicates 6G of innodb_buffer_pool_size gives you stability. Use 6G. If you are loading lots of data, consider innodb_change_buffer_max_size=50 to improve INSERT rows per second. It takes a while for this to be effective. Monitor with SHOW FULL PROCESSLIST; to determine when MySQL has completed this adjustment to the change buffer size capacity. Next load should be at higher rate per second. Secondary verification, SELECT @@innodb_change_buffer_max_size; You may want to keep at 50% if you load lots of data. Sep 4 '21 at 16:25
  • @WilsonHauck I think I do not understand, when I activate innodb_buffer_pool_size the RAM begins to fill until there is no space left and the server fails, at the moment from Friday we uploaded to 128GB of RAM to the server and innodb_buffer_pool_size = 96G so far it is in the 33GB occupied but not stop uploading Sep 6 '21 at 16:37

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.

  • When I refer to RAM overflow (12GB has my server) when I activate innodb_buffer_pool_size = 7G it reaches 7G and after that it continues to fill up until it occupies all the RAM memory Sep 1 '21 at 19:26
  • @SantiagoTrujilloTerán - The buffer_pool won't go beyond 7G. What are you seeing in ps or top or task manager for the size of MySQL? I would expect it to grow to about 8 or 9G, then stop growing.
    – Rick James
    Sep 1 '21 at 19:34
  • I see it in two ways, I use MobaXterm, it gives me RAM memory usage parameters and I use the htop where it gives me the same information, it should be noted that this happens only when I activate the innodb_buffer_pool_size = 7G Sep 1 '21 at 19:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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