Laravel Slow Query Notifier

Published in on Feb 15, 2020

Only bad developers write slow queries. I mean, I've never written one...

Pinocchio

I know you never have either, but if you ever do...we've got your back:

Introducing Slow Query Notifier for Laravel.
Get notified if your app ever runs an objectively slow database call.


Inspiration

We wrote this package after seeing a tweet from Marcel Pociot that reminded us even REALLY GOOD developers write slow queries sometimes:

To be specific, this query was fine (fast enough) until there were 40M records in the table. Then he started to experience a slow down that increased costs. This package will notify you as soon as you cross into this dangerous territory. We hope you get some peace of mind out of it!


Installation

composer require thomasjohnkane/slow-query-notifier

Set an email address

// app/Providers/AppServiceProvider.php

use SlowQueryNotifier\SlowQueryNotifierFacade as SlowQueryNotifier;

public function boot()
{
	SlowQueryNotifier::toEmail('admin@example.com');
}

Test it works (in Production)

If you are using this in production (as intendend) make sure it is working correctly:

php artisan sqn:test

This command will test two things:

  • We can detect slow queries in your app
  • We can send an email to you if a slow query runs

Configuration

In general, we set up all of the configuration for you with sensible defaults. However, you can change the default settings if you'd like:

Threshold

The default is 99ms. Set a different threshold in milliseconds in your configuration:

SlowQueryNotifier::threshold(200)->toEmail('admin@example.com');

Enable/Disable

The package is enabled by default. Set this value to false in your .env to bypass the listener.

SLOW_QUERY_NOTIFIER_ENABLED=false

Why we chose 99ms as a threshold

After substantial research, we found a common number kept popping up for "when" queries "should" be looked at. Notably, many experts in the field of database/query optimization suggest you should look at anything taking over 50ms. However, we decided to set the default to 99ms, which will notify you if something is double the time of 50ms. That is not to say that a 100ms query is bad. Some applications and situations require queries to run longer. Depending on the expectations of the specific users, 100ms could be considered really fast. You can set this number to whatever you'd like. In the future, we'd like to be able to set up thresholds by "connection." Then you could in theory have a long running connection that won't blow up your email inbox.


Performance

You might be wondering if this will slow down your application in production. After all, we're listening and checking the length of every query. We ran a fair amount of benchmarking to test it out:

Without the package:

We ran a query that pulls all users from the database. We hit the route with 1,000 requests (100 concurrent):

Benchmark screenshot with package disabled

There wasn't a meaningful change in requests per second or time per request compared to having the package installed.

With the package:

Benchmark screenshot with package enabled

Additionally, the notification is set to use shouldQueue (assuming you have a queue set up). Our guess is that most people worried about query performance will already have email and queues set up in their apps. We'll see if that's true.



Next Steps & Plans

We'd like to move away from the real-time notifier. Instead, we'd like to send a report weekly. A weekly report would feel less intrusive and reduce the risk of blowing up your inbox. To start, the report would show you:

  • Top 3 slowest queries
  • Full table scans and n+1 situations
  • Any queries that violated your threshold

Alternatively, we'd like to eventually pull this data from the database instead of the application. For example, it looks like MySQL and Postgres do a lot of performance monitoring out of the box. It would be nice to just create a GUI to show this existing data. Maybe a Nova package?

Screenshot of MySQL built-in performance monitoring

We're excited to learn if this helps anyone else. Thanks!

Onboard to my newsletter if you like the articles. It makes me feel good...😉