Laravel Slow Query Notifier
Only bad developers write slow queries. I mean, I've never written one...
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:Today I applied a fix to our @flareappio codebase that is probably going to save us a couple hundred dollars of serverless costs every month. I'll blog about it soon! 🚀 pic.twitter.com/yBZMNZiWwh
— Marcel Pociot (@marcelpociot) January 8, 2020
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):
There wasn't a meaningful change in requests per second or time per request compared to having the package installed.
With the package:
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?
We're excited to learn if this helps anyone else. Thanks!