Improve catcache/syscache performance.

From: Andres Freund <andres(at)anarazel(dot)de>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Improve catcache/syscache performance.
Date: 2017-09-14 06:12:07
Message-ID: 20170914061207.zxotvyopetm7lrrp@alap3.anarazel.de
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

Hi,

There's plenty workloads where SearchSysCache()/SearchCatCache() shows
up as a major runtime factor. Primarily in workloads with very fast
queries.

A fair while ago, before I had my commit bit, I'd posted [1]. Looking at
the profiles/benchmarks I was easily able to confirm that it still
helps, but that there's also still a lot left on the table.

Attached is a patch that tries to improve sys/catcache performance,
going further than the patch referenced earlier.

This primarily includes four pieces:

1) Avoidance of FunctionCallInfo based function calls, replaced by
more efficient functions with a native C argument interface.
2) Only initializing the ScanKey when necessary, i.e. catcache misses,
reduces cache unnecessary cpu cache misses.
3) Allowing the compiler to specialize critical SearchCatCache for a
specific number of attributes allows to unroll loops and avoid
other nkeys dependant initialization.
4) Split of the heap lookup from the hash lookup, reducing stack
allocations etc in the common case.

There's further potential:
- replace open coded hash with simplehash - the list walk right now
shows up in profiles.
- As oid is the only system column supported, avoid the use of
heap_getsysattr(), by adding an explicit branch for
ObjectIdAttributeNumber. This shows up in profiles.
- move cache initialization out of the search path
- add more proper functions, rather than macros for
SearchSysCacheCopyN etc., but right now they don't show up in profiles.

The reason the macro wrapper for syscache.c/h have to be changed,
rather than just catcache, is that doing otherwise would require
exposing the SysCache array to the outside. That might be a good idea
anyway, but it's for another day.

This patch gives me roughly 8% speedup in a workload that consists out
of a fast query that returns a lot of columns. If I apply a few
other performance patches, this patch itself starts to make a bigger
difference, of around 11%.

Greetings,

Andres Freund

[1] https://www.postgresql.org/message-id/20130905191323.GC490889@alap2.anarazel.de

Attachment Content-Type Size
0004-Add-inline-murmurhash32-int32-function.patch text/x-diff 2.3 KB
0006-Add-pg_noinline-macro-to-c.h.patch text/x-diff 1.4 KB
0007-Improve-sys-catcache-performance.patch text/x-diff 26.1 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2017-09-14 06:34:18 SendRowDescriptionMessage() is slow for queries with a lot of columns
Previous Message Andres Freund 2017-09-14 06:03:30 Re: More efficient truncation of pg_stat_activity query strings