Perfomance suggestions for 1.4 and 1.5 versions

[remdex]

Hi,

Just waned to share my experience about coppermine performance with a lot of photo around 130 000 images and share some tips and recommendations.

1. Missing indexes

functions.inc.php 1603 line.
Top rated sorting is missing triple column index on images table.
ORDER BY pic_rating $DESC, r.votes $DESC, pid $DESC
KEY pic_rate_sort (pic_rating,votes,pid)
Run SELECT EXPLAIN... and you will see about what I'm talking about.


Second thing i do not like.
ORDER BY mtime $DESC, pid $ASC
You are mixing DESC and ASC sorting. That way if you even and create mtime and pid index. It's useless.... Because sorting is mixing ASC and DESC... With 130 000 images it makes different about half second.
See http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/ Sort in one direction paragraph. If you have to sort in custom directions just create reverse_index column and sort in one direction...

$sort_array = array(
    'na' => "filename $ASC",
    'nd' => "filename $DESC",
    'ta' => "title $ASC",
    'td' => "title $DESC",
    'da' => "pid $ASC",
    'dd' => "pid $DESC",
    'pa' => "position $ASC",
    'pd' => "position $DESC",
);

Missing indexes:
filename - we can use let say 6 characters index here.
position - missing index
title - we can use let say 6 characters index here.

If you dig deeper you could find even more places where indexes are missing just edit mysql config that it logs all queries not using index.

2. General ideas

I have attempted to cache some parts of coppermine with memcache, but in general it's useless because of current coppermine architecture... I dowt that it's possible with current coppermine architecture witch is very inflexible.
If you try to cache with ETag you will endup with the same problem...

So please consider atleast implementing some changes based on first part of suggestions. It will give some boost with a lot of images.

[Joachim Müller]

Just waned to share my experience about coppermine performance with a lot of photo around 130 000 images

Would be even more helpfull if you told us the link to that gallery. In fact that's even mandatory when posting as per board rules.

What version of coppermine have you been testing with, i.e. what version is your site running on? Please post a link as suggested.

You can not possibly suggest features for cpg1.4.x: development has ended for that version long ago - there are only maintenance releases.
You can no longer suggest features for cpg1.5.x, as we're in feature freeze stage. This being said, your suggestion can be considedered for cpg1.6.x, for which development hasn't even started yet. Currently, we're busy coming up with a release for cpg1.5.x.

You posted this on the support board although you obviously are not asking for support, but are giving suggestions for future versions. That's fine, we appreciate that you're trying to help us improve the application. Moving to feature requests board.

[remdex]

Hi,

I do not want to paste link to gallery because it's adult site. I can give database dump if it's interesting. While writing post i was reviewing 1.5.x version some code parts. I was thinking about migration from 1.4.x to 1.5.x but I did not noticed perfomance/architecture changes, so Just wrote some suggestions.

Like i told i can give database dump for perfomance tweaking if it's interesting? currently i done test migration from 1.4.x to 1.5.x so dump data would contain 1.5.x version compatibility if it's ok? . Just tell me how to give a dump you if it's interesting

[Joachim Müller]

I do not want to paste link to gallery because it's adult site.

That's not a problem as long as you respect board rules:

8. Post links
When posting a support request, it's helpfull for supporters in most cases to see what the actual page looks like, that's why it's mandatory that you always post a link to your coppermine gallery page when asking for support.

If your site contains adult content, make sure to post a warning together with your link!
Supporters might get into issues if they accidentally visit a page that interferes with their employer's internet policy. If your site contains adult content (even mild nudity even falls into this category), post the words "not safe for work" next to the link.

[Αndré]

I was thinking about migration from 1.4.x to 1.5.x but I did not noticed perfomance/architecture changes, so Just wrote some suggestions.

Side note: as far I can see your suggestions deals with meta albums. In cpg1.5.x we have a plugin hook where you can add/change meta albums.

[remdex]

Hi,

Sorry did not now that i can post links to adult gallery. Anyway it's a dump of 1.5 gallery with 110 000 images. I think it will help for debugging and testing gallery speed.

Just change username in users table.
http://remdex.info/examples/ani15.tgz

I hope that this information will help to archive better speed with coppermine. Main thing i was missing, was just indexes in main pictures table. And all queries not using indexes can be easily grabbed using log-queries-not-using-indexes variable in my.cnf

Then it's very easy to optimize most of the queries just by watching mysql query log.

Because currently I use my own developer gallery I do not see a point to post link to it or should I?

Bye.

[Joachim Müller]

I do not see a point to post link to it or should I?

Yes, you should (as suggested three times already):

Would be even more helpfull if you told us the link to that gallery. In fact that's even mandatory when posting as per board rules.

Please post a link as suggested.

and

I do not want to paste link to gallery because it's adult site.

That's not a problem as long as you respect board rules:

8. Post links
When posting a support request, it's helpfull for supporters in most cases to see what the actual page looks like, that's why it's mandatory that you always post a link to your coppermine gallery page when asking for support.

If your site contains adult content, make sure to post a warning together with your link!
Supporters might get into issues if they accidentally visit a page that interferes with their employer's internet policy. If your site contains adult content (even mild nudity even falls into this category), post the words "not safe for work" next to the link.

Yes, yes, yes: we want you to post a link to your gallery. We need you to post a link to your gallery. We won't be looking into your suggestions if you don't. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Post a link to your gallery. Enough?

[remdex]

Hi,

Ok 
Currently gallery uses.
Lighttpd
Mysql 5.1.x
APC Accelerator
Memcache
Sphinx - i have beed integrated sphinx and in coppermine gallery. If anyone interesting i can give a patch.

Album and category pages produces around ~200 #/s. Cached page produces output in 0.005 seconds around. You can do not notice this number because page is also cached with headers using ETag.

http://animenly.org - around 70 000 images. Safe for work

http://hentai-wallpapers.com - around 140 000 images. [Not safe for work]

Is it ok now ?

Bye,

[Joachim Müller]

http://animenly.org

Link doesn't work for me. You probably meant http://animeonly.org/

Is it ok now ?

No, not OK: the "Powered by Coppermine" tag is not there. It's a license fraud to use Coppermine without the tag (see http://coppermine-gallery.net/demo/cpg14x/docs/index.htm#copyright_additional_preservation). Restore the footer or stop using the application. And stop saying that it is your application

powered by http://fof.remdex.info

, while it isn't!

[remdex]

Hi,

1. Sorry it was http://animeonly.org misspelled

Hi,

Sorry did not now that i can post links to adult gallery. Anyway it's a dump of 1.5 gallery with 110 000 images. I think it will help for debugging and testing gallery speed.

Just change username in users table.
http://remdex.info/examples/ani15.tgz

I hope that this information will help to archive better speed with coppermine. Main thing i was missing, was just indexes in main pictures table. And all queries not using indexes can be easily grabbed using log-queries-not-using-indexes variable in my.cnf

Then it's very easy to optimize most of the queries just by watching mysql query log.

Because currently I use my own developer gallery I do not see a point to post link to it or should I?

Bye.

Link doesn't work for me. You probably meant http://animeonly.org/
No, not OK: the "Powered by Coppermine" tag is not there. It's a license fraud to use Coppermine without the tag (see http://coppermine-gallery.net/demo/cpg14x/docs/index.htm#copyright_additional_preservation). Restore the footer or stop using the application. And stop saying that it is your application, while it isn't!

You should read more carefully. Like i wrote it's no more Coppermine gallery engine. That's why it does not have anything to do with Coppermine gallery and it's copyrights...

It should be ok now. I just sharing my experience about performance...

[Αndré]

Because currently I use my own developer gallery

Sounds like you talk about a local development instance of cpg. So I can understand Joachim's reply.

Thanks for sharing your experience with us.

[remdex]

Yes,

While developing gallery from zero, just waned to share some information that could be helpful improving coppermine performance...