I have a live MySQL database that suddenly started running extremely slowly for relatively simple queries, e.g. simple counts of tables which were 500k in size. These queries that would usually take a second or two last week, were yesterday taking 2-3 minutes to run.

I wasn't sure what the issue was, so I restarted MySQL (no settings changed) on the server and everything started running very quickly again.

I'm trying to understand what might be the possible reasons this happened, and why simply restarting mysql solved this. Could there have been a memory or caching issue? Are there any steps I could take to prevent this happening again?

CodePudding user response：

I would run EXPLAIN for those queries and check if the optimiser changed table selection order. Just recently I've experienced a similar case when MySQL optimiser has changed the order of joined tables. I could only fix this by using STRAIGHT_JOIN instead of INNER JOIN as it forces optimiser to always read left table first.

CodePudding user response：

MySQL can "suddenly" slow down when these planets converge:

• The data grows, even a little, and
• Table scans are common, and
• The size of such a table is now bigger than will fit in innodb_buffer_pool_size.

This may not be what happened to you.

Use the slowlog to find what is slowest. Then we can discuss that query in more detail -- both "why" and "how to fix".

Note even SELECT COUNT(*) FROM tbl requires a table scan. Let's see SHOW CREATE TABLE and why you are doing the query. There are workarounds.

"Restarting" may fix it today, but not tomorrow (after the table grows a little more).

Please provide RAM size and value of innodb_buffer_pool_size.

If you have run out of disk space, usually no simple thing can get the system to run again.

CodePudding user response：

some of the biggest performance bottlenecks usually boil down to several issues:

1. bind variables
2. query is not well performed
3. database structer is not well defined/normalized
4. proper caching is not in place
5. number of rows in table too large
6. connections are not being pooled
7. Stored procedures for complex computations on database