Share your optimization tips

[copperminepro]

Share your tips/modifications here for optimizing the performance of your gallery. As of now, there is no dedicated performance thread on this forum that I am aware of. This is not a support thread. Just post your tips and improvements upon others tips.

I've started a quest this afternoon to reduce the server load created by my gallery... My gallery has 45k pictures, 14k users, 8k+ uniques on a weekday, 120k impressions a day. My server also runs a forum for said site (phpbb), but there is a lot more out there in regards for optimization of phpbb.

I'll be posting some of the stuff I have done below. I'm planning on hiring someone to make use of indexes on most queries, and overall improve most of the troublesome queries. I'm not good with MySql, so I can't guarantee anything I have done below. It works on my gallery and site, it might not on yours.

As of now, my load has dropped from 95% avg cpu usage to 80% average cpu usage. I'll record the queries I am having optimized and post them here in a week or two.

[copperminepro]

1.) Remove all keywords from albums/categories if possible. Having keywords in an album/category spawns some very nasty queries.


2.) I noticed that on EVERY single page there was this query "SELECT COUNT(*) FROM cpg1411_pictures WHERE approved = 'NO'"
This query deals with admin approval of pictures. I don't approve pictures, so this query is completely useless. I found that removing it from functions.inc.php eliminated the query. This may or may not be a good idea, but the query took .018 seconds and that was on EVERY single gallery page. Removing that query saved a lot.

To eliminate this, find in functions.inc.php:

Code Select Expand

$result = cpg_db_query("SELECT COUNT(*) FROM {$CONFIG['TABLE_PICTURES']} WHERE approved = 'NO'");

Replace with:

Code Select Expand

$result = ' ';

This query also appears in editpics.php, though I left it there just in case it was used somewhere (didn't have time to look  , not to mention I am an idiot)


3.) Use query caching

You can enable query caching on your server by editing your my.cnf file...  http://www.techiecorner.com/45/turn-on-mysql-query-cache-to-speed-up-mysql-query-performance/ and http://www.cyberciti.biz/tips/enable-the-query-cache-in-mysql-to-improve-performance.html both explain this  in better detail than I ever could... For my site, I use a 64 mb query cache, which works for my site. You may need a significantly lower number, or higher depending on your specific site. I also run phpbb with caching, so i'm sure a lot of my query cache also involves phpbb queries. I may increase this number later as queries are improved and become cachable

[copperminepro]

Looking over my install to see what all I have changed that may be helpful for others.

Also, noticed i posted this in a locked forum     Could someone moved this to cpg1.4 miscellaneous?

[copperminepro]

4.) Limit last uploads and last comments to the first 10 pages. There is a mod for doing this somewhere, I have tried searching for it and can't locate it. This will save a lot if you are using last uploads and comments, as it won't have to look at every single picture (or something like that )

5.) Without starting to delve into mysql's slow query log, I have found the worst query visible in debug mode:
SELECT pid, filepath, filename, url_prefix, filesize, pwidth, pheight, ctime, aid, keywords, votes, pic_rating, title, caption,hits,owner_id,owner_name from cpg1411_pictures WHERE ((aid='13'  ) )   ORDER BY title DESC  LIMIT 0 ,12 (0.745s)

This query appears in one form or another on every thumbnail album. This is the worst query visible from the debug mode on the entire site, so I will be working on improving this query and providing a patch. Anything beyond this, such as digging into the slow query log and fixing those querys, will have to be performed by a freelancer. I'll try to provide an improvement upon this query within a couple days, or post that I'm an utter failure at life and MySql.

[Joachim Müller]

Also, noticed i posted this in a locked forum     Could someone moved this to cpg1.4 miscellaneous?

Done

[extremest]

One of the queries I was seeing in my site that hurt the most was this in Index.php.  It is to get the total views at the top of the page.  I have not seen any il effects from changing it, but it did reduce a 90 sec query down to almost nothing.

Code Select Expand

$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;

I changed it to this.

Code Select Expand

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

[Nibbler]

That will cause the view counter to include view counts from files the user cannot see. If you don't mind that little side effect then no problem.

You should remove that whole stats section if you want to boost performance.

[extremest]

I don't have any private groups so everything is fine for me, But I need the count.  Just haven't figured out how to remove the view part of the code just yet.  Too much else going on.

[Stramm]

Download the modpack and compare... there's a config option to disable private gallery sensitive stats. Just copy this parts...

[copperminepro]

5.) I have found a way to improve that query that MAY be useful for most users.

Query to improve:

Code Select Expand

SELECT pid, filepath, filename, url_prefix, filesize, pwidth, pheight, ctime, aid, keywords, votes, pic_rating, title, caption,hits,owner_id,owner_name from cpg1411_pictures WHERE ((aid='13'  ) )   ORDER BY title DESC  LIMIT 0 ,12 (0.745s)


What causes most of this slow-ness is the ORDER BY.. Order by hogs resources, so what we are going to do is REMOVE the sort order. Now, if you want to save weight in your php, remove all the sort order functions / etc.

Removing the sort order is going to cause the records to appear as they were accessed, so your oldest pictures will appear first. This doesn't work for my gallery, so I will be posting another method later. After than I'm going to attack the queries without indexes.

Open Functions.inc.php and find:

Code Select Expand

SELECT $select_columns from {$CONFIG['TABLE_PICTURES']} WHERE ((aid='$album' $forbidden_set_string ) $keyword) $approved $ALBUM_SET ORDER BY $sort_order $limit
Replace with:

Code Select Expand

SELECT $select_columns from {$CONFIG['TABLE_PICTURES']} WHERE ((aid='$album' $forbidden_set_string ) $keyword) $approved $ALBUM_SET $limit


Resulting query (under much higher load than the original query showed):

Code Select Expand

SELECT pid, filepath, filename, url_prefix, filesize, pwidth, pheight, ctime, aid, votes, pic_rating, title, caption,hits,owner_id,owner_name from cpg1411_pictures WHERE ((aid='12'  ) )    LIMIT 0 ,12 (0.001s)

It's far from perfect, but your looking at a 745% improvement. I haven't added an index on it yet, so I'm guessing that when I change the index from approved, the query will be even faster.

[dke]

Great thread, bookmarked it!

Ill try to find the optimizations ive found over the year!

[dke]

Here is a great optmization tip from a user on these boards, directly quoted.

[Edit GauGau] Removed the lengthy quote. Don't clutter our database with identical content. A plain link would have done the same: http://forum.coppermine-gallery.net/index.php/topic,15653.msg199421.html#msg199421 [/Edit]

I've used all these and the first one is really really really great for performance if you still want to keep statistics. A mod to keep all statistics static and updates from cronjobs would be amazing, however this only covers the frontpage statistics!

[Joachim Müller]

[moderation]
@dke: use some common sense and stop behaving so childish - see my note in your posting. Pasting hundreds of lines of another posting in is just silly. Readability of your quote is zero. If you want to contribute, contribute your own work, not the work of someone else!
[/moderation]