forum.coppermine-gallery.net
No Support => Feature requests => Scheduled for cpg1.5.x => Topic started by: Rodinou on March 09, 2005, 03:57:32 pm
-
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
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;
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" ? ...
-
I will look into caching/db performance/unnecessary queries etc for 1.5 when I have time.
-
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.
-
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.
-
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?
-
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.
candid (ok not that much :P) point of view: why then not doing it, and do the filtering on the script side? I had experiences where that happend to be the most efficient solution
-
@nol33t - elaborate
-
happy my request is not an invalid request ...
I'm afraid in fact because my site is known by a lot of people : this summer, we'll cover "Sziget Festival" and I'll have thousands new visitors for photos of the biggest european festival ... if my server CRASHES today ... I don't want to think about in few months ...
I'll wait your solution to use coppermine with fantasya (which has MORE visitors that my site sortons.net ...)
But I remember, when we dev' fantasya.net ... we have done a caching system about 5mn .... and it could support 20 000 visitors / day without problem.
-
@Tarique
I'm working on a project where some table rowcount went from 1 to 15 millions in one year
The same queries which were taking 2 min to run, suddenly ( really suddenly, i mean the next day ) were taking 45 min
Some of those queries were queries with nested NOT IN in it.
From what i learnt: the sql engine, depending on the size of the table, choose or not to use the index of the table when running subqueries.
When you reach a certain point ( i'm not dba so i won't go into more details ), the engine decides not to use the index anymore, but to do a table scan --> that's when you start to get in trouble.
To solve that and get back (lot) faster running queries, depending on the need, we did one of the two following things:
- replaced queries of the kind
select * from a where a.id not in ( select id from b where etc...)
with
select b.id into #tmptable_b where etc.. ( syntax in mysql being "select into temporary table tmptable_b i think..)
select * from a where a.id not in ( select id from #temptable_b )
OR ( and i think that example fits more w/ cpg )
- replaced the same query
select * from a where a.id not in ( ... ) and a.another_column = 'another_value"
with
select * from a where a.another_column = 'another_value'
then in the script/program,
after fetching the results
(pseudo coding here )
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
Those are just some thoughts
but even if the numbers are differents..i think the concept stays the same
-matt-
-
@nol33t - you obviously are not too familiar with CPG - go figure :P
-
@Tarique
LOL ;D
elaborate? ::)
Read my post again, those are just some thoughts, the way CPG exactly works doesn't matter - since you have to change things to optimize them.
My main point was: NOT IN queries are always avoidable ( or optimizable ), replacing them by code or temp table can be way faster, and i'm familiar w/ that one ;)
--edit: if you got time to write more that one line / post, please provide a concrete example, and i'll show you the concrete solution :P
-
@nol33t - you obviously are not too familiar with CPG - go figure :P
I was under the impression that nol33t knows his way around in coppermine pretty well - at least I don't understand why you're posting this, sounds a bit rude to me (maybe a language issue on my side though). After all, nol33t knows the trade and volunteers to discuss and improve coppermine, he deserves a better reply imo.
Joachim
-
@Gaugau - I am not being rude - may be being a bit terse but definitely not yet being rude - I just want nol33t to not talk in generalizations and dismiss others point of view as candid and go on to question why not do it.
What he has pointed out is something very general, known to almost anyone who has been programming long enough BUT I know this cannot be applied in CPG in many places - this is the reason for my response doubting his familiarity with the CPG (code)
Then he goes on to ask for concrete examples - the very first post in this thread is replete with examples.
Just like you rightly do not have tolerance for people asking silly question - I always question people who just talk about programming without showing me the code :)
@all: Once again like I have mentioned we have optimised a whole lot of stuff for NG - just that *as of now* I do not know how to avoid "aid NOT IN" clause in the all the SQL. Anyone who has CPG specific suggestions is most welcome to contribute to the thread and code.
Caching of stats is one area where we are doing quite a bit of RnD also caching of the index page for non logged in user is another area which we are looking into
-
Funny, last time i've showed some code ( forgotten password issue ), nobody took even 5min to look at it.
And it's the first time i've "genreralized" an idea in this board without appropriate code for it
Then he goes on to ask for concrete examples - the very first post in this thread is replete with examples.
Please don't be so elitist ( i'm no-l33t ), don't reply to others when i'm the one who asked the question
about those queries in the first post: true, forgot about them.
That still can be optimized, but i don't even feel like discussing it anymore - I'm out of that thread
-
Nol33t :) if you continue like this, you'll have a bad karma like me !! (-17 : thanx all people who smite me, I love it) :)
I think Coppermine is used by thousands users, but not thousands users have big traffic : and I think this problem will become bigger with time : Coppermine becomes a standard, and who says standard, says ... more traffic.
-
Funny, last time i've showed some code ( forgotten password issue ), nobody took even 5min to look at it.
And it's the first time i've "genreralized" an idea in this board without appropriate code for it
Hmm...I remember discussing it.... ;)
That's why I responded with, what I thought, a better solution. It was partly using your ideas, but not all; however, you didn't care to respond or do it that way.
-
[ot]
That's why I responded with, what I thought, a better solution. It was partly using your ideas, but not all; however, you didn't care to respond or do it that way.
I did care and thought about it dude...
but linking to the user session ( id=0..) without big changes seemed to me way to complex for the purpose intented, compared to a ~50 lines diff mod in one file
ok maybe this answer i should have posted ;)
[/ot]
-
[ot]
That's why I responded with, what I thought, a better solution. It was partly using your ideas, but not all; however, you didn't care to respond or do it that way.
I did care and thought about it dude...
but linking to the user session ( id=0..) without big changes seemed to me way to complex for the purpose intented, compared to a ~50 lines diff mod in one file
ok maybe this answer i should have posted ;)
[/ot]
Ok. As I said before, it states a solution to what you're saying.
@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.
-
that's why dhtml solutions are welcome (like the mod nol has made about the filmstrip, and I dream to have a whole interface like this ...)
-
Rod, DHTML is in no way related to this discussion. I understand that it is your pet topic, but we're talking about database queries here, which are server-sided. DHTML is client-sided and won't help us in trying to reduce the query load ;)...
Joachim
-
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: 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
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
-
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.
-
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 ...
-
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
-
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 :(
-
like I said: remove the stats, as they use the mySQl comand "count", which is critical.
Joachim
-
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 ...
-
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
-
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).
-
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?
-
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 ....
-
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
-
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.
-
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.
-
"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
-
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.
-
I have a dual 2.8 xeon, 1 GB ram and about 8000 visitors per day and use 70% server usage.
-
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 ...
-
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.
-
Where do we change this ?
actually mysql on my server is configured like this
back log 50
basedir /
binlog cache size 32768
character set latin1
character sets latin1 big5 czech euc_kr gb2312 gbk sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5
concurrent insert ON
connect timeout 20
datadir /var/lib/mysql/
delay key write ON
delayed insert limit 100
delayed insert timeout 300
delayed queue size 1000
flush OFF
flush time 0
have bdb NO
have gemini NO
have innodb NO
have isam YES
have raid NO
have openssl NO
init file
interactive timeout 28800
join buffer size 131072
key buffer size 8388600
language /usr/share/mysql/english/
large files support ON
locked in memory OFF
log OFF
log update OFF
log bin OFF
log slave updates OFF
log long queries ON
long query time 4
low priority updates OFF
lower case table names 0
max allowed packet 1048576
max binlog cache size 4294967295
max binlog size 1073741824
max connections 270
max connect errors 10
max delayed threads 20
max heap table size 16777216
max join size 4294967295
max sort length 1024
max user connections 0
max tmp tables 32
max write lock count 4294967295
myisam max extra sort file size 256
myisam max sort file size 2047
myisam recover options 0
myisam sort buffer size 8388608
net buffer length 16384
net read timeout 30
net retry count 10
net write timeout 60
open files limit 0
pid file /var/lib/mysql/NAME OF MY SERVER :)
port 3306
protocol version 10
record buffer 131072
record rnd buffer 131072
query buffer size 0
safe show database OFF
server id 0
slave net timeout 3600
skip locking ON
skip networking OFF
skip show database OFF
slow launch time 2
socket /var/lib/mysql/mysql.sock
sort buffer 2097144
sql mode 0
table cache 64
table type MYISAM
thread cache size 0
thread stack 65536
transaction isolation READ-COMMITTED
timezone CEST
tmp table size 33554432
tmpdir /tmp/
version 3.23.58-log
wait timeout 28800
-
Mine is configured in my my.ini; it could be my.cnf depending on your configuration.
just add :
query_cache_size=16M
query_cache_type=on
to that whatever file you printed that config from,
another mysql query cache tuning doc (http://www.mysql.com/news-and-events/newsletter/2003-01/a0000000108.html)
You can also change it in the MySQL Admin GUI Client under server configuration/performance.
You have to restart mysql after changing the config.
Other sql queries that will tell you the status of query caching:
SHOW VARIABLES LIKE '%query_cache%'
SHOW STATUS LIKE 'Qcache%'
-
funny to see this thread, i see you've posted on my other thread too http://forum.coppermine-gallery.net/index.php?topic=14663.0
The changes I made there had reduced my gallery extremely already. But i've also made a security lock in it too, if the load reaches a certain degree, it'd temporarely disable usage of coppermine too.
If you are using linux you can add this code in init.inc.php. If the load goes above 25, it'll lock users out so that the server can handle things first. I find load 50 to be the limit, if you goes over that, you have a large chance that your server crashes.
If someone interested, i'll try to find what i've done to reduce load and let you guys mod it.
//check for serverload
if ( file_exists('/proc/loadavg') )
{
if ( $fh = @fopen( '/proc/loadavg', 'r' ) )
{
$data = @fread( $fh, 6 );
@fclose( $fh );
$load_avg = explode( " ", $data );
$server_load = trim($load_avg[0]);
echo "Current serverload: ".$server_load ;
if ($server_load > 25)
{
pageheader("Server too busy, please try later");
msg_box("Server too busy, please try later", "Server too busy, please try later<br> current load $server_load ");
pagefooter();
exit;
}
}
}
put this before
if (!USER_IS_ADMIN && $CONFIG['offline'] && !strstr($_SERVER["SCRIPT_NAME"],'login')) {
pageheader($lang_errors['offline_title']);
msg_box($lang_errors['offline_title'], $lang_errors['offline_text']);
pagefooter();
exit;
}
-
Given a sample database and a explanation of how to get to the point where it crasses your server I would be willing to look into optimizing these sql statments for you.
It seems quite strange to me that anyone would need to make such a bige exclude list. It might just be inexperiance with SQL or with optimizing of SQL that caused these statments to be created in the first place but I am sure that these can be optimized with a little TLC. ;)
If you want me to have a go at me drop me a email or a PM and point me to a sample database so I can slap that on a test system and have a go at optimizing these quries for you.
-
Ratking do you have a 1.4 development Gallery setup?
If you could post some of your ideas at optimizing SQL queries we can certainly discuss them. Once we have some workable queries that show significant perfomance gains without sacrificing features I can talk to the other devs and start fitting them into the roadmap.
If you just need to setup your dev gallery, search the boards for timer.php. Drag a few directories of pictures into your albums folder and run timer.php against it, you'll have a decent sized test gallery in no time. Complete with Categories, Albums, and Images.
-
Regarding the Usage of the mysql query cache: I have looked at the 1.4 DB schema, and it still has a structure
that (partly) makes caching useless. One of the most-used tables of course is the "pictures" table, and this
table has counter values for "votes" and "views" for each picture. So if voting and/or counting is enabled ,
the content of the picture table changes frequently and the query cache can never be used.
This could only be circumvented with a kind of "queue" approach: collect counts and votes in a "queue" table and insert the values into the picture table in batches, like once per hour or so (admin setting). This will
make it possible to benefit from the query cache.
-
I would like to add my support for much optimising of the SQL queries made by CPG, I think the current 1.3.4 server load is excessive and detracts from an otherwise excellent gallery.
I would like to propose the option to turn off in the admin config the display of forum statistics (view counts, album counts etc.), checking for banned users and updating the banned list.
A preferable option would perhaps be for these counts, and maintainence actions not to occur with every page load but to be scheduled. I realise many users won't have access to cron and requiring a scheduled job to keep these things up to date as standard isn't a good idea, being able to turn them off in the admin control panel, and replacing the function by running a dedicated maintainance php file regularly using cron.
-
Theres no reason maintenance routines have to be run by cron. Coppermine already has an example in it that uses a timestamp in the config to make sure some maintenance doesn't happen faster than once an hour.
This same method could be used in other schemes.
-
Mine is configured in my my.ini; it could be my.cnf depending on your configuration.
just add :
query_cache_size=16M
query_cache_type=on
to that whatever file you printed that config from,
another mysql query cache tuning doc (http://www.mysql.com/news-and-events/newsletter/2003-01/a0000000108.html)
You can also change it in the MySQL Admin GUI Client under server configuration/performance.
You have to restart mysql after changing the config.
Other sql queries that will tell you the status of query caching:
SHOW VARIABLES LIKE '%query_cache%'
SHOW STATUS LIKE 'Qcache%'
Hey,
just found the topic.
I tried to change/add that to my "my.cnf", but after I did that my mysql rejected my login. I could not use mysql anymore.
So, where exactly do I have to add this?
-
this is not a support thread, butt out.
-
Hello all. At the site where i am an administrator we have a large user gallery based on coppermine. You can see it here if you want : http://www.dpgr.gr/usergalleries/index.php?cat=0
The gallery has more than 117 000 photos and more than 20 000 page views per day.
We also have a dedicated dual Xeon server @ 3Ghz and 2Gb ram but the server was crashing almost 3 times per day. So i had to do something about it. I timed several parts of the code and finally located the most heavy queries. The good news is that i managed with some modifications to considerably decrease the page creation times. So lets start with the mainpage...
In our server the main page was created in something like 3 to 4 seconds. Very large time for a gallery with 20 000 page views per day. There were 2 main problems about it.
1) The stats display that say how many photos, albums, comments etc the gallery has took about 0,7 seconds. So i decided to remove the code that gathers the stats from the index.php and place it in a seperate .php file which you can either run manually when you want the stats to be updated or make it a cron job to automatically run every 1 or 4 hours or whatever period you want (but dont do it every minute!).
Here is the modifications you need to do:
1a) Open index.php and comment the following lines :
/*$result = cpg_db_query("SELECT count(*) FROM {$CONFIG['TABLE_ALBUMS']} as a WHERE 1" . $album_filter);
$nbEnr = mysql_fetch_array($result);
$album_count = $nbEnr[0];
......................................... More code here ..................................
$nbEnr = mysql_fetch_array($result);
$hit_count = (int)$nbEnr[0];
mysql_free_result($result);*/
1b) Immediatly after that add the code:
$exec = mysql_query("SELECT * FROM gallery_stats LIMIT 0,1") or die(mysql_error());
$data = mysql_fetch_assoc($exec);
$album_count = $data['albums'];
$picture_count = $data['pictures'];
$comment_count = $data['comments'];
$cat_count = $data['categories'];
$hit_count = $data['views'];
Save and upload the file. 2 more steps remaining ...
1c) No go to your database with phpmyadmin and run the following SQL:
CREATE TABLE `gallery_stats` (
`stat_id` tinyint(3) unsigned NOT NULL auto_increment,
`albums` bigint(20) NOT NULL default '0',
`pictures` bigint(20) NOT NULL default '0',
`categories` bigint(20) NOT NULL default '0',
`comments` bigint(20) NOT NULL default '0',
`views` bigint(20) NOT NULL default '0',
PRIMARY KEY (`stat_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
--
-- Dumping data for table `gallery_stats`
--
INSERT INTO `gallery_stats` VALUES (1, 0, 0, 0, 0, 0);
This will create a table that will hold the stats that index.php will retrieve and display and that the following file (gallery_stats.php) will generate...
1d) Create a php file gallery_stats.php and put the following code in it
<?php
$db_name = "yourDB";
$db_user = "yourUser";
$db_passwd = "yourPass";
$db_server = "localhost";
mysql_connect("localhost", $db_user, $db_passwd);
mysql_select_db($db_name);
$CONFIG['TABLE_ALBUMS'] = "usercpg11d_albums";
$CONFIG['TABLE_PICTURES'] = "usercpg11d_pictures";
$CONFIG['TABLE_COMMENTS'] = "usercpg11d_comments";
$CONFIG['TABLE_CATEGORIES'] = "usercpg11d_categories";
$result = mysql_query("SELECT count(*) FROM {$CONFIG['TABLE_ALBUMS']} as a WHERE 1" . $album_filter);
$nbEnr = mysql_fetch_array($result);
$album_count = $nbEnr[0];
mysql_free_result($result);
$sql = "SELECT count(*) FROM {$CONFIG['TABLE_PICTURES']} as p " . 'LEFT JOIN ' . $CONFIG['TABLE_ALBUMS'] . ' as a ' . 'ON a.aid=p.aid ' . 'WHERE 1' . $pic_filter . ' AND approved=\'YES\'';
$result = mysql_query($sql);
$nbEnr = mysql_fetch_array($result);
$picture_count = $nbEnr[0];
mysql_free_result($result);
$sql = "SELECT count(*) FROM {$CONFIG['TABLE_COMMENTS']} as c " . 'LEFT JOIN ' . $CONFIG['TABLE_PICTURES'] . ' as p ' . 'ON c.pid=p.pid ' . 'LEFT JOIN ' . $CONFIG['TABLE_ALBUMS'] . ' as a ' . 'ON a.aid=p.aid ' . 'WHERE 1' . $pic_filter;
$result = mysql_query($sql);
$nbEnr = mysql_fetch_array($result);
$comment_count = $nbEnr[0];
mysql_free_result($result);
$sql = "SELECT count(*) FROM {$CONFIG['TABLE_CATEGORIES']} WHERE 1";
$result = mysql_query($sql);
$nbEnr = mysql_fetch_array($result);
$cat_count = $nbEnr[0] - $HIDE_USER_CAT;
mysql_free_result($result);
$sql = "SELECT sum(hits) FROM {$CONFIG['TABLE_PICTURES']} as p " . 'LEFT JOIN ' . $CONFIG['TABLE_ALBUMS'] . ' as a ' . 'ON p.aid=a.aid ' . 'WHERE 1' . $pic_filter;
$result = mysql_query($sql);
$nbEnr = mysql_fetch_array($result);
$hit_count = (int)$nbEnr[0];
mysql_free_result($result);
mysql_query("UPDATE gallery_stats SET albums=$album_count, categories=$cat_count, comments=$comment_count, views=$hit_count, pictures=$picture_count WHERE stat_id=1") or die(mysql_error());
mysql_close();
?>Just replace the lines with th db connection data with your settings as well the table names. Now save the file and upload it. As i said you can either run it manually ormake it a cron job.
Ok. This is maybe too much for some so lets see another easier modification you can do.
If you have lots of photos and you have enabled the random photos box it will greatly increase your page creation time. So you can either disable it from the admin panel or make the following modification that will decrease the query time from 1,4 secs to about 0,3 :
2a) Open /include/functions.inc.php and find the line :
$query = "SELECT $select_columns FROM {$CONFIG['TABLE_PICTURES']} WHERE approved = 'YES' $META_ALBUM_SET ORDER BY RAND() LIMIT $limit2";
Replace it with the following lines :
$q = "SELECT pid FROM {$CONFIG['TABLE_PICTURES']} WHERE approved = 'YES' $META_ALBUM_SET ORDER BY RAND() LIMIT $limit2";
$ex = mysql_query($q) or die(mysql_error());
$pid_array = array();
while($d = mysql_fetch_assoc($ex)){
$pid_array[] = $d['pid'];
}
$pid_string = implode(", ", $pid_array);
$query = "SELECT $select_columns FROM {$CONFIG['TABLE_PICTURES']} WHERE pid IN ($pid_string)";
Save and upload the file. We are done for the mainpage. With these modifications i was able to drop the page creation time of index.php from 3 seconds to 1.
Now another thing that really slows down coppermine on large galleries is top viewed photos. When you visit the top views album and from there you click to see a photo the page will be created in 4 to 5 seconds. The reason for this is the way the filmstrip is generated trying to figure out what is the previous and next photos. So lets drop this time to around 0,5 seconds doing the following modifications:
3a) find the line :
$query ="SELECT COUNT(*) from {$CONFIG['TABLE_PICTURES']} WHERE approved = 'YES' AND hits > 0 $META_ALBUM_SET $keyword";
and replace with the following:
$cat >= 10000 ? $hit_limit = 0 : $hit_limit = 310;
$query ="SELECT COUNT(*) from {$CONFIG['TABLE_PICTURES']} WHERE approved = 'YES' AND hits > $hit_limit $META_ALBUM_SET $keyword";
3b) find the line:
$query = "SELECT $select_columns FROM {$CONFIG['TABLE_PICTURES']} WHERE approved = 'YES'AND hits > 0 $META_ALBUM_SET $keyword ORDER BY hits DESC, filename $limit";
replace with the following :
$query = "SELECT $select_columns FROM {$CONFIG['TABLE_PICTURES']} WHERE approved = 'YES'AND hits > $hit_limit $META_ALBUM_SET $keyword ORDER BY hits DESC, filename $limit";
The only change you need to do is replace 303 with your own number. To find what the number for your gallery should be do this: Go to the 10th page of the top views album and see how much views the last photo of this page has. Now this is the number you should use instead of 303.
The downside of this modification is that you limit the top views album to the first 10 pages. But when you consider that the page creation time drops to 1/10th of original i think its a fair trade. After all, top views is about top, not bottom ;D
So finishing up, i would like to make some things clear.
1) Those solutions may not be the best ones ... but they work.
2) I am not trying to say that the coppermine gallery is not good. I am just trying to help people that have similar proplems with me and maybe offer some ideas for future versions of coppermine.
Hope this helped, try the modifications and tell me what you think.
-
Thanks for sharing your thoughts and code tweaks. Will be considered for future versions.
-
Now another thing that really slows down coppermine on large galleries is top viewed photos. When you visit the top views album and from there you click to see a photo the page will be created in 4 to 5 seconds. The reason for this is the way the filmstrip is generated trying to figure out what is the previous and next photos. So lets drop this time to around 0,5 seconds doing the following modifications:
That was one awesome hack, thank you so much. I've used the config tool to make my own button called "Last Viewed" , is there a way to apply this hack to that to? i use string "thumbnails.php?album=lasthits&cat=0" to call the page.
-
No support in feature requests. (http://forum.coppermine-gallery.net/index.php?topic=15866.0)
If anyone out there has a gallery causing high load on a dedicated server and would consider moving it to my server for analysis please contact me via PM.
-
I actually figured this out my self, if anyone wants to apply this my sloppy way:
go to functions.inc.php
find code:
case 'lasthits': // Last viewed pictures
if ($META_ALBUM_SET && $CURRENT_CAT_NAME) {
$album_name = $lang_meta_album_names['lasthits'].' - '. $CURRENT_CAT_NAME;
} else {
$album_name = $lang_meta_album_names['lasthits'];
}
$query = "SELECT COUNT(*) from {$CONFIG['TABLE_PICTURES']} WHERE approved = 'YES' and hits > 0 $META_ALBUM_SET";
$result = cpg_db_query($query);
$nbEnr = mysql_fetch_array($result);
$count = $nbEnr[0];
mysql_free_result($result);
//if($select_columns != '*') $select_columns .= ', UNIX_TIMESTAMP(mtime) as mtime, aid, hits, lasthit_ip, owner_id, owner_name';
$select_columns = '*, UNIX_TIMESTAMP(mtime) as mtime'; //allows building any data into any thumbnail caption
$query = "SELECT $select_columns FROM {$CONFIG['TABLE_PICTURES']} WHERE approved = 'YES' and hits > 0 $META_ALBUM_SET ORDER BY mtime DESC $limit";
$result = cpg_db_query($query);
$rowset = cpg_db_fetch_rowset($result);
mysql_free_result($result);
if ($set_caption) build_caption($rowset,array('mtime','hits'));
$rowset = CPGPluginAPI::filter('thumb_caption_lasthits',$rowset);
return $rowset;
break;
change the number within the part " $count = $nbEnr[0]; " to the number of images you want to list on your "Last Viewed" page.
-
No, that is rubbish. Please don't force me to lock the thread just to stop you making invalid posts.