Big Queries

[DST Daddy]

Here are my photopost stats

Current Version 5.62
Registered Users 2,287
Comments/Ratings 313,953
Image Space Used 5,205.1 mb
Total Photos 57,347
Total disk space used by the DATA directory 6,453.78 mb
Number of Albums 400
Photos in Albums 4701
Posts in Albums 20203
ECards in Queue 0
Awaiting Approval 0
Total Views 2,914,191


I don't mean to bash the person(s) who wrote these queries, because clearly photopost is a great solution for a lot of people, and you can't just go around charging $130.00 for crap software and expect people to pay it.


But when looking at my situation, I just don't see why it is necessary for these queries to return 50,000 and 300,000 records to php for processing. I don't even know why the queries are examining 100,000 & 600,000 records either.

These queries are ran very VERY frequently on the site (www.digishoptalk.com/gallery)

# User@Host: digi_gallery[digi_gallery] @ localhost []
# Query_time: 12 Lock_time: 0 Rows_sent: 52465 Rows_examined: 104930
SELECT id,user,userid,date FROM pp_photos WHERE cat=500 ORDER BY date DESC;


# User@Host: digi_gallery[digi_gallery] @ localhost []
# Query_time: 21 Lock_time: 0 Rows_sent: 292273 Rows_examined: 605942
SELECT username,id,date,photo FROM pp_comments WHERE cat=500 ORDER BY date DESC;


I just don't see why it is necessary for photopost to request every record in the pp_comments table & pp_photos tables for a specific category. I can't find any page or view that actually will display all 50,000 photos or all 300,000 comments. It all seems like queries that were written without any scalability in mind. Or written with the idea that "no one will ever have that many comments/photos in one category"


I can disable photopost in the admin and my system load goes down to near zero, I can then re-enable photopost and system load is off the charts. And I'm not using any kind of slouch machine. It's a Dell server (an actual server, not just a desktop machine being used as a server), Intel(R) Xeon(TM) CPU 3.00GHz, with 2 gig of ram, with sata drives in a hardware raid 1 configuration.


All of the above leads me to believe that the root problem is photopost. The other thread I've started on this topic has yielded some good advice, and some of the things have marginally helped, but I kind of feel that the difficult questions have been blown off.


I'm also running vBulletin (not integrated with photopost), vBulletin has 3000 users, 26,000 threads & 300,000 posts. And nothing about vBulletin is running slow, and from what I can tel, nothing about vBulletin is requesting from the database the amount of data that photopost is (best I can tell).


we are going to break things out into categories, which will probably help significantly, but doing that seems like only a band aid to the real problem. Because what do we do when each of the new categories reaches 300,000 comments and 50,000 photos?


if any other users with similar levels of comments/posts could please chime in also, perhaps there is some server configuration I can implement to help out.


-Nick
DigiShop Talk - Server Admin

[DST Daddy]

I'd like to post in addition to Nick's post as the owner of DigiShopTalk and add that the solution to break things into categories is very undesirable to our users. It has always been preferred to keep our categories as minimal as possible for easier navigation.

This morning we're finding that since the gallery update, any photos that were added into a category other than the Member Gallery no longer shows up in that member's main gallery. Maybe there is a setting we can adjust to fix this? None of our users would be happy using categories if it means that their photos won't still show up in their main gallery.

Shannon - DST Owner

[Michael P]

By it's very nature; PhotoPost is designed to work in categories, just as vBulletin is designed to work with forums. Do you have one forum on your site that you have all your users post in?

PhotoPost installs with most of its featured enabled; something that the vast majority of our users want because it gives their users as many features as possible. As a site grows, it may become necessary to look at how you have your gallery laid out and which options you have enabled. It may also require additional horsepower (like a Dual Xeon setup, or more than 2gb of memory).

vBulletin doesn't provide for features like film strips (or thread previews of the next and previous X threads) which require additional processing.

If you have support issues; please post in the respective forums, but before you go insulting the developers you might want to rethink your post before hitting submit.

[DST Daddy]

Originally Posted by Michael P

If you have support issues; please post in the respective forums, but before you go insulting the developers you might want to rethink your post before hitting submit.

Please move this topic to the appropriate forum. I didn't see a specific section titled "support"... But I do now see that the entire lower section of the main forum page is under a "support" category. It did seem strange to me that there wasn't a section for "support", I guess I just missed the boat on that one.

Originally Posted by Michael P

By it's very nature; PhotoPost is designed to work in categories, just as vBulletin is designed to work with forums. Do you have one forum on your site that you have all your users post in?

There is not one single topic in vBulletin that everyone posts under, but if there were vBulletin probably would select every post in that topic while only viewing a page of 20 or so posts.

Originally Posted by Michael P

PhotoPost installs with most of its featured enabled; something that the vast majority of our users want because it gives their users as many features as possible. As a site grows, it may become necessary to look at how you have your gallery laid out and which options you have enabled. It may also require additional horsepower (like a Dual Xeon setup, or more than 2gb of memory).

PhotoPost has a great set of features no doubt. As the site grows I also agree that it may be necessary to increase the horsepower. We can add additional categories initially, but what do we do once those categories begin to fill up? Throwing additional cpu cycles in the mix will help in the short term, but not in the long.

Originally Posted by Michael P

vBulletin doesn't provide for features like film strips (or thread previews of the next and previous X threads) which require additional processing.

Exactly why we use PhotoPost and not vBulletin for images.



My frustration is with the fact that I have pointed out what I feel is a blaring performance/design issue, and all I've received in response are suggestions to turn off the very features of PhotoPost that the users like and want. My difficult questions have been avoided/ignored. Nothing in any responses have even hinted that anyone is looking into the issue, or even that there is an issue with PhotoPost.

I understand that PhotoPost is designed with for a specific purpose, and has been designed by a talented group of people (companies aren't built by bumbling idiots). To fulfill that design, there are certain assumptions about how the end project will be used. Once we as users cross that assumption line, we are now using the software in a way it wasn't designed to be used. And that isn't something we can hold the designers accountable for.

I don't want to insult anyone, and perhaps my criticism should have been better worded. I didn't write PhotoPost, so I don't know why these queries are being ran. Also, I don't claim to know it all, and don't claim to know the solution. All I see is that it appears these queries are needlessly adding excessive database load, and to me that is a big problem.


Please help

-Nick
DigiShop Talk - Server Admin

[Michael P]

Nick,

Your first post wasn't written as a request for support - you seem to be asking for opinions of other users.

PhotoPost has a long history of development and working with our users to get the most out of our software. If your criticism is about a query or two; let's discuss those queries and how to better tailor them for your setup. While these may be insignificant on many users site, clearly it isn't on yours and we can work on alternatives. But I would expect it to be done in a non-confrontational manner.

We may not end up rewriting entire sections of code for every issue you report; but we can try to help when we can. However, some of the burden is on you to make sure that as your site grows you are also providing the hardware necessary to support a larger system.

I will post some code suggestions shortly.

[Michael P]

In pp-inc.php at line 2869 (in upgradecategories function), I made the changes in bold:

Content visible to verified customers only.

This should limit the returns by using 4 smaller queries instead of 2 bigger ones while giving the same results.

[DST Daddy]

Thank you SO much!!

One other thing, in the code that you posted, for the $query1 and $query2, I didn't have the 'limit 1' directive in the sql statement, and I am running 5.62. So that text should be bold as well (to make it easier for future reference)


Saving the code update....

-Nick

[Yellowspurs]

I have a gallery of approximately 20,000 photos and have to move from two different hosts due to big querries issue also. I've since then move to a more powerful server and are ok right now.

Thank you Nick for bringing this up and Michael for the solution.