How AI made my Amazon RDS for MySQL queries 23x faster
Tags: mysql, rds, query, optimize
Published: 2024-01-04 10:00:00

---
title: How AI made my Amazon RDS for MySQL queries 23x faster
published: true
date: 2024-01-04 10:00 UTC
tags: mysql, rds, query, optimize
canonical_url: https://community.aws/content/2aOjqjbBPh58opBm2TjgHJvTyUE/how-i-used-ai-to-make-my-amazon-rds-for-mysql-query-23x-faster
---

![Hero image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sdw40vzfatxm7od5cqai.png)


Many people use Amazon RDS for MySQL for its ease of deployment and high availability, however its performance directly depends on the quality of the SQL statements we write to query it. Writing performant SQL might seem like magic: from indexes, to functions and subquery expressions, till data cardinality, there are a lot of factors into play when it comes to writing a well tuned SQL statement.  

This blog showcases how, using the AI-driven insights provided by [EverSQL](https://www.eversql.com/?utm_medium=organic&utm_source=blog&utm_content=rdsawscommunity), I was able to identify a slow performing query and automatically receive optimization suggestions which made it **23x faster, all for free**!


{% youtube AGJ2MCi6iuo %}


If you are interested in getting the best performance from your Amazon RDS for MySQL database, read the blog!


**What you will learn**


* How to enable Amazon RDS for MySQL slow queries log, a feature which tracks non-performant SQL statements in your database
* How to connect the EverSQL sensor to an Amazon RDS for MySQL instance and start receiving performance insights and optimization suggestions
* How to optimize a query by adding indexes and rewriting the SQL statement


## Create an Amazon RDS for MySQL service

If you don’t have an Amazon RDS for MySQL service up & running already you can create one by navigating to the [RDS page in the AWS console](https://aws.amazon.com/rds/) and click on **Create Database**.


![Create database](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fnkc717ayyx3uxi0v6lk.png)

* In the _Engine options _section select **MySQL** and the **_8.0.33_** as version
* Select the **Dev/Test** Template that is sufficient for our example 


![Dev/Test](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mozozicjpklidofh7q28.png)


* In the _Settings_ section leave `database-1` as _DB instance identifier_ and set a _Master password_
* In the _Instance configuration_ section select `db.m5.large`, the minimal instance size is sufficient


![Instance configuration](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zgc9xb0zdk1zczprk8ey.png)


* In the _Connectivity_ section, enable **Public access** 

> Enabling Public Access can expose the database to the internet. It's suitable for demo purposes, but for production systems, consider enabling VPC access only from dedicated hosts.


* In the _VPC security group (firewall)_ section select **Create new** to create a new VPC security group and enter `aws-rds-mysql-security-group` as name



* In the _Monitoring_ section **disable Performance Insights**. 

![Performance Insights](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j8aot6l5zzsnc2iu1fw9.png)


> [Amazon RDS Performance Insight](https://aws.amazon.com/rds/performance-insights/) allows you to monitor RDS databases, review changes in behavior and pinpoint problematic queries. However, the optimization of these queries is still up to us. The AI-driven solution by EverSQL shown in this blog provides not only visibility on performance alterations but also index and query rewrite suggestions.



* Click **Create database**

The above command starts the creation of the Amazon RDS for MySQL database.


### Enable MySQL slow query log

In order to monitor the MySQL database, we need to enable the [slow query log](https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html). The slow query log captures every statement taking more than the specified [long_query_time](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_long_query_time) seconds to execute. To enable the slow query log we need to:



* Create a new parameter group
* Customize the parameters to enable slow query log
* Associate the parameter group with the Amazon RDS for MySQL database


### Create a new Parameter group

A Parameter group allows us to define a set of custom parameters to associate to one or more database instances. To create a new parameter group:


* Navigate to the [Amazon RDS console](https://console.aws.amazon.com/rds/)
* Choose **Parameter group**


![Parameter Group](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q9emnmtodrvposbifec2.png)


* Select **Create parameter group**
* In the _Parameter group family_ section select **mysql8.0**
* In the _Type_ section select **DB Parameter Group**
* Write `slow-query-parameter` as _Group Name_
* Write `Slow query parameter` as _Description_ 


![Parameter group details](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tft4zhcywz1rkcpgv63n.png)


* Click on **Create**


### Customize the parameters in the Parameter group

Once created the parameter group, we can customize the parameters within it. 

We should now see the newly created Parameter group called `slow-query-parameter`. Click on it to edit.



![Slow query parameter](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hqs7cqiplw4vrglf3wnt.png)


* Click on **Edit**
* Set the following parameters:
    * `general_log = 1` to enable logging
    * `slow_query_log = 1` to enable slow query logging
    * `long_query_time = 1 `to log every query taking more than 1 second (you can change the parameter as needed)
    * `log_output =FILE `to write the slow query log in a dedicated table queryable by SQL
* Click on **Save Changes**


### Associate the Parameter Group to Amazon RDS for MySQL

The last step in the setup is to associate the parameter group with the Amazon RDS for MySQL instance created. To do so:

* Navigate to the [Amazon RDS console](https://console.aws.amazon.com/rds/)
* Click on **Databases**
* Click on the `database-1` we created previously
* Click on **Modify**
* In the _Additional configuration_ section, select `slow-query-parameter` as the _DB parameter group_


![Slow query parameter](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w4nrbdbr2lgif3oumen5.png)


* Click on **Continue**
* The _Summary of modifications_ showcases the change in the DB parameter group, select the **Apply immediately** to restart the database and apply the changes.
* Click on **Modify DB instance**

**Alert**: After clicking on **Modify DB instance**, the database reboots immediately to make the necessary modifications to the parameters. After a few minutes the database will be up and running again and we’ll be able to connect to it.


## Create an EverSQL account

As mentioned at the beginning, we’ll use [EverSQL](https://www.eversql.com/?utm_medium=organic&utm_source=ext_blog&utm_content=rdsawscommunity) for our optimization. EverSQL monitors the database, and gives us AI-driven performance insights and optimization suggestions. You can create a **FREE** EverSQL account by:


* Navigate to [https://www.eversql.com/](https://www.eversql.com/?utm_medium=organic&utm_source=ext_blog&utm_content=rdsawscommunity)
* Click on **Start Optimizing for Free**
* Select **Analyze why my database is slow**


![Analyze why my database is sl](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/l1vsq4d6lrejm1riztlm.png)


* Select **MySQL**
* Select **Amazon RDS**
* Select **Native SQL**. We are assuming we can directly change the SQL queries. If you are using an ORM to manage the database, please select the dedicated option
* After creating the account, select **Analyze why my database is slow**
* Click on **install now, it takes 60 seconds**
* The screen in the below picture showcases the steps needed to install the EverSQL performance sensor and provide the **personal API key**



![Personal API token](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w2hv19n8w5kt6wvj5jj7.png)


## Install the EverSQL sensor from the Amazon Serverless Application repository

With both the Amazon RDS for MySQL instance and EverSQL account created, we can now install the sensor that monitors the database load with the following steps:



* Navigate to the [EverSQL-Performance-Sensor Amazon Serverless Application repository](https://serverlessrepo.aws.amazon.com/applications/arn:aws:serverlessrepo:us-east-2:196422412051:applications~EverSQL-Performance-Sensor) 
* Click on **Deploy**
* In the  _Application settings_ section:
    * Leave `EverSQL-Performance-Sensor` as _Application name_
    * Write `database-1` as _AwsDBServersList_, this name should match your database name
    * Write the AWS region where the Amazon RDS for MySQL instance is deployed (e.g. `eu-west-3` for Paris)
    * Copy the EverSQL API Key in the _EverSQLApiKey_ field
    * Check the **I acknowledge that this app creates custom IAM roles **checkbox


![Sensor details](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6p7lynh3hzlr643ejbho.png)

* Click on **Deploy**

Alert: if you don’t see the sensor as available in  the [EverSQL console](https://www.eversql.com/sensors/?utm_medium=organic&utm_source=ext_blog&utm_content=rdsawscommunity), don’t worry! We’ll need to generate some slow traffic for it to show up.


## Download the dataset

For the purpose of the blog, we’ll use the [Newyork Taxi Trip Data](https://www.kaggle.com/datasets/microize/newyork-yellow-taxi-trip-data-2020-2019) from Kaggle. This dataset provides information about New York taxi trips and can be downloaded once created a free Kaggle account.

After signing in, click on the **Download** button.

![Kaggle dataset](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1kna4hh5wqu8ucebsurk.png)


Once the zip file is downloaded, we can extract it and navigate with the terminal within the folder which contains a list of CSV files called `yellow_trip_data` together with the year and month.


## Connect to the Amazon RDS for MySQL instance

The next step is to connect to the MySQL instance and generate some traffic


* Navigate to the [Amazon RDS console](https://console.aws.amazon.com/rds/)
* Click on **Databases**
* Click on the `database-1` we created previously
* In the _Connectivity and security_ section we can find the **Endpoint and port** 
* With `mysql` [command line already installed](https://dev.mysql.com/doc/mysql-getting-started/en/), we can execute the following command to connect:

```bash
mysql -u [USERNAME] -h [HOST] -P [PORT] -p[PASSWORD]
```

Where:

* `[USERNAME]` is the MySQL user, by default `admin`
* `[HOST]` is the database hostname, that you can find in the _Connectivity and security_ section
* `[PORT]`  is the database port, that you can find in the _Connectivity and security_ section
* `[PASSWORD]` is the MySQL password that we set during the database creation


### Load the data

Once connected, we can start loading the taxi data, but, first of all we need to create a database and a table that will contain the dataset. We can do it with the following script.

```sql
CREATE DATABASE taxi;
USE taxi;
CREATE TABLE taxi_trips (
    VendorID int,
    tpep_pickup_datetime timestamp,
    tpep_dropoff_datetime timestamp,
    passenger_count int,
    trip_distance float,
    RatecodeID int,
    store_and_fwd_flag text,
    PULocationID int,
    DOLocationID int,
    payment_type int,
    fare_amount float,
    extra float,
    mta_tax float,
    tip_amount float,
    tolls_amount float,
    improvement_surcharge float,
    total_amount float,
    congestion_surcharge float
);

CREATE TABLE taxi_zones (
    LocationID int,
    Borough VARCHAR(100),
    Zone VARCHAR(100),
    service_zone VARCHAR(100)
);

```

The above script:

* Creates a database called `taxi`
* Creates a table called `taxi_trips`
* Creates a table called `taxi_zones`

Now let’s load the data.We can use the [LOAD DATA](https://dev.mysql.com/doc/refman/8.0/en/load-data.html) MySQL command for this.

```sql
LOAD DATA LOCAL INFILE  
'yellow_tripdata_2019-01.csv'
INTO TABLE taxi_trips  
FIELDS TERMINATED BY ','
ENCLOSED BY '"' 
LINES TERMINATED BY '\n'
(VendorID,tpep_pickup_datetime,tpep_dropoff_datetime,passenger_count,trip_distance,RatecodeID,store_and_fwd_flag,PULocationID,DOLocationID,payment_type,fare_amount,extra,mta_tax,tip_amount,tolls_amount,improvement_surcharge,total_amount,congestion_surcharge);
```

The above command loads `7667793` records in the `taxi_trips` table. By switching the file name in the 2nd row of the above SQL (now mentioning `yellow_tripdata_2019-01.csv`) we can also load the other 17 files loading data for 2019 and 2020.

We can also load the `taxi_zones` table with `133` records with the following statement.

```sql
LOAD DATA LOCAL INFILE  
'taxi+_zone_lookup.csv'
INTO TABLE taxi_zones  
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(LocationID,Borough,Zone,service_zone);

```

To create a slow query we can run the following SQL statement to retrieve the average number of the trips starting from the `Queens` or `Manhattan` and are on date `1st Jan 2019`.

```sql
select src_taxi_zone.zone src, 
    dest_taxi_zone.zone dest, 
    avg(total_amount)
from taxi_trips 
    join taxi_zones src_taxi_zone 
        on src_taxi_zone.LocationID = taxi_trips.PULocationID
    join taxi_zones dest_taxi_zone 
        on dest_taxi_zone.LocationID = taxi_trips.DOLocationID
where  
    src_taxi_zone.Zone in 
        (select zone from taxi_zones where Borough in ('Queens','Manhattan'))
    and payment_type=2
    and DATE(tpep_pickup_datetime) = '2019-01-01'
group by src_taxi_zone.zone, dest_taxi_zone.zone;
```

This is a complex query, so the execution time should be greater than the 1 second we defined for slow queries. In my test, the query lasted `16.47 seconds `on average.


### Verify the presence of the slow query log

Before heading to the EverSQL console, we can verify that our query appears in the slow query log. The log entry is only created when a slow query is detected in our database and, until the log is created, the sensor will not appear in the EverSQL console.

To check the presence of the slow query log:



* Navigate to the [Amazon RDS console](https://console.aws.amazon.com/rds/)
* Click on **Databases**
* Click on the `database-1` we created previously
* Click on **Logs & events**
* Scroll down to the **Logs** section
* Verify the presence of the `slowquery/mysql-slowquery.log` log


![Slow query log](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2txo24zmrex1al3caw1r.png)


If the `mysql-slowquery.log` is not appearing, reboot the database and issue the query again.


## Optimize the query with EverSQL

It’s finally time to optimize our query using EverSQL! We can head to the [EverSQL sensor page](https://www.eversql.com/sensors/?utm_medium=organic&utm_source=ext_blog&utm_content=rdsawscommunity) and we should see the sensor being active on `database-1`.


![Sensor active](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/27xo0ie1t91dbst22xme.png)


So far so good! The next step is to head to the [EverSQL dashboard console page](https://www.eversql.com/sql-query-optimizer/?display_dashboard=y&utm_medium=organic&utm_source=ext_blog&utm_content=rdsawscommunity). Scrolling down, we can see the **Your SQL Queries Container** section listing the query we executed previously. We can find an **Optimize** button that provides us insight on how to speed the query.

**Note**: The EverSQL sensor runs every 3 minutes by default. You might need to wait a few minutes for your query to show up in the EverSQL console.

{% youtube AGJ2MCi6iuo %}

The EverSQL Optimization engine suggests the following:

* Adding three indexes, one for the `taxi_trips` based on `payment_type `and` tpep_pickup_datetime `and two for `taxi_zones` on `LocationID` and `Zone`, more one dedicated to the `Borough`

```sql
ALTER TABLE `taxi_trips` ADD INDEX `taxi_trips_idx_payment_type_tpep_datetime` (`payment_type`,`tpep_pickup_datetime`);
ALTER TABLE `taxi_zones` ADD INDEX `taxi_zones_idx_locationid_zone` (`LocationID`,`Zone`);
ALTER TABLE `taxi_zones` ADD INDEX `taxi_zones_idx_borough` (`Borough`);
```
* Rewriting the query to the following, avoiding using the `DATE` function in the filter condition and limiting the data from `taxi_trips` (filtering for the `tpep_pickup_datetime` and `payment_type`) before joining. All the explanations are available in the same window.

```sql
SELECT
        src_taxi_zone.zone src,
        dest_taxi_zone.zone dest,
        avg(taxi_trips_total_amount) 
    FROM
        (SELECT
            taxi_trips.total_amount AS taxi_trips_total_amount,
            taxi_trips.PULocationID AS taxi_trips_PULocationID,
            taxi_trips.DOLocationID AS taxi_trips_DOLocationID 
        FROM
            taxi_trips 
        WHERE
            taxi_trips.payment_type = 2 
            AND taxi_trips.tpep_pickup_datetime BETWEEN '2019-01-01 00:00:00' AND '2019-01-01 23:59:59' 
        ORDER BY
            NULL) AS taxi_trips 
    JOIN
        taxi_zones src_taxi_zone 
            ON src_taxi_zone.LocationID = taxi_trips.taxi_trips_PULocationID 
    JOIN
        taxi_zones dest_taxi_zone 
            ON dest_taxi_zone.LocationID = taxi_trips.taxi_trips_DOLocationID 
    WHERE
        src_taxi_zone.Zone IN (
            SELECT
                taxi_zones.zone 
            FROM
                taxi_zones 
            WHERE
                taxi_zones.Borough IN (
                    'Queens', 'Manhattan'
                )
        ) 
        AND 1 = 1 
        AND 1 = 1 
    GROUP BY
        src_taxi_zone.zone,
        dest_taxi_zone.zone 
    ORDER BY
        NULL
```

After creating the indexes, the updated query now executes in just `0.71 seconds`. This is a more than **23x performance increase**!


## Conclusion

To get the most out of our Amazon RDS for MySQL we need to spend time understanding what SQL statements impact performance and how to optimize them. Small changes in the database structure and SQL statements can have an amazing impact on performance like an immediate 23x gain!

[EverSQL](https://www.eversql.com/?utm_medium=organic&utm_source=ext_blog&utm_content=rdsawscommunity) allows us to monitor the database, review insights about non performing queries or workload anomalies and automatically receive suggestions on indexes and sql rewrites, exactly what we need to optimize performance and spend on our MySQL instance.