[goRt]

***update following diagnosis:
XBMC has several new DB views which place a huge additional RAM requirement on the MySQL server (from 6mb to over 256mb) causing failures when navigating TV shows, etc.***


Hi,
I upgraded on win7 to the latest monthly ALPHA2 and when browsing to TV series I am unable to open e.g. I see 24, but selecting that after 10-15s I'm presented with the TV show list again.
I've tried the same upgrade on RPI (after deleting the TV DB) with the same result (to 88 or 89 DB version)

Upgrade log with the issue in:
http://xbmclogs.com/show.php?id=264954
Log just opening XBMC and then going straight to the issue:
http://xbmclogs.com/show.php?id=264957

Sorry if this is in the incorrect section.

Thanks

[goRt]

OK 44 views and no responses so I've done some further testing
DB upgrade from 78 to 79 works
DB update from 79 to 85 causes the issue

These were tested on the RPi using MH builds, I only have access to the above DB updates

To clarify my environment, all media presented on an NFS share, DBs sit on same server on mySQL

Thanks for looking

[Milhouse]

This isn't a problem for me with MySQL 5.5 and MyVideos79, 85, 86, 87, 88 or 89.

Have you Googled error code 126?

It looks more like a server configuration/setup error. Check your MySQL log to see if there are more details, but the full error text for this error code is usually along the lines of:

Code:

Mysql error : Error Code: 126. Incorrect key file for table '/tmp/#sql_52e_0.MYI'; try to repair it

For some, it's because the temp partition being used by MySQL is full - maybe you need to increase the partition size.

[goRt]

Thank you for your reply @Milhouse

I have a 6tb NAS with ~ 1.5tb free; a RPi version B, a laptop with 500gb SSD ~125gb free.
With MyVideos78 (and 75/76/77) in place and upgrade to 79 works fine, then going to 85 or 88 produces this issue.

Where would I look for the root of the SQL issues please

Thanks again

[Milhouse]

Where would I look for the root of the SQL issues please

On your NAS, or wherever you run the MySQL Server process.

MySQL uses a temporary location as a sort of scratchpad, which in your case may not be large enough for some queries, so you need to look at your MySQL configuration and either increase the size of the partition (assuming it is /tmp) or specify an alternative location.

[goRt]

Thank you again @Milhouse, you're correct that /tmp/ is filling up on my NAS - quite some learning curve!

HOWEVER, 13.2, etc that use v78 utilise 3-6% of 64m, but when I switch to a v88 db then I rapidly get to 100% of 64m and then the errors.
If I modify MySQL to write to the NAS array, then I don't get the failures, but I do have to wait ~20 mins for the result to be returned.

This leads me to the conclusion that there's something wrong / changed with the way the v88 is being handled.

Any thoughts gratefully received.



For those that want to experiment, I have a QNAP 419p, searching the QNAP forums reveals this as a known problem (well 2 individuals report that the system is wrongly delivered, but don't offer a solution).
I found pointers here:
http://forum.qnap.com/viewtopic.php?f=32...ng#p336496

[Milhouse]

In order to progress the discussion within this thread I'll just quickly repeat what I wrote here:

Thanks Milhouse, but further testing has demonstrated that the upgrade is producing the data - with the same 99% of 64mb available previous versions require 3-6% whereas v88 onwards fails in short order having got to 100%.

I notice that the developed (of xbmc) doesn't like MySQL and indicates it has put support for that on hold. The wiki recommends the use of MySQL in this scenario.

Thanks

How large is your tvshow library (how many tvshows, seasons and episodes)?

You should be able to reproduce the problem by running the same query (SELECT * FROM seasonview WHERE seasonview.idShow = N) where N is a valid tvshow id, in your case 405, using any client, eg. MySQL Workbench.

Testing here on a fairly low powered NAS (HP N36L, approximately Atom-class with 8GB RAM, v89 db) and this query completes in less than 0.281 seconds. Running without the WHERE clause returns 215 rows and completes in 0.297 seconds. This is for a library with 61 tvshows, 215 seasons and 2992 episodes, and produces scratch files of less than 100KB:

Code:

-rw-rw----  1 mysql  wheel  71752 Aug 13 17:59 /tmp/#sql1113_816e2_1f.MYD
-rw-rw----  1 mysql  wheel   1024 Aug 13 17:59 /tmp/#sql1113_816e2_1f.MYI

If your tvshow library is not ridiculously large, maybe there's a problem with the database (ie. with one or more of the indexes) that could be fixed by running repair on one or more of the tables that make up the view. Note that I'm not a MySQL expert so you'll need to Google for more information on the repair command (or open a new thread to discuss this issue, just so you get more eyes-on as it's not particularly relevant to the Pi).

Do you still have this problem with v89 (which my latest builds are using)?

and here:

Another factor may be the amount of RAM available to the MySQL server instance. Relational databases love memory more than horsepower, but a database starved of RAM may be forced to hit the scratch area much harder. Much of this is "tuneable" in my.cnf assuming you have the RAM to spare. My configuration is custom (FreeNAS with MySQL running in a jail) and RAM isn't really an issue which may mean it has to use the scratch area much less (if at all). For reference, here's the my.cnf I'm using (HP N36L, AMD Neo 1.3GHz dual-core processor with 8GB RAM).

I've allocated as much as 256MB of RAM to key buffers which may be half the total RAM available on some NAS devices. I'm under no illusion that my database configuration is perfect - I'm not a MySQL expert and just tweaked until the database seemed to be working well enough! - but at least it gives anyone with this problem something to compare against.

[terranow]

execute this script https://raw.githubusercontent.com/major/...qltuner.pl it should point out potential misconfiguration etc.. i run mysql on the same pi using openelec together with tvheadend and xbmc. my database has 89 tv shows and 5678 episodes. Performance is decent..

[goRt]

Thanks again @Milhouse, I'll stick to this thread going forward.
I've executed your query against:
v78 (Query took 0.9634 sec)
v88 (Query took 0.3363 sec)
v89 (Query took 0.3261 sec)

v89 exhibits the same behaviour as 88, testing has demonstrated that the error appears somewhere between 79>85.
Are there any changes to the select statements in there?

I don't think the issue is with my DB.

My NAS has 500mb RAM, of which 64mb is allocated for temporary storage.
As I said
HOWEVER, 13.2, etc that use v78 utilise 3-6% of 64m, but when I switch to a v88 db then I rapidly get to 100% of 64m and then the errors.
If I modify MySQL to write to the NAS array, then I don't get the failures, but I do have to wait ~20 mins for the result to be returned.

This leads me to the conclusion that there's something wrong / changed with the way the v88 is being handled.

Any thoughts gratefully received.

[goRt]

In order to progress the discussion within this thread I'll just quickly repeat what I wrote here:

Thanks Milhouse, but further testing has demonstrated that the upgrade is producing the data - with the same 99% of 64mb available previous versions require 3-6% whereas v88 onwards fails in short order having got to 100%.

I notice that the developed (of xbmc) doesn't like MySQL and indicates it has put support for that on hold. The wiki recommends the use of MySQL in this scenario.

Thanks

How large is your tvshow library (how many tvshows, seasons and episodes)?

You should be able to reproduce the problem by running the same query (SELECT * FROM seasonview WHERE seasonview.idShow = N) where N is a valid tvshow id, in your case 405, using any client, eg. MySQL Workbench.


Do you still have this problem with v89 (which my latest builds are using)?

OK, managed some more testing today.

Running the query above against the v89 DB runs me out of workspace on the NAS, just clicking on the view also does the same.
As you know this view does not exist in V78 (nor do a couple of other views), so it appears that this view is creating a large table space which my NAS can't handle.
I may be the first to encounter this, but I'm sure I won't be the last.

I also deleted my entire library and then re-imported from a saved version (from v78) into v88/v89 produces the same results.

Thanks

[goRt]

I've allocated as much as 256MB of RAM to key buffers which may be half the total RAM available on some NAS devices. I'm under no illusion that my database configuration is perfect - I'm not a MySQL expert and just tweaked until the database seemed to be working well enough! - but at least it gives anyone with this problem something to compare against.

OK, so I've hacked about with my NAS and allocated 256mb of RAM to the SQL sort work area (that should show how old I am) and I still get the failure.
This works if I let the tmp area have full access to the RAID array, but the result takes ~20 mins to return.

I'm still left with the belief that the concept or execution of the view is flawed, previous version without this view used 3-6% of 64mb

Thanks

[goRt]

Title and first post updated for clarity