I am looking at switching from MySQL to MariaDB and have been comparing the
performance of the two.
I am using the same databases on the same Windows machine and running queries
using MySQL and MariaDB
and I am finding that MariaDB is 6 times slower. A query that takes 5 seconds
on MySQL is taking 28 seconds on MariaDB.
I am hoping I have some configuration wrong, so I looking for some help to work
out what needs changing.
One of the problem queries is a join between two tables. Analyzing the query
gives..
ANALYZE FORMAT=JSON SELECT r.rushid FROM rushes r LEFT JOIN browse b ON
r.rushID = b.rushID WHERE b.rushID IS NULL AND r.updated < NOW() - INTERVAL 1
DAY;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------+
| ANALYZE
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------+
| {
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 107974,
"table": {
"table_name": "r",
"access_type": "ALL",
"r_loops": 1,
"rows": 784286,
"r_rows": 784286,
"r_total_time_ms": 245.5,
"filtered": 100,
"r_filtered": 100,
"attached_condition": "r.updated < '2020-05-31 16:35:59'"
},
"table": {
"table_name": "b",
"access_type": "ref",
"possible_keys": ["rushid_start", "rushid"],
"key": "rushid",
"key_length": "96",
"used_key_parts": ["rushID"],
"ref": ["quentin_v3afp.r.rushID"],
"r_loops": 784286,
"rows": 10,
"r_rows": 1,
"r_total_time_ms": 106252,
"filtered": 100,
"r_filtered": 100,
"attached_condition": "trigcond(b.rushID is null)",
"using_index": true,
"not_exists": true
}
}
} |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------+
1 row in set (1 min 48.244 sec)
The table has been converted to the Aria engine from MyISAM. The my.ini file
has had the following added/changed..
#Not using MyISAM so save memory
key_buffer_size=64k
#Setting to improve Aria performance
aria_pagecache_buffer_size=4007M
tmp_table_size=35M
max_heap_table_size=35M
Thanks for any help.
Andy Ling
**********************************************************************
DISCLAIMER:
Privileged and/or Confidential information may be contained in this message. If
you are not the addressee of this message, you may not copy, use or deliver
this message to anyone. In such event, you should destroy the message and
kindly notify the sender by reply e-mail. It is understood that opinions or
conclusions that do not relate to the official business of the company are
neither given nor endorsed by the company. Thank You.
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
