MG 2.1 XFMG\Cron\generateRandomAlbumCache::generateRandomAlbumCache() takes 45 seconds

[nocte]

see title.

The forum has a quite big gallery. Is there a way to optimize the cache rebuild?

I saw that just by chance. Did not check the random media cache rebuild yet.

Or maybe introduce a setting, that turns the the cache rebuild off? I assume that cache is only used for a widget.

Here's the code:

    public function generateRandomAlbumCache()
    {
        $limit = 5;
        $iterations = 100;

        $maxId = (int)$this->db()->fetchOne('SELECT MAX(album_id) FROM xf_mg_album');

        $albumIds = [];
        while ($iterations > 0)
        {
            $iterations--;

            $gt = mt_rand(0, max(0, $maxId - $limit));

            $albumIds = array_merge($albumIds, $this->db()->fetchAllColumn('
                SELECT album_id
                FROM xf_mg_album
                WHERE album_id > ?
                LIMIT ?
            ', [$gt, $limit]));
        }

        return array_unique($albumIds);
    }

    public function getUserAlbumCount($userId)
    {
        return $this->db()->fetchOne("
            SELECT COUNT(*)
            FROM xf_mg_album
            WHERE user_id = ?
                AND album_state = 'visible'
        ", $userId);
    }

 

[Chris D]

This really should not be taking 45 seconds on any media gallery of any size. How sure are you that it is this taking time?

It runs this query:

SELECT album_id
FROM xf_mg_album
WHERE album_id > 10
LIMIT 5

It does run it 100 times, but the query is just selecting 5 values from a single column and the column happens to be the primary key of the table. I'd really not expect it to take much longer than around 10 seconds to perform the query 100 times. The media version of this is the same.

If you run that query manually on its own, how long does it take on your database?

 

[nocte]

This really should not be taking 45 seconds on any media gallery of any size.

Yeah, was curious too why this can take so long. It was my fault reading endless logs.

This is the one that really takes long:

[2020-10-20 06:12:40] Hampel\JobRunner\XF\Job\Cron: Cron entry [xfmgCacheStats] executed in 41.75 seconds {} {}

Method: XFMG\Cron\Statistics::cacheGalleryStatistics():

    public static function cacheGalleryStatistics()
    {
        $cache = \XF::app()->simpleCache()->XFMG;
        $db = \XF::db();

        $categoryCount = $db->fetchOne('
            SELECT COUNT(*)
            FROM xf_mg_category
        ');

        $albumCount = $db->fetchOne('
            SELECT COUNT(*)
            FROM xf_mg_album 
            WHERE album_state = \'visible\''
        );

        $uploadCount = $db->fetchOne('
            SELECT COUNT(*)
            FROM xf_mg_media_item AS mi
            LEFT JOIN xf_mg_album AS a ON
                (mi.album_id = a.album_id)
            WHERE mi.media_state = \'visible\'
            AND mi.media_type IN(\'audio\', \'image\', \'video\')
            AND IF(mi.album_id > 0, a.album_state = \'visible\', 1=1)
        ');

        $embedCount = $db->fetchOne('
            SELECT COUNT(*)
            FROM xf_mg_media_item AS mi
            LEFT JOIN xf_mg_album AS a ON
                (mi.album_id = a.album_id)
            WHERE mi.media_state = \'visible\'
            AND mi.media_type IN(\'embed\')
            AND IF(mi.album_id > 0, a.album_state = \'visible\', 1=1)
        ');

        $commentCount = $db->fetchOne('
            SELECT COUNT(*)
            FROM xf_mg_comment AS c
            LEFT JOIN xf_mg_media_item AS mi ON
                (c.content_type = \'xfmg_media\' AND c.content_id = mi.media_id)
            LEFT JOIN xf_mg_album AS a ON
                (c.content_type = \'xfmg_album\' AND c.content_id = a.album_id)
            WHERE c.comment_state = \'visible\'
            AND IF(mi.media_id > 0, mi.media_state = \'visible\', 1=1)
            AND IF(a.album_id > 0, a.album_state = \'visible\', 1=1)
        ');

        $diskUsage = $db->fetchOne('
            SELECT SUM(attd.file_size)
            FROM xf_attachment_data AS attd
            INNER JOIN xf_attachment AS att ON
                (attd.data_id = att.data_id)
            LEFT JOIN xf_mg_media_item AS mi ON
                (att.content_type = \'xfmg_media\' AND att.content_id = mi.media_id)
            LEFT JOIN xf_mg_album AS a ON
                (mi.album_id = a.album_id)
            WHERE att.content_type = \'xfmg_media\'
            AND mi.media_state = \'visible\'
            AND IF(a.album_id > 0, a.album_state = \'visible\', 1=1)
        ');

        $cache->statisticsCache = [
            'category_count' => $categoryCount,
            'album_count' => $albumCount,

            'upload_count' => $uploadCount,
            'embed_count' => $embedCount,

            'comment_count' => $commentCount,

            'disk_usage' => $diskUsage
        ];
    }

 

[Chris D]

That one does make some more sense

I'll move this to bugs for now. I haven't yet looked in great detail as to what we can do to fix it though we should probably try to narrow it down.

I suspect it's probably this one:

SELECT SUM(attd.file_size)
FROM xf_attachment_data AS attd
INNER JOIN xf_attachment AS att ON
    (attd.data_id = att.data_id)
LEFT JOIN xf_mg_media_item AS mi ON
    (att.content_type = 'xfmg_media' AND att.content_id = mi.media_id)
LEFT JOIN xf_mg_album AS a ON
    (mi.album_id = a.album_id)
WHERE att.content_type = 'xfmg_media'
    AND mi.media_state = 'visible'
    AND IF(a.album_id > 0, a.album_state = 'visible', 1=1)

Would you mind running that manually and telling us how long it takes?

 

[nocte]

Would you mind running that manually and telling us how long it takes?

sure. it takes not too long compared to the whole job: 1 row in set (7.34 sec).

 

[Chris D]

Would you mind running each of the queries to see how long each one takes? Maybe it’s one of the others, or maybe the whole set just take a while cumulatively.

 

[nocte]

hmm.. when I run the queries separately I get these run times:

mysql> SELECT COUNT(*)
    -> FROM xf_mg_media_item AS mi
    -> LEFT JOIN xf_mg_album AS a ON
    -> (mi.album_id = a.album_id)
    -> WHERE mi.media_state = 'visible'
    -> AND mi.media_type IN('audio', 'image', 'video')
    -> AND IF(mi.album_id > 0, a.album_state = 'visible', 1=1);

1 row in set (1.79 sec)


mysql> SELECT COUNT(*)
    -> FROM xf_mg_media_item AS mi
    -> LEFT JOIN xf_mg_album AS a ON
    -> (mi.album_id = a.album_id)
    -> WHERE mi.media_state = 'visible'
    -> AND mi.media_type IN('embed')
    -> AND IF(mi.album_id > 0, a.album_state = 'visible', 1=1);

1 row in set (0.52 sec)


mysql> SELECT COUNT(*)
    -> FROM xf_mg_media_item AS mi
    -> LEFT JOIN xf_mg_album AS a ON
    -> (mi.album_id = a.album_id)
    -> WHERE mi.media_state = 'visible'
    -> AND mi.media_type IN('embed')
    -> AND IF(mi.album_id > 0, a.album_state = 'visible', 1=1);

1 row in set (0.51 sec)


mysql> SELECT SUM(attd.file_size) FROM xf_attachment_data AS attd INNER JOIN xf_attachment AS att ON (attd.data_id = att.data_id) LEFT JOIN xf_mg_media_item AS mi ON (att.content_type = 'xfmg_media' AND att.content_id = mi.media_id) LEFT JOIN xf_mg_album AS a ON (mi.album_id = a.album_id) WHERE att.content_type = 'xfmg_media' AND mi.media_state = 'visible' AND IF(a.album_id > 0, a.album_state = 'visible', 1=1);

1 row in set (6.48 sec)