[john_es]

Just moved to a new NAS (mysql database)... for whatever reason, the scanning of the library is taking 30 second or more per movie whereas on my old configuration it took much much less time.

Code:

skip-external-locking
#skip-name-resolve
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 10.0.0.5

innodb_buffer_pool_size = 6M
key_buffer_size = 16k
myisam_sort_buffer_size = 16k
max_connection = 20

#
# * Fine Tuning
#
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query

Can someone point out my problem? I think I just need a new set of eyes on the config...

[baggar11]

I think we'll need more info. Where is the bottleneck that you see right now? CPU/Memory/Disk? Lan/Internet? NAS? XBMC side? What's the xbmc.log saying during the scan?

[Aenima99x]

Have you run MySQL Tuner to see what it recommends?
https://rtcamp.com/tutorials/mysql/mysqltuner/

[john_es]

See, I thought I had this issue before and solved it with an option in the my.cnf related to the skip-* lines.

But the new version of the Readynas has a new mysql addon and I wonder if that instead is the issue. I'll write back in a bit with a log, but there was nothign interesting - it was just completely normal.

As for the bottleneck, I don't know - I am running XBMC on a super fast laptop, connected to the NAS (314).

---

Code:

>>  MySQLTuner 1.1.1 - Major Hayden <[email protected]>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.31-0+wheezy1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in InnoDB tables: 4M (Tables: 56)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 56



-------- Performance Metrics -------------------------------------------------
[--] Up for: 18h 37m 49s (31K q [0.471 qps], 227 conn, TX: 12M, RX: 3M)
[--] Reads / Writes: 63% / 37%
[--] Total buffers: 70.0M global + 2.7M per thread (20 max threads)
[OK] Maximum possible memory usage: 123.8M (6% of installed RAM)
[OK] Slow queries: 0% (0/31K)
[OK] Highest usage of available connections: 20% (4/20)
[OK] Key buffer size / total MyISAM indexes: 16.0M/99.0K
[OK] Query cache efficiency: 43.1% (9K cached / 22K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 182 sorts)
[OK] Temporary tables created on disk: 9% (83 on disk / 899 total)
[OK] Thread cache hit rate: 98% (4 created / 227 connections)
[OK] Table cache hit rate: 27% (112 open / 410 opened)
[OK] Open file limit used: 4% (48/1K)
[OK] Table locks acquired immediately: 100% (21K immediate / 21K locks)
[OK] InnoDB data size / buffer pool: 4.3M/6.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries

[Aenima99x]

Just noticed something in your my.cnf file, uncomment skip-name-resolve. That should fix it

[john_es]

I uncommented skip-name-resolve and same thing... weird thing is that this is reproducible on 2 ReadyNAS 316s, the old Pros work like a charm. The status indicates that it's scraping themoviedb, but nothign should be hitting that because I have local NFOs and fanart saved as movie.nfo, etc....

[jmarshall]

One presumes you've created a Debug Log. You're not guessing, right? RIGHT

[john_es]

Here it is
http://xbmclogs.com/show.php?id=139749

[john_es]

It seems that what is taking forever (>10 seconds per episode, as well as scan for new content) is stuff like this:

Code:

xbmcadmin | 192.168.10.2:58022 | MyVideos75 | Sleep   |  113 |              | NULL                                                                        |
| 131 | root      | localhost            | MyVideos75 | Query   |    0 | NULL         | SHOW PROCESSLIST                                                            |
| 134 | xbmcadmin | 192.168.10.2:58245 | MyVideos75 | Query   |    0 | Sending data | select idActor from actors where strActor like ' Constance Wu (Guest Star)' |

| 134 | xbmcadmin | 192.168.10.2:58245 | MyVideos75 | Query   |    0 | query end | insert into actors (idActor, strActor, strThumb) values( NULL, ' Alan Davidson (Guest Star)','') |
| 135 | root      | localhost            | NULL       | Query   |    0 | NULL      | SHOW PROCESSLIST                                                                                 |

I am wondering if setting an index on strActor would speed things up...?

[jmarshall]

Hmm, there should really be one on there

Please try adding one and compare the times to update (you may need to do a case-free collation) for it to be used.

Unfortunately I suspect the case-free collation syntax will differ between mysql and sqlite, so it may not be a trivial thing to fix.

Cheers,
Jonathan

[john_es]

So it turns out that the XBMC DB is using utf8_general_ci and not utf8_unicode_ci (https://dev.mysql.com/doc/refman/5.6/en/...ivity.html)

That's a case insensitive collation. I've kept it the way it is even though there are benefits to the latter (details here: https://stackoverflow.com/questions/1036...unicode-ci).

I added an index to actors.strActor (the only index was on the primary key), via: https://stackoverflow.com/questions/1827...key-length

This has sped things up greatly.

So my question is, should XBMC have setup these indexes?
If not, what fields on which tables should have indexes?

[jmarshall]

What is the index (SQL for it) that you used? The trick is getting something to work with both sqlite and mysql.

[john_es]

alter table actors add index strActor(strActor(200));