•   
  • 1
  • 29
  • 30
  • 31
  • 32
  • 33(current)
HOW-TO:Share libraries using MySQL: Wiki Edition
(2018-01-07, 19:35)ventolin Wrote: Am I right in understanding that Kodi should play nicely with MariaDB and that using it might be a possible workaround? I'd had problems with it before: https://forum.kodi.tv/showthread.php?tid=318705

Kodi should in theory play nicely with MariaDB, but there's no absolute guarantees as for the time being the only two database backends that are "certified" are SQLite and MySQL. Kodi may work with MariaDB, but only so long as MariaDB remains compatible with MySQL.

I'm aware of your thread, but so far the behaviour you have observed isn't reproducible. We have done internal testing against MySQL and MariaDB and with/without derived_merge=off both backends return results in a similar time 0.001s (ie. basically immeasurable).

By all means try MariaDB instead. Kodi may gain official MariaDB support if PR13318 is merged.

I would also suggest testing with LibreELEC from a USB thumb drive to test your backend server, just in case there's a problem with your docker container.
Texture Cache Maintenance Utility: Preload your texture cache for optimal UI performance. Remotely manage media libraries. Purge unused artwork to free up space. Find missing media. Configurable QA check to highlight metadata issues. Aid in diagnosis of library and cache related problems.
Reply
I've downgraded to mysql 5.6 and everything is behaving itself again, so I'm happy for the time being. 

In your tests, what size does the MyMusic db have? I would suggest filling it with an awful lot of data in order to simulate this behaviour. For example:

Code:

mysql> select count(*) from song;
+----------+
| count(*) |
+----------+
|   103294 |
+----------+
1 row in set (0.02 sec)
Reply
(2018-01-07, 20:04)ventolin Wrote: I've downgraded to mysql 5.6 and everything is behaving itself again, so I'm happy for the time being. 

In your tests, what size does the MyMusic db have? I would suggest filling it with an awful lot of data in order to simulate this behaviour. For example:

I don't recall but probably not that many, I guess the important thing is that there was no obvious difference in performance.

It's entirely possible there is a regression/bug in the MySQL 5.7.19 server you were testing - I think we did internal testing with MySQL 5.7.20/MariaDB 10.1.26. This is really the biggest problem with server-based relational databases - every version can behave a little differently, whether intentionally or not.
Texture Cache Maintenance Utility: Preload your texture cache for optimal UI performance. Remotely manage media libraries. Purge unused artwork to free up space. Find missing media. Configurable QA check to highlight metadata issues. Aid in diagnosis of library and cache related problems.
Reply
(2018-01-07, 20:21)Milhouse Wrote:
(2018-01-07, 20:04)ventolin Wrote: I've downgraded to mysql 5.6 and everything is behaving itself again, so I'm happy for the time being. 

In your tests, what size does the MyMusic db have? I would suggest filling it with an awful lot of data in order to simulate this behaviour. For example:

I don't recall but probably not that many, I guess the important thing is that there was no obvious difference in performance.

It's entirely possible there is a regression/bug in the MySQL 5.7.19 server you were testing - I think we did internal testing with MySQL 5.7.20/MariaDB 10.1.26. This is really the biggest problem with server-based relational databases - every version can behave a little differently, whether intentionally or not. 
 It has nothing to do with the minor version. I wasted much time testing it on several 5.7 versions, inside and outside of docker. 

Since what Kodi is doing is essentially turning off database optimizations, then the important thing is to test with a realistic set of fixtures in the db. Put more data in the db and you *will* see a very obvious difference in performance.
Reply
(2018-01-07, 20:24)ventolin Wrote: It has nothing to do with the minor version. I wasted much time testing it on several 5.7 versions, inside and outside of docker. 

OK, if not the minor version, then you yourself admit 5.6.x is fine while 5.7.x is not. Is this a Kodi issue or a MySQL issue? I honestly don't know.

Without further evidence it's difficult to know what to do - assuming it's a MySQL bug, we can't code around every MySQL server bug, the best option in such a situation is to just avoid the specific MySQL Server version - we've had this situation with MariaDB too, when they released a buggy server version and the issue was resolved in the next minor update of MariaDB Server.

To the best of my knowledge MySQL performance hasn't been an issue since we introduced the derived_merge=off change over 2 years ago, so I'm really not sure why it would be a problem now unless something has changed in MySQL 5.7.x, or it's the type of connector you are using (testing with LibireELEC would have ruled out such variables).

(2018-01-07, 20:24)ventolin Wrote: Since what Kodi is doing is essentially turning off database optimizations

Not so much turning off an optimisation, rather we are instructing MySQL to support the types of queries it has historically supported (prior to 5.7.6, I believe) which means we don't have to refactor the entire MySQL support (which would probably have meant a swifter end to MySQL support if I'm honest).

(2018-01-07, 20:24)ventolin Wrote: then the important thing is to test with a realistic set of fixtures in the db. Put more data in the db and you *will* see a very obvious difference in performance.

What's realistic for you isn't necessarily realistic for me, particularly without knowing the spec of the server hosting the database. But sure - do you want to upload a backup of your Music database, maybe someone will be willing to load/test it, or we can use it in future. Personally my Music db is quite small, 7800 songs/351 albums, and my backend MySQL Server is only 5.5.21 running on a low-powered NAS (HP N36L with 8GB RAM), so in some sense it's all relative - a small db on low powered server, large db on higher powered server, what is realistic here, at what point does it become unreasonable to host a large database on a low powered server?
Texture Cache Maintenance Utility: Preload your texture cache for optimal UI performance. Remotely manage media libraries. Purge unused artwork to free up space. Find missing media. Configurable QA check to highlight metadata issues. Aid in diagnosis of library and cache related problems.
Reply
  •   
  • 1
  • 29
  • 30
  • 31
  • 32
  • 33(current)
 
Thread Rating:
  • 2 Vote(s) - 5 Average



Logout Mark Read Team Forum Stats Members Help
HOW-TO:Share libraries using MySQL: Wiki Edition52