Re: profiling pgbench

On Wednesday 24 November 2010 21:24:43 Robert Haas wrote:
> I'd like to get access to a box with (a lot) more cores, to see
> whether the lock stuff moves up in the profile. A big chunk of that
> hash_search_with_hash_value overhead is coming from
> LockAcquireExtended. The __strcmp_sse2 is almost entirely parsing
> overhead. In general, I'm not sure there's much hope for reducing the
> parsing overhead, although ScanKeywordLookup() can certainly be done
> better. XLogInsert() is spending a lot of time doing CRC's.
> LWLockAcquire() is dropping cycles in many different places.
I can get you profiles of machines with up two 24 real cores, unfortunately I
can't give access away.

Regarding CRCs:
I spent some time optimizing these, as you might remember. The wall I hit
optimizing it benefit-wise is that the single CRC calls (4 for a non-indexed
single-row insert on a table with 1 column inside a transaction) are just too
damn small to get more efficient. Its causing pipeline stalls all over...
(21, 5, 1, 28 bytes).

I have a very preliminary patch calculating the CRC over the whole thing in
one go if it can do so (no switch, no xl buffers wraparound), but its highly
ugly as it needs to read from the xl insert buffers and then reinsert the crc
at the correct position.
While it shows a noticable improvement, that doesn't seem to be a good way to
go. It could be made to work properly though.

I played around with some ideas to do that more nicely, but none were
gratifying.

Recarding LWLockAcquire costs:
Yes, its pretty noticeable - on loads of different usages. On a bunch of
production machines its the second (begind XLogInsert) on some the most
expensive function. Most of the time

All of those machines are Nehalems though, so the image may be a bit
distorted.

Andres