minimizing pg_stat_statements performance overhead

From: Raymond Martin <ramarti(at)microsoft(dot)com>
To: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: minimizing pg_stat_statements performance overhead
Date: 2019-03-27 00:33:49
Message-ID: BN8PR21MB1217B003C4F79DE230AA36B9B1580@BN8PR21MB1217.namprd21.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello hackers,
This email is regarding the Postgres pg_stat_statements extension.
I noticed that enabling pg_stat_statements can effect performance. I thought that changing the pg_stat_statements.track parameter to 'none' could reduce this overhead without requiring a restart to remove it from shared_preload_libraries. Changing this config did not improve performance as I expected. Looking over the code, I noticed that pg_stat_statements is not checking if it is enabled before executing the post_parse_analyze_hook function. Other hooks that require access to the pg_stat_statements query hash table (through the pgss_store function) check for pgss_enabled.
Would it make sense to check for pgss_enabled in the post_parse_analyze_hook function?
 
**Patching**
Making this change drastically improved performance while pg_stat_statement.track was set to NONE. This change allows me to more effectively enable/disable pg_stat_statements without requiring a restart.
Example patch:
@@ -783,8 +783,8 @@ pgss_post_parse_analyze(ParseState *pstate, Query *query)
        /* Assert we didn't do this already */
        Assert(query->queryId == 0);
 
-       /* Safety check... */
-       if (!pgss || !pgss_hash)
+       /* Safety check...and ensure that pgss is enabled before we do any work */
+       if (!pgss || !pgss_hash || !pgss_enabled())
                return;

**Simple Mini Benchmark**
I ran a simple test on my local machine with this spec: 16 core/32 GB memory/Windows Server 2016.
The simple query I used was 'select 1'. I called pg_stat_statements_reset() before each simple query to clear the pg_stat_statements query hash. The majority of the latency happens the first time a query is run.
Median runtime of 100 simple queries in milliseconds:
PGSS loaded (ms) PGSS loaded + this patch (ms)
track = top          0.53 0.55
track = none       0.41 0.20

PGSS not loaded: 0.18ms

--
Raymond Martin
ramarti(at)microsoft(dot)com
Azure Database for PostgreSQL

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tomas Vondra 2019-03-27 00:36:42 Re: basebackup checksum verification
Previous Message Andres Freund 2019-03-27 00:31:16 Re: basebackup checksum verification