I saw huge slowness in MariaDB 10.1 myself when coming from Mysql 5.5, it was mostly the query analyzer and seemed to be fixed in 10.4 Look like i should check if it's really the case ... On 02/06/2020 13:11, Ling, Andy wrote:
MariaDB is 10.4.12
MySQL is 5.5.34 – pretty old I know.
Regards
Andy Ling
*From:*Vicențiu Ciorbaru [mailto:vicentiu@mariadb.org] *Sent:* Tue 02 June 2020 12:08 *To:* Ling, Andy
*Cc:* Gordan Bobic ; Mailing-List mariadb *Subject:* [EXTERNAL] Re: [Maria-discuss] Poor performance compared to MySQL
*External Message:Use caution before opening links or attachments*
For completeness, can you specify which versions of MySQL & MariaDB you are comparing?
I assume both servers are running on the same hardware (not an SSD for MySQL and a hard drive for MariaDB), is that correct? As this is a time-related query, did you run both queries using the same data & roughly the same time of day?
Also, since the query plans are the same and it's not a storage engine issue (myisam - myisam is still slower on MariaDB's side), I need to check if there was anything Windows specific that was changed in MariaDB.
Vicențiu
On Tue, 2 Jun 2020 at 13:58, Ling, Andy
mailto:Andy.Ling@grassvalley.com> wrote: mysql> EXPLAIN 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;
+----+-------------+-------+------+---------------+--------------+---------+---------------------+--------+--------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------------+---------+---------------------+--------+--------------------------------------+
| 1 | SIMPLE | r | ALL | NULL | NULL | NULL | NULL | 784286 | Using where |
| 1 | SIMPLE | b | ref | rushid_start | rushid_start | 96 | quentin_v3.r.rushID | 10 | Using where; Using index; Not exists |
+----+-------------+-------+------+---------------+--------------+---------+---------------------+--------+--------------------------------------+
2 rows in set (0.00 sec)
MariaDB [quentin_v3]> EXPLAIN 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;
+------+-------------+-------+------+---------------+--------------+---------+---------------------+--------+--------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+--------------+---------+---------------------+--------+--------------------------------------+
| 1 | SIMPLE | r | ALL | NULL | NULL | NULL | NULL | 784286 | Using where |
| 1 | SIMPLE | b | ref | rushid_start | rushid_start | 96 | quentin_v3.r.rushID | 10 | Using where; Using index; Not exists |
+------+-------------+-------+------+---------------+--------------+---------+---------------------+--------+--------------------------------------+
2 rows in set (0.001 sec)
Look pretty similar.
Regards
Andy Ling
*From:*Maria-discuss [mailto:maria-discuss-bounces+andy.ling mailto:maria-discuss-bounces%2Bandy.ling=grassvalley.com@lists.launchpad.net mailto:grassvalley.com@lists.launchpad.net] *On Behalf Of *Gordan Bobic *Sent:* Tue 02 June 2020 09:44 *Cc:* Mailing-List mariadb
mailto:maria-discuss@lists.launchpad.net> *Subject:* Re: [Maria-discuss] [EXTERNAL] Re: Poor performance compared to MySQL
Can you post EXPLAIN from both? Is it exactly the same?
On Tue, 2 Jun 2020, 09:21 Ling, Andy,
mailto:Andy.Ling@grassvalley.com> wrote: MariaDB is still significantly slower.
*From:*Roberto Spadim [mailto:roberto@spadim.com.br mailto:roberto@spadim.com.br] *Sent:* Mon 01 June 2020 18:33 *To:* Ling, Andy
mailto:Andy.Ling@grassvalley.com> *Cc:* Mailing-List mariadb mailto:maria-discuss@lists.launchpad.net> *Subject:* [EXTERNAL] Re: [Maria-discuss] Poor performance compared to MySQL
*External Message:Use caution before opening links or attachments*
what happen when comparing myisam-myisam?
Em seg., 1 de jun. de 2020 às 12:51, Ling, Andy
mailto:Andy.Ling@grassvalley.com> escreveu: 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 https://urldefense.com/v3/__https:/launchpad.net/*maria-discuss__;fg!!B_EDKQ... Post to : maria-discuss@lists.launchpad.net mailto:maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss https://urldefense.com/v3/__https:/launchpad.net/*maria-discuss__;fg!!B_EDKQ... More help : https://help.launchpad.net/ListHelp https://urldefense.com/v3/__https:/help.launchpad.net/ListHelp__;!!B_EDKQmmv...
--
Roberto Spadim SPAEmpresarial - Software ERP/Scada
Eng. Automação e Controle, Eng. Financeira
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss https://urldefense.com/v3/__https:/launchpad.net/*maria-discuss__;fg!!B_EDKQ... Post to : maria-discuss@lists.launchpad.net mailto:maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss https://urldefense.com/v3/__https:/launchpad.net/*maria-discuss__;fg!!B_EDKQ... More help : https://help.launchpad.net/ListHelp https://urldefense.com/v3/__https:/help.launchpad.net/ListHelp__;!!B_EDKQmmv...
------------------------------------------------------------------------
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 https://urldefense.com/v3/__https:/launchpad.net/*maria-discuss__;fg!!B_EDKQ... Post to : maria-discuss@lists.launchpad.net mailto:maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss https://urldefense.com/v3/__https:/launchpad.net/*maria-discuss__;fg!!B_EDKQ... More help : https://help.launchpad.net/ListHelp https://urldefense.com/v3/__https:/help.launchpad.net/ListHelp__;!!B_EDKQmmv...
------------------------------------------------------------------------ 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