Difference between revisions of "Tuning the PostgreSQL Database"
From Observer GigaFlow Support | VIAVI Solutions Inc.
Kevin Wilkie (Talk | contribs) |
|||
(5 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) | ||
Line 4: | Line 5: | ||
*Restart Postgres | *Restart Postgres | ||
*Restart applications requiring Postgres | *Restart applications requiring Postgres | ||
− | |||
− | |||
'''Settings based on available RAM''' | '''Settings based on available RAM''' | ||
Line 49: | Line 48: | ||
Housekeeping | Housekeeping | ||
vacuum pg_depend;vacuum full pg_constraint;vacuum full pg_inherits; | 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