Advanced Rails - Building Industrial-Strength Web Apps in Record Time

ActiveRecord Performance | 171

Updating index statistics

The query planner maintains statistics on each index to decide which one to use dur-
ing query planning. While the index is updated whenever the table is modified (as
the index always needs to be up to date), the index statistics are only updated at the
DBA’s request. In PostgreSQL, the commandVACUUM ANALYZE table_nameis used. In
MySQL, the equivalent commands areANALYZE TABLE table_nameandOPTIMIZE TABLE
table_name, depending on the storage engine.

It is important to run these commands when the “shape” of the table or index
changes substantially—for example, when many rows are inserted or deleted. Under
PostgreSQL, the pg_autovacuum daemon can be set up to run maintenance auto-
matically on a periodic basis. This is highly recommended for better performance,
and it is automatically enabled starting in PostgreSQL 8.3.

Database Performance Measurement

The first place to look for simple query timing is the Rails development log. By
default, the development log lists each SQL query as it is executed, and prepends the
query with its execution time in seconds. This is a fine measure relative to other
actions and queries on the development machine, but it should not be compared to
actions in different environments.

You can diagnose a database bottleneck in a production environment by scanning
the production logs. Although the Rails production logs do not list each query, they
do list the total time spent in the database for each request:

Completed in 0.06189 (16 reqs/sec) | Rendering: 0.04007 (64%) |

DB: 0.01952 (31%) | 200 OK

Using this information, you can find the database-hungry actions in a real-world
environment. Then, you can break down the queries that comprise that action and
profile each one at the database. This will give you hints about how you might better
design your application or database structure to avoid the bottlenecks.

Examining the query plan

Before any SQL query is executed, it must be compiled and planned. The planning
process decides on the steps that are taken to answer the query. This includes the
selection of indexes and the series of scans, filters, merges, sorts, and other low-level
operations that take place to generate the requested data.

All major DBMSs provide powerful tools that show how the query planner has
decided to execute a query. In PostgreSQL, theEXPLAINkeyword shows the query
plan corresponding to the requested query. TheEXPLAIN ANALYZE querysyntax is a
variant that actually executes the query and returns actual cost values. Here is an
example of the first variant: