Postgres Tuning For Developers: Difference between revisions
From MoodleDocs
Damyon Wiese (talk | contribs) (Created page with "These are instructions for tuning a default Postgres 9.1.7 install on Ubuntu (12.04) with 16GB of RAM. These instructions are intended for development machines only - not produc...") |
(Note about plan not to migrate this page to the new developer resources. See template for more info.) |
||
| (7 intermediate revisions by 5 users not shown) | |||
| Line 1: | Line 1: | ||
{{Template:WillNotMigrate}} | |||
These are instructions for tuning a default Postgres 9.1.7 install on Ubuntu (12.04) with 16GB of RAM. | These are instructions for tuning a default Postgres 9.1.7 install on Ubuntu (12.04) with 16GB of RAM. | ||
| Line 13: | Line 15: | ||
=Changes= | =Changes= | ||
Change Setting in | Change Setting in postgresql.conf. (The path will depend on your system, e.g. Linux: /etc/postgresql/9.1/main/postgresql.conf Mac: /Library/PostgreSQL/9.3/data/postgresql.conf) | ||
/etc/postgresql/9.1/main/postgresql.conf | |||
<pre> | <pre> | ||
shared_buffers = 512MB # Recommended 1/4 of RAM - I'm leaving this lower on a dev machine (still up from 24MB) | shared_buffers = 512MB # Recommended 1/4 of RAM - I'm leaving this lower on a dev machine (still up from 24MB) | ||
effective_cache_size = 8GB # Recommended 1/2 of RAM | effective_cache_size = 8GB # Recommended 1/2 of RAM | ||
checkpoint_segments = 20 # Recommended minimum | checkpoint_segments = 20 # Recommended minimum -- Don't do this in Postgres 11. This setting no longer exists. | ||
checkpoint_completion_target = 0.9 # Up from 0.5 | checkpoint_completion_target = 0.9 # Up from 0.5 | ||
work_mem = 20MB # Used for in memory sorts | work_mem = 20MB # Used for in memory sorts | ||
| Line 34: | Line 36: | ||
Reboot! | Reboot! | ||
=Don't bother with a RAM disk= | |||
* sam and Tim have tested using a RAM disk for phpunit runs on Windows and found no improvement once these settings are in place (even when main drive is an HD not an SSD). | |||
* Eloy also did some testing without significant improvements. | |||
* NOTE: This is not true of MySQL, where significant gains can be seen. | |||
[[Category:Developer tools]] | |||
Latest revision as of 13:07, 4 January 2023
| Warning: This page is no longer in use. The information contained on the page should NOT be seen as relevant or reliable. |
These are instructions for tuning a default Postgres 9.1.7 install on Ubuntu (12.04) with 16GB of RAM.
These instructions are intended for development machines only - not production environments.
These are based on recommendations from here: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
First - results:
Before changes - unit tests on master took ~ 09:00
After changes - unit tests on master took 05:48
Changes
Change Setting in postgresql.conf. (The path will depend on your system, e.g. Linux: /etc/postgresql/9.1/main/postgresql.conf Mac: /Library/PostgreSQL/9.3/data/postgresql.conf)
shared_buffers = 512MB # Recommended 1/4 of RAM - I'm leaving this lower on a dev machine (still up from 24MB) effective_cache_size = 8GB # Recommended 1/2 of RAM checkpoint_segments = 20 # Recommended minimum -- Don't do this in Postgres 11. This setting no longer exists. checkpoint_completion_target = 0.9 # Up from 0.5 work_mem = 20MB # Used for in memory sorts synchronous_commit = off # Fine only for dev machines
Append File: /etc/sysctl.conf
# Postgres tuning kernel.shmmax=1073741824
Reboot!
Don't bother with a RAM disk
- sam and Tim have tested using a RAM disk for phpunit runs on Windows and found no improvement once these settings are in place (even when main drive is an HD not an SSD).
- Eloy also did some testing without significant improvements.
- NOTE: This is not true of MySQL, where significant gains can be seen.
