[theowiesengrund]

Hey everyone,

after finally purchasing a NAS (Syno DS413j) i decided to start using a MySQL library.
So, i configured XBMC as well as the NAS (MariaDB is used in the latest Syno DSM firmware) - everything worked without a problem.

My RaspPi is connected via ethernet to the NAS, so there shouldn't be any speed problems. The media folders (also on the NAS) are shared via NFS.

First i added all my tv shows over night and i actually didn't check how long it took, because in the morning everything was fine.

Yesterday i added one of my movie folders to my library. The movie folder consists of about 500 movies, each in its own folder "<filmtitle> (<year>)" and every movie has it's own nfo file and all the artwork is also stored locally in the movie folders. In the scraping settings i activated "Movies are in separate folders that match the movie title" and i deactivated "Scan recursively".

I started yesterday evening and today in the morning it was still NOT finished (approx. 80% was done).

Here is the log file - unfortunately without debug logging - http://sprunge.us/gHaF

I added the movies at approx 22:20 and at 07:55 it wasn't done. How is this possible, especially since my media is organized accurately and the client shouldn't need to connect to the internet for any artwork. My NAS isn't the fastest one, but such a long time cannot be normal. There weren't any other processes active on my NAS at the time of scraping. I checked - after recognizing the slow scan - with top the cpu usage, and mysql was using approx. 90%.

Any ideas, what's going wrong? I could add later in the evening another movie folder with debug logging enabled if it helps.

[da-anda]

I assume you had actor scraping enabled, and this could take a while. But 500 movies shouldn't take that long. Your log shows some errors which mention that some fanart could not be read, was corrupt or had other issues (pacific rim fanart f.e.), but even that shouldn't delay stuff for that long. Are you sure you configured the local nfo scraper?

[theowiesengrund]

Well, i have indeed in every nfo file actors included - to be more specific: the complete cast from imdb. So, there are a LOT of actors.
Nevertheless i don't have any local artwork for the actors and i disabled the "download actor thumbnails" option, therefore it shouldn't download and/or cache any actor artwork. Are the SQL queries really that slow, that filling the actors table takes so long?

By the way i didn't configured the nfo scraper, but use the TMDB scraper. In my understanding, even with this setting XBMC checks first if there is a local nfo file available before contacting TMDB.
I used this setting for the rare case in the future, that i add a movie without an according nfo file.

I used the same settings before when i used a local library. But while it took a time to scrape the contents, it wasn't nearly as long as now with MySQL.

[theowiesengrund]

Got home from work.
Just for the record: "09:40:13 41133.921875 T:2805052496 NOTICE: VideoInfoScanner: Finished scan. Scanning for video info took 11:17:14"

Doing now another scan with debug logging enabled (also approx. 500 movies)

[theowiesengrund]

Ok, the problem are indeed the actors:

Here is an excerpt for one movie:

http://pastebin.com/3U3SH8AA

While the "Mysql execute: insert into actors" query is processed very fast, the next one called "Mysql execute: insert into actorlinkmovie" takes about 1 second. :-(

Any idea?

[Milhouse]

I'd check the MySQL/MariaDB configuration and confirm your Synology is up to snuff at being a database server, chances are it's short of memory and/or horsepower. The table actorlinkmovie has two indexes on it, check these have been created correctly, perhaps also check how big this table is (total number of rows) - you may just have too many rows for the Synology to cope with.

You might also want to keep an eye on PR5170, although I don't think it's likely to help you just yet, and may not make much difference at all in future for new scrapes, but will make a huge difference when updating existing library items.

[theowiesengrund]

My my.cnf

Code:

[client]
port = 3306
socket = /run/mysqld/mysqld.sock

[mysqld]
bind-address = 0.0.0.0
port = 3306
socket = /run/mysqld/mysqld.sock
skip-external-locking
skip-name-resolve
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 = 240K
innodb_data_home_dir = /var/services/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/services/mysql
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout

# Please add your custom configuration to here:
!include /var/packages/MariaDB/etc/my.cnf

"SELECT * FROM `actorlinkmovie`" delivers 59926 results.

I have two indexes ix_actorlinkmovie_1 & ix_actorlinkmovie_2. The space usage of the table is 8.5 MB (3.5 data and 5 MB index). Any simple SQL query is delivered fast as expected (SELECT * FROM `actorlinkmovie` WHERE `idMovie` = 1606 => (109 total, Query took 0.0034 sec)) I haven't tried any inserts though.

[steve1977]

Actually, I am facing the same issue (just opened a new thread including this and a related topic). My "server" is windows run and has more than enough horse power (i7, 12GB ram). So, it shouldnt't be the NAS problem, but somewhat related to openelec / raspberry. I have another HTPC (with i5) and this scans the same library in a few minutes.

[theowiesengrund]

Ok, so i did a few tests to rule out some theories.

Installed a fresh XBMC 13.1 on my iMac and scraped one movie folder (with 530 movies) into the local library. Scan was finished in 5 minutes and 1 seconds.

Deleted after that the sources and linked the iMac XBMC installation to the advancedsettings.xml & sources.xml files from my RaspPi, for the use of the MySQL library. Added another folder with approx. 300 movies into the library. Takes over a minute for ONE movie - as on the RaspPi:

http://pastebin.com/7icveDV4

So, it is definitely my NAS, but why is MySQL THAT slow - is this really normal? What about my settings above? I added already skip-name-resolve in the mysqld section, like mentioned often in the forum. Another speed up tweak seems to be using InnoDB as the engine. According to this screenshot from my phpMyAdmin tool, MariaDB (and/or XBMC) is already using InnoDB in the current version:

https://i.imgur.com/IL1AHxa.png

So, basically there is nothing to do than to move the library to a more powerful machine? I will do tomorrow a test with a MySQL server on my iMac to compare the results...

[theowiesengrund]

Couldn't wait until tomorrow. So i just installed MySQL on my iMac and pointed the OpenElec installation to the new library and did a rescan: Takes about 2-3 seconds for one movie. That should be evidence enough.
Not a huge deal, since full library scans should be a rare case, but nevertheless shocking.

[Kib]

There are some large changes in Helix coming up, apparently the rescan does a full insert instead of a partial one and this has been fixed.

[steve1977]

There are some large changes in Helix coming up, apparently the rescan does a full insert instead of a partial one and this has been fixed.

This is great to hear. Will this get back-ported to 13.2 or 13.3?

[Kib]

It isn't in Helix yet and I can't find the thread on the forum discussing it now.

Also, I am not sure it is the root cause of your problem.

[schumi2004]

I also encountered slow library creation with my NAS, not sure what's the cause.
It didn't help to switch from SMB to NFS and vice-versa.
I also tweaked some MariaDB settings that did improve some but not much.
Luckily it's a one time action