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;

Mike
  • 111
  • 1

1 Answers1

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.

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • 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 Feb 04 '22 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 Feb 04 '22 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 Feb 04 '22 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 Feb 04 '22 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 Feb 04 '22 at 21:25
  • Ok thanks. What's confusing to me though is before I started these 15 minute database backups, the MySQL slow log would show me various slow queries due to poor code which I would work to optimize over time in my application. Now when I check, all I see is this table dump every 15 minutes and nothing else. Any reason why it would stop recording and displaying everything else? – Mike Feb 04 '22 at 23:33
  • That means everything else is running less that whatever long_query_time is. – RolandoMySQLDBA Feb 05 '22 at 00:47
  • @Mike - Taking a full dump every 15 minutes is excessive. Rethink your backup policy. Then we can talk about having a useful slowlog. – Rick James Feb 05 '22 at 23:11