BIG queries are killing my server

PhilipJCaputo · November 27th, 2006, 04:45 PM

I've got the following queries running quite regularly

SELECT username,id,date,photo
FROM pp_comments
ORDER BY date DESC

SELECT id,bigimage,cat,userid,approved,storecat,height,width
FROM pp_photos
WHERE cat=500 AND approved=1
ORDER BY disporder,date DESC

if I replace the select line with a count(*) the first query returns a count of 238,344 records, and the second query returns a count of 41,302.

what can I do to limit the number of records these queries return? And or what pages would run these queries?

Michael P · November 27th, 2006, 04:47 PM

Your account is not showing up as a Verified Customer; in order to obtain support we'll need you to update your email address to match your license so we can verify your status.

PhilipJCaputo · November 27th, 2006, 04:50 PM

Quote:

Originally Posted by Michael P

Your account is not showing up as a Verified Customer; in order to obtain support we'll need you to update your email address to match your license so we can verify your status.

I admin the server.... I will contact the registered user.

DST Daddy · November 28th, 2006, 05:24 PM

he hosts my sites - i'll repost it:

I've got the following queries running quite regularly

SELECT username,id,date,photo
FROM pp_comments
ORDER BY date DESC

SELECT id,bigimage,cat,userid,approved,storecat,height,width
FROM pp_photos
WHERE cat=500 AND approved=1
ORDER BY disporder,date DESC

if I replace the select line with a count(*) the first query returns a count of 238,344 records, and the second query returns a count of 41,302.

what can I do to limit the number of records these queries return? And or what pages would run these queries?

Michael P · November 28th, 2006, 05:41 PM

I don't recommend modifying any of our queries; if you do, you do so at your own risk. Both queries serve specific purposes in our code and I don't have alternatives for you to substitute them with.

DST Daddy · November 28th, 2006, 06:38 PM

The site is 5 months old, already there are 240,000 comments and 45,000 photos. Assuming no growth (which isn't likely), these queries which are sorting through 240k rows and 45k rows now will be sorting through 550k rows and 108k rows in another 7 months.

Please help us find a solution that scales.

Michael P · November 28th, 2006, 06:46 PM

I would suggest trying to make sure that you are using the categories efficiently; one category with > 50k images isn't very efficient. We are looking at ways to improve some of our queries for larger sites, but PhotoPost was designed with many features for the large majority of our customers; larger sites, to be more efficent, need less options.

For example, we could easily reduce the size of queries if we didn't allow categories to be sorted - just limiting the display to the photo date would allow us to limit our queries based on the date of the photo being displayed - but when you allow the flexibility of sorting a gallery and then having that sort "stick" while viewing photos, well, then its going to use more resources.

It's not that PhotoPost can't scale to large systems, it's that you need to manage your options accordingly and possibly even look at pulling back on some of the options to allow even greater scalability.

DST Daddy · November 28th, 2006, 07:56 PM

Quote:

Originally Posted by Michael P

I would suggest trying to make sure that you are using the categories efficiently; one category with > 50k images isn't very efficient.

This is something we can look into, but you can only break up 50k photos into so many categories before there are too many categories.

I have traced both queries down to this block of code in pp-inc.php starting on line 2714.

Code:

Content visible to verified customers only.

what can I do to make "if ( $id != 500 )" evaluate to true? those queries are much more efficient.

Michael P · November 28th, 2006, 07:57 PM

I don't have any recommendations that would give you the same functionality by changing those lines.

These are not frequent queries.

DST Daddy · November 28th, 2006, 08:29 PM

any chance I would see some performance improvements with an upgrade?

looks like we're currently running 5.31

Database Statistics
Current Version 5.31
Registered Users 2,026
Comments/Ratings 240,800
Image Space Used 4,112.1 mb
Total Photos 45,345

Total disk space used by the DATA directory 5,121.61 mb
Number of Albums 342
Photos in Albums 3589
Posts in Albums 15552
ECards in Queue 0
Awaiting Approval 0
Total Views 2,134,360

Michael P · November 28th, 2006, 08:32 PM

Yes, you would see improvements as we've made enhancements to the showmembers page to significantly reduce the queries used to generate that page.

DST Daddy · November 28th, 2006, 08:37 PM

Can I jump right from 5.31 to 5.61 with the most recent update, or do I first need to hit an intermediate version?

Michael P · November 28th, 2006, 09:22 PM

You upload the 5.61 files and run the upgrade.php doing each of the upgrades sequentially.

DST Daddy · January 5th, 2007, 08:54 AM

I finally got around to updating photopost, am now running 5.62.

several queries run VERY regularly which a month ago only had to sort through 240,000 records, now the queries sort through 300,000 records.

doing a "show full processlist" on my mysql server I find 4 instances of this query

"SELECT username,id,date,photo FROM pp_comments ORDER BY date DESC"

which selected 305,773 records and sorts them by date (a field without an index)...

another query which comes up quite regularly is "SELECT id,user,userid,date FROM pp_photos ORDER BY date DESC" or a variation which looks similar to "SELECT id,user,userid,date FROM pp_photos ORDER BY date DESC" these queries sort through 50,000 + records each, again on date... which is not in indexed field.

what should the next step be? (we are working on getting things broken down into multiple categories)

Chuck S · January 5th, 2007, 09:13 AM

As Michael states above limit your options.

If you do not want those specific queries to run when updating your category counts then in admin => showgallery options turn off this setting

Do you want Members Gallery to show all of a users photos?

DST Daddy · January 5th, 2007, 09:45 AM

Quote:

Originally Posted by Chuck S

Do you want Members Gallery to show all of a users photos?

not if it runs a big query

Chuck S · January 5th, 2007, 10:11 AM

Correct if you set that option to YES you are telling our program when we update the category photo and comment counts for the members gallery to scan the entire table.

Therefore if you set it to NO then it will only scan a count from category 500 which is the members gallery

It is all about options. Here are some other tidbits to turn off to save processing

http://www.photopost.com/forum/showt...38#post1113838

DST Daddy · January 5th, 2007, 10:34 AM

Thanks... I will look into it...

I have turned on the slow queries log in mysql, and am seeing lots of queries returning over 50k records and over 290k records

I know that there are not any pages which show 50k records or 290k records on them..... can't these queries be limited to return a smaller record set? Most of what is being returned is being disregarded by php anyway.