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

Vlad
  • 111
  • 2
  • 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: https://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
  • @vlad Before you try the 100-thread test, I would suggest in mysqld thread_cache_size=100 in your my.cnf. Thanks for the pastebin ulimit Open Files looks good to me. Yes, would like the data requested a couple hours ago with the BEFORE and AFTER of SHOW GLOBAL STATUS; and all the other parts whenever you can put the TESTING together. Hoping you can do the same test after I provide my suggestions. Please view my profile, Network profile for contact info and get in touch with Skype. Thanks – Wilson Hauck Nov 20 '18 at 18:31
  • @Vlad iostat -xm 5 3 would show us your # cores. Complete MySQLTuner report would be nice. More critical is need for B) SHOW GLOBAL STATUS; BEFORE your benchmark testing AND B2) SHOW GLOBAL STATUS; AFTER testing C) SHOW GLOBAL VARIABLES; to analyze data. Posting on pastebin.com would be good. – Wilson Hauck Nov 27 '18 at 14:14
  • @Vlad Any chance we would Skype CHAT or TALK? Using VOIP at no cost? – Wilson Hauck Dec 27 '18 at 22:46
  • Have you posted the things Wilson asked for? How about `SHOW CREATE TABLE` and the queries in the benchmark -- paying special attention to when `COMMITs` are performed. – Rick James Jan 02 '19 at 05:38
  • @WilsonHauck and RickJames, sorry I was dragged away from the project as soon as we've resolved the slowdowns/bottlenecks (caused by partitioning and virtualization in our case). If mgmt ever decides to continue - I'll be happy to gather/share the data. Trying to find out how to "close" the srvfault question... – Vlad Jan 28 '19 at 19:19
  • @Vlad To close the question you can simply post what you did, if Wilson post it just upvote him, but else you can mark your answer – yagmoth555 Jan 30 '19 at 01:03

0 Answers0