don't have much time at the moment. But would like to give you some hints for going on If you have queries where you can't find out what to do send me a PM. I will also try to follow this. But I have got lots of work and three sick kids, so not much time.

If you would like to find out which are the slow queries, activate this (remove the #) in /etc/mysql/my.cnf :

Note : "long_query_time" means the time that a query should run before it gets into the logfile. A good start is probably 10 seconds, but for some things you can also go lower

And do a

/etc/init.d/mysql restart

After that use try to show the video datagrid and take a look in the "/var/log/mysql/mysql-slow.log" logfile.You can the take the query from there an execute it on it's own.

I di not have time to look at the UpdateMedia query after the optimizing, but before it use 215 millions rows to get it done. That will prbably clear the query_cache

You should not run you system permanently with this turned on though. The log will grow quite big.

-------

To find out what is actually done use this :

EXPLAIN EXTENDED <query>;SHOW WARNINGS;

In the Update Media query that looked like this. As you can see in "before" it uses temp tables (using temporary) and "after" it is using indexes. That is a lot faster So if a query uses temp tables , we can probably improve performance with indexes.

My data grids populate pretty much instantly all the time. I have about 1750 avi files, and probably lots more as mkv, mp4, etc. I usually use Filename sort mode which obviously isn't going to run the same big queries, so I just tested using Title... same results. It populates in like 0.5 seconds.

I tried /etc/init.d/mysql restart, then tried it again (on the basis that presumably restarting the SQL server would flush any caches) the first time it seemed to take about 1 second to populate, and subsequent retries back to 0.5 seconds. I note that only a relatively small percentage of my media files have artwork assigned, in case that slows things down (wouldn't think so, as it should only be retrieving the artwork for the tiles that are actually in the display at the time, so probably no more than 20 images)

When I type /etc/init.d/mysql status the SQL server had been running for 25 days and had executed about 12 million queries (ave around 5 per second), but only about 600 were considered "long". That being said, the cnf file has the "long" query defined as 2 seconds commented out, so I'm not sure how it is determining which queries are long!

Obviously I don't want my queries to take as long as yours! But I'm a bit baffled as to why they don't. (using 0710 btw)

...You must be doing something special... I'm looking at a pretty noticeable delay, and I have much less than 1000 video files... Mine are all fully tagged, mostly ISOs, with cover-art. I haven't timed it, but I'd say it's taking at least 3-5 seconds to get the orbiter updated. Not sure if that's an SQL issue or not, though - it performs much better with my new Wireless AP, before I was looking at about 10 seconds... The web orbiter is also noticeably slow, but harder to put my finger on how long that takes. I don't usually use the on-screen orbiter, so I can't compare to that right now. I'll look into it more if someone needs more data.

In 14 days of uptime, I have 9.6 million queries, average of 7.6 query/second, 1 slow query. Using whatever the default is for slow query time. The slow query log doesn't exist. This is in 0810, last updated a week or two ago.

Artwork may be a contributing factor then, almost all of my media has artwork. I can see the memory being allocated for the query in mysql when I select Audio or Video button in orbiter (takes ~7s). The Audio query occupies a about 570Kb in the cache on my system. I return to the home menu and select audio, the artists now display in about 0.5s. As I monitor the cache in mysql after a couple minutes a 570kb chunk is freed from the cache memory. Selecting audio again takes ~7s and about 570kb is added to the query_cache. Mysql is abosolutely removing the query from the cache every few minutes on my system. I'm not home from work yet but I'm going to dig around a bit more when I get home.

When the query is executed and not present in the cache, top shows mysql using 90-100% of the processor for nearly the entire 7s time period, and it doesn't even show up as a blip in top when the query is in the cache. I'm about to investigate some of the slow query logging options Viking pointed out and see what I can find out.

How are you determining the amount of cache space consumed by the query?

I would have assumed that the query to create the collection of files would not have also retrieved the artwork. Only once Orbiter determines how many and which files are needed for the onscreen grid would it then query and return the artwork for those files only. In which case the artwork assigned shouldn't matter.....

I would have assumed that the query to create the collection of files would not have also retrieved the artwork. Only once Orbiter determines how many and which files are needed for the onscreen grid would it then query and return the artwork for those files only. In which case the artwork assigned shouldn't matter.....

Yes, you are correct as far as I can tell. I don't see this being artwork related with the behaviour I'm observing.

What strikes me. Why is it doing this :"AND PK_File in (37968,37996,38006, ....."

It looks like there is an query before that which sorts out the files to look at. The question is then if it makes sence to split that up in two. But maybe we should just try and optimize the query first

The second thing i posted gives some more infos :

# write output to a logfile mysql --tee=/tmp/mysql-output.log

set profiling=1;"run query"show profile all;

That shows where the time has gone (second row) - for example if the problem is that it creates a temp table.

Here a cutout of the UpdateMedia query - you can see that it spents quite some time with "Copying to tmp table" and does some I/O (Block_ops_out) :

just did find time to find the query for my system and it puzzels me that every dvd/recording shows up not only once, but several times !? :(there were more Evolution lies, had to remove thre to fit in 20000 chars limit)

just did find time to find the query for my system and it puzzels me that every dvd/recording shows up not only once, but several times !? :

there is a command to prevent duplicate lines in result sets from mysql. Just try to do a "SELECT DISTINCT ..." instead of "SELECT ...". Reducing the result set of a query does also reduce resource usage, especially if the results are used to start another query.

Quote

What strikes me. Why is it doing this :"AND PK_File in (37968,37996,38006, ....."

It looks like there is an query before that which sorts out the files to look at. The question is then if it makes sence to split that up in two. But maybe we should just try and optimize the query first.

Using nested queries should even speed up the process because the results do not have to be tranferred back and forth, i.e. try something like "SELECT ... FROM ... WHERE ... AND PK_File in (SELECT DISTINCT FK_File FROM ....)".