News:

Bored?  Looking to kill some time?  Want to chat with other SMF users?  Join us in IRC chat or Discord

Main Menu

Sites SQL is at a Crawl: Help needed

Started by xtremecruiser, August 20, 2005, 09:35:45 PM

Previous topic - Next topic

xtremecruiser

Ok, my host is playing with the sql and has well basically killed my sites speed.  It takes over 100 seconds to load a thread when it took less than 10 seconds a week ago.
They updated the PHP to 4.4.0 a month ago and when I check tonight after it being down for 2 days at all kinds of different times and its back at 4.3.11.
I have some links to the scripts that everyone looks at below.. Can someone look at them that is a SQL and PHP guru and help me understand what I going on ?

Status.php
php_info

I did the PHP 4.4.0 mod and now they drop back to 4.3.1.. will that be causing issues ?

[Unknown]

Your host really should be using PHP as an Apache module, and using an accelerator.

I'm not sure why pages would be taking 10 seconds to load, your forum isn't that large.  What mods do you have installed?

-[Unknown]

blunt

I've been having the same problem - it often takes ten or fifteen seconds to fully load a page, and then sometimes there are still several images missing.

I'm not sure what the problem is, but I keep doing tracerts to my site, and I've sometimes found network errors around the main server, at 'theplanet'.  The domain resolves OK, but I get a blank white screen - view source and it's all there, but nothing shows.  Go back and refresh and it comes up, 9 times out of 10.

The database does seem to be getting progressively slower as well.  A lot of downtime lately.  It's annoying.  I'm considering changing hosts.

xtremecruiser

#3
Quote from: [Unknown] on August 21, 2005, 12:46:52 AM
Your host really should be using PHP as an Apache module, and using an accelerator.

I'm not sure why pages would be taking 10 seconds to load, your forum isn't that large.  What mods do you have installed?

-[Unknown]

I am at version 1.02 and was planning to do a Upgrade this weekend so I could get all the mods out but with it being so slow I am afraid to do anything.
I would love 10 seconds for it to load, 120 to 160 is what it was for the last 2 days.

1.     Banner Manager      3.4       [ Uninstall ]  [ List Files ]  [ Delete ]
2.    OnLine Users Today    1.2    [ Uninstall ] [ List Files ] [ Delete ]
3.    Actions in Profile Display    1.3    [ Uninstall ] [ List Files ] [ Delete ]
4.    The Bar Of Experience    1.1    [ Uninstall ] [ List Files ] [ Delete ]
5.    Visual Warning / Post Moderation    1.21    [ Uninstall ] [ List Files ] [ Delete ]
6.    Faster Parsecode    1.07    [ Uninstall ] [ List Files ] [ Delete ]
7.    Unreplied Topics    1.02    [ Uninstall ] [ List Files ] [ Delete ]
8.    Board Default Sort    1.23    [ Uninstall ] [ List Files ] [ Delete ]
9.    G&S_Sticky    1.0    [ Uninstall ] [ List Files ] [ Delete ]
10.    Integrated Chat    2.2    [ Uninstall ] [ List Files ] [ Delete ]
11.    Excess Blank Lines    1.02    [ Uninstall ] [ List Files ] [ Delete ]
12.    SMF 1.0.3 Update Package    1.0.3    [ List Files ] [ Delete ]
13.    SMF 1.0.4 Update Package    1.0.4    [ Apply Mod ] [ List Files ] [ Delete ]
14.    Karma Log    1.0.1    [ Uninstall ] [ List Files ] [ Delete ]

I also emailed them of the php suggestion ;D

blndsyd

I had the same problem, it started a week ago. I was using 1.05 on my server. They wouldnt help out , so I pulled the plug and moved to another host.
I dunno, but after all this , my site is flying again.

xtremecruiser

Quote from: [Unknown] on August 21, 2005, 12:46:52 AM
Your host really should be using PHP as an Apache module, and using an accelerator.

I'm not sure why pages would be taking 10 seconds to load, your forum isn't that large.  What mods do you have installed?

-[Unknown]
The updated the PHP, is it still compiled wrong ?

xtremecruiser

Quote from: xtremecruiser on August 24, 2005, 08:07:09 AM
Quote from: [Unknown] on August 21, 2005, 12:46:52 AM
Your host really should be using PHP as an Apache module, and using an accelerator.

I'm not sure why pages would be taking 10 seconds to load, your forum isn't that large.  What mods do you have installed?

-[Unknown]
The updated the PHP, is it still compiled wrong ?
They said the PHP load is causing problems and have offered to move me to a new server that has little if any php useage, I am going to let them, but the php still wont work with "spell check" and man that really PO's me 8)

They are good about listening to my usggestions though, if you would be so kind as to suggest what changes they need to make, they try to do them.
Thanks

[Unknown]

Their load averages and the number of httpd processes are crazy.  The box is getting 62 queries per second, but MySQL looks configured by now.

I don't think Zend Optimizer is cutting the bread, but they may not be willing to try eAccelerator.

-[Unknown]

xtremecruiser

Quote from: [Unknown] on September 15, 2005, 04:13:38 PM
Their load averages and the number of httpd processes are crazy.  The box is getting 62 queries per second, but MySQL looks configured by now.

I don't think Zend Optimizer is cutting the bread, but they may not be willing to try eAccelerator.

-[Unknown]
Some days the sql is a rocket some days its crawls they say that server has a huge sql load.
What can I suggest to them to help get the speed and load down ?

Joshua Dickerson

httpd (607) 67.9% <-- wow. I am looking for a solution to reduce the number of sleeping processes. I need to hone in on my Apache optimization skills as it is definitely a setting in the apache httpd.conf file. Although, you won't have access to that, you still may be able to give your host a hint as how to fix the problem.
Come work with me at Promenade Group



Need help? See the wiki. Want to help SMF? See the wiki!

Did you know you can help develop SMF? See us on Github.

How have you bettered the world today?

xtremecruiser

I am PHP impaired :-X
So I am guessing this is bad

Load averages:      17.92, 12.39, 11.57
Current processes:     758 (745 sleeping, 12 running, 1 zombie)
Processes by CPU:     httpd (620) 43.2%, mysqld (23) 24.1%, exim (8) 9.1%, (other) (50) 1.5%, [httpd] (1) 0.8%, cpanellogd (1) 0.6%

What should the no's be at ?

[Unknown]

It should look more like:

Load averages:      0.92, 0.39, 0.57
Current processes:     204 (200 sleeping, 4 running, 0 zombie)
Processes by CPU:     httpd (180) 15.0%, mysqld (23) 8.0%, (other) (50) 1.0%

But, of course, that's just an example.  It varies from server to server.

-[Unknown]

Joshua Dickerson

From what I have been reading, that is a lot of forked processes for Apache.
Come work with me at Promenade Group



Need help? See the wiki. Want to help SMF? See the wiki!

Did you know you can help develop SMF? See us on Github.

How have you bettered the world today?

[Unknown]

Yes, but 200 is better than 700.  Like a lot better.  Here we have:

httpd (25) 10.8%, mysqld (1) 1.8%, (other) (37) 0.2%

And the server isn't even doing very well these days.

-[Unknown]

xtremecruiser

Yall are killing me when I look at mine  :'(

httpd (768) 44.3%, mysqld (20) 12.2%, (other) (48) 2.1%

blunt

Look at some of the load averages I've been suffering with my host, lately (I had one that went up as high as 150. :-\   They've swapped me onto another server, but I'm still getting very slow page loads, especially when posting.  The loads on the new server stay mostly around 0.8 to 1.2, even when the forum goes down, but they occasionally spike up to what you see below.  I wish I knew more about this stuff.


Server Status
Basic Information
April 25, 2006, 12:50:34 PM
Operating System: CentOS release 4.3 (Final) 
Processor: Dual Core AMD Opteron(tm) Processor 170 (1995.084MHz)
Load averages: 72.67, 74.80, 51.03
Current processes: 303 (267 sleeping, 35 running, 1 zombie)
Processes by CPU: httpd (151) 89.9%, mysqld (1) 2.5%, php (6) 2.3%, gam_server (2) 1.6%, dcpumon (1) 0.6%, imapd (10) 0.7% 
Memory usage: 99.259% (2041200k / 2056444k)
Swap: 72.541% (1485836k / 2048276k) 

MySQL Statistics
MySQL 4.1.x
Connections per second:  2.3868 
Kilobytes received per second:  8.9832 
Kilobytes sent per second:  303.0818 
Queries per second:  65.9869 
Percentage of slow queries:  0 
Opened vs. Open tables:
(table_cache)  36.7402 (should be <= 80) 
Table cache usage:
(table_cache)  1 (should be >= 0.5 and <= 0.9) 
Key buffer read hit rate:
(key_buffer_size)  0.001 (should be <= 0.01) 
Key buffer write hit rate:
(key_buffer_size)  0.3469 (should be <= 0.5) 
Thread cache hit rate:
(thread_cache_size)  0.006 (should be <= 0.05) 
Thread cache usage:
(thread_cache_size)  0.2 (should be >= 0.8 and <= 0.9) 
Temporary table disk usage:
(tmp_table_size)  0.2272 (should be <= 0.5) 
Sort merge pass rate:
(sort_buffer)  0 (should be <= 0.001) 
Query cache enabled:
(query_cache_type)  1 (should be >= 1 and <= 1) 
Query cache miss rate:
(query_cache_limit)  0.0143 (should be <= 0.1) 
Query cache prune rate:
(query_cache_size)  0.2495 (should be <= 0.05) 

Show more information...
***


***


Server Status
Basic Information
April 25, 2006, 12:51:03 PM
Operating System: CentOS release 4.3 (Final) 
Processor: Dual Core AMD Opteron(tm) Processor 170 (1995.084MHz)
Load averages: 59.84, 71.58, 50.72
Current processes: 312 (283 sleeping, 26 running, 3 zombie)
Processes by CPU: httpd (141) 84.0%, mysqld (1) 2.5%, boxtrapper (1) 1.0%, gam_server (3) 1.4%, imapd (10) 0.6% 
Memory usage: 98.975% (2035368k / 2056444k)
Swap: 68.584% (1404780k / 2048276k) 

Advertisement: