Atomicorp

Running centos 6 64 bit. It's a quad core 3 GHz and 4 GB RAM. It flies but my forums recently are getting weird delayed responses.

http://www.aus-city.com/ubbthreads/ubbthreads.php

Quite often I hit new topic or open a post and some 10 seconds go by before it starts to load.

However I got a plain webpage with live weather, off the same site..

http://www.aus-city.com/weather/index.htm

This is fast and responsive. Yes my forum is php based using mysql and on centos 5 or fedora was never an issue.

Server never uses all the RAM, never had resource warnings from plesk.

But it's just pathetic. Waiting 10 seconds for each post read, reply is just useless.

How can I track it down? Is ASL slowing it?

Thanks!
David

If it is only the forum and not other php-based scripts, I'd be inclined to think in terms of MySQL.

Do a "show full processlist" in mysql while doing stuff in the forum to see if there's anything obvious. also turn on slow query logging.

It your my.cnf different on this machine?

Hi Faris,

Yes everything is on the server..

Right now its having those so long delays, about 10 to 15 seconds, but everything looks fine:

top - 08:00:25 up 8 days, 23:31, 2 users, load average: 0.91, 0.65, 0.50
Tasks: 318 total, 1 running, 315 sleeping, 0 stopped, 2 zombie
Cpu(s): 5.9%us, 3.9%sy, 0.0%ni, 88.1%id, 2.1%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 4054584k total, 3630908k used, 423676k free, 376160k buffers
Swap: 6160376k total, 327856k used, 5832520k free, 875744k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1384 ossec 20 0 8904 3320 644 S 13.1 0.1 47:39.13 ossec-analysisd
1659 root 20 0 1139m 321m 3936 S 6.6 8.1 69:40.34 clamd
634 root 20 0 15072 1364 892 R 1.6 0.0 0:00.33 top
1286 root 20 0 80964 4456 3264 S 1.3 0.1 0:00.04 curl
1279 root 20 0 80964 4508 3312 S 1.0 0.1 0:00.03 curl
1290 root 20 0 80832 4072 2976 S 1.0 0.1 0:00.03 curl
1702 popuser 20 0 250m 56m 3244 S 1.0 1.4 0:22.82 spamd
3289 root 20 0 523m 6664 800 S 1.0 0.2 345:44.91 sw-collectd
3018 root 20 0 242m 16m 1696 S 0.3 0.4 0:49.81 spamd
3772 user 20 0 286m 2916 2332 S 0.3 0.1 13:40.85 vino-server
30211 postfix 20 0 94448 14m 3600 S 0.3 0.4 0:00.26 smtpd
32064 apache 20 0 738m 276m 6052 S 0.3 7.0 0:00.78 httpd
1 root 20 0 19236 1044 832 S 0.0 0.0 3:00.46 init
2 root 20 0 0 0 0 S 0.0 0.0 0:00.03 kthreadd
3 root RT 0 0 0 0 S 0.0 0.0 0:00.91 migration/0
4 root 20 0 0 0 0 S 0.0 0.0 0:34.86 ksoftirqd/0
5 root RT 0 0 0 0 S 0.0 0.0 0:00.00 watchdog/0


Right now seems according to who is online, 144 guests. I have seen 600 with centos 5.6 and no slow down at all.

I did this change yesterday seeing if it would help in httpd.conf

#MaxRequestPerChild 0
MaxRequestsPerChild 1000

Also I have got plesk monitoring CPU, Memory and its sending no alerts, so the CPU or RAM is not low..

So if its just one app, as Faris said, you can rule out ASL. If ASL was slowing anything down you would see that across all apps, not just one, and you'd see apache killing itself as well.

Is there some way to track it down? I was thinking of building a new httpd and php from upstream. It makes no sense there are 250 online now and its fine. Was great with centos 5 but with 6 its always doing this intermittently. It even occasionally times out when clicking another post or on a reply.

Is there some way to track it down?

Have you tried what Faris suggested? Can you post the data?

Hi Mike,

How do you do the show full process for mysqld?

I copied this from phpmyadmin

Processes

ID User Host Database Command Time Status SQL query
Kill 156 admin localhost psa Sleep 256 --- ---
Kill 48672 tortix localhost:42242 tortix Sleep 0 --- ---
Kill 55400 auscity localhost auscity Sleep 20 --- ---
Kill 55402 auscity localhost auscity Sleep 15 --- ---
Kill 55403 auscity localhost auscity Sleep 13 --- ---
Kill 55405 auscity localhost auscity Sleep 9 --- ---
Kill 55408 auscity localhost auscity Sleep 5 --- ---
Kill 55409 auscity localhost auscity Sleep 0 --- ---
Kill 55411 admin localhost psa Sleep 0 --- ---
Kill 55412 pma_c4LUQ8s5cvya localhost None Sleep 0 --- ---
Kill 55413 admin localhost mysql Query 0 --- SHOW PROCESSLIST

aus-city wrote:How do you do the show full process for mysqld?
...
Kill 55413 admin localhost mysql Query 0 --- SHOW PROCESSLIST

From terminal/cli in Plesk environment (enter password at prompt): Or in php/mysql code, use:

+-------+---------+-----------------+---------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+---------+-----------------+---------+---------+------+-------+------------------+
| 156 | admin | localhost | psa | Sleep | 29 | | |
| 48672 | tortix | localhost:42242 | tortix | Sleep | 4 | | |
| 68298 | auscity | localhost | auscity | Sleep | 28 | | |
| 68299 | auscity | localhost | auscity | Sleep | 28 | | |
| 68330 | root | localhost | | Query | 0 | | show processlist |
+-------+---------+-----------------+---------+---------+------+-------+------------------+

Sorry -- my fault: You need do the processlist while posting etc on the forum to see if there's something going on in MySQL while you do so.

my shiny nickel is on him not turning query caching on this server.

Hi Faris,

The post a few above I did out of phpmyadmin was when it was acting up.

Processes

ID User Host Database Command Time Status SQL query
Kill 156 admin localhost psa Sleep 256 --- ---
Kill 48672 tortix localhost:42242 tortix Sleep 0 --- ---
Kill 55400 auscity localhost auscity Sleep 20 --- ---
Kill 55402 auscity localhost auscity Sleep 15 --- ---
Kill 55403 auscity localhost auscity Sleep 13 --- ---
Kill 55405 auscity localhost auscity Sleep 9 --- ---
Kill 55408 auscity localhost auscity Sleep 5 --- ---
Kill 55409 auscity localhost auscity Sleep 0 --- ---
Kill 55411 admin localhost psa Sleep 0 --- ---
Kill 55412 pma_c4LUQ8s5cvya localhost None Sleep 0 --- ---
Kill 55413 admin localhost mysql Query 0 --- SHOW PROCESSLIST

How do I check query caching, its a centos default mysql it should be on?

I see this:

Server traffic: These tables show the network traffic statistics of this MySQL server since its startup.
Traffic ø per hour
Received 0.5 GiB 11.9 MiB
Sent 4.5 GiB 0.1 GiB
Total 5.0 GiB 0.1 GiB
Connections ø per hour %
max. concurrent connections 78 --- ---
Failed attempts 6,688 157.60 6.38%
Aborted 132 3.11 0.13%
Total 105 k 2,470.43 100.00%




Server variables and settings Documentation
Variable Session value / Global value Documentation
auto increment increment 1 Documentation
auto increment offset 1 Documentation
autocommit ON Documentation
automatic sp privileges ON Documentation
back log 50 Documentation
basedir /usr/ Documentation
big tables OFF Documentation
binlog cache size 32,768 Documentation
binlog direct non transactional updates OFF Documentation
binlog format STATEMENT Documentation
bulk insert buffer size 8,388,608 Documentation
character set client utf8 Documentation
(Global value) latin1
character set connection utf8 Documentation
(Global value) latin1
character set database latin1 Documentation
character set filesystem binary Documentation
character set results utf8 Documentation
(Global value) latin1
character set server latin1 Documentation
character set system utf8 Documentation
character sets dir /usr/share/mysql/charsets/ Documentation
collation connection utf8_general_ci Documentation
(Global value) latin1_swedish_ci
collation database latin1_swedish_ci Documentation
collation server latin1_swedish_ci Documentation
completion type 0 Documentation
concurrent insert 1 Documentation
connect timeout 10 Documentation
datadir /var/lib/mysql/ Documentation
date format %Y-%m-%d Documentation
datetime format %Y-%m-%d %H:%i:%s Documentation
default week format 0 Documentation
delay key write ON Documentation
delayed insert limit 100 Documentation
delayed insert timeout 300 Documentation
delayed queue size 1,000 Documentation
div precision increment 4 Documentation
engine condition pushdown ON Documentation
error count 0 Documentation
event scheduler OFF Documentation
expire logs days 0 Documentation
flush OFF Documentation
flush time 0 Documentation
foreign key checks ON Documentation
ft boolean syntax + -><()~*:""&| Documentation
ft max word len 84 Documentation
ft min word len 4 Documentation
ft query expansion limit 20 Documentation
ft stopword file (built-in) Documentation
general log OFF Documentation
general log file /var/run/mysqld/mysqld.log Documentation
group concat max len 1,024 Documentation
have community features YES
have compress YES Documentation
have crypt YES Documentation
have csv YES Documentation
have dynamic loading YES Documentation
have geometry YES Documentation
have innodb YES Documentation
have ndbcluster NO
have openssl DISABLED Documentation
have partitioning YES Documentation
have query cache YES Documentation
have rtree keys YES Documentation
have ssl DISABLED Documentation
have symlink DISABLED Documentation
hostname xx Documentation
identity 0 Documentation
ignore builtin innodb OFF Documentation
init connect Documentation
init file Documentation
init slave Documentation
innodb adaptive hash index ON Documentation
innodb additional mem pool size 1,048,576 Documentation
innodb autoextend increment 8 Documentation
innodb autoinc lock mode 1 Documentation
innodb buffer pool size 8,388,608 Documentation
innodb checksums ON Documentation
innodb commit concurrency 0 Documentation
innodb concurrency tickets 500 Documentation
innodb data file path ibdata1:10M:autoextend Documentation
innodb data home dir Documentation
innodb doublewrite ON Documentation
innodb fast shutdown 1 Documentation
innodb file io threads 4
innodb file per table OFF Documentation
innodb flush log at trx commit 1 Documentation
innodb flush method Documentation
innodb force recovery 0 Documentation
innodb lock wait timeout 50 Documentation
innodb locks unsafe for binlog OFF Documentation
innodb log buffer size 1,048,576 Documentation
innodb log file size 5,242,880 Documentation
innodb log files in group 2 Documentation
innodb log group home dir ./ Documentation
innodb max dirty pages pct 90 Documentation
innodb max purge lag 0 Documentation
innodb mirrored log groups 1 Documentation
innodb open files 300 Documentation
innodb rollback on timeout OFF Documentation
innodb stats on metadata ON Documentation
innodb support xa ON Documentation
innodb sync spin loops 20 Documentation
innodb table locks ON Documentation
innodb thread concurrency 8 Documentation
innodb thread sleep delay 10,000 Documentation
innodb use legacy cardinality algorithm ON
insert id 0 Documentation
interactive timeout 28,800 Documentation
join buffer size 131,072 Documentation
keep files on create OFF Documentation
key buffer size 8,384,512 Documentation
key cache age threshold 300 Documentation
key cache block size 1,024 Documentation
key cache division limit 100 Documentation
language /usr/share/mysql/english/ Documentation
large files support ON Documentation
large page size 0 Documentation
large pages OFF Documentation
last insert id 0 Documentation
lc time names en_US Documentation
license GPL Documentation
local infile OFF Documentation
locked in memory OFF Documentation
log OFF Documentation
log bin OFF Documentation
log bin trust function creators OFF Documentation
log bin trust routine creators OFF
log error /var/log/mysqld.log Documentation
log output FILE Documentation
log queries not using indexes OFF Documentation
log slave updates OFF Documentation
log slow queries OFF Documentation
log warnings 1 Documentation
long query time 10 Documentation
low priority updates OFF Documentation
lower case file system OFF Documentation
lower case table names 0 Documentation
max allowed packet 1,048,576 Documentation
max binlog cache size 18446744073709547520 Documentation
max binlog size 1,073,741,824 Documentation
max connect errors 10 Documentation
max connections 151 Documentation
max delayed threads 20 Documentation
max error count 64 Documentation
max heap table size 16,777,216 Documentation
max insert delayed threads 20 Documentation
max join size 18446744073709551615 Documentation
max length for sort data 1,024 Documentation
max prepared stmt count 16,382 Documentation
max relay log size 0 Documentation
max seeks for key 18446744073709551615 Documentation
max sort length 1,024 Documentation
max sp recursion depth 0 Documentation
max tmp tables 32 Documentation
max user connections 0 Documentation
max write lock count 18446744073709551615 Documentation
min examined row limit 0 Documentation
multi range count 256
myisam data pointer size 6 Documentation
myisam max sort file size 9223372036853727232 Documentation
myisam mmap size 18446744073709551615 Documentation
myisam recover options OFF Documentation
myisam repair threads 1 Documentation
myisam sort buffer size 8,388,608 Documentation
myisam stats method nulls_unequal Documentation
myisam use mmap OFF Documentation
net buffer length 16,384 Documentation
net read timeout 30 Documentation
net retry count 10 Documentation
net write timeout 60 Documentation
new OFF Documentation
old OFF Documentation
old alter table OFF Documentation
old passwords OFF Documentation
open files limit 1,024 Documentation
optimizer prune level 1 Documentation
optimizer search depth 62 Documentation
optimizer switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on Documentation
pid file /var/run/mysqld/mysqld.pid Documentation
plugin dir /usr/lib64/mysql/plugin Documentation
port 3,306 Documentation
preload buffer size 32,768 Documentation
profiling OFF Documentation
profiling history size 15 Documentation
protocol version 10 Documentation
pseudo thread id 104,777 Documentation
(Global value) 0
query alloc block size 8,192 Documentation
query cache limit 1,048,576 Documentation
query cache min res unit 4,096 Documentation
query cache size 0 Documentation
query cache type ON Documentation
query cache wlock invalidate OFF Documentation
query prealloc size 8,192 Documentation
rand seed1 Documentation
rand seed2 Documentation
range alloc block size 4,096 Documentation
read buffer size 131,072 Documentation
read only OFF Documentation
read rnd buffer size 262,144 Documentation
relay log
relay log index Documentation
relay log info file relay-log.info Documentation
relay log purge ON Documentation
relay log space limit 0 Documentation
report host Documentation
report password Documentation
report port 3,306 Documentation
report user Documentation
rpl recovery rank 0 Documentation
secure auth OFF Documentation
secure file priv Documentation
server id 0 Documentation
skip external locking ON Documentation
skip name resolve OFF Documentation
skip networking OFF Documentation
skip show database OFF Documentation
slave compressed protocol OFF Documentation
slave exec mode STRICT Documentation
slave load tmpdir /tmp Documentation
slave net timeout 3,600 Documentation
slave skip errors OFF Documentation
slave transaction retries 10 Documentation
slow launch time 2 Documentation
slow query log OFF Documentation
slow query log file /var/run/mysqld/mysqld-slow.log Documentation
socket /var/lib/mysql/mysql.sock Documentation
sort buffer size 2,097,144 Documentation
sql auto is null ON Documentation
sql big selects ON Documentation
sql big tables OFF Documentation
sql buffer result OFF Documentation
sql log bin ON Documentation
sql log off OFF Documentation
sql log update ON Documentation
sql low priority updates OFF Documentation
sql max join size 18446744073709551615 Documentation
sql mode Documentation
sql notes ON Documentation
sql quote show create ON Documentation
sql safe updates OFF Documentation
sql select limit 18446744073709551615 Documentation
sql slave skip counter Documentation
sql warnings OFF Documentation
ssl ca Documentation
ssl capath Documentation
ssl cert Documentation
ssl cipher Documentation
ssl key Documentation
storage engine MyISAM Documentation
sync binlog 0 Documentation
sync frm ON Documentation
system time zone EST Documentation
table definition cache 256 Documentation
table lock wait timeout 50 Documentation
table open cache 64 Documentation
table type MyISAM Documentation
thread cache size 0 Documentation
thread handling one-thread-per-connection Documentation
thread stack 262,144 Documentation
time format %H:%i:%s Documentation
time zone SYSTEM Documentation
timed mutexes OFF Documentation
timestamp 1,316,043,245 Documentation
tmp table size 16,777,216 Documentation
tmpdir /tmp Documentation
transaction alloc block size 8,192 Documentation
transaction prealloc size 4,096 Documentation
tx isolation REPEATABLE-READ Documentation
unique checks ON Documentation
updatable views with limit YES Documentation
version 5.1.52 Documentation
version comment Source distribution Documentation
version compile machine x86_64 Documentation
version compile os unknown-linux-gnu Documentation
wait timeout 28,800 Documentation
warning count 0 Documentation

I played with SQL and it got a bit faster, but still this silly 15 second intermittent delay.

I added in my.cnf

query_cache_size=32M
tmp_table_size=128M
max_heap_table_size=128M
key_buffer_size=32M
table_cache=128


Then I saw a timeout showing a proxy server error page.

But I am directly connected to the server, no proxy. Then I looked in httpd.conf and then disabled all this:


#LoadModule proxy_module modules/mod_proxy.so
#LoadModule proxy_balancer_module modules/mod_proxy_balancer.so
#LoadModule proxy_ftp_module modules/mod_proxy_ftp.so
#LoadModule proxy_http_module modules/mod_proxy_http.so
#LoadModule proxy_ajp_module modules/mod_proxy_ajp.so
#LoadModule proxy_connect_module modules/mod_proxy_connect.so

Now the issue is gone, no more delays and it acts as it should.

What is this damn thing for anyway, besides intermittently and randomly giving you 15 second delays in a php / SQL forum (maybe tine to reflect on what your going to do next) ?

Also I changed this in httpd.conf:

#MaxRequestPerChild 0
MaxRequestsPerChild 1000

Should I leave it as 1000?

Thanks!

So not caused by ASL.