We have a lot of MariaDB's that we have recently moved to "Azure Database for MariaDB server". Most of the time it works OK, but we sometimes experience some snags with slow queries compared to running on our old servers. Let's just call our database "customerdb".
For example, we have two big tables, data_orders and data_orders_cardpayment, both quite big (data orders also contains an xml reprecentation of the order). For a customer that has about 900K rows in data_orders, the following query looks like this:
explain SELECT SQL_NO_CACHE
SUM(T0.GiftCard+T0.CreditNote+T0.Nettbank+T0.Depositum) AS Sum
FROM DATA_ORDERS_CARDPAYMENT T0
INNER JOIN DATA_ORDERS T1 ON T0.OrderId = T1.Id
WHERE T1.IsTransferred = 0
AND T1.CountedId = -1
AND T1.InstanceCode = 'B';
+------+-------------+-------+-------------+----------------------------------------------+-------------------------+---------+--------------------+------+-------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------------+----------------------------------------------+-------------------------+---------+--------------------+------+-------------------------------------------------------+
| 1 | SIMPLE | T1 | index_merge | PRIMARY,CountedId,InstanceCode,IsTransferred | IsTransferred,CountedId | 1,4 | NULL | 256 | Using intersect(IsTransferred,CountedId); Using where |
| 1 | SIMPLE | T0 | ref | OrderId | OrderId | 4 | customerdb.T1.Id | 1 | |
+------+-------------+-------+-------------+----------------------------------------------+-------------------------+---------+--------------------+------+-------------------------------------------------------+
So this only needs to check 256 rows? Good! Then I run this query at the start of the day, and it takes like this:
SELECT SQL_NO_CACHE
SUM(T0.GiftCard+T0.CreditNote+T0.Nettbank+T0.Depositum) AS Sum
FROM DATA_ORDERS_CARDPAYMENT T0
INNER JOIN DATA_ORDERS T1 ON T0.OrderId = T1.Id
WHERE T1.IsTransferred = 0
AND T1.CountedId = -1
AND T1.InstanceCode = 'B';
+------+
| Sum |
+------+
| 0 |
+------+
1 row in set (1 min 38.283 sec)
So this took 1 minute and 38 seconds. Running it again, it takes only 0.119 seconds, 0.128 seconds, 0.106 seconds etc. I ran the same query on our old database server on the old database "customerdb_moved", there it took 2.68 seconds at the start of the day, and then 0.07 seconds afterwards (but that was a quite beefed up server).
So, why does it take forever the first time, and only the first time? I assume it for some unknown reason must load most of the table data from disk into memory, even though the indexes in theory should avoid this. Successive loads are always fast.
Can I avoid the initial loading from disk? If not, how do I speed up the initial disk access time in my Azure database? (BTW, at the moment, the pricing tier for the database server in Azure is 8 vCores, 80GB memory, 292 GB storage, 876 available IOPS, memory optimized.)
[Update] I found the main culprit at least. The explain statement somehow got things wrong. When running the following:
SELECT COUNT(*)
FROM data_orders T1
where T1.IsTransferred = 0
AND T1.CountedId = -1
AND T1.InstanceCode = 'B';
It actually returned about 5,5k rows, as there was some old lines that didn't have the T1.IsTransferred-flag set. I don't know why my explain returned 256 rows, and I still think the original query should take way less time than 1 minutes and 38 seconds initially for summing over these 5,5k rows on the first loop for the day. When setting the old lines to IsTransferred, the query today shows 5 rows for T1 in explain, while the count(*) FROM T1 shows 164 actual rows, and the query took about 3 seconds for the initial first time (still quite a lot for only a load of 164 rows) and then about 0.02 for the successive queries.
