Slow query at one DB, but fast at his copy

Question

We have a Laravel application (version 5.4.18) which is connected to MySQL (5.6.38) database (300k rows totally), MyISAM type. And we have api-response like Model::with('anothermodel')->paginate(25), and at some point the execution time of this query has reached 18 seconds, which is very big value. I created a sandbox with a copy of the live laravel environment that uses a copy of the exaclty same database (at the same server), and now this api-response is executed in 2,5 seconds. If we trying to connect sandbox laravel to the live DB, then api execution is 18s again. Please check the image.

So if we assume that the problem is in the DB table of live website, but if we try to measure this query with microtime, then it will show that time required to generate this query and grab the data from database is just 0.7 sec.

So if we assume that problem is in API routes or in the Laravel code, but exaclty same code is executed without any problem if I just copy it into the subdirectory (as a sandbox).

Any ideas?

Also some server info: PHP 7.0.29 at Linux server with 32gb RAM.

Answer 1

I have 3 solutions for you:

1) check & optimize & repair the table using this command:

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

2) if problem not solved check your query execution plan using monitoring apps like Newrelic

3) Sometimes it's not about specific table it's about database corruption, try to drop and create a new one with old data. How to recover/recreate mysql's default 'mysql' database

Answer 2

Have you tried to rebuild indexes on live DB?

In MySQL this could be done with REPAIR <table> QUICK command (quick repair - means only repair indexes - rebuild them).

(Please backup DB before applying... There is almost no chance to broke DB by this command, but...)

---

Some additional explanation:

Mysql mostly using self-balancing B+ trees for indexes, they are trying to be as most flat as possible to provide good search times.

But in some cases (often it is related to incremental data insertions into DATETIME column e.g. createdAt with index) MySQL B+ tree implementation unable to handle such type of load. Generated tree delivers performance which constantly decrease over a time(days, weeks). Such indexes must be rebuild...

If this approach will help you - rebuild indexes once per x days (Once per week worked for me last time)

Answer 3

There can be a couple of issue, One is rebuilding indexes, which is addressed and do take backup before that,

The other is the server you are running, I mean Apache or NGINX.

See whats running for apache

ps auxf

Monitor apache by turning on server-status

apachectl fullstatus

A good read here on Apache performance

Answer 4

You need to repair your database.

Why you compact and repair a database

This overview explains how using the Compact and Repair Database command can help prevent and correct the following problems that sometimes affect a database: files growing larger with use and files becoming corrupted.

Database files grow with use As you add and update data and change its design, a database file becomes larger. Some of this growth comes from new data, but some comes from other sources:

• Access creates temporary, hidden objects to accomplish various tasks. Sometimes, these temporary objects remain in your database after Access no longer needs them.

  When you delete a database object, the disk space that the object occupied is not automatically reclaimed — the database file still uses that disk space, even though the object is deleted.

As your database file fills up with the remains of temporary and deleted objects, its performance can degrade. Objects may open more slowly, queries may take longer than normal to run, and typical operations generally seem to take longer.

Reference :

https://support.office.com/en-us/article/Compact-and-repair-a-database-6ee60f16-aed0-40ac-bf22-85fa9f4005b2

Answer 5

Not enough information to give a detailed diagnosis. Personally I would start to check the memory and cpu occupation on the server when the query is executing slow, htop is a good tool in case you don't have access to any other licensed software for server monitoring just to be sure that not only the machine are equal but also the workload. Many times such kind of issues are depending from lock or contention of resources, rather than from the specific query. Eventually isolate a sql sequence and get the explain on both the server. At this point you should be able to reproduce the issue by woking with the same client, i.e. SQLYOG and be able to evaluates the changes on the explain and performance after rebuilding indexes or tables. Just to do it and hope that it will be fine is not really useful if the purpose is to resolve the issue and produce documentation on how to fix it again in the future. Yes, usually degradation with performance due to an index which need to be rebuilt are happening again and again when a careful db maintenance program is not in place.