This post's content
How to detect PostregSQL/MySQL performance anomalies in 3 minutes?
Remember when your website checkout didn't work for 4 hours just because PostgreSQL was overloaded and CPU reached 100%? Here is a quick guide on detecting these anomalies as soon as they start.
1. Enable Slowlog
PostgreSQL has a unique mechanism that can capture SQL queries slower than X milliseconds. This means defining a threshold and capturing all these way-too-slow queries. You can collect them in a table or a file. For this process, we will use a slowlog file.
Note: Please ensure that you configure the Slowlog parameters correctly and with the correct values. For example, setting log_min_duration_statement to '0' or a small number and setting log_statement to 'all' can generate too much logging information on the production database, increasing your storage consumption.
You can follow the detailed steps on how to enable it Here.
2. Deploy EverSQL AI-powered Sensor
To detect anomalies, the ML algorithms should have time series data which is the collection of data at specific time intervals. The EverSQL Sensor will do it for you, without impacting performance or requiring any credentials.
EverSQL Performance Sensor is a non-intrusive module that collects external signals from your database. By installing EverSQL Performance Sensor, you can automatically track and identify performance bottlenecks, optimize slow queries, and expect additional ai-powered insights.
Sensor supported databases: MySQL, PostgreSQL, MariaDB, Percona Server.
Sensor supported platforms: Centos, RedHat, Ubuntu, Debian, Amazon RDS(MySQL/MariaDB), Amazon Aurora for MySQL/PostgreSQL, Google Cloud SQL, Microsoft Azure Database.
Here you can find instructions on how to install the database sensor.
3. Be amazed at how accurate the ML algorithm is
You will receive a time-series graph to see past performance crises by allowing the ML algorithms to analyze your performance history. You will be surprised how accurate it can be to map all the performance events that your database had in the past.
4. Receive anomaly alerts via Slack or email
With EverSQL you can get alerts as soon as anomalies happen on your database. You can receive them via email, or via Slack.
for example, an alert about an extreme load that does not fit a common Sunday 2pm.
5. Summary - Finding database performance anomalies
You can avoid production incidents on your database, and identify them before your customers. With tools like EverSQL you can get notifications as soon as the anomaly starts and use online solutions to fix it within a few minutes, instead of hiring expensive database consultants.
Click here to try it, for free.
