As 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.
I've always understood that IN clauses are hard on query optimizers and should be re-written as correlated subqueries with EXISTS/NOT EXISTS, often for giant speedups. But I've met lots of developers who have never seen this pattern before and get scared by it. Of course if you can also re-write it as a join, that's even easier.
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()
I don't remember specifically, but I believe it was in the thousands to low tens of thousands of rows. It's also not consistent. I've seen larger IN() clauses that never have a problem, and smaller ones that consistently do. It's been on my very low priority to-do list to put together some demo cases for the mailing lists, because overnight to < 4s just from that little refactor isn't the greatest...
Are we talking about IN clauses that contain a correlated subquery or something the optimizer would have a hard time determining was independent of outer context?
PostgreSQL is able to convert correlated subqueries with IN () clauses into joins in most cases. My guess is that it could have been two queries refactored into one.
This is not super fast in MySQL either, compared to the alternative of creating a temporary table with your 5000 values in it and doing a join on that.
That's a problem I routinely come across, and it's frustrating, because there's no reason a self-contained IN(SELECT) should ever be slower than two-stepping it with a temporary table, or worse, two queries on the client side. But it often is.
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?
Part of the reason why PostgreSQL has so any knobs is that these things are not always thing that cookie cutter approaches work with. Although if you do need to worry about these, it probably is your job and you probably are at least going to learn it.
I am not convinced about his list of "stupid db tricks you should not do." For example:
1) Sessions in the db are sometimes a good thing and sometimes not. They do have a real performance cost (we have a query in LedgerSMB that for large db's takes 20x longer because of having to do this, but the performance cost is necessary and still worth it, though we will probably offer non-web-based alternatives instead later where this wont be necessary). Of course that query is actually using another table to provide "discretionary locks" of rows to a session.... and those locks MUST persist across transactions because of the fact we are mapping to a series of HTTP requests...
2) I dont know about celery queues, but with listen/notify, you can do some really cool message queuing in PostgreSQL.
3) Usually when my app has to store files attached to data, I usually find that it's simpler to put it in the db than the filesystem. That guarantees that the files are in the backups among other things. Also for larger files, PostgreSQL's lob interface (up to 2gb) provides seek operations and more. performance issues here end up occurring outside PostgreSQL.
I am with him on very long-running transactions.
COPY is good for some things, but if you are trying to create more portable code you probably want to do something like insert foo (....) values (...), (....), (....)....
I would also suggest it is important to know the difference between LIKE '%this%' and full text searching on PostgreSQL. These are not simple drop-in equivalents. However additionally LIKE '%this%' cannot use an index (though like 'this%' can, and you can do full text indexing on Pg).
Actually since PostgreSQL 9.1 LIKE '%this%' is indexable with the pg_trgm contrib module. Since it is based on the trigrams in your search query it obviously has its caveats, generally the longer the query the more effective the index lookup is. I would imagine for example '%th%' requires a full table/index scan since it contains zero trigrams.
Yes, pg_trgm was built for that but someone figured out how LIKE could be hacked to use the trigram indexes (gist_trgm_ops, gin_trgm_ops). So if you have a * _trgm_ops index on the column normal LIKE and ILIKE queries may use that index.
I assume your example would be a false index hit which then is necessary to verify against the real value. The same would apply to make sure 'This' is not a hit when doing a case sensitive search for '%this%'. So index-only LIKE scans are not possible with * _trgm_ops indexes.
EDIT: I just realized how awesome the extensibility of PostgreSQL is. An extension can make a core operator such as LIKE indexable in an entirely new way without touching the core code.
Here's an attempt[0](by the creator of the Postgres High Perf Book, (and some patches by yours truly)) to automate PG configuration. It's not perfect, but certainly better than OOB settings
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. I've forgotten that part more than just in this comment...
Some of the suggestions make PostgreSQL seem less mature than InnoDB, still: "[don't put] sessions in the DB", "[don't put] constantly-updated counters in the database", and "[don't put] task queues in the database."
My forum gets almost a million page views daily; we store all our data in a(n) InnoDB database, including sessions, task queues, and constantly updated counters. They work just fine and are not even bottlenecks when the isolation level is REPEATABLE READ.
We use SERIALIZABLE though, as a matter of principle, so we had to implement a more fancy schema, but everything happens in the InnoDB database. We don't have to worry about running VACUUM periodically either. I feel PostgreSQL may be a wee bit overhyped.
Sessions in the db are fine, depending on what you are doing with them. LedgerSMB for example uses them to track who is doing what right now in the db, as well as maintain per-user locks that have to persist across db transactions.
In most workflows there is no significant performance penalty here. The only problem is where we are checking those locks and trying to obtain them if they are not held by someone else. This is a significant problem and currently makes a query in a large db take about 20x as long.
It all depends on what you are doing. But yeah trying to have extra-transactional locks so you can do reliable locking across HTTP requests tying it to the session sucks :-)
My take is those suggestions are RDBMS generic, not PostgreSQL specific - although many do store those types of data successfully in PostgreSQL, MySQL, etc., because of the mention of Redis as a counter store.
You don't worry about VACUUM with InnoDB, but that doesn't mean you never feel the pain of the cleanup of old row versions. See 'purge thread spiral of death' for more details.
> Some of the suggestions make PostgreSQL seem less mature than InnoDB, still: "[don't put] sessions in the DB", "[don't put] constantly-updated counters in the database", and "[don't put] task queues in the database."
It's not that you can't do them in postgresql, or even that it will perform slowly. But when you have the chance to offload work from the database server that doesn't need to be there, it's often a good idea to do so.
I wouldn't have worded it the way the author did, either --it's certainly situation-dependent whether those are a good idea. But I wouldn't take it as a "postgres isn't up to the task".
I read a similar argument against git recently: too many things to do manually, but the counter argument is that at this level you need full control on what happens and there should be no magic.
So If you have counters table you need to analyse it more often, by hand, because you don't want your rdbms to guess on your behalf if a table is a write only or update only. MySQL do a lot of guessing, like, say, PHP.
>Some of the suggestions make PostgreSQL seem less mature than InnoDB, still
I like the way you throw "still" on the end, as if innodb has ever been more mature than postgresql. That's very amusing. The suggestions you reference are not postgresql specific, they are general suggestions to not do really dumb stuff with your database, and apply to every database.
>My forum gets almost a million page views daily
Of course you can do dumb stuff, especially if you have a tiny site like that where even the crappiest server you can buy is going to be way overkill. In a case like that, go right ahead and store sessions in the DB if you want, it won't matter at all. The suggestion is for people who expect to end up with a busy site, where updating your database constantly with every page request is going to hurt your scalability and gains you nothing.
>We don't have to worry about running VACUUM periodically either.
I don't think anyone is suggesting that running a 10 year old version of postgresql is a good idea.
>I feel PostgreSQL may be a wee bit overhyped
I wonder if that has anything to do with your obvious ignorance of the subject.
One question on avoiding giant IN clauses with Django?
Say I have a class called Fridge, and a classes called Vegetables and Condiments.
Both of these have ManyToMany relationships between themselves and Fridge.
So something like:
class Fridge(models.Model):
condiments = models.ManyToManyField(Condiments)
vegetables = models.ManyToManyField(Vegetables)
And here we have a QuerySet that represents our white fridges:
qs = Fridges.objects.filter(color='white')
First query:
"Given a list of condiment IDs, get me all the fridges that have ANY of those condiments in them (modifying the original QuerySet).""
Second query:
"Given a list of vegetable IDs, get me all the fridges that have ALL of those vegetables in them (modifying the original QuerySet)."
How on earth would I do that without building a list of fridge IDs and adding an IN clause to my queryset?
Here are solutions that do it with IN clauses:
First query:
condiment_ids = [...] # list of condiment IDs
condiments = Condiment.objects.filter(
id__in=condiment_ids).all()
condiment_fridges = None
for condiment in condiments:
qs = condiment.fridge_set.all()
if not condiment_fridges:
condiment_fridges = qs
else:
condiment_fridges = condiment_fridges | qs
qs = qs.filter(id__in=[l.id for l in condiment_fridges])
Second query:
vegetable_ids = [...] # list of vegetable IDs
vegetables = vegetable.objects.filter(id__in=vegetable_ids).all()
vegetable_fridges = None
for vegetable in vegetables:
qs = vegetable.location_set.all()
if not vegetable_fridges:
vegetable_fridges = qs
else:
vegetable_fridges = vegetable_fridges & qs
qs = qs.filter(id__in=[l.id for l in vegetable_fridges])
These solutions seem horrible and hackish and I was wondering if there was a better way to do them with Django. Something like object HAS these objects or object HAS ALL of these objects.
Might be a good idea to take that to stackoverflow. My suggestion though would be to use that little sauce that we can't achieve programmatically yet. Human intelligence. How often do you think you're going to need this data ? How often does it change ?
Cache it based on that. Whatever way you do it, queries like this are expensive. If you need it often enough, throw it into memory and invalidate as necessary. More often than not, your cache invalidation will use less lines than what you wrote up there.
Also, don't be afraid to drop into raw sql if you really want to do something a certain way. The example you posted above clearly looks like the ORM working against you.
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)
I used to agree that mysql was far more pleasant than psql but I don't has been true since Pg 7.3. I get frustrated with mysql and having to go read the manual when I need to look up some syntax detail (in psql it's \h sql command, for example \h ALTER TABLE). Also the on-line help for psql is great. \? if you want a list of psql commands.
I would also say that if you see pgsql as a whole as being a waste of time for small things but MySQl as being the choice, the only justification for that can be lack of familiarity. I will say though (and I am totally biassed here by having worked in Pgsql increasingly over the last 12 years) that the more familiar I become with Pgsql the nicer it becomes, while the more familiar I become with MySQL the less I like it.
Is MySQL faster at handling IN than PSQL? Just because it says it's not well-supported in PSQL doesn't mean it is in MySQL. I don't know either way, but I'd like to.
I'm not sure how Django constructs the query, but it certainly does differently between MySQL and PostgreSql (and Oracle) since this is in the Django driver
>PSQL is sincerely a waste of time and energy for small things
As someone who has been using SQL server, postgresql, mysql, and oracle virtually every day for 13 years, I have to disagree. Postgresql is by far the easiest, most user-friendly one of the bunch. Mysql is full of tons of weird unexpected behaviours, and the command line mysql shell is so bad it is essentially useless.
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.