In today's tech companies, engineers are expected to know and care more about the database and the performance of their application. To effectively face these challenging requirements, DBA, engineering and DevOps teams need the right tools to monitor, diagnose and resolve performance issues.
In this post, I'll demonstrate how one can track down slow SQL queries and optimize them automatically, by utilizing the recent integration of EverSQL Query Optimizer with Amazon RDS Performance Insights, using EverSQL's Chrome extension.
Monitoring and Optimizing Slow SQL Queries
As an AWS user, you probably already have Performance Instance enabled (as it's the default configuration). When navigating to the Performance Insights dashboard, the first thing you'll notice is the database load chart. Right below that, you'll see a list of SQL queries sorted by the amount of load each query is generating on your server. The load each query generates is measured and visualized using the Average Active Sessions (AAS) metric.
In this post, we'll use the StackOverflow database to demonstrate the optimization process. The SQL query at the top of the list seems to be very I/O intensive (as indicated by the light blue color). The query is scanning StackOverflow's comments table, looking to fetch the ones written at the first day of 2019, or the ones with very high scores (>500). The table contains roughly 6.5 million records.
SELECT * FROM comments c WHERE DATE(c.CreationDate) = '2019-01-01' OR c.score > 500 ORDER BY c.id DESC LIMIT 1000;
The execution duration of the original query is 38 seconds.
If you have EverSQL's Chrome extension installed, you'll get an additional detailed report within the same page on RDS Performance Insights, explaining how each of the SQL queries can be optimized. EverSQL will automatically present the optimized query and suggest the optimal indexes you should create, to improve the query's performance.
This is a short glance at how the integration looks like and how the recommendations appear on Performance Insights:
To better understand the internals of this optimization process, let's start from the beginning, by looking at the comments table's structure and the query's execution plan:
CREATE TABLE `comments` ( `Id` int(11) NOT NULL, `CreationDate` datetime NOT NULL, `PostId` int(11) NOT NULL, `Score` int(11) DEFAULT NULL, `Text` text CHARACTER SET utf8 NOT NULL, `UserId` int(11) DEFAULT NULL, PRIMARY KEY (`Id`), KEY `comments_idx_score` (`Score`), KEY `comments_idx_creationdate` (`CreationDate`), KEY `comments_idx_creationdate_score` (`CreationDate`,`Score`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
As you can see, MySQL didn't choose to use any of the indexes containing the Score or CreationDate columns. This is why:
- The column CreationDate is hidden inside the DATE function, which prevents MySQL from using an index on that column. To overcome it, we can transform the original condition to one with a date range check (lines 24-25 in the query below).
- There are two conditions in the WHERE clause, with an OR operator between them. In many similar cases, we saw that MySQL can't / won't use composite indexes effectively, nor use index_merge. Therefore, splitting the query to two parts combined with a UNION, will allow MySQL to use the relevant indexes.
So after applying the query transformations:
SELECT * FROM ( ( SELECT * FROM comments c WHERE c.score > 500 ORDER BY c.score DESC LIMIT 1000 ) UNION DISTINCT ( SELECT * FROM comments c WHERE c.CreationDate BETWEEN '2019-01-01 00:00:00' AND '2019-01-01 23:59:59' ORDER BY c.score DESC LIMIT 1000 ) ) AS union1 ORDER BY union1.score DESC LIMIT 1000;
As you can see from the new execution plan, MySQL now uses the indexes for both columns.
As mentioned above, the optimized query's execution duration is roughly 20ms, which is significantly faster than the original one.
If you're using AWS RDS and would like to optimize SQL queries directly from Performance Insights, all you have to do is install EverSQL's Chrome extension, to integrate EverSQL with RDS Performance Insights.
EverSQL currently supports MySQL and MariaDB, though support for more database types is coming up soon. Feel free to send us your feedback at [email protected]