increacing random slow page load times

[BigBensDad]

helter we are begging for help and if you or the person you mentioned need access let us know who and when by email <a>richard@reynoldsproperties.net</a>

[Helter]

Ill contact him now and ask him to reply to this thread

[livewirestu]

Hi Guys, I just got Helter's PM.
I got:
[Page generation time: 5.0666s (PHP: 20% | SQL: 80%) | SQL queries: 882 | GZIP enabled | Debug on]
on your site which seems like a very excessive query count!
I am not familiar with phpbb2 or IM141 but I can give it a try if you like. If you could please PM me with FTP and phpMyAdmin details so I can take a local copy of your site, hopefully I will be able to debug a little easier.

Cheers,
Stu

[Helter]

thank you Stu. I know that the queries vary depending on what options/functions are in use. They also are higher in php5 than in php4, but a healthy 141 forum in heavy use should still not have much more than 170 querries max and will usually average around 50-90

[BigBensMom]

Thank you very much, Stu! (and Helter!)

Stu - I sent you a PM ...let me know if there's more info needed.

Thanks!!!
Mary

[livewirestu]

Ok, I found something potentially very wasteful...
In page_header.php (which, incedentally is called on EVERY page), there is some code to work out who has visited in the last 24 hours. There is also an option to display who has NOT visited in that time. However, even with that option disabled, a query is still executed on every non-visiting user - I.e., 1 sql query for every user in the database! On Mary's site, that is about 850 members.
I have made a slight alteration so that the non-visitors are not processed if this option is disabled. That reduced the query count by about 700 (will vary on a day to day basis depending on the amount of visitors).
The forum page now loads like a bullet - However, the portal page is still somewhat slow, although I can't seem to reproduce the 60+ secs load times.
So, the fix to reduce that count... (I have already done this on your live site Mary)
includes/page_header.php
Find:

Code: Select all

// ############ Edit below ############
// #
$display_not_day_userlist = 0; // change to 1 here if you also want the list of the users who didn't visit to be displayed
$users_list_delay = 24; // change here to the number of hours wanted for the list
// #
// ############ Edit above ############
$sql = "SELECT user_id, username, user_allow_viewonline, user_level, user_session_time
      FROM ".USERS_TABLE."
      WHERE user_id > 0
      ORDER BY IF(user_level=1,3,user_level) DESC, username ASC";

Replace with:

Code: Select all

// ############ Edit below ############
$display_not_day_userlist = 0; // change to 1 here if you also want the list of the users who didn't visit to be displayed
$users_list_delay = 24; // change here to the number of hours wanted for the list
$time_min = time() - ($users_list_delay * 3600);
// ############ Edit above ############

$sql = "SELECT user_id, username, user_allow_viewonline, user_level, user_session_time
        FROM ".USERS_TABLE."
        WHERE user_id > 0 "
        . ($display_not_day_userlist ? '' : ("AND user_session_time >= " . $time_min)) .
        " ORDER BY IF(user_level=1,3,user_level) DESC, username ASC";

So now I'm off to try to find what is still causing slow-motion on the portal page.
Hopefully be back soon with some good results <img>

Stu

[BigBensMom]

AWESOME!!!!!

I think I've told you "thank you" about a million times --- but get ready to hear it again!
THANK YOU!!!!
thanks sooooooo much, Stu!

Mary

[livewirestu]

You're welcome <img>
I am actually getting sub-second page loads on your live site now! Had a couple around the 0.6 seconds mark on the portal page. So maybe this has fixed it afterall?

I can see you have quite a few images hotlinked from photobucket and a few other sites on your portal page. Depending on the traffic on those sites at any given time, the page load times on your site may be degraded.
For now it looks pretty fast, but lets keep an eye on it for a while.

What sort of load times are everyone else seeing at the moment?

Stu

[BigBensMom]

I can't get the portal to load.... I'm getting the "page cannot be found" error.

[livewirestu]

Really? It's working for me.
Are you using firefox? Occasionally it reports that a page cant be found if you are downloading something or otherwise have a reasonable amount of traffic coming through your port.
Normally a couple of refreshes does the trick.

[BigBensMom]

ok... portal just loaded for me:

[Page generation time: 1.1613s (PHP: 63% | SQL: 37%) | SQL queries: 180 | GZIP enabled | Debug on]

...a random forum page:

[Page generation time: 1.4398s (PHP: 56% | SQL: 44%) | SQL queries: 207 | GZIP enabled | Debug on]


1.03 seconds
0.8 seconds

[.QUACK.Major.Pain]

Good to see your getting some improvement.

My question is, why is this happening to you, when everyone else has the same type of site, and myself, with 2000 members, only average 170 sql queries.

Why is this site acting different to others?

[Helter]

there have been several others with this problem. There are so many different setup combination's for servers and forums, it is difficult to find the common denominator, but this has already helped the one ive been working on for months.
Thx Stu! let me know if you find anything else.

[BigBensMom]

not sure if this is related to the original problem, or the fix... or something new... but our site has been down more than it's been up today <img> I've been off line, but our admins have emailed that the site's been down a lot.... same thing --- random times, the page doesn't load and gives the HTTP 400 Bad Request / Page Cannot Be Found" error.

We've contacted GoDaddy and they said they'd "look into it" and get back with us. :/

I guess it's happening a LOT more.... the problem is very bad right now. I don't know if this has anything to do with sql queries or not. If GoDaddy gives us any info, I'll post it here.

Thanks
Mary

[BigBensDad]

Well just checkin in on things, Stu did you have a chance to look at the error logs? we are still getting 400 and 404 errors and don't get it....Godaddy tells us they have looked into it and we are not having problems....weird it seems so real and the 800 other members are having the same illusion i'm guessing that they are trying to mislead us... <img> but tonight we could not get on the server while we were having "problems" thanks for all the help you guys are great...

[BigBensMom]

We pruned our users... deleted the accounts of anyone that has never posted (244 fit that descripton). And we are working on pruning a few of our fourms that are not that important to the purpose of the site.... so, hoping that will reduce the database some. ?

What we've discovered is that EVERY time the forum has problems the PHP % is very very low --- and the SQL % is very very high. We are looking at the bottom of the page where it gives the "page generation" information....

If the PHP% and SQL% are more equal -- like 50/50 or so... the forum works great.... if the PHP goes to 2% or even 0% --- the SQL is then 98% or 100% ---- then the forum will not load. I do not see any pattern with the SQL Queries... they vary from 200 to 500, but have no pattern... it's the % that seems to be showing the pattern related to when the forum won't load.

Please keep in mind that I am "learning as I go" and while this is probably something very "basic" -- I don't know what it means.

Can anyone explain it? Is there something I can do to make the % stay more "even"?

Thanks!!!! <img>

[BigBensDad]

I just want to thank you guys for your help, I have dealt with some not so helpful folks but you guys.....you fixed it.....and you don't even have mastiffs....but 800 mastiff owners are enjoying pictures tonight and haveing a good ol time...You guys can move this to the solved area...Helter thanks for your help and stu if you guys ever need a house built in missouri.....I'm you man....thanks again...

[Helter]

glad you got it worked out <img>

[DjPorkchop]

I got pruned too Glad you all got it sorted out. You guys have a great site and it would be a shame to see it down all the time. Happy posting to all your mastiff owner members!

[BigBensDad]

I got pruned too Glad you all got it sorted out. You guys have a great site and it would be a shame to see it down all the time. Happy posting to all your mastiff owner members!

thanks goes to you too.....thanks for the help