Index missing for 'approved' column?

bajabob · June 25th, 2007, 12:33 PM

I'm experiencing very high load spikes in my gallery (a gallery with over 250K images and has about 150-300 users online usually, runs on 2 clovertown quad core cpus with 6 gigs of ram, and scsi drives)
and the server tech guys said they see the following query runs a lot:

Quote:

SELECT id, bigimage, cat, userid, approved, storecat, height, width
FROM pp_photos
WHERE approved =1
ORDER BY...

and that it takes a lot of resources because they see that the `approved` column is missing an index.
Would adding an index to it help? or what do you suggest?
I also saw this post: http://www.photopost.com/forum/showthread.php?t=115029
Would it still be good? with a gallery that large I definitly need any performance tweaks.

Thanks.

Chuck S · June 27th, 2007, 05:01 AM

Yes you can try running an index on that field sure

This is the performance thread you should yse I think

PhotoPost Performance FAQ

Astaran · June 27th, 2007, 05:13 AM

I don't think an index on "approve" will make things faster. Assuming that most of your photos are approved, the index won't be used.

Chuck S · June 27th, 2007, 05:16 AM

Yep index's only help in that screnerio otherwise they are useless

The linked thread I linked should assist

bajabob · June 27th, 2007, 11:34 AM

Well actually I already did most of the suggestions in the FAQ. My gallery is very large (over 260K photos so far). Are there any more suggestions?

Another query I see locking the table is:
UPDATE LOW_PRIORITY pp_photos SET views = views+1.
Perhaps using low priority isn't good in busy galleries when there's always activity. Anything can be done here?

What do you think about the thread I mentioned? PhotoPost Performance FAQ
I just can't seem to get the server load low enough.

Also, as I removed the stats from the main page, it also removes the num of users online counter that is pretty important to me. Is there way to just keep the counter, but remove the stats? (disk usages etc)

Thanks for your help.

bajabob · June 27th, 2007, 10:22 PM

My server spiked again so I ran show processlist and found this query a lot:

Quote:

SELECT id,user,userid,cat,storecat,date,title,description,keywords,bigimage,width,height,filesize,views,medwidth,medheight,medsize,approved,rating,allowprint,numcom,lastposter FROM pp_photos WHERE approved < 2 ORDER BY disporder,date DESC

Looks like it's very server intensive. after the load calmed I ran it myself twice, and the results pretty shocked me: Showing rows 0 - 29 (263,231 total, Query took 1.9312 sec)
almost 2 seconds! then I ran it again (3rd time after a few mins) and i got 2.5 seconds! what can I do to avoid this query or optimize it? I guess its ordering 262K photos that takes so long, since when I removed it, it was fast (9 micro secs)

Where does such query even exists? notice it doesn't have any terms besides approved < 2...no cat or anything.

Astaran · June 28th, 2007, 02:31 AM

There's not much you can do about this particular query. If you're not using approval, you can remove the where condition, but that won't make a huge different. You can tune your mysql configuration so that the query will execute a bit faster.

You might be interested in this thread. It describes the current bottlenecks in photopost.

I use memcached to cache most of this data. Doing this, I was able to reduce the page generation time from about 8 seconds to 0.5 seconds on most pages.

bajabob · June 28th, 2007, 04:25 AM

Thanks I'll read through the thread, but can you explain where this query is used? Notice that its a query that just shows/sort all the photos - with basically no conditions - no specific cat, limits, or anything.

Michael P · June 28th, 2007, 10:51 AM

You didn't mention your hardware; as your site grows, you may need to have your resources grow with it. I've seen people with large galleries running on 5 year old server with 1gb of ram and the reality is these kinds of galleries when they grow that large will need more horsepower.

bajabob · June 28th, 2007, 04:53 PM

I did mention my hardware on the first post, but here are more details:
2 x Intel Xeon-Clovertown 5345-QuadCore
6 Gigs of ram
2 x 150 GB SCSI drives
Software:
Cent OS 4.5 (latest kernel)
PHP 4.4.7
MySQL 4.1.22
apache 1.3.3.7

Thank you.

Michael P · June 28th, 2007, 05:20 PM

Sorry, by bad; I didn't see that. Well, it wouldn't be a hardware issue; you seem to have that covered. 150-300 users online at a time would cause a higher load for sure, we'll have to look to see what techniques we can possibly apply to balance between alot of great features for smaller sites and a leaner set for more popular sites.

bajabob · June 28th, 2007, 07:26 PM

Anything I can do on my end or just wait for a gallery update? For not I pretty much turned anything server-intensive off but I still like some of them back - like the stats on top - I turned it off because they're pretty server intensive - I notice it actually counts the stats when the page loads, and with 262K photos it takes a while. so I can live without that but I still wanna know how many users are on my gallery, not even names needed, just a number would be ok.

Astaran · June 29th, 2007, 03:07 AM

Make sure your mysql server configuration is tuned, if you want to change the application code, implement some caching. It helped a lot on my site.

bajabob · June 29th, 2007, 05:24 PM

Thanks for the suggestions:
I've tuned it several times and it should be ok now. as for changing the application code I just did a small change to avoid running the query I mentioned before if there are not enough conditions. As for caching, is there anything simple you think I can do or do you mean like an entire caching system?

Thanks.

Astaran · June 30th, 2007, 03:32 AM

Check if you have turned on the mysql query cache.
I don't cache entire pages, because of the different user permissions. I implemented a cache that sits on top of the built-in database cache which caches the categories, category properties and the user permissions. I use memcached to store these values so that the database will only be hit if the cache invalidates. Took me about 6hours to get it right, because the built-in caching isn't encapsulated within specific functions. So I had to change several scripts to get it right.

ryand789 · September 10th, 2007, 06:44 PM

Quote:

Originally Posted by Astaran

Check if you have turned on the mysql query cache.
I don't cache entire pages, because of the different user permissions. I implemented a cache that sits on top of the built-in database cache which caches the categories, category properties and the user permissions. I use memcached to store these values so that the database will only be hit if the cache invalidates. Took me about 6hours to get it right, because the built-in caching isn't encapsulated within specific functions. So I had to change several scripts to get it right.

We could really use memcache on our site. Any plans to make this an option in PhotoPost Pro?

Ryan

Chuck S · September 10th, 2007, 07:17 PM

You would have to discuss with the individual in the post above what he did. We use our own cache system so I am unsure what he did to his site.