Site being very slow, too many queries, High load times!

faeronsayn

faeronsayn

Well-known member
Page Time: 6.4313s

Memory: 14.3228 MB (Peak: 17.4902 MB)

Queries (32, time: 3.2619s, 50.7%)




Those are the load times I am getting when I go into debug.

I am pretty sure that these are high, even for my site. I only get about 100 people on simultaneously, no more than that. I do have a several addons installed, but I am not so sure if that is the problem or not. If anyone can help optimize this or at least let me know where I could go about optimizing it, would be really appreciated. Thank you :)

Edit: More infromation

At the time of this screenshot I have the following members online: Online now: 157 (members: 30, guests: 127)

Caasdfasdfasdfpture.PNG


Processes Running


Code: 
  PID TTY          TIME CMD
    1 ?        00:00:00 init
    2 ?        00:00:00 kthreadd
    3 ?        00:00:00 migration/0
    4 ?        00:00:00 ksoftirqd/0
    5 ?        00:00:00 migration/0
    6 ?        00:00:00 watchdog/0
    7 ?        00:00:00 migration/1
    8 ?        00:00:00 migration/1
    9 ?        00:00:00 ksoftirqd/1
  10 ?        00:00:00 watchdog/1
  11 ?        00:00:00 migration/2
  12 ?        00:00:00 migration/2
  13 ?        00:00:00 ksoftirqd/2
  14 ?        00:00:00 watchdog/2
  15 ?        00:00:00 migration/3
  16 ?        00:00:00 migration/3
  17 ?        00:00:00 ksoftirqd/3
  18 ?        00:00:00 watchdog/3
  19 ?        00:00:00 events/0
  20 ?        00:00:00 events/1
  21 ?        00:00:00 events/2
  22 ?        00:00:00 events/3
  23 ?        00:00:00 cpuset
  24 ?        00:00:00 khelper
  25 ?        00:00:00 netns
  26 ?        00:00:00 async/mgr
  27 ?        00:00:00 pm
  28 ?        00:00:00 xenwatch
  29 ?        00:00:00 xenbus
  30 ?        00:00:00 sync_supers
  31 ?        00:00:00 bdi-default
  32 ?        00:00:00 kintegrityd/0
  33 ?        00:00:00 kintegrityd/1
  34 ?        00:00:00 kintegrityd/2
  35 ?        00:00:00 kintegrityd/3
  36 ?        00:00:00 kblockd/0
  37 ?        00:00:00 kblockd/1
  38 ?        00:00:00 kblockd/2
  39 ?        00:00:00 kblockd/3
  40 ?        00:00:00 ata/0
  41 ?        00:00:00 ata/1
  42 ?        00:00:00 ata/2
  43 ?        00:00:00 ata/3
  44 ?        00:00:00 ata_aux
  45 ?        00:00:00 ksuspend_usbd
  46 ?        00:00:00 khubd
  47 ?        00:00:00 kseriod
  48 ?        00:00:00 md/0
  49 ?        00:00:00 md/1
  50 ?        00:00:00 md/2
  51 ?        00:00:00 md/3
  52 ?        00:00:00 md_misc/0
  53 ?        00:00:00 md_misc/1
  54 ?        00:00:00 md_misc/2
  55 ?        00:00:00 md_misc/3
  56 ?        00:00:00 khungtaskd
  57 ?        00:00:01 kswapd0
  58 ?        00:00:00 ksmd
  59 ?        00:00:00 aio/0
  60 ?        00:00:00 aio/1
  61 ?        00:00:00 aio/2
  62 ?        00:00:00 aio/3
  63 ?        00:00:00 crypto/0
  64 ?        00:00:00 crypto/1
  65 ?        00:00:00 crypto/2
  66 ?        00:00:00 crypto/3
  71 ?        00:00:00 kthrotld/0
  72 ?        00:00:00 kthrotld/1
  73 ?        00:00:00 kthrotld/2
  74 ?        00:00:00 kthrotld/3
  76 ?        00:00:00 khvcd
  77 ?        00:00:00 kpsmoused
  78 ?        00:00:00 usbhid_resumer
  219 ?        00:00:03 kjournald
  285 ?        00:00:00 udevd
  518 ?        00:00:00 kstriped
  610 ?        00:00:07 flush-202:1
  629 ?        00:00:00 kauditd
  836 ?        00:00:00 auditd
  852 ?        00:00:01 rsyslogd
  904 ?        00:00:00 sw-cp-serverd
  914 ?        00:00:00 sshd
  922 ?        00:00:00 xinetd
  935 ?        00:00:00 couriertcpd
  937 ?        00:00:00 courierlogger
  945 ?        00:00:00 couriertcpd
  947 ?        00:00:00 courierlogger
  953 ?        00:00:00 couriertcpd
  955 ?        00:00:00 courierlogger
  962 ?        00:00:00 couriertcpd
  964 ?        00:00:00 courierlogger
1044 ?        00:00:00 master
1055 ?        00:00:00 qmgr
1059 ?        00:00:00 psa-pc-remote
1076 ?        00:00:00 tlsmgr
1130 ?        00:00:01 named
1171 ?        00:00:00 mysqld_safe
1263 ?        00:09:21 mysqld
1524 ?        00:01:00 drwebd.real
1534 ?        00:00:00 crond
1549 tty1    00:00:00 mingetty
1551 tty2    00:00:00 mingetty
1553 tty3    00:00:00 mingetty
1555 tty4    00:00:00 mingetty
1557 tty5    00:00:00 mingetty
1559 tty6    00:00:00 mingetty
1562 ?        00:00:00 udevd
1563 ?        00:00:00 udevd
1564 hvc0    00:00:00 agetty
4715 ?        00:00:01 httpd
4717 ?        00:00:00 httpd
5054 ?        00:00:00 sshd
5071 ?        00:00:00 sftp-server
19118 ?        00:00:00 pickup
23333 ?        00:02:53 httpd
25819 ?        00:02:07 httpd
26920 ?        00:01:55 httpd
26929 ?        00:01:45 httpd
27852 ?        00:01:27 httpd
28047 ?        00:01:27 httpd
28543 ?        00:01:28 httpd
29413 ?        00:00:46 httpd
29456 ?        00:01:03 httpd
29463 ?        00:01:13 httpd
29838 ?        00:00:53 httpd
30270 ?        00:00:47 httpd
30289 ?        00:00:44 httpd
30310 ?        00:00:51 httpd
30420 ?        00:00:46 httpd
30591 ?        00:00:48 httpd
30939 ?        00:00:31 httpd
31003 ?        00:00:00 sshd
31028 pts/0    00:00:00 bash
31054 pts/0    00:00:00 top
31305 ?        00:00:28 httpd
31519 ?        00:00:00 drwebd.real
31520 ?        00:00:00 drwebd.real
31521 ?        00:00:00 drwebd.real
31523 ?        00:00:00 drwebd.real
31673 ?        00:00:16 httpd
31674 ?        00:00:22 httpd
32050 ?        00:00:10 httpd
32498 pts/0    00:00:00 ps
 
Sort by date Sort by votes
I guess I get the problem. My real problem is how to fix it, I am not much of a server guy. Thats why I was hoping members here could point me to what I should be trying to fix.
 
Upvote 0 Downvote
there are too many variables to easily fix things like this in a forum thread...
BUT,

The server load seem pretty high.
If it were me, I'd first try something crazy like turning XF completely OFF - and then loading a regular web page (make some if you don't have one) or some images from the site - the server load should go down to way below 1 if XF is off..........

If it does not, there is something wrong at a higher level - probably your hosts fault! In fact, a real test would be to turn everything off (apache and mysql) and see if the load goes down near zero.

At least then you know you can start with a clean slate as you turn things on again. It's just possible that your VPS is not giving you the proper CPU time.....or something crazy like that.
 
Upvote 0 Downvote
Dittos to finding out what CPU you have access to...BUT

Apache (which is what serves the web page part) normally should not take up those large amounts of CPU percentage! For instance, you have ONE apache process (that's the equiv. of one logo loading up or similar!) taking up 50% of the CPU!

A problem like this sometimes must be solved by process of elimination. That's what I would do......it will at least give you a hint of what may be causing most of the problems.
 
Upvote 0 Downvote
craigiri said:
Dittos to finding out what CPU you have access to...BUT

Apache (which is what serves the web page part) normally should not take up those large amounts of CPU percentage! For instance, you have ONE apache process (that's the equiv. of one logo loading up or similar!) taking up 50% of the CPU!

A problem like this sometimes must be solved by process of elimination. That's what I would do......it will at least give you a hint of what may be causing most of the problems.
Click to expand...

I am going to try this, but I think someone might be attacking my site.
 
Upvote 0 Downvote
Have you moved sites or changed forum software recently? I had high loads both times I changed software as Google immediately noticed the change and went nuts reindexing; I only mention it in case it is a part of the problem.

If it's heavy web traffic look in your logs to see what the requests are for and where they are coming from.

You might want to consider IP blocking the Baidu Chinese search engine bots too since they can be quite aggressive and often bring you little or no traffic at all.
 
Upvote 0 Downvote
Clickfinity said:
Have you moved sites or changed forum software recently? I had high loads both times I changed software as Google immediately noticed the change and went nuts reindexing; I only mention it in case it is a part of the problem.

If it's heavy web traffic look in your logs to see what the requests are for and where they are coming from.

You might want to consider IP blocking the Baidu Chinese search engine bots too since they can be quite aggressive and often bring you little or no traffic at all.
Click to expand...

Yes lol I have recently changed hosts. But I changed hosts because I was having this type of problem on the previous host.

I am not sure how I can tell if Google is being aggressive with its indexing.
 
Upvote 0 Downvote
Okay, well there are a number of things you can check/do:
  • Apache - check the number of initial startup servers and also the number of spare servers (that use up resources whilst sitting in standby) - look in your Apache conf file for StartServers, MinSpareServers, MaxSpareServers
  • Apache - check if you have the mod_deflate module enabled (compresses output and speeds up delivery)
  • Apache - consider using Expires Headers (mod_expires enabled) to cache static files on your visitor's machine (http://www.justinbritten.com/work/2008/12/how-to-set-an-expires-header-in-apache/)
  • Apache - check that HostnameLookups is set to - Off - if it isn't change it to Off (you don't want to be doing a DNS lookup for every host making a request!)
  • Server - consider installing memcached - start with maybe 256MB and increase if you feel the server can handle it
  • Server - consider installing an opcode cache to accelerate PHP such as APC, eAccelerator or Xcache (then edit your XF site config to use whichever cache you end up using)
  • MySQL - post your my.cnf file (at the command prompt type - "locate my.cnf" - without the quotes).
Hope this gives you something to look at and points you in the right direction.

Cheers,
Shaun :D
 
Upvote 0 Downvote
Clickfinity said:
Okay, well there are a number of things you can check/do:
  • Apache - check the number of initial startup servers and also the number of spare servers (that use up resources whilst sitting in standby) - look in your Apache conf file for StartServers, MinSpareServers, MaxSpareServers
  • Apache - check if you have the mod_deflate module enabled (compresses output and speeds up delivery)
  • Apache - consider using Expires Headers (mod_expires enabled) to cache static files on your visitor's machine (http://www.justinbritten.com/work/2008/12/how-to-set-an-expires-header-in-apache/)
  • Apache - check that HostnameLookups is set to - Off - if it isn't change it to Off (you don't want to be doing a DNS lookup for every host making a request!)
  • Server - consider installing memcached - start with maybe 256MB and increase if you feel the server can handle it
  • Server - consider installing an opcode cache to accelerate PHP such as APC, eAccelerator or Xcache (then edit your XF site config to use whichever cache you end up using)
  • MySQL - post your my.cnf file (at the command prompt type - "locate my.cnf" - without the quotes).
Hope this gives you something to look at and points you in the right direction.


Cheers,
Shaun :D
Click to expand...

Thank you for this post :)

Well first of all here is the contents of the my.cnf file.

Code: 
[mysqld]
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
 
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Secondly about memcached and APC and such, don't they use a lot of CPU resources? If I enable this and my cpu loads are already high would it benefit the speed of the site ?
 
Upvote 0 Downvote
That's a pretty basic my.cnf file, and isn't really optimised for anything.

Another thing, there are a lot of apache processes running. Are you able to do a netstat -a to see if you are being hammered by loads of requests from single / multiple ip addresses. You could always install DDOS deflate http://deflate.medialayer.com/ which will block ip addresses with multiple connections
 
Upvote 0 Downvote
MattW said:
That's a pretty basic my.cnf file, and isn't really optimised for anything.

Another thing, there are a lot of apache processes running. Are you able to do a netstat -a to see if you are being hammered by loads of requests from single / multiple ip addresses. You could always install DDOS deflate http://deflate.medialayer.com/ which will block ip addresses with multiple connections
Click to expand...

Should I be installing a firewall as well? (Advanced Policy Firewall (APF)) ?
 
Upvote 0 Downvote
Keeping it simple, the (lack of) cache does not appear to be the problem with the throwing off of all those server errors and very high loads.

How about your apache conf file. Can you post it here? It should be called httpd.conf or something like that.

The chances of a DDOS attack also seem small. I've never had it happen in my 18 years with a server. Anything can happen, but it's best to start at the beginning and eliminate all the basics....first!
 
Upvote 0 Downvote
craigiri said:
Keeping it simple, the (lack of) cache does not appear to be the problem with the throwing off of all those server errors and very high loads.

How about your apache conf file. Can you post it here? It should be called httpd.conf or something like that.

The chances of a DDOS attack also seem small. I've never had it happen in my 18 years with a server. Anything can happen, but it's best to start at the beginning and eliminate all the basics....first!
Click to expand...


I've attached my httpd.conf file below, since it was too long I didn't just copy paste it.
 

Attachments

Upvote 0 Downvote
MattW said:
That's a pretty basic my.cnf file, and isn't really optimised for anything.

Another thing, there are a lot of apache processes running. Are you able to do a netstat -a to see if you are being hammered by loads of requests from single / multiple ip addresses. You could always install DDOS deflate http://deflate.medialayer.com/ which will block ip addresses with multiple connections
Click to expand...

How is this my.cnf ?

Code: 
# The following options will be passed to all MySQL clients
[client]
socket=/tmp/mysql.sock


# The MySQL server
[mysqld]
tmpdir=/tmp
socket=/tmp/mysql.sock
skip-locking
skip-networking
skip-name-resolve

server-id=1

max_connections=500
key_buffer_size=384M
max_allowed_packet=16M
table_cache=256
sort_buffer_size=2M
read_buffer_size=2M
join_buffer_size=2M

# if you are performing GROUP BY or ORDER BY queries on tables that
# are much larger than your available memory, you should increase
# the value of read_rnd_buffer_size to speed up the reading of rows
# following sorting operations. 
# but: change the session variable only from within those clients 
# that need to run large queries
read_rnd_buffer_size=2M

max_heap_table_size=256M
tmp_table_size=256M

myisam_sort_buffer_size=64M

# increase until threads_created doesnt grow anymore
thread_cache=256

query_cache_type=1
query_cache_limit=1M
query_cache_size=32M

# Try number of CPU's*2 for thread_concurrency
thread_concurrency=4


[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer=128M
sort_buffer_size=128M
read_buffer=2M
write_buffer=2M

[myisamchk]
key_buffer=128M
sort_buffer_size=128M
read_buffer=2M
write_buffer=2M

[mysqlhotcopy]
interactive-timeout
 
Upvote 0 Downvote
Okay, make these changes to the values in the first part of the file, restart Apache, and let us know if it helps?

Rich (BB code): 
Timeout 45
 
KeepAlive On
 
MaxKeepAliveRequests 1000
 
KeepAliveTimeout 1
 
 
<IfModule prefork.c>
StartServers      10
MaxRequestsPerChild  10000

Then if you want to enable cachingof status stuff on your visitor's machine add this to the end of the file (copied from my own - adjust to suit as best you think) - and restart Apache again:

Rich (BB code): 
# Caching - Load images, css, and javascript from visitors
# browser for 30 days before re-caching
 
ExpiresActive on
ExpiresByType text/css "access plus 1 day"
ExpiresByType text/javascript "access plus 1 month"
ExpiresByType application/javascript "access plus 1 month"
ExpiresByType application/x-javascript "access plus 1 month"
ExpiresByType application/x-shockwave-flash "access plus 1 month"
ExpiresByType image/gif "access plus 1 month"
ExpiresByType image/jpg "access plus 1 month"
ExpiresByType image/jpeg "access plus 1 month"
ExpiresByType image/png "access plus 1 month"
ExpiresByType image/ico "access plus 1 month"
ExpiresByType image/icon "access plus 1 month"

NB: I only cache CSS for one day as I change that file type the most often. (y)

Cheers,
Shaun :D
 
Upvote 0 Downvote
You have no innodb setting! XF is innodb tables!

add some like this:

Code: 
innodb_buffer_pool_size = 300M
innodb_additional_mem_pool_size = 10M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 100
 
Upvote 0 Downvote
Clickfinity said:
Okay, make these changes to the values in the first part of the file, restart Apache, and let us know if it helps?

Rich (BB code): 
Timeout 45
 
KeepAlive On
 
MaxKeepAliveRequests 1000
 
KeepAliveTimeout 1
 
 
<IfModule prefork.c>
StartServers      10
MaxRequestsPerChild  10000

Then if you want to enable cachingof status stuff on your visitor's machine add this to the end of the file (copied from my own - adjust to suit as best you think) - and restart Apache again:

Rich (BB code): 
# Caching - Load images, css, and javascript from visitors
# browser for 30 days before re-caching
 
ExpiresActive on
ExpiresByType text/css "access plus 1 day"
ExpiresByType text/javascript "access plus 1 month"
ExpiresByType application/javascript "access plus 1 month"
ExpiresByType application/x-javascript "access plus 1 month"
ExpiresByType application/x-shockwave-flash "access plus 1 month"
ExpiresByType image/gif "access plus 1 month"
ExpiresByType image/jpg "access plus 1 month"
ExpiresByType image/jpeg "access plus 1 month"
ExpiresByType image/png "access plus 1 month"
ExpiresByType image/ico "access plus 1 month"
ExpiresByType image/icon "access plus 1 month"

NB: I only cache CSS for one day as I change that file type the most often. (y)

Cheers,
Shaun :D
Click to expand...

Thank you so much, I basically copied that to my apache file.

craigiri said:
You have no innodb setting! XF is innodb tables!

add some like this:

Code: 
innodb_buffer_pool_size = 300M
innodb_additional_mem_pool_size = 10M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 100
Click to expand...


This would go into the my.cnf file right ?

I should add this to my own or the one that I posted that was customized ?
 
Upvote 0 Downvote
You must log in or register to reply here.
Top Bottom