Re: Single client performance on trivial SELECTs

On 14.04.2011 17:43, Tom Lane wrote:
> Greg Smith<greg@2ndquadrant.com>  writes:
>> samples  %        image name               symbol name
>> 53548     6.7609  postgres                 AllocSetAlloc
>> 32787     4.1396  postgres                 MemoryContextAllocZeroAligned
>> 26330     3.3244  postgres                 base_yyparse
>> 21723     2.7427  postgres                 hash_search_with_hash_value
>> 20831     2.6301  postgres                 SearchCatCache
>> 19094     2.4108  postgres                 hash_seq_search
>> 18402     2.3234  postgres                 hash_any
>> 15975     2.0170  postgres                 AllocSetFreeIndex
>> 14205     1.7935  postgres                 _bt_compareSince
>> 13370     1.6881  postgres                 core_yylex
>> 10455     1.3200  postgres                 MemoryContextAlloc
>> 10330     1.3042  postgres                 LockAcquireExtended
>> 10197     1.2875  postgres                 ScanKeywordLookup
>> 9312      1.1757  postgres                 MemoryContextAllocZero
>
> Yeah, this is pretty typical ...

In this case you could just use prepared statements and get rid of all 
the parser related overhead, which includes much of the allocations.

>> I don't know nearly enough about the memory allocator to comment on
>> whether it's possible to optimize it better for this case to relieve any
>> bottleneck.
>
> I doubt that it's possible to make AllocSetAlloc radically cheaper.
> I think the more likely route to improvement there is going to be to
> find a way to do fewer pallocs.  For instance, if we had more rigorous
> rules about which data structures are read-only to which code, we could
> probably get rid of a lot of just-in-case tree copying that happens in
> the parser and planner.
>
> But at the same time, even if we could drive all palloc costs to zero,
> it would only make a 10% difference in this example.  And this sort of
> fairly flat profile is what I see in most cases these days --- we've
> been playing performance whack-a-mole for long enough now that there
> isn't much low-hanging fruit left.  For better or worse, the system
> design we've chosen just isn't amenable to minimal overhead for simple
> queries.  I think a lot of this ultimately traces to the extensible,
> data-type-agnostic design philosophy.  The fact that we don't know what
> an integer is until we look in pg_type, and don't know what an "="
> operator does until we look up its properties, is great from a flexibility
> point of view; but this sort of query is where the costs become obvious.

I think the general strategy to make this kind of queries faster will be 
to add various fastpaths to cache and skip even more work. For example,

There's one very low-hanging fruit here, though. I profiled the pgbench 
case, with -M prepared, and found that like in Greg Smith's profile, 
hash_seq_search pops up quite high in the list. Those calls are coming 
from LockReleaseAll(), where we scan the local lock hash to find all 
locks held. We specify the initial size of the local lock hash table as 
128, which is unnecessarily large for small queries like this. Reducing 
it to 8 slashed the time spent in hash_seq_search().

I think we should make that hash table smaller. It won't buy much, 
somewhere between 1-5 % in this test case, but it's very easy to do and 
I don't see much downside, it's a local hash table so it will grow as 
needed.

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