0

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?

Improve this question
2
  • check whether you have enough storage space left, check any active transaction exists. this will happen when you forget to commit transactions
    – sojin
    Commented Oct 22, 2021 at 13:06
  • or run it again, to see if it quicker by "warm" Server. This can be a index is missing (not created)
    – Bernd Buffen
    Commented Oct 22, 2021 at 13:09

3 Answers 3

Reset to default
1

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.

Improve this answer
1
  • Thanks Rick. The innodb_buffer_pool_size seemed to be it — my tables were around 100-200mb in size, and my innodb_buffer_pool_size flag was set to 256mb. I increased this flag to 1.5gb, and there is a clear performance improvement. Thank you.
    – Rohan
    Commented Nov 2, 2021 at 12:08
0

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.

Improve this answer
0

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
Improve this answer
2
  • 1
    Please share more details such that others can learn from your answer. How does your list of possible bottlenecks (for which you should list some credible sources) relate to the given question? "bind variables" - what does that mean? "number of rows in table too large" - 500k rows are not pretty large
    – Nico Haase
    Commented Oct 22, 2021 at 15:32
  • 2
    Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community
    Commented Oct 22, 2021 at 16:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.