No Support > Scheduled for cpg1.5.x
Optimize requests of CPG for next versions ? ...
Rodinou:
My server crashes all the days because Coppermine seems to have some limits when they are a lot of people with a lot of galleries ...
with 5000 visits days since 1 week, and about 200 000 pages / day ... we have seen it
--- Quote ---SELECT COUNT(*) from cpg11d_comments, cpg11d_pictures WHERE approved = 'YES' AND cpg11d_comments.pid = cpg11d_pictures.pid A
ND aid NOT IN (20,691,93,195,215,343,368,383,538,443,466,483,486,493,494,495,525,540,567,610,622,638,659,664,753,761,774,782,7
92,821,894,947,982,993,1033,1055,1075,1081,1100,1150,1164,1223);
# Time: 050309 15:35:05
# User@Host: rouenonline[rouenonline] @ localhost []
# Query_time: 153 Lock_time: 147 Rows_sent: 1 Rows_examined: 59029
SELECT * FROM cpg11d_pictures WHERE approved = 'YES'AND hits > 0 AND aid NOT IN (20,691,93,195,215,343,368,383,538,443,466,483
,486,493,494,495,525,540,567,610,622,638,659,664,753,761,774,782,792,821,894,947,982,993,1033,1055,1075,1081,1100,1150,1164,12
23) ORDER BY hits DESC, filename LIMIT 78 ,1;
# User@Host: rouenonline[rouenonline] @ localhost []
# Query_time: 50 Lock_time: 44 Rows_sent: 1 Rows_examined: 58997
SELECT * FROM cpg11d_pictures WHERE approved = 'YES'AND hits > 0 AND aid NOT IN (20,691,93,195,215,343,368,383,538,443,466,483
,486,493,494,495,525,540,567,610,622,638,659,664,753,761,774,782,792,821,894,947,982,993,1033,1055,1075,1081,1100,1150,1164,12
23) ORDER BY hits DESC, filename LIMIT 46 ,1;
# User@Host: rouenonline[rouenonline] @ localhost []
# Query_time: 46 Lock_time: 40 Rows_sent: 1 Rows_examined: 58997
SELECT * FROM cpg11d_pictures WHERE approved = 'YES'AND hits > 0 AND aid NOT IN (20,691,93,195,215,343,368,383,538,443,466,483
,486,493,494,495,525,540,567,610,622,638,659,664,753,761,774,782,792,821,894,947,982,993,1033,1055,1075,1081,1100,1150,1164,12
23) ORDER BY hits DESC, filename LIMIT 46 ,1;
# User@Host: rouenonline[rouenonline] @ localhost []
# Query_time: 60 Lock_time: 54 Rows_sent: 1 Rows_examined: 59086
SELECT * FROM cpg11d_pictures WHERE approved = 'YES'AND hits > 0 AND aid NOT IN (20,691,93,195,215,343,368,383,538,443,466,483
,486,493,494,495,525,540,567,610,622,638,659,664,753,761,774,782,792,821,894,947,982,993,1033,1055,1075,1081,1100,1150,1164,12
23) ORDER BY hits DESC, filename LIMIT 135 ,1;
# User@Host: rouenonline[rouenonline] @ localhost []
# Query_time: 46 Lock_time: 40 Rows_sent: 1 Rows_examined: 58997
SELECT * FROM cpg11d_pictures WHERE approved = 'YES'AND hits > 0 AND aid NOT IN (20,691,93,195,215,343,368,383,538,443,466,483
,486,493,494,495,525,540,567,610,622,638,659,664,753,761,774,782,792,821,894,947,982,993,1033,1055,1075,1081,1100,1150,1164,12
23) ORDER BY hits DESC, filename LIMIT 46 ,1;
# User@Host: rouenonline[rouenonline] @ localhost []
# Query_time: 46 Lock_time: 40 Rows_sent: 1 Rows_examined: 58997
SELECT * FROM cpg11d_pictures WHERE approved = 'YES'AND hits > 0 AND aid NOT IN (20,691,93,195,215,343,368,383,538,443,466,483
,486,493,494,495,525,540,567,610,622,638,659,664,753,761,774,782,792,821,894,947,982,993,1033,1055,1075,1081,1100,1150,1164,12
23) ORDER BY hits DESC, filename LIMIT 46 ,1;
# User@Host: rouenonline[rouenonline] @ localhost []
# Query_time: 81 Lock_time: 75 Rows_sent: 1 Rows_examined: 59086
SELECT * FROM cpg11d_pictures WHERE approved = 'YES'AND hits > 0 AND aid NOT IN (20,691,93,195,215,343,368,383,538,443,466,483
,486,493,494,495,525,540,567,610,622,638,659,664,753,761,774,782,792,821,894,947,982,993,1033,1055,1075,1081,1100,1150,1164,12
23) ORDER BY hits DESC, filename LIMIT 135 ,1;
--- End quote ---
It's quite incredible !!! for each request, Coppermine looks all the counts !!! Is it plan to "change" this ? Or Coppermine is "perfect" for "little galleries with few traffic" ? ...
Nibbler:
I will look into caching/db performance/unnecessary queries etc for 1.5 when I have time.
Tarique Sani:
We have already done a lot of optimization of queries where * was used and count(*) was used for CPG NG, what really kills the MySQL is the NOT IN clause with long list of aids.... As of now I do not know how to avoid that.
omniscientdeveloper:
I think, like Nibbler said, we could use some sort of caching. Even if we cached values for 5 minutes (or some admin setting), that would improve the speed a lot. A button on the admin page could erase the caches. I also think that a default cache of 5 minutes is small enough to prevent the server from getting cluttered with cache files, unless the site constantly has a high level of users online at one time.
Tarique Sani:
We can use a cache BUT lets start with discussing what do we cache? The most intensive page is index.php and that is cacheable only for non loggedin users - will that help?
Navigation
[0] Message Index
[#] Next page
Go to full version