Re: profiling connection overhead - Mailing list pgsql-hackers

From Heikki Linnakangas
Subject Re: profiling connection overhead
Date
Msg-id 4CECBA6D.4020802@enterprisedb.com
Whole thread Raw
In response to profiling connection overhead  (Robert Haas <robertmhaas@gmail.com>)
Responses Re: profiling connection overhead
List pgsql-hackers
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

Attachment

pgsql-hackers by date:

Previous
From: Radosław Smogura
Date:
Subject: JDBC and Binary protocol error, for some statements
Next
From: Pavel Stehule
Date:
Subject: Re: final patch - plpgsql: for-in-array