Slow query in terminable middlware

I'm trying to troubleshoot a bug in my Laravel 6.x app. I have a terminable middleware that runs the following query:

 $deviceHistory = DeviceHistory::where('client_id', $device->client_id)
    ->where('device_id', $device->id)
    ->whereDate('created_at', '>=', Carbon::now()->subSeconds(10))
    ->withTrashed()
    ->latest()
    ->first();

Which resolves to this Mysql query:

SELECT * FROM `device_histories` WHERE `client_id` = ? AND `device_id` = ? AND DATE ( `created_at` ) >= ? ORDER BY `created_at` DESC LIMIT ?

The table has 8,466,489 records. Most of the time the query runs fine, but in forge I can see that this query sometimes runs for 14 seconds, but I've had days where it is running for 2 hours and finally crashes the server. I didn't write the code, I'm just trying to debug the error.

If a mysql query runs in a terminable middleware is there anyway to detect that it hasn't finished within a specific time, kill it, and log the error? Or, any others where I should be looking for what it causing the error. The query is so basic and runs quickly 99% of the time. And, this is the only offending query. The rest of the app runs as expected.

LaryAI

3 years ago

Level 58

The slow query may be caused by the large number of records in the table. One solution is to add an index to the client_id, device_id, and created_at columns. This can significantly improve the query performance.

Another solution is to optimize the query by removing unnecessary clauses. For example, the withTrashed() clause may not be needed if the device_histories table does not have any soft-deleted records. Similarly, the latest() clause may not be needed if the created_at column is already indexed.

To detect and log slow queries, you can use Laravel's query log. Enable the query log in your config/database.php file by setting the log option to true. Then, you can check the log file for slow queries and log them using Laravel's logging system.

Here's an example of how to enable the query log and log slow queries:

// In config/database.php
'connections' => [
    'mysql' => [
        // ...
        'log' => true,
        'log_level' => 'warning',
    ],
],

// In your middleware
\DB::connection()->enableQueryLog();

// Run the query
$deviceHistory = DeviceHistory::where('client_id', $device->client_id)
    ->where('device_id', $device->id)
    ->whereDate('created_at', '>=', Carbon::now()->subSeconds(10))
    ->withTrashed()
    ->latest()
    ->first();

// Check for slow queries
$queries = \DB::getQueryLog();
foreach ($queries as $query) {
    if ($query['time'] > 1000) { // Log queries that take more than 1 second
        \Log::warning('Slow query: ' . $query['query'], $query['bindings']);
    }
}

Snapey

3 years ago

Best Answer

Level 122

check your database indexes.

Once you send the request to the database, the deed is done. Its going to run until the database times out. I don't think there is a way to interrupt it.

I would change this line;

->whereDate('created_at', '>=', Carbon::now()->subSeconds(10))

to just

->where('created_at', '>=', Carbon::now()->subSeconds(10))

Since this will simplify the query and may allow it to use an index on the created_at column.

robinsonryan

3 years ago

Level 3

You answered the main question that I was curious about. Once the query is sent to the DB it just runs until it times out. I guess as a follow up, let's say I wanted to log the mysql query. If the query times out will laravel's query loggin function even log that the quey happened. Something like this:

\DB::connection()->enableQueryLog();

//my query that may work or may run for 2 hours

$queries = \DB::getQueryLog();

foreach ($queries as $query) {
    if ($query['time'] > 1000) { // Log queries that take more than 1 second
        \Log::channel('db')->info('Slow query: ' . $query['query']);
    }
}

Snapey

3 years ago

Level 122

@robinsonryan I would start by running EXPLAIN on the database query

robinsonryan

3 years ago

Level 3

Thanks for the replies. I'll start with what we've talked about here and see where it gets me. Thanks!

Please or to participate in this conversation.