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.
pg_stat_statements.sample_rate = 1.0
cpu = 85%
pgbench -c 45 -j 45 -T 200 -f pgbench_script.sql --progress=10 pgbench
pgbench (16.6)
starting vacuum...end.
progress: 10.0 s, 1200445.1 tps, lat 0.037 ms stddev 0.038, 0 failed
progress: 20.0 s, 1232634.5 tps, lat 0.036 ms stddev 0.030, 0 failed
progress: 30.0 s, 1209760.7 tps, lat 0.037 ms stddev 0.027, 0 failed
progress: 40.0 s, 1204625.5 tps, lat 0.037 ms stddev 0.026, 0 failed
progress: 50.0 s, 1206608.7 tps, lat 0.037 ms stddev 0.027, 0 failed
progress: 60.0 s, 1214047.9 tps, lat 0.037 ms stddev 0.034, 0 failed
progress: 70.0 s, 1222126.5 tps, lat 0.037 ms stddev 0.036, 0 failed
progress: 80.0 s, 1198452.9 tps, lat 0.037 ms stddev 0.030, 0 failed
progress: 90.0 s, 1198871.7 tps, lat 0.037 ms stddev 0.027, 0 failed
progress: 100.0 s, 1200786.9 tps, lat 0.037 ms stddev 0.028, 0 failed
progress: 110.0 s, 1200419.5 tps, lat 0.037 ms stddev 0.030, 0 failed
progress: 120.0 s, 1208394.4 tps, lat 0.037 ms stddev 0.030, 0 failed
progress: 130.0 s, 1199204.3 tps, lat 0.037 ms stddev 0.028, 0 failed
progress: 140.0 s, 1213919.6 tps, lat 0.037 ms stddev 0.037, 0 failed
progress: 150.0 s, 1195670.1 tps, lat 0.037 ms stddev 0.028, 0 failed
progress: 160.0 s, 1190519.2 tps, lat 0.038 ms stddev 0.029, 0 failed
progress: 170.0 s, 1213450.1 tps, lat 0.037 ms stddev 0.027, 0 failed
progress: 180.0 s, 1220433.0 tps, lat 0.037 ms stddev 0.029, 0 failed
progress: 190.0 s, 1199628.7 tps, lat 0.037 ms stddev 0.028, 0 failed
transaction type: pgbench_script.sql
scaling factor: 1
query mode: simple
number of clients: 45
number of threads: 45
maximum number of tries: 1
duration: 200 s
number of transactions actually processed: 241400836
number of failed transactions: 0 (0.000%)
latency average = 0.037 ms
latency stddev = 0.030 ms
initial connection time = 25.051 ms
tps = 1207127.572580 (without initial connection time)
======================================================================
pg_stat_statements.sample_rate = 0.8
cpu = 90%
pgbench -c 45 -j 45 -T 200 -f pgbench_script.sql --progress=10 pgbench
pgbench (16.6)
starting vacuum...end.
progress: 10.0 s, 1429922.8 tps, lat 0.031 ms stddev 0.028, 0 failed
progress: 20.0 s, 1200380.2 tps, lat 0.037 ms stddev 0.029, 0 failed
progress: 30.0 s, 1198102.6 tps, lat 0.037 ms stddev 0.027, 0 failed
progress: 40.0 s, 1225912.9 tps, lat 0.037 ms stddev 0.028, 0 failed
progress: 50.0 s, 1234056.2 tps, lat 0.036 ms stddev 0.027, 0 failed
progress: 60.0 s, 1234894.6 tps, lat 0.036 ms stddev 0.027, 0 failed
progress: 70.0 s, 1235032.9 tps, lat 0.036 ms stddev 0.027, 0 failed
progress: 80.0 s, 1548607.9 tps, lat 0.029 ms stddev 0.035, 0 failed
progress: 90.0 s, 1553966.9 tps, lat 0.029 ms stddev 0.031, 0 failed
progress: 100.0 s, 1354695.2 tps, lat 0.033 ms stddev 0.030, 0 failed
progress: 110.0 s, 1238990.4 tps, lat 0.036 ms stddev 0.027, 0 failed
progress: 120.0 s, 1246169.7 tps, lat 0.036 ms stddev 0.027, 0 failed
progress: 130.0 s, 1234562.5 tps, lat 0.036 ms stddev 0.028, 0 failed
progress: 140.0 s, 1544423.2 tps, lat 0.029 ms stddev 0.033, 0 failed
progress: 150.0 s, 1594321.7 tps, lat 0.028 ms stddev 0.034, 0 failed
progress: 160.0 s, 1597711.2 tps, lat 0.028 ms stddev 0.035, 0 failed
progress: 170.0 s, 1605669.6 tps, lat 0.028 ms stddev 0.034, 0 failed
progress: 180.0 s, 1595276.5 tps, lat 0.028 ms stddev 0.033, 0 failed
progress: 190.0 s, 1600233.0 tps, lat 0.028 ms stddev 0.033, 0 failed
transaction type: pgbench_script.sql
scaling factor: 1
query mode: simple
number of clients: 45
number of threads: 45
maximum number of tries: 1
duration: 200 s
number of transactions actually processed: 280673286
number of failed transactions: 0 (0.000%)
latency average = 0.032 ms
latency stddev = 0.031 ms
initial connection time = 27.482 ms
tps = 1403551.516338 (without initial connection time)
======================================================================
pg_stat_statements.sample_rate = 0.5
cpu = 93%
pgbench -c 45 -j 45 -T 200 -f pgbench_script.sql --progress=10 pgbench
pgbench (16.6)
starting vacuum...end.
progress: 10.0 s, 1678693.4 tps, lat 0.027 ms stddev 0.012, 0 failed
progress: 20.0 s, 1688327.0 tps, lat 0.026 ms stddev 0.010, 0 failed
progress: 30.0 s, 1695953.0 tps, lat 0.026 ms stddev 0.010, 0 failed
progress: 40.0 s, 1711265.8 tps, lat 0.026 ms stddev 0.011, 0 failed
progress: 50.0 s, 1687865.3 tps, lat 0.026 ms stddev 0.011, 0 failed
progress: 60.0 s, 1682873.3 tps, lat 0.027 ms stddev 0.010, 0 failed
progress: 70.0 s, 1683221.7 tps, lat 0.027 ms stddev 0.011, 0 failed
progress: 80.0 s, 1688871.2 tps, lat 0.026 ms stddev 0.010, 0 failed
progress: 90.0 s, 1697633.3 tps, lat 0.026 ms stddev 0.010, 0 failed
progress: 100.0 s, 1690004.5 tps, lat 0.026 ms stddev 0.012, 0 failed
progress: 110.0 s, 1676941.1 tps, lat 0.027 ms stddev 0.011, 0 failed
progress: 120.0 s, 1692659.0 tps, lat 0.026 ms stddev 0.010, 0 failed
progress: 130.0 s, 1690660.0 tps, lat 0.026 ms stddev 0.011, 0 failed
progress: 140.0 s, 1710984.9 tps, lat 0.026 ms stddev 0.009, 0 failed
progress: 150.0 s, 1686285.0 tps, lat 0.026 ms stddev 0.010, 0 failed
progress: 160.0 s, 1691694.3 tps, lat 0.026 ms stddev 0.012, 0 failed
progress: 170.0 s, 1718431.1 tps, lat 0.026 ms stddev 0.011, 0 failed
progress: 180.0 s, 1671634.2 tps, lat 0.027 ms stddev 0.011, 0 failed
progress: 190.0 s, 1481564.6 tps, lat 0.030 ms stddev 0.019, 0 failed
transaction type: pgbench_script.sql
scaling factor: 1
query mode: simple
number of clients: 45
number of threads: 45
maximum number of tries: 1
duration: 200 s
number of transactions actually processed: 331679344
number of failed transactions: 0 (0.000%)
latency average = 0.027 ms
latency stddev = 0.012 ms
initial connection time = 25.117 ms
tps = 1658596.614064 (without initial connection time)
======================================================================
pg_stat_statements.sample_rate = 0.2
cpu = 94%
pgbench -c 45 -j 45 -T 200 -f pgbench_script.sql --progress=10 pgbench
pgbench (16.6)
starting vacuum...end.
progress: 10.0 s, 1693957.1 tps, lat 0.026 ms stddev 0.013, 0 failed
progress: 20.0 s, 1768328.5 tps, lat 0.025 ms stddev 0.007, 0 failed
progress: 30.0 s, 1778577.8 tps, lat 0.025 ms stddev 0.007, 0 failed
progress: 40.0 s, 1787534.0 tps, lat 0.025 ms stddev 0.007, 0 failed
progress: 50.0 s, 1781983.5 tps, lat 0.025 ms stddev 0.007, 0 failed
progress: 60.0 s, 1767252.1 tps, lat 0.025 ms stddev 0.008, 0 failed
progress: 70.0 s, 1761202.6 tps, lat 0.025 ms stddev 0.007, 0 failed
progress: 80.0 s, 1779318.0 tps, lat 0.025 ms stddev 0.008, 0 failed
progress: 90.0 s, 1769644.7 tps, lat 0.025 ms stddev 0.010, 0 failed
progress: 100.0 s, 1740872.8 tps, lat 0.026 ms stddev 0.009, 0 failed
progress: 110.0 s, 1761984.8 tps, lat 0.025 ms stddev 0.007, 0 failed
progress: 120.0 s, 1729532.1 tps, lat 0.026 ms stddev 0.008, 0 failed
progress: 130.0 s, 1756819.5 tps, lat 0.025 ms stddev 0.007, 0 failed
progress: 140.0 s, 1727874.3 tps, lat 0.026 ms stddev 0.009, 0 failed
progress: 150.0 s, 1734178.7 tps, lat 0.026 ms stddev 0.010, 0 failed
progress: 160.0 s, 1765601.8 tps, lat 0.025 ms stddev 0.007, 0 failed
progress: 170.0 s, 1764996.4 tps, lat 0.025 ms stddev 0.009, 0 failed
progress: 180.0 s, 1783042.6 tps, lat 0.025 ms stddev 0.008, 0 failed
progress: 190.0 s, 1750719.6 tps, lat 0.026 ms stddev 0.008, 0 failed
transaction type: pgbench_script.sql
scaling factor: 1
query mode: simple
number of clients: 45
number of threads: 45
maximum number of tries: 1
duration: 200 s
number of transactions actually processed: 351507156
number of failed transactions: 0 (0.000%)
latency average = 0.025 ms
latency stddev = 0.008 ms
initial connection time = 25.166 ms
tps = 1757747.509476 (without initial connection time)
======================================================================
pg_stat_statements.sample_rate = 0.0
cpu = 94%
pgbench -c 45 -j 45 -T 200 -f pgbench_script.sql --progress=10 pgbench
pgbench (16.6)
starting vacuum...end.
progress: 10.0 s, 1730860.9 tps, lat 0.026 ms stddev 0.011, 0 failed
progress: 20.0 s, 1789006.9 tps, lat 0.025 ms stddev 0.007, 0 failed
progress: 30.0 s, 1746421.7 tps, lat 0.026 ms stddev 0.007, 0 failed
progress: 40.0 s, 1761420.1 tps, lat 0.025 ms stddev 0.007, 0 failed
progress: 50.0 s, 1749654.5 tps, lat 0.026 ms stddev 0.007, 0 failed
progress: 60.0 s, 1753652.6 tps, lat 0.025 ms stddev 0.008, 0 failed
progress: 70.0 s, 1737531.5 tps, lat 0.026 ms stddev 0.007, 0 failed
progress: 80.0 s, 1772362.2 tps, lat 0.025 ms stddev 0.007, 0 failed
progress: 90.0 s, 1743025.0 tps, lat 0.026 ms stddev 0.008, 0 failed
progress: 100.0 s, 1768115.7 tps, lat 0.025 ms stddev 0.008, 0 failed
progress: 110.0 s, 1768149.9 tps, lat 0.025 ms stddev 0.007, 0 failed
progress: 120.0 s, 1767274.2 tps, lat 0.025 ms stddev 0.007, 0 failed
progress: 130.0 s, 1737409.1 tps, lat 0.026 ms stddev 0.007, 0 failed
progress: 140.0 s, 1764889.8 tps, lat 0.025 ms stddev 0.008, 0 failed
progress: 150.0 s, 1759014.0 tps, lat 0.025 ms stddev 0.009, 0 failed
progress: 160.0 s, 1765296.9 tps, lat 0.025 ms stddev 0.010, 0 failed
progress: 170.0 s, 1762959.2 tps, lat 0.025 ms stddev 0.008, 0 failed
progress: 180.0 s, 1767145.3 tps, lat 0.025 ms stddev 0.007, 0 failed
progress: 190.0 s, 1772519.8 tps, lat 0.025 ms stddev 0.008, 0 failed
transaction type: pgbench_script.sql
scaling factor: 1
query mode: simple
number of clients: 45
number of threads: 45
maximum number of tries: 1
duration: 200 s
number of transactions actually processed: 351965559
number of failed transactions: 0 (0.000%)
latency average = 0.025 ms
latency stddev = 0.008 ms
initial connection time = 26.944 ms
tps = 1760055.986259 (without initial connection time)
======================================================================
pg_stat_statements is off
cpu = 95%
pgbench -c 45 -j 45 -T 200 -f pgbench_script.sql --progress=10 pgbench
pgbench (16.6)
starting vacuum...end.
progress: 10.0 s, 1771226.4 tps, lat 0.025 ms stddev 0.013, 0 failed
progress: 20.0 s, 1873003.9 tps, lat 0.024 ms stddev 0.006, 0 failed
progress: 30.0 s, 1852849.3 tps, lat 0.024 ms stddev 0.008, 0 failed
progress: 40.0 s, 1863411.1 tps, lat 0.024 ms stddev 0.006, 0 failed
progress: 50.0 s, 1877219.2 tps, lat 0.024 ms stddev 0.006, 0 failed
progress: 60.0 s, 1826381.5 tps, lat 0.024 ms stddev 0.007, 0 failed
progress: 70.0 s, 1830563.7 tps, lat 0.024 ms stddev 0.007, 0 failed
progress: 80.0 s, 1836822.3 tps, lat 0.024 ms stddev 0.007, 0 failed
progress: 90.0 s, 1844969.2 tps, lat 0.024 ms stddev 0.008, 0 failed
progress: 100.0 s, 1832167.8 tps, lat 0.024 ms stddev 0.007, 0 failed
progress: 110.0 s, 1830603.9 tps, lat 0.024 ms stddev 0.008, 0 failed
progress: 120.0 s, 1800987.2 tps, lat 0.025 ms stddev 0.007, 0 failed
progress: 130.0 s, 1822877.6 tps, lat 0.024 ms stddev 0.007, 0 failed
progress: 140.0 s, 1843891.2 tps, lat 0.024 ms stddev 0.007, 0 failed
progress: 150.0 s, 1821845.2 tps, lat 0.024 ms stddev 0.007, 0 failed
progress: 160.0 s, 1794748.3 tps, lat 0.025 ms stddev 0.009, 0 failed
progress: 170.0 s, 1813191.1 tps, lat 0.025 ms stddev 0.007, 0 failed
progress: 180.0 s, 1814675.0 tps, lat 0.025 ms stddev 0.007, 0 failed
progress: 190.0 s, 1820666.0 tps, lat 0.025 ms stddev 0.007, 0 failed
transaction type: pgbench_script.sql
scaling factor: 1
query mode: simple
number of clients: 45
number of threads: 45
maximum number of tries: 1
duration: 200 s
number of transactions actually processed: 365703762
number of failed transactions: 0 (0.000%)
latency average = 0.024 ms
latency stddev = 0.008 ms
initial connection time = 25.624 ms
tps = 1828743.791205 (without initial connection time)
