maria-discuss team mailing list archive

["Ling, Andy" <Andy.Ling@xxxxxxxxxxxxxxx>]

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@xxxxxxxxxxxxxxxxxxx] On Behalf Of Vicen?iu Ciorbaru
Sent: Tue 02 June 2020 16:15
To: maria-discuss@xxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxx]
Sent: Tue 02 June 2020 13:18
To: Ling, Andy <Andy.Ling@xxxxxxxxxxxxxxx><mailto:Andy.Ling@xxxxxxxxxxxxxxx>
Cc: Mailing-List mariadb <maria-discuss@xxxxxxxxxxxxxxxxxxx><mailto:maria-discuss@xxxxxxxxxxxxxxxxxxx>
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 <Andy.Ling@xxxxxxxxxxxxxxx<mailto:Andy.Ling@xxxxxxxxxxxxxxx>> 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.



_______________________________________________

Mailing list: https://launchpad.net/~maria-discuss<https://urldefense.com/v3/__https:/launchpad.net/*maria-discuss__;fg!!B_EDKQmmvsU!_5AScGkbJC77LxwsPKHWSPPyUJ8-FipiFXy4stFIqECnMBj2MRDfT7eWF2rrM660qVSf$>

Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx<mailto:maria-discuss@xxxxxxxxxxxxxxxxxxx>

Unsubscribe : https://launchpad.net/~maria-discuss<https://urldefense.com/v3/__https:/launchpad.net/*maria-discuss__;fg!!B_EDKQmmvsU!_5AScGkbJC77LxwsPKHWSPPyUJ8-FipiFXy4stFIqECnMBj2MRDfT7eWF2rrM660qVSf$>

More help   : https://help.launchpad.net/ListHelp<https://urldefense.com/v3/__https:/help.launchpad.net/ListHelp__;!!B_EDKQmmvsU!_5AScGkbJC77LxwsPKHWSPPyUJ8-FipiFXy4stFIqECnMBj2MRDfT7eWF2rrMy0gDKGW$>