If it helps, here the handler information.
5.5.34-log MySQL Community Server (GPL)
mysql> show global status like '%handler%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 3 |
| Handler_read_key | 0 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 24 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 2 |
+----------------------------+-------+
16 rows in set (0.00 sec)
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.28 sec)
mysql> show global status like '%handler%';
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 3 |
| Handler_read_key | 784286 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 784328 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 18 |
+----------------------------+--------+
16 rows in set (0.00 sec)
================================
5.5.68-MariaDB mariadb.org binary distribution default my.ini
MariaDB [quentin_v3]> show global status like '%handler%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_icp_attempts | 0 |
| Handler_icp_match | 0 |
| Handler_mrr_init | 0 |
| Handler_mrr_key_refills | 0 |
| Handler_mrr_rowid_refills | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_deleted | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_tmp_update | 0 |
| Handler_tmp_write | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
24 rows in set (0.00 sec)
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 (2.99 sec)
MariaDB [quentin_v3]> show global status like '%handler%';
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_icp_attempts | 0 |
| Handler_icp_match | 0 |
| Handler_mrr_init | 0 |
| Handler_mrr_key_refills | 0 |
| Handler_mrr_rowid_refills | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 784286 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_deleted | 0 |
| Handler_read_rnd_next | 784312 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_tmp_update | 0 |
| Handler_tmp_write | 24 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+--------+
24 rows in set (0.00 sec)
MariaDB [quentin_v3]>
=====================================================================
10.4.12-MariaDB mariadb.org binary distribution default my.ini
MariaDB [quentin_v3]> show global status like '%handler%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 0 |
| Handler_icp_attempts | 0 |
| Handler_icp_match | 0 |
| Handler_mrr_init | 0 |
| Handler_mrr_key_refills | 0 |
| Handler_mrr_rowid_refills | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 3 |
| Handler_read_key | 0 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_retry | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_deleted | 0 |
| Handler_read_rnd_next | 19 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_tmp_delete | 0 |
| Handler_tmp_update | 0 |
| Handler_tmp_write | 2 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
27 rows in set (0.001 sec)
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 (6.008 sec)
MariaDB [quentin_v3]> show global status like '%handler%';
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 0 |
| Handler_icp_attempts | 0 |
| Handler_icp_match | 0 |
| Handler_mrr_init | 0 |
| Handler_mrr_key_refills | 0 |
| Handler_mrr_rowid_refills | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 3 |
| Handler_read_key | 784317 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_retry | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_deleted | 0 |
| Handler_read_rnd_next | 784334 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_tmp_delete | 0 |
| Handler_tmp_update | 0 |
| Handler_tmp_write | 29 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+--------+
27 rows in set (0.001 sec)
MariaDB [quentin_v3]>
Regards
Andy Ling
From: Maria-discuss [mailto:maria-discuss-bounces+andy.ling=grassvalley.com@lists.launchpad.net] On Behalf Of Vicen?iu Ciorbaru
Sent: Tue 02 June 2020 16:15
To: maria-discuss@lists.launchpad.net
Subject: [EXTERNAL] Re: [Maria-discuss] Poor performance compared to MySQL
External Message:Use caution before opening links or attachments
Ok, some debugging ideas:
Let's investigate if the number of times we're calling the storage engine code.
What would be useful information to have: Can you run
show global status like '%handler%';
Both for MariaDB and MySQL (both 5.5 versions and 10.4 version), before and after the problematic query.
(In total you should have 6 outputs, 4 for MariaDB (5.5 and 10.4 before and after the query) and 2 for MySQL (before and after the query).
We should compare how big of an increase we see for both servers. This may give us some direction if we see a big difference between the two. Additionally, if you can share the data in any way so I can try to reproduce the problem that may help speed things up.
Vicențiu
On 02.06.2020 17:48, Ling, Andy wrote:
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