I have a wholesale account at a web host and we have numerous sites (mainly SilverStripe) hosted with them. Up until recently, we've had no issues, but for the last couple of weeks, I've been notified of the following error on 4 of the sites.:
"Error: Couldn't run query: SHOW TABLES; Query execution was interrupted" errors
After some discussion with the hosts Tech Support and examination of the MySQL Governor log files, it appears that the sites are being restricted for excessive MySQL reads:
[Mon Nov 19 12:36:41 2012] XXXXXXX LIMIT_ENFORCED period current value, field busy_time value 61/limit 30 restrict level 1 loadavg(1.27 1.20 1.03 1/695 78829) vmstat( 3 0 108 655928 139012 1785304 0 0 139 124 1 0 9 7 81 4 0) slow_queries_timeout = 46912518684544, busy_time = 61, cpu_time = 0, bytes_received = 53330292, bytes_sent = 102199989, binlog_bytes_written = 0, rows_fetched = 126248, rows_updated = 163, table_rows_read = 37105, select_commands = 8752, update_commands = 169, other_commands = 6530, commit_transactions = 0, rollback_transactions = 0, denied_connections = 0, lost_connections = 0, access_denied = 0, empty_queries = 1784
It's a bit weird since the sites have been running for many many months with no issues, but there have been a flurry of these error for last 2 weeks (so I'm suspecting somethings else, eg the host has changed the MySQL limits). Also these sites are doing anything overly complex - very straightforward SS CMS sites.
What I'd like to find out is, is there anything I can do to make these SS sites (a mixture of 2.4 and 3.0) to run a little leaner, apart from caching? What about the following suggestions? Would they make a difference?
- Making cache TTL longer?
- Optimize / Repair MySQL tables (ie via PhpMyAdmin) - is this wise to do? Any side effects?
- Prune SiteTree version history?
Also were there significant performance gains or optimisations between SS2.4.5 and 2.4.7?
Looking forward to hearing your suggestions.