pg_stat_monitor: Benchmark pg_stat_monitor.

Description

We need to benchmark pg_stat_monitor to see the performance impact of the extension. The benchmark should be done on an AWS machine with millions of queries. There should be three different benchmarks required.

  1. Vanilla PostgreSQL

  2. pg_stat_statments

  3. pg_stat_monitor.

We need to add specifically what we need to benchmark here. How many concurrent users, databases, connections, check-world scenario, TPC-B/C/H?

How to document

None

How to test

None

Attachments

1

Activity

Show:

Ibrar Ahmed January 2, 2023 at 12:05 PM

This script runs the installcheck-world target 100 times, and measure the time it takes to run each iteration. It compares the time it takes to run with vanilla PostgreSQL, with pg_stat_monitor enabled, with both pg_stat_statements and pg_stat_monitor enabled, and with both extensions disabled. It restarts the PostgreSQL server every time the postgresql.conf file is modified. At the end of each iteration, it prints the elapsed time in nanoseconds.

 

Ibrar Ahmed December 21, 2022 at 1:36 PM

We keep that open because we will repeat that after 2.0 release.

Ibrar Ahmed November 11, 2022 at 12:09 PM

Finally, a long-awaited pg_stat_monitor benchmark is done. The benchmark is done on AWS large
instance. Multiple options have been done, Vanilla PostgreSQL, pg_stat_monitor and
pg_stat_statments. One good news is that pg_stat_monitor works well with millions of
queries and transactions in multiple threads. There was no memory leak or crash. That
benchmark not only did the benchmark but also did some stress testing and pg_stat_monitor passed
the test. The benchmark results are also excellent. In a large number of transactions, pg_stat_monitor
did well than pg_stat_statments. Is it surprising if we think that pg_stat_monitor has more 
functionalities than pg_stat_statments? But there are some tricks we have done which improve the
performance of pg_stat_monito, such as (1) pg_stat_monitor saving the queries in shared_buffer but 
pg_stat_statments on disk. (2) There is no query eviction cost in pg_stat_monitor. In fewer
transactions, pg_stat_statments works well than pg_stat_monitor because of obvious reasons. I
have a performance patch, which will improve the performance more, and its results will
become available tomorrow.
 
Here are the results.
 

Transactions

PostgreSQL Vanilla (s)

pg_stat_statments (s)

pg_stat_monitor (s)

65500

33

39

41

655000

365

380

390

32500000

19800

24900

24840

Done

Details

Assignee

Reporter

Components

Fix versions

Affects versions

Priority

Smart Checklist

Created November 11, 2022 at 12:08 PM
Updated July 10, 2024 at 10:58 AM
Resolved March 29, 2023 at 10:16 AM