MySQL Forums
Forum List  »  Optimizer & Parser

Re: long execution time of all requests, drop base
Posted by: Peter Brawley
Date: June 12, 2018 12:00PM

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


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

Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

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.