1. Introduction

When developing or optimizing Rails applications, one of the important jobs is understanding and optimizing SQL queries because most of the slow web speed is due to inappropriate DB query / processing logic. We are going to ask questions like: How many SQL queries are called after each request, how long does it take to complete an SQL query? Are the query repeatedly called in different places in the code? No ?, which query is called more times than others?
Normally, we usually find them by checking log files, but if we want to analyze a request in detail or if we want to track multiple requests at the same time, it will be difficult for us to find and filter out the messages. related information in log file.
To answer those questions, Steven Yue created a gem called sql_tracker . In this article, we will learn how the sql_tracker gem works and its features to help track and analyze SQL queries.

Tracking

Basically, sql_tracker uses instrumentation API to track SQL queries called from Active Record through the monitoring of the sql.active_record hook:

Instead of using a block, we can provide an instance object, implemented by the method call:

sql_tracker is declared and tracks queries when a Rails process runs, and stops tracking when the Rails process finishes.

Filtering

We may not want to listen for all the queries. For example, we just want to track SELECT statements of queries, or we want to track queries that are called with a certain directory.
By default, sql_tracker tracks and record all 4 commands (SELECT, INSERT, UPDATE and DELETE), but we can change it by changing the settings tracked_sql_command

By default, sql_tracker tracks the app directory and lib folders to ignore unrelated queries (eg queries called from the tests folder).

Grouping

sql_tracker will try to simplify and assemble queries by replacing the special value with xxx. For example, if we execute the following SQL query:

And then another query:

sql_tracker will combine them into 1 query:

sql_tracker uses regular expressions to rewrite SQL queries. Here for example, it has found and replaced values ​​after operators like =, <>,>, <and even BETWEEN … AND … Once grouped, it becomes easy. It’s easier to analyze queries, and calculate total queries, average time, and more.

Storing

sql_tracker keeps all data in memory, and exports data in a JSON file when the Rails process finishes. All data contained in a hash, the format will have the form:

When the keys are md5 or the sql queries have been simplified, and the values ​​are full sql query + some statistical information.
By default, the file will be saved in the tmp directory in Rails roots folder, you can also change it using config:

If we have used the puma app server, and set more than 1 worker, we will see more than 1 JSON output file in the output folder because each worker will track and store data separately.

Reporting

Finally, we can generate the report using 1 or more JSON dump files:

The report will take the form: