Author Topic: MySQL 8 vs MariaDB - Important!!!  (Read 3852 times)

Marius

  • Administrator
  • Hero Member

  • Offline
  • *****
  • 5496
    • Radio DJ Romania
MySQL 8 vs MariaDB - Important!!!
« 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.
DOWNLOADS PAGE

HOW TO FIX RADIODJ DATABASE
----------------
Please don't PM me for support requests. Use the forums instead.

Marius

  • Administrator
  • Hero Member

  • Offline
  • *****
  • 5496
    • Radio DJ Romania
Re: MySQL 8 vs MariaDB - Important!!!
« Reply #1 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.
DOWNLOADS PAGE

HOW TO FIX RADIODJ DATABASE
----------------
Please don't PM me for support requests. Use the forums instead.

stevewa

  • Hero Member

  • Offline
  • *****
  • 552
Re: MySQL 8 vs MariaDB - Important!!!
« Reply #2 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...

madhatter

  • New User

  • Offline
  • *
  • 4
Re: MySQL 8 vs MariaDB - Important!!!
« Reply #3 on: May 17, 2019, 04:31:04 AM »
Mine is very sluggish running MySQL.  How do you switch from MySQL to MariaDB?

renelatour1972

  • New User

  • Offline
  • *
  • 38
  • Personal Text
    www.rmasalerno.it
    • www.rmasalerno.it
Re: MySQL 8 vs MariaDB - Important!!!
« Reply #4 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
www.rmasalerno.it
rma altri suoni!

Calypso

  • Sr. Member

  • Offline
  • ****
  • 421
Re: MySQL 8 vs MariaDB - Important!!!
« Reply #5 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.

renelatour1972

  • New User

  • Offline
  • *
  • 38
  • Personal Text
    www.rmasalerno.it
    • www.rmasalerno.it
Re: MySQL 8 vs MariaDB - Important!!!
« Reply #6 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!!
www.rmasalerno.it
rma altri suoni!

Jhonny

  • Hero Member

  • Online
  • *****
  • 3596
  • Personal Text
    Peace, enjoy life.it's over before you've lived it
    • Jhonny's RadioDJ tutorials.
Re: MySQL 8 vs MariaDB - Important!!!
« Reply #7 on: May 20, 2019, 01:02:53 PM »
Dit you stop or uninstalled the first sql server?
R.T.F.M. means to me, Read The Fantastic Messages.
Make things simple, but don't make simple things simpler!
I don't do drugs, I do tuts. ( ͡° ͜ʖ ͡°) visit https://radiodj.info

Testing RadioDJ v2.0.1.6 Beta

stevewa

  • Hero Member

  • Offline
  • *****
  • 552
Re: MySQL 8 vs MariaDB - Important!!!
« Reply #8 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.

Trey

  • New User

  • Offline
  • *
  • 5
Re: MySQL 8 vs MariaDB - Important!!!
« Reply #9 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.

Jhonny

  • Hero Member

  • Online
  • *****
  • 3596
  • Personal Text
    Peace, enjoy life.it's over before you've lived it
    • Jhonny's RadioDJ tutorials.
Re: MySQL 8 vs MariaDB - Important!!!
« Reply #10 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

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.
R.T.F.M. means to me, Read The Fantastic Messages.
Make things simple, but don't make simple things simpler!
I don't do drugs, I do tuts. ( ͡° ͜ʖ ͡°) visit https://radiodj.info

Testing RadioDJ v2.0.1.6 Beta

Jhonny

  • Hero Member

  • Online
  • *****
  • 3596
  • Personal Text
    Peace, enjoy life.it's over before you've lived it
    • Jhonny's RadioDJ tutorials.
Re: MySQL 8 vs MariaDB - Important!!!
« Reply #11 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.
R.T.F.M. means to me, Read The Fantastic Messages.
Make things simple, but don't make simple things simpler!
I don't do drugs, I do tuts. ( ͡° ͜ʖ ͡°) visit https://radiodj.info

Testing RadioDJ v2.0.1.6 Beta

chrismadog

  • Sr. Member

  • Offline
  • ****
  • 275
  • Personal Text
    Be part of the solution, not part of the problem !
    • Rose City FM
Re: MySQL 8 vs MariaDB - Important!!!
« Reply #12 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
Presenter - 4SDB - Rose City FM 89.3 - Warwick, QLD Australia.
Community Radio - Bringing you the best of local radio.
http://rosecityfm.org.au

stevewa

  • Hero Member

  • Offline
  • *****
  • 552
Re: MySQL 8 vs MariaDB - Important!!!
« Reply #13 on: July 16, 2019, 01:50:05 PM »
ok grumpy dog  :D

KJ6EO

  • Full Member

  • Offline
  • ***
  • 102
  • Personal Text
    Omnia Pro Radio
    • 101.5 FM KZNQ Santa Clarita, CA
Re: MySQL 8 vs MariaDB - Important!!!
« Reply #14 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.