← Back to team overview

maria-discuss team mailing list archive

  1. maria-discuss team
  2. Mailing list archive
  3. Message #05798

Re: Poor performance compared to MySQL

  Thread Previous • Date Previous • • Thread Next 
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> 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=
> grassvalley.com@xxxxxxxxxxxxxxxxxxx] *On Behalf Of *Gordan Bobic
> *Sent:* Tue 02 June 2020 09:44
> *Cc:* Mailing-List mariadb <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> wrote:
>
> MariaDB is still significantly slower.
>
>
>
>
>
> *From:* Roberto Spadim [mailto:roberto@xxxxxxxxxxxxx]
> *Sent:* Mon 01 June 2020 18:33
> *To:* Ling, Andy <Andy.Ling@xxxxxxxxxxxxxxx>
> *Cc:* 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*
>
> what happen when comparing myisam-myisam?
>
>
>
> Em seg., 1 de jun. de 2020 às 12:51, Ling, Andy <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
> 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
> 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
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>

Follow ups

References

  Thread Previous • Date Previous • • Thread Next