Tune Your Database
Your database server can be either a liability or an asset to performance. If you use one of the modern SQL databases, don’t treat it as a dumb storage mechanism. We have seen in Offload Work to the Database how rewriting Ruby code into SQL can give you one or more orders of magnitude improvement.
But equally important is having your database server tuned up for optimal performance. You’ll want to do this, because the default settings are usually inadequate, especially for PostgreSQL.
Example: Tune Up PostgreSQL for Optimal Performance
This example is relevant for you only when you have to set up the database server on your own. If you host on Heroku, then you can expect your PostgreSQL to be configured reasonably well. That might be true for other hosting solutions.
PostgreSQL has a plethora of configuration options, so instead of diving into details, we’ll talk at a high level about what we need to configure.
-
Let the database use as much memory as possible. Ideally, all your data should fit into RAM for faster access.
-
Make sure the database has enough memory to store intermediate results, especially for sorts and aggregations.
-
Set the database to log slow queries and preserve as much information about them as possible to reproduce the problem.
Let me show you the PostgreSQL configuration snippet that implements these goals. This is an extract from the config we used for Acunote. It’s not a complete config, so you should review these settings, read comments, and merge with your own config as necessary.
| chp9/postgresql.conf | |
| | # For all memory settings below, RAM_FOR_DATABASE is the amount of memory |
| | # available to the PostgreSQL after the operating system and all other |
| | # services are started. |
| | # |
| | # Evaluate the Ruby pseudocode in angle brackets and replace |
| | # it with actual values. |
| | |
| | # =========================================================================== |
| | # Use as Much Memory as Possible |
| | # =========================================================================== |
| | |
| | # How much memory we have for database disk caches in memory |
| | # Note, disk caching is controlled by the operating system, |
| | # so this setting is just a guideline. |
| | # Recommended setting: RAM_FOR_DATABASE * 3/4 |
| | set effective_cache_size <ram_for_database.to_i * 3/4>MB |
| | |
| | # Shared memory that PostgreSQL itself allocates for data caching in RAM |
| | # Recommended setting: RAM_FOR_DATABASE/4 |
| | # Warning: on Linux make sure to increase the SHMMAX kernel setting. |
| | set shared_buffers <ram_for_database.to_i / 4>MB |
| | |
| | |
| | # =========================================================================== |
| | # Allocate Enough Memory for Intermediate Results |
| | # =========================================================================== |
| | |
| | # Work memory for queries (to store sort results, aggregates, etc.) |
| | # This is a per-connection setting, so it depends on the expected |
| | # maximum number of active connections. |
| | # Recommended setting: (RAM_FOR_DATABASE/max_connections) ROUND DOWN 2^x |
| | set work_mem < 2**(Math.log(ram_for_database.to_i / |
| | expected_max_active_connections.to_i)/Math.log(2)).floor >MB |
| | |
| | # Memory for vacuum, autovacuum, index creation |
| | # Recommended setting: RAM_FOR_DATABASE/16 ROUND DOWN 2^x |
| | set maintenance_work_mem < 2**(Math.log(ram_for_database.to_i / 16) |
| | /Math.log(2)).floor >MB |
| | |
| | |
| | # =========================================================================== |
| | # Log Slow Queries |
| | # =========================================================================== |
| | |
| | # Log only autovacuum's longer than 1 sec. |
| | set log_autovacuum_min_duration 1000ms |
| | |
| | # Log long queries. |
| | set log_min_duration_statement 1000ms |
| | set auto_explain.log_min_duration 1000ms |
| | |
| | # Explain long queries in the log using the auto_explain plug-in. |
| | set shared_preload_libraries 'auto_explain' |
| | set custom_variable_classes 'auto_explain' |
| | |
| | # But do not use explain analyze, which may be slow |
| | set auto_explain.log_analyze off |
You might have noticed that this configuration mostly optimizes PostgreSQL memory usage. Yes, it’s memory again. We spent the greater part of this book talking about Ruby memory optimization, and now it’s our database that also needs memory tuning.
That’s not a coincidence. Modern software is rarely limited by CPU. The most severe limitation is the amount of available memory, followed by network latency and throughput, and disk I/O.
That’s why, no matter what database you use, you need to make sure it has and uses as much memory as possible.