[SOLVED] - UpdateMedia mysql queries takes 100% CPU on a core - is that normal ?

[Viking]

Hi,

on my core server one of the the AMD e5050 (dualcore) cores max out with 100% when the mysql queries for UpdateMedia are running.

Is that normal ?

I am working for a danish company that has got a lot of really good database people - also ones that are specialized on database optimizing. I could try get them to optimize some of the queries if you like ?
If so, could you point me to a place where I can find those queries ? Or just send them to me ?

The /home/public/data directories at the moment only contains the following amount of files - which I dont considder as much.
pvr : 1979
audio : 1442
video : 580



From /var/log/pluto/UpdateMedia.log

Code:

10      11/09/09 16:15:07.135           MediaState::LoadDbInfo ready to run big query <0xb52fdb90>

top

Code:

top - 16:16:01 up 1 day, 21:22,  2 users,  load average: 1.11, 1.01, 1.03
Tasks: 253 total,   1 running, 252 sleeping,   0 stopped,   0 zombie
Cpu0  : 87.0%us,  8.6%sy,  0.7%ni,  3.7%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu1  :  4.0%us,  3.3%sy,  4.0%ni, 80.8%id,  5.6%wa,  0.7%hi,  1.7%si,  0.0%st
Mem:   3371624k total,  2271536k used,  1100088k free,    60104k buffers
Swap:  4008208k total,     5736k used,  4002472k free,  1308160k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 6212 mysql     20   0  243m 164m 5936 S   93  5.0   1215:56 mysqld
25026 root      39  19 79908  31m 6620 S    5  0.9 121:11.69 UpdateMedia
19134 root      20   0 86768  36m  14m S    3  1.1   1:04.78 Photo_Screen_Sa
18286 mythtv    20   0  391m  81m  11m S    1  2.5 115:25.43 mythbackend

[jimbodude]

Yes, this is "normal".  The queries are in dire need of optimization...

[Viking]

Hi,

found it in src/UpdateMedia/MediaState.cpp

But decided to go for MySQL "log-slow-queries" - that gave me this. It examins 215 mio. rows - holy shit.

Code:

# Time: 091109 17:05:38
# User@Host: root[root] @ localhost []
# Query_time: 53  Lock_time: 0  Rows_sent: 20889  Rows_examined: 215591614
use pluto_media;
SELECT PK_File, Path, Filename, INode,
MAX(PSC_MOD) As CurrentDbAttrDate,
(1000000 * SUM(BOOKMARKS) + 10000 * SUM(ATTRIBUTES) + 100 * SUM(LONG_ATTRIBUTES) + SUM(PICTURES)) AS CurrentDbAttrCount,
(SUM(ATTRIBUTES) + SUM(LONG_ATTRIBUTES)) AS HasAttributes,
AttrDate AS OldDbAttrDate, AttrCount AS OldDbAttrCount, ModificationDate AS OldFileDate,
Source
FROM
(
        (
                SELECT PK_File, Path, Filename, INode, Source, Missing,
                        AttrDate, AttrCount, ModificationDate,
                        MAX(IF(Bookmark.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), Bookmark.psc_mod)) AS PSC_MOD,
                        COUNT(Bookmark.PK_Bookmark) AS BOOKMARKS,
                        0 AS ATTRIBUTES, 0 AS LONG_ATTRIBUTES, 0 AS PICTURES
                FROM File
                LEFT JOIN Bookmark ON Bookmark.FK_File = PK_File
                WHERE 1 = 1 AND (Path LIKE '/home/public/data%' OR (Path = '/home/public' AND Filename = 'data' )  OR Path LIKE '/home/user_1/data%' OR (Path = '/home/user_1' AND Filename = 'data' )  OR Path LIKE '/home/user_2/data%' OR (Path = '/home/user_2' AND Filename = 'data' )  OR Path LIKE '/home/user_3/data%' OR (Path = '/home/user_3' AND Filename = 'data' )  )  AND Missing = 0
                GROUP BY PK_File, Path, Filename, INode,  AttrDate, AttrCount, ModificationDate
        )
UNION
        (
                SELECT PK_File, Path, Filename, INode, Source, Missing,
                        AttrDate, AttrCount, ModificationDate,
                        MAX(IF(Attribute.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), Attribute.psc_mod)) AS PSC_MOD,
                        0 AS BOOKMARKS,
                        COUNT(PK_Attribute) AS ATTRIBUTES,
                        0 AS LONG_ATTRIBUTES, 0 AS PICTURES
                FROM File
                LEFT JOIN File_Attribute ON File_Attribute.FK_File = PK_File
                LEFT JOIN Attribute ON File_Attribute.FK_Attribute = PK_Attribute
                WHERE 1 = 1 AND (Path LIKE '/home/public/data%' OR (Path = '/home/public' AND Filename = 'data' )  OR Path LIKE '/home/user_1/data%' OR (Path = '/home/user_1' AND Filename = 'data' )  OR Path LIKE '/home/user_2/data%' OR (Path = '/home/user_2' AND Filename = 'data' )  OR Path LIKE '/home/user_3/data%' OR (Path = '/home/user_3' AND Filename = 'data' )  )  AND Missing = 0
                GROUP BY PK_File, Path, Filename, INode,  AttrDate, AttrCount, ModificationDate
        )
UNION
        (
                SELECT PK_File, Path, Filename, INode, Source, Missing,
                        AttrDate, AttrCount, ModificationDate,
                        MAX(IF(LongAttribute.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), LongAttribute.psc_mod)) AS PSC_MOD,
                        0 AS BOOKMARKS, 0 AS ATTRIBUTES,
                        COUNT(PK_LongAttribute) AS LONG_ATTRIBUTES,
                        0 AS PICTURES
                FROM File
                LEFT JOIN LongAttribute ON LongAttribute.FK_File = PK_File
                WHERE 1 = 1 AND (Path LIKE '/home/public/data%' OR (Path = '/home/public' AND Filename = 'data' )  OR Path LIKE '/home/user_1/data%' OR (Path = '/home/user_1' AND Filename = 'data' )  OR Path LIKE '/home/user_2/data%' OR (Path = '/home/user_2' AND Filename = 'data' )  OR Path LIKE '/home/user_3/data%' OR (Path = '/home/user_3' AND Filename = 'data' )  )  AND Missing = 0
                GROUP BY PK_File, Path, Filename, INode,  AttrDate, AttrCount, ModificationDate
        )
UNION
        (
                SELECT PK_File, Path, Filename, INode, Source, Missing,
                        AttrDate, AttrCount, ModificationDate,
                        MAX(IF(Picture_File.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), Picture_File.psc_mod)) AS PSC_MOD,
                        0 AS BOOKMARKS, 0 AS ATTRIBUTES, 0 AS LONG_ATTRIBUTES,
                        COUNT(FK_Picture) AS PICTURES
                FROM File
                LEFT JOIN Picture_File ON Picture_File.FK_File = PK_File
                WHERE 1 = 1 AND (Path LIKE '/home/public/data%' OR (Path = '/home/public' AND Filename = 'data' )  OR Path LIKE '/home/user_1/data%' OR (Path = '/home/user_1' AND Filename = 'data' )  OR Path LIKE '/home/user_2/data%' OR (Path = '/home/user_2' AND Filename = 'data' )  OR Path LIKE '/home/user_3/data%' OR (Path = '/home/user_3' AND Filename = 'data' )  )  AND Missing = 0
                GROUP BY PK_File, Path, Filename, INode, AttrDate, AttrCount, ModificationDate
        )
) AS FILE_UNION
WHERE 1 = 1 AND (Path LIKE '/home/public/data%' OR (Path = '/home/public' AND Filename = 'data' )  OR Path LIKE '/home/user_1/data%' OR (Path = '/home/user_1' AND Filename = 'data' )  OR Path LIKE '/home/user_2/data%' OR (Path = '/home/user_2' AND Filename = 'data' )  OR Path LIKE '/home/user_3/data%' OR (Path = '/home/user_3' AND Filename = 'data' )  )  AND Missing = 0
GROUP BY PK_File, Path, Filename, INode, AttrDate, AttrCount, ModificationDate;

Are there any other queries that are not so good ? Video/audio list queres or something like that ?

Greetings
Viking

[Viking]

Hi,

OK we need some indexes on the Database

Before :
19495 rows in set (1 min 23.67 sec)

After :
19821 rows in set (8.56 sec)

I assume the different number of rows has to do with that it was scanning pictures while I did the first run. But maybe on of yiu can confirm that there is no difference if you disable UpdateMedia while doing it :
/usr/pluto/bin/UpdateMediaDaemonControl.sh -disable


here is what to do :

ALTER TABLE `pluto_media`.`File` ADD INDEX  `FatGroupBy`(`PK_File`,`Path`,`Filename`,`INode`,`AttrDate`,`AttrCount`,`ModificationDate`)

ALTER TABLE `pluto_media`.`Bookmark` ADD INDEX `FK_File`(`FK_File`);
ALTER TABLE `pluto_media`.`LongAttribute` ADD INDEX `FK_File`(`FK_File`);

Greetings
Viking

[jimbodude]

...well that's not very hard...  Nice find.

Can you put that on a ticket so it gets in the code?

[Viking]

I'll do that if someone confirms that there is no difference between rows before and after the change

Viking

[niz23]

Viking,

I'll do that if someone confirms that there is no difference between rows before and after the change

Viking

Nice find.
This is a huge performance boost for a simple change.


I took the liberty to add this to trac so we can track what is done to MySQL considering optimizations. See ticket 439.

/niz23

[phenigma]

I tried this on my production system with about 41000 rows in the pluto_media table.

The indexes have reduced the period of time mysqld pegs my processor at 100%.

I monitor my core with monitorix and rrdtool, it's a P4 2.8G hyperthreaded cpu with 1GB ram.  It looks like adding the indexes created an average reduction of ~7% kernel usage, load average has dropped from about 1.0-1.1 average to 0.6-0.7 average.  IOWaits have also been cut in half of what they were.  The one day change is noticeable looking at my week and month summary charts.  I'm going to keep my eye on it to see if it's consistent.

J.

[Viking]

Viking,

I'll do that if someone confirms that there is no difference between rows before and after the change

Viking

Nice find.
This is a huge performance boost for a simple change.


I took the liberty to add this to trac so we can track what is done to MySQL considering optimizations. See ticket 439.

/niz23

Great, as I can see it will already be included in the next beta

If there is more SQL that you need someone to look at, feel free to PM or mail me.

Greetings
Viking

[jimbodude]

Have you had a chance to look at the rest of the tables?  Maybe others also do not have indexes, or have the wrong ones?

Really a great find, thank you.

[niz23]

Best way to find things to optimize is to identify slow queries as Viking have already done.
If any of you want to do it feel free to post optimizations here.
When a slow query is found post them here or even better identify where in the code they come from so that we can look at sql code optimization too.

To start with identify queries that take more than 10s to complete.
When we have fixed those we can lower it to 5s.

There´s other ways to optimize mysql too. But as I´m no mysql expert I don´t know how to tune it´s query cache etc.
If someone do. Please tell us what can be done to make queries faster.

/niz23

[phenigma]

I was looking at query_cache a little last night.  Most of the use/config seems transparent and efficiencies come by writing less frequently to tables.  Essentially a cached query is dropped from the cache when a write is made to a table used in the cached query.

Example: Queries that bring up the Audio/Video file lists from orbiter execute in about 7s when not in the query_cache and in about 0.5s when they are in the query_cache (on my system).  Some of these large queries are not cached for enough time (because of a write to a table used in the query) to make them really useful (like the audio/video file lists).  Update media may be writing to the media tables even when media has not changed, which is clearing these queries from the cache (I havn't even begun to look at updatemedia).

I have not looked past this yet but was intrigued by the initial improvements from Viking's index additions and did a little bit of poking around.

J.

[colinjones]

phenigma

are you saying that most of the time calling up media grids on your system takes around 7 seconds?

How many media files/entries in pluto_media.file do you have?

My experience is that the first time I call up the media grids after a reload, they are marginally slower... say 2s max, then from then on they are extremely nippy! I don't get a sense at all that SQL is the bottleneck in my system. Interestingly, reloading the Router does not restart MySQL nor can I think of any reason it would be writing to tables at that point so the cache should be intact over a reload.... I assumed it more more the orbiter code itself, perhaps caching thumbnails, or other activities.

Anyway, my underlying point is, if I'm not doing anything, it certainly doesn't appear that UpdateMedia running in anyway impacts cache performance by flushing it. Although I do note there is still (0710, and 0810 I think) a bug in UM that means when you set a thumbnail for a folder this causes UM constantly to redetect this as a change, and perhaps that is constantly writing into the database.

[jondecker76]

With about 800 videos, on my system it takes probably 10 seconds to complete the query and populate the datagrid (this has been the case both in 0710 and 0810). Mysql definitely seems to be a bottleneck on my system!

[phenigma]

Without actually looking, I think I have about 600 videos and around 800 or 900 artists in my audio folders.

The response time is the same regardless of the orbiter I use (n800,webdt,onscreen), UI1 or 2, core or MD.  It takes about 7 seconds from click/touch to display of the datagrid.  When I do this the query is cached by query_cache in mysql and choosing audio/video again immediately is very responsive from click/touch to display of the datagrid, 0.5 seconds on my windows orbiter to about 3 seconds on my WebDT.  Response is the same now as it was under 0710, I'm currently running 0810 Alpha 2.37.

The responsiveness lasts for a couple minutes, until the query is removed from the cache, and then it takes about 7 seconds to re-rerun the query and re-store the results in mysql query_cache for another couple of minutes.  I can only assume that UpdateMedia is writing to the table but I'm speculating.

J.