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
craigiri said:
Try adding it to the customized one - although some of those settings in the custom one can probably be turned down. Still, it should work.
Click to expand...

I've done that.

Lets hope the performance increases or the loads decrease.

I think that update did something and after I restarted my VPS this error came up

Code: 
An exception occurred: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /var/www/vhosts/www.shadygamer.com/httpdocs/library/Zend/Db/Adapter/Mysqli.php on line 333

Zend_Db_Adapter_Mysqli->_connect() in Zend/Db/Adapter/Abstract.php at line 315
Zend_Db_Adapter_Abstract->getConnection() in XenForo/Application.php at line 553
XenForo_Application->loadDb()
call_user_func_array() in XenForo/Application.php at line 780
XenForo_Application->lazyLoad() in XenForo/Application.php at line 810
XenForo_Application::get() in XenForo/Application.php at line 1099
XenForo_Application::getDb() in XenForo/Error.php at line 47
XenForo_Error::unexpectedException() in XenForo/Application.php at line 295
XenForo_Application::handleException()
 
Upvote 0 Downvote
Make sure you use the TOP PART of your original file...only use the custom one from a certain point down!

That is, use:
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

Don't use the top of the new one.....that you found!

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
Click to expand...
 
Upvote 0 Downvote
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
 
 
innodb_buffer_pool_size = 300M
innodb_additional_mem_pool_size = 10M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 100

This is what I used.

With this the site works.

I was just checking, when the site gave out the error, my server loads are 0.15 - 0.30 which is awesome... I mean without the site running. After the site was fixed just a second a go the loads jumped back to 40.0 - 60.0
 
Upvote 0 Downvote
Did you ever find your apache log file? You can look at it as it goes by and see how fast docs are being served.

I think it is time to take it to your hosts. If I had to guess with this very limited info, it seems as if you are not correctly being assigned CPU use! That kind of load is unheard of.
 
Upvote 0 Downvote
I'm on a VPS, and I have CSF installed, and run DDOS inflate, and I get instances where single IP addresses have upto 400 connections to port 80, and this blocked them.
 
Upvote 0 Downvote
MattW said:
I'm on a VPS, and I have CSF installed, and run DDOS inflate, and I get instances where single IP addresses have upto 400 connections to port 80, and this blocked them.
Click to expand...

Well today I just looked at my email and this is what I found...

Captureasdfasdfasdf.PNG



There is more of these emails. So I guess a lot of people's IP got banned, the problem is I can't see the IP that was actually banned in the email. Anyways, I guess this is good?
 
Upvote 0 Downvote
faeronsayn said:
Well today I just looked at my email and this is what I found...

Captureasdfasdfasdf.PNG



There is more of these emails. So I guess a lot of people's IP got banned, the problem is I can't see the IP that was actually banned in the email. Anyways, I guess this is good?
Click to expand...
It might be worth banning people with over 50 connections, that's what I use and it seems to work but you do run the risk of false-positives and you also don't want to have a huge number of IPs banned one by one(this is not that likely though). Those are definitely a lot of connections and it's not like it is only happening once or twice, it appears that someone is trying to purposely do this or possibly one of those resource and connection happy search engines(Baidu use to soak up 150 connections at a time for me a while back)?
 
Upvote 0 Downvote
a legacy reborn said:
It might be worth banning people with over 50 connections, that's what I use and it seems to work but you do run the risk of false-positives and you also don't want to have a huge number of IPs banned one by one(this is not that likely though). Those are definitely a lot of connections and it's not like it is only happening once or twice, it appears that someone is trying to purposely do this or possibly one of those resource and connection happy search engines(Baidu use to soak up 150 connections at a time for me a while back)?
Click to expand...

So could this possibly be a bad thing? I mean my email is now flooded with these messages, connections going all the way up to 300. Would I have potentially banned Google or Baidu for their aggressive indexing ?

EDIT

Wow... Look at the frequency of the bans

asdfasdfasdfasCapture.PNG


Someone got banned for having 400+ connections. Could that be a search engine bot or is that someone purposely trying to take my site down ?
 
Upvote 0 Downvote
Have you actually had a look at some of the ip addresses which have been banned? The subject of the email doesn't tell you the address, it's in the message body

Banned the following ip addresses on Tue Jun 12 13:33:01 BST 2012

213.205.225.123 with 170 connections
Click to expand...

You can then do a Whois on the ip address.
 
Upvote 0 Downvote
faeronsayn said:
Okay well I am using a VPS server. It has 2gigs of ram, I am currently running Plesk 10 on it. This is a Linux server running CentOS. Also I do not know where the my.cnf file is but if you could give me the location I'll post it here. I am also not running any other applications on the vps besides the actual website, and centos/plesk.

Could you tell me how to do this command, so I can check how busy my VPS really is. By the way, I am not on shared hosting, I am running a VPS with 2 gigs of ram.
Click to expand...
if you don't know where everything is, download mysqlmymonlite.sh from http://mysqlmymon.com/ and grab the centos only version from specific folder and follow readme.txt to set it and run it to gather all server stats in less than <12 seconds :)

look in text file that you saved the output from, copy and paste and post that here in general code tags.
 
Upvote 0 Downvote
OK, so this is all sorted now.

What I was looking at when I started:

Code: 
[root@server1 ~]# uptime
 02:52:24 up  2:54,  4 users,  load average: 90.38, 35.25, 17.95

faeronsayn is on a nicely spec'd VPS, however, is getting a LOT of traffic.

Server wasn't configured at all, standard apache / php / mysql configs, no firewall etc.

So, I've secured the server, installed CSF, fixed an issue with DDOS deflate (server has IPV6 enabled), and tweaked httpd.conf, php.ini, my.cnf

We've also enabled back end file cache for XenForo install, and this is how the server is currently running now:
Code: 
 [root@server1 csf]# uptime
 05:14:43 up  5:16,  4 users,  load average: 0.54, 0.95, 0.92

All in all, a nice way to spend a quiet Sunday morning :-)
 
Upvote 0 Downvote
You must log in or register to reply here.
Top Bottom