Amit Merchant

Amit Merchant

A blog on PHP, JavaScript, and more

Detecting slow database operations in Laravel 9.x

June 24, 2022 ·

When working with databases with web applications, you never know when some query, which used to work fine, starts slowing down in some scenarios.

For instance, in a scenario where the indexing is not done properly or the data being inflated to some table has gotten overwhelmingly large.

You may want to know when something like this happens to your application, don’t you? So, let’s say you’re working with MySQL, the one way to check this is by using the slow query log which can be used to find queries that take a long time to execute.

The slow query log is a great tool for determining the slow queries and it definitely should be in place but sometimes, you only want to get notified of what has gone wrong. So that you quickly get to the problem as soon as possible.

That’s where this new database utility that has been added in Laravel 9.x can come in really handy.

The DB::whenQueryingForLongerThan method

This PR by Tim MacDonald adds a new whenQueryingForLongerThan in the Illuminate\Support\Facades\DB facade that lets you check if operations take longer than the specified time querying the database throughout a request/job and notify you about it.

To get started, you need to set up the DB::whenQueryingForLongerThan in the boot method of your application’s AppServiceProvider like so.

namespace App\Providers;

use Illuminate\Support\ServiceProvider;
use Illuminate\Support\Facades\DB;
use Illuminate\Database\Connection;
use Illuminate\Support\Facades\Log;

class AppServiceProvider extends ServiceProvider
{
    public function boot()
    {
        DB::whenQueryingForLongerThan(500, function (Connection $connection) {
            Log::warning("Database queries exceeded 5 seconds on {$connection->getName()}");

            // or notify the development team...
        });
    }
}

As you can tell, once setup, the whenQueryingForLongerThan method will execute its closure when it takes more than 5 seconds to query the database throughout a request/job.

Notice also that, it’s a per-connection basis. So, you’ll also get all the details about the connection which got slowed down.

Extras

There’s a handy totalQueryDuration method we get as a byproduct of this PR that can be used to get the time it took to complete the database operation for a request off of a connection like so.

DB::connection()->totalQueryDuration();

And that’s it! That’s a quick and dirty way to get notified about some slow DB operations in Laravel 9.x!

Learn the fundamentals of PHP 8 (and 8.1), the latest version of PHP, and how to use it today with my new book PHP 8 in a Nutshell. It's a no-fluff and easy-to-read guide to the latest features and nitty-gritty details of PHP 8. So, if you're looking for a quick and easy way to PHP 8, this is the book for you.

👋 Hi there! I'm Amit. I write articles about all things web development. If you like what I write and want me to continue doing the same, I would like you buy me some coffees. I'd highly appreciate that. Cheers!

Comments?