SqlSafetyNet¶ ↑

ActiveRecord makes it very easy and seamless to access data from a database. A downside of this is you often don’t realize what kind of load you are putting on the database either by the number or the type of queries generated because “it just works.” This can lead to performance problems in production because databases are notoriously hard and expensive to scale.

This gem exposes debugging information about SQL queries generated by ActiveRecord in a Rails application. It is intended to be used in development mode to allow developers to see what queries are being generated so issues can be caught before code goes to production.

It works by injecting code into the connection adapter to count and analyze SELECT queries. It does not collect any information on INSERT, UPDATE, or DELETE queries. The analysis is exposed by a Rack middleware handler in a variety of ways.

SqlSafetyNet will track data about each query in your request and analyze them individually and as a group.

• Rows returned by each query

• Estimated number of bytes returned by each query

• Time taken to execute each query

• Total number of queries

• Total number or rows returned for all queries

• Total estimated bytes returned for all queries

• Total time taken to execute all queries

• Query plan analysis for each query (if supported)

A summary of the queries will be added to all responses in the X-SqlSafetyNet header. This will include the number of queries, the number of rows returned, the approximate amount of data returned from the database, and the elapsed time to make the queries.

When issues are found with queries in a request, this information will be logged.

If the response is an HTML document and the request was not from Ajax, a debug info window will be inserted into the document if there were any queries flagged as problematic. This is the most effective way to insure that the analysis is always visible to the developers. The box can also be expanded to details about each query. The debug box will always be displayed if the request queries are flagged with issues. There is also a configuration setting to always show the debug box. The box will be green if there are no issues, red if there are issues, or orange if there are issues but the queries that generate them are cached in Rails.cache.

There are variety of configuration options where you can specify the thresholds which you’d consider excess database usage. See SqlSafetyNet::Config for details.

If you are using MySQL or PostgreSQL, then each query will also get the query plan from the database and analyze it for problems like table scans on large tables.

The query analysis for PostgreSQL is much less detailed than MySQL because the MySQL plans are much more straightforward to understand programatically. Reading PostgreSQL query plans is more of an art. In addition, take the PostgreSQL warnings with a grain of salt. It only looks for large table scans or large number of rows examined in a query. However, PostgreSQL will only estimate these numbers on a simple EXPLAIN plan and sometimes it gets the number very wrong on small tables. For the query plan may estimate the query will do a table scan on 300 rows even though the table only has 10 rows in it.

For details on enabling query plan analysis see SqlSafteyNet::ExplainPlan.