Gallery is very slow - had to close it to keep site up and running ):

[glowinggal]

Our entire since we had an issue with our site since we upgraded to 7.0.2.

Our tmp directly also filled up and generated some errors on the site/gallery and not being able to write to the tmp directory.

Ever since occurred the gallery has been so slow to the point I have to turn it off otherwise it drags the entire site/server down.

Have already optimized the database several times.

we also ran a slow query mysql log we can provide.

Please let me know what info I can provide you with to see if we can get it resolved... our site has been in a bad state for about a week or so now due to the gallery.

__________________________
Web Server Apache v1.3.42
PHP 5.2.14
MySQL Version 5.0.91-community-log
__________________________
our hosting/server maintenance says:

For example, here's queries that took over 100 seconds

# Query_time: 115 Lock_time: 0 Rows_sent: 300464 Rows_examined: 600928
# Query_time: 172 Lock_time: 0 Rows_sent: 298789 Rows_examined: 599253
# Query_time: 167 Lock_time: 0 Rows_sent: 298789 Rows_examined: 599253
# Query_time: 159 Lock_time: 0 Rows_sent: 298789 Rows_examined: 599253
# Query_time: 160 Lock_time: 0 Rows_sent: 298789 Rows_examined: 599253
# Query_time: 117 Lock_time: 0 Rows_sent: 298789 Rows_examined: 599253
# Query_time: 176 Lock_time: 0 Rows_sent: 298789 Rows_examined: 599253
# Query_time: 232 Lock_time: 0 Rows_sent: 218 Rows_examined: 300682
# Query_time: 253 Lock_time: 0 Rows_sent: 298789 Rows_examined: 599253
# Query_time: 211 Lock_time: 0 Rows_sent: 298789 Rows_examined: 599253
# Query_time: 214 Lock_time: 0 Rows_sent: 298789 Rows_examined: 599253
# Query_time: 173 Lock_time: 0 Rows_sent: 298789 Rows_examined: 599253
# Query_time: 151 Lock_time: 0 Rows_sent: 298789 Rows_examined: 599253
# Query_time: 132 Lock_time: 0 Rows_sent: 298789 Rows_examined: 599253
# Query_time: 154 Lock_time: 0 Rows_sent: 298789 Rows_examined: 599253
# Query_time: 129 Lock_time: 0 Rows_sent: 300464 Rows_examined: 600928
# Query_time: 193 Lock_time: 0 Rows_sent: 298789 Rows_examined: 599253
# Query_time: 129 Lock_time: 0 Rows_sent: 298789 Rows_examined: 599253


show it to your programmer or software vendor. See if they can optimize or cache any of the queries. This is where all usage and load is coming from.
__________________________

Our hardware stats:

Processor #1 Vendor: GenuineIntel
Processor #1 Name: Intel(R) Xeon(R) CPU X3220 @ 2.40GHz
Processor #1 speed: 2394.140 MHz
Processor #1 cache size: 4096 KB

Processor #2 Vendor: GenuineIntel
Processor #2 Name: Intel(R) Xeon(R) CPU X3220 @ 2.40GHz
Processor #2 speed: 2394.140 MHz
Processor #2 cache size: 4096 KB

Processor #3 Vendor: GenuineIntel
Processor #3 Name: Intel(R) Xeon(R) CPU X3220 @ 2.40GHz
Processor #3 speed: 2394.140 MHz
Processor #3 cache size: 4096 KB

Processor #4 Vendor: GenuineIntel
Processor #4 Name: Intel(R) Xeon(R) CPU X3220 @ 2.40GHz
Processor #4 speed: 2394.140 MHz
Processor #4 cache size: 4096 KB


Memory Information

Memory: 3759416k/4194304k available (2043k kernel code, 39180k reserved, 846k data, 232k init, 2882432k highmem)


System Information

Linux cp.scrapbook-bytes.com 2.6.18-8.1.1.el5 #1 SMP Mon Apr 9 09:46:54 EDT 2007 i686 i686 i386 GNU/Linux


Physical Disks

SCSI device sda: 625142448 512-byte hdwr sectors (320073 MB)
sda: Write Protect is off
sda: Mode Sense: 00 3a 00 00
SCSI device sda: drive cache: write back
SCSI device sda: 625142448 512-byte hdwr sectors (320073 MB)
sda: Write Protect is off
sda: Mode Sense: 00 3a 00 00
SCSI device sda: drive cache: write back
sd 0:0:0:0: Attached scsi disk sda
sd 0:0:0:0: Attached scsi generic sg0 type 0


Current Memory Usage

total used free shared buffers cached
Mem: 3762084 3330660 431424 0 61224 2866040
-/+ buffers/cache: 403396 3358688
Swap: 4096564 60 4096504
Total: 7858648 3330720 4527928


Current Disk Usage
Filesystem Size Used Avail Use% Mounted on
tmpfs 1.8G 0 1.8G 0% /dev/shm

============================

[Chuck S]

what are the actual queries?

There are lots of options in the program have you viewed the performance faq in the faq forum.

[glowinggal]

yes but will double check them and post back.

[glowinggal]

ok applied a few others listed there we hadn't done yet - will post an update back when I see how it runs this way for a bit.

For anyone that finds this and needs reference: the FAQ were here (:

[glowinggal]

seems to be a bit better but when leaving some comments via the quick box is about 30 seconds or more to process?

[glowinggal]

ok for leaving comments - i viewed the slow log as soon as the site got hung up and this is what is showed:

# Time: 100910 13:04:17
# User@Host: [omitted] @ localhost []
# Query_time: 86 Lock_time: 0 Rows_sent: 298870 Rows_examined: 599425
SELECT id,cat,title,bigimage,userid,approved,storecat,height,width,ismature FROM photos WHERE approved=1 AND storecat=0 ORDER BY date DESC;


# User@Host: [omitted] @ localhost []
# Query_time: 89 Lock_time: 0 Rows_sent: 298870 Rows_examined: 599425
SELECT id,cat,title,bigimage,userid,approved,storecat,height,width,ismature FROM photos WHERE approved=1 AND storecat=0 ORDER BY date DESC;


# Time: 100910 13:04:44
# User@Host: [omitted] @ localhost []
# Query_time: 95 Lock_time: 68 Rows_sent: 298870 Rows_examined: 599425
SELECT id,cat,title,bigimage,userid,approved,storecat,height,width,ismature FROM photos WHERE approved=1 AND storecat=0 ORDER BY date DESC;


# Time: 100910 13:05:01
# User@Host: [omitted] @ localhost []
# Query_time: 4 Lock_time: 0 Rows_sent: 5 Rows_examined: 10
SELECT id,username,userid,date,rating,comment,approved,ipaddress FROM comments WHERE photo=53170 AND LENGTH(comment) > 0 ORDER BY date ASC;


# Time: 100910 13:05:16
# User@Host: [omitted] @ localhost []
# Query_time: 32 Lock_time: 0 Rows_sent: 298870 Rows_examined: 599425
SELECT id,cat,title,bigimage,userid,approved,storecat,height,width,ismature FROM photos WHERE approved=1 AND storecat=0 ORDER BY date DESC;

[Chuck S]

# Time: 100910 13:04:44
# User@Host: [omitted] @ localhost []
# Query_time: 95 Lock_time: 68 Rows_sent: 298870 Rows_examined: 599425
SELECT id,cat,title,bigimage,userid,approved,storecat,height,width,ismature FROM photos WHERE approved=1 AND storecat=0 ORDER BY date DESC;

Thats the only slow query so this is viewing a photo. Have you tried turning off the next prev thumbnails in showphoto options.

[glowinggal]

it says that' is already set to "no"

[Chuck S]

well then your quoting earlier queries because the query I noted only runs when you have next prev thumbs turned on. So if that is the query that your site is having issues with turning off that feature should solve your issue.

[glowinggal]

that query is from today and I never changed that option - when I went to check it it was showing as already off? Is there a possibility it's not actually turned off when it should be? or it's still running somehow?

[glowinggal]

ie: right now our entire site is taking nearly a minute to even connect - this only occurs when the gallery is activated ):

I am going to try and turn it on and back off again and see what happens? I don't see why it would make a difference but I've seen crazier things.

[Chuck S]

what is the url of your site?

[glowinggal]

ok i enabled it and whoa! we def. didn't have it enabled earlier [it showed a TON of photos!] with a more link at the right... but then disabled it again and it went back to one image on the page with just "next image" text/link at the top.

[glowinggal]

Digital Scrapbooking Inspiration - Digital Scrapbooking Layouts

[glowinggal]

here is a new query i just pulled:

# Time: 100910 17:41:52
# User@Host: [removed] @ localhost []
# Query_time: 516 Lock_time: 171 Rows_sent: 216 Rows_examined: 251561
SELECT id,cat,title,bigimage,userid,approved,storecat,height,width,ismature FROM photos WHERE cat=500 AND approved=1 ORDER BY disporder,date DESC;

[glowinggal]

I've ended up turning the gallery off for the time being - it's causing so much down time for us at this point until we can resolve somehow.

[Chuck S]

well you can sent us a url admin login and ftp info so I can look at it.

It seems your whole issue is one query

[glowinggal]

agreed - we also spent the weekend moving photos to other categories thinking that might be the issue; but alas this morning when we turned the gallery back on - it was still hanging up when leaving comments.

for instance i just posted a comment to a photo and it's been processing for about 3 minutes now; just timed out and sent server load to 12+ -- went back and check once the load went back down and the comment did in fact post though.

I also noticed this morning it happened when viewing a photo but not sure if that is what prompted the hang up or if someone was commenting in the gallery at the same time.

Our mysql-slow.log is still running since this started if you would like us to include a link to grab that as well.

Once this usually occurs it takes a few minutes for everything to settle down and the load resume to normal before the gallery will come back up properly.


Where/how do i send the log in details?

[glowinggal]

also we had set up a test gallery with very few photos in it; data; and it processes VERY quickly the information, images, commenting, etc. -- not sure if that helps with anything but i wanted to at least see how it worked with little data compared to the volumes of data we have in our current gallery.

[Chuck S]

feel free to send the info through the contact us link in our header