Overview

Pinned:

• Benchmarks

Posts for 2020:

• Explaining explain to understand index usage in MySQL

Posts for 2023:

• Chasing down a CPU regression for MyRocks using flamegraphs

Benchmark reports for 2024

• Insert Benchmark
  • Small server
    • Postgres, Beelink 16G RAM (SER4), Jan24: cached
    • Postgres, Beelink 16G RAM (SER4), Jan24, v2: cached
    • Postgres, Beelink 16G RAM (SER4), Jan24, v3: cached
    • Postgres, Beelink 16G RAM (SER4), Jan24: IO-bound
    • Postgres, Beelink 16G RAM (SER4), Feb24: cached
    • Postgres, Beelink 16G RAM (SER4), Feb24: IO-bound
    • Postgres, Beelink 16G RAM (SER4), Mar24, tuning v16: cached, IO-bound
    • MyRocks, Beelink 16G RAM (SER4), Jan24, v2, cached: 5.6.35, 8.0, all, latest
    • MyRocks, Beelink 16G RAM (SER4), Mar24: cached, IO-bound
    • InnoDB, Beelink 16G RAM (SER4), Jan24, cached: 5.6, 5.7, 8.0, all
    • InnoDB, Beelink 16G RAM (SER4), Feb24, IO-bound: 5.6, 5.7, 8.0, all
    • InnoDB, Beelink 16G RAM (SER4), Mar24: cached, IO-bound
    • Postgres and MySQL, Beelink 16G RAM (SER4), Mar24: cached, IO-bound
    • MariaDB and MySQL, Beelink 16G RAM (SER4), Apr24, cached
      • Low concurrency (1 client): all, MariaDB: all, MariaDB: 10.x, MariaDB: 11.4, MariaDB vs MySQL, MySQL, MariaDB: 10.4
      • Some concurrency (4 clients): all, MariaDB:all, MariaDB: 10.x, MariaDB: 11.x, MariaDB vs MYSQL, MySQL, MariaDB: 10.4
    • MariaDB and MySQL, Beelink 16G RAM (SER4), May24, IO-bound
      • 1 client: all, MariaDB, MariaDB 10.x, MariaDB 11.x, MariaDB vs MySQL, MySQL
      • 4 clients: all, MariaDB, MariaDB 10.x, MariaDB 11.x, MariaDB vs MySQL, MySQL
    • Postgres, Beelink 32G RAM (SER7), Jan24: cached
    • Postgres, Beelink 32G RAM (SER7), Feb24: cached
    • Postgres, Beelink 32G RAM (SER7), Feb24: IO-bound
    • Postgres, Beelink 32G RAM (SER7), Mar24: cached, IO-bound
    • MyRocks, Beelink 32G RAM (SER7), cached, Jan24: 5.6.35, 8.0.28, 8.0.32, 8.0, all
    • MyRocks, Beelink 32G RAM (SER7), cached, Jan24, v2: 5.6.35, 8.0, all, latest
    • MariaDB and MySQL, PN53, cached, May24:
      • 1 user: all, MariaDB, 1 purge_thread, MariaDB, 4 purge threads, MySQL, MariaDB vs MySQL, 1 purge thread, MariaDB vs MySQL, 4 purge threads
      • 4 users: all, MariaDB, 1 purge thread, MariaDB, 4 purge threads, MySQL, MariaDB vs MySQL, 1 purge thread, MariaDB vs MySQL, 4 purge threads
    • Postgres 17beta1, PN53, May34:
      • cached: 1 user
    • Postgres 16.3 with and without full_page_writes vs the Insert Benchmark, PN53: cached, v1, cached, v2
    • Postgres 17beta1 with patch that enforces VISITED_PAGES_LIMIT during get_actual_value_range calls: cached
    • MyRocks and InnoDB, Insert Benchmark on SER4 and PN53, Jun24:
      • cached, 1u.1t, SER4: all, MyRocks, InnoDB, MyRocks vs InnoDB
      • IO-bound, 1u.1t, SER4: all, MyRocks, InnoDB, MyRocks vs InnoDB
      • cached, 1u.1t, PN53: all, MyRocks, InnoDB, MyRocks vs InnoDB
  • Medium server (GCP, c2-standard-30)
    • MySQL, InnoDB, cached workload, Jan24: MySQL 5.6, MySQL 5.7, MySQL 8.0, MySQL 5.6, 5.7 and 8.0
    • MyRocks, cached workload, Jan24: 5.6.35, 8.0.28, 8.0.32, 5.6.35, 8.0.28, 8.0.32 - many versions, 5.6.35, 8.0.28, 8.0.32 - latest versions
    • MyRocks, cached workload, Jan24, v2: 5.6.35, 8.0, latest 5.6, 8.0, all
    • MyRocks, IO-bound workload, Jan24, v2: 5.6.35, 8.0, all, latest
    • MySQL 8.0.28, Insert Benchmark, PGO and LTO cached
  • Medium server (GCP, c2d-standard-32, high-compute)
    • Insert Benchmark, Postgres 17beta2, Jul24: cached, IO-bound
    • Insert Benchmark, Postgres 17beta3, Aug24: cached, IO-bound
    • Insert Benchmark, MySQL 8.0.38, Jul24: cached, IO-bound
    • Insert Benchmark, MariaDB, Aug24:
      • Cached: 10.2 as base, 10.6 as base, vs MySQL
      • IO-bound: 10.2 as base, 10.6 as base, vs MySQL
    • Insert Benchmark, MariaDB, Sep24:
      • Using 10.2.44 as the base case: cached, IO-bound
      • Using 10.3.39 as the base case: cached, IO-bound
      • Latest MariaDB vs latest MySQL: cached, IO-bound
      • Latest MariaDB vs latest MySQL v2: cached, IO-bound
  • Large server (2-socket, 24-core)
    • MyRocks, cached workload, Jan24: 5.6.35, 8.0.28, 8.0.32
    • Postgres, Feb24: cached workload
  • Large server (32-core)
    • Postgres, Jun24, up to pg17beta1: cached, IO-bound
  • Large server (2-socket, 20 cores/socket)
    • InnoDB, v2: cached
  • Large server (48-core, Hetzner ax162-s)
    • Cached database: MySQL vs Postgres, MySQL, Postgres
    • Less IO-bound (1280m rows): MySQL vs Postgres, MySQL, Postgres
    • More IO-bound (4000m rows): MySQL vs Postgres, MySQL, Postgres
    • More IO-bound (4000m rows): MariaDB 10.6, 10.11

Benchmark reports for 2023

• jemalloc
  • Medium server, focus on jemalloc 5.2.1: IO-bound with LRU block cache, Hyper Clock cache
• Postgres 16 (Sep 2023)
  • Small server, insert benchmark
    • wal_compression=lz4: Cached by Postgres, IO-bound
    • wal_compression=none: Cached by Postgres, IO-bound
  • Medium server, insert benchmark
    • wal_compression=lz4: Cached by Postgres, IO-bound
    • wal_compression=none: Cached by Postgres, IO-bound
• Postgres 16 beta (Aug 2023)
  • Small server, insert benchmark:
    • wal_compression=lz4: Cached by Postgres, IO-bound
    • wal_compression=none: Cached by Postgres, IO-bound
  • Medium server, insert benchmark: Cached by Postgres, IO-bound, wal_compression=lz4, IO-bound, wal_compression=none
• MyRocks tombstones (Aug 2023)
  • Large server: Cached by RocksDB, IO-bound
• MySQL perf regressions with the Insert Benchmark
  • Large server, Dec 2023, FB MySQL with MyRocks 5.6.35, 8.0.28, 8.0.32
    • Cached database: latest builds
  • Large server, Dec 2023, MySQL with InnoDB
    • Cached workload: without NUMA interleave
  • Small server, Dec 2023, cached workload, MySQL with InnoDB
    • Some point releases: 5.6, 5.7, 8.0, 5.6, 5.7 and 8.0
    • One point release, different my.cnf: 5.7.44, 8.0.35
  • Medium server, Nov 2023, 5.6 to 8.0
    • Cached: some 5.6 releases, all 5.7 releases, All 8.0 releases, some 5.6, 5.7 and 8.0 releases
    • IO-bound
  • Small server, Oct 2023, 5.6 to 8.0, IO-bound: InnoDB 5.6, InnoDB 5.7, InnoDB 8.0, InnoDB 5.6, 5.7 and 8.0
  • Small server, Sep 2023, 5.6 to 8.0 all versions, cached: 5.6, 5.7, 8.0, 5.6, 5.7, 8.0
  • Large server, Sep 2023, part 3 (oops): cached workload, IO-bound workload
  • Large server, Sep 2023, part 2: cached workload base config, c5 config
  • Large server, Aug 2023, part 1
    • Base config: Cached, IO-bound
    • c5 config: Cached, IO-bound
  • Medium server, Aug 2023, part 2
    • Base config: Cached, IO-bound
    • c5 config: Cached, IO-bound
  • Medium server, Aug 2023, part 1
    • Base config: Cached and IO-bound
    • c5 config: Cached and IO-bound
  • Small server, Aug 2023, part 1
    • Cached by RocksDB, IO-bound
  • Small server, Aug 2023, part 2
    • Cached by RocksDB: base config, c5 config
    • IO-bound: base config, c5 config
• Insert benchmark with deletes (July 2023)
  • MyRocks
    • Small server, tuning my.cnf (Beelink): Cached by RocksDB, Cached by OS, IO-bound
    • Small server (Beelink, 8-cores)
      • 1 client: cached by RocksDB and by OS page cache, IO-bound
      • 4 clients: cached by RocksDB and by OS page cache, IO-bound
    • Medium server (c2-standard-30, 15-cores)
      • With delete-per-insert: Cached by MyRocks, Cached by OS, IO-bound
      • Configuration tuning, medium server, July 2023: Cached by RocksDB, Cached by OS, IO-bound
    • Large server, builds back to 2022: Cached by RocksDB, IO-bound
    • Large server, my.cnf tuning: Cached by RocksDB, Cached by OS, IO-bound
  • InnoDB
    • Write stalls: big server and cached by OS
  • Postgres
• InnoDB config tuning for the insert benchmark
  • medium server with Percona Server, August 2023
    • Cached by InnoDB: 8.0 only, 5.6 vs 5.7 vs 8.0
    • IO-bound: 8.0 only, 5.6 vs 5.7 vs 8.0
  • big server, August 2023
    • Cached by InnoDB: MySQL 5.6.51, MySQL 5.7.40, MySQL 8.0.33, MySQL 5.6, 5.7 and 8.0
    • Cached by OS: MySQL 5.6.51, MySQL 5.7.40, MySQL 8.0.33, MySQL 5.6, 5.7 and 8.0
    • IO-bound: MySQL 5.6.51, MySQL 5.7.40, MySQL 8.0.33, MySQL 5.6, 5.7 and 8.0
  • c2-standard-30, June 2023
    • MySQL 5.6.51: cached by InnoDB, cached by OS, IO-bound
    • MySQL 5.7.40: cached by InnoDB, cached by OS, IO-bound
    • MySQL 8.0.33: cached by InnoDB, cached by OS, IO-bound
  • c2-standard-30, July 2023
    • MySQL 5.6.51: cached by InnoDB, cached by OS, IO-bound
    • MySQL 5.7.40: cached by InnoDB, cached by OS, IO-bound
    • MySQL 8.0.33: cached by InnoDB, cached by OS, IO-bound
    • MySQL 5.6, 5.7, 8.0: cached by InnoDB, cached by OS, IO-bound
  • small server, August 2023
    • Cached by InnoDB: 5.6.51, 5.7.40, 8.0.33, 5.6 vs 5.7 vs 8.0
    • Cached by OS: 5.6.51, 5.7.40, 8.0.33, 5.6 vs 5.7 vs 8.0
    • IO-bound: 5.6.51, 5.7.40, 8.0.33, 5.6 vs 5.7 vs 8.0
• Postgres config tuning for the insert benchmark
  • Postgres 15.2 and 15.3, c2-standard-30 server, Aug 2023: Cached by Postgres, IO-bound
  • Postgres 15.3, c2-standard-30 server, May 2023
    • Cached by Postgres, 8 users, 8 tables
    • Cached by OS, 8 users, 8 tables
  • Postgres 15.3, c2-standard-30 server, more tuning, with delete_per_insert, up to a17 config, May 2023
    • Cached by OS, 8 users, 8 tables
    • Cached by Postgres, 8 users, 8 tables
  • Postgres 15.3, c2-standard-30 server, more tuning, with delete_per_insert, adds a18 config, June 2023
    • Cached by OS, 8 users, 8 tables
    • Cached by Postgres, 8 users, 8 tables
  • Postgres 15.3, c2-standard-30 server: IO-bound, with delete per insert, configs up to a18, June 2023
    • All configs
    • Some configs
  • Postgres 15.3, c2-standard-30 server with delete per insert, configs up to a22, July 2023
    • Cached by Postgres, Cached by OS, IO-bound
  • Postgres 15.2, Beelink (8-cores, 16G RAM), June 2023
    • Cached by Postgres: 1 user and 4 users
    • Cached by OS: 1 user and 4 users
  • Postgres 15.2, Beelink (8-cores, 16G RAM), July 2023
    • Cached by Postgres: 1 user and 4 users
    • Cached by OS: 1 user and 4 users
    • IO-bound: 1 user and 4 users
  • Postgres 15.2 and 15.3, Beelink Aug 2023: Cached by Postgres, IO-bound
• Postgres 16
  • at git sha 1ab763fc2
    • Beelink (server with 8 cores, 16G RAM)
      • insert benchmark
        • cached by Postgres: 1 user, 1 table, 4 users, 4 tables, 4 users, 1 table
        • cached by OS page cache: 1 user, 1 table, 4 users, 4 tables, 4 users, 1 table
      • sysbench
        • cached by Postgres: pending
        • cached by OS page cache: pending
• Compiler options, insert benchmark, small server (Beelink, 8 AMD cores, 16G RAM, NVMe SSD), May 2023
  • Cached by database
    • MyRocks
      • 1 thread & 1 table - 5.6.35, 8.0.28, 5.6 vs 8.0
      • 4 threads & 4 tables - 5.6.35, 8.0.28, 5.6 vs 8.0
      • 4 threads & 1 table - 5.6.35, 8.0.28, 5.6 vs 8.0
    • InnoDB
      • 1 thread & 1 table - versions 5.6, 5.7, 8.0, versions 8.0.2x, 8.0.3x, version 8.0.31 builds
      • 4 threads & 4 tables - versions 5.6, 5.7, 8.0, versions 8.0.2x, 8.0.3x, version 8.0.31 builds
      • 4 threads & 1 table - versions 5.6, 5.7, 8.0, versions 8.0.2x, 8.0.3x, version 8.0.31 builds
    • Postgres
      • 1 thread & 1 table - Version 15.1 builds, Versions 11 to 15
      • 4 threads & 4 tables - Version 15.1 builds, Versions 11 to 15
      • 4 threads & 1 table - Version 15.1 builds, Versions 11 to 15
  • Cached by OS
    • MyRocks
      • 1 thread & 1 table - 5.6.35, 8.0.28, 5.6 vs 8.0
      • 4 threads & 4 tables - 5.6.35, 8.0.28, 5.6 vs 8.0
      • 4 threads & 1 table - 5.6.35, 8.0.28, 5.6 vs 8.0
    • InnoDB
      • 1 thread & 1 table - versions 5.6, 5.7, 8.0, versions 8.0.2x, 8.0.3x, version 8.0.31 builds
      • 4 threads & 4 tables - versions 5.6, 5.7, 8.0, versions 8.0.2x, 8.0.3x, version 8.0.31 builds
      • 4 threads & 1 table - versions 5.6, 5.7, 8.0, versions 8.0.2x, 8.0.3x, version 8.0.31 builds
    • Postgres
      • 1 thread & 1 table - Version 15.1 builds, Versions 11 to 15
      • 4 threads & 4 tables - Version 15.1 builds, Versions 11 to 15
      • 4 threads & 1 table - Version 15.1 builds, Versions 11 to 15
• Compiler options, insert benchmark, big server (c2-standard-60, 30 cores, hyperthreads disabled, 240G RAM), May 2023
  • Cached by database
    • MyRocks
      • 20 threads & 20 tables - 5.6.35, 8.0.28, 5.6 vs 8.0
      • 20 threads & 1 table - 5.6.35, 8.0.28, 5.6 vs 8.0
    • InnoDB
      • 20 threads & 20 tables - versions 5.6, 5.7, 8.0, versions 8.0.22, 8.0.28, 8.0.31, 8.0.32, 8.0.31 builds
      • 20 threads & 1 table - versions 5.6, 5.7, 8.0, versions 8.0.22, 8.0.28, 8.0.31, 8.0.32, 8.0.32 builds
    • Postgres
      • 20 threads & 20 tables - Version 15.1 builds, Versions 11 to 15
      • 20 threads & 1 table - Version 15.1 builds, Versions 11 to 15
  • Cached by OS
    • MyRocks
      • 20 threads & 20 tables - 5.6.35, 8.0.28, 5.6 vs 8.0
      • 20 threads & 1 table - 5.6.35, 8.0.28, 5.6 vs 8.0
    • InnoDB
      • 20 threads & 20 tables - versions 5.6, 5.7, 8.0, versions 8.0.22, 8.0.28, 8.0.31, 8.0.32, 8.0.31 builds
      • 20 threads & 1 table - versions 5.6, 5.7, 8.0, versions 8.0.22, 8.0.28, 8.0.31, 8.0.32
    • Postgres
      • 20 threads & 20 tables - Version 15.1 builds, Versions 11 to 15
      • 20 threads & 1 table - Version 15.1 builds, Versions 11 to 15
• Compiler options, sysbench, small server (Beelink, 8-core AMD, 16G RAM)
  • MyRocks
    • One thread, one table: 5.6.35, 8.0.28, 5.6 vs 8.0
    • Four threads, four tables: 5.6.35, 8.0.28, 5.6 vs 8.0
    • Four threads, one table: 5.6.35, 8.0.28, 5.6 vs 8.0
• Insert benchmark on Intel NUC to show the benefit from the insert buffer
  • 1 client, 500M rows, IO-bound

Others from 2023:

• Insert Benchmark with Postgres 16 storage engines
  • Beelink: Cached, IO-bound
  • c2-standard-30: Cached, IO-bound

Benchmark reports for 2022:

• Insert Benchmark on c2-standard-60 (GCP, 30 cores, 240G RAM), Postgres
  • Cached by Postgres, 20 clients, table per client for versions 11.19, 12.14, 13.10, 14.7, 15.1, 15.2, 11 to 15
  • Cached by Postgres, 20 clients, only one table for versions 11.19, 12.14, 13.10, 14.7, 15.1, 15.2, 11 to 15
  • Cached by OS page cache, 20 clients, table per client for versions 11.19, 12.14, 13.10, 14.7, 15.1, 15.2, 11 to 15
  • Cached by OS page cache, 20 clients, only one table for versions 11.19, 12.14, 13.10, 14.7, 15.1, 15.2, 11 to 15
• Insert Benchmark on Beelink (AMD, 8-core, 16G RAM, NVMe)
  • Cached by DBMS, 20M rows
    • MySQL 5.6, 5.7, 8.0 with InnoDB and MyRocks: 1 client, 2 clients, 3 clients, 4 clients
    • Postgres 12, 13, 14 and 15: 1 client, 2 clients, 3 clients, 4 clients
  • IO-bound, 500m rows
    • MySQL 5.6, 5.7, 8.0 with InnoDB and MyRocks: 1 client, 2 clients, 3 clients, 4 clients
    • Postgres 12, 13, 14 and 15: 1 client, 2 clients, 3 clients, 4 clients
• Insert Benchmark on AWS c7g.2xl with io2 SSD (256G, 10K IOPs)
  • 1 client, 20M rows, cached by DBMS
• Insert Benchmark on AWS c6i.2xl with io2 SSD (256G, 10K IOPs)
  • 1 client, 20M rows, cached by DBMS, hyperthread disabled
• Insert Benchmark on AWS c7g.4xl with io2 SSD (1T, 32K IOPs)
  • 4 clients, 50M rows, cached by DBMS
  • 8 clients, 50M rows, cached by DBMS
• Insert Benchmark on AWS c6i.4xl with io2 SSD (1T, 32K IOPs)
  • 4 clients, 50M rows, cached by DBMS, hyperthread disabled
  • 8 clients, 50M rows, cached by DBMS, hyperthread disabled
• Insert Benchmark on AWS c7g.8xl with io2 SSD (2T, 64K IOPs)
  • 8 clients, 100M rows, cached by DBMS
  • 16 clients, 100M rows, cached by DBMS
• Insert Benchmark on AWS c6i.8xl with io2 SSD (2T, 64K IOPs)
  • 8 clients, 100M rows, cached by DBMS, hyperthread disabled
  • 16 clients, 100M rows, cached by DBMS, hyperthread disabled
• Insert Benchmark on AWS c7g.16xl with io2 SSD (5T, 100K IOPs)
  • 22 clients, 500M rows, cached by DBMS
  • 44 clients, 500M rows, cached by DBMS
• Insert Benchmark on AWS c6i.16xl with io2 SSD (5T, 64K IOPs)
  • 22 clients, 500M rows, cached by DBMS, hyperthread disabled
  • 22 clients, 500M rows, cached by DBMS, hyperthread enabled
  • 44 clients, 500M rows, cached by DBMS, hyperthread disabled
  • 44 clients, 500M rows, cached by DBMS, hyperthread enabled
• Insert Benchmark
  • Intel NUC, not IO-bound, Postgres
  • Intel NUC, IO-bound, Postgres
  • Intel NUC, not IO-bound, InnoDB
  • Intel NUC, IO-bound, InnoDB
  • Intel NUC, not IO-bound, MyRocks
  • Intel NUC, IO-bound, MyRocks
• Insert Benchmark to test wal_compression in Postgres 15
  • Intel NUC, IO-bound
  • Intel NUC, not IO-bound
• Insert Benchmark for Postgres 12.11, 13.7, 14.3 and 15beta
  • Intel NUC, IO-bound
  • Intel NUC, IO-bound, uses prepared statements
  • Intel NUC, IO-bound, uses prepared statements, 15b1 with patch 1
  • Intel NUC, IO-bound, uses prepared statements, 15b1 with patch 1, larger WAL
  • Intel NUC, IO-bound, uses prepared statements, larger WAL, includes 15beta
  • Intel NUC, not IO-bound
  • Intel NUC, not IO-bound, uses prepared statements
  • Intel NUC, not IO-bound, uses prepared statements, 15b1 with patch 1
  • Intel NUC, not IO-bound, uses prepared statements, 15b1 with patch 1, larger WAL
  • Intel NUC, IO-bound, uses prepared statements, larger WAL, includes 15beta
• Insert Benchmark for Postgres 14.0, 14.1, 14.2, 14.3
  • Intel NUC, IO-bound
  • Intel NUC, not IO-bound

Benchmark reports for 2021:

• November
  • Insert benchmark, Intel NUC, 1 user, 20M rows (in memory)
    • InnoDB in MySQL 5.6, 5.7 and 8.0
    • Postgres 12.4, 13.4 and 14.0
    • MyRocks 5.6.35 and 8.0.20
  • Insert benchmark, Intel NUC, 1 user, 500M rows (IO-bound)
    • InnoDB in MySQL 5.6, 5.7 and 8.0
    • Postgres 12.4, 13.4 and 14.0
    • MyRocks 5.6.35 and 8.0.20
• January, Intel NUC
  • Insert benchmark, 20M rows, 1 user, NUC server
  • Insert benchmark, 100M rows, 1 user, NUC server
  • Insert benchmark, 500M rows, 1 user, NUC server

Benchmark reports for 2020:

• Insert benchmark tests with a table per user
  • CPU-bound, 15m rows, 1 user
  • CPU-bound, 40m rows, 8 user
  • IO-bound 800m rows, 8 users
  • IO-bound 2000m rows, 8 users
• Insert benchmark tests with 1 table shared by all users
  • CPU-bound, 40m rows, 8 user
  • IO-bound 800m rows, 8 users
  • IO-bound 2000m rows, 8 users
• Linkbench
  • CPU-bound, maxid1=10m, 8 users
  • IO-bound, maxid1=200m, 8 users
  • IO-bound, maxid1=500m, 8 users
• Insert benchmark on NUC to determine the best configuration
  • CPU-bound, 20m rows for InnoDB, MyRocks and Postgres
  • Less IO-bound, 100m rows for InnoDB, MyRocks and Postgres
  • More IO-bound, 500m rows for InnoDB, MyRocks and Postgres