Hadn't used MySQL family for years. Now need to move from OracleDB to either MariaDB or PostgreSQL.
So on a similar "hardware" (
Openstack VM: RHEL 7.3,
4HT cores of 2.5GHz Xeon E312xx,
"local" SAN & SATA storage) got
Created a simple set of 10-column tables with numeric primary-key and no indexes/triggers/etc. A java (hibernate+jdbc) 1-threaded 1-connection app doing purely 6 inserts in a loop (with commit every 100 cycles).
I'm either doing something wrong or over-expecting the performance of MariaDB, but on OracleDB this routine is taking 348ms/cycle (6*100 inserts + commit) on MariaDB same takes 3291ms/cycle, in other words about 10x slower than OracleDB on same hardware (all i'm switching in java app is the hibernate dialect and connection url/user/pass). I did double-check using tcpdumps that app is behaving same performance, it's the response-time-from-DB that differs so much.
Given what I see so far - should I just lower my MariaDB performance expectations or should I rather continue digging into IOwait-tracing or fine-tuning of MariaDB? Just couldn't find a reference anywhere of what a "normal" performance would be for MariaDB (on some reference-hardware)... 5+ms/insert just feels "too-much"...
Found an article:
(doing 1000-inserts batch) completing in 5ms, so about 5 MICRO(!)-seconds per insert....
Seems our setup works about 1000x slower than expected...
Moved away from Openstack VM to a bare-metal equivalent (all same params, except the CPU is 4HT cores of Xeon E5450 @3GHz). Installed OracleDB and MariaDB with default settings, migrated the schemas.
On bare-metal MariaDB the "6*100 INSERTs + commit" took 430ms/cycle which is totally comparable to OracleDB (also totally good in a sense of doing it via plain jdbc/PreparedStatements without any batching - quite acceptable). Same with "6*100 DELETEs + commit" - 625ms/cycle. Now the only remaining issue I'm facing is the SelectForUpdates/UPDATEs being slow..
The following mixture (same done by app and thus chosen as micro-bench) performance on MariaDB:
- 13 SELECTs + 5 SFUs + 6 UPDATEs + commit = 252ms on former Openstack VM setup
- 13 SELECTs + 5 SFUs + 6 UPDATEs + commit = 133ms on new bare-metal setup
- 13 SELECTs + 0 SFUs + 6 UPDATEs + commit = 122ms on new bare-metal setup
- 13 SELECTs + 5 SFUs + 0 UPDATEs + commit = 122ms on new bare-metal setup
for comparison, on OracleDB Openstack VM:
- 13 SELECTs + 5 SFUs + 6 UPDATEs + commit = 14ms
At the moment - exhausted the obvious/documented optimizations and trying some counter-intuitive tests, such as disabling any partitioning/indexes/foreign-keys/etc...