Print

[xplicit]

Hello, perhaps this is gonna be a long post so I will explain this as clearly possible.

Browsing trough the forums for a few months now I sometimes stumbled into the questions like, why is it slow, it uses a lot of resources, HP versions, etc etc. I didn't care so much about these thing and thought well you probably have to have thousends of users before this will effect me, but well this week my host cut me off due to the following:

It should be noted that the increased traffic volumes caused by your site appear to have increased the load on the server by as much as 50%. This estimate is based on the anecdotal evidence of a 50% drop in the load immediately following, and sustained since, the rate limit was applied to your site.

So they cut me off after only 2 days of high traffic (while unique visotors is not even so high)

date                    requests      pages          unique visitors
6/Nov/05             62445           615                    169
7/Nov/05            148769         1709                   492
8/Nov/05            173892         2172                   700
9/Nov/05            227846         1798                   543
10/Nov/05            25226         1125                   407

I find it very astounishing that even 700 unique vistors can produce so much requests

So I had to force a solution, considering the site was not usefull anymore I had to host my database and thumbnails on a separate server and link to the normal and big images, to minimize the request on the cut off server this was no problem but this seperate server is consuming pretty much processor load. My current host even suggested a dedicated server but well this is only hobby

I know there has been a discusssion about a High performance version but I was wondering if this project is still alive? I think it could be interesting for the bigger sites and I'm willing to participate to code if nessesary

I also want to hear from high traffic sites how they handled this problem and if they havent had problems, I wanna know where they are hosted (I used Hagenhosting.com) I think 700 unique users doesnt have to be a problem but well I'm wondering how others look at this

Here are a few related subjects:

Using count * querys and speeding up:

http://forum.coppermine-gallery.net/index.php?topic=14663.0
http://forum.coppermine-gallery.net/index.php?topic=13188.0


Other slow topics

http://forum.coppermine-gallery.net/index.php?topic=23378.0 (allthough probably caused by extra stats etc)

Optimize requests

http://forum.coppermine-gallery.net/index.php?topic=15653.0 (discussion died on september)

A part about table locking solution

http://forum.coppermine-gallery.net/index.php?topic=8744.0

About db querys and solutions to speed up the index page (double querys with no cats)

http://forum.coppermine-gallery.net/index.php?topic=8837.0

Optimize requests

http://forum.coppermine-gallery.net/index.php?topic=21514.0

[Rodinou]

Hello !!

thanx for this great message.

But we have decided to realize a "parallel" project based on CPG, by for example putting cats on cache, and drop the template system (very not optimized) and ... using  A LOT OF AJAX

today, I have more visits, less queries, and 2x more visits on the pics !!!

http://www.sortons.net/photos/ : say my site = 4000 visits/day (it can give you an idea of the quick answer of my server)

I'll look into the links you have put : THANX.

[xplicit]

Thnx for your input!

I think looking at coppermine from a different angle is probably the best way, I took a look at what I think is the largest site running on coppermine http://gallery.greatestjournal.com (1,202,922 Members -> 3,000,000 photos) but if I look at theire setup
http://www.greatestjournal.com/servers.bml I can imagine that they dont use a standard system aswell 

Caching could be one solution once I know what to cache 

Optimizing querys is I think now more or less the bottle neck

[Rodinou]

We have made "this" for the moment
- 100% tableless > very easy to customize and update
- cats are caching > as you can see, the main page loads very fast, even all the stats there are inside popup dhtml info
- reduce the number of the pages to see the SAME info > in fact there is only ONE page to see 84000 photos ... but you can dynamically by a false url show a gallery directly

To do so ...
- admin directly in CPG (very hard !)
- optimize the requests more and more
- possibility to choose the interface (classic, or dynamic like ours)

But the REAL pb of CPG is queries : we have given a solution, but ... So don't hesitate to send me PM, or contact me via MSN

[xplicit]

I think most of the SELECT count (*)  querys should not be used or used otherwise. I see them used a lot and well they are pretty consuming if you got a lot of visitors. So preferable these are indeed cached of put into a table or colum.

For those galleries where people can not upload and therefore the number of pictures don't vary daily it can be made very simple, just add a colum statsitcs in the album table and make a special function in the admin menu to do a statistics job.

In the statistics job should be the count SELECT count (*) things and should only be run when new pictures are added, could be even more integrated

Also there is another way to avoid the SELECT count (*) if you want to do a row count.

You can use the sysindexes to avoid a complete table scan being performed. for instance you can use udf_Tbl_RowCount

I have to code this into my test version but would be something like this (standard usage of the function)

SELECT rows
FROM sysindexes
WHERE id = OBJECT_ID('table_name') AND indid < 2

for information about the used indid

0 Table data when there is no clustered index
1 Refers to the clustered index

just standard sql stuf

I know this will not give very exact numbers but who carea with a few thousend pictures/users etc

[Stramm]

you can increase with some lil modifications in index CPGs speed by a factor of 3-4 (MySQL)
below     // Gather gallery statistics are 3 sql statements with some left joins
shorten them so that they read like

Code: [Select]

        $sql = "SELECT count(*) FROM {$CONFIG['TABLE_PICTURES']}";
and
        $sql = "SELECT count(*) FROM {$CONFIG['TABLE_COMMENTS']}";
and
        $sql = "SELECT sum(hits) FROM {$CONFIG['TABLE_PICTURES']} ";


stats (the line: 11255 files in 18 albums and 1 categories with 41162 comments viewed 2723948 times) aren't 100% anymore cause private albums and pics got counted now too. But I don't care. That increased query time for index.php from 0.45s to 0.075s on my site

The SELECT COUNT (*) queries don't have a big impact (at least for me)

you also can comment out the banning block in init.inc.php (if you don't use banning), saves 2-3 queries

The next thing I've optimized was the onlinestats mod. It added a query for each registered user to look up his name. When there are over 40 registered users online that'll slow things down (on my box it was 40. Other setups prolly will be able to do more or less). So I just added a new column to the online table (online_name) and save the user name in there when he logs in. This has reduced load as well a lot.

Some more things to keep in mind. Each category that's visible on the index needs 3 queries (subcats too). The random block is a resource eater as well.
 -> So a good setup would be to have eg. max. 4 main cats and not to show subcats and first level albums on the index. You can show category images, that's not a mysql slowdown.
And you may have some (4-6) albums that are not in any category. They will appeare directly on the main and pretty it (in my humble opinion )

Such a setup's able to do a lot of visits

[Rodinou]

Stramm > there are very good ways in your post !

[xplicit]

The next thing I've optimized was the onlinestats mod. It added a query for each registered user to look up his name. When there are over 40 registered users online that'll slow things down (on my box it was 40. Other setups prolly will be able to do more or less). So I just added a new column to the online table (online_name) and save the user name in there when he logs in. This has reduced load as well a lot.

I'm also using the online user part and well I just recently realized that it increases the queries heavely when id and name have to be searched separately, for each user I use it on every page so that was pretty dumb of me just realizing it eats a lot of extra querys.

Indeed some good points in your article stramm!

I also removed the normal querys:

[10] => SELECT aid FROM cpg131_albums as a WHERE category>=10000
[11] => SELECT count(*) FROM cpg131_pictures as p, cpg131_albums as a WHERE p.aid = a.aid AND category >= 10000

Since I never use private albums, so saved me 2 querys

[xplicit]

Ok for those interested in removing the banned feature as mentioned before:

Saves 2 querys:

* NOTE don't remove features like this if you don't 100% understand its inpact *
* In general this complete topic is not for newbees but for people who know theire way around in coppermine *

search in init.inc.php :

Code: [Select]

// load the main template
load_template();
// Remove expired bans
$now = time();
db_query("DELETE FROM {$CONFIG['TABLE_BANNED']} WHERE expiry < $now");
// Check if the user is banned
$user_id = USER_ID;
$result = db_query("SELECT * FROM {$CONFIG['TABLE_BANNED']} WHERE ip_addr='$raw_ip' OR ip_addr='$hdr_ip' OR user_id=$user_id");
if (mysql_num_rows($result)) {
   pageheader($lang_error);
   msg_box($lang_info, $lang_errors['banned']);
   pagefooter();
   exit;
}
mysql_free_result($result);
// Retrieve the "private" album set

edited like

Code: [Select]

// load the main template
load_template();
// Remove expired bans
//$now = time();
//db_query("DELETE FROM {$CONFIG['TABLE_BANNED']} WHERE expiry < $now");
// Check if the user is banned
$user_id = USER_ID;
//$result = db_query("SELECT * FROM {$CONFIG['TABLE_BANNED']} WHERE ip_addr='$raw_ip' OR ip_addr='$hdr_ip' OR user_id=$user_id");
//if (mysql_num_rows($result)) {
 //   pageheader($lang_error);
 //   msg_box($lang_info, $lang_errors['banned']);
 //   pagefooter();
 //   exit;
//}
// mysql_free_result($result);
// Retrieve the "private" album set

[xplicit]

you can increase with some lil modifications in index CPGs speed by a factor of 3-4 (MySQL)
below     // Gather gallery statistics are 3 sql statements with some left joins
shorten them so that they read like

Code: [Select]

        $sql = "SELECT count(*) FROM {$CONFIG['TABLE_PICTURES']}";
and
        $sql = "SELECT count(*) FROM {$CONFIG['TABLE_COMMENTS']}";
and
        $sql = "SELECT sum(hits) FROM {$CONFIG['TABLE_PICTURES']} ";



Used it and improved the speed a lot!

Allthough my figures are extreme low, I must say this is measured at localhost (AMD Atlon 3 Ghz 64bit 1 Gb RAM) so ever little second has to be multiplied by a factor X at real servers setup abroad so allthough these figures seem low I think its a big difference

before:
Page generated in 0.126 seconds - 32 queries in 0.078 seconds - Album set :
Page generated in 0.127 seconds - 32 queries in 0.077 seconds - Album set :
Page generated in 0.127 seconds - 32 queries in 0.077 seconds - Album set :
Page generated in 0.127 seconds - 32 queries in 0.075 seconds - Album set :
Page generated in 0.127 seconds - 32 queries in 0.077 seconds - Album set :

after:
Page generated in 0.108 seconds - 32 queries in 0.058 seconds - Album set :
Page generated in 0.109 seconds - 32 queries in 0.059 seconds - Album set :
Page generated in 0.11 seconds - 32 queries in 0.06 seconds - Album set :
Page generated in 0.111 seconds - 32 queries in 0.06 seconds - Album set :
Page generated in 0.109 seconds - 32 queries in 0.06 seconds - Album set :

[Stramm]

Used it and improved the speed a lot!

Allthough my figures are extreme low, I must say this is measured at localhost (AMD Atlon 3 Ghz 64bit 1 Gb RAM) so ever little second has to be multiplied by a factor X at real servers setup abroad so allthough these figures seem low I think its a big difference

You need a bunch of users, hits on pictures and comments to see the improvement (in my example 10512, 2723948, 41162).
If you're on a test machine with an empty db... that's no challenge for MySQL

Also I made all admin settable. So you can switch off stuff when you feel the index slowing down

[Stramm]

- cats are caching > as you can see, the main page loads very fast, even all the stats there are inside popup dhtml info

yes, that's the only thing that's good for caching and can improve speed some more

But the REAL pb of CPG is queries : we have given a solution, but ... So don't hesitate to send me PM, or contact me via MSN

you have a link? or you can elaborate more?

[xplicit]

- cats are caching > as you can see, the main page loads very fast, even all the stats there are inside popup dhtml info

yes, that's the only thing that's good for caching and can improve speed some more

But the REAL pb of CPG is queries : we have given a solution, but ... So don't hesitate to send me PM, or contact me via MSN

you have a link? or you can elaborate more?

I think the galley of Rodinou is http://www.sortons.net/photos/ load very fast!

And well my localhost is not empty   It's an exact copy of my main site  5159 pictures, 205330 views, 4879 members

Will be doing some tuning today

[xplicit]

Ok I tried to speed things up this afternoon and succeed

I think once you got the gallery running the way you want it to its good to review all the querys and see which once you really use and which not. For instance in my configuration I managed to remove a lot of extra querys for every cat, original theire are 7 for each cat but I only need 4, in case you got a few cats this can speed up the querys draticly.

In real situation, also by seeing that I was doing things double I managed to get the following results: (measured at real server)

old
Page generated in 1.932 seconds - 82 queries in 1.043 seconds - Album set :
Page generated in 1.638 seconds - 82 queries in 0.915 seconds - Album set :
Page generated in 2.423 seconds - 82 queries in 0.944 seconds - Album set :
Page generated in 1.675 seconds - 82 queries in 0.923 seconds - Album set :
Page generated in 1.71 seconds - 82 queries in 0.923 seconds - Album set :

Figures were getting even worse because I used a online user block (this was measured with 12 users online)

new
Page generated in 1.013 seconds - 46 queries in 0.391 seconds - Album set :
Page generated in 1.03 seconds - 46 queries in 0.407 seconds - Album set :
Page generated in 1.012 seconds - 46 queries in 0.412 seconds - Album set :
Page generated in 1.038 seconds - 46 queries in 0.411 seconds - Album set :
Page generated in 1.026 seconds - 46 queries in 0.403 seconds - Album set :

Also with 12 users online
I think my host will like this too

In general for speeding up I would suggest quote out the not used features once you got it running the way you want.

Cause high speed and lots of features are mostly two thing which are sort of opposite of each other this is to be a decission for each developer, so perhaps a high performance version will allways be something unique for everyone, and not a simple thing which ca be made to fit everybodys need (just my 2 cents)

[Rodinou]

For the INDEX CPG, we have ALL changed ...
no more template (css is powerful to change as you want a gallery without restrictions of TABLE)
and CACHE YOUR CATS !!! It's the thing which loads the more requests and the index page ! (more than the stats !!!)

I'm looking all the ideas you have taken, we'll add them to our project
Actually > 84 000 photos (about) / 3700 members, and sometimes ... 80 members at same time > ANY PROBLEM.

We have seen by "erasing" the template system, it loaded faster : it would be good, for people who want to keep the template system, to see WHERE it's wrong.