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 <Andy.Ling@grassvalley.com>
Cc: Mailing-List mariadb <maria-discuss@lists.launchpad.net>
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@grassvalley.com> wrote:
MariaDB is 10.4.12
MySQL is 5.5.34 – pretty old I know.
Regards
Andy Ling