0

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

Santiago Trujillo Terán
  • 1
  • 1
  • 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 01 '21 at 15:49
  • See if this Q&A helps. – mustaccio Sep 01 '21 at 17:06
  • See that answer too. – Kondybas Sep 01 '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. – Wilson Hauck Sep 04 '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 – Santiago Trujillo Terán Sep 06 '21 at 16:37
  • Additional information request. Last 400 lines of your error log. 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: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; AND Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. – Wilson Hauck Sep 06 '21 at 20:55

1 Answers1

0

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.

Wilson Hauck
  • 1,748
  • 11
  • 13
Rick James
  • 78,038
  • 5
  • 47
  • 113
  • 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 – Santiago Trujillo Terán Sep 01 '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 01 '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 – Santiago Trujillo Terán Sep 01 '21 at 19:36