Slow Queries

[rsj1]

Hi
This particular query keeps showing up in my slow query log

Content visible to verified customers only.

That's 14 occurrences of it within an hour

I believe that a query that examines 436,852,647 rows is either a bug or very unoptimised.

This also appears to be what is causing the mysql on my server to max out using 350-400%cpu (4 processors) and increasing server load to 8+

here is an excerpt from a "mysqladmin processlist" ssh command

Content visible to verified customers only.

and the same command run a few minutes later - as you can see, many of the same queries are still there.

Content visible to verified customers only.

Information:
Server Software: CentOS V5 32bit
CPU: AMD Phenom II X4 945 (3ghz per core)
RAM: 8GB
PHP version: 5.2.12
PHP Extensions: Xcache 1.3.0, ioncube loader 3.3.0, zend optimiser 3.3.9, suhosin 0.9.29.
mySQL version: 5.0.89
Photopost version: PhotoPost PHP Pro v7.01, Build: 1/21/10 5:54 PM
Most used photopost installation information:
Users 31,902
Photos 13,935
Comments 106,323
Views 20,619,882
Disk Space 2,772.7mb

my.cnf (mySQL config file) contents:

Content visible to verified customers only.

Further mySQL information from tuning-primer.sh ( Sundry MySQL Scripts and Docs )

Content visible to verified customers only.

[Michael P]

This query comes from the showmembers.php script and is used when you have showing a members last thumbnail enabled - you can try turning that option off to remove the overhead for now.

I don't have a quick answer for you on how this could be changed to be faster.

[rsj1]

Thankyou. I have disabled that and will test things to see if I still get any slow query issues from photopost. What exactly have I just disabled? I can't seem to find any differences.

It seems that when the query was run once it would be below the slow query time, only when it was run multiple times in short succession did it cause these load issues. I would assume that the number of temporary tables it created on disk would saturate the IO and make everything begin to back up on the server.

[Chuck S]

There will be no thumbnail showing when you view members galleries

[rsj1]

I assume that's within the gallery list view? To be honest I'm not sure how that code was even executed - I have the listing view disabled, or at least the links to it. Perhaps there are other areas it's linked to. I'll need to have another look through the templates.

[Chuck S]

The query Michael noted is ran to grab the members last photo upload. If you elect to not show the thumbnail on the members gallery then that query is not ran.

Admin => Show Photo Options => Show last photo thumbnail in member galleries listing?

[rsj1]

I've also had to implament an htaccess fix to another bug in your coding, my error log was getting spammed with thousands of 404's like:

Content visible to verified customers only.

All were those same two picture files, and all were /gallery/[name of gallery]/[picture file]

I had to insert this into the htaccess file to fix this.

Content visible to verified customers only.

[Chuck S]

thats probally a bug in your server apache handling etc

Our code does not link images like that. The link to an actual image in our code is like this

regular image

/gallery/data/catid/image.jpg

thumbnails

/gallery/data/catid/thumbs/image.jpg

medium image

/gallery/data/catid/image.jpg

Thats an absolute there. Look at our coding and you will see what I am saying. The reference your talking about simply doesnt exist in our code. Nowhere do we reference the gallery then a category name them the actual file as some file link in our code.

We have SEO urls that reference category name in a picture page

12 year old granddaddy clown - ReefTalk Gallery

As you can see thats not a link to an image thats an html page. Thats the only type of url you are going to see with a catname in it and that essentially translates simply to this url

12 year old granddaddy clown - ReefTalk Gallery