Re: Re: bulk_multi_insert infinite loops with large rows and small fill factors

On Wed, 12 Dec 2012 14:23:12 +0200
Heikki Linnakangas <hlinnakangas(at)vmware(dot)com> wrote:

> The bug's been fixed now, but note that huge tuples like this will
> always cause the table to be extended. Even if there are completely
> empty pages in the table, after a vacuum. Even a completely empty
> existing page is not considered spacious enough in this case, because
> it's still too small when you take fillfactor into account, so the
> insertion will always extend the table. If you regularly run into this
> situation, you might want to raise your fillfactor..

Actually, we'd like it lower. Ideally, one row per page.

We lose noticable performance when we raise fill-factor above 10. Even 20 is
slower.

During busy times these hosts sometimes fall into a stable state
with very high cpu use mostly in s_lock() and LWLockAcquire() and I think
PinBuffer plus very high system cpu in the scheduler (I don't have the perf
trace in front of me so take this with a grain of salt). In this mode they
fall from the normal 7000 queries per second to below 3000. Once in this
state they tend to stay that way. If we turn down the number of incoming
requests they go back to normal. Our conjecture is that most requests are
for only a few keys and so we have multiple sessions contending for few
pages and convoying in the buffer manager. The table is under 20k rows, but
the hot items are probably only a couple hundred different rows. The busy
processes are doing reads only, but there is some update activity on this
table too.

Ah, found an email with the significant part of the perf output:

> ... set number of client threads = number of postgres backends = 70. That way
> all my threads have constant access to a backend and they just spin in a tight
> loop running the same query over and over (with different values). ... this
> seems to have tapped into 9.2's resonant frequency, right now we're spending
> almost all our time spin locking.
...
> 762377.00 71.0% s_lock /usr/local/bin/postgres
> 22279.00 2.1% LWLockAcquire /usr/local/bin/postgres
> 18916.00 1.8% LWLockRelease /usr/local/bin/postgres

I was trying to resurrect the pthread s_lock() patch to see if that helps,
but it did not apply at all and I have not had time to persue it.

We have tried lots of number of processes and get the best result with
about ten less active postgresql backends than HT cores. System is 128GB
with:

processor : 79
vendor_id : GenuineIntel
cpu family : 6
model : 47
model name : Intel(R) Xeon(R) CPU E7-L8867 @ 2.13GHz
stepping : 2
cpu MHz : 2128.478
cache size : 30720 KB

-dg

--
David Gould 510 282 0869 daveg(at)sonic(dot)net
If simplicity worked, the world would be overrun with insects.