PostgreSQL when it is not your job
reinout.vanrees.org
PostgreSQL when it is not your job
1–10 of 44 posts
Re: PostgreSQL when it is not your job
#2If you don't configure your shmmax first, you might have a problem when tweaking the memory setting in postgresql.conf.
Relevant doc: http://www.postgresql.org/docs/9.1/static/kernel-resources.h...
Re: PostgreSQL when it is not your job
#3If you don't configure your shmmax first, you might have a problem when tweaking the memory setting in postgresql.conf. Relevant doc: http://www.postgresql.org/docs/9.1/static/kernel-resources.h...
TL; DR: Postgres will just fail to start if shmmax is too small, and the logs will reflect that. On Linux, you'd say:
# sysctl -w kernel.shmmax=N
EDIT: Of course, to make the change persist across reboots, you'll also want to add it to /etc/sysctl.conf. I've forgotten that part more than just in this comment...
Re: PostgreSQL when it is not your job
#4As someone whose job it is to keep peoples' PostgreSQL instances happy, this list is fairly comprehensive, and much of it is good. His advice about configuration directives towards the top of the article, however, is terrible.
In particular, work_mem: the article suggests setting it to 2-3x the size of the largest temp file you see. The thing you need to be mindful of with work_mem is that the limit is per sort. I have a process on one of my masters that periodically regenerates a materialized view. Each run leaves hundreds of mibibytes of temp files. Configured per the article's advice, it's eminently possible to exhaust physical memory on sorts (100 connections each doing 10 sorts, for example). Unfortunately, the Linux OOM-killer is naïve about postgres; it tends just to thump the postmaster. Fun times.
To the contrary, something low like 16MB is the general recommendation. You can tweak that per session, if you know you'll be doing larger sorts and don't want to spill to disk ("SET work_mem = $desired_value"), but there's no need to allocate 100s of mbytes to sort tens of tuples.
EDIT: Heed the advice about transactions under Django. At a previous gig, correcting the default behavior to leave a transaction open for sometimes days at a time reduced the amount of bloat on some hotter, but small tables, by three orders of magnitude. VACUUM can't do its job if there are transactions open to whom the dead tuples it's trying to reclaim might still be visible.
Also important, the bit about IN() clauses. A few months ago, I was given a query that hadn't completed overnight and asked to make it go faster. It contained a moderately sized (but not massive) IN() clause, which I refactored into a JOIN. That was the only change I made, after which it ran in 3.7s.
EDIT: clarification.
Re: PostgreSQL when it is not your job
#5This advice is just copypasta.
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...
Re: PostgreSQL when it is not your job
#6If you don't configure your shmmax first, you might have a problem when tweaking the memory setting in postgresql.conf. Relevant doc: http://www.postgresql.org/docs/9.1/static/kernel-resources.h...
TL; DR: Postgres will just fail to start if shmmax is too small, and the logs will reflect that. On Linux, you'd say: # sysctl -w kernel.shmmax=N Where N is the desired maximum shm segment size. (You'll also want to increase shmall, to accommodate shmmax, and any other shared memory requirements you may have.) EDIT: Of course, to make the change persist across reboots, you'll also want to add it to /etc/sysctl.conf.…
Try this strategy instead of using sysctl to set individual values:
- edit /etc/sysctl.conf
- run "sysctl -p"
Re: PostgreSQL when it is not your job
#7This advice is just copypasta. 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 perf…
> This advice is just copypasta.
> It's also pretty dangerous and wrong.
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.
(emphasis mine)
Re: PostgreSQL when it is not your job
#8THIS
Thanks
Even better if it was a quick guide to all quirks PSQL
Dear DBAs, PostgreSql may be great and etc, but if I need to spin a DB for testing/proof of concept, you bet I'm going to use MySQL 20 out of 10 times.
"Go RTFM" sorry, I lost count of how many times I had to set up MySQL or PSQL and MySQL is much more intuitive and easy to work with.
PSQL is sincerely a waste of time and energy for small things. If I need scalability, etc, sure, get a dba and go for PSQL
"Stupid DB tricks you should not do:" don't forget no logs in the DB. Or jut put it in a totally separate DB, but hey, an append only file is ok
"Don’t use gigantic IN clauses. Django generates them a lot. JOINs are fine, but IN isn’t well-supported in postgres."
Oh really?!?! And then they say Django in PSQL is faster?! Maybe to something really simple. (not in my experience, maybe that's why some things are faster in MySQL)
And don't forget PgAdmin3 rocks
Re: PostgreSQL when it is not your job
#9Earlier quoted context omitted.
TL; DR: Postgres will just fail to start if shmmax is too small, and the logs will reflect that. On Linux, you'd say: # sysctl -w kernel.shmmax=N Where N is the desired maximum shm segment size. (You'll also want to increase shmall, to accommodate shmmax, and any other shared memory requirements you may have.) EDIT: Of course, to make the change persist across reboots, you'll also want to add it to /etc/sysctl.conf.…
Try this strategy instead of using sysctl to set individual values: - edit /etc/sysctl.conf - run "sysctl -p"
Yes, definitely. I just learned about -p recently, and had been editing the config AND running sysctl -w for way too long.
Re: PostgreSQL when it is not your job
#10As someone whose job it is to keep peoples' PostgreSQL instances happy, this list is fairly comprehensive, and much of it is good. His advice about configuration directives towards the top of the article, however, is terrible. In particular, work_mem: the article suggests setting it to 2-3x the size of the largest temp file you see. The thing you need to be mindful of with work_mem is that the limit is per sort . I h…
Just out of interest, but how large was the "moderately sized" IN() clause? I am asking, as we are looking at postgresql as an alternative to MySQL, and we have some queries currently with up to 5000 values inside IN()