Benchmarking Managed PostgreSQL Cloud Solutions - Part One: Amazon Aurora


  • Edit ~/.bashrc to set the environment variables for the database connection and required paths to PostgreSQL libraries:
    export PGHOST=aurora.cluster-ctfirtyhadgr.us-east-1.rds.amazonaws.com
    export PGUSER=postgres
    export PGPASSWORD=postgres
    export PGDATABASE=postgres
    export PATH=$PATH:/usr/local/pgsql/bin
    export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/pgsql/lib
  • Initialize the database:
    [root@ip-172-31-19-190 ~]# pgbench -i --fillfactor=90 --scale=10000
    NOTICE: table "pgbench_history" does not exist, skipping
    NOTICE: table "pgbench_tellers" does not exist, skipping
    NOTICE: table "pgbench_accounts" does not exist, skipping
    NOTICE: table "pgbench_branches" does not exist, skipping
    creating tables...
    100000 of 1000000000 tuples (0%) done (elapsed 0.05 s, remaining 457.23 s)
    200000 of 1000000000 tuples (0%) done (elapsed 0.13 s, remaining 631.70 s)
    300000 of 1000000000 tuples (0%) done (elapsed 0.21 s, remaining 688.29 s) ... 999500000 of 1000000000 tuples (99%) done (elapsed 811.41 s, remaining 0.41 s)
    999600000 of 1000000000 tuples (99%) done (elapsed 811.50 s, remaining 0.32 s)
    999700000 of 1000000000 tuples (99%) done (elapsed 811.58 s, remaining 0.24 s)
    999800000 of 1000000000 tuples (99%) done (elapsed 811.65 s, remaining 0.16 s)
    999900000 of 1000000000 tuples (99%) done (elapsed 811.73 s, remaining 0.08 s)
    1000000000 of 1000000000 tuples (100%) done (elapsed 811.80 s, remaining 0.00 s)
    vacuum...
    set primary keys...
    done.
  • Verify the database size:
    postgres=> \l+ postgres List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
    ----------+----------+----------+-------------+-------------+-------------------+--------+------------+-------------------------------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 160 GB | pg_default | default administrative connection database
    (1 row)
  • Use the following query to verify that the time interval between checkpoints is set so checkpoints will be forced during the 10 min run:
    SELECT total_checkpoints, seconds_since_start / total_checkpoints / 60 AS minutes_between_checkpoints FROM ( SELECT EXTRACT( EPOCH FROM ( now() - pg_postmaster_start_time() ) ) AS seconds_since_start, (checkpoints_timed+checkpoints_req) AS total_checkpoints
    FROM pg_stat_bgwriter) AS sub;
    Result:
    postgres=> \e total_checkpoints | minutes_between_checkpoints
    -------------------+----------------------------- 50 | 0.977392292333333
    (1 row)
  • Run the Read/Write workload:
    [root@ip-172-31-19-190 ~]# pgbench --protocol=prepared -P 60 --time=600 --client=1000 --jobs=2048
    Output
    starting vacuum...end.
    progress: 60.0 s, 35670.3 tps, lat 27.243 ms stddev 10.915
    progress: 120.0 s, 36569.5 tps, lat 27.352 ms stddev 11.859
    progress: 180.0 s, 35845.2 tps, lat 27.896 ms stddev 12.785
    progress: 240.0 s, 36613.7 tps, lat 27.310 ms stddev 11.804
    progress: 300.0 s, 37323.4 tps, lat 26.793 ms stddev 11.376
    progress: 360.0 s, 36828.8 tps, lat 27.155 ms stddev 11.318
    progress: 420.0 s, 36670.7 tps, lat 27.268 ms stddev 12.083
    progress: 480.0 s, 37176.1 tps, lat 26.899 ms stddev 10.981
    progress: 540.0 s, 37210.8 tps, lat 26.875 ms stddev 11.341
    progress: 600.0 s, 37415.4 tps, lat 26.727 ms stddev 11.521
    transaction type: <builtin: TPC-B (sort of)>
    scaling factor: 10000
    query mode: prepared
    number of clients: 1000
    number of threads: 1000
    duration: 600 s
    number of transactions actually processed: 22040445
    latency average = 27.149 ms
    latency stddev = 11.617 ms
    tps = 36710.828624 (including connections establishing)
    tps = 36811.054851 (excluding connections establishing)
  • Prepare the sysbench test:
    sysbench --test=/usr/local/share/sysbench/oltp.lua \ --pgsql-host=aurora.cluster-ctfirtyhadgr.us-east-1.rds.amazonaws.com \ --pgsql-db=postgres \ --pgsql-user=postgres \ --pgsql-password=postgres \ --pgsql-port=5432 \ --oltp-tables-count=250\ --oltp-table-size=450000 \ prepare
    Output:
    sysbench 0.5: multi-threaded system evaluation benchmark Creating table 'sbtest1'...
    Inserting 450000 records into 'sbtest1'
    Creating secondary indexes on 'sbtest1'...
    Creating table 'sbtest2'...
    ...
    Creating table 'sbtest250'...
    Inserting 450000 records into 'sbtest250'
    Creating secondary indexes on 'sbtest250'...
  • Run the sysbench test:
    sysbench --test=/usr/local/share/sysbench/oltp.lua \ --pgsql-host=aurora.cluster-ctfirtyhadgr.us-east-1.rds.amazonaws.com \ --pgsql-db=postgres \ --pgsql-user=postgres \ --pgsql-password=postgres \ --pgsql-port=5432 \ --oltp-tables-count=250 \ --oltp-table-size=450000 \ --max-requests=0 \ --forced-shutdown \ --report-interval=60 \ --oltp_simple_ranges=0 \ --oltp-distinct-ranges=0 \ --oltp-sum-ranges=0 \ --oltp-order-ranges=0 \ --oltp-point-selects=0 \ --rand-type=uniform \ --max-time=600 \ --num-threads=1000 \ run
    Output:
    sysbench 0.5: multi-threaded system evaluation benchmark Running the test with following options:
    Number of threads: 1000
    Report intermediate results every 60 second(s)
    Random number generator seed is 0 and will be ignored Forcing shutdown in 630 seconds Initializing worker threads... Threads started! [ 60s] threads: 1000, tps: 20443.09, reads: 0.00, writes: 81834.16, response time: 68.24ms (95%), errors: 0.62, reconnects: 0.00
    [ 120s] threads: 1000, tps: 20580.68, reads: 0.00, writes: 82324.33, response time: 70.75ms (95%), errors: 0.73, reconnects: 0.00
    [ 180s] threads: 1000, tps: 20531.85, reads: 0.00, writes: 82127.21, response time: 70.63ms (95%), errors: 0.73, reconnects: 0.00
    [ 240s] threads: 1000, tps: 20212.67, reads: 0.00, writes: 80861.67, response time: 71.99ms (95%), errors: 0.43, reconnects: 0.00
    [ 300s] threads: 1000, tps: 19383.90, reads: 0.00, writes: 77537.87, response time: 75.64ms (95%), errors: 0.75, reconnects: 0.00
    [ 360s] threads: 1000, tps: 19797.20, reads: 0.00, writes: 79190.78, response time: 75.27ms (95%), errors: 0.68, reconnects: 0.00
    [ 420s] threads: 1000, tps: 20304.43, reads: 0.00, writes: 81212.87, response time: 73.82ms (95%), errors: 0.70, reconnects: 0.00
    [ 480s] threads: 1000, tps: 20933.80, reads: 0.00, writes: 83737.16, response time: 74.71ms (95%), errors: 0.68, reconnects: 0.00
    [ 540s] threads: 1000, tps: 20663.05, reads: 0.00, writes: 82626.42, response time: 73.56ms (95%), errors: 0.75, reconnects: 0.00
    [ 600s] threads: 1000, tps: 20746.02, reads: 0.00, writes: 83015.81, response time: 73.58ms (95%), errors: 0.78, reconnects: 0.00
    OLTP test statistics: queries performed: read: 0 write: 48868458 other: 24434022 total: 73302480 transactions: 12216804 (20359.59 per sec.) read/write requests: 48868458 (81440.43 per sec.) other operations: 24434022 (40719.87 per sec.) ignored errors: 414 (0.69 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 600.0516s total number of events: 12216804 total time taken by event execution: 599964.4735s response time: min: 6.27ms avg: 49.11ms max: 350.24ms approx. 95 percentile: 72.90ms Threads fairness: events (avg/stddev): 12216.8040/31.27 execution time (avg/stddev): 599.9645/0.01