public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ilia Evdokimov <[email protected]>
To: Sami Imseih <[email protected]>
To: Alexander Korotkov <[email protected]>
Cc: Andrey M. Borodin <[email protected]>
Cc: Alena Rybakina <[email protected]>
Cc: Michael Paquier <[email protected]>
Cc: Greg Sabino Mullane <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: Sample rate added to pg_stat_statements
Date: Thu, 23 Jan 2025 01:11:55 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<CAA5RZ0s=JH4owVr=Tfr4DYpFnBrYJjKoq2d7NqdkBQKY5whiRQ@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAA5RZ0sBsajHdE_HPoJ-C5rp8QmfArd6a6yJo6bDMVpyW25E+Q@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<CAA5RZ0vYu+dm3KKd5iaQ=LY0J2sjfxJG+-VCfkeVtWFuhSb-4A@mail.gmail.com>
<[email protected]>
<CAA5RZ0vCLbYfCuECC-wdsyG4VK=UD8wsqtw3xKK1_jZPFqUrYw@mail.gmail.com>
<[email protected]>
On 20.01.2025 17:20, Ilia Evdokimov wrote:
>
> On 15.01.2025 20:16, Sami Imseih wrote:
>>> Probably, but first I suggest benchmarking with sampling applied to
>>> all queries. If the results are good, we can later filter certain
>>> queries based on different characteristics.
>> Absolutely. The benchmark numbers to justify this feature are
>> the next step. Thanks for your work on this!
>>
>> Regards,
>>
>> Sami
>>
> Hi hackers,
>
> I’d like to share the results of my benchmark.
>
> To stress the spinlock in pg_stat_statements while incrementing
> counters for popular entries, it's important to use the same query
> repeatedly. To avoid overloading pgss with normalization, the queries
> should not contain constants. I found that using the query 'SELECT
> now()' works best for this purpose.
>
> I ran the benchmark on a machine with 48 CPUs, which may not be
> sufficient to fully test sampling, but I was able to achieve around
> 1.5 million TPS using 'SELECT now()'. To load the CPUs to about
> 85-90%, I ran pgbench with -c 45 -j 45, using a custom 'SELECT now()'
> in pgbench_script.sql file containing 'SELECT now()'. The benchmark
> was conducted on a newly created 'pgbench' database, , with processes
> like autovacuum, fsync, and checkpoints disabled(e.g., checkpoint
> _timeout = '24h').
>
> I tested various 'sample_rate' values and compared the performance
> with pgss both enabled and disabled. The detailed results are attached
> in the 'sample_rate.txt' file, along with pgbench progress reports
> every 10 seconds.
>
>
> pgbench -c 45 -j 45 -T 200 -f pgbench_script.sql --progress=10 pgbench
>
> sample_rate | num of transactions | lat | stddev | tps
>
> 1.0 | 1207127.572580 |0.037| 0.030 | 241400836
> 0.8 | 1403551.516338 |0.032| 0.031 | 280673286
> 0.5 | 1658596.614064 |0.027| 0.012 | 331679344
> 0.2 | 1757747.509476 |0.025| 0.008 | 351507156
> 0.0 | 1760055.986259 |0.025| 0.008 | 351965559
> pgss off | 1828743.791205 |0.024| 0.008 | 365703762
>
>
> If anyone has the capability to run this benchmark on machines with
> more CPUs or with different queries, it would be nice. I’d appreciate
> any suggestions or feedback.
>
> --.
> Best regards,
> Ilia Evdokimov,
> Tantor Labs LLC.
Hi hackers,
I'd like to share the results of a new benchmark I conducted using the
pgbench tool with the -M prepared -S flags!
Previously, I shared a benchmark focused on extreme high-load scenarios,
but now I’ve conducted a more realistic test case based on Alexander’s
suggestion [0].
Machine Characteristics:
* 48 CPUs
* 374 GB RAM
* 3 TB Disk
To avoid interference from background processes, I disabled autovacuum
and synchronous operations using the following settings in
postgresql.auto.conf:
autovacuum = off
fsync = off
checkpoint_timeout = '24h'
shared_preload_libraries = 'pg_stat_statements'
Create a test database:
createdb pgbench
pgbench -i -s 3000 pgbench
If pg_stat_statements is enabled, reset its hash table before starting
the benchmark:
psql -c 'SELECT pg_stat_statements_reset()'
Run the benchmark with the following command:
pgbench -c 46 -j 46 -T 200 -M prepared -S --progress=10 pgbench
Here are the results with and without pg_stat_statements, varying the
sample_rate parameter:
sample_rate | tps | lat | stddev | num of transactions
1.0 | 1299070.105018 |0.035| 0.055 | 259781895
0.8 | 1393327.873249 |0.033| 0.039 | 278630669
0.5 | 1420303.856480 |0.032| 0.037 | 284023396
0.2 | 1432482.672301 |0.032| 0.037 | 286461834
0.0 | 1760055.986259 |0.032| 0.036 | 290169584
pgss off | 1460920.805259 |0.031| 0.036 | 292144256
When pg_stat_statements is enabled, TPS decreases by about 10%. However,
by leveraging the new sampling feature (sample_rate), it is possible to
significantly mitigate this overhead. For detailed benchmark results
with the '-M prepared -S' flags, please see the attached file
'sample_rate_prepared.txt'
P.S. Previously, I shared results under extreme high-load conditions
using the query 'SELECT now()'. For clarity, I’m rewriting those results
for better readability:
sample_rate | tps | lat | stddev | num of transactions
1.0 | 1207127.572580 |0.037| 0.030 | 241400836
0.8 | 1403551.516338 |0.032| 0.031 | 280673286
0.5 | 1658596.614064 |0.027| 0.012 | 331679344
0.2 | 1757747.509476 |0.025| 0.008 | 351507156
0.0 | 1760055.986259 |0.025| 0.008 | 351965559
pgss off | 1828743.791205 |0.024| 0.008 | 365703762
Here, we observed a significant ~33% decrease in TPS when pgss was
enabled without sampling.
Any thoughts?
[0]:
https://www.postgresql.org/message-id/CAPpHfdsTKAQqC3A48-MGQhrhfEamXZPb64w%3Dutk7thQcOMNr7Q%40mail.g...
--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.
pg_stat_statements.sample_rate = 1.0
pgbench -c 46 -j 46 -T 200 -M prepared -S --progress=10 pgbench
pgbench (16.6)
starting vacuum...end.
progress: 10.0 s, 314978.6 tps, lat 0.146 ms stddev 0.257, 0 failed
progress: 20.0 s, 849655.3 tps, lat 0.054 ms stddev 0.108, 0 failed
progress: 30.0 s, 1338227.1 tps, lat 0.034 ms stddev 0.045, 0 failed
progress: 40.0 s, 1375800.0 tps, lat 0.033 ms stddev 0.042, 0 failed
progress: 50.0 s, 1383403.2 tps, lat 0.033 ms stddev 0.039, 0 failed
progress: 60.0 s, 1381348.2 tps, lat 0.033 ms stddev 0.044, 0 failed
progress: 70.0 s, 1386079.3 tps, lat 0.033 ms stddev 0.040, 0 failed
progress: 80.0 s, 1372314.9 tps, lat 0.033 ms stddev 0.040, 0 failed
progress: 90.0 s, 1383231.2 tps, lat 0.033 ms stddev 0.042, 0 failed
progress: 100.0 s, 1376702.3 tps, lat 0.033 ms stddev 0.042, 0 failed
progress: 110.0 s, 1375622.4 tps, lat 0.033 ms stddev 0.043, 0 failed
progress: 120.0 s, 1387577.0 tps, lat 0.033 ms stddev 0.040, 0 failed
progress: 130.0 s, 1383540.3 tps, lat 0.033 ms stddev 0.039, 0 failed
progress: 140.0 s, 1383138.6 tps, lat 0.033 ms stddev 0.040, 0 failed
progress: 150.0 s, 1377247.6 tps, lat 0.033 ms stddev 0.042, 0 failed
progress: 160.0 s, 1382830.9 tps, lat 0.033 ms stddev 0.039, 0 failed
progress: 170.0 s, 1383637.7 tps, lat 0.033 ms stddev 0.041, 0 failed
progress: 180.0 s, 1381811.7 tps, lat 0.033 ms stddev 0.041, 0 failed
progress: 190.0 s, 1382384.7 tps, lat 0.033 ms stddev 0.041, 0 failed
transaction type: <builtin: select only>
scaling factor: 3000
query mode: prepared
number of clients: 46
number of threads: 46
maximum number of tries: 1
duration: 200 s
number of transactions actually processed: 259781895
number of failed transactions: 0 (0.000%)
latency average = 0.035 ms
latency stddev = 0.055 ms
initial connection time = 26.448 ms
tps = 1299070.105018 (without initial connection time)
pgbench=# select query, calls from pg_stat_statements where calls > 1000;
query | calls
------------------------------------------------------+-----------
SELECT abalance FROM pgbench_accounts WHERE aid = $1 | 259781895
(1 row)
pgbench=# show pg_stat_statements.sample_rate ;
pg_stat_statements.sample_rate
--------------------------------
1
(1 row)
pgbench=# select count(*) from pg_stat_statements;
count
-------
130
(1 row)
========================================================================
pg_stat_statements.sample_rate = 0.8
pgbench -c 46 -j 46 -T 200 -M prepared -S --progress=10 pgbench
pgbench (16.6)
starting vacuum...end.
progress: 10.0 s, 319046.3 tps, lat 0.144 ms stddev 0.257, 0 failed
progress: 20.0 s, 893116.1 tps, lat 0.051 ms stddev 0.102, 0 failed
progress: 30.0 s, 1440539.1 tps, lat 0.032 ms stddev 0.025, 0 failed
progress: 40.0 s, 1484261.8 tps, lat 0.031 ms stddev 0.015, 0 failed
progress: 50.0 s, 1483957.5 tps, lat 0.031 ms stddev 0.018, 0 failed
progress: 60.0 s, 1491030.9 tps, lat 0.031 ms stddev 0.014, 0 failed
progress: 70.0 s, 1483171.0 tps, lat 0.031 ms stddev 0.014, 0 failed
progress: 80.0 s, 1475368.5 tps, lat 0.031 ms stddev 0.015, 0 failed
progress: 90.0 s, 1487719.7 tps, lat 0.031 ms stddev 0.015, 0 failed
progress: 100.0 s, 1485387.0 tps, lat 0.031 ms stddev 0.016, 0 failed
progress: 110.0 s, 1474178.4 tps, lat 0.031 ms stddev 0.020, 0 failed
progress: 120.0 s, 1489178.2 tps, lat 0.031 ms stddev 0.016, 0 failed
progress: 130.0 s, 1480493.7 tps, lat 0.031 ms stddev 0.015, 0 failed
progress: 140.0 s, 1477666.3 tps, lat 0.031 ms stddev 0.014, 0 failed
progress: 150.0 s, 1483067.4 tps, lat 0.031 ms stddev 0.016, 0 failed
progress: 160.0 s, 1480664.3 tps, lat 0.031 ms stddev 0.014, 0 failed
progress: 170.0 s, 1482848.3 tps, lat 0.031 ms stddev 0.019, 0 failed
progress: 180.0 s, 1482780.2 tps, lat 0.031 ms stddev 0.015, 0 failed
progress: 190.0 s, 1487093.0 tps, lat 0.031 ms stddev 0.015, 0 failed
transaction type: <builtin: select only>
scaling factor: 3000
query mode: prepared
number of clients: 46
number of threads: 46
maximum number of tries: 1
duration: 200 s
number of transactions actually processed: 278630669
number of failed transactions: 0 (0.000%)
latency average = 0.033 ms
latency stddev = 0.039 ms
initial connection time = 26.004 ms
tps = 1393327.873249 (without initial connection time)
pgbench=# select query, calls from pg_stat_statements where calls > 1000;
query | calls
------------------------------------------------------+-----------
SELECT abalance FROM pgbench_accounts WHERE aid = $1 | 222902226
(1 row)
pgbench=# show pg_stat_statements.sample_rate ;
pg_stat_statements.sample_rate
--------------------------------
0.8
(1 row)
pgbench=# select count(*) from pg_stat_statements;
count
-------
117
(1 row)
========================================================================
pg_stat_statements.sample_rate = 0.5
pgbench -c 46 -j 46 -T 200 -M prepared -S --progress=10 pgbench
pgbench (16.6)
starting vacuum...end.
progress: 10.0 s, 313361.7 tps, lat 0.146 ms stddev 0.263, 0 failed
progress: 20.0 s, 907177.0 tps, lat 0.051 ms stddev 0.103, 0 failed
progress: 30.0 s, 1467741.3 tps, lat 0.031 ms stddev 0.022, 0 failed
progress: 40.0 s, 1514111.4 tps, lat 0.030 ms stddev 0.009, 0 failed
progress: 50.0 s, 1508402.2 tps, lat 0.030 ms stddev 0.008, 0 failed
progress: 60.0 s, 1511752.6 tps, lat 0.030 ms stddev 0.008, 0 failed
progress: 70.0 s, 1505456.0 tps, lat 0.030 ms stddev 0.009, 0 failed
progress: 80.0 s, 1502666.4 tps, lat 0.030 ms stddev 0.008, 0 failed
progress: 90.0 s, 1512033.1 tps, lat 0.030 ms stddev 0.007, 0 failed
progress: 100.0 s, 1503996.7 tps, lat 0.030 ms stddev 0.007, 0 failed
progress: 110.0 s, 1506571.7 tps, lat 0.030 ms stddev 0.007, 0 failed
progress: 120.0 s, 1523809.6 tps, lat 0.030 ms stddev 0.013, 0 failed
progress: 130.0 s, 1515853.1 tps, lat 0.030 ms stddev 0.008, 0 failed
progress: 140.0 s, 1511753.9 tps, lat 0.030 ms stddev 0.010, 0 failed
progress: 150.0 s, 1511626.1 tps, lat 0.030 ms stddev 0.010, 0 failed
progress: 160.0 s, 1513088.5 tps, lat 0.030 ms stddev 0.008, 0 failed
progress: 170.0 s, 1520756.9 tps, lat 0.030 ms stddev 0.009, 0 failed
progress: 180.0 s, 1519851.7 tps, lat 0.030 ms stddev 0.010, 0 failed
progress: 190.0 s, 1517730.6 tps, lat 0.030 ms stddev 0.009, 0 failed
transaction type: <builtin: select only>
scaling factor: 3000
query mode: prepared
number of clients: 46
number of threads: 46
maximum number of tries: 1
duration: 200 s
number of transactions actually processed: 284023396
number of failed transactions: 0 (0.000%)
latency average = 0.032 ms
latency stddev = 0.037 ms
initial connection time = 27.242 ms
tps = 1420303.856480 (without initial connection time)
pgbench=# select query, calls from pg_stat_statements where calls > 1000;
query | calls
------------------------------------------------------+-----------
SELECT abalance FROM pgbench_accounts WHERE aid = $1 | 142014320
(1 row)
pgbench=# show pg_stat_statements.sample_rate ;
pg_stat_statements.sample_rate
--------------------------------
0.5
(1 row)
pgbench=# select count(*) from pg_stat_statements;
count
-------
83
(1 row)
========================================================================
pg_stat_statements.sample_rate = 0.2
pgbench -c 46 -j 46 -T 200 -M prepared -S --progress=10 pgbench
pgbench (16.6)
starting vacuum...end.
progress: 10.0 s, 305735.8 tps, lat 0.150 ms stddev 0.266, 0 failed
progress: 20.0 s, 886211.5 tps, lat 0.052 ms stddev 0.105, 0 failed
progress: 30.0 s, 1477449.3 tps, lat 0.031 ms stddev 0.023, 0 failed
progress: 40.0 s, 1533608.6 tps, lat 0.030 ms stddev 0.007, 0 failed
progress: 50.0 s, 1520686.0 tps, lat 0.030 ms stddev 0.007, 0 failed
progress: 60.0 s, 1521163.7 tps, lat 0.030 ms stddev 0.007, 0 failed
progress: 70.0 s, 1506093.3 tps, lat 0.030 ms stddev 0.007, 0 failed
progress: 80.0 s, 1521926.6 tps, lat 0.030 ms stddev 0.009, 0 failed
progress: 90.0 s, 1532715.4 tps, lat 0.030 ms stddev 0.006, 0 failed
progress: 100.0 s, 1536818.2 tps, lat 0.030 ms stddev 0.008, 0 failed
progress: 110.0 s, 1532156.5 tps, lat 0.030 ms stddev 0.008, 0 failed
progress: 120.0 s, 1531077.6 tps, lat 0.030 ms stddev 0.007, 0 failed
progress: 130.0 s, 1533445.0 tps, lat 0.030 ms stddev 0.011, 0 failed
progress: 140.0 s, 1530465.1 tps, lat 0.030 ms stddev 0.006, 0 failed
progress: 150.0 s, 1533264.8 tps, lat 0.030 ms stddev 0.006, 0 failed
progress: 160.0 s, 1531706.4 tps, lat 0.030 ms stddev 0.009, 0 failed
progress: 170.0 s, 1527768.1 tps, lat 0.030 ms stddev 0.006, 0 failed
progress: 180.0 s, 1523762.9 tps, lat 0.030 ms stddev 0.008, 0 failed
progress: 190.0 s, 1531264.7 tps, lat 0.030 ms stddev 0.008, 0 failed
transaction type: <builtin: select only>
scaling factor: 3000
query mode: prepared
number of clients: 46
number of threads: 46
maximum number of tries: 1
duration: 200 s
number of transactions actually processed: 286461834
number of failed transactions: 0 (0.000%)
latency average = 0.032 ms
latency stddev = 0.037 ms
initial connection time = 26.189 ms
tps = 1432482.672301 (without initial connection time)
pgbench=# select query, calls from pg_stat_statements where calls > 1000;
query | calls
------------------------------------------------------+----------
SELECT abalance FROM pgbench_accounts WHERE aid = $1 | 57294631
(1 row)
pgbench=# show pg_stat_statements.sample_rate ;
pg_stat_statements.sample_rate
--------------------------------
0.2
(1 row)
pgbench=# select count(*) from pg_stat_statements;
count
-------
56
(1 row)
========================================================================
pg_stat_statements.sample_rate = 0.0
pgbench -c 46 -j 46 -T 200 -M prepared -S --progress=10 pgbench
pgbench (16.6)
starting vacuum...end.
progress: 10.0 s, 308612.3 tps, lat 0.149 ms stddev 0.263, 0 failed
progress: 20.0 s, 904494.3 tps, lat 0.051 ms stddev 0.103, 0 failed
progress: 30.0 s, 1497609.8 tps, lat 0.031 ms stddev 0.023, 0 failed
progress: 40.0 s, 1549945.7 tps, lat 0.030 ms stddev 0.007, 0 failed
progress: 50.0 s, 1552001.6 tps, lat 0.030 ms stddev 0.009, 0 failed
progress: 60.0 s, 1550998.6 tps, lat 0.030 ms stddev 0.007, 0 failed
progress: 70.0 s, 1552726.1 tps, lat 0.029 ms stddev 0.006, 0 failed
progress: 80.0 s, 1551847.2 tps, lat 0.030 ms stddev 0.006, 0 failed
progress: 90.0 s, 1542375.5 tps, lat 0.030 ms stddev 0.008, 0 failed
progress: 100.0 s, 1552666.8 tps, lat 0.029 ms stddev 0.006, 0 failed
progress: 110.0 s, 1542779.7 tps, lat 0.030 ms stddev 0.010, 0 failed
progress: 120.0 s, 1533717.7 tps, lat 0.030 ms stddev 0.007, 0 failed
progress: 130.0 s, 1532413.9 tps, lat 0.030 ms stddev 0.007, 0 failed
progress: 140.0 s, 1544919.6 tps, lat 0.030 ms stddev 0.006, 0 failed
progress: 150.0 s, 1539568.5 tps, lat 0.030 ms stddev 0.006, 0 failed
progress: 160.0 s, 1551072.9 tps, lat 0.030 ms stddev 0.007, 0 failed
progress: 170.0 s, 1558352.5 tps, lat 0.029 ms stddev 0.009, 0 failed
progress: 180.0 s, 1556477.4 tps, lat 0.029 ms stddev 0.007, 0 failed
progress: 190.0 s, 1549669.6 tps, lat 0.030 ms stddev 0.007, 0 failed
transaction type: <builtin: select only>
scaling factor: 3000
query mode: prepared
number of clients: 46
number of threads: 46
maximum number of tries: 1
duration: 200 s
number of transactions actually processed: 290169584
number of failed transactions: 0 (0.000%)
latency average = 0.032 ms
latency stddev = 0.036 ms
initial connection time = 27.090 ms
tps = 1451036.547496 (without initial connection time)
pgbench=# select query, calls from pg_stat_statements where calls > 1000;
query | calls
-------+-------
(0 rows)
pgbench=# show pg_stat_statements.sample_rate ;
pg_stat_statements.sample_rate
--------------------------------
0
(1 row)
pgbench=# select count(*) from pg_stat_statements;
count
-------
0
(1 row)
========================================================================
pg_stat_statements is off
pgbench -c 46 -j 46 -T 200 -M prepared -S --progress=10 pgbench
pgbench (16.6)
starting vacuum...end.
progress: 10.0 s, 316410.4 tps, lat 0.145 ms stddev 0.257, 0 failed
progress: 20.0 s, 919027.2 tps, lat 0.050 ms stddev 0.103, 0 failed
progress: 30.0 s, 1513827.2 tps, lat 0.030 ms stddev 0.021, 0 failed
progress: 40.0 s, 1555707.4 tps, lat 0.029 ms stddev 0.006, 0 failed
progress: 50.0 s, 1552820.8 tps, lat 0.029 ms stddev 0.006, 0 failed
progress: 60.0 s, 1564985.9 tps, lat 0.029 ms stddev 0.006, 0 failed
progress: 70.0 s, 1559507.4 tps, lat 0.029 ms stddev 0.007, 0 failed
progress: 80.0 s, 1561871.2 tps, lat 0.029 ms stddev 0.006, 0 failed
progress: 90.0 s, 1563076.5 tps, lat 0.029 ms stddev 0.006, 0 failed
progress: 100.0 s, 1562030.4 tps, lat 0.029 ms stddev 0.006, 0 failed
progress: 110.0 s, 1555040.3 tps, lat 0.029 ms stddev 0.008, 0 failed
progress: 120.0 s, 1557295.8 tps, lat 0.029 ms stddev 0.008, 0 failed
progress: 130.0 s, 1552556.9 tps, lat 0.029 ms stddev 0.008, 0 failed
progress: 140.0 s, 1547155.3 tps, lat 0.030 ms stddev 0.006, 0 failed
progress: 150.0 s, 1543266.5 tps, lat 0.030 ms stddev 0.008, 0 failed
progress: 160.0 s, 1561086.8 tps, lat 0.029 ms stddev 0.007, 0 failed
progress: 170.0 s, 1558993.9 tps, lat 0.029 ms stddev 0.009, 0 failed
progress: 180.0 s, 1554616.8 tps, lat 0.029 ms stddev 0.006, 0 failed
progress: 190.0 s, 1557972.0 tps, lat 0.029 ms stddev 0.007, 0 failed
transaction type: <builtin: select only>
scaling factor: 3000
query mode: prepared
number of clients: 46
number of threads: 46
maximum number of tries: 1
duration: 200 s
number of transactions actually processed: 292144256
number of failed transactions: 0 (0.000%)
latency average = 0.031 ms
latency stddev = 0.036 ms
initial connection time = 29.085 ms
tps = 1460920.805259 (without initial connection time)
Attachments:
[text/plain] sample_rate_prepared.txt (13.5K, ../[email protected]/3-sample_rate_prepared.txt)
download | inline:
pg_stat_statements.sample_rate = 1.0
pgbench -c 46 -j 46 -T 200 -M prepared -S --progress=10 pgbench
pgbench (16.6)
starting vacuum...end.
progress: 10.0 s, 314978.6 tps, lat 0.146 ms stddev 0.257, 0 failed
progress: 20.0 s, 849655.3 tps, lat 0.054 ms stddev 0.108, 0 failed
progress: 30.0 s, 1338227.1 tps, lat 0.034 ms stddev 0.045, 0 failed
progress: 40.0 s, 1375800.0 tps, lat 0.033 ms stddev 0.042, 0 failed
progress: 50.0 s, 1383403.2 tps, lat 0.033 ms stddev 0.039, 0 failed
progress: 60.0 s, 1381348.2 tps, lat 0.033 ms stddev 0.044, 0 failed
progress: 70.0 s, 1386079.3 tps, lat 0.033 ms stddev 0.040, 0 failed
progress: 80.0 s, 1372314.9 tps, lat 0.033 ms stddev 0.040, 0 failed
progress: 90.0 s, 1383231.2 tps, lat 0.033 ms stddev 0.042, 0 failed
progress: 100.0 s, 1376702.3 tps, lat 0.033 ms stddev 0.042, 0 failed
progress: 110.0 s, 1375622.4 tps, lat 0.033 ms stddev 0.043, 0 failed
progress: 120.0 s, 1387577.0 tps, lat 0.033 ms stddev 0.040, 0 failed
progress: 130.0 s, 1383540.3 tps, lat 0.033 ms stddev 0.039, 0 failed
progress: 140.0 s, 1383138.6 tps, lat 0.033 ms stddev 0.040, 0 failed
progress: 150.0 s, 1377247.6 tps, lat 0.033 ms stddev 0.042, 0 failed
progress: 160.0 s, 1382830.9 tps, lat 0.033 ms stddev 0.039, 0 failed
progress: 170.0 s, 1383637.7 tps, lat 0.033 ms stddev 0.041, 0 failed
progress: 180.0 s, 1381811.7 tps, lat 0.033 ms stddev 0.041, 0 failed
progress: 190.0 s, 1382384.7 tps, lat 0.033 ms stddev 0.041, 0 failed
transaction type: <builtin: select only>
scaling factor: 3000
query mode: prepared
number of clients: 46
number of threads: 46
maximum number of tries: 1
duration: 200 s
number of transactions actually processed: 259781895
number of failed transactions: 0 (0.000%)
latency average = 0.035 ms
latency stddev = 0.055 ms
initial connection time = 26.448 ms
tps = 1299070.105018 (without initial connection time)
pgbench=# select query, calls from pg_stat_statements where calls > 1000;
query | calls
------------------------------------------------------+-----------
SELECT abalance FROM pgbench_accounts WHERE aid = $1 | 259781895
(1 row)
pgbench=# show pg_stat_statements.sample_rate ;
pg_stat_statements.sample_rate
--------------------------------
1
(1 row)
pgbench=# select count(*) from pg_stat_statements;
count
-------
130
(1 row)
========================================================================
pg_stat_statements.sample_rate = 0.8
pgbench -c 46 -j 46 -T 200 -M prepared -S --progress=10 pgbench
pgbench (16.6)
starting vacuum...end.
progress: 10.0 s, 319046.3 tps, lat 0.144 ms stddev 0.257, 0 failed
progress: 20.0 s, 893116.1 tps, lat 0.051 ms stddev 0.102, 0 failed
progress: 30.0 s, 1440539.1 tps, lat 0.032 ms stddev 0.025, 0 failed
progress: 40.0 s, 1484261.8 tps, lat 0.031 ms stddev 0.015, 0 failed
progress: 50.0 s, 1483957.5 tps, lat 0.031 ms stddev 0.018, 0 failed
progress: 60.0 s, 1491030.9 tps, lat 0.031 ms stddev 0.014, 0 failed
progress: 70.0 s, 1483171.0 tps, lat 0.031 ms stddev 0.014, 0 failed
progress: 80.0 s, 1475368.5 tps, lat 0.031 ms stddev 0.015, 0 failed
progress: 90.0 s, 1487719.7 tps, lat 0.031 ms stddev 0.015, 0 failed
progress: 100.0 s, 1485387.0 tps, lat 0.031 ms stddev 0.016, 0 failed
progress: 110.0 s, 1474178.4 tps, lat 0.031 ms stddev 0.020, 0 failed
progress: 120.0 s, 1489178.2 tps, lat 0.031 ms stddev 0.016, 0 failed
progress: 130.0 s, 1480493.7 tps, lat 0.031 ms stddev 0.015, 0 failed
progress: 140.0 s, 1477666.3 tps, lat 0.031 ms stddev 0.014, 0 failed
progress: 150.0 s, 1483067.4 tps, lat 0.031 ms stddev 0.016, 0 failed
progress: 160.0 s, 1480664.3 tps, lat 0.031 ms stddev 0.014, 0 failed
progress: 170.0 s, 1482848.3 tps, lat 0.031 ms stddev 0.019, 0 failed
progress: 180.0 s, 1482780.2 tps, lat 0.031 ms stddev 0.015, 0 failed
progress: 190.0 s, 1487093.0 tps, lat 0.031 ms stddev 0.015, 0 failed
transaction type: <builtin: select only>
scaling factor: 3000
query mode: prepared
number of clients: 46
number of threads: 46
maximum number of tries: 1
duration: 200 s
number of transactions actually processed: 278630669
number of failed transactions: 0 (0.000%)
latency average = 0.033 ms
latency stddev = 0.039 ms
initial connection time = 26.004 ms
tps = 1393327.873249 (without initial connection time)
pgbench=# select query, calls from pg_stat_statements where calls > 1000;
query | calls
------------------------------------------------------+-----------
SELECT abalance FROM pgbench_accounts WHERE aid = $1 | 222902226
(1 row)
pgbench=# show pg_stat_statements.sample_rate ;
pg_stat_statements.sample_rate
--------------------------------
0.8
(1 row)
pgbench=# select count(*) from pg_stat_statements;
count
-------
117
(1 row)
========================================================================
pg_stat_statements.sample_rate = 0.5
pgbench -c 46 -j 46 -T 200 -M prepared -S --progress=10 pgbench
pgbench (16.6)
starting vacuum...end.
progress: 10.0 s, 313361.7 tps, lat 0.146 ms stddev 0.263, 0 failed
progress: 20.0 s, 907177.0 tps, lat 0.051 ms stddev 0.103, 0 failed
progress: 30.0 s, 1467741.3 tps, lat 0.031 ms stddev 0.022, 0 failed
progress: 40.0 s, 1514111.4 tps, lat 0.030 ms stddev 0.009, 0 failed
progress: 50.0 s, 1508402.2 tps, lat 0.030 ms stddev 0.008, 0 failed
progress: 60.0 s, 1511752.6 tps, lat 0.030 ms stddev 0.008, 0 failed
progress: 70.0 s, 1505456.0 tps, lat 0.030 ms stddev 0.009, 0 failed
progress: 80.0 s, 1502666.4 tps, lat 0.030 ms stddev 0.008, 0 failed
progress: 90.0 s, 1512033.1 tps, lat 0.030 ms stddev 0.007, 0 failed
progress: 100.0 s, 1503996.7 tps, lat 0.030 ms stddev 0.007, 0 failed
progress: 110.0 s, 1506571.7 tps, lat 0.030 ms stddev 0.007, 0 failed
progress: 120.0 s, 1523809.6 tps, lat 0.030 ms stddev 0.013, 0 failed
progress: 130.0 s, 1515853.1 tps, lat 0.030 ms stddev 0.008, 0 failed
progress: 140.0 s, 1511753.9 tps, lat 0.030 ms stddev 0.010, 0 failed
progress: 150.0 s, 1511626.1 tps, lat 0.030 ms stddev 0.010, 0 failed
progress: 160.0 s, 1513088.5 tps, lat 0.030 ms stddev 0.008, 0 failed
progress: 170.0 s, 1520756.9 tps, lat 0.030 ms stddev 0.009, 0 failed
progress: 180.0 s, 1519851.7 tps, lat 0.030 ms stddev 0.010, 0 failed
progress: 190.0 s, 1517730.6 tps, lat 0.030 ms stddev 0.009, 0 failed
transaction type: <builtin: select only>
scaling factor: 3000
query mode: prepared
number of clients: 46
number of threads: 46
maximum number of tries: 1
duration: 200 s
number of transactions actually processed: 284023396
number of failed transactions: 0 (0.000%)
latency average = 0.032 ms
latency stddev = 0.037 ms
initial connection time = 27.242 ms
tps = 1420303.856480 (without initial connection time)
pgbench=# select query, calls from pg_stat_statements where calls > 1000;
query | calls
------------------------------------------------------+-----------
SELECT abalance FROM pgbench_accounts WHERE aid = $1 | 142014320
(1 row)
pgbench=# show pg_stat_statements.sample_rate ;
pg_stat_statements.sample_rate
--------------------------------
0.5
(1 row)
pgbench=# select count(*) from pg_stat_statements;
count
-------
83
(1 row)
========================================================================
pg_stat_statements.sample_rate = 0.2
pgbench -c 46 -j 46 -T 200 -M prepared -S --progress=10 pgbench
pgbench (16.6)
starting vacuum...end.
progress: 10.0 s, 305735.8 tps, lat 0.150 ms stddev 0.266, 0 failed
progress: 20.0 s, 886211.5 tps, lat 0.052 ms stddev 0.105, 0 failed
progress: 30.0 s, 1477449.3 tps, lat 0.031 ms stddev 0.023, 0 failed
progress: 40.0 s, 1533608.6 tps, lat 0.030 ms stddev 0.007, 0 failed
progress: 50.0 s, 1520686.0 tps, lat 0.030 ms stddev 0.007, 0 failed
progress: 60.0 s, 1521163.7 tps, lat 0.030 ms stddev 0.007, 0 failed
progress: 70.0 s, 1506093.3 tps, lat 0.030 ms stddev 0.007, 0 failed
progress: 80.0 s, 1521926.6 tps, lat 0.030 ms stddev 0.009, 0 failed
progress: 90.0 s, 1532715.4 tps, lat 0.030 ms stddev 0.006, 0 failed
progress: 100.0 s, 1536818.2 tps, lat 0.030 ms stddev 0.008, 0 failed
progress: 110.0 s, 1532156.5 tps, lat 0.030 ms stddev 0.008, 0 failed
progress: 120.0 s, 1531077.6 tps, lat 0.030 ms stddev 0.007, 0 failed
progress: 130.0 s, 1533445.0 tps, lat 0.030 ms stddev 0.011, 0 failed
progress: 140.0 s, 1530465.1 tps, lat 0.030 ms stddev 0.006, 0 failed
progress: 150.0 s, 1533264.8 tps, lat 0.030 ms stddev 0.006, 0 failed
progress: 160.0 s, 1531706.4 tps, lat 0.030 ms stddev 0.009, 0 failed
progress: 170.0 s, 1527768.1 tps, lat 0.030 ms stddev 0.006, 0 failed
progress: 180.0 s, 1523762.9 tps, lat 0.030 ms stddev 0.008, 0 failed
progress: 190.0 s, 1531264.7 tps, lat 0.030 ms stddev 0.008, 0 failed
transaction type: <builtin: select only>
scaling factor: 3000
query mode: prepared
number of clients: 46
number of threads: 46
maximum number of tries: 1
duration: 200 s
number of transactions actually processed: 286461834
number of failed transactions: 0 (0.000%)
latency average = 0.032 ms
latency stddev = 0.037 ms
initial connection time = 26.189 ms
tps = 1432482.672301 (without initial connection time)
pgbench=# select query, calls from pg_stat_statements where calls > 1000;
query | calls
------------------------------------------------------+----------
SELECT abalance FROM pgbench_accounts WHERE aid = $1 | 57294631
(1 row)
pgbench=# show pg_stat_statements.sample_rate ;
pg_stat_statements.sample_rate
--------------------------------
0.2
(1 row)
pgbench=# select count(*) from pg_stat_statements;
count
-------
56
(1 row)
========================================================================
pg_stat_statements.sample_rate = 0.0
pgbench -c 46 -j 46 -T 200 -M prepared -S --progress=10 pgbench
pgbench (16.6)
starting vacuum...end.
progress: 10.0 s, 308612.3 tps, lat 0.149 ms stddev 0.263, 0 failed
progress: 20.0 s, 904494.3 tps, lat 0.051 ms stddev 0.103, 0 failed
progress: 30.0 s, 1497609.8 tps, lat 0.031 ms stddev 0.023, 0 failed
progress: 40.0 s, 1549945.7 tps, lat 0.030 ms stddev 0.007, 0 failed
progress: 50.0 s, 1552001.6 tps, lat 0.030 ms stddev 0.009, 0 failed
progress: 60.0 s, 1550998.6 tps, lat 0.030 ms stddev 0.007, 0 failed
progress: 70.0 s, 1552726.1 tps, lat 0.029 ms stddev 0.006, 0 failed
progress: 80.0 s, 1551847.2 tps, lat 0.030 ms stddev 0.006, 0 failed
progress: 90.0 s, 1542375.5 tps, lat 0.030 ms stddev 0.008, 0 failed
progress: 100.0 s, 1552666.8 tps, lat 0.029 ms stddev 0.006, 0 failed
progress: 110.0 s, 1542779.7 tps, lat 0.030 ms stddev 0.010, 0 failed
progress: 120.0 s, 1533717.7 tps, lat 0.030 ms stddev 0.007, 0 failed
progress: 130.0 s, 1532413.9 tps, lat 0.030 ms stddev 0.007, 0 failed
progress: 140.0 s, 1544919.6 tps, lat 0.030 ms stddev 0.006, 0 failed
progress: 150.0 s, 1539568.5 tps, lat 0.030 ms stddev 0.006, 0 failed
progress: 160.0 s, 1551072.9 tps, lat 0.030 ms stddev 0.007, 0 failed
progress: 170.0 s, 1558352.5 tps, lat 0.029 ms stddev 0.009, 0 failed
progress: 180.0 s, 1556477.4 tps, lat 0.029 ms stddev 0.007, 0 failed
progress: 190.0 s, 1549669.6 tps, lat 0.030 ms stddev 0.007, 0 failed
transaction type: <builtin: select only>
scaling factor: 3000
query mode: prepared
number of clients: 46
number of threads: 46
maximum number of tries: 1
duration: 200 s
number of transactions actually processed: 290169584
number of failed transactions: 0 (0.000%)
latency average = 0.032 ms
latency stddev = 0.036 ms
initial connection time = 27.090 ms
tps = 1451036.547496 (without initial connection time)
pgbench=# select query, calls from pg_stat_statements where calls > 1000;
query | calls
-------+-------
(0 rows)
pgbench=# show pg_stat_statements.sample_rate ;
pg_stat_statements.sample_rate
--------------------------------
0
(1 row)
pgbench=# select count(*) from pg_stat_statements;
count
-------
0
(1 row)
========================================================================
pg_stat_statements is off
pgbench -c 46 -j 46 -T 200 -M prepared -S --progress=10 pgbench
pgbench (16.6)
starting vacuum...end.
progress: 10.0 s, 316410.4 tps, lat 0.145 ms stddev 0.257, 0 failed
progress: 20.0 s, 919027.2 tps, lat 0.050 ms stddev 0.103, 0 failed
progress: 30.0 s, 1513827.2 tps, lat 0.030 ms stddev 0.021, 0 failed
progress: 40.0 s, 1555707.4 tps, lat 0.029 ms stddev 0.006, 0 failed
progress: 50.0 s, 1552820.8 tps, lat 0.029 ms stddev 0.006, 0 failed
progress: 60.0 s, 1564985.9 tps, lat 0.029 ms stddev 0.006, 0 failed
progress: 70.0 s, 1559507.4 tps, lat 0.029 ms stddev 0.007, 0 failed
progress: 80.0 s, 1561871.2 tps, lat 0.029 ms stddev 0.006, 0 failed
progress: 90.0 s, 1563076.5 tps, lat 0.029 ms stddev 0.006, 0 failed
progress: 100.0 s, 1562030.4 tps, lat 0.029 ms stddev 0.006, 0 failed
progress: 110.0 s, 1555040.3 tps, lat 0.029 ms stddev 0.008, 0 failed
progress: 120.0 s, 1557295.8 tps, lat 0.029 ms stddev 0.008, 0 failed
progress: 130.0 s, 1552556.9 tps, lat 0.029 ms stddev 0.008, 0 failed
progress: 140.0 s, 1547155.3 tps, lat 0.030 ms stddev 0.006, 0 failed
progress: 150.0 s, 1543266.5 tps, lat 0.030 ms stddev 0.008, 0 failed
progress: 160.0 s, 1561086.8 tps, lat 0.029 ms stddev 0.007, 0 failed
progress: 170.0 s, 1558993.9 tps, lat 0.029 ms stddev 0.009, 0 failed
progress: 180.0 s, 1554616.8 tps, lat 0.029 ms stddev 0.006, 0 failed
progress: 190.0 s, 1557972.0 tps, lat 0.029 ms stddev 0.007, 0 failed
transaction type: <builtin: select only>
scaling factor: 3000
query mode: prepared
number of clients: 46
number of threads: 46
maximum number of tries: 1
duration: 200 s
number of transactions actually processed: 292144256
number of failed transactions: 0 (0.000%)
latency average = 0.031 ms
latency stddev = 0.036 ms
initial connection time = 29.085 ms
tps = 1460920.805259 (without initial connection time)
view thread (58+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Sample rate added to pg_stat_statements
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox