[dkangel]

Hello

I have a shared mariadb database
I just upgrade from 20.3 to 21 beta 3 my three kodi devices ( 2 shield and a pc) and everything is ok expect the access to the database, it's very slow since the upgrade.
Any ideas ?

Thank you for your help

[Hitcher]

To receive meaningful assistance you will need to provide a full debug log.

The instructions are here... debug log (wiki)

If you are using the Basic Method, then ensure the following is applied...
1.Enable debugging in Settings>System Settings>Logging,
2.Restart Kodi
3.Replicate the problem.
4.Upload the log to Kodi Paste Site manually or use the Kodi Logfile Uploader. (wiki) With either method post the link to the log back here.

If you are using the Advanced Method ensure you have correctly created and applied the advancedsettings.xml file (wiki)

In both instances, you should see the word DEBUG throughout the log.

Note: Full logs only. No partial or redacted logs
Do NOT post your logs directly into the forum. Use the Kodi Paste Site. Post the link to your pasted log in the forum

[dkangel]

tekibenizu.kodi (paste)

I hope it's that you want

[CrystalP]

Yes that's the requested log.

Can you be more specific about what action is slow? How many movies in the library?

[dkangel]

it's just when i go to the movies section of kodi and it's downloading movie list from the database, it seems to be blocked for a minute and after all the list is downloaded.
before the update it toke 2 or 3 seconds now it's freezing kodi during a minute and after that it's working
i have 4500 movies and a mariadb 10.10 database

PS : is it normal to have already myvideos131 database number ?

[izprtxqkft]

if you're trying to speed up kodi maybe disable ws-discovery for a 1 minute speed up
look in the log, it takes 1 minute to finish just that

doesn't fix kodi but a minute gain would be good

[dkangel]

Already tried that but change nothing unfortunately...

[dkangel]

so today i had a power shutdown in my city and all my stuff reboot
now it's working like before
so sorry for your waste of time, next time i will try reboot everything before anything else

[PatK]

Thread marked solved.

[sky.joshua]

Hi

Have the same problem, however restarting mariadb or the underlying server did not resolve it.

Log: https://paste.kodi.tv/ojusarewet

MariaDB is running in a ProxMox container. For 20.5 everything is running fast, but in 21.0 it takes a long time to open Movies. Observation: in 20.5 cpu on the mariadb server is low (10%) during opening Movies and network traffic is high. In 21.0, CPU on the mariadb server is maxing out to 100% (!) and network traffic is low. So definitively a very different behaviour here. 

Going back to 20.5 restores the normal behaviour.

Thanks for looking into it again.

[CrystalP]

Tried something similar with a mariadb 10.11.7 docker and there is no such performance issue.
2000 movies are returned in 100ms. Your 3800 should not take minutes.

Please run the queries directly in a mariadb frontend running on the same computer as the Kodi client to confirm that they take a couple minutes outside of Kodi as well (adjust the paging options so that all rows are retrieved immediately)
You can also run them separately with a frontend running on the same computer as the database. The times can then be compared to measure the impact of the network transfer.

select * from movie_view WHERE (isDefaultVersion = 1);

select * from movie_view WHERE (isDefaultVersion = 1) AND (((movie_view.playCount IS NULL OR movie_view.playCount = 0)));

If network is not the issue (it most likely isn't), then run those to see the execution plans of the db engine and paste them here.

explain select * from movie_view WHERE (isDefaultVersion = 1);

explain select * from movie_view WHERE (isDefaultVersion = 1) AND (((movie_view.playCount IS NULL OR movie_view.playCount = 0)));

[sky.joshua]

Thanks for trying. The times in the shell were essentially identical from the one in the log. In the end I dropped the DB and had Kodi 21 rebuild it from the .NFO files which brought the times (again) into the 0,5 sec range.  Here the output of the last two commands in the migrated and the rebuilt DB

Migrated from MyVideos131:

MariaDB [MyVideos131]> explain select * from movie_view WHERE (isDefaultVersion = 1);
+------+--------------------+----------+--------+-------------------------+-------------+---------+-----------------------------+------+-------------+
| id   | select_type        | table    | type   | possible_keys           | key         | key_len | ref                         | rows | Extra       |
+------+--------------------+----------+--------+-------------------------+-------------+---------+-----------------------------+------+-------------+
|    1 | PRIMARY            | vv       | ALL    | PRIMARY,ix_videoversion | NULL        | NULL    | NULL                        | 1    | Using where |
|    1 | PRIMARY            | movie    | eq_ref | PRIMARY,ix_movie_file_2 | PRIMARY     | 4       | MyVideos131.vv.idMedia      | 1    | Using where |
|    1 | PRIMARY            | sets     | eq_ref | PRIMARY                 | PRIMARY     | 4       | MyVideos131.movie.idSet     | 1    | Using where |
|    1 | PRIMARY            | vvt      | eq_ref | PRIMARY                 | PRIMARY     | 4       | MyVideos131.vv.idType       | 1    | Using where |
|    1 | PRIMARY            | rating   | eq_ref | PRIMARY                 | PRIMARY     | 4       | MyVideos131.movie.c05       | 1    | Using where |
|    1 | PRIMARY            | files    | eq_ref | PRIMARY,ix_files        | PRIMARY     | 4       | MyVideos131.vv.idFile       | 1    | Using where |
|    1 | PRIMARY            | path     | eq_ref | PRIMARY                 | PRIMARY     | 4       | MyVideos131.files.idPath    | 1    |             |
|    1 | PRIMARY            | uniqueid | eq_ref | PRIMARY                 | PRIMARY     | 4       | MyVideos131.movie.c09       | 1    | Using where |
|    1 | PRIMARY            | bookmark | ref    | ix_bookmark             | ix_bookmark | 10      | MyVideos131.vv.idFile,const | 1    |             |
|    4 | DEPENDENT SUBQUERY | vv       | ALL    | ix_videoversion         | NULL        | NULL    | NULL                        | 1    | Using where |
|    3 | DEPENDENT SUBQUERY | vv       | ALL    | ix_videoversion         | NULL        | NULL    | NULL                        | 1    | Using where |
+------+--------------------+----------+--------+-------------------------+-------------+---------+-----------------------------+------+-------------+
11 rows in set (0,002 sec)

MariaDB [MyVideos131]> 
MariaDB [MyVideos131]> explain select * from movie_view WHERE (isDefaultVersion = 1) AND (((movie_view.playCount IS NULL OR movie_view.playCount = 0)));
+------+--------------------+----------+--------+-------------------------+-------------+---------+-----------------------------+------+-------------+
| id   | select_type        | table    | type   | possible_keys           | key         | key_len | ref                         | rows | Extra       |
+------+--------------------+----------+--------+-------------------------+-------------+---------+-----------------------------+------+-------------+
|    1 | PRIMARY            | vv       | ALL    | PRIMARY,ix_videoversion | NULL        | NULL    | NULL                        | 1    | Using where |
|    1 | PRIMARY            | movie    | eq_ref | PRIMARY,ix_movie_file_2 | PRIMARY     | 4       | MyVideos131.vv.idMedia      | 1    | Using where |
|    1 | PRIMARY            | sets     | eq_ref | PRIMARY                 | PRIMARY     | 4       | MyVideos131.movie.idSet     | 1    | Using where |
|    1 | PRIMARY            | vvt      | eq_ref | PRIMARY                 | PRIMARY     | 4       | MyVideos131.vv.idType       | 1    | Using where |
|    1 | PRIMARY            | rating   | eq_ref | PRIMARY                 | PRIMARY     | 4       | MyVideos131.movie.c05       | 1    | Using where |
|    1 | PRIMARY            | files    | eq_ref | PRIMARY,ix_files        | PRIMARY     | 4       | MyVideos131.vv.idFile       | 1    | Using where |
|    1 | PRIMARY            | path     | eq_ref | PRIMARY                 | PRIMARY     | 4       | MyVideos131.files.idPath    | 1    |             |
|    1 | PRIMARY            | uniqueid | eq_ref | PRIMARY                 | PRIMARY     | 4       | MyVideos131.movie.c09       | 1    | Using where |
|    1 | PRIMARY            | bookmark | ref    | ix_bookmark             | ix_bookmark | 10      | MyVideos131.vv.idFile,const | 1    |             |
|    4 | DEPENDENT SUBQUERY | vv       | ALL    | ix_videoversion         | NULL        | NULL    | NULL                        | 1    | Using where |
|    3 | DEPENDENT SUBQUERY | vv       | ALL    | ix_videoversion         | NULL        | NULL    | NULL                        | 1    | Using where |
+------+--------------------+----------+--------+-------------------------+-------------+---------+-----------------------------+------+-------------+
11 rows in set (0,003 sec)


Rebuilt from NFO:

MariaDB [MyVideos131]> explain select * from movie_view WHERE (isDefaultVersion = 1);
+------+--------------------+----------+--------+-------------------------+-----------------+---------+---------------------------------+------+------------------------------------+
| id   | select_type        | table    | type   | possible_keys           | key             | key_len | ref                             | rows | Extra                              |
+------+--------------------+----------+--------+-------------------------+-----------------+---------+---------------------------------+------+------------------------------------+
|    1 | PRIMARY            | movie    | ALL    | PRIMARY,ix_movie_file_2 | NULL            | NULL    | NULL                            | 3592 |                                    |
|    1 | PRIMARY            | sets     | eq_ref | PRIMARY                 | PRIMARY         | 4       | MyVideos131.movie.idSet         | 1    | Using where                        |
|    1 | PRIMARY            | rating   | eq_ref | PRIMARY                 | PRIMARY         | 4       | MyVideos131.movie.c05           | 1    | Using where                        |
|    1 | PRIMARY            | uniqueid | eq_ref | PRIMARY                 | PRIMARY         | 4       | MyVideos131.movie.c09           | 1    | Using where                        |
|    1 | PRIMARY            | vv       | ref    | PRIMARY,ix_videoversion | ix_videoversion | 68      | MyVideos131.movie.idMovie,const | 1    | Using where                        |
|    1 | PRIMARY            | vvt      | eq_ref | PRIMARY                 | PRIMARY         | 4       | MyVideos131.vv.idType           | 1    | Using where                        |
|    1 | PRIMARY            | files    | eq_ref | PRIMARY,ix_files        | PRIMARY         | 4       | MyVideos131.vv.idFile           | 1    | Using where                        |
|    1 | PRIMARY            | path     | eq_ref | PRIMARY                 | PRIMARY         | 4       | MyVideos131.files.idPath        | 1    |                                    |
|    1 | PRIMARY            | bookmark | ref    | ix_bookmark             | ix_bookmark     | 10      | MyVideos131.vv.idFile,const     | 1    |                                    |
|    4 | DEPENDENT SUBQUERY | vv       | ref    | ix_videoversion         | ix_videoversion | 68      | MyVideos131.movie.idMovie,const | 1    | Using where                        |
|    3 | DEPENDENT SUBQUERY | vv       | ref    | ix_videoversion         | ix_videoversion | 68      | MyVideos131.movie.idMovie,const | 1    | Using index condition; Using where |
+------+--------------------+----------+--------+-------------------------+-----------------+---------+---------------------------------+------+------------------------------------+
11 rows in set (0,005 sec)

MariaDB [MyVideos131]> 
MariaDB [MyVideos131]> explain select * from movie_view WHERE (isDefaultVersion = 1) AND (((movie_view.playCount IS NULL OR movie_view.playCount = 0)));
+------+--------------------+----------+--------+-------------------------+-----------------+---------+---------------------------------+------+------------------------------------+
| id   | select_type        | table    | type   | possible_keys           | key             | key_len | ref                             | rows | Extra                              |
+------+--------------------+----------+--------+-------------------------+-----------------+---------+---------------------------------+------+------------------------------------+
|    1 | PRIMARY            | movie    | ALL    | PRIMARY,ix_movie_file_2 | NULL            | NULL    | NULL                            | 3592 |                                    |
|    1 | PRIMARY            | sets     | eq_ref | PRIMARY                 | PRIMARY         | 4       | MyVideos131.movie.idSet         | 1    | Using where                        |
|    1 | PRIMARY            | rating   | eq_ref | PRIMARY                 | PRIMARY         | 4       | MyVideos131.movie.c05           | 1    | Using where                        |
|    1 | PRIMARY            | uniqueid | eq_ref | PRIMARY                 | PRIMARY         | 4       | MyVideos131.movie.c09           | 1    | Using where                        |
|    1 | PRIMARY            | vv       | ref    | PRIMARY,ix_videoversion | ix_videoversion | 68      | MyVideos131.movie.idMovie,const | 1    | Using where                        |
|    1 | PRIMARY            | vvt      | eq_ref | PRIMARY                 | PRIMARY         | 4       | MyVideos131.vv.idType           | 1    | Using where                        |
|    1 | PRIMARY            | files    | eq_ref | PRIMARY,ix_files        | PRIMARY         | 4       | MyVideos131.vv.idFile           | 1    | Using where                        |
|    1 | PRIMARY            | path     | eq_ref | PRIMARY                 | PRIMARY         | 4       | MyVideos131.files.idPath        | 1    |                                    |
|    1 | PRIMARY            | bookmark | ref    | ix_bookmark             | ix_bookmark     | 10      | MyVideos131.vv.idFile,const     | 1    |                                    |
|    4 | DEPENDENT SUBQUERY | vv       | ref    | ix_videoversion         | ix_videoversion | 68      | MyVideos131.movie.idMovie,const | 1    | Using where                        |
|    3 | DEPENDENT SUBQUERY | vv       | ref    | ix_videoversion         | ix_videoversion | 68      | MyVideos131.movie.idMovie,const | 1    | Using index condition; Using where |
+------+--------------------+----------+--------+-------------------------+-----------------+---------+---------------------------------+------+------------------------------------+

[CrystalP]

OK that's good news. Solved!

There doesn't seem to be an index missing outright, but the query optimizer chose to not use an important one. Strange, but I'm not a mariadb expert and don't know the factors that go in that decision.

[sky.joshua]

Thanks for looking into it. Yes, problem solved for me and for now, I am wondering though what is going on in the data base migration, since the error can be 100% reproduced: Create the data base in 20.5 and migrate -> slow. Drop data base and create new from NFO in 21 -> fast. I also tried it with a brand new vanilla installation of MariaDB in a separate container -> same thing. If my installation is the odd case, the so be it, but definitively something odd here in the migration path. From the item count it seems the view is not really populated (however this somehow contradicts how I expect views to work).

[CrystalP]

Can you provide a debug log that covers the db migration?