Optimize requests of CPG for next versions ? ...

[Rodinou]

gaugau : NO !!!

dhtml reduces queries because to call a pic, you don't need to call a whole interface, interface which calls A LOT OF QUERIES !!!

Simply ... but I know, one day, nolt33 and some developers realize a dhtml interface (I'll pay them !!!) and you'll see with dhtml ...

example : the excellent mod CSS / TREE > to see the structure of coppermine : ONLY ONE PAGE, against several in "normal" configuration = a lot of queries not realized

but ... it's my vision and we are ok ... we are not ok !!

[Tarique Sani]

@tarique: His post actually was applicable to Coppermine. We're not using any temp tables and not many subqueries, but if you combine queries, it'd form what he showed. As you said, the index and thumbnails pages are the biggest hoggers. Anyway, I still need to take some time and see what you guys have done with NG. I'm sure some of these issues will be void.

@Omni - we cannot use subqueries till 4.1 is popular enough, also because I have done enough head banging with CPG queries I can tell temp tables will not solve the NOT IN issue

Something like SELECT blah FROM albums WHERE album.aid NOT IN (1,2,3,4) is faster than SELECT blah FROM albums WHERE album.aid NOT IN ( SELECT aid FROM temp_table ) besides we have the over head of creating a temp table with call to every script

Code Select Expand


forbidden_set = list( 4, 7, 9, 12) //list recovered by sql also
if ( forbidden_set.contains( mysql_result[$i.['aid']] )
then
  skip //NOT IN replacement
else
  process it


This cannot be used for places where we are counting stats as it will make the process unduly more complex - we will need to keep track of counts in output loops, again this will not work for most meta-albums either

So like I said the suggestions offered are very generic - or should I say candid

One thing which we have achieved in NG is that there never will be a "aid IN" and "aid NOT IN" clause in the same query, we are also on our way to eliminate the LIKE %keyword% stuff

[omniscientdeveloper]

Yea. I was also checking out the pre-ordered trees that Mambo uses for its groups. I know it'll solve part of these issues, but it creates another when you have to add/delete from the tree. I haven't done A LOT of testing on this yet, but I think it'll help with things that don't change much, like groups....

I think we'll end up using various methods, including cache files.

[Rodinou]

I come back to you because my host wants to stop to host me if there is no solution with coppermine. Or to me to find another solution than Coppermine.
The server is now PIV 3ghz, and 1go RAM but now, I have 4500 visits/day and 230 000 view pages/day.

The server becomes very slow with Coppermine.

I think "video", "flash", or some gadgets are not the priority : or if there is no possible solution, explain on the manual Coppermine is not done for big traffic. I'm very sad, I think the soft very perfect, but between not to have a host, or continue to use a soft which crashes a server, my choice is done.

So is it possible to generate a "STATIC" index.php when all updates are done ?! We go to admin, push a button "generate static index" ... OK the numbers of comments will be not synchronized untile the next "generation" but ... the server will say : "THANX MAN"

Or if it's impossible, it's to me to copy / paste the whole code of index.php and create a html index ...

[Joachim Müller]

It's not a good idea to request this on a thread that is clearly labelled with "Feature scheduled for cpg1.5" (this is what I would call "thread hijacking"). There are lot's of postings how to remove the stats from the index page and speed it up by reducing the number of queries there, I recommend searching the board. If coppermine doesn't suit your needs, I'm sorry for this - with the amount of traffic you have, I suggest you review the whole idea of shared/virtual hosting, it may be time for you to consider a dedicated server, as you will run into similar issues with other gallery apps...

Joachim

[Rodinou]

It's already a dedicated server !!!!
There are only 8 sites on this server
- sortons.net
- fantasya.net
- druuna.net
- akendengue.com
- and other I don't know

So my host said me this (and I know where is the problem, and it's a incredible problem)

(french)

Ben si c'est coppermine qui bouffe avec des requêtes super mal optimisées ...
en fait quand tu passes de photo en photo, il fait une requete qui analyse à chaque fois pres de 200 000 résultats !!!
tu multiplies ça par le nombre de visiteurs que tu as et le fait qu'il regarde au moins 10 à 20 photos mini ...

Approximative translation ...

when you see a photo to another photo, CPG does a request which analyzes about 200 000 results !!!
You add this with the number of visitors and they look mini 10 to 20 photos ...

So it's more than simple stats to see again : All photos by their position (pos=-38494) does many many requests. 1.5 ok ... but 1.4 is not OK yet

[Joachim Müller]

like I said: remove the stats, as they use the mySQl comand "count", which is critical.

Joachim

[Rodinou]

I have spent 2 hours on the search board to find my solution

"sql queries"

"less queries"

"optimize queries"

but nothing found to my problem ... if someone could say me WHERE I can find "a" solution ...

[Tarique Sani]

Somebody should seriously think of forking cpg for a High Performance version - cpgHP!!  making a HP version is not just about optimizing queries. I had hoped the cpgNG would the real answer and but again it is now a battle of Performance Vs. Features Vs. Backwards compatibility. For the masses the later two specially the backward compatibility wins over increased Performance.

So someone who really has the inclination can take the lessons from the Flickr guys and implement a HP version (for those who do not know Flickr uses SMARTY templates stored in MySQL databases)

@Rod - your site fantasya.net fails in Firefox - the JS menu just does not work

[Rodinou]

a battle of Performance Vs. Features Vs. Backwards compatibility

Exactly that.

But Understand that Coppermine becomes popular ... like "big sites" like me rather Coppermine to code themselves a soft, and this problem of performance, you'll have it very often : because fantasya.net will be in coppermine mode (wait 1.4) and fantasya did 20 000 visits/day 1 year ago ...

I'm surprised it's fails on FF (I'm using it, and it works well) : which version do you have ?

I think it's time to do "2 coppermines" : people who wants gadgets, and people who want an impressive soft, but light : a PIV 3ghZ and 1go RAM is not enough : it's incredible !

Another thing : if today I have choosen PunBB (I was before on phpBB), it's for it's high level server performances. Less 8 queries per page, and the developper, Rickard, estimates it's "too" ... no gadget, but one of the most powerful and easy forums I have ever seen.

I say you too developping CPG 100% tableless is 100% backward compatible (not very beautiful layout, it's sure), but performance are better (less code to generate, page very light).

[Tarique Sani]

I am using FF 1.0.2  - BTW how much bandwidth are you using daily at fantasya? If I ever decide to do a HP version would you be willing to allow access to MySQL and CPU load data etc?

[Rodinou]

I am using FF 1.0.2  - BTW how much bandwidth are you using daily at fantasya? If I ever decide to do a HP version would you be willing to allow access to MySQL and CPU load data etc?

Sure : I'm ready.

When I'll know what's HP High Performances ?!! (Harry Potter ?)

For fantasya ... I'm waiting future versions of CPG (too many things are not ok with my "vision" of art communauty : specially the ability to CPG to put an album of a user inside a category but the user stays mod of this gallery).

But for Sortons.Net I can answer you
- 2 mb/s of BW
- 4000 visits/day
- 233 348 viewed pages yesterday.

As you can see, it's quite big. But when Fantasya will be up, all these numbers will be x2 or 3 !!! ... or today the server has crashed 10 times since 00h ....

[Tarique Sani]

Ummm I wrote above HP will stand for High Performance - will get in touch with you when / if we decide to do a HP version

[Rodinou]

But I'm ready !!!

We see with nol33t too a dhtml light version

http://www.sortons.net/nol33t/menu.htm

clic on ....
rouen and agglo (on the         +         ), after photos cinema, after "rencontre avec equipe brice de nice"

(of course when my server will be ok, CPG doesn't stop to crash the server, I have to stop TODAY this soft)

you'll see another vision to surf with CPG. But I'm ready to work with you about all I know about CSS/XHTML/DHTML and you could talk about it if you have msn (mine is   sortons.net@wanadoo.fr)

I think "big sites" will thank you. I'll never use CPG for gadgets, but only pics. yesterday I have spent all my night to see 20 pages on Hotscripts : CPG is the best, really. But can be better.

[Rodinou]

my host today has not permit access on my whole site ... here are the requests

Query_time: 12  Lock_time: 0  Rows_sent: 1  Rows_examined: 63235
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 141 ,1;
# Time: 050402  7:43:08
# User@Host: rouenonline[rouenonline] @ localhost []
# Query_time: 5  Lock_time: 0  Rows_sent: 1  Rows_examined: 63123
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 19 ,1;
# Time: 050402 13:27:17
# User@Host: rouenonline[rouenonline] @ localhost []
# Query_time: 5  Lock_time: 0  Rows_sent: 20  Rows_examined: 124829
SELECT pid, filepath, filename, url_prefix, filesize, pwidth, pheight, ctime, aid, hits, aid, filename FROM cpg11d_pictures WH
ERE 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,1223)   ORDER BY hits DESC, filena
me   LIMIT 61700 ,20;
# Time: 050402 13:27:18
# User@Host: rouenonline[rouenonline] @ localhost []
# Query_time: 5  Lock_time: 0  Rows_sent: 20  Rows_examined: 124829
SELECT pid, filepath, filename, url_prefix, filesize, pwidth, pheight, ctime, aid, hits, aid, filename FROM cpg11d_pictures WH
ERE 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,1223)   ORDER BY hits DESC, filena
me   LIMIT 61700 ,20;
# Time: 050402 13:27:22
# User@Host: rouenonline[rouenonline] @ localhost []
# Query_time: 5  Lock_time: 0  Rows_sent: 20  Rows_examined: 63189
SELECT pid, filepath, filename, url_prefix, filesize, pwidth, pheight, ctime, aid, hits, aid, filename FROM cpg11d_pictures WH
ERE 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,1223)   ORDER BY hits DESC, filena
me   LIMIT 60 ,20;
# Time: 050402 13:27:23
# User@Host: rouenonline[rouenonline] @ localhost []
# Query_time: 6  Lock_time: 0  Rows_sent: 1  Rows_examined: 63112
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 2 ,1;
# User@Host: rouenonline[rouenonline] @ localhost []
# Query_time: 10  Lock_time: 4  Rows_sent: 20  Rows_examined: 124829
SELECT pid, filepath, filename, url_prefix, filesize, pwidth, pheight, ctime, aid, hits, aid, filename FROM cpg11d_pictures WH
ERE 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,1223)   ORDER BY hits DESC, filena
me   LIMIT 61700 ,20;

I can't understand it's not the priority of the dev team : even a dedicated server, it's not enough.

He said me "when you have Lock_time, it's very bad" ....

Nol33t disappears, only one solution for me : stop coppermine.

What a pity, but that's life.

[Tarique Sani]

"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,1223)"

IF you can reduce the number of private albums to as low as possible it will work for the time being...

also if you can have someone look over the indexes for your database - looks like the indexing is not proper

[Rodinou]

I have found a temporary solution
2 dedicated servers

One where there is CPG and all the photos
One for ONLY CPG DB ...

The server for DB CPG is a Celeron 2,6 ghZ, 1go RAM and under Redhat.

CPG DB (so ... THE ONLY DB USED on this server) takes ... (incredible) 65% of the ressources !!!!

Another thing : we are near from 6000 visits/day ... now.

[cdrake]

I have a dual 2.8 xeon, 1 GB ram  and about 8000 visitors per day and use 70% server usage.

[Rodinou]

I have a dual 2.8 xeon, 1 GB ram  and about 8000 visitors per day and use 70% server usage.

Coppermine is Server Gargantua Impressive ...

[donnoman]

MySQL's Query Caching.

Have the users that have large galleries looked at tuning thier MySQL Query Cache? In my brief tests it suggests that a well tuned cache may drastically improve performance.

I've done some more research on MySQL's caching. By default it comes configured but with a default cache size of 0 which effectively disables it.

I did 5 refreshes of my devel coppermine install's index.php and noted the changes before and after changing the cache size.

Query Cache Size 0 (default)
Page generated in 0.643 seconds - 97 queries in 0.313 seconds - Album set : ; Meta set: ;
Page generated in 0.642 seconds - 97 queries in 0.309 seconds - Album set : ; Meta set: ;
Page generated in 0.659 seconds - 97 queries in 0.321 seconds - Album set : ; Meta set: ;
Page generated in 0.642 seconds - 97 queries in 0.312 seconds - Album set : ; Meta set: ;
Page generated in 0.641 seconds - 97 queries in 0.312 seconds - Album set : ; Meta set: ;

Query Cache Size 16mb
Page generated in 0.662 seconds - 97 queries in 0.31 seconds - Album set : ; Meta set: ;
Page generated in 0.459 seconds - 97 queries in 0.087 seconds - Album set : ; Meta set: ;
Page generated in 0.473 seconds - 97 queries in 0.098 seconds - Album set : ; Meta set: ;
Page generated in 0.484 seconds - 97 queries in 0.087 seconds - Album set : ; Meta set: ;
Page generated in 0.463 seconds - 97 queries in 0.092 seconds - Album set : ; Meta set: ;

To find out if you need to tune the cache you need to occasionly check the status.

SHOW STATUS LIKE 'Qcache%'

from: http://lists.mysql.com/mysql/181084

Try to increaze your query_cache_size until there's no more additional
lowmem_prunes reported.

The only starting figure for cache size that I saw was 16mb for a machine that had 4gb of ram. I would probably start at 4mb and tune accordingly.  I set mine at 16mb just cause I didn't have a better reference.