Re: Mysql use up all my swap space WHY?

[Peter Brawley]

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.