When we create or modify an existing layout within the Xibo web interface, we notice that on the (colocated) MySQL server (5.7.17) the CPU usage spikes. This is due to a single query from Xibo which is stuck on ‘Sending data’ (according to MySQL).
The following query is executed:
SELECT media.mediaID, media.name, media.type, media.duration, media.userID, media.FileSize, media.storedAs, media.valid, media.moduleSystemFile, media.expires, IFNULL((SELECT parentmedia.mediaid FROM media parentmedia WHERE parentmedia.editedmediaid = media.mediaid),0) AS ParentID, (SELECT GROUP_CONCAT(DISTINCT tag) FROM tag INNER JOIN lktagmedia ON lktagmedia.tagId = tag.tagId WHERE lktagmedia.mediaId = media.mediaID GROUP BY lktagmedia.mediaId) AS tags, (SELECT GROUP_CONCAT(DISTINCTgroup.Group) FROMgroupINNER JOIN lkmediagroup ONgroup.GroupID = lkmediagroup.GroupID WHERE lkmediagroup.MediaID = media.mediaID GROUP BY lkmediagroup.mediaId ) AS groups, media.originalFileName FROM media LEFT OUTER JOIN media parentmedia ON parentmedia.MediaID = media.MediaID WHERE media.isEdited = 0 AND media.expires < '1486556447' AND IFNULL(media.expires, 0) <> 0
This happens (in our case) when you select ‘design’ for an existing template or create a new one. No further actions are required. When the page is simply showing the blank/gray layout this query is launced every minute and keeps coming back until the server is clogged and stops responding. So after 4 or 5 outstanding queries the server ‘collapses’. All of these query are showing up as ‘Sending data’.
Our MySQL server is upgraded from 5.1 to 5.7.17 which makes no difference. This problem was also showing in Xibo 1.7.4. Our current Xibo version is 1.7.9 but the problem persists
OK, so you have 18452 media files which have been downloaded by layouts - most likely active content like MediaRSS or Twitter. When the CMS tries to clear those up - it understandably dies trying to load them all into its object model ready for deletion.
Are you running the maintenance routine? This should have been routinely cleaning up these files so that there isn’t such a large backlog to deal with.
You can probably get yourself out of a hole with the following statements:
DELETE FROM `lklayoutmedia` WHERE mediaId IN (SELECT mediaId FROM `media` WHERE IFNULL(media.expires, 0) <> 0 AND media.expires < '1486556447')
DELETE FROM `media` WHERE IFNULL(media.expires, 0) <> 0 AND media.expires < '1486556447'
Please take a backup before running these statements - just in case.
Hi Dan. Great, so this did indeed solve our little problem. While we have activated the maintenance routine it was not configured as a scheduled task on the server. Thank you very much for the support!