Difference between revisions of "Postgres Tuning For Developers"

From MoodleDocs
m
 
(2 intermediate revisions by one other user not shown)
Line 13: Line 13:
 
=Changes=
 
=Changes=
  
Change Setting in File: /etc/postgresql/9.1/main/postgresql.conf
+
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)
 
 
(On my Mac, this file seems to be /Library/PostgreSQL/9.3/data/postgresql.conf)
 
  
 
<pre>
 
<pre>
Line 21: Line 19:
  
 
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 41: Line 39:
 
* 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).
 
* 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.
 
* Eloy also did some testing without significant improvements.
 +
* NOTE: This is not true of MySQL, where significant gains can be seen.
  
 
[[Category:Developer tools]]
 
[[Category:Developer tools]]

Latest revision as of 18:49, 11 July 2019

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.