reducing the overhead of frequent table locks - now, with WIP patch - Mailing list pgsql-hackers
From | Robert Haas |
---|---|
Subject | reducing the overhead of frequent table locks - now, with WIP patch |
Date | |
Msg-id | BANLkTimVboKxzGS9BhL7XphBCr4iy-s0BQ@mail.gmail.com Whole thread Raw |
Responses |
Re: reducing the overhead of frequent table locks -
now, with WIP patch
Re: reducing the overhead of frequent table locks - now, with WIP patch Re: reducing the overhead of frequent table locks - now, with WIP patch Re: reducing the overhead of frequent table locks - now, with WIP patch Re: reducing the overhead of frequent table locks - now, with WIP patch Re: reducing the overhead of frequent table locks - now, with WIP patch Re: reducing the overhead of frequent table locks - now, with WIP patch |
List | pgsql-hackers |
I've now spent enough time working on this issue now to be convinced that the approach has merit, if we can work out the kinks. I'll start with some performance numbers. The case where the current system for taking table locks is really hurting us is where we have a large number of backends attempting to access a small number of relations. They all fight over the lock manager lock on whichever partition (or partitions) that relation (or those relations) fall in. Increasing the number of partitions doesn't help, because they are all trying to access the same object, and that object is only ever going to be in one partition. To exercise this case, I chose the following benchmark: pgbench -n -S -T 300 -c 36 -j 36. I first tested this on my MacBook Pro, with scale factor 10 and shared_buffers=400MB. Here are the results of alternating runs without and with the patch: tps = 23997.120971 (including connections establishing) tps = 25003.186860 (including connections establishing) tps = 23499.257892 (including connections establishing) tps = 24435.793773 (including connections establishing) tps = 23579.624360 (including connections establishing) tps = 24791.974810 (including connections establishing) As you can see, this works out to a bit more than a 4% improvement on this two-core box. I also got access (thanks to Nate Boley) to a 24-core box and ran the same test with scale factor 100 and shared_buffers=8GB. Here are the results of alternating runs without and with the patch on that machine: tps = 36291.996228 (including connections establishing) tps = 129242.054578 (including connections establishing) tps = 36704.393055 (including connections establishing) tps = 128998.648106 (including connections establishing) tps = 36531.208898 (including connections establishing) tps = 131341.367344 (including connections establishing) That's an improvement of about ~3.5x. According to the vmstat output, when running without the patch, the CPU state was about 40% idle. With the patch, it dropped down to around 6%. There are numerous problems with the code as it stands at this point. It crashes if you try to use 2PC, which means the regression tests fail; it probably does horrible things if you run out of shared memory; pg_locks knows nothing about the new mechanism (arguably, we could leave it that way: only locks that can't possibly be conflicting with anything can be taken using this mechanism, but it would be nice to fix, I think); and there are likely some other gotchas as well. Still, the basic mechanism appears to work. The code is attached, for anyone who may be curious. Known idiocies are marked with "ZZZ". The design was discussed on the previous thread ("reducing the overhead of frequent table locks"), q.v. There are some comments in the patch as well, but more is likely needed. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Attachment
pgsql-hackers by date: