Re: profiling connection overhead

On 24.11.2010 07:07, Robert Haas wrote:
> Per previous threats, I spent some time tonight running oprofile
> (using the directions Tom Lane was foolish enough to provide me back
> in May). I took testlibpq.c and hacked it up to just connect to the
> server and then disconnect in a tight loop without doing anything
> useful, hoping to measure the overhead of starting up a new
> connection. Ha, ha, funny about that:
>
> 120899 18.0616 postgres AtProcExit_Buffers
> 56891 8.4992 libc-2.11.2.so memset
> 30987 4.6293 libc-2.11.2.so memcpy
> 26944 4.0253 postgres hash_search_with_hash_value
> 26554 3.9670 postgres AllocSetAlloc
> 20407 3.0487 libc-2.11.2.so _int_malloc
> 17269 2.5799 libc-2.11.2.so fread
> 13005 1.9429 ld-2.11.2.so do_lookup_x
> 11850 1.7703 ld-2.11.2.so _dl_fixup
> 10194 1.5229 libc-2.11.2.so _IO_file_xsgetn
>
> In English: the #1 overhead here is actually something that happens
> when processes EXIT, not when they start. Essentially all the time is
> in two lines:
>
> 56920 6.6006 : for (i = 0; i< NBuffers; i++)
> : {
> 98745 11.4507 : if (PrivateRefCount[i] != 0)

Oh, that's quite surprising.

> Anything we can do about this? That's a lot of overhead, and it'd be
> a lot worse on a big machine with 8GB of shared_buffers.

Micro-optimizing that search for the non-zero value helps a little bit
(attached). Reduces the percentage shown by oprofile from about 16% to
12% on my laptop.

For bigger gains, I think you need to somehow make the PrivateRefCount
smaller. Perhaps only use one byte for each buffer instead of int32, and
use some sort of an overflow list for the rare case that a buffer is
pinned more than 255 times. Or make it a hash table instead of a simple
lookup array. But whatever you do, you have to be very careful to not
add overhead to PinBuffer/UnPinBuffer, those can already be quite high
in oprofile reports of real applications. It might be worth
experimenting a bit, at the moment PrivateRefCount takes up 5MB of
memory per 1GB of shared_buffers. Machines with a high shared_buffers
setting have no shortage of memory, but a large array like that might
waste a lot of precious CPU cache.

Now, the other question is if this really matters. Even if we eliminate
that loop in AtProcExit_Buffers altogether, is connect/disconnect still
be so slow that you have to use a connection pooler if you do that a lot?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com