Re: long execution time of all requests, drop base

[Peter Brawley]

MySQL is configured to use a max of 9GB, and it's swapping about 4GB, That may be the biggest reason so many queries are slow. Without adding RAM it could use 13GB. The result for the query on table & index sizes is missing---that result would help in estimating what changes would help prevent swapping. Meanwhile for starters consider adding ...

vm.swappiness=0

... on its own line in /etc/sysctl.conf.

key_buffer_size needs to be at least 40MB.

The query cache isn't helping, that's not unusual, which is why it's deprecated in 5.7 and removed in 8.0, Turn it off.

The system is running about 5 queries requiring table scan per sec, and about about 8 slow queries/min. Run Explain on those queries and optimise their index use.

To optimise log writes, adjust innodb_log_file_size such that Uptime_since_flush_status / 60 * innodb_log_file_size / Innodb_os_log_written is somewhere near 30 mins.