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

Marius

  • Administrator
  • Hero Member

  • Offline
  • *****
  • 5416
    • 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
  • *****
  • 5416
    • 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

  • Sr. Member

  • Offline
  • ****
  • 383
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
  • *
  • 2
  • 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
  • ****
  • 295
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
  • *
  • 2
  • Personal Text
    www.rmasalerno.it
    • www.rmasalerno.it
Re: MySQL 8 vs MariaDB - Important!!!
« Reply #6 on: Today at 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

  • Offline
  • *****
  • 3405
  • 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: Today at 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 v.2.0.1.5 beta

stevewa

  • Sr. Member

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