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 :
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
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 :
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....