Freento SQL Log + Advanced Actions

Freento SQL Log + Advanced Actions is a powerful tool for logging and analyzing SQL queries in Magento 2. The module stores all SQL queries in an ElasticSearch or OpenSearch database, along with additional details and a call trace for each query, helping to identify system bottlenecks and optimize performance. Flexible settings allow you to control logging at the level of web requests and CLI commands, as well as filter entries based on specified parameters.

Optimization on a Real Codebase

The module is best used on a local system for debugging. However, it can also be used in production. In such cases, it should not be enabled for all users. There is an option to enable logging only for users with a special cookie or a specific GET parameter—ensuring that only such requests are logged, making it safe to use in production.

Identifying Problematic Queries and Debugging Them

The module groups SQL queries by each request, allowing you to analyze system load.
It displays the execution time of each individual query, making it easy to identify the slowest ones and prioritize their optimization.
It groups identical queries. If two or more identical SQL queries are executed for a single request, it's a sign that the code needs optimization.
It groups identical queries while excluding parameters. A high number of such queries may indicate inefficient processing of data element by element instead of batch loading, for example, via collections.
It allows sorting requests by the number of queries, helping to identify the most resource-intensive operations.
For each SQL query, you can view the execution trace. Statistics show that in 85% of cases, performance issues originate from custom modules, which is evident in the trace. However, this tool has also been used multiple times to optimize Magento Core code itself.

Facts:

Our research shows that 60% of performance issues are related to inefficient MySQL usage.

Why Is This Important?

A large number of SQL queries combined with network latency can result in slow page loading, especially for pages that are not cached by FPC.
Optimizing SQL queries in Magento 2 can significantly reduce server load and improve the overall responsiveness of the system.