Most of our work will be related to postgresql.conf file during our tour of performance tuning.
So first of all you have to backup the postgresql.conf file. There are hundreds of parameters in
postgresql.conf file. Most of the website regarding performance tuning focus on the following.
Summary:
1. shared_buffers [related to buffer cache]
i.Should be set to 25% of the system memory on Unix and Linux system
ii.Larger settings for shared_buffers usually require a corresponding
increase in checkpoint_segment
iii.We may need to configure the linux kernel
2. work_mem [related to sort]
i. Should be set between 4MB to 64MB
ii. For 100 concurrent connections, it can be 10MB
3. maintenance_work_mem [related to regular maintenance index, vacuuming]
i. should be set to 5% of the system memory
ii. but not more then 500MB.
4. effective_cache_size [related to query planner/optimizer]
i. should be set 50%-75% of physical memory
5. wal_buffers [related to transaction commit]
i. should be set to 1MB to 16MB
ii. but larger values can result in extra fsync calls or to request more System V shared memory
6. Tuning Checkpoint Parameters [if needed]
i. checkpoint_segments [related to bulk data load]
ii. can be set to 30
-> checkpoint_completion_target: Once you've increased this parameter,
it also makes sense to
increase checkpoint_completion_target to 0.9
-> checkpoint_timeout: 5 min to 15 min
7. synchronous_commit [related to commit]
i. set to off for best performance
8. random_page_cost and seq_page_cost[related to control the planner's estimate]
i. if your database is significantly larger than physical memory,
you might want to try setting these parameters to 2 and 1
ii. Never set random_page_cost less than seq_page_cost,
but consider setting them equal (or very close to equal)
Othes :
1. Use a current version
2. ANALYZE database so that postgres keeps statistics about database to execute queries correctly
3. Run VACUUM on database tables frequently
4. EXPLAIN ANALYZE your slow statements
To Configure the linux kernel
1. http://www.postgresql.org/docs/8.4/static/kernel-resources.html
2. http://spinroot.com/spin/multicore/setup.html
3. http://www.depesz.com/2012/07/12/waiting-for-9-3-dramatically-reduce-system-v-shared-memory-consumption/
Remarks: sample query to test :
select count(emailaddress) from (
select length(emailaddress),emailaddress from companies
where emailaddress in (select emailaddress from emailcustomers)
and heading in (select heading from adminheadings)
group by emailaddress
having length(emailaddress)>5
)a;
In a PC with 2GB RAM this query takes 33603.661ms without changing the parameters.
But after changing the parameters that takes only 5751.443 ms.
So the performance is increased by five times.
source : http://www.postgresql.org/docs/8.4/static/runtime-config-resource.html
http://www.linux.com/learn/tutorials/394523-configuring-postgresql-for-pretty-good-performance
http://www.westnet.com/~gsmith/content/postgresql/pg-5minute.htm
http://www.revsys.com/writings/postgresql-performance.html
http://linuxfinances.info/info/quickstart.html
http://www.varlena.com/GeneralBits/Tidbits/perf.html
So first of all you have to backup the postgresql.conf file. There are hundreds of parameters in
postgresql.conf file. Most of the website regarding performance tuning focus on the following.
Summary:
1. shared_buffers [related to buffer cache]
i.Should be set to 25% of the system memory on Unix and Linux system
ii.Larger settings for shared_buffers usually require a corresponding
increase in checkpoint_segment
iii.We may need to configure the linux kernel
2. work_mem [related to sort]
i. Should be set between 4MB to 64MB
ii. For 100 concurrent connections, it can be 10MB
3. maintenance_work_mem [related to regular maintenance index, vacuuming]
i. should be set to 5% of the system memory
ii. but not more then 500MB.
4. effective_cache_size [related to query planner/optimizer]
i. should be set 50%-75% of physical memory
5. wal_buffers [related to transaction commit]
i. should be set to 1MB to 16MB
ii. but larger values can result in extra fsync calls or to request more System V shared memory
6. Tuning Checkpoint Parameters [if needed]
i. checkpoint_segments [related to bulk data load]
ii. can be set to 30
-> checkpoint_completion_target: Once you've increased this parameter,
it also makes sense to
increase checkpoint_completion_target to 0.9
-> checkpoint_timeout: 5 min to 15 min
7. synchronous_commit [related to commit]
i. set to off for best performance
8. random_page_cost and seq_page_cost[related to control the planner's estimate]
i. if your database is significantly larger than physical memory,
you might want to try setting these parameters to 2 and 1
ii. Never set random_page_cost less than seq_page_cost,
but consider setting them equal (or very close to equal)
Othes :
1. Use a current version
2. ANALYZE database so that postgres keeps statistics about database to execute queries correctly
3. Run VACUUM on database tables frequently
4. EXPLAIN ANALYZE your slow statements
To Configure the linux kernel
1. http://www.postgresql.org/docs/8.4/static/kernel-resources.html
2. http://spinroot.com/spin/multicore/setup.html
3. http://www.depesz.com/2012/07/12/waiting-for-9-3-dramatically-reduce-system-v-shared-memory-consumption/
Remarks: sample query to test :
select count(emailaddress) from (
select length(emailaddress),emailaddress from companies
where emailaddress in (select emailaddress from emailcustomers)
and heading in (select heading from adminheadings)
group by emailaddress
having length(emailaddress)>5
)a;
In a PC with 2GB RAM this query takes 33603.661ms without changing the parameters.
But after changing the parameters that takes only 5751.443 ms.
So the performance is increased by five times.
source : http://www.postgresql.org/docs/8.4/static/runtime-config-resource.html
http://www.linux.com/learn/tutorials/394523-configuring-postgresql-for-pretty-good-performance
http://www.westnet.com/~gsmith/content/postgresql/pg-5minute.htm
http://www.revsys.com/writings/postgresql-performance.html
http://linuxfinances.info/info/quickstart.html
http://www.varlena.com/GeneralBits/Tidbits/perf.html