Performance comparison of MySQL vs. Postgres with HammerDb on an I/O-bound workload with SERIALIZABLE isolation level
I/O-bound workload = RAM << data size. MySQL 8.0.26. Postgres 14.5. Postgres is also referred to as Pg for short.
From HammerDb docs:
As such NOPM (New Orders per minute) as a performance metric independent of any particular database implementation is the recommended primary metric to use.
| Virtual Users (threads) | MySQL | Pg 1 | Pg 2 | Pg 3 |
|---|---|---|---|---|
| 1 | 183 | 418 | - | - |
| 10 | 1688 | x | 2779 | 2602 |
| 100 | 2335 | x | 2891 | 2736 |
MySQL gave me no issues but with Pg, I had to try 3 times.
The first attempt with Pg (pg_raiseerror=false) ended up with some unsuccessful runs. x = unsuccessful run.
First I received a out of shared memory error that I fixed by setting max_pred_locks_per_transaction and max_locks_per_transaction from 64 to 128.
After that when run with 10 virtual users, an invalid transaction termination
was observed in server logs which is displayed as Vuser 9:New Order Procedure Error set RAISEERROR for Details for example by HammerDb.
I gave up and could not investigate more. The test with 100 users was aborted. The test with 10 users was allowed to run but it
did not terminate after the 30 minute duration. Server kept on showing running queries. Ultimately it was aborted.
MySQL query snapshot running with 100 vu.
Pg query snapshot running with 1 vu.
The second attempt was done with pg_raiseerror=true. With this setting the threads that receive an error are aborted and thus the contention
(concurrency) goes down. The third attempt was done with same config as the first attempt, namely that pg_raiseerror=false and this time I did not get
unusable runs although there were many serialization errors and invalid transaction termination.
MySQL is with raiseerror=false as I believe that is the right setting to simulate a sustained concurrent load and to judge the performance against.
From the docs:
If set to TRUE on detecting an error the user will report the error into the HammerDB console and then terminate execution. If set to FALSE the virtual user will ignore the error and proceed with executing the next transaction.
I don't know if HammerDb discounts transactions that return with an error. If its counting unsuccessful transactions in its NOPM count, that is a bug.
| Virtual Users | MySQL | Pg 1 | Pg 2 | Pg 3 |
|---|---|---|---|---|
| 1 | 418 | 1230 | - | - |
| 10 | 3913 | x | 6611 | 6190 |
| 100 | 5432 | x | 6888 | 6717 |
| Virtual Users | MySQL | Pg 1 | Pg 2 | Pg 3 |
|---|---|---|---|---|
| 1 | 15% | 18% | - | - |
| 10 | 64% | x | 71% | 71% |
| 100 | 100% | x | 40% | 100% |
Note on Pg 2: With 100 users Pg starts out with 100% CPU Utilization but the failures cause vu threads to abort which eventually leads to drop in CPU Utilization.
Pre-built Docker image of HammerDb version 4.7 was used.
docker run --network=host -it --name hammerdb hammerdb bash
buildschema generated schema with 400 warehouses and 100 virtual users
(time taken = 30 min on both MySQL and Pg). This resulted in following data sizes on MySQL and Postgres. Roughly 38GB of data was generated across
9 tables and totalling approximately 200M rows (~200 bytes/row). To simulate an I/O-bound workload, both MySQL and Postgres
servers were provisioned with 3.75GB RAM and 1 vCPU with SSD storage. Tests were run for 30 minutes each and the transaction isolation level
was set to SERIALIZABLE as that is what was of interest to me. In his video Steve Shaw specifically
states he does not do any concurrency control whatever in HammerDb; a design choice he has made.
MySQL:
mysql> show tables;
+--------------------+
| Tables_in_hammerdb |
+--------------------+
| customer |
| district |
| history |
| item |
| new_order |
| order_line |
| orders |
| stock |
| warehouse |
+--------------------+
9 rows in set (0.04 sec)
mysql> SELECT SUM(data_length + index_length) / 1024 / 1024 AS 'DB Size (MB)' FROM information_schema.tables WHERE table_schema = 'hammerdb';
+----------------+
| DB Size (MB) |
+----------------+
| 35,431.82812500 |
+----------------+
1 row in set (0.04 sec)
mysql> SELECT SUM(data_length) / 1024 / 1024 AS 'DB Size (MB)' FROM information_schema.tables WHERE table_schema = 'hammerdb';
+----------------+
| DB Size (MB) |
+----------------+
| 34,074.82812500 |
+----------------+
1 row in set (0.05 sec)
Postgres:
hammerdb=> select relname, n_tup_ins - n_tup_del as rowcount from pg_stat_user_tables;
relname | rowcount
------------+-----------
district | 4000
order_line | 119,324,502
orders | 11,933,700
item | 100,000
stock | 40,000,000
customer | 11,953,000
new_order | 3,573,700
history | 11,953,000
warehouse | 400
(9 rows)
hammerdb=> SELECT pg_size_pretty(pg_database_size('hammerdb'));
pg_size_pretty
----------------
38 GB
(1 row)
print vuconf for the test with 10 vu
print vuconf for the test with 10 vu
Of note are innodb_buffer_pool_size=1,476,395,008.
mysql -h x.x.x.x -u hammerdb -p -e "show variables" > mysql-settings.txt
Of note are effective_cache_size=2000000kB, shared_buffers=1226MB.
hammerdb=> \o pg-settings.txt
hammerdb=> select name, setting from pg_settings;
hammerdb=> \o
The script that runs the perf test.
The script that runs the perf test.
After first unsuccessful attempt with Pg, I then re-ran Pg tests but this time I set pg_raiseerror=true.
This time I was able to get better luck with Pg and got following numbers:
10vu: there was one vu that got aborted with invalid transaction termination exception.
Vuser 1:TEST RESULT : System achieved 2779 NOPM from 6611 PostgreSQL TPM
100vu: many vus got aborted with exceptions. See log file.
Vuser 1:TEST RESULT : System achieved 2891 NOPM from 6888 PostgreSQL TPM
I also had to set max_connections=200 on Pg server (earlier it was 100).
Many Pg users (threads) got aborted like this when running with 100vu:
Error in Virtual User 30: ERROR: could not serialize access due to read/write dependencies among transactions
DETAIL: Reason code: Canceled on conflict out to pivot 6214880, during read.
HINT: The transaction might succeed if retried.
Vuser 30:FINISHED FAILED
In Pg, these failures are expected to happen due to serialization conflicts and the transaction should be re-tried.
That is how SSI (serialization snapshot isolation) works. But with raiseerror=true HammerDb aborts the vu whereas with raiseerror=false
I couldn't get a successful run. The aborting of users actually helped Pg (vs. MySQL) here because it reduced the contention and that improves the
throughput. Refer Steve Shaw's video where he shows a graph of this phenomenon.
In case of MySQL, it handles concurrency using a combination of 2PL and MVCC. Sometimes conflicting transactions get blocked which is a sign of locking. At other times it raises a deadlock error. In this study a deadlock error presumably never happened as I never saw any failure in HammerDb output when running MySQL. Refer log
Below is how HammerDb is handling RAISEERROR. Refer source:
if {[pg_result $result -status] != "PGRES_TUPLES_OK"} {
if { $RAISEERROR } {
error "[pg_result $result -error]"
} else {
puts "New Order Procedure Error set RAISEERROR for Details"
}
pg_result $result -clear
} else {
pg_result $result -clear
}
SSI (implemented in Pg) is believed by some to be superior to 2PL method of implementing serializable transaction isolation (2PL is what MySQL is using for serializable transaction isolation) because SSI implements optimistic concurrency control where concurrent transactions do not block each other. Thinking from purely theoretical perspective, I feel SSI should outperform 2PL when the contention is not too high and transactions are short. If the contention is high and transactions are long lived, then SSI should give worse performance than 2PL because with SSI, the database will end up executing many CPU instructions that will not lead to any useful work whereas with 2PL, the conflicting transactions will wait for their turn and CPU resources will not be wasted on work that gets thrown away. The downside of locks is that they are risky. Bugs happen in the code and if there is a lock placed erroneously it can be devastating and catastrophic.
As I had suspected, pgoltp.tcl:
set choice [ RandomNumber 1 23 ]
if {$choice <= 10} {
puts "new order"
if { $KEYANDTHINK } { keytime 18 }
set curn_no [ pick_cursor $neworder_policy [ join $neworder_cursors ] $nocnt $nolen ]
set cursor_position [ lsearch $neworder_cursors $curn_no ]
set lda_no [ lindex [ join $csneworder ] $cursor_position ]
neword $lda_no $w_id $w_id_input $RAISEERROR $ora_compatible $pg_storedprocs
incr nocnt
the NOPM counter is incremented irrespective of whether the call to neword was successful or not. So in that case
all the results are meaningless and this tool is of no use. It will always favor Pg over MySQL irrespective of whether
RAISEERROR is true or false. When RAISEERROR is true, Pg threads will get aborted whereas MySQL will
continue to get hammered by many more threads and when RAISEERROR is false, even if the call to neword fails,
HammerDB will keep on merrily incrementing NOPM count.
Hold on, it gets better. Above is not the code that is computing NOPM. I modified the code so that neword returns a success or error
and based on that increment the nocnt but it did not change the NOPM count. I still got 2870 NOPM with 100 vu.
The code that is computing NOPM is like this I believe:
pg_select $lda1 "select sum(d_next_o_id) from district" o_id_arr {
set start_nopm $o_id_arr(sum)
}
pg_select $lda1 "select sum(d_next_o_id) from district" o_id_arr {
set end_nopm $o_id_arr(sum)
}
set tpm [ expr {($end_trans - $start_trans)/$durmin} ]
set nopm [ expr {($end_nopm - $start_nopm)/$durmin} ]
Each district is filling orders independently so each district has its own next new order id counter which is d_next_o_id. Refer this code in the neword sproc:
UPDATE district SET d_next_o_id = d_next_o_id + 1 WHERE d_id = no_d_id AND d_w_id = no_w_id RETURNING d_next_o_id, d_tax INTO no_d_next_o_id, no_d_tax;
o_id := no_d_next_o_id;
INSERT INTO ORDERS (o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_ol_cnt, o_all_local) VALUES (o_id, no_d_id, no_w_id, no_c_id, tstamp, no_o_ol_cnt, no_o_all_local);
INSERT INTO NEW_ORDER (no_o_id, no_d_id, no_w_id) VALUES (o_id, no_d_id, no_w_id);
But the sum of d_next_o_id does not match with the row count of new_order table.
hammerdb=> \d district
Table "public.district"
Column | Type | Collation | Nullable | Default
-------------+-----------------------+-----------+----------+---------
d_w_id | integer | | not null |
d_next_o_id | integer | | not null |
d_id | smallint | | not null |
d_ytd | numeric(12,2) | | not null |
d_tax | numeric(4,4) | | not null |
d_name | character varying(10) | | not null |
d_street_1 | character varying(20) | | not null |
d_street_2 | character varying(20) | | not null |
d_city | character varying(20) | | not null |
d_state | character(2) | | not null |
d_zip | character(9) | | not null |
Indexes:
"district_i1" PRIMARY KEY, btree (d_w_id, d_id)
hammerdb=> select d_next_o_id from district limit 10;
d_next_o_id
-------------
3207
3183
3239
3228
3201
3209
3217
3195
3188
3207
(10 rows)
hammerdb=> select count(*) from new_order;
count
---------
3,591,798
(1 row)
hammerdb=> select sum(d_next_o_id) from district;
sum
----------
12,932,708
(1 row)
This is because of following code inside the delivery sproc. Line 207:
DELETE FROM new_order WHERE no_w_id = d_w_id AND no_d_id = d_d_id AND no_o_id = d_no_o_id;
which in turn is due to TPC-C specification. Refer section 2.7.4.1 page 42 of the spec.