1

My server has been getting slow periods due to high CPU spikes. Whenever this happens, I run "top" in command line and I see a MySQL process taking up over 300% CPU. So I inspect my MySQL slow log.

Exactly every 15 minutes non stop, the largest table (item_orders) in my database with almost 1 million records is being queried. Is this caused from my server automatically indexing the table? There is nothing in my codebase that involves doing this.

And if it's showing up in my slow log I'm guessing the table needs to be optimized better? Do Laravel attributes get taken into account when querying a table? I can see attributes that should be optimized better as they call other tables. But would a raw query directly in the database involve this?

The exact query that shows is this: SELECT /*!40001 SQL_NO_CACHE */ * FROM item_orders;

Improve this question

1 Answer 1

Reset to default
1

This is the SELECT you usually see when performing a mysqldump

I have written about this before

This means that someone or something is running a mysqldump backup. If your database instance has replication, the mysqldumps should be executed on the replica DB server rather than the primary DB server.

If you must run mysqldumps on the one DB instance, do it during off-peak hours.

Improve this answer
8
  • Man I totally forgot I have automatic backups happening every 15 minutes. Thank you. For it to show up in my slow log, that still means this particular table should be optimized right?
    – Mike
    Commented Feb 4, 2022 at 19:24
  • @Mike you cannot optimize select * from item_orders that doesn't have a where clause. Well, you could, by throwing hardware at it, I guess.
    – mustaccio
    Commented Feb 4, 2022 at 19:30
  • Any idea why a table dump then would show up in my MySQL slow log then? It's a table of 900,000 rows less than 20 columns. This is all my MySQL slow log shows now when I inspect the file. I want to be able to see which queries are slow so I can optimize
    – Mike
    Commented Feb 4, 2022 at 19:36
  • The reason ??? The global variable long_query_time sets the minimum value a query runs in seconds without being recorded as slow query in the slow query log or incremented in the global status variable Slow_queries. EXAMPLE : Default long_query_time in MySQL 5.7 is 10. If a query takes longer than 10 seconds, the query is written to the slow log when the query finishes.
    – RolandoMySQLDBA
    Commented Feb 4, 2022 at 21:14
  • Any query longer than long_query_time is fair game for being recorded in the slow query log, including mysqldumps. That is why I mentioned it in my answer, especially in the 3rd post I wrote over 10 years ago. :-(
    – RolandoMySQLDBA
    Commented Feb 4, 2022 at 21:25

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.