MySQL Forums
Forum List  »  Performance

Re: Mysql use up all my swap space WHY?
Posted by: Peter Brawley
Date: June 05, 2018 01:40PM

400+ queries/sec, a fairly busy system; nearly 20k inserts/sec, that's pretty much the limit.

With the settings you posted, max MySQL RAM use is 30GB: innodb_buffer_pool_size + key_buffer_size + ((read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size) x max_connections). You have 48GB. Assuming the system is dedicated to MySQL, you can grow buffers (esp. innodb_buffer_pool_size?) to fill the unused 18GB.

But it's trying to swap 30GB, so it appears you need to add 16GB or so of RAM in order to get rid of swapping altogether. When yiu do that, performance is bound to improve dramatically.

It's doing 34k next-row table reads/sec, that's way way too high, inefficient ram use---you need to turn on the slow query log to find the responsible queries and optimise them.

The query cache isn't helping much if at all and it's using a bit of RAM, which you're short of. With the cache that's pretty much the general case, which is why it's deprecatedc in 5.7 and removed from 8.0. Turn it off.

innodb_log_file_size * innodb_log_files_in_group / innodb_buffer_pool_size is 0.07, is usually best near 0.5; grow innodb_log_file_size.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Mysql use up all my swap space WHY?
20
June 05, 2018 01:40PM


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.