maria-discuss team mailing list archive
Mailing list archive
MariaDB embedded, prepared statements
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:
("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.