On Thu, May 31, 2012 at 11:54 AM, Sergey Koposov <kopo...@ast.cam.ac.uk> wrote: > On Thu, 31 May 2012, Robert Haas wrote: > >> Oh, ho. So from this we can see that the problem is that we're >> getting huge amounts of spinlock contention when pinning and unpinning >> index pages. >> >> It would be nice to have a self-contained reproducible test case for >> this, so that we could experiment with it on other systems. > > > I have created it a few days ago: > http://archives.postgresql.org/pgsql-hackers/2012-05/msg01143.php > > It is still valid. And I'm using exactly it to test. The only thing to > change is to create a two-col index and drop another index. > The scripts are precisely the ones I'm using now. > > The problem is that in order to see a really big slowdown (10 times slower > than a single thread) I've had to raise the buffers to 48g but it was slow > for smaller shared buffer settings as well. > > But I'm not sure how sensitive the test is to the hardware.
It's not: high contention on spinlocks is going to suck no matter what hardware you have. I think the problem is pretty obvious now: any case where multiple backends are scanning the same sequence of buffers in a very tight loop is going to display this behavior. It doesn't come up that often: it takes a pretty unusual sequence of events to get a bunch of backends hitting the same buffer like that. Hm, I wonder if you could alleviate the symptoms by making making the Pin/UnpinBuffer smarter so that frequently pinned buffers could stay pinned longer -- kinda as if your private ref count was hacked to be higher in that case. It would be a complex fix for a narrow issue though. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers