This has results for IO-bound sysbench with Postsgres versions 11.10, 12.4 and 13.1. The test is similar to what I used for in-memory sysbench except the table has 400M rows instead of 10M and the test table is much larger than memory. The goal is to understand how performance and efficiency change over time. I also have posts for InnoDB and MyRocks.

Summary:

- Many tests get between 5% and 10% more throughput in 13.1 relative to 11.10
- Tests that do covering queries on secondary indexes do ~20% less read IO/query and I am not sure whether this is a benefit from index deduplication
- There aren't significant regressions from 11.10 to 13.1

**Overview**

I use my sysbench fork. I have yet to change the code but added Lua scripts for new tests. Tests are run in a sequence (prepare, pause to let write back & compaction catch up, read-only, write-heavy, pause again, read-only, delete, insert) via all_small.sh which calls another helper script, run.sh, to run tests and collect HW metrics.

The tests use 1 table with 400M rows and each test is run for 300 seconds for 1, 2 and 3 threads. The test servers have 4 CPU cores with HT disabled, 16G of RAM and NVMe SSD. The test table is much larger than RAM and I call this an IO-bound setup.

Tests used Postgres versions 11.10, 12.4 and 13.1. The servers use Ubuntu 20.04 and XFS.

**Results**

Load:

- For the first 4 tests that do point queries
- QPS ratios for v13 are
**1.07, 1.04, 0.96, 0.83**(here to here) - QPS ratios for v12 are 1.07, 1.02, 1.00, 1.00
- CPU/query ratios for v13 are 0.85, 0.92, 1.05, 1.03 (here to here)
- The next 3 tests have range scans from oltp_read_write.lua with ranges of size 10, 100 & 10,000
- QPS ratios for v13 are
**1.02, 0.92, 0.98**(here to here) - QPS ratios for v12 are 1.07, 0.99, 0.98
- CPU/query ratios for v13 are 0.94, 1.10, 1.02 (here to here)
- The next 2 tests do point queries via in-lists that are covering and not covering for the PK index
- QPS ratios for v13 are
**0.96, 1.00**(here to here) - QPS ratios for v12 are 0.98, 1.02
- CPU/query ratios for v13 are 1.04, 0.98 (here to here)
- The next 2 tests are similar to the previous but use the secondary index
- QPS ratios for v13 are
**1.07, 1.08**(here to here) - QPS ratios for v12 are 0.99, 1.00
- CPU/query ratios for v13 are 0.99, 0.92 (here to here)
- IO read KB/query ratios for v13 are 0.82, 0.91
- The next 2 tests do range queries that are covering and not covering for the PK index
- QPS ratios for v13 are
**1.00, 0.99**(here to here) - QPS ratios for v12 are 1.03, 1.02
- CPU/query ratios for v13 are 1.00, 1.03 (here to here)
- The next 2 tests are similar to the previous but use the secondary index

- For the next 5 tests that are update-only
- QPS ratios for v13 are
**1.02, 1.02, 1.01, 1.02, 0.97**(here to here) - QPS ratios for v12 are 1.01, 1.00, 1.01, 1.00, 0.99
- CPU/statement ratios for v13 are 0.99, 0.99, 0.98, 0.98, 0.98 (here to here)
- The next test is write-only that has the writes from oltp_read_write.lua
- QPS ratio for v13 is
**1.01**(here) - QPS ratio for v12 is 0.98
- CPU/transaction ratio is 1.02 for v12 and 0.99 for v13 (here)
- The next 2 tests are the traditional sysbench tests with ranges of size 10 & 100

- The next 3 tests have range scans from oltp_read_write.lua with ranges of size 10, 100 & 10,000
- QPS ratios for v13 are
**0.95, 1.00, 0.98**(here to here). - QPS ratios for v12 are 0.93, 0.98, 0.98
- CPU/transaction ratios for v13 are 1.10, 1.01, 1.01 (here to here)
- The next 5 tests do point queries
- QPS ratios for v13 are
**1.08, 1.01, 1.00, 1.00, 0.96**(here to here) - QPS ratios for v12 are 1.08, 0.99, 1.00, 1.00, 0.99
- CPU/query ratios for v13 are 0.89, 0.99, 0.99, 1.02, 1.03 (here to here)
- The next 2 tests do point queries via in-lists that are covering and not covering for the PK index
- QPS ratios for v13 are
**0.97, 1.00**(here to here) - QPS ratios for v12 are 0.98, 0.98
- CPU/query ratios for v13 are 1.03, 0.99 (here to here)
- The next 2 tests are similar to the previous test but use the secondary index
- QPS ratios for v13 are
**1.08, 1.08**(here to here) - QPS ratios for v12 are 0.99, 1.00
- CPU/query ratios for v13 are 0.97, 0.94 (here to here)
- IO read KB/query ratios for v13 are 0.81, 0.91
- The next 2 tests do range queries that are covering and not covering for the PK index
- QPS ratios for v13 are
**1.03, 0.99**(here to here) - QPS ratios for v12 are 1.05, 0.97
- CPU/query ratios for v13 are 0.97, 1.02 (here to here)
- The next 2 tests are similar to the previous but use the secondary index
- QPS ratios for v13 are
**1.13, 1.00**(here to here) - QPS ratios for v12 are 1.00, 1.02
- CPU/query ratios for v13 are 0.92, 0.98 (here to here)
- IO read KB/query ratios for v13 are 0.82, 1.00
- The next test does a single-threaded full scan of the test table with a filter so that the result set is empty.

## No comments:

## Post a Comment