2018-11-16, 21:37
Perhaps you missed the the warning on it that it should only be used by "team members only"?
2018-11-16, 23:53
For the record, I didn't "suggest you post your findings" there but to present a programmatic solution since you seemed to have a deeper knowledge of databases. Also, you've been registered here for 3 years so I assumed you knew how things are done.
2018-11-17, 10:49
@annomatik:
As long as you do not test with version 18 any issue report is useless.
There has been work done to the databases between 17 and 18, and we will not release another version of 17 any more.
Attached to that is the fact that it states quite clearly on the issue templates not to post feature requests there.
It is also probably best to start a separate thread for your issue here on the forum and ask others to help you, instead of hijacking this useful thread.
The first thing I would suggest you do is run a kodi 18 instance somewhere so you get updated databases. Be sure to follow the requirements set out in the wiki to get all views and triggers created correctly, eg. do not restrict the kodi sql account on the sql environment, but give it full access. Since the databases will be created next to your existing ones you can easily drop them later.
It is fair that at some point an issue needs to be raised, but it is definitely necessary to figure out which indexes need to be added to a v18 system, and have this verified by multiple people, before such is done.
@HeresJohnny: It's clear your intentions were good
As long as you do not test with version 18 any issue report is useless.
There has been work done to the databases between 17 and 18, and we will not release another version of 17 any more.
Attached to that is the fact that it states quite clearly on the issue templates not to post feature requests there.
It is also probably best to start a separate thread for your issue here on the forum and ask others to help you, instead of hijacking this useful thread.
The first thing I would suggest you do is run a kodi 18 instance somewhere so you get updated databases. Be sure to follow the requirements set out in the wiki to get all views and triggers created correctly, eg. do not restrict the kodi sql account on the sql environment, but give it full access. Since the databases will be created next to your existing ones you can easily drop them later.
It is fair that at some point an issue needs to be raised, but it is definitely necessary to figure out which indexes need to be added to a v18 system, and have this verified by multiple people, before such is done.
@HeresJohnny: It's clear your intentions were good
2018-11-17, 11:32
Good info thanks, but nobody is going to take any notice of a 2 year old obsolete version of Kodi.
Much better to do the tests on latest nightly, then maybe a Dev will take notice.
EDIT: yeh basically what Kib said at the same time as I posted
Much better to do the tests on latest nightly, then maybe a Dev will take notice.
EDIT: yeh basically what Kib said at the same time as I posted
2018-11-17, 18:13
The topic is "Using and tuning MariaDB". That's what I did. I'm using the current stable version. If you don't want my input, don't use it.
I'm not going to install a development-environment for getting Kodi to compile from source and I don't see a newer stable version than Kodi 17.6 for RetroPie either.
I don't see how using the latest stable version is a problem. Also, this was not a feature request, so I don't see why I should post it as a feature request. I'm not requesting anything.
It's a suggestion, I have clearly labeled it as such. Again, if you don't want it, don't use it.
I'm not going to install a development-environment for getting Kodi to compile from source and I don't see a newer stable version than Kodi 17.6 for RetroPie either.
I don't see how using the latest stable version is a problem. Also, this was not a feature request, so I don't see why I should post it as a feature request. I'm not requesting anything.
It's a suggestion, I have clearly labeled it as such. Again, if you don't want it, don't use it.
2018-11-17, 19:08
You can literally just download a nightly build, configure it and have it update your databases so you can run a test.
No need to have a development environment at all. I am quite positive your poor performance is because you have a poorly configured MariaDB running.
The tuning options in the first post might very well not be optimal for Synology (especially on old Synology).
For your reference, getting an episode_view from my synology, which is properly set up, takes 0.0258 seconds, out of which 93% of the time consists of actually sending the data. (I ran a profiler on it). This with 4846 items returned. The reason I used episode_view in this example of speed is because I have many more episodes than movies. (movie_view takes
0.0105 secs)
Obviously a view usually takes longer to complete than a simple query on a straight table because it has to do more work and is returning more data. (If you had inspected the view you would have seen it does a join on a few tables)
One of the queries you are doing is indeed missing an index (takes 0.11 seconds, of which 0,01 creating a temp index), but I wonder where you got that query from, as far as I can see you did not explain this anywhere.
The views do not seem to be missing indices, however.
No need to have a development environment at all. I am quite positive your poor performance is because you have a poorly configured MariaDB running.
The tuning options in the first post might very well not be optimal for Synology (especially on old Synology).
For your reference, getting an episode_view from my synology, which is properly set up, takes 0.0258 seconds, out of which 93% of the time consists of actually sending the data. (I ran a profiler on it). This with 4846 items returned. The reason I used episode_view in this example of speed is because I have many more episodes than movies. (movie_view takes
0.0105 secs)
Obviously a view usually takes longer to complete than a simple query on a straight table because it has to do more work and is returning more data. (If you had inspected the view you would have seen it does a join on a few tables)
One of the queries you are doing is indeed missing an index (takes 0.11 seconds, of which 0,01 creating a temp index), but I wonder where you got that query from, as far as I can see you did not explain this anywhere.
Code:
select e.c00 episode_title, t.c00 show_title, e.c12 snr, e.c13 enr FROM episode e, tvshow t, files f WHERE e.idFile=f.idFile and e.idShow=t.idShow ORDER BY dateAdded DESC LIMIT 15The views do not seem to be missing indices, however.
2018-11-18, 12:28
Ok, I can try that. My Maria DB is pretty much out-of-the-box Synology "Maria 10 DB", except some tweaks from the initial post.
Background: I was working on integrating queries to the Kodi database into a web-site, an overview of the latest tv shows and latest movies added to the database. The query you are pointing at is from exactly that php page, so it's one made by me.
Is it possible to install the nightly in parallel to my existing Kodi installation, without breaking my main installation? Like a portable version?
Background: I was working on integrating queries to the Kodi database into a web-site, an overview of the latest tv shows and latest movies added to the database. The query you are pointing at is from exactly that php page, so it's one made by me.
Is it possible to install the nightly in parallel to my existing Kodi installation, without breaking my main installation? Like a portable version?
2018-11-18, 13:42
Installing multiple Kodi instances on a Windows machine is possible when you are adding the portable option to your Kodi shortcut parameters. ' -p', I think...
2018-11-19, 10:15
(2018-11-18, 12:28)annomatik Wrote: Is it possible to install the nightly in parallel to my existing Kodi installation, without breaking my main installation? Like a portable version?Yes, see https://kodi.wiki/view/Windows_FAQ#Portable_mode
2018-11-19, 17:46
Sure, let's split this topic, it's getting a bit too... detailed.
I'll try the nightly in a week or two; currently adding capacity to the NAS and that costs performance.
I'll try the nightly in a week or two; currently adding capacity to the NAS and that costs performance.
2019-01-20, 19:00
In MariaDB addon I made for LibreELEC I was using suggested optimizer_search_depth=1. But this really kills performance.
Single select * from episode_view took
8230 rows in set (7.646 sec)
but without this argument it took
8230 rows in set (0.125 sec)
Pretty much difference!
Any idea what is going on? I have no idea about MySQL - the highlight of my knowledge is basic SELECT
Single select * from episode_view took
8230 rows in set (7.646 sec)
but without this argument it took
8230 rows in set (0.125 sec)
Pretty much difference!
Any idea what is going on? I have no idea about MySQL - the highlight of my knowledge is basic SELECT
2019-01-21, 00:42
I've amended my recommendation, try again with a value of "0", please.
2019-02-03, 19:00
(2018-02-28, 21:01)HeresJohnny Wrote: UPDATE 2019-01-20
Changing the recommended value for optimizer_search_depth to "0". That means, the actual value is automatically determined by MariaDB. This will probalby lead to a value around 7 for Kodi, which is safer than 1. 1 is the fastest option but may lead to premature ending of queries because of lack of search depth, thus having to run the query multiple times.
Unbelievable difference in search results, lists load instantly now!
Thanks @HeresJohnny
2019-03-11, 15:23
11.03.2019
- Amended some values in the first post
- Added value for Aria pagecache
- Added recommendations for different system RAM
- Amended some values in the first post
- Added value for Aria pagecache
- Added recommendations for different system RAM
