Can you post EXPLAIN from both? Is it exactly the same? On Tue, 2 Jun 2020, 09:21 Ling, Andy, <[email protected]> wrote:
> MariaDB is still significantly slower. > > > > > > *From:* Roberto Spadim [mailto:[email protected]] > *Sent:* Mon 01 June 2020 18:33 > *To:* Ling, Andy <[email protected]> > *Cc:* Mailing-List mariadb <[email protected]> > *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 <[email protected]> > 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 : [email protected] > 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 : [email protected] > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp >
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
