1

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 Oracle11g and MariaDB10.3 side-by-side.

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"...


Update:

Found an article:

showing their PrepareStatementBatch100InsertPrepareHit.mariadb
(doing 1000-inserts batch) completing in 5ms, so about 5 MICRO(!)-seconds per insert....
Seems our setup works about 1000x slower than expected...


Update 2

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...

  • 1
    Is this micro-benchmark at all similar to the actual applications you will be running? You would be better served picking one by any criteria, migrating to it, then optimizing for your actual workload. – John Mahowald Nov 18 '18 at 13:19
  • 1
    @JohnMahowald - yes, this micro-benchmark is built precisely off the logs of the actual application. It didn't make sense to go further (to do multi-threaded load) while single-threaded performance is still ~10x worse than expected. We did resolve the issues with INSERT/SELECT/DELETE by just going off OpenStackVM into bare-metal (see update) But UPDATEs are still very-slow (same ~10x curlpit). Thank You for looking into it. – Vlad Nov 19 '18 at 21:16
  • You already are doing the comparison testing needed to find what the differences are. I don't have much to add other than good luck, and perhaps you will find insight with the DBAs on the SE network: dba.stackexchange.com – John Mahowald Nov 19 '18 at 21:18
  • @vlad Additional information request, please. Post on pastebin.com or here. RAM size of your MySQL Host server A) complete (not edited) my.cnf or my.ini Text results of: B) SHOW GLOBAL STATUS; BEFORE your benchmark testing AND B2) SHOW GLOBAL STATUS; AFTER testing C) SHOW GLOBAL VARIABLES; D) complete MySQLTuner report after BENCHMARK AND Optional very helpful information, includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -xm 5 3 when system is busy for an idea of IOPS by device, for server tuning analysis. – Wilson Hauck Nov 20 '18 at 15:41
  • 1
    @WilsonHauck, thx! We've managed to drop the 133ms down to 16ms by just removing the PARTITIONS 1024 on the table which DBAs "optimized" for us during the ddl (will update the question later). 32GB of RAM, currently doing 1-threaded population of ~50mln rows for further 100-threaded run (so no real load-test today yet). Current free/ulimit/top is on pastebin.com/z1vrH0n7 Didn't use MySQLTuner yet, will likely be able to do #A/B/C once we start 100-thread test if You're still interested. – Vlad Nov 20 '18 at 18:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.