maria-discuss team mailing list archive
-
maria-discuss team
-
Mailing list archive
-
Message #05809
Re: Poor performance compared to MySQL
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@xxxxxxxxxxx]
> *Sent:* Tue 02 June 2020 12:08
> *To:* Ling, Andy <Andy.Ling@xxxxxxxxxxxxxxx>
> *Cc:* Gordan Bobic <gordan.bobic@xxxxxxxxx>; Mailing-List mariadb
> <maria-discuss@xxxxxxxxxxxxxxxxxxx>
> *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 <Andy.Ling@xxxxxxxxxxxxxxx
> <mailto:Andy.Ling@xxxxxxxxxxxxxxx>> 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@xxxxxxxxxxxxxxxxxxx
> <mailto:grassvalley.com@xxxxxxxxxxxxxxxxxxx>] *On Behalf Of
> *Gordan Bobic
> *Sent:* Tue 02 June 2020 09:44
> *Cc:* Mailing-List mariadb <maria-discuss@xxxxxxxxxxxxxxxxxxx
> <mailto:maria-discuss@xxxxxxxxxxxxxxxxxxx>>
> *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, <Andy.Ling@xxxxxxxxxxxxxxx
> <mailto:Andy.Ling@xxxxxxxxxxxxxxx>> wrote:
>
> MariaDB is still significantly slower.
>
>
>
>
>
> *From:*Roberto Spadim [mailto:roberto@xxxxxxxxxxxxx
> <mailto:roberto@xxxxxxxxxxxxx>]
> *Sent:* Mon 01 June 2020 18:33
> *To:* Ling, Andy <Andy.Ling@xxxxxxxxxxxxxxx
> <mailto:Andy.Ling@xxxxxxxxxxxxxxx>>
> *Cc:* Mailing-List mariadb <maria-discuss@xxxxxxxxxxxxxxxxxxx
> <mailto:maria-discuss@xxxxxxxxxxxxxxxxxxx>>
> *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
> <Andy.Ling@xxxxxxxxxxxxxxx <mailto:Andy.Ling@xxxxxxxxxxxxxxx>>
> 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_EDKQmmvsU!7H4WuJBXqeFZECQVfG-JYPJ4jBQuogJuxG49NXvW345dDGFhkJyHmzNG8qfjprUme0Io$>
> Post to : maria-discuss@xxxxxxxxxxxxxxxxxxx
> <mailto:maria-discuss@xxxxxxxxxxxxxxxxxxx>
> Unsubscribe : https://launchpad.net/~maria-discuss
> <https://urldefense.com/v3/__https:/launchpad.net/*maria-discuss__;fg!!B_EDKQmmvsU!7H4WuJBXqeFZECQVfG-JYPJ4jBQuogJuxG49NXvW345dDGFhkJyHmzNG8qfjprUme0Io$>
> More help : https://help.launchpad.net/ListHelp
> <https://urldefense.com/v3/__https:/help.launchpad.net/ListHelp__;!!B_EDKQmmvsU!7H4WuJBXqeFZECQVfG-JYPJ4jBQuogJuxG49NXvW345dDGFhkJyHmzNG8qfjpt4IeMCL$>
>
>
>
>
> --
>
> 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_EDKQmmvsU!-OY7N3hOuVFtlZjCxVQuqUmeXvTFY__pTQgp8SPixHlx84xyuSbWyD2vStQhXKRi-enW$>
> Post to : maria-discuss@xxxxxxxxxxxxxxxxxxx
> <mailto:maria-discuss@xxxxxxxxxxxxxxxxxxx>
> Unsubscribe : https://launchpad.net/~maria-discuss
> <https://urldefense.com/v3/__https:/launchpad.net/*maria-discuss__;fg!!B_EDKQmmvsU!-OY7N3hOuVFtlZjCxVQuqUmeXvTFY__pTQgp8SPixHlx84xyuSbWyD2vStQhXKRi-enW$>
> More help : https://help.launchpad.net/ListHelp
> <https://urldefense.com/v3/__https:/help.launchpad.net/ListHelp__;!!B_EDKQmmvsU!-OY7N3hOuVFtlZjCxVQuqUmeXvTFY__pTQgp8SPixHlx84xyuSbWyD2vStQhXOKqY4e_$>
>
> ------------------------------------------------------------------------
>
> 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_EDKQmmvsU!4UFT8f04cYYM55c5ME51tXFYRuhAF9_UK1yGO_KT2TvbpYrXmN9ZdRUXOB2fV_xpubbz$>
> Post to : maria-discuss@xxxxxxxxxxxxxxxxxxx
> <mailto:maria-discuss@xxxxxxxxxxxxxxxxxxx>
> Unsubscribe : https://launchpad.net/~maria-discuss
> <https://urldefense.com/v3/__https:/launchpad.net/*maria-discuss__;fg!!B_EDKQmmvsU!4UFT8f04cYYM55c5ME51tXFYRuhAF9_UK1yGO_KT2TvbpYrXmN9ZdRUXOB2fV_xpubbz$>
> More help : https://help.launchpad.net/ListHelp
> <https://urldefense.com/v3/__https:/help.launchpad.net/ListHelp__;!!B_EDKQmmvsU!4UFT8f04cYYM55c5ME51tXFYRuhAF9_UK1yGO_KT2TvbpYrXmN9ZdRUXOB2fV_VSZAgj$>
>
> ------------------------------------------------------------------------
> 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@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help : https://help.launchpad.net/ListHelp
References
-
Poor performance compared to MySQL
From: Ling, Andy, 2020-06-01
-
Re: Poor performance compared to MySQL
From: Roberto Spadim, 2020-06-01
-
Re: [EXTERNAL] Re: Poor performance compared to MySQL
From: Ling, Andy, 2020-06-02
-
Re: [EXTERNAL] Re: Poor performance compared to MySQL
From: Gordan Bobic, 2020-06-02
-
Re: Poor performance compared to MySQL
From: Ling, Andy, 2020-06-02
-
Re: Poor performance compared to MySQL
From: Vicențiu Ciorbaru, 2020-06-02
-
Re: Poor performance compared to MySQL
From: Ling, Andy, 2020-06-02
