Introduction

If your application uses MySQL and you’re interested in getting the best out of it via performance tuning and monitoring, then Releem is a very nice tool that can assist you in your endeavor.

I discovered Releem from Roman Agabekov’s posts on social media. Roman is the founder of this tool, and since his posts about MySQL performance tuning are very insightful, I decided to give it a try and see what it has to offer.

Installing Releem

Releem uses a client-server architecture. The server aggregates data received from the client and displays the optimization tips in a dashboard that can be accessed online.

The client is basically an agent that can scan both MySQL and the OS and send the stats to the server so that you can visualize them.

As mentioned in the documentation, there are many options for installing the local agent. You can install it in the cloud or manually on-prem. In my case, I decided to install it locally on Windows, as I have a MySQL server that I use to test both Hypersistence Optimizer and my training repositories.

1️ The first step is to create a database user that Releem will use to extract the MySQL metrics and statistics:

CREATE USER 'releem'@'%' identified by 'my-secret-password';

GRANT PROCESS, REPLICATION CLIENT, SHOW VIEW ON ＊.＊ TO 'releem'@'%';
GRANT SELECT ON performance_schema.events_statements_summary_by_digest TO 'releem'@'%';
GRANT SELECT ON performance_schema.table_io_waits_summary_by_index_usage TO 'releem'@'%';
GRANT SELECT ON performance_schema.file_summary_by_instance TO 'releem'@'%';
GRANT SYSTEM_VARIABLES_ADMIN ON ＊.＊ TO 'releem'@'%';
GRANT SELECT ON ＊.＊ TO 'releem'@'%';

2️ Creating the Releem agent configuration

For Windows, we first need to create the ReleemAgent folder where we are going to store the agent configuration:

mkdir "C:\ProgramData\ReleemAgent\"
echo .> "C:\ProgramData\ReleemAgent\releem.conf"

The releem.conf file will contain the following configuration:

apikey="my-secret-key"
releem_cnf_dir="C:\ProgramData\ReleemAgent"
mysql_user="releem"
mysql_password="my-secret-password"
mysql_restart_service="net stop MySQL80 && net start MySQL80"
mysql_cnf_dir="c:\ProgramData\MySQL\'MySQL Server 8.0'"
interval_seconds=60
interval_read_config_seconds=3600
query_optimization=true

Apart from the configuration of the agent itself, we also need to enable the slow query log and the Performance Schema in the MySQL configuration file:

performance_schema=1
slow_query_log=1
performance-schema-consumer-events-statements-history=ON
performance-schema-consumer-events-statements-current=ON
performance_schema_events_statements_history_size=500

3️ Downloading the Releem agent

Next, we have to download the agent:

mkdir "C:\Program Files\ReleemAgent"
PowerShell -noexit -Command "cd \"C:\Program Files\ReleemAgent\""
curl -o releem-agent.exe https://releem.s3.us-east-1.amazonaws.com/v2/releem-agent.exe

4️ Installing the Releem agent

After the agent is downloaded, we can install it as a Windows Service like this:

"C:\Program Files\ReleemAgent\releem-agent.exe" -f
"C:\Program Files\ReleemAgent\releem-agent.exe" install
"C:\Program Files\ReleemAgent\releem-agent.exe" start

Thats’s it!

MySQL Performance Tuning with Releem

Once the Releem agent is running on a system, you can navigate to the dashboard, and the first thing you’ll see is a score card that ranks your MySQL set up:

Releem allows you to monitor the query response time, throughput, and slow queries via the Metrics view:

In the Query view, you can check both slow-running queries as well as queries that contribute the most to application response time (total execution time is the query execution time multiples by the query execution count).

In my tests, I deliberately used the TABLE identifier generator, which is bad for performance, as illustrated by the first query, which increments and gets the next identifier value.

To avoid this issue on MySQL, you should avoid using the GeneratedType.AUTO strategy and instead use IDENTITY.

Apart from database schema optimization tips, we also get tips about improving the design of our database schema:

As I explained in this article, the salesmanager database belongs to the Shopizer e-commerce application and looks like it could benefit from some optimizations. For more details about optimizing a complex Java applications, such as Shopizer, check out the following videos:

• Tuning a Spring e-commerce application using Hypersistence Optimizer
• Tuning the JPA and Hibernate configurations of a Spring e-commerce application
• Tuning the JPA and Hibernate mappings of a Spring e-commerce application

Awesome, right?

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

Releem provides more features than I managed to test because I didn’t have a production system that could be monitored by the Releem agent over a longer period of time in order to get some query optimization tips based on the query execution statistics.

If you are using MySQL and want to boost application performance, then you should definitely give it a try to Releem.

Insert details about how the information is going to be processed

DOWNLOAD NOW