Forum loads slowly or times out. Please help.

محروم.كوم · #1 07-27-2009, 08:20 PM

Recently, our users complained that the forum loads slowly and always times out. After checking the database and the related environment, our technicians found that the database tables keep being blocked. And we tried to optimize the tables, but since the data is large, it usually loads over time and gets timed out.

Our forum version is 3.6.8 Patch Level 1.

Two sites(forums) share one server.
Here is the configuration of the server:
CPU:
[root@db1 ~]# cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 23
model name : Intel(R) Xeon(R) CPU E5405 @ 2.00GHz
stepping : 6
cpu MHz : 1995.004
cache size : 6144 KB
physical id : 0
siblings : 4
core id : 0
cpu cores : 4
fpu : yes
fpu_exception : yes
cpuid level : 10
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm constant_tsc pni monitor ds_cpl vmx tm2 cx16 xtpr lahf_lm
bogomips : 3992.89
clflush size : 64
cache_alignment : 64
address sizes : 38 bits physical, 48 bits virtual
power management:
processor : 1
vendor_id : GenuineIntel
cpu family : 6
model : 23
model name : Intel(R) Xeon(R) CPU E5405 @ 2.00GHz
stepping : 6
cpu MHz : 1995.004
cache size : 6144 KB
physical id : 1
siblings : 4
core id : 0
cpu cores : 4
fpu : yes
fpu_exception : yes
cpuid level : 10
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm constant_tsc pni monitor ds_cpl vmx tm2 cx16 xtpr lahf_lm

bogomips : 3990.04
clflush size : 64
cache_alignment : 64
address sizes : 38 bits physical, 48 bits virtual
power management:
processor : 2
vendor_id : GenuineIntel
cpu family : 6
model : 23
model name : Intel(R) Xeon(R) CPU E5405 @ 2.00GHz
stepping : 6
cpu MHz : 1995.004
cache size : 6144 KB
physical id : 0
siblings : 4
core id : 2
cpu cores : 4
fpu : yes
fpu_exception : yes
cpuid level : 10
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm constant_tsc pni monitor ds_cpl vmx tm2 cx16 xtpr lahf_lm
bogomips : 3990.00
clflush size : 64
cache_alignment : 64
address sizes : 38 bits physical, 48 bits virtual
power management:
processor : 3
vendor_id : GenuineIntel
cpu family : 6
model : 23
model name : Intel(R) Xeon(R) CPU E5405 @ 2.00GHz
stepping : 6
cpu MHz : 1995.004
cache size : 6144 KB
physical id : 1
siblings : 4
core id : 2
cpu cores : 4
fpu : yes
fpu_exception : yes
cpuid level : 10
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm constant_tsc pni monitor ds_cpl vmx tm2 cx16 xtpr lahf_lm
bogomips : 3990.01
clflush size : 64
cache_alignment : 64
address sizes : 38 bits physical, 48 bits virtual
power management:
processor : 4
vendor_id : GenuineIntel
cpu family : 6
model : 23
model name : Intel(R) Xeon(R) CPU E5405 @ 2.00GHz
stepping : 6
cpu MHz : 1995.004
cache size : 6144 KB
physical id : 0
siblings : 4
core id : 1
cpu cores : 4
fpu : yes
fpu_exception : yes
cpuid level : 10
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm constant_tsc pni monitor ds_cpl vmx tm2 cx16 xtpr lahf_lm
bogomips : 3990.00
clflush size : 64
cache_alignment : 64
address sizes : 38 bits physical, 48 bits virtual
power management:
processor : 5
vendor_id : GenuineIntel
cpu family : 6
model : 23
model name : Intel(R) Xeon(R) CPU E5405 @ 2.00GHz
stepping : 6
cpu MHz : 1995.004
cache size : 6144 KB
physical id : 1
siblings : 4
core id : 1
cpu cores : 4
fpu : yes
fpu_exception : yes
cpuid level : 10
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm constant_tsc pni monitor ds_cpl vmx tm2 cx16 xtpr lahf_lm
bogomips : 3990.04
clflush size : 64
cache_alignment : 64
address sizes : 38 bits physical, 48 bits virtual
power management:
processor : 6
vendor_id : GenuineIntel
cpu family : 6
model : 23
model name : Intel(R) Xeon(R) CPU E5405 @ 2.00GHz
stepping : 6
cpu MHz : 1995.004
cache size : 6144 KB
physical id : 0
siblings : 4
core id : 3
cpu cores : 4
fpu : yes
fpu_exception : yes
cpuid level : 10
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm constant_tsc pni monitor ds_cpl vmx tm2 cx16 xtpr lahf_lm
bogomips : 3990.02
clflush size : 64
cache_alignment : 64
address sizes : 38 bits physical, 48 bits virtual
power management:
processor : 7
vendor_id : GenuineIntel
cpu family : 6
model : 23
model name : Intel(R) Xeon(R) CPU E5405 @ 2.00GHz
stepping : 6
cpu MHz : 1995.004
cache size : 6144 KB
physical id : 1
siblings : 4
core id : 3
cpu cores : 4
fpu : yes
fpu_exception : yes
cpuid level : 10
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm constant_tsc pni monitor ds_cpl vmx tm2 cx16 xtpr lahf_lm
bogomips : 3990.03
clflush size : 64
cache_alignment : 64
address sizes : 38 bits physical, 48 bits virtual
power management:
-----------------------------------------------------
MEMEORY:
[root@db1 ~]# cat /proc/meminfo
MemTotal: 4039832 kB
MemFree: 91968 kB
Buffers: 5932 kB
Cached: 1008208 kB
SwapCached: 87496 kB
Active: 3669128 kB
Inactive: 146316 kB
HighTotal: 0 kB
HighFree: 0 kB
LowTotal: 4039832 kB
LowFree: 91968 kB
SwapTotal: 2096440 kB
SwapFree: 1691396 kB
Dirty: 844 kB
Writeback: 0 kB
AnonPages: 2800180 kB
Mapped: 11620 kB
Slab: 49608 kB
PageTables: 47064 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
CommitLimit: 4116356 kB
Committed_AS: 4153376 kB
VmallocTotal: 34359738367 kB
VmallocUsed: 263672 kB
VmallocChunk: 34359474347 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
Hugepagesize: 2048 kB
[root@db1 ~]# free -m
total used free shared buffers cached
Mem: 3945 3886 58 0 5 1016
-/+ buffers/cache: 2864 1080
Swap: 2047 395 1651
You have new mail in /var/spool/mail/root
-----------------------------------------------
DISK:
[root@db1 data]# df
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sda2 3050092 508680 2383976 18% /
/dev/sda6 61600408 2839232 55581516 5% /usr
/dev/sda3 2030768 624628 1301316 33% /var
/dev/sda1 101086 22952 72915 24% /boot
tmpfs 2019916 0 2019916 0% /dev/shm
/dev/md0 139852616 75927700 56820820 58% /data
/dev/sde1 69924732 16530756 49842008 25% /backup
[root@db1 data]# cat /proc/mdstat
Personalities : [raid6] [raid5] [raid4]
md0 : active raid5 sdd[2] sdc[1] sdb[0]
142081920 blocks level 5, 64k chunk, algorithm 2 [3/3] [UUU]
unused devices:
[root@db1 data]#
----------------------------------------------
OS:
[root@db1 data]# cat /etc/redhat-release
CentOS release 5.3 (Final)
[root@db1 data]# uname -a
Linux db1.usa.XXXXX 2.6.18-92.1.10.el5 #1 SMP Tue Aug 5 07:42:41 EDT 2008 x86_64 x86_64 x86_64 GNU/Linux
----------------------------------------------
WEBserver: nginx-0.7.59
PHP: php-fcgi-5.2.8
MYSQL:Mysql-4.0.30 & skip innodb
Version of vB :3.6.8 patch level 1
----------------------------------------------
TOP:
top - 0113 up 294 days, 6:13, 3 users, load average: 2.84, 3.06, 2.43
Tasks: 260 total, 1 running, 259 sleeping, 0 stopped, 0 zombie
Cpu0 : 0.3%us, 0.9%sy, 0.0%ni, 0.0%id, 98.8%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu1 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu2 : 5.2%us, 0.6%sy, 0.0%ni, 93.8%id, 0.0%wa, 0.0%hi, 0.3%si, 0.0%st
Cpu3 : 3.4%us, 0.9%sy, 0.0%ni, 0.0%id, 95.7%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu4 : 9.5%us, 0.9%sy, 0.0%ni, 86.2%id, 3.4%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu5 : 1.5%us, 0.0%sy, 0.0%ni, 98.5%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu6 : 2.8%us, 0.0%sy, 0.0%ni, 97.2%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu7 : 8.0%us, 0.3%sy, 0.0%ni, 88.6%id, 3.1%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 4039832k total, 4013780k used, 26052k free, 7012k buffers
Swap: 2096440k total, 405044k used, 1691396k free, 1072296k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
8036 mysql 15 0 1609m 585m 2196 S 12.9 14.8 490:23.69 mysqld
22730 nobody 16 0 182m 48m 3816 S 5.2 1.2 24:05.35 php-cgi
22774 nobody 16 0 149m 18m 3816 S 4.9 0.5 23:40.91 php-cgi
22834 nobody 16 0 152m 21m 3840 S 2.8 0.6 24:03.37 php-cgi
22791 nobody 16 0 149m 19m 3812 S 2.5 0.5 24:14.50 php-cgi
22825 nobody 16 0 152m 20m 4076 S 2.2 0.5 23:58.85 php-cgi
22808 nobody 16 0 153m 23m 4124 S 1.5 0.6 24:04.25 php-cgi
20110 root 10 -5 0 0 0 S 0.6 0.0 194:42.69 md0_raid5
----------------------------------------------
my.cnf:
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
log_slow_queries=/data/mysql_log/slow_query.log
log=/data/mysql_log/querylog.log
datadir=/data/mysql_data
log-bin=/data/log-bin/log-bin
#log-slave-updates
#default-character-set=utf8
key_buffer = 384M
max_allowed_packet = 2M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size= 32M
thread_concurrency = 16
read_rnd_buffer_size = 8M
thread_cache_size = 128
query_cache_size= 128M
query-cache_type=1

max_connect_errors=9999999
max_connections=2048
long_query_time=3
wait_timeout=300
skip-name-resolve
skip-innodb
skip-bdb
lower_case_table_names=1
back_log=300
#skip-networking
max_tmp_tables=32M
tmp_table_size = 2048M
# Replication Master Server (default)
# binary logging is required for replication
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL

#safe-updates
[isamchk]
key_buffer = 8M
sort_buffer_size = 8M
[myisamchk]
key_buffer = 8M
sort_buffer_size = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit=65535
----------------------------------------------
mysqladmin -u root -p var ext stat ver proc
+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| back_log | 300 |
| basedir | /usr/local/mysql-4.0.30/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set | latin1 |
| character_sets | latin1 big5 cp1251 cp1257 croat czech danish dec8 dos estonia euc_kr gb2312 gbk german1 greek hebrew hp8 hungarian koi8_ru koi8_ukr latin1_de latin2 latin5 sjis swe7 tis620 ujis usa7 win1250 win1251ukr win1251 |
| concurrent_insert | ON |
| connect_timeout | 5 |
| convert_character_set | |
| datadir | /data/mysql_data/ |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + ->t | conquer | Sleep | 153 | | |br / | 37786 | conquer | localhost | conquer | Sleep | 41 | | |br / | 92123 | conquer | localhost | conquer | Sleep | 18 | | |br / | 124199 | conquer | localhost | conquer | Sleep | 25 | | |br / | 125901 | conquer | localhost | conquer | Sleep | 21 | | |br / | 151964 | conquer | localhost | conquer | Sleep | 9 | | |br / | 157026 | root | localhost | | Query | 0 | | show processlist |br / +--------+----------+---------------------+---------+-------------+--------+----------------------------------------------------------------+------------------+br / br / ------------------------------------------------------------------------br / ./mysqlreport --password XXXXXXXXXX -r 10 > /statistics.txt
#
# Beginning report, 0 0:0:0
#
MySQL 4.0.30-Community- uptime 5 656 Mon Jul 27 01:47:48 2009
__ Key __________________________________________________ _______________
Buffer used 352.46M of 384.00M %Used: 91.79
Write hit 34.39%
Read hit 99.87%
__ Questions __________________________________________________ _________
Total 12.34M 27.2/s
DMS 5.64M 12.4/s %Total: 45.73
QC Hits 5.21M 11.5/s 42.20
Com_ 2.50M 5.5/s 20.23
-Unknown 1.17M 2.6/s 9.44
COM_QUIT 157.08k 0.3/s 1.27
Slow 3 s 36.43k 0.1/s 0.30 %DMS: 0.65 Log: ON
DMS 5.64M 12.4/s 45.73
SELECT 4.01M 8.8/s 32.46 70.98
UPDATE 1.07M 2.4/s 8.69 19.01
INSERT 445.75k 1.0/s 3.61 7.90
REPLACE 64.11k 0.1/s 0.52 1.14
DELETE 54.99k 0.1/s 0.45 0.97
Com_ 2.50M 5.5/s 20.23
change_db 1.31M 2.9/s 10.63
admin_comma 1.17M 2.6/s 9.45
show_fields 7.85k 0.0/s 0.06
__ SELECT and Sort __________________________________________________ ___
Scan 804.43k 1.8/s %SELECT: 20.08
Range 1.02M 2.3/s 25.55
Full join 278 0.0/s 0.01
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 127.14k 0.3/s
Sort range 1.01M 2.2/s
Sort mrg pass 6.43k 0.0/s
__ Query Cache __________________________________________________ _______
Memory usage 72.85M of 128.00M %Used: 56.91
Block Fragmnt 22.64%
Hits 5.21M 11.5/s
Inserts 3.75M 8.2/s
Insrt:Prune 647.22:1 8.2/s
Hit:Insert 1.39:1
__ Table Locks __________________________________________________ _______
Waited 655.37k 1.4/s %Total: 5.74
Immediate 10.77M 23.7/s
__ Tables __________________________________________________ ____________
Open 512 of 512 %Cache: 100.00
Opened 705.61k 1.6/s
__ Connections __________________________________________________ _______
Max used 155 of 2048 %Max: 7.57
Total 157.10k 0.3/s
__ Created Temp __________________________________________________ ______
Disk table 1.52k 0.0/s
Table 229.13k 0.5/s Size: 2.0G
File 12.86k 0.0/s
__ Threads __________________________________________________ ___________
Running 4 of 130
Cached 26 of 128 %Hit: 99.90
Created 156 0.0/s
Slow 0 0/s
__ Aborted __________________________________________________ ___________
Clients 38 0.0/s
Connects 5 0.0/s
__ Bytes __________________________________________________ _____________
Sent 796.46M 1.8k/s
Received 4.06G 8.9k/s
#
# Interval report 1, +0 0:0:10
#
MySQL 4.0.30-Community- uptime 5 66 Mon Jul 27 01:47:58 2009
__ Key __________________________________________________ _______________
Buffer used 352.46M of 384.00M %Used: 91.79
Write hit 0.00%
Read hit 100.00%
__ Questions __________________________________________________ _________
Total 191 19.1/s
QC Hits 86 8.6/s %Total: 45.03
DMS 73 7.3/s 38.22
Com_ 38 3.8/s 19.90
-Unknown 12 1.2/s 6.28
COM_QUIT 6 0.6/s 3.14
Slow 3 s 0 0/s 0.00 %DMS: 0.00 Log: ON
DMS 73 7.3/s 38.22
SELECT 53 5.3/s 27.75 72.60
UPDATE 15 1.5/s 7.85 20.55
INSERT 5 0.5/s 2.62 6.85
REPLACE 0 0/s 0.00 0.00
DELETE 0 0/s 0.00 0.00
Com_ 38 3.8/s 19.90
change_db 21 2.1/s 10.99
admin_comma 14 1.4/s 7.33
show_status 1 0.1/s 0.52
__ SELECT and Sort __________________________________________________ ___
Scan 14 1.4/s %SELECT: 26.42
Range 8 0.8/s 15.09
Full join 0 0/s 0.00
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 2 0.2/s
Sort range 14 1.4/s
Sort mrg pass 1 0.1/s
__ Query Cache __________________________________________________ _______
Memory usage 73.28M of 128.00M %Used: 57.25
Block Fragmnt 22.52%
Hits 86 8.6/s
Inserts 53 5.3/s
Insrt:Prune 53:1 5.2/s
Hit:Insert 1.62:1
__ Table Locks __________________________________________________ _______
Waited 0 0/s %Total: 0.00
Immediate 171 17.1/s
__ Tables __________________________________________________ ____________
Open 512 of 512 %Cache: 100.00
Opened 0 0/s
__ Connections __DEFINE_LIKE_SHARE__

المواضيع المتشابهه
الموضوع	كاتب الموضوع	المنتدى	مشاركات	آخر مشاركة
Forum Slow page loads in IE	محروم.كوم	منتدى أخبار المواقع والمنتديات العربية والأجنبية	0	07-15-2010 07:50 PM
Forum Loads in chrome but not I.E. or Firefox	محروم.كوم	منتدى أخبار المواقع والمنتديات العربية والأجنبية	0	04-22-2010 06:30 AM
Forum Forum Index Loading Slowly	محروم.كوم	منتدى أخبار المواقع والمنتديات العربية والأجنبية	0	02-10-2010 11:22 AM
Forum CMS Home... how do I make this the page that loads and not the forum???	محروم.كوم	منتدى أخبار المواقع والمنتديات العربية والأجنبية	0	01-09-2010 06:50 PM
Forum loads slowly or times out. Please help.	محروم.كوم	منتدى أخبار المواقع والمنتديات العربية والأجنبية	0	07-27-2009 09:10 PM