Using and tuning MariaDB as your central database
#1
When using MariaDB, the default values work very well. However, there is a single value that will speed up your queries by a factor between 5 and 10! I'm talking about optimizer_search_depth which defaults to 62. A value of 1 is much better, and you can also try values 0 or 5. The other recommendation is to run your database off a SSD. CPU power is not that important. Anyway, here are my optimizations for my.ini in a concise form for systems with 4GB RAM:
Quote:innodb_buffer_pool_size=2G;MariaDB recommends using 80% of your available memory but the default 1G might be enough. Always set to multiples of 1G.
innodb_buffer_pool_instances=2;When innodb_buffer_pool_size is > 1G, MariaDB will split the pool in instances. They should be even dividers of 1G.
innodb_file_format=Barracuda;This is an optimized storage engine not available in MySQL
innodb_log_block_size=4096;Default is 512, when using an SSD or 4k drives, block size should be 4096
innodb_adaptive_hash_index=OFF;For Kodi no benefit is gained from additional indexes. This should only be used with large databases.
character_set_server=utf8;I really hope you all used this in MySQL already.  Angel This avoids potential pitfalls with text in exotic codepages.
key_buffer_size=16k;When not using MyISAM as storage engine, this buffer can be reduced to a minimum
skip_name_resolve=1;Avoids name resolving in the local network, thus reducing overhead. That also means that all connections are done by IP only.
optimizer_search_depth=0;Here we are, this is winner of the year.
aria_pagecache_buffer_size=512M;MariaDB uses Aria, not MyISAM for temporary files. Whatever doesn't fit into memory will be written to disk temporarily. Set this to a reasonable amount to avoid disk flushing.
innodb_buffer_pool_dump_at_shutdown=ON
innodb_buffer_pool_load_at_startup=ON
;use from MariaDB 10.0 upwards to preload buffer on startup
innodb_doublewrite=0; small speed-up at the expense of safety. Don't use in production system!

For systems with 2GB RAM, use
innodb_buffer_pool_size=1G
innodb_buffer_pool_instances=1
aria_pagecache_buffer_size=128M; default value

For systems with 8GB RAM, use
innodb_buffer_pool_size=4G
innodb_buffer_pool_instances=4
aria_pagecache_buffer_size=1G

Starting from MariaDB 10.2.2, use this instead (example for 8GB RAM):
# innodb_buffer_pool_size=4G
innodb_buffer_pool_chunk_size=1G
innodb_buffer_pool_instances=4
aria_pagecache_buffer_size=1G

UPDATE 2018-11-30

As of MariaDB 10.3 the following options will have no effect and even prevent MariaDB from starting on Windows:
  • innodb_log_block_size
  • innodb_file_format; (is always Barracuda)

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 probably 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.

UPDATE 2019-03-11
  • Amended some values in the first post
  • Added value for Aria pagecache
  • Added recommendations for different system RAM

UPDATE 2019-03-14
  • Added info for dynamic buffer
  • Added info for dumping and pre-loading buffer
  • Added info to turn off double-buffer writes

If possible, you should run MariaDB 10.2.2 or higher as more and more values are being automatically optimized when they are not specified! Also, older versions than 10 will lose Kodi core support at some point.
Reply
#2
Good advice thanks!
Reply
#3
+1 on the thanks!

Also, because I'm dumb I actually had to google this: on non-Windows systems, my.ini is my.cnf. For anyone else's future reference.
Reply
#4
Nice post OP. I tried some of these on my Synology's MariaDB settings. While I didn't record before times, I think this really helps the beat the slow query issue. Results come back almost instant from a large library. If confirmed by others, this should be in the wiki.
Reply
#5
Does this go in the server config file? Or where? Ubuntu 16.04 lts.

[edit]. See it. My.cnf
Reply
#6
Thanks HeresJohnny, you gave me inspiration to do some digging. My Kodi would crash often at times. Looking further, I noticed, that my MySQL (Maria DB 10) had a CPU load of over 250 % -- constantly. When it went down, it still was over 100 %. I have added some of your tricks, but apparently, this wasn't enough. So I have noticed, that Kodi is not using any Indexes. I monitored the Kodi SQL statements used and am currently trying my luck with four indexes (need to be added to each Kodi video database used, in my case I have 4 for my 4 profiles):
Code:
create index movie_idSet on movie(idSet);
create index files_lastPlayed on files(lastPlayed(255));
create index files_dateAdded on files(dateAdded(255));
create index files_playCount on files(playCount);

For me, this is a big stability / performance plus, but I haven't used this for long yet. Will keep watching it. It should be possible to optimize database access further on a program-level, e.g. Kodi is using Blobs (potentially endless big objects) for date storage instead of date objects and it has queries on them.

To monitor, what the MySQL daemon is doing, I use the following loop:
Code:
while true; do clear; uptime; echo show full processlist | mysql -u root; sleep 5; done
Reply
#7
(2018-10-16, 23:16)annomatik Wrote: So I have noticed, that Kodi is not using any Indexes.
Along with your 250% usage this makes me think you have something serious wrong with your video database. Kodi certainly does define and use indexes. Unfortunately so I have no idea why you are not seeing them but allowing Kodi to recreate the db may be the best way to proceed.
Reply
#8
I was puzzled by that myself because the video and music databases certainly use indexes. Maybe what @annomatik tried to say was that for the queries he monitored certain fields had no indexes. For large databases, more indexes might make sense but as always with databases only lots of benchmarking will show if a change is useful.
Reply
#9
Hmmm. I have been migrating the Kodi 17.6 DB twice, maybe the indexes got lost somewhere on the way?

Kodi 18 will have new database versions, right? And they will have their own indexes, I asume? Let's see what happens when I migrate to Kodi 18.
Reply
#10
Ok, having another look today... The performance for the _view tables seems to be quite bad for me (factor 15). Is this just happening for me or is this a general problem?
Code:
MariaDB [myvideos107]> select count(*) from movie;
+----------+
| count(*) |
+----------+
| 3124     |
+----------+
1 row in set (0.33 sec)

MariaDB [myvideos107]> select count(*) from movie_view;
+----------+
| count(*) |
+----------+
| 3101     |
+----------+
1 row in set (5.11 sec)

Also had a look regarding indexes. Yes, I have some, but the slow-downs I had where not covered by them. The one with movie_ is the one I created:
Code:
MariaDB [myvideos107]> show index from movie;
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| movie | 0 | PRIMARY | 1 | idMovie | A | 2646 | NULL | NULL | | BTREE | | |
| movie | 0 | ix_movie_file_1 | 1 | idFile | A | 2646 | NULL | NULL | YES | BTREE | | |
| movie | 0 | ix_movie_file_1 | 2 | idMovie | A | 2646 | NULL | NULL | | BTREE | | |
| movie | 0 | ix_movie_file_2 | 1 | idMovie | A | 2646 | NULL | NULL | | BTREE | | |
| movie | 0 | ix_movie_file_2 | 2 | idFile | A | 2646 | NULL | NULL | YES | BTREE | | |
| movie | 1 | ixMovieBasePath | 1 | c23 | A | 240 | 12 | NULL | YES | BTREE | | |
| movie | 1 | movie_idSet | 1 | idSet | A | 882 | NULL | NULL | YES | BTREE | | |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.00 sec)

MariaDB [myvideos107]>


Joining manually seems to be much quicker, about factor 7 (I didn't join everything here though):

Code:
MariaDB [myvideos107]> select c00, dateAdded, uniqueid_value from movie_view order by dateAdded desc limit 5;
+----------------------------------------------+---------------------+----------------+
| c00                                          | dateAdded           | uniqueid_value |
+----------------------------------------------+---------------------+----------------+
| Crank 2: High Voltage                        | 2018-11-12 19:59:09 | tt1121931      |
| Crank 1                                      | 2018-11-12 19:54:53 | tt0479884      |
| The Incredibles 1.8: Mr. Incredible and Pals | 2018-11-11 10:47:09 | tt0486141      |
| The Incredibles 1.3: Jack-Jack Attack        | 2018-11-10 23:23:51 | tt0455565      |
| Class of Nuke 'Em High 1                     | 2018-11-10 13:45:41 | tt0090849      |
+----------------------------------------------+---------------------+----------------+
5 rows in set (1.25 sec)

MariaDB [myvideos107]> select m.c00, f.dateAdded, r.rating_id, r.rating_type from movie m, files f, rating r where m.idFile=f.idFile and m.c05=r.rating_id order by dateAdded desc  limit 5;
+----------------------------------------------+---------------------+-----------+-------------+
| c00                                          | dateAdded           | rating_id | rating_type |
+----------------------------------------------+---------------------+-----------+-------------+
| Crank 2: High Voltage                        | 2018-11-12 19:59:09 |     71140 | themoviedb  |
| Crank 1                                      | 2018-11-12 19:54:53 |     71141 | themoviedb  |
| The Incredibles 1.8: Mr. Incredible and Pals | 2018-11-11 10:47:09 |     71143 | themoviedb  |
| The Incredibles 1.3: Jack-Jack Attack        | 2018-11-10 23:23:51 |     71142 | themoviedb  |
| Class of Nuke 'Em High 1                     | 2018-11-10 13:45:41 |     71144 | themoviedb  |
+----------------------------------------------+---------------------+-----------+-------------+
5 rows in set (0.18 sec)

MariaDB [myvideos107]>
Reply
#11
Found another missing index. In episodes, idFile is a combined index, it needs idEpisode AND idFile to work. In the episode_view, in the join, only idFile is used --> bad performance.

Still not happy with the performance, but it's a start. (1.78s vs. 6.51s for that query)
Code:
MariaDB [myvideos107]> 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 15;
+---------------------------------------------+--------------------------------+------+------+
| episode_title                               | show_title                     | snr  | enr  |
+---------------------------------------------+--------------------------------+------+------+
| Up In Smoke We Go                           | Trailer Park Boys              | 10   | 7    |
| Week 8: Semi-Finals                         | Dancing with the Stars         | 27   | 10   |
| Super Human Stuntman                        | Tosh.0                         | 8    | 24   |
| Awkwafina/Travis Scott                      | Saturday Night Live            | 44   | 2    |
| Sonderhalt in Ehrenfeld                     | Neo Magazin Royale (de)        | 11   | 12   |
| Shelly, das schnappende Geburtstagsgeschenk | Der Hundeflüsterer (de dub)    | 4    | 23   |
| Turbo der Schäferhund                       | Auf den Hund gekommen (de dub) | 1    | 11   |
| Ruby and Mary                               | Hoarders                       | 8    | 4    |
| Jackie and Richard                          | Hoarders                       | 8    | 3    |
| Über die Grenze                             | Der Winzerkönig                | 2    | 4    |
| Fire and Reign                              | American Horror Story          | 8    | 9    |
| One Hole or Two?                            | Tosh.0                         | 10   | 19   |
| Bart's Not Dead                             | The Simpsons                   | 30   | 1    |
| Just One of the Boyz 4 Now for Now          | Bob's Burgers                  | 9    | 1    |
| Heartbreak Hotel                            | The Simpsons                   | 30   | 2    |
+---------------------------------------------+--------------------------------+------+------+
15 rows in set (6.51 sec)
 
Code:
MariaDB [myvideos107]> show index from episode;
+---------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name                  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| episode |          0 | PRIMARY                   |            1 | idEpisode   | A         |       37783 |     NULL | NULL   |      | BTREE      |         |               |
| episode |          0 | ix_episode_file_1         |            1 | idEpisode   | A         |       37783 |     NULL | NULL   |      | BTREE      |         |               |
| episode |          0 | ix_episode_file_1         |            2 | idFile      | A         |       37783 |     NULL | NULL   | YES  | BTREE      |         |               |
| episode |          0 | id_episode_file_2         |            1 | idFile      | A         |       37783 |     NULL | NULL   | YES  | BTREE      |         |               |
| episode |          0 | id_episode_file_2         |            2 | idEpisode   | A         |       37783 |     NULL | NULL   |      | BTREE      |         |               |
| episode |          1 | ix_episode_season_episode |            1 | c12         | A         |         257 |     NULL | NULL   | YES  | BTREE      |         |               |
| episode |          1 | ix_episode_season_episode |            2 | c13         | A         |        7556 |     NULL | NULL   | YES  | BTREE      |         |               |
| episode |          1 | ix_episode_bookmark       |            1 | c17         | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| episode |          1 | ix_episode_show1          |            1 | idEpisode   | A         |       37783 |     NULL | NULL   |      | BTREE      |         |               |
| episode |          1 | ix_episode_show1          |            2 | idShow      | A         |       37783 |     NULL | NULL   | YES  | BTREE      |         |               |
| episode |          1 | ix_episode_show2          |            1 | idShow      | A         |        1642 |     NULL | NULL   | YES  | BTREE      |         |               |
| episode |          1 | ix_episode_show2          |            2 | idEpisode   | A         |       37783 |     NULL | NULL   |      | BTREE      |         |               |
| episode |          1 | ixEpisodeBasePath         |            1 | c19         | A         |        5397 |       12 | NULL   | YES  | BTREE      |         |               |
+---------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
13 rows in set (0.00 sec)

MariaDB [myvideos107]> create index episode_idFile on episode(idFile);
Query OK, 0 rows affected (9.49 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
Code:
MariaDB [myvideos107]> 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 15;
+---------------------------------------------+--------------------------------+------+------+
| episode_title                               | show_title                     | snr  | enr  |
+---------------------------------------------+--------------------------------+------+------+
| Up In Smoke We Go                           | Trailer Park Boys              | 10   | 7    |
| Week 8: Semi-Finals                         | Dancing with the Stars         | 27   | 10   |
| Super Human Stuntman                        | Tosh.0                         | 8    | 24   |
| Awkwafina/Travis Scott                      | Saturday Night Live            | 44   | 2    |
| Sonderhalt in Ehrenfeld                     | Neo Magazin Royale (de)        | 11   | 12   |
| Shelly, das schnappende Geburtstagsgeschenk | Der Hundeflüsterer (de dub)    | 4    | 23   |
| Turbo der Schäferhund                       | Auf den Hund gekommen (de dub) | 1    | 11   |
| Ruby and Mary                               | Hoarders                       | 8    | 4    |
| Jackie and Richard                          | Hoarders                       | 8    | 3    |
| Über die Grenze                             | Der Winzerkönig                | 2    | 4    |
| Fire and Reign                              | American Horror Story          | 8    | 9    |
| One Hole or Two?                            | Tosh.0                         | 10   | 19   |
| Bart's Not Dead                             | The Simpsons                   | 30   | 1    |
| Just One of the Boyz 4 Now for Now          | Bob's Burgers                  | 9    | 1    |
| Heartbreak Hotel                            | The Simpsons                   | 30   | 2    |
+---------------------------------------------+--------------------------------+------+------+
15 rows in set (1.78 sec)
Reply
#12
This is quite interesting but may be beyond the scope of discussions with other users. Maybe you could submit some general PRs to add those indexes on github?

Edit: How come you're at myvideos107? We're at myvideos112, you should test with latest.
Reply
#13
Hmmm. :-) I'm still on Kodi 17.6 (actually, I'm "just" a Kodi user), maybe that's the reason :-)

I'm doing some more digging, probably better suited to put that into github. Where exactly, do you have a link for me?

*Edit*: Here you go: https://github.com/xbmc/xbmc/issues/14892
Reply
#14
Since db's are Kodi core this would be the one: https://github.com/xbmc/xbmc/pulls
Reply
#15
Thanks, I did a "todo" request, as I don't want to do the programming :-)

https://github.com/xbmc/xbmc/issues/14892
Reply



Logout Mark Read Team Forum Stats Members Help
Using and tuning MariaDB as your central database1
This forum uses Lukasz Tkacz MyBB addons.