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
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 |