MySQL Forums
Forum List  »  InnoDB

Re: Table freeze when size exceeds innodb buffer pool size
Posted by: newbie Shai
Date: September 28, 2017 02:57PM

Hi Peter,
Appreciate if you could explain how you derive to this "The main thing: Select_scan > 1/sec, next-row requests > 1200/sec; there are way too many queries requiring table scans. Turn on the slow query log, find those offending queries, run Explain on them, optimise them so they use indexes. "

I saw this Select_scan 907314 how you decide it >1/sec ? I have actually turn the slow query log and add the necessary index now I dont find many logs in the slow query log.

Is quite hard to upgrade cause its a live system.

InnoDB log files are being rotated every 6.5 hours, ~30 min is usually preferable, try shrinking the logs (How you derive on this? What size is normally best )

The InnoDB buffer pool appears to be running 50% free, so you could try shrinking it too.(How do you check its running 50%, if I shrink will I need to expand later when the table size grows?)

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.