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 _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
