SELECT performance on MariaDB Galera cluster vs. MariaDB is terrible

We are evaluating a Galera setup and so far we have not noticed to many drawbacks except a few queries that have terrible READ performance I cannot put my finger on it.

The query by itself isn't really optimized but it comes back under 0.20 seconds on the production box. And takes several minutes on a beefy galera 3-node setup. (on much beefier hardware in fact).

The versions are for galera:

mysqld  Ver 10.0.16-MariaDB-1~trusty-wsrep-log for 
debian-linux-gnu on x86_64 (mariadb.org binary distribution, wsrep_25.10.r4144)

and for the 'old' prod machine

mysqld  Ver 5.3.12-MariaDB-mariadb122~maverick for 
debian-linux-gnu on x86_64 ((MariaDB - http://mariadb.com/))

The query:

``
MariaDB [ticketing]> EXPLAIN SELECT DISTINCT purchase.id,
purchase.invoiceid, purchase.userid, purchase.currencyid, purchase.purchasestatusid, purchase.isdeleted,
purchase.emailshistory, purchase.created,
purchase.paymentfee FROM purchase
INNER JOIN payment ON payment.purchaseid = purchase.id
WHERE (invoiceid IS NULL) AND (purchasetypeid = 1)
AND (purchase.created >= '2015-01-19 10:40:17')
AND (paymenttypeid = 15) ORDER BY created` DESC;
+------+-------------+----------+--------+-------------------------------------------------------------------+-----------------+---------+------------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+--------+-------------------------------------------------------------------+-----------------+---------+------------------------------+-------+----------------------------------------------+
| 1 | SIMPLE | payment | ref | purchaseid,paymenttypeid_2

I managed to solve this -for the most part- by changing a few parameters. apparently it looks like the database needs to warm up by opening the tables at least once. This is behavior I am familiar with since mysql 5 with plenty of tables and databases.

The query still runs slow when it's the first time it runs (TABLE CACHE IS OFF). By changing the parameters below, this only happens on the first run of the query. Any additional attempts to run it after that does not exhibit this slow down anymore. The following parameters helped

[mysqld_safe]
open-files-limit = 65535

And

[mysqld]
innodb_open_files       = 16384

table_cache = 800
table_open_cache = 40000
table_definition_cache = 3000
thread_cache_size = 50

I believe the reason is that the information_scheme is the bottleneck here. Afaik, a known problem when having plenty of tables around.

StackExchange Database Administrators Q#97560, answer score: 2