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 command VACUUM ANALYZE table_name is used. In
MySQL, the equivalent commands are ANALYZE TABLE table_name and OPTIMIZE 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 automatically
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.
Pages:
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266