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:[email protected]] > *Sent:* Tue 02 June 2020 12:08 > *To:* Ling, Andy <[email protected]> > *Cc:* Gordan Bobic <[email protected]>; Mailing-List mariadb > <[email protected]> > *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 <[email protected] > <mailto:[email protected]>> 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>[email protected] > <mailto:[email protected]>] *On Behalf Of > *Gordan Bobic > *Sent:* Tue 02 June 2020 09:44 > *Cc:* Mailing-List mariadb <[email protected] > <mailto:[email protected]>> > *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, <[email protected] > <mailto:[email protected]>> wrote: > > MariaDB is still significantly slower. > > > > > > *From:*Roberto Spadim [mailto:[email protected] > <mailto:[email protected]>] > *Sent:* Mon 01 June 2020 18:33 > *To:* Ling, Andy <[email protected] > <mailto:[email protected]>> > *Cc:* Mailing-List mariadb <[email protected] > <mailto:[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] <mailto:[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] > <mailto:[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 > > <https://urldefense.com/v3/__https:/launchpad.net/*maria-discuss__;fg!!B_EDKQmmvsU!-OY7N3hOuVFtlZjCxVQuqUmeXvTFY__pTQgp8SPixHlx84xyuSbWyD2vStQhXKRi-enW$> > Post to : [email protected] > <mailto:[email protected]> > 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 : [email protected] > <mailto:[email protected]> > 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 : [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
