[ventolin]

just fyi - Just tested using mysql-server-5.7 installed directly on ubuntu (no dockerization), same issue persists

[ventolin]

Hi DaveBlake.

After more investigating and some ideas from a friend, I've managed to find the cause and come up with a reproducible. 

Enabling the mysql full log and then selecting an album in Kodi, I see the following in the log:

Code:

mysqld, Version: 5.7.20 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
2018-01-03T19:21:35.796959Z        20 Connect   [email protected] on  using SSL/TLS
2018-01-03T19:21:35.797607Z        20 Query     SET NAMES utf8
2018-01-03T19:21:35.798267Z        20 Query     SET SESSION sql_mode = (SELECT REPLACE(@@SESSION.sql_mode,'ONLY_FULL_GROUP_BY',''))
2018-01-03T19:21:35.798901Z        20 Query     SELECT @@SESSION.optimizer_switch
2018-01-03T19:21:35.799614Z        20 Query     SET SESSION optimizer_switch = 'derived_merge=off'
2018-01-03T19:21:35.800626Z        20 Query     show databases like 'MyMusic60'
2018-01-03T19:21:35.802001Z        20 Init DB   MyMusic60
2018-01-03T19:21:35.802847Z        20 Query     show databases like 'MyMusic60'
2018-01-03T19:21:35.803500Z        20 Query     show tables
2018-01-03T19:21:35.830449Z        21 Connect   [email protected] on  using SSL/TLS
2018-01-03T19:21:35.831148Z        21 Query     SET NAMES utf8
2018-01-03T19:21:35.831816Z        21 Query     SET SESSION sql_mode = (SELECT REPLACE(@@SESSION.sql_mode,'ONLY_FULL_GROUP_BY',''))
2018-01-03T19:21:35.832471Z        21 Query     SELECT @@SESSION.optimizer_switch
2018-01-03T19:21:35.833139Z        21 Query     SET SESSION optimizer_switch = 'derived_merge=off'
2018-01-03T19:21:35.834396Z        21 Query     show databases like 'MyMusic60'
2018-01-03T19:21:35.835821Z        21 Init DB   MyMusic60
2018-01-03T19:21:35.836721Z        21 Query     show databases like 'MyMusic60'
2018-01-03T19:21:35.837417Z        21 Query     show tables
2018-01-03T19:21:35.838299Z        21 Query     SELECT COUNT(1) FROM songview  WHERE songview.idAlbum = 33661
2018-01-03T19:21:37.124151Z        21 Query     SELECT songview.*, songartistview.* FROM songview JOIN songartistview ON songartistview.idsong = songview.idsong  WHERE songview.idAlbum = 33661 ORDER BY songartistview.idsong, songartistview.idRole, songartistview.iOrder
2018-01-03T19:21:40.479931Z        21 Quit

The line "SET SESSION optimizer_switch = 'derived_merge=off'" looked interesting. Setting it, and then entering the same sql query from the command line results in the songview,songartistview query taking almost 4 seconds:

Code:

mysql> use MyMusic60;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SET SESSION optimizer_switch = 'derived_merge=off';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT sql_no_cache songview.*, songartistview.* FROM songview JOIN songartistview ON songartistview.idsong = songview.idsong  WHERE songview.idAlbum = 33091 ORDER BY songartistview.idsong, songart
istview.idRole, songartistview.iOrder;

[.... results removed for brevity ....]

4 rows in set, 1 warning (3.37 sec)

mysql> quit;

Looking through the code and the github issues, it seems that this was the rationale behind it: https://github.com/xbmc/xbmc/pull/8393#i...-159857118 This is done to have the really weird DELETE queries (example: https://forum.kodi.tv/showthread.php?tid...pid2170957 ) not break. However, it's being applied to *every* session - not just sessions opened in order to perform these crazy delete queries. 

Suggested fix: only set this in sessions when these weird deletes with subselects are performed. 

What're your thoughts on this? If there are people responsible for the kodi-wide DB handling (not just music db), could you ping them with this message? Cheers!

[DaveBlake]

Firstly well done @ventolin for persisting with this and coming up with something we can check out.

If there are people responsible for the kodi-wide DB handling (not just music db), could you ping them with this message?

There are no such "responsible" people, but there are a few team members that use client/server setups themselves and that is a near as we get to it. I will certianly raise it with them. Historically MySQL and its variants have thrown up a variety of issues over the years. These are often fixed by Millhouse, one of the active MySQL users on the team, but I doubt if he would want to be considered responsible.

What I am unclear about is why I have not seen the general slowness of queries during testing, but I will have a look at the impact of 'derived_merge=off' or on. Maybe I have been missing something.

Looking through the code and the github issues, it seems that this was the rationale behind it: https://github.com/xbmc/xbmc/pull/8393#i...-159857118 This is done to have the really weird DELETE queries (example: https://forum.kodi.tv/showthread.php?tid...pid2170957 ) not break. However, it's being applied to *every* session - not just sessions opened in order to perform these crazy delete queries. 

Suggested fix: only set this in sessions when these weird deletes with subselects are performed. 

These queries are not so weird, but MySQL has some huge defficiencies in the way it handle subsqueries, seems this is just more of that. Your suggestion seems sound to me, but I want to do some more investigation.

[HeresJohnny]

I would warmly recommend a switch to MariaDB. MySQL seems to handle query queuing and lock detection a lot worse.

[xad001x0w]

Hi all,

I made an account just to say I am having the same problem. I have music files on a NAS and a separate web/database server then hosts the MySQL database (running on Linux). I have multiple Kodi clients on 3 raspberry pis (running OSMC), 2 windows PCs and a Mac. I've been using the MySQL based Kodi library since about 2013 and it had been working great for the past couple of years until recently. I have the same very delayed music library, no gapless playback and it takes a few seconds to "load" an Album when you select one to play. TV and movie playback is fine.

I will post a debug log either later tonight or tomorrow when I have a chance to pull one. Happy to share other details if people wouldlike to know them.

[ventolin]

Hi xad001x0w ,

the cause of this problem is described in my post in this thread from 2018-01-03. 

My workaround was to downgrade MySQL to 5.6. Then everything works as expected, but unfortunately you're stuck on this old version of mysql.

[xad001x0w]

Hi,

ventolin, thanks for listing your workaround, serves me right for just skim reading the existing thread!

I had been holding out upgrading my MySQL server to 16.04 Ubuntu but gave in and upgraded right around the time when I noticed the performance drop (but figured it was coincidental as I upgraded my Kodi installations at the same time). For the benefit of anyone else in the same situation as me, 16.04 changes the way services are run and therefore forces you to upgrade MySQL 5.6 which uses the old method, to 5.7 which uses the new one. I have now been looking into trying to get 5.6 working on 16.04 but apparently that's not particularly straightforward, not to mention out of scope for this discussion.

Kind of annoying and I'm wondering whether or not to put the time and effort into hacking about my MySQL install, or just wait for Kodi 18 to drop which might potentially fix the issue from the other side anyway?

Thanks all

[ventolin]

I have now been looking into trying to get 5.6 working on 16.04 but apparently that's not particularly straightforward, not to mention out of scope for this discussion.

Kind of annoying and I'm wondering whether or not to put the time and effort into hacking about my MySQL install, or just wait for Kodi 18 to drop which might potentially fix the issue from the other side anyway?

or just run a mysql:5.6 docker container!

[marantz]

just tested this on mariadb 10.2.13 and SET SESSION optimizer_switch = 'derived_merge=off'; had no effect on performance

[ventolin]

just tested this on mariadb 10.2.13 and SET SESSION optimizer_switch = 'derived_merge=off'; had no effect on performance

 Good to know. I had bad (and similar) experiences when I switched to MariaDB and was met with very hostile answers along the lines of "mysql db backend is barely supported, mariadb definitely not" in #xbmc on freenode when I asked about it. 

I might give it another go this weekend just to see if things have improved with mariadb 10.1. Thanks!

[docwra]

just tested this on mariadb 10.2.13 and SET SESSION optimizer_switch = 'derived_merge=off'; had no effect on performance

 Good to know. I had bad (and similar) experiences when I switched to MariaDB and was met with very hostile answers along the lines of "mysql db backend is barely supported, mariadb definitely not" in #xbmc on freenode when I asked about it. 

I might give it another go this weekend just to see if things have improved with mariadb 10.1. Thanks! 

 Yes this is a running theme within the team as MYSQL support was added, when really the team wanted a full client to client approach in the long term. Nobody wrote that yet so, we are kind of stuck with the old style central server approach which nobody really supports.