maria-discuss team mailing list archive

[Julien Muchembled <jm@xxxxxxxxxx>]

Hello,


In our application (NEO - https://neo.nexedi.com/ ), we have 2 backends: SQLite and MySQL/MariaDB

We also have a benchmarking tool working with a quite small dataset and showing that SQLite is ~30% than MariaDB.

For years, we supposed that SQLite performed better in some cases for 2 reasons:
- everything processed in same thread (i.e. no socket or context switches)
- precompiled queries

And by combining both, skip any kind of string serialization (i.e. do not format a query on one side, and parse on the other side).

Now, we are trying to optimize our MariaDB backend by trying to do like SQLite.

= MariaDB embedded

I could make it work, but only with InnoDB for the moment. I kept using the same binding, MySQLdb, which I rebuilt against libmariadbd-dev_10.1.29-6+b1_amd64.deb

Currently, we only use TokuDB in production and I have the same issue as:
  https://forums.mysql.com/read.php?94,629158,629158
  ("embedded server cannot accept a plugin engine")

This user solved it by embedding his engine inside libmysqld. I wonder if it will work with TokuDB.

Anyway, I haven't seen any speed improvement.

= Prepared statements

I couldn't find anything else that's closer to SQLite precompiled queries. Which means that there's still some formatting/parsing.

| Com_stmt_execute        | 96528 |
| Com_stmt_prepare        | 40    |

But again, not faster. Well, I do my best to have stable results, like having the DB in tmpfs or disabling C-states, but it's hard to measure small changes of performance. With 10.1.29, prepared statements look slightly slower. I also tried the new EXECUTE syntax with 10.2.16 to avoid SET queries, and it may be 1% faster (< error margin).

I only turned the most often used queries into prepared statements. Here is one of the most complex queries we have:

  SELECT tid, compression, data.hash, value, value_tid
    FROM obj FORCE INDEX(PRIMARY) LEFT JOIN data ON (obj.data_id = data.id)
    WHERE `partition`=? AND oid=? AND tid < ?
    ORDER BY tid DESC LIMIT 1

https://mariadb.com/kb/en/library/prepared-statements/ should explain the purpose of prepared statements. The "In addition to using prepared statements from the libmysqld" clause seems to tell there's a specific kind of prepared statements within MariaDB embedded but I haven't found anything about that. 


We're disappointed by all these results. Did I miss something ?

If not, I'll have to do some profiling of MariaDB.


Julien