Huge query in pluto_media : taking my disk down to crawling

[bulek]

Hi,

I've posted a thread about freqent crashes when clicking on Video button on main Orbiter screen... Crash came out of data not being available in reasonable time - and cause is that my disk is so slooow...

But I guess this is not the HW problem, but more SW. if I do this :

Code: [Select]

hdparm -tT /dev/sda
/dev/sda:
 Timing cached reads:   3548 MB in  2.00 seconds = 1771.32 MB/sec
 Timing buffered disk reads:  10 MB in  3.17 seconds =   3.15 MB/sec

When this happens, I get frequent crashes in media browsers on Orbiter, have unresponsive Orbiter (needs 30+ secs to get from screensaver to Orbiter screen), etc....

When second parameter is >60MB/sec, everything is working much faster and ok...

Now what is slowing my disk down ?
when I do

Code: [Select]

iotop -oP I see that mysqld is producing massive amount of disk writes and reads (I always thought this is pretty normal, till Radu didn't check his machines and we compared...) - approx. 5-10 M/s...

when I check what mysql is doing with :

Code: [Select]

mysql
show full processlist;
I see that there is a massive sql query going on, probably taking all that massive amount of disk accesses...

|   594 | root   | localhost | pluto_media    | Query   |  1623 | Sorting result | 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' )  OR Path LIKE '/home/user_4/data%' OR (Path = '/home/user_4' AND Filename = 'data' )  OR Path LIKE '/home/user_5/data%' OR (Path = '/home/user_5' AND Filename = 'data' )  OR Path LIKE '/home/user_6/data%' OR (Path = '/home/user_6' AND Filename = 'data' )  OR Path LIKE '/home/user_7/data%' OR (Path = '/home/user_7' AND Filename = 'data' )  OR Path LIKE '/home/user_8/data%' OR (Path = '/home/user_8' 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' )  OR Path LIKE '/home/user_4/data%' OR (Path = '/home/user_4' AND Filename = 'data' )  OR Path LIKE '/home/user_5/data%' OR (Path = '/home/user_5' AND Filename = 'data' )  OR Path LIKE '/home/user_6/data%' OR (Path = '/home/user_6' AND Filename = 'data' )  OR Path LIKE '/home/user_7/data%' OR (Path = '/home/user_7' AND Filename = 'data' )  OR Path LIKE '/home/user_8/data%' OR (Path = '/home/user_8' 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' )  OR Path LIKE '/home/user_4/data%' OR (Path = '/home/user_4' AND Filename = 'data' )  OR Path LIKE '/home/user_5/data%' OR (Path = '/home/user_5' AND Filename = 'data' )  OR Path LIKE '/home/user_6/data%' OR (Path = '/home/user_6' AND Filename = 'data' )  OR Path LIKE '/home/user_7/data%' OR (Path = '/home/user_7' AND Filename = 'data' )  OR Path LIKE '/home/user_8/data%' OR (Path = '/home/user_8' 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' )  OR Path LIKE '/home/user_4/data%' OR (Path = '/home/user_4' AND Filename = 'data' )  OR Path LIKE '/home/user_5/data%' OR (Path = '/home/user_5' AND Filename = 'data' )  OR Path LIKE '/home/user_6/data%' OR (Path = '/home/user_6' AND Filename = 'data' )  OR Path LIKE '/home/user_7/data%' OR (Path = '/home/user_7' AND Filename = 'data' )  OR Path LIKE '/home/user_8/data%' OR (Path = '/home/user_8' 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' )  OR Path LIKE '/home/user_4/data%' OR (Path = '/home/user_4' AND Filename = 'data' )  OR Path LIKE '/home/user_5/data%' OR (Path = '/home/user_5' AND Filename = 'data' )  OR Path LIKE '/home/user_6/data%' OR (Path = '/home/user_6' AND Filename = 'data' )  OR Path LIKE '/home/user_7/data%' OR (Path = '/home/user_7' AND Filename = 'data' )  OR Path LIKE '/home/user_8/data%' OR (Path = '/home/user_8' AND Filename = 'data' )  )  AND Missing = 0
GROUP BY PK_File, Path, Filename, INode, AttrDate, AttrCount, ModificationDate |

My first question (dillema) is whether I'm chasing a ghost here and this is pretty normal or is there something to be checked further...

I'm on Dianemo S, but I guess this part of the system is pretty similar to LMCE also...

Any advice would help me a lot. My system is useless when this happens and it happens quite often (or all the time)...

Thanks in advance,

regards,

Bulek.

Edit: I think that Radu found the origin of this query (or similar one when we talked about it) here :
http://svn.linuxmce.org/trac.cgi/browser/trunk/src/UpdateMedia/MediaState.cpp    line 59....

[Marie.O]

Disable UpdateMedia and see if that helps your throughput, ie.

/usr/pluto/bin/UpdateMediaDaemonControl -disable

[tschak909]

Are any of these disks on USB? Or are you using PCI cards to attach the disks? How big is your library?

-Thom

[bulek]

Hi,

both disks are sata. First is system disk, the second is for media (2T)... First one is about half full, second aroung 75%. I doubt I have the biggest media collection. When disk is fast enough (when that huge query is not slowing it down), then Orbiter doesn't crash. It probably gets all data within timeout... But I have a feeling, that all media is retrieved at that moment, although only subset will be shown on screen...

I've disabled UpdateMediaDaemon from command line (Radu just gave me a hint that check in web-admin is broken) and will see if that query will come back again....



Thanks in advance,

regards,

Bulek.
 

[tschak909]

I am currently working with someone to see if we can't optimize that query. This one is going to take a long while to fix... this query is a clusterfuck.

-Thom

[tschak909]

To give you an idea of how crazy of a rabbit hole, this is.. I've spent the last three and a half hours with a friend well versed in SQL. We were able to compact the query, but the results actually wound up being slower than when we started.

I could REALLY use a database designer's help here.

-Thom

[bulek]

Hi,

I'm sorry I cannot help on this one. Thanks for your efforts...

Regards,

Bulek.

[jamo]

To give you an idea of how crazy of a rabbit hole, this is.. I've spent the last three and a half hours with a friend well versed in SQL. We were able to compact the query, but the results actually wound up being slower than when we started.

I could REALLY use a database designer's help here.

-Thom

Thom... did you have any luck there? I ran that query on my DB and I got

3017 rows in set (0.40 sec)

Obviously my media db is pretty modest but that's pretty snappy all the same... . Bulek, how big is yours and how long does it take if you run the query manually from the mysql prompt?

[jamo]

Hmmm... don't have time to check if the data is exactly the same but it is certainly the same number of rows and results in about a 25% speed improvement for me... perhaps someone else can try the following query rewrite to compare with the original:

Code: [Select]

SELECT PK_File, Path, Filename, INode,
  greatest(
    IF(Bookmark.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), Bookmark.psc_mod),
    IF(Attribute.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), Attribute.psc_mod),
    IF(LongAttribute.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), LongAttribute.psc_mod),
    IF(Picture_File.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), Picture_File.psc_mod),
    CAST('0000-00-00 00:00:00' AS DATE)
  ) As CurrentDbAttrDate,
  (1000000 * COUNT(Bookmark.PK_Bookmark) + 10000 * COUNT(Attribute.PK_Attribute) +
   100 * COUNT(LongAttribute.PK_LongAttribute) + COUNT(Picture_File.FK_Picture))
  AS CurrentDbAttrCount,
  COUNT(Attribute.PK_Attribute)+COUNT(LongAttribute.PK_LongAttribute) AS HasAttributes,
  AttrDate AS OldDbAttrDate, AttrCount AS OldDbAttrCount, ModificationDate AS OldFileDate,
  Source
FROM
File
LEFT JOIN Bookmark ON Bookmark.FK_File = PK_File
LEFT JOIN File_Attribute ON File_Attribute.FK_File = PK_File
LEFT JOIN Attribute ON File_Attribute.FK_Attribute = PK_Attribute
LEFT JOIN LongAttribute ON LongAttribute.FK_File = PK_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' )  OR Path LIKE '/home/user_4/data%' OR (Path = '/home/user_4' AND Filename = 'data' )  OR Path LIKE '/home/user_5/data%' OR (Path = '/home/user_5' AND Filename = 'data' )  OR Path LIKE '/home/user_6/data%' OR (Path = '/home/user_6' AND Filename = 'data' )  OR Path LIKE '/home/user_7/data%' OR (Path = '/home/user_7' AND Filename = 'data' )  OR Path LIKE '/home/user_8/data%' OR (Path = '/home/user_8' AND Filename = 'data' )  )  AND Missing = 0
GROUP BY PK_File, Path, Filename, INode, AttrDate, AttrCount, ModificationDate;


[Marie.O]

Could paste the output of the following commands:

select count(*) from Picture_File;

select count(*) from File;

select count(*) from File_Attribute;

select count(*) from LongAttribute;


EDIT: My results:

Code: [Select]

mysql> select count(*) from Picture_File;
+----------+
|     4285 |
+----------+
mysql> select count(*) from File;
+----------+
|    16715 |
+----------+
mysql> select count(*) from File_Attribute;
+----------+
|    35065 |
+----------+
mysql> select count(*) from LongAttribute;
+----------+
|     1043 |
+----------+


[tschak909]

That is the very same query that my SQL friend and I came up with, last night. I suddenly went from 9 seconds, to 14.5 seconds. So for me, it's slower.

-Thom

[tschak909]

I ran an explain select on jamo's query:

Code: [Select]

+----+-------------+----------------+--------+-----------------------------+---------+---------+-----------------------------------------+-------+----------------------------------------------+
| id | select_type | table          | type   | possible_keys               | key     | key_len | ref                                     | rows  | Extra                                        |
+----+-------------+----------------+--------+-----------------------------+---------+---------+-----------------------------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | File           | ALL    | Path_Filename,Path,Filename | NULL    | NULL    | NULL                                    | 21015 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | Bookmark       | ALL    | NULL                        | NULL    | NULL    | NULL                                    |   387 |                                              |
|  1 | SIMPLE      | File_Attribute | ref    | PRIMARY,FK_File             | FK_File | 4       | pluto_media.File.PK_File                |     2 | Using index                                  |
|  1 | SIMPLE      | Attribute      | eq_ref | PRIMARY                     | PRIMARY | 4       | pluto_media.File_Attribute.FK_Attribute |     1 |                                              |
|  1 | SIMPLE      | LongAttribute  | ALL    | NULL                        | NULL    | NULL    | NULL                                    |   262 |                                              |
|  1 | SIMPLE      | Picture_File   | ref    | FK_File                     | FK_File | 4       | pluto_media.File.PK_File                |     1 |                                              |
+----+-------------+----------------+--------+-----------------------------+---------+---------+-----------------------------------------+-------+----------------------------------------------+

The filesort basically shows that for the bulk of our query, we are HAMMERING the filesystem...

-Thom

[jamo]

Code: [Select]

mysql> select count(*) from Picture_File;
+----------+
| count(*) |
+----------+
|      154 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from File;
+----------+
| count(*) |
+----------+
|     4333 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from File_Attribute;
+----------+
| count(*) |
+----------+
|    11794 |
+----------+
1 row in set (0.01 sec)

mysql> select count(*) from LongAttribute;
+----------+
| count(*) |
+----------+
|       22 |
+----------+
1 row in set (0.00 sec)

Yes, you have a lot more media than I do (and more pictures!)

[jamo]

Thom

Did some research on that explain plan you posted and I think the problem was the group-by clause that groups by a bunch of dependent fields that aren't indexed. If we group on the PK of File and then join back to file to get the fields we need it seems to take care of that. In fact, it improves my query by quite a dramatic amount. Does anyone else want to try this?

Code: [Select]

SELECT File.PK_File, File.Path, File.Filename, File.Inode,
  s.CurrentDbAttrDate, s.CurrentDbAttrCount, s.HasAttributes,
  File.AttrDate AS OldDbAttrDate, File.AttrCount AS OldDbAttrCount,
  File.ModificationDate AS OldFileDate, File.Source
FROM
(SELECT PK_File,
  greatest(
    IF(Bookmark.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), Bookmark.psc_mod),
    IF(Attribute.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), Attribute.psc_mod),
    IF(LongAttribute.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), LongAttribute.psc_mod),
    IF(Picture_File.psc_mod IS NULL,CAST('0000-00-00 00:00:00' AS DATE), Picture_File.psc_mod),
    CAST('0000-00-00 00:00:00' AS DATE)
  ) As CurrentDbAttrDate,
  (1000000 * COUNT(Bookmark.PK_Bookmark) + 10000 * COUNT(Attribute.PK_Attribute) +
   100 * COUNT(LongAttribute.PK_LongAttribute) + COUNT(Picture_File.FK_Picture))
  AS CurrentDbAttrCount,
  COUNT(Attribute.PK_Attribute)+COUNT(LongAttribute.PK_LongAttribute) AS HasAttributes
FROM
File
LEFT JOIN Bookmark ON Bookmark.FK_File = PK_File
LEFT JOIN File_Attribute ON File_Attribute.FK_File = PK_File
LEFT JOIN Attribute ON File_Attribute.FK_Attribute = PK_Attribute
LEFT JOIN LongAttribute ON LongAttribute.FK_File = PK_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' )  OR Path LIKE '/home/user_4/data%' OR (Path = '/home/user_4' AND Filename = 'data' )  OR Path LIKE '/home/user_5/data%' OR (Path = '/home/user_5' AND Filename = 'data' )  OR Path LIKE '/home/user_6/data%' OR (Path = '/home/user_6' AND Filename = 'data' )  OR Path LIKE '/home/user_7/data%' OR (Path = '/home/user_7' AND Filename = 'data' )  OR Path LIKE '/home/user_8/data%' OR (Path = '/home/user_8' AND Filename = 'data' )  )  AND Missing = 0
GROUP BY PK_File) s
inner join File on s.PK_File=File.PK_File;


[tschak909]

Shaved off about 4 seconds, to 11.90 seconds:

Code: [Select]

+----+-------------+----------------+--------+-----------------------------+---------+---------+-----------------------------------------+-------+----------------------------------------------+
| id | select_type | table          | type   | possible_keys               | key     | key_len | ref                                     | rows  | Extra                                        |
+----+-------------+----------------+--------+-----------------------------+---------+---------+-----------------------------------------+-------+----------------------------------------------+
|  1 | PRIMARY     | <derived2>     | ALL    | NULL                        | NULL    | NULL    | NULL                                    | 20292 |                                              |
|  1 | PRIMARY     | File           | eq_ref | PRIMARY                     | PRIMARY | 4       | s.PK_File                               |     1 |                                              |
|  2 | DERIVED     | File           | ALL    | Path_Filename,Path,Filename | NULL    | NULL    | NULL                                    | 21015 | Using where; Using temporary; Using filesort |
|  2 | DERIVED     | Bookmark       | ALL    | NULL                        | NULL    | NULL    | NULL                                    |   386 |                                              |
|  2 | DERIVED     | File_Attribute | ref    | PRIMARY,FK_File             | FK_File | 4       | pluto_media.File.PK_File                |     2 | Using index                                  |
|  2 | DERIVED     | Attribute      | eq_ref | PRIMARY                     | PRIMARY | 4       | pluto_media.File_Attribute.FK_Attribute |     1 |                                              |
|  2 | DERIVED     | LongAttribute  | ALL    | NULL                        | NULL    | NULL    | NULL                                    |   262 |                                              |
|  2 | DERIVED     | Picture_File   | ref    | FK_File                     | FK_File | 4       | pluto_media.File.PK_File                |     1 |                                              |
+----+-------------+----------------+--------+-----------------------------+---------+---------+-----------------------------------------+-------+----------------------------------------------+
8 rows in set (12.18 sec)