Kodi Community Forum

My media is housed on a diy WHS machine along with symlinks to artwork, as well as a MySQL database used to synch the library across all my xbmc clients dotted around the house.

With over 30,000 episodes spanning over 900 series, plus some 2000 movies in my collection, it seems to be taking ages for the library to load. For example, it can take in excess of 3 minutes for the TV Shows library screen to show up. Drilling down/up the series/season screens can take another minute or so. Once an episode has finished playing, it can take another minute or so for XBMC to return to the library screen.

All my clients are wired over gigabit ethernet, and playback of media works just fine (including the use of transport controls). My gut instinct is that the bottleneck is on the MySQL side of things, but I could be wrong. I installed MySQL using the add-in available from MediaSmartServer website, and I think it uses a bespoke my.ini file for WHS, so I wonder whether that’s the problem. Below are excerpts from said ini file:

[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-name-resolve
skip-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K

[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M


I’ve also checked/added indexes to idFile and idMovie on the movie table, as well as idShow on the tvshow table. Also I’ve noticed that all the text fields are using latin1_swedish_ci rather than latin1_general_ci collation (I’ve never even been to Sweden, so I’ve got no idea how this happened)? Could that be causing a problem? Is there a way of converting it to latin1_general_ci without losing the database? Is there something else I should be doing, like adding more indexes and/or changing the setting of the ini file? Thanks.

Also add an index to the idEpisodes in the episode table. You can also try to optimize the tables by entering:

optimize table movie;
optimize table tvshow;
optimize table episode;

That might help it run faster.

Thanks for the advice, unfortunately it didn't make any difference. I did notice that the database was using InnoDB so I altered all the tables to use MyISAM, and that did improve things somewhat. Loading the TV Shows library takes 2 minutes instead of 3 minutes during preliminary testing.

I think I'm going to experiment with mysql config file to see if that improves things, and add some RAM to the server.

Please let me know what you find because I have the same problem as you with pretty much the same specs for everything.

Mark

Mark,

Upgrading the RAM from 1GB to 2GB will not improve MySQL performance until you edit it's config file. However I did notice a marked improvement to the general running of the server, so it's a worthwhile upgrade regardless. It's generally recommended that WHS needs 2Gb of RAM if you are running WHS add-ins and other services (e.g. torrents, mysql, etc., etc.). Just remember that WHS v1 supports a maximum of 4GB RAM.

With the extra RAM installed, I edited my-whs.ini (after making a backup), deleted the entries under [mysqld] & [myisamchk], and copied over the settings from my-large.ini. After saving the file, I restarted the mysql service (or you can reboot the server) for the changes to take effect. I checked to see the new settings were being used by mysql by checking the Status tab using phpMyAdmin. I've listed the entries below (you'll notice that I've also added skip-name-resolve):


[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-name-resolve
skip-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8


[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M


Loading the library initially takes less than 30 seconds, subsequently it then takes 3 seconds (I'm assuming mysql caches the data for a period of time). Drilling down/up the library takes a couple of seconds at most. All in all, miles better than before, and the WAF has improved. All of these changes are on top of the other ones that I performed previously:

• tables/fields are using latin1_general_ci collation
  
• tables are using MyISAM storage engine (not InnoDB)
  
• added indexes to movie table for idFile & idMovie fields
  
• added index to tvshow table for idShow field
  
• added index to episode table for idEpisode field
  
• optimized movie, tvshow & episode tables