Slow mysql queries

[npetrov]

Hello all,
I have a gallery with about 60K images. Sometimes there are too many simultaneous slow queries that cause a high load on a server and i have to kill this queries or to stop these queries anyway (restart apache or mysql).
Earlier there was issues with a slow queries with some keywords that i have fix regarding one similar post in this forum.

Here are one slow query from last night:
# Time: 140421  3:56:58
# User@Host: tpetrov_gallery[tpetrov_gallery] @ localhost []
# Query_time: 6.434808  Lock_time: 0.000172 Rows_sent: 1  Rows_examined: 263764
use tpetrov_gallery;
SET timestamp=1398041818;
SELECT r.pid, r.aid, filepath, filename, url_prefix, pwidth, pheight, filesize, ctime, r.title, r.keywords, r.votes, pic_rating, hits, caption, r.owner_id, UNIX_TIMESTAMP(msg_date) AS msg_date, msg_body, author_id, msg_author, msg_id
                FROM cpg_comments AS c
                INNER JOIN cpg_pictures AS r ON r.pid = c.pid
                INNER JOIN cpg_albums AS a ON a.aid = r.aid
                INNER JOIN cpg_categories AS c2 ON c2.cid = category WHERE (c2.lft BETWEEN 3 AND 4)
                AND r.approved = 'YES'
                AND c.approval = 'YES'
                ORDER BY msg_id DESC;

<....>
107325 rows in set (3.43 sec)
--------------------------------------------------------

Here are another slow query that cause high load sometimes (this is old, i'm not sure that at this point this cause any issues) :
SELECT COUNT(*)
                FROM cpg_comments AS c
                INNER JOIN cpg_pictures AS r ON r.pid = c.pid
                INNER JOIN cpg_albums AS a ON a.aid = r.aid
                WHERE (1)
                AND r.approved = 'YES'
                AND c.approval = 'YES';



+----------+
| COUNT(*) |
+----------+
|   111797 |
+----------+
1 row in set (0.71 sec)


From the logging settings only "Count file views" is enabled.
All tables are using myisam.
Did someone have similar issues? Any recommendations?

Info:
site: http://gallery.pigeons-bg.com/
ver: 1.5.28 (stable)
test user/pass: testuser/testp@ss!

Thank you.

[Αndré]

Those queries are most likely generated by the meta album 'lastcom'. The queries themselves cannot be optimized, so you either have to upgrade your server or disable that meta albums.

[npetrov]

Those queries are most likely generated by the meta album 'lastcom'. The queries themselves cannot be optimized, so you either have to upgrade your server or disable that meta albums.

Thank you for your answer.

I'm not sure that upgrade will help, because there is no difference if the VPS have 2 or 4GB RAM and 2 or 4 CPU cores, i've already try with more resources on two different hardware nodes - one with XEON CPU (as i remember 8 cores - 4 for the VPS) 4GB DDR3 ECC RAM and another normal PC with 7GB DDR2 RAM, AMD Athlon 64 CPU (4GB for the VPS, only one VPS on the node) and really there is no difference. MySQL is also tuned and have enough resources and also on this VPS there are about 10 websites with 10 databases that works without any issues. Virtualization is OpenVZ. I could post some mysql checks/tests if you want to.

About disabling the meta album, could you please explain what will this cause to the functionality of the gallery? How this could be implemented?

Maybe i will try to create mysql load balancer with second mysql server, but i don't think this will help much, because when there are many simultaneous slow queries hardware node also gets a high load until i kill queries or restart apache/mysql....so two mysql vpses on the same hardware node will be pointless...

As last thing maybe i would try to migrate the gallery to XEN on another server, because XEN isolates resources more better than OpenVZ...

Thank you again.

[Αndré]

About disabling the meta album, could you please explain what will this cause to the functionality of the gallery? How this could be implemented?

I just had a closer look at the 'lastcom' meta album and I'm quite sure the queries belong to it:

Code (include/functions.inc.php) Select Expand

        $select_columns = implode(', ', $select_column_list);

        $query = "SELECT $select_columns
                FROM {$CONFIG['TABLE_COMMENTS']} AS c
                INNER JOIN {$CONFIG['TABLE_PICTURES']} AS r ON r.pid = c.pid
                INNER JOIN {$CONFIG['TABLE_ALBUMS']} AS a ON a.aid = r.aid
                $RESTRICTEDWHERE
                AND r.approved = 'YES'
                AND c.approval = 'YES'
                ORDER BY msg_id $DESC
                $limit";

To "disable" the meta album, you could start by removing the link which points to http://gallery.pigeons-bg.com/thumbnails.php?album=lastcom from the menu (e.g. with the plugin "FinalExtract" or via the theme engine). This of course just hides the meta album, so if you want to disable it completely, you need to modify the Coppermine code or add a plugin which disables that meta album (I haven't tested the latter one and such a plugin doesn't exist yet, but it should be possible and quite easy to create). If you need help to disable that meta album completely, let me know.

[npetrov]

Thank you for information.

For the last 5 days there are only two slow queries... I don't want to disable last comments functionality. I think that instead of disabling anything or moving VPS on XEN i can put two 30GB SSD in raid1 for mysql and move /var on it. That must fix the problem. I will try and write back.

Thank you again for useful information.