maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

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


-- 
Roberto Spadim
SPAEmpresarial - Software ERP/Scada
Eng. Automação e Controle, Eng. Financeira