Detecting slow database operations in Laravel 9.x
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!
Like this article?
Buy me a coffee
👋 Hi there! I'm Amit. I write articles about all things web development. You can become a sponsor on my blog to help me continue my writing journey and get your brand in front of thousands of eyes.
