MariaDB is still significantly slower. From: Roberto Spadim [mailto:roberto@spadim.com.br] Sent: Mon 01 June 2020 18:33 To: Ling, Andy Cc: Mailing-List mariadb 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 mailto:Andy.Ling@grassvalley.com> 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-discusshttps://urldefense.com/v3/__https:/launchpad.net/*maria-discuss__;fg!!B_EDKQ... Post to : maria-discuss@lists.launchpad.netmailto:maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discusshttps://urldefense.com/v3/__https:/launchpad.net/*maria-discuss__;fg!!B_EDKQ... More help : https://help.launchpad.net/ListHelphttps://urldefense.com/v3/__https:/help.launchpad.net/ListHelp__;!!B_EDKQmmv... -- Roberto Spadim SPAEmpresarial - Software ERP/Scada Eng. Automação e Controle, Eng. Financeira

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@lists.launchpad.net] On Behalf Of Gordan Bobic Sent: Tue 02 June 2020 09:44 Cc: Mailing-List mariadb 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, mailto:Andy.Ling@grassvalley.com> wrote: MariaDB is still significantly slower. From: Roberto Spadim [mailto:roberto@spadim.com.brmailto:roberto@spadim.com.br] Sent: Mon 01 June 2020 18:33 To: Ling, Andy mailto:Andy.Ling@grassvalley.com> Cc: Mailing-List mariadb mailto:maria-discuss@lists.launchpad.net> 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 mailto:Andy.Ling@grassvalley.com> 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-discusshttps://urldefense.com/v3/__https:/launchpad.net/*maria-discuss__;fg!!B_EDKQ... Post to : maria-discuss@lists.launchpad.netmailto:maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discusshttps://urldefense.com/v3/__https:/launchpad.net/*maria-discuss__;fg!!B_EDKQ... More help : https://help.launchpad.net/ListHelphttps://urldefense.com/v3/__https:/help.launchpad.net/ListHelp__;!!B_EDKQmmv... -- Roberto Spadim SPAEmpresarial - Software ERP/Scada Eng. Automação e Controle, Eng. Financeira _______________________________________________ Mailing list: https://launchpad.net/~maria-discusshttps://urldefense.com/v3/__https:/launchpad.net/*maria-discuss__;fg!!B_EDKQ... Post to : maria-discuss@lists.launchpad.netmailto:maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discusshttps://urldefense.com/v3/__https:/launchpad.net/*maria-discuss__;fg!!B_EDKQ... More help : https://help.launchpad.net/ListHelphttps://urldefense.com/v3/__https:/help.launchpad.net/ListHelp__;!!B_EDKQmmv... ********************************************************************** 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.

MariaDB is 10.4.12 MySQL is 5.5.34 – pretty old I know. Regards Andy Ling From: Vicențiu Ciorbaru [mailto:vicentiu@mariadb.org] Sent: Tue 02 June 2020 12:08 To: Ling, Andy Cc: Gordan Bobic ; Mailing-List mariadb 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 mailto:Andy.Ling@grassvalley.com> 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.lingmailto:maria-discuss-bounces%2Bandy.ling=grassvalley.com@lists.launchpad.netmailto:grassvalley.com@lists.launchpad.net] On Behalf Of Gordan Bobic Sent: Tue 02 June 2020 09:44 Cc: Mailing-List mariadb mailto:maria-discuss@lists.launchpad.net> 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, mailto:Andy.Ling@grassvalley.com> wrote: MariaDB is still significantly slower. From: Roberto Spadim [mailto:roberto@spadim.com.brmailto:roberto@spadim.com.br] Sent: Mon 01 June 2020 18:33 To: Ling, Andy mailto:Andy.Ling@grassvalley.com> Cc: Mailing-List mariadb mailto:maria-discuss@lists.launchpad.net> 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 mailto:Andy.Ling@grassvalley.com> 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-discusshttps://urldefense.com/v3/__https:/launchpad.net/*maria-discuss__;fg!!B_EDKQ... Post to : maria-discuss@lists.launchpad.netmailto:maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discusshttps://urldefense.com/v3/__https:/launchpad.net/*maria-discuss__;fg!!B_EDKQ... More help : https://help.launchpad.net/ListHelphttps://urldefense.com/v3/__https:/help.launchpad.net/ListHelp__;!!B_EDKQmmv... -- Roberto Spadim SPAEmpresarial - Software ERP/Scada Eng. Automação e Controle, Eng. Financeira _______________________________________________ Mailing list: https://launchpad.net/~maria-discusshttps://urldefense.com/v3/__https:/launchpad.net/*maria-discuss__;fg!!B_EDKQ... Post to : maria-discuss@lists.launchpad.netmailto:maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discusshttps://urldefense.com/v3/__https:/launchpad.net/*maria-discuss__;fg!!B_EDKQ... More help : https://help.launchpad.net/ListHelphttps://urldefense.com/v3/__https:/help.launchpad.net/ListHelp__;!!B_EDKQmmv... ________________________________ 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-discusshttps://urldefense.com/v3/__https:/launchpad.net/*maria-discuss__;fg!!B_EDKQ... Post to : maria-discuss@lists.launchpad.netmailto:maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discusshttps://urldefense.com/v3/__https:/launchpad.net/*maria-discuss__;fg!!B_EDKQ... More help : https://help.launchpad.net/ListHelphttps://urldefense.com/v3/__https:/help.launchpad.net/ListHelp__;!!B_EDKQmmv... ********************************************************************** 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.

Well I’ve had a go. Using MariaDB 5.5.68 and MyISAM tables I get times very similar to MySQL Some more definitive timings all on the same hardware… MySQL 5.5 MyISAM mysql> 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; +----------------------------------+ | rushid | +----------------------------------+ | 4de1e340d664dd87c4afda2c27f700a8 | | 455166dd2cefff65f578aa333f7a5581 | | 44f02723e901d2e958c58b9813ebaeae | +----------------------------------+ 3 rows in set (3.63 sec) MariaDB 5.5.68 MyISAM MariaDB [quentin_v3]> 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; +----------------------------------+ | rushid | +----------------------------------+ | 4de1e340d664dd87c4afda2c27f700a8 | | 455166dd2cefff65f578aa333f7a5581 | | 44f02723e901d2e958c58b9813ebaeae | +----------------------------------+ 3 rows in set (3.01 sec) MariaDB 10.4.2 MyISAM MariaDB [quentin_v3]> 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; +----------------------------------+ | rushid | +----------------------------------+ | 4de1e340d664dd87c4afda2c27f700a8 | | 455166dd2cefff65f578aa333f7a5581 | | 44f02723e901d2e958c58b9813ebaeae | +----------------------------------+ 3 rows in set (12.890 sec) MariaDB 10.4.2 Aria MariaDB [quentin_v3]> 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; +----------------------------------+ | rushid | +----------------------------------+ | 4de1e340d664dd87c4afda2c27f700a8 | | 455166dd2cefff65f578aa333f7a5581 | | 44f02723e901d2e958c58b9813ebaeae | +----------------------------------+ 3 rows in set (16.268 sec) So Aria is the slowest, but MariaDB 10 MyISAM is still a long way behind 5.5 Regards Andy Ling From: Vicențiu Ciorbaru [mailto:vicentiu@mariadb.org] Sent: Tue 02 June 2020 13:18 To: Ling, Andy Cc: Mailing-List mariadb Subject: [EXTERNAL] Re: [Maria-discuss] Poor performance compared to MySQL External Message:Use caution before opening links or attachments I don't really know what might have caused the slowdown. I'll ask around to see if any ideas come up. I know this might be a bit tedious for you, but can you try with the latest MariaDB 5.5 and see if you get the same bad performance? You'll probably have to set-up a new datadir from the MySQL deployment. MariaDB 5.5 should be very similar to MySQL 5.5. If we can narrow it down to a particular major version upgrade, it will be easier to track down where the slowdown happened. Probably analyzing performance logs could also hint towards a solution, but I have limited experience handling those. Vicențiu On Tue, 2 Jun 2020 at 14:12, Ling, Andy mailto:Andy.Ling@grassvalley.com> wrote: MariaDB is 10.4.12 MySQL is 5.5.34 – pretty old I know. Regards 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.

Am 02.06.20 um 17:03 schrieb Roberto Spadim:

Try to avoid compare aria/myisam cause it have a journal

how does that matter for selects and the point is "but MariaDB 10 MyISAM is still a long way behind 5.5" it's complety normal to also compare the same operations with different engines and the point is still MyISAM without anything else touched got way slower

Em ter., 2 de jun. de 2020 às 11:48, Ling, Andy mailto:Andy.Ling@grassvalley.com> escreveu:

Well I’ve had a go. Using MariaDB 5.5.68 and MyISAM tables I get times very similar to MySQL____

__ __

Some more definitive timings all on the same hardware…____

__ __

MySQL 5.5 MyISAM____

__ __

mysql> 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;____

+----------------------------------+____

| rushid                           |____

+----------------------------------+____

| 4de1e340d664dd87c4afda2c27f700a8 |____

| 455166dd2cefff65f578aa333f7a5581 |____

| 44f02723e901d2e958c58b9813ebaeae |____

+----------------------------------+____

3 rows in set (3.63 sec)____

__ __

__ __

MariaDB 5.5.68 MyISAM____

__ __

MariaDB [quentin_v3]> 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;____

+----------------------------------+____

| rushid                           |____

+----------------------------------+____

| 4de1e340d664dd87c4afda2c27f700a8 |____

| 455166dd2cefff65f578aa333f7a5581 |____

| 44f02723e901d2e958c58b9813ebaeae |____

+----------------------------------+____

3 rows in set (3.01 sec)____

__ __

MariaDB 10.4.2 MyISAM____

__ __

MariaDB [quentin_v3]> 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;____

+----------------------------------+____

| rushid                           |____

+----------------------------------+____

| 4de1e340d664dd87c4afda2c27f700a8 |____

| 455166dd2cefff65f578aa333f7a5581 |____

| 44f02723e901d2e958c58b9813ebaeae |____

+----------------------------------+____

3 rows in set (12.890 sec)____

__ __

MariaDB 10.4.2 Aria____

__ __

MariaDB [quentin_v3]> 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;____

+----------------------------------+____

| rushid                           |____

+----------------------------------+____

| 4de1e340d664dd87c4afda2c27f700a8 |____

| 455166dd2cefff65f578aa333f7a5581 |____

| 44f02723e901d2e958c58b9813ebaeae |____

+----------------------------------+____

3 rows in set (16.268 sec)____

__ __

So Aria is the slowest, but MariaDB 10 MyISAM is still a long way behind 5.5_

Am 02.06.20 um 17:13 schrieb Roberto Spadim:

yeap, i'm just trying to help to reproduce the problem 

i'm talking about the regression problem of speed between the same engine but different versions

tha focus on that when one comes up with numbers comparing exactly that and *additionally* provides numbers with Aria BTW: that below looks like after shot in the head because of using HTML mails previosuly

Em ter., 2 de jun. de 2020 às 12:07, Reindl Harald mailto:h.reindl@thelounge.net> escreveu:

Am 02.06.20 um 17:03 schrieb Roberto Spadim: > Try to avoid compare aria/myisam cause it have a journal

how does that matter for selects and the point is "but MariaDB 10 MyISAM is still a long way behind 5.5"

it's complety normal to also compare the same operations with different engines and the point is still MyISAM without anything else touched got way slower

> Em ter., 2 de jun. de 2020 às 11:48, Ling, Andy > mailto:Andy.Ling@grassvalley.com mailto:Andy.Ling@grassvalley.com>> escreveu: > >     Well I’ve had a go. Using MariaDB 5.5.68 and MyISAM tables I get >     times very similar to MySQL____ > >     __ __ > >     Some more definitive timings all on the same hardware…____ > >     __ __ > >     MySQL 5.5 MyISAM____ > >     __ __ > >     mysql> 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;____ > >     +----------------------------------+____ > >     | rushid                           |____ > >     +----------------------------------+____ > >     | 4de1e340d664dd87c4afda2c27f700a8 |____ > >     | 455166dd2cefff65f578aa333f7a5581 |____ > >     | 44f02723e901d2e958c58b9813ebaeae |____ > >     +----------------------------------+____ > >     3 rows in set (3.63 sec)____ > >     __ __ > >     __ __ > >     MariaDB 5.5.68 MyISAM____ > >     __ __ > >     MariaDB [quentin_v3]> 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;____ > >     +----------------------------------+____ > >     | rushid                           |____ > >     +----------------------------------+____ > >     | 4de1e340d664dd87c4afda2c27f700a8 |____ > >     | 455166dd2cefff65f578aa333f7a5581 |____ > >     | 44f02723e901d2e958c58b9813ebaeae |____ > >     +----------------------------------+____ > >     3 rows in set (3.01 sec)____ > >     __ __ > >     MariaDB 10.4.2 MyISAM____ > >     __ __ > >     MariaDB [quentin_v3]> 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;____ > >     +----------------------------------+____ > >     | rushid                           |____ > >     +----------------------------------+____ > >     | 4de1e340d664dd87c4afda2c27f700a8 |____ > >     | 455166dd2cefff65f578aa333f7a5581 |____ > >     | 44f02723e901d2e958c58b9813ebaeae |____ > >     +----------------------------------+____ > >     3 rows in set (12.890 sec)____ > >     __ __ > >     MariaDB 10.4.2 Aria____ > >     __ __ > >     MariaDB [quentin_v3]> 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;____ > >     +----------------------------------+____ > >     | rushid                           |____ > >     +----------------------------------+____ > >     | 4de1e340d664dd87c4afda2c27f700a8 |____ > >     | 455166dd2cefff65f578aa333f7a5581 |____ > >     | 44f02723e901d2e958c58b9813ebaeae |____ > >     +----------------------------------+____ > >     3 rows in set (16.268 sec)____ > >     __ __ > >     So Aria is the slowest, but MariaDB 10 MyISAM is still a long way >     behind 5.5_

Additionally, if you can share the data in any way so I can try to reproduce the problem that may help speed things up. I would have thought the data in just the two tables involved could be shared if that would be helpful. I need to check with others, but would the FRM, MYD & MYI files be enough? It amounts to about 2GB for the 6 files uncompressed. If I miss out the MYI files and let you regenerate the indexes it drops to 1.22 GB. Regards 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.

Actually, right now the Corporation FTP server seems to be offline. Only the public foundation one is available. If the data must stay private, feel free to send me a link privately. I'll make a note to clean up the KnowledgeBase and make sure it's up to date this week. On 02.06.2020 18:42, Vicențiu Ciorbaru wrote:

The 3 files should be enough. Let's include all 3.

You can upload them on the FTP server here if you don't want to share them publicly otherwise.

https://mariadb.com/kb/en/meta/mariadb-ftp-server/ https://mariadb.com/kb/en/meta/mariadb-ftp-server/#mariadb-foundationdevelop...

I'll have a look and see if I can reproduce it. Best if you provide the relevant config files too.

Vicențiu

On 02.06.2020 18:30, Ling, Andy wrote:

...

Additionally, if you can share the data in any way so I can try to reproduce the problem that may help speed things up.

I would have thought the data in just the two tables involved could be shared if that would be helpful. I need to check with others, but would the FRM, MYD & MYI files be enough? It amounts to about 2GB for the 6 files uncompressed. If I miss out the MYI files and let you regenerate the indexes it drops to 1.22 GB.

Regards

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

_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

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:vicentiu@mariadb.org] *Sent:* Tue 02 June 2020 12:08 *To:* Ling, Andy *Cc:* Gordan Bobic ; Mailing-List mariadb *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 mailto:Andy.Ling@grassvalley.com> 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=grassvalley.com@lists.launchpad.net mailto:grassvalley.com@lists.launchpad.net] *On Behalf Of *Gordan Bobic *Sent:* Tue 02 June 2020 09:44 *Cc:* Mailing-List mariadb mailto:maria-discuss@lists.launchpad.net> *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, mailto:Andy.Ling@grassvalley.com> wrote:

MariaDB is still significantly slower.

 

 

*From:*Roberto Spadim [mailto:roberto@spadim.com.br mailto:roberto@spadim.com.br] *Sent:* Mon 01 June 2020 18:33 *To:* Ling, Andy mailto:Andy.Ling@grassvalley.com> *Cc:* Mailing-List mariadb mailto:maria-discuss@lists.launchpad.net> *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 mailto:Andy.Ling@grassvalley.com> 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_EDKQ... Post to     : maria-discuss@lists.launchpad.net mailto:maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss https://urldefense.com/v3/__https:/launchpad.net/*maria-discuss__;fg!!B_EDKQ... More help   : https://help.launchpad.net/ListHelp https://urldefense.com/v3/__https:/help.launchpad.net/ListHelp__;!!B_EDKQmmv...

 

--

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_EDKQ... Post to     : maria-discuss@lists.launchpad.net mailto:maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss https://urldefense.com/v3/__https:/launchpad.net/*maria-discuss__;fg!!B_EDKQ... More help   : https://help.launchpad.net/ListHelp https://urldefense.com/v3/__https:/help.launchpad.net/ListHelp__;!!B_EDKQmmv...

------------------------------------------------------------------------

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_EDKQ... Post to     : maria-discuss@lists.launchpad.net mailto:maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss https://urldefense.com/v3/__https:/launchpad.net/*maria-discuss__;fg!!B_EDKQ... More help   : https://help.launchpad.net/ListHelp https://urldefense.com/v3/__https:/help.launchpad.net/ListHelp__;!!B_EDKQmmv...

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