maria-discuss team mailing list archive

[Gordan Bobic <gordan.bobic@xxxxxxxxx>]

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
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>