Example: "shared-buffers. below 2GB: set it to 20% of full memory, below 32GB: 25% of your full memory." -- Don't do this. Set it to around 20% of your memory if you have a small machine, such as a vps or desktop. If you have lots of memory, set it between 2GB and 4GB. Anything above 8GB exceeds what it is designed to handle and can cause major performance problems, such as the database becoming unresponsive for 1-2 minutes.
"work_mem. Start low at 32/64MB. Look for temporary file lines in logs. Then set it to 2-3x the largest temp file that you see. This setting can give a huge speed boost (if set properly)." -- This is a great way to cause your database machine to swap to death. A single query can cause many times work_mem to be allocated (it can allocate this much for every sort or hash). So this really depends on how many connections you have and what the queries are. No silver bullet here, but 16-24MB usually works pretty well if you have enough memory.
maintenance_work_mem: 2GB is plenty. This is used whenever you create an index, and I think autovacuum also uses this setting. 10% is way too high.
checkpoint_timeout: the higher the better here, but keep in mind that if your db goes down and has to be started, it can take this long before it is available to accept queries. 5M is probably what I would use unless I knew I could accept more down time than that.
Also, changing linux kernel settings can make a huge difference, but tuning disk performance and dirty buffer sizes is a whole topic I won't get into here.
It's livenotes from a presentation, I expect Reinout van Rees noted the parts that interested him as the actual presentation has what you think "right":
> If you have lots of memory, set it between 2GB and 4GB. Anything above 8GB exceeds what it is designed to handle
From the slides:
> Above 32GB (lucky you!), set to 8GB.
> This is a great way to cause your database machine to swap to death.
from the slides:
> But be careful: It can use that amount of memory per planner node.
maintenance_work_mem: 2GB is plenty. [...] 10% is way too high.
from the slides:
> 10% of system memory, up to 1GB. Maybe even higher if you are having VACUUM problems.
Hi, I'm the Reinout that made the notes. Yes, it was a live summary and his slides went by very fast. I'm surprised how much I got written down. So I had to leave stuff out, for instance the >32GB comment (which I did as it seemed applicable only to few people).
Masklinn, thanks for doing this bit of checking! For many people (including myself :-) the summary will be enough, but checking the actual presentation is a good idea if you run into problems.
Are you actually suggesting that using asynchronous commit on non-battery-backed disks is safe, while calling the article's advice "dangerous and wrong"? You do understand that asynchronous commit increases your likelihood of data loss, right?
Async commit has nothing to do with battery backed disks or not. That is Fsync. Async commit will bundle multiple commits and sync them at the same time. Depending on how you configure it, you stand to lose as much as the last X seconds of commits before the server went down, and never more. (you can configure it to lose much less, it's configurable!).
It's all a tradeoff of reliability and recoverability vs performance. Postgres gives you lots of choices so you can make the tradeoff that is appropriate for your data.
"Anything above 8GB exceeds what it is designed to handle and can cause major performance problems, such as the database becoming unresponsive for 1-2 minutes."
Can you please expand on that? What version did you test, on what hardware, and what workload?
It's also pretty dangerous and wrong.
Example: "shared-buffers. below 2GB: set it to 20% of full memory, below 32GB: 25% of your full memory." -- Don't do this. Set it to around 20% of your memory if you have a small machine, such as a vps or desktop. If you have lots of memory, set it between 2GB and 4GB. Anything above 8GB exceeds what it is designed to handle and can cause major performance problems, such as the database becoming unresponsive for 1-2 minutes.
"work_mem. Start low at 32/64MB. Look for temporary file lines in logs. Then set it to 2-3x the largest temp file that you see. This setting can give a huge speed boost (if set properly)." -- This is a great way to cause your database machine to swap to death. A single query can cause many times work_mem to be allocated (it can allocate this much for every sort or hash). So this really depends on how many connections you have and what the queries are. No silver bullet here, but 16-24MB usually works pretty well if you have enough memory.
maintenance_work_mem: 2GB is plenty. This is used whenever you create an index, and I think autovacuum also uses this setting. 10% is way too high.
checkpoint_timeout: the higher the better here, but keep in mind that if your db goes down and has to be started, it can take this long before it is available to accept queries. 5M is probably what I would use unless I knew I could accept more down time than that.
Also, changing linux kernel settings can make a huge difference, but tuning disk performance and dirty buffer sizes is a whole topic I won't get into here.
You can learn basically everything you need here: http://www.2ndquadrant.com/en/postgresql-90-high-performance...
One thing not mentioned, but which can have a HUGE performance advantage, especially on virtualized disks or spinning disks that don't have a battery backed raid controller, is this: http://www.postgresql.org/docs/9.1/static/wal-async-commit.h...