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.