3. Database server tuning - sergiocontrino/docs GitHub Wiki
[Public document] (http://intermine.readthedocs.org/en/latest/system-requirements/software/postgres/postgres/)
| Parameter | Build | Production | Notes |
|---|---|---|---|
| shared_buffers | 10-25% of total RAM | 40% of total RAM | Starting values for tuning. Lower if other applications running (i.e. build, you could need to try a 15% instead) |
| temp_buffers | ~80MB | Sets the maximum number of temporary buffers used by each database session. These are session-local buffers used only for access to temporary tables. The default is eight megabytes (8MB). The setting can be changed within individual sessions, but only before the first use of temporary tables within the session; subsequent attempts to change the value will have no effect on that session. | |
| work_mem | ~500MB but < 1/10 of available RAM | can be higher | Specifies the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files. The value defaults to one megabyte (1MB). Note that for a complex query, several sort or hash operations might be running in parallel; each operation will be allowed to use as much memory as this value specifies before it starts to write data into temporary files. Also, several running sessions could be doing such operations concurrently. Therefore, the total memory used could be many times the value of work_mem; it is necessary to keep this fact in mind when choosing the value. Sort operations are used for ORDER BY, DISTINCT, and merge joins. Hash tables are used in hash joins, hash-based aggregation, and hash-based processing of IN subqueries. |
| maintenance_work_mem | 5% of RAM (max 20% of RAM) |
Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. It defaults to 16 megabytes (16MB). Since only one of these operations can be executed at a time by a database session, and an installation normally doesn't have many of them running concurrently, it's safe to set this value significantly larger than work_mem. Larger settings might improve performance for vacuuming and for restoring database dumps. When autovacuum runs, up to autovacuum_max_workers times this memory may be allocated, so be careful not to set the default value too high. |
|
| default_statistics_target | ~250 | ||
| effective_io_concurrency | see note | number of separate drives comprising a RAID 0 stripe or RAID 1 mirror being used for the database. (For RAID 5 the parity drive should not be counted) | |
| max_locks_per_transaction | 640 | The default, 64, has historically proven sufficient, but you might need to raise this value if you have queries that touch many different tables in a single transaction. | |
| effective_cache_size | up to 50% of RAM | up to 75% of RAM | |
| synchronous_commit | off | safer alternative to fsync=off (see postgres manual) | |
| checkpoint_segments | 128 | The default is 3 and need to be raised (big improvement) | |
| checkpoint_timeout | 10 min | Linked to the increase in checkpoint_segments ( see postgres manual). | |
| checkpoint_completion_target | 0.9 | ||
| random_page_cost | Set to around 2.0, rather than 4.0 | tbc | |
| geqo_threshold | Set to 14 | tbc | |
| from_collapse_limit | Set to 14 | tbc | |
| join_collapse_limit | Set to 14 | tbc | |