Occasional slow page, is it my webhost causing problem?

[sharpo]

Much of the time my Gallery works very well, but there are times when it can take 3 or 4 minutes for a page to be displayed. I think the problem is with my webhost, but I would appreciate your thoughts on the matter please.

This is the index.php downloading normally:-
<<Page generated in 0.547 seconds - 204 queries in 0.329 seconds - Album set : ; Meta set: ;>>

This is one of the occasions when the index.php download has taken a long time:-
<<Page generated in 110.295 seconds - 204 queries in 110.043 seconds - Album set : ; Meta set: ;>>

I have compared the rest of the debug info and for the "quick" pages the timings are 0s or .001s but for the slow pages some of the timings are several seconds as in the examples below:-

fast:-
    [106] => SELECT aid FROM cpg143_albums WHERE category = 44 (0.001s)
    [107] => SELECT count(*) FROM cpg143_pictures as p, cpg143_albums as a WHERE p.aid = a.aid AND approved='YES' AND category = 44 (0s)
    [108] => SELECT cid, name, description, thumb FROM cpg143_categories WHERE parent = '44'  ORDER BY pos (0.001s)
    [109] => SELECT aid FROM cpg143_albums WHERE category = 18 (0s)
    [110] => SELECT count(*) FROM cpg143_pictures as p, cpg143_albums as a WHERE p.aid = a.aid AND approved='YES' AND category = 18 (0.001s)
    [111] => SELECT cid, name, description, thumb FROM cpg143_categories WHERE parent = '18'  ORDER BY pos (0s)
    [112] => SELECT aid FROM cpg143_albums WHERE category = 61 (0.001s)
    [113] => SELECT count(*) FROM cpg143_pictures as p, cpg143_albums as a WHERE p.aid = a.aid AND approved='YES' AND category = 61 (0s)
    [114] => SELECT cid, name, description, thumb FROM cpg143_categories
slow:-
    [106] => SELECT aid FROM cpg143_albums WHERE category = 44 (3.408s)
    [107] => SELECT count(*) FROM cpg143_pictures as p, cpg143_albums as a WHERE p.aid = a.aid AND approved='YES' AND category = 44 (0.783s)
    [108] => SELECT cid, name, description, thumb FROM cpg143_categories WHERE parent = '44'  ORDER BY pos (2.15s)
    [109] => SELECT aid FROM cpg143_albums WHERE category = 18 (3.641s)
    [110] => SELECT count(*) FROM cpg143_pictures as p, cpg143_albums as a WHERE p.aid = a.aid AND approved='YES' AND category = 18 (1.174s)
    [111] => SELECT cid, name, description, thumb FROM cpg143_categories WHERE parent = '18'  ORDER BY pos (3.608s)
    [112] => SELECT aid FROM cpg143_albums WHERE category = 61 (1.65s)
    [113] => SELECT count(*) FROM cpg143_pictures as p, cpg143_albums as a WHERE p.aid = a.aid AND approved='YES' AND category = 61 (3.587s)
    [114] => SELECT cid, name, description, thumb FROM cpg143_categories

I have also found that while I am waiting for a page to load, I can use ftp to upload .jpg files to my webspace without problems, and also view my old sites that don't use Coppermine. This makes me think it might be the MySQL database is working slowly, perhaps on a busy server??

Any help appreciated, thanks for your time.

http://www.sharpos-world.co.uk/cpg/index.php

Shortly after posting the above I had a very long wait for the page to download:-
<<Page generated in 346.171 seconds - 204 queries in 330.323 seconds - Album set : ; Meta set: ;>>

[Sami]

As you said it's mysql issue
- Enable debug mode for everyone
- Search the board for "tuning" , you'll find posts about that like this one:
http://forum.coppermine-gallery.net/index.php?topic=30233.msg145702#msg145702

[sharpo]

Debug mode enabled.

It has been running very slow this evening

Sharpo

[sharpo]

Just had to disable debug, as when I logged out of admin the page stretched to about 9 screens width.

Is it any help if I post a "quick" debug printout, which you can compare with the one which took 6 minutes, and I can't see any passwords on it - is there anything to *** (delete) before postsing? I can't see any passwords.

Sharpo

[Sami]

it's okey , you can disable it
you can remove userid , username ....

[sharpo]

Ive tried posting a debug print but it takes me over the character limit, and I can't leave debug enabled as it makes the gallery impossible to view for visitors. Can I e-mail you a debug print from when it took 349 seconds for the page to load?

Sharpo

[Sami]

Your gallery was fine , the issue is your server , as it seems (server gets busy on peak time and give you poor performance)
BTW I didn't see the issue personally

[sharpo]

thanks Sami, I'll get onto my host, again!!

Sharpo

[sharpo]

This is what my host said:-  "Since your database is hosted on a shared server,
there may be times which the load will spike causing temporary delays.
This is the nature of shared hosting. If you require better performance,
I would suggest a dedicated server."

As I have upto 8 MySQL databases available, I created another one & moved my CPG database across to it & the gallery now seems to run faster.

Problem solved.

Sharpo

[sharpo]

A further final comment, my ISP eventually admitted that there were some heavy users on the server where my database was, but they have now been moved.

So I was right all the time, the problem was with their server which hosted the MySQL database.

Sharpo