RadioDJ - Free Radio Automation Software Forum

RadioDJ v2 => RadioDJ v2 Support Forum => Topic started by: Marius on May 06, 2019, 11:45:32 PM

Title: MySQL 8 vs MariaDB - Important!!!
Post by: Marius on May 06, 2019, 11:45:32 PM
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]
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]
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]
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]
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.
Title: Re: MySQL 8 vs MariaDB - Important!!!
Post by: Marius on May 07, 2019, 12:42:05 PM
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.
Title: Re: MySQL 8 vs MariaDB - Important!!!
Post by: stevewa on May 07, 2019, 02:27:41 PM
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...
Title: Re: MySQL 8 vs MariaDB - Important!!!
Post by: madhatter on May 17, 2019, 04:31:04 AM
Mine is very sluggish running MySQL.  How do you switch from MySQL to MariaDB?
Title: Re: MySQL 8 vs MariaDB - Important!!!
Post by: renelatour1972 on May 19, 2019, 08:22:45 AM
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]
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]
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]
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]
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
Title: Re: MySQL 8 vs MariaDB - Important!!!
Post by: Calypso on May 19, 2019, 02:26:09 PM
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.
Title: Re: MySQL 8 vs MariaDB - Important!!!
Post by: renelatour1972 on May 20, 2019, 09:26:47 AM
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!!
Title: Re: MySQL 8 vs MariaDB - Important!!!
Post by: Jhonny on May 20, 2019, 01:02:53 PM
Dit you stop or uninstalled the first sql server?
Title: Re: MySQL 8 vs MariaDB - Important!!!
Post by: stevewa on May 20, 2019, 02:43:19 PM
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.
Title: Re: MySQL 8 vs MariaDB - Important!!!
Post by: Trey on June 01, 2019, 01:26:10 AM
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.
Title: Re: MySQL 8 vs MariaDB - Important!!!
Post by: Jhonny on June 01, 2019, 08:40:21 AM
How to uninstall MySQL from windows 7 / windows 8 / windows 8.1

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

Installing MariaDB on Windows

https://www.youtube.com/watch?v=yQPnCxJMOWI&t=73s (https://www.youtube.com/watch?v=yQPnCxJMOWI&t=73s)
 
step by step
Title: Re: MySQL 8 vs MariaDB - Important!!!
Post by: Jhonny on June 01, 2019, 03:13:22 PM
Just made a tutorial how to move from MySQL to MariaDB
Hope this is informative enough.
Tutorial. (https://radiodj.info/switch/switch.php)
Title: Re: MySQL 8 vs MariaDB - Important!!!
Post by: chrismadog on July 15, 2019, 07:00:21 AM
Stevewa,

You said
Quote
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
Title: Re: MySQL 8 vs MariaDB - Important!!!
Post by: stevewa on July 16, 2019, 01:50:05 PM
ok grumpy dog  :D
Title: Re: MySQL 8 vs MariaDB - Important!!!
Post by: KJ6EO on October 08, 2019, 04:21:01 AM
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.