Difference between revisions of "Tuning the PostgreSQL Database"
From Observer GigaFlow Support | VIAVI Solutions Inc.
Kevin Wilkie (Talk | contribs) |
|||
| (9 intermediate revisions by 2 users not shown) | |||
| Line 1: | Line 1: | ||
| + | [[Category:Database]] | ||
Tuning of Postgres is done via the postgresql.conf file. | Tuning of Postgres is done via the postgresql.conf file. | ||
*Make a copy of the original file should changes to the file cause any problems (allowing you to revert) | *Make a copy of the original file should changes to the file cause any problems (allowing you to revert) | ||
| − | + | *Edit file as appropriate and save | |
| + | *Restart Postgres | ||
| + | *Restart applications requiring Postgres | ||
'''Settings based on available RAM''' | '''Settings based on available RAM''' | ||
| Line 10: | Line 13: | ||
'''Standard Settings''' | '''Standard Settings''' | ||
| + | effective_cache_size = 2GB | ||
| + | shared_buffers = 1000MB | ||
autovacuum_max_workers = 1 | autovacuum_max_workers = 1 | ||
checkpoint_completion_target = 0.9 | checkpoint_completion_target = 0.9 | ||
| Line 19: | Line 24: | ||
commit_delay = 10 | commit_delay = 10 | ||
max_locks_per_transaction = 3000 | max_locks_per_transaction = 3000 | ||
| + | max_wal_size = 2000MB | ||
| + | max_connections = 150 | ||
| + | maintenance_work_mem = 64MB | ||
| + | |||
| Line 33: | Line 42: | ||
commit_delay = 10 | commit_delay = 10 | ||
max_locks_per_transaction = 3000 | max_locks_per_transaction = 3000 | ||
| + | max_wal_size = 2000MB | ||
| + | max_connections = 150 | ||
| + | maintenance_work_mem = 128MB | ||
| + | |||
| + | Housekeeping | ||
| + | vacuum pg_depend;vacuum full pg_constraint;vacuum full pg_inherits; | ||
| + | |||
| + | Creating a ramdisk of the stats table | ||
| + | mkdir -p /db/ramdisk | ||
| + | chown postgres:postgres /db/ramdisk | ||
| + | chmod 777 /db/ramdisk | ||
| + | chmod +t /db/ramdisk | ||
| + | |||
| + | #in /etc/fstab add | ||
| + | tmpfs /db/ramdisk tmpfs defaults,noatime,nosuid,nodev,size=30M 0 0 | ||
| + | #In postgresql.conf, add : | ||
| + | stats_temp_directory = '/db/ramdisk' | ||
| + | #Restart server | ||
| + | |||
| + | Using a RAM disk for the postgres stats | ||
| + | mkdir -p /db/ramdisk | ||
| + | chmod 777 /db/ramdisk | ||
| + | chmod +t /db/ramdisk | ||
| + | cd /db | ||
| + | chown postgres:postgres ramdisk | ||
| + | |||
| + | Add the following to /etc/fstab | ||
| + | tmpfs /db/ramdisk tmpfs defaults,noatime,nosuid,nodev,size=30M 0 0 | ||
| + | |||
| + | |||
| + | Add the following to the postgresql.conf file | ||
| + | stats_temp_directory = '/db/ramdisk' | ||
| + | |||
| + | Run the following | ||
| + | mount /db/ramdisk/ | ||
| + | systemctl restart postgresql-10 | ||
| + | /etc/init.d/rosd restart | ||
| + | |||
| + | You should now see files in the /db/ramdisk folder | ||
Latest revision as of 11:53, 23 November 2018
Tuning of Postgres is done via the postgresql.conf file.
- Make a copy of the original file should changes to the file cause any problems (allowing you to revert)
- Edit file as appropriate and save
- Restart Postgres
- Restart applications requiring Postgres
Settings based on available RAM
shared_buffers=1/4 of the available memory
effective_cache_size = 1/2 of available memory
Standard Settings
effective_cache_size = 2GB shared_buffers = 1000MB autovacuum_max_workers = 1 checkpoint_completion_target = 0.9 checkpoint_segments = 32 log_min_duration_statement = 5000 synchronous_commit = off work_mem = 10MB wal_buffers = 10MB commit_delay = 10 max_locks_per_transaction = 3000 max_wal_size = 2000MB max_connections = 150 maintenance_work_mem = 64MB
Example for a server with 16GB of RAM
effective_cache_size = 8GB shared_buffers = 4000MB autovacuum_max_workers = 1 checkpoint_completion_target = 0.9 checkpoint_segments = 32 log_min_duration_statement = 5000 synchronous_commit = off work_mem = 10MB wal_buffers = 10MB commit_delay = 10 max_locks_per_transaction = 3000 max_wal_size = 2000MB max_connections = 150 maintenance_work_mem = 128MB
Housekeeping
vacuum pg_depend;vacuum full pg_constraint;vacuum full pg_inherits;
Creating a ramdisk of the stats table
mkdir -p /db/ramdisk chown postgres:postgres /db/ramdisk chmod 777 /db/ramdisk chmod +t /db/ramdisk
#in /etc/fstab add tmpfs /db/ramdisk tmpfs defaults,noatime,nosuid,nodev,size=30M 0 0 #In postgresql.conf, add : stats_temp_directory = '/db/ramdisk' #Restart server
Using a RAM disk for the postgres stats
mkdir -p /db/ramdisk chmod 777 /db/ramdisk chmod +t /db/ramdisk cd /db chown postgres:postgres ramdisk
Add the following to /etc/fstab
tmpfs /db/ramdisk tmpfs defaults,noatime,nosuid,nodev,size=30M 0 0
Add the following to the postgresql.conf file
stats_temp_directory = '/db/ramdisk'
Run the following
mount /db/ramdisk/ systemctl restart postgresql-10 /etc/init.d/rosd restart
You should now see files in the /db/ramdisk folder
