Resolved: Page generation time

[Oracle_SOD]

Hi everyone,

we have just moved from phpBB2 to Integramod 1.4.0, its taken us about a year to complete build the site and the functionality we need.

We begun the transition on monday this week and the site is now fully functional (except for a few minor bugs that im still sorting out).

Our bigest concern ins the page generation time, on the original dev box it was through the roof, which was acceptable, we moved it to the live server and still had loads over 2sec (2-4sec)

We have now moved to a shared server space with 4 CPU's and 8Gb of ram, running Linux Enterprise.

With no post data in the site (still had user tables and PM's) the load times were under 1sec

Two days ago i migrated the post data across to the new server and load times have sky rocketed to 3+ sec

EG:
[Page generation time: 3.026s (PHP: 12% | SQL: 88%) | SQL queries: 72 | GZIP enabled | Debug off]

as you can see its mostly SQL. i stripped the header and footer custom code and there was no change (now restord to new site code). i have checked all my new SQL statements, the ones that can be limited have been limited to 1 result, and the joins all look ok

the site is running on http://www.fordlaser.com/portal.php

please keep in mind this site is not avaliable to the public yet as http://www.fordlaser.com/ loads a temp page

we have 352,112 posts and 61,72 users

i have read through both this site and the archive site and tried basicly everything listed short of remving prillian and a few of the other functions we would like to leave there

it is critical i get these load times down asap as we need to get the site running again (admins and mods are getting 100's of emails with user questions)

can anyone help us reduce the SQL times ??

[Scissors]

Have you tried the simple stuff yet?

Defraging the database
Repairing the database
Disabling Gzip and Debug

[Oracle_SOD]

Hi Scissors,

thankx for the responce, so far we have done most of what you have sugested with little resolve


Repairing the database - via the "Optimize DB" page - yes
Disabling Gzip and Debug - Yes tried both didnt help (Debug is currently off)

Defraging the database - how do i do this ? if you mean via "DB Maintenance" page then yes

as a note, the page load times are still high, even when loading the generic "default msg" pages like the post successfull page

is there any way to get mysql to log every query it runs ? id like to do some testing

[ZacFields]

Shared servers aren't good with integramod as far as page generation time. Page generation time on my site (which is on a shared host) is 2-3 seconds most of the time too.

Ask your host how many sites are currently on each host. I know that the host I have (Ipowerweb) does 500-700 sites on one server. The servers are awesome...but 700 sites will bog down any server.

It might be largely out of your control.

Zac

[Oracle_SOD]

that shouldnt be the case, they are a local australian group, and arnt a huge group

i have spent the past hour playing with cache for each of the pannels on the main pages, this has reduced the time 1.3 sec when its not reloading the cache

we would like to get it below 1 sec if possible

currently
[Page generation time: 1.3368s (PHP: 25% | SQL: 75%) | SQL queries: 52 | GZIP enabled | Debug off]

[Oracle_SOD]

ok, we put the site live

by the time we had 15 users the CPU usage as sitting on 100%

and page times were around 10-20sec

20 users = the site froze and couldnt take the user load....

no one could load pages

site is now off line...

the issue i still sql, not PHP for some reason mysql cant keep up

[Oracle_SOD]

*Bump*

anyone have any ideas ?

[Simon N]

Well I can say that I use a shared server for free-riders.co.uk and the site runs fine even when busy; so it can not be the shared status of the hosting as such.

Do you have the shout box enabled?
If so remove it and see what happens. A shoutbox detracts from users posting anyway and serves no grown up purpose that I can see.

Do you have users visited/did not visit enabled?
If so once again get rid of this its so damn resource hungry its untrue and do you really need a list of who did not visit and who did...its pointless.

Prillian?
I know you hinted at wanting to keep it but do you really use it? With MSN live messenger and now yahoo thats compatible with msn do we really need a slow annoying site only messaging system to run alongside Private Messages?

I assume you have debug still turned off and gzip turned on...and also have the theme set to cache?

[Driver 7]

I just wanted to comment on how awesome your site looks. Great work man.

Your site does not take all that long at all to load for me.

I would suggest optimizing your images a bit. Your images: part-body-fit-02.gif and part-body-fit-03.gif are 76px wide. You could slice them so they were 8px wide. Might help a little.

[Oracle_SOD]

Yes shoutbox is enabled, but we are using the portal page as the test for the speed, the shoutbox only appears on the index.php page

users visited block is not enabled

the reason for prillian is that many of our local users are unable to access msn or yahoo during business hours as their employers have blocked those ports, this gives the users another option....

i have disabled prillian from loading on pages though, it will only load if the user click the prillian link on the portal page, therefore removing the effect on the server...

Cache is turned on, but i am thinking about turning it off as its producing an overhead when 20 users log in

[Oracle_SOD]

after working on this for a few more hours i found the news extract code is EXTREAMLY hungry

like 0.2 - 0.4 sec of time

i have optimised this down to 0.02 sec

and also optimised the header code and reduced that by about 0.08 sec

so we are trying the site live again...

[ZacFields]

Well I can say that I use a shared server for free-riders.co.uk and the site runs fine even when busy; so it can not be the shared status of the hosting as such.

I disagree.

I can almost guarantee his problem is with his host. What else would make his IM 1.4.0 different from yours or mine or integrmod.com's?

Different hosts make different accomodations for their shared servers. Some hosts will put 200 on a server, some will do 400, some will do 700 and yes, some will do more than that. Plus it all depends on how many of those 700 sites are huge SQL drains. Remember on a shared host you're all running off the same box.

You can't rule out shared host just because it works for some people. It works fine for me....usually. Sometimes page loads can take 3-4 seconds though. But when I run the same site on my Virtual host it takes between .5 and 1.5 seconds for most pages.

A site of that size really should have a dedicated server or a virtual host, IMO. Shared hosts give you tons of space for cheap, but what they don't tell u is that you have to share SQL usage with however many sites they have on your server with you.

Ipower virtual hosting starts at $27 and that's pretty much dirt cheap for what they give you. IM all by itself is an SQL drain anyways. A blank php3 index page loads on my virtual host in 0.06 seconds. A default IM index loads in about 1.5 seconds on my virtual host.

host host host. I'm tellin' ya sometimes those shared hosts are complete ripoffs. If you've removed all this stuff and done all this work and it's still giving you problems, it is most definitely the host.

Zac

[Oracle_SOD]

sorry, what i mean by a shared host is that we have our own MySQL daemon that i can start and stop as i please, its a virtual host basicly...


The site runs quite well with 30+ users now that i have fixed that code issue, but.... (and isnt there always a but)

something is causeing a memory leak or a loop is causing Mysql to go nuts...

basicly it ran for an hour and then the cpu usage hit 100% and memory usage got to 90% (i killed the mysql daemon) now there is 8gb of ram on this server... so there is something very wrong....

im going to have to go through 100's of pages of code...

unless someone knows how to get mysql to log every sql query it runs (preferably where i can limit it to querys that take longer than 1sec)

[Oracle_SOD]

ok, i have installed mytop on the server to monitor the sql code

here is an example of the query that is choaking the server, these are taking up to 800 sec of server time to complete

Code: Select all

SELECT p.forum_id, p.topic_id, p.post_time, p.post_username, u.username, u.user_id, t.topic_last_post_id, t.topic_titleFROM (((phpbb_posts pLEFT JOIN phpbb_topics t ON t.topic_id = p.topic_idAND t.forum_id = p.forum_idAND t.topic_moved_id =0)LEFT JOIN phpbb_users u ON u.user_id = p.poster_id)LEFT OUTER JOIN phpbb_approve_posts ap ON ap.post_id = p.post_id)WHERE (p.post_time >1153052850OR p.post_idIN ( 538805, 538713, 538238, 538822, 538765, 538772, 538835, 538828, 538799, 538831, 538833, 530339, 538740, 538786, 525007, 528001, 529294, 476447, 538050, 538800, 538834, 538808, 538273, 538829, 538821 ) )AND p.post_id = t.topic_last_post_idAND p.post_time <1153052850AND ap.post_id IS NULL LIMIT 0 , 30

im trying to find where this code has come from... if anyone knows could u please post it up

[Oracle_SOD]

we have narrowed the issue now to the MY.CNF file

is it possible to see some examples of what people are using


the server we are on is a VPS and is as follows

4x 3.0ghz Pentium Zeon's
8gb ram (256 guarenteed to us)


at the moment the CPU usage is 100% and we cant seam to work it out