Re: Mysql memory usage increasing until it hits the RAM limit and starts using swap
Posted by: Peter Brawley
Date: December 11, 2018 04:18PM

Commonly reported with 5.7. A fix to 5.7.13 for this bug did not stop such reports, eg ...

https://bugs.mysql.com/bug.php?id=84003 ...

where a user said "The problem in 5.7 is this: once MySQL consumes memory it *never* releases it back to the OS!". In that case doubling RAM seemed to fix it, but this bug page ...

https://bugs.mysql.com/bug.php?id=83047

... was created before 5.7.13 and revived in 2017, a year later, and it reports the same problem, so keep reading, it was necessary to change the memory allocator!

I ran your numbers through a mem analysis script. It said ...

Queries needing full table scans/sec  2.1714   Find & optimise those queries

InnoDB log file thrashing             0.0179   Best is near 0.5; grow innodb_log_file_size if 
                                               well under 0.5, shrink if much larger
                                               
Est. best InnoDB log file size        736MB    Average hour of writes rounded up to 32MB boundary

MyISAM key buffer size                32MB     Pre-8.0 MyISAM system tables need about 40MB

MyISAM cache miss ratio               311.88   If > 10, grow key_buffer_size

MyISAM key read rate                  0.03     Should be high when the buffer is full

Temp tables created/sec               86.41    > 20 is too much, find the queries responsible 
                                               and optimise them

Queries/sec                           2265.52  Very busy system, you may need better hardware

Inserts/sec                           7496.68 

Next table row requests/sec           30993    Some queries need optimising esp. with covering indexes

Max MySQL RAM use                     114GB    Must be less than RAM available to MySQL

ie it's a very busy server, you're using 90% of available RAM, you need more RAM, some queries need optimising, and you may need faster hardware.



Edited 1 time(s). Last edit at 12/11/2018 04:19PM by Peter Brawley.

Options: ReplyQuote




Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.