MySQL 8 vs MariaDB - Important!!!

[Marius]

I started to notice something rather strange lately and until now i'm not sure if the problem comes from latest windows updates (Windows 10 X64 v1809 build 17763.437), or from the newest versions of MySQL.
When i do any search is working really slow, so i did some benchmarks using both MySQL connector (which is used by RadioDJ) and in HeidiSQL.

The results are incredible:

MariaDB v10.3.14 X64 on a SSD:

Running search query in RadioDJ:

Code Select Expand

Query Duration: 0.0081946 seconds = SELECT COUNT( ID ) FROM `songs` WHERE `enabled`=1 AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01'))  AND (`artist` LIKE '%abba%' OR `title` LIKE '%abba%' OR `album` LIKE '%abba%')

Running search query in HeidiSQL v10.1.0.5557

Code Select Expand

SELECT COUNT( ID ) FROM `songs` WHERE `enabled`=1 AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01'))  AND (`artist` LIKE '%abba%' OR `title` LIKE '%abba%' OR `album` LIKE '%abba%') ;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 0.016 sec. */

MySQL v8.0.16 X64 on the same SSD and same system:

Running search query in RadioDJ:

Code Select Expand

Query Duration: 1.7712764 seconds = SELECT COUNT( ID ) FROM `songs` WHERE `enabled`=1 AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01'))  AND (`artist` LIKE '%abba%' OR `title` LIKE '%abba%' OR `album` LIKE '%abba%')

Running search query in HeidiSQL v10.1.0.5557:

Code Select Expand

SELECT COUNT( ID ) FROM `songs` WHERE `enabled`=1 AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01'))  AND (`artist` LIKE '%abba%' OR `title` LIKE '%abba%' OR `album` LIKE '%abba%') ;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 1.890 sec. */

As you can see the difference is huge, on MySQL it takes almost 2 seconds just to run a single query (without any joins). When running RadioDJ in the background may run multiple queries (like updating the playing track, track search etc) and with so many time spent on each query is not a surprise that the program will become very slow.

So if you notice that RadioDJ becomes sluggish, please try to move the database to MariaDB to see if it fixes the problem.

[Marius]

UPDATE: after messing up with the indexes i got as low as 0.047 seconds for the same query in MySQL (having a total of ~2200 entries in the database). Still not as good as 0.016 as in MariaDB, but definitively better than 1.89 seconds...
I will update the database schema for the next release after a bit more testing.

[stevewa]

is there really any reason to use MySQL, when MariaDB does it all, and with a much smaller footprint and better performance? Just change the software requirements to be all MariaDB, and no MySQL...

[madhatter]

Mine is very sluggish running MySQL.  How do you switch from MySQL to MariaDB?

[renelatour1972]

I started to notice something rather strange lately and until now i'm not sure if the problem comes from latest windows updates (Windows 10 X64 v1809 build 17763.437), or from the newest versions of MySQL.
When i do any search is working really slow, so i did some benchmarks using both MySQL connector (which is used by RadioDJ) and in HeidiSQL.

The results are incredible:

MariaDB v10.3.14 X64 on a SSD:

Running search query in RadioDJ:

Code Select Expand

Query Duration: 0.0081946 seconds = SELECT COUNT( ID ) FROM `songs` WHERE `enabled`=1 AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01'))  AND (`artist` LIKE '%abba%' OR `title` LIKE '%abba%' OR `album` LIKE '%abba%')

Running search query in HeidiSQL v10.1.0.5557

Code Select Expand

SELECT COUNT( ID ) FROM `songs` WHERE `enabled`=1 AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01'))  AND (`artist` LIKE '%abba%' OR `title` LIKE '%abba%' OR `album` LIKE '%abba%') ;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 0.016 sec. */

MySQL v8.0.16 X64 on the same SSD and same system:

Running search query in RadioDJ:

Code Select Expand

Query Duration: 1.7712764 seconds = SELECT COUNT( ID ) FROM `songs` WHERE `enabled`=1 AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01'))  AND (`artist` LIKE '%abba%' OR `title` LIKE '%abba%' OR `album` LIKE '%abba%')

Running search query in HeidiSQL v10.1.0.5557:

Code Select Expand

SELECT COUNT( ID ) FROM `songs` WHERE `enabled`=1 AND ((`start_date` <= Now()) AND (`end_date` >= NOW() OR `end_date` = '2002-01-01 00:00:01'))  AND (`artist` LIKE '%abba%' OR `title` LIKE '%abba%' OR `album` LIKE '%abba%') ;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 1.890 sec. */

As you can see the difference is huge, on MySQL it takes almost 2 seconds just to run a single query (without any joins). When running RadioDJ in the background may run multiple queries (like updating the playing track, track search etc) and with so many time spent on each query is not a surprise that the program will become very slow.

So if you notice that RadioDJ becomes sluggish, please try to move the database to MariaDB to see if it fixes the problem.

i have a problem ti install mariadb 10.3 on windows 10 64 bit..the error code is 2503 and 2502

[Calypso]

i have a problem ti install mariadb 10.3 on windows 10 64 bit..the error code is 2503 and 2502

Did run the MariaDB installer as Administrator? The 2503/2502 Windows Error codes in Windows 10 usually are permission problems.

[renelatour1972]

Did run the MariaDB installer as Administrator? The 2503/2502 Windows Error codes in Windows 10 usually are permission problems.

yes..!!but it's the same!!

[Jhonny]

Dit you stop or uninstalled the first sql server?

[stevewa]

When installing on Windows 8 or later, an internal error 2503 and 2502 may pop up during install.

Resolution

1. From the Start option, find the shortcut to the cmd.exe program, right click on it and make sure to open it using the 'Run as Administrator' option
2. Depending on your User Account Control settings you may have to respond OK to the question as to whether to Allow changes to the PC.
3. Proceeding should open a black window with some text in it something like this
C:\Windows\system32>
4. Type in the path to the .msi file causing the problem and press return e.g. if the .msi is located in the C:Temp folder then you need to type C:\Temp\WhateverTheProgramNameIs.msi
5. The install should then proceed correctly.

[Trey]

How about a step by step tutorial on how to migrate from MySQL to MariaDB

I've seen several requests in other threads but no responses.


Also, please consider changing MySql from recommended to 'it works...but' in the system requirements.  I selected MySQL only because I was unfamiliar with MariaDB name.

[Jhonny]

How to uninstall MySQL from windows 7 / windows 8 / windows 8.1

https://www.youtube.com/watch?v=G-HwMi6tiII

Installing MariaDB on Windows

https://www.youtube.com/watch?v=yQPnCxJMOWI&t=73s

step by step

   
• backup the database with radio DJ
• uninstall mysql.
• install mariadb 32 or 64 msi.
• Restor the database with radio DJ.
• that is it.

[Jhonny]

Just made a tutorial how to move from MySQL to MariaDB
Hope this is informative enough.
Tutorial.

[chrismadog]

Stevewa,

You said

is there really any reason to use MySQL, when MariaDB does it all, and with a much smaller footprint and better performance? Just change the software requirements to be all MariaDB, and no MySQL...

I run a server with Mysql that also has other tasks using EasyPHP (LAMP server) for access over the network for scheduling applications and heap of other things. It is also in use for fault recording (Station technical problems) and  other things.
They use Apache, PHP and MySQL. And they work just fine together with occasional use of each (except RadioDJ on 24/7/365) and no interactions or transactions between them - and more importantly, no problems. Rock solid.

Requiring me to change all of them over to MariaDb would mean I would replace RadioDJ with something that =will= handle MySQL or something that doesn't require either MySQL or MariaDB so I can continue to use the other applications as at present.

"My way or the Highway" is in my very honest opinion and experience, not a good way to go.

I will one day, install MariaDB on the server when I can get the rest all migrated away from it but it is way, way, way, way down the priority list of work to be done. We are about to move the station to new premises and the transmitter to a different location, possibly 25Km away. I don't have the time nor the inclination to change everything on top of my current workload.

Regards,
Chris

[KJ6EO]

I have the sluggish database problem with MySQL once in a while.  To fix it, I just do a database backup, then restore.  Works every time.

[AdrianHondema]

I too had serious performance issues with mysql, resulting in queries that were canceled after 30 seconds. I had done a developer installation of mysql. I deleted mysql completely (after having made a backup first) and performed a new install in dedicated mode. In this mode, mysql can use more internal memory. It solved my problems!