PDA

View Full Version : Server Optimisation Request Please.


NickFritz
Wed 27th Jun '07, 9:36pm
1. Is this on dedicated or shared virual server. If shared, how many sites share this server (ask web host if needed)

Dedicated but with two forums.

2. your server specs. For example:

Dual Opteron 246

2gb ram

73gb SCSI 10k/15k no idea which rpm

Centos 4

apache 2.0.59

php 5.2.4


MySQL version: mysql 4.0.25

3. what version of vB are you running ?

latest 3.6.7

4. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server ?

no

5. if possible how mysql was compiled/installed

rpm from mysql.com

6. your top stats

top - 19:21:13 up 8 days, 18:06, 1 user, load average: 0.80, 6.09, 8.39
Tasks: 313 total, 5 running, 308 sleeping, 0 stopped, 0 zombie
Cpu(s): 18.2% us, 2.5% sy, 0.0% ni, 74.7% id, 4.5% wa, 0.1% hi, 0.0% si
Mem: 2074080k total, 1620264k used, 453816k free, 26092k buffers
Swap: 2040244k total, 47536k used, 1992708k free, 446936k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
17120 apache 15 0 188m 14m 5728 S 6.3 0.7 0:00.24 httpd
17086 apache 15 0 188m 13m 5104 S 5.0 0.7 0:00.19 httpd
17088 apache 15 0 188m 16m 8388 S 4.5 0.8 0:00.26 httpd
17034 apache 16 0 188m 15m 7336 S 4.2 0.8 0:00.24 httpd
17085 apache 15 0 188m 14m 6124 S 3.9 0.7 0:00.32 httpd
16558 apache 15 0 189m 21m 13m S 3.4 1.1 0:01.97 httpd
16646 apache 15 0 189m 18m 10m S 3.4 0.9 0:01.19 httpd
3616 mysql 11 -5 598m 532m 2764 S 1.8 26.3 4:36.32 mysqld
17021 apache 16 0 191m 18m 7624 R 1.8 0.9 0:00.31 httpd
5657 mysql 11 -5 598m 532m 2764 S 1.3 26.3 8:51.43 mysqld
16588 apache 17 0 190m 23m 13m R 1.1 1.2 0:00.93 httpd
5137 mysql 11 -5 598m 532m 2764 S 0.8 26.3 21:46.28 mysqld
3271 mysql 10 -5 598m 532m 2764 S 0.8 26.3 19:22.33 mysqld
3324 mysql 11 -5 598m 532m 2764 S 0.5 26.3 9:20.98 mysqld
5135 mysql 11 -5 598m 532m 2764 S 0.3 26.3 9:13.65 mysqld
5140 mysql 11 -5 598m 532m 2764 S 0.3 26.3 16:21.60 mysqld
3231 mysql 11 -5 598m 532m 2764 S 0.3 26.3 5:57.29 mysqld
3269 mysql 11 -5 598m 532m 2764 R 0.3 26.3 35:20.93 mysqld
3632 mysql 10 -5 598m 532m 2764 S 0.3 26.3 4:47.19 mysqld
16660 apache 15 0 188m 17m 9424 S 0.3 0.9 0:00.91 httpd
16687 apache 16 0 188m 14m 6120 S 0.3 0.7 0:00.65 httpd
17099 apache 15 0 188m 15m 7620 S 0.3 0.8 0:00.27 httpd
17119 root 16 0 2868 1132 772 R 0.3 0.1 0:00.04 top
1 root 16 0 2144 560 480 S 0.0 0.0 0:02.24 init
2 root RT 0 0 0 0 S 0.0 0.0 0:00.85 migration/0
3 root 34 19 0 0 0 S 0.0 0.0 0:00.16 ksoftirqd/0
4 root RT 0 0 0 0 S 0.0 0.0 0:01.21 migration/1
5 root 34 19 0 0 0 S 0.0 0.0 0:00.10 ksoftirqd/1
6 root 5 -10 0 0 0 S 0.0 0.0 0:00.02 events/0
7 root 5 -10 0 0 0 S 0.0 0.0 0:00.03 events/1
8 root 7 -10 0 0 0 S 0.0 0.0 0:00.00 khelper
9 root 15 -10 0 0 0 S 0.0 0.0 0:00.00 kacpid
45 root 5 -10 0 0 0 S 0.0 0.0 0:00.00 kblockd/0
46 root 5 -10 0 0 0 S 0.0 0.0 0:00.01 kblockd/1
47 root 15 0 0 0 0 S 0.0 0.0 0:00.00 khubd
67 root 6 -10 0 0 0 S 0.0 0.0 0:00.00 aio/0
68 root 6 -10 0 0 0 S 0.0 0.0 0:00.00 aio/1
66 root 15 0 0 0 0 S 0.0 0.0 1:24.85 kswapd0
212 root 25 0 0 0 0 S 0.0 0.0 0:00.00 kseriod


7. your mysql configuration variables located at /etc/my.cnf or c:\my.cnf or my.ini so post the contents inside of my.cnf (minus any passwords of course).

[client]
socket=/var/lib/mysql/mysql.sock

[mysqld]
back_log = 50
skip-innodb
max_connections = 500
key_buffer = 324M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 2200
thread_cache_size = 384
wait_timeout = 200
connect_timeout = 10
tmp_table_size = 128M
max_heap_table_size = 128M
max_allowed_packet = 64M
max_connect_errors = 10
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
query_cache_limit = 6M
query_cache_size = 128M
query_cache_type = 1
query_prealloc_size = 65536
query_alloc_block_size = 131072
default-storage-engine = MyISAM

[mysqld_safe]
nice = -5
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M


8. MySQL stats from ssh telnet as root user type:
Quote:


MySQL 5.0.41-community uptime 2 18:1:7 Wed Jun 27 19:16:40 2007

__ Key __________________________________________________ _______________
Buffer used 287.19M of 324.00M %Used: 88.64
Current 324.00M %Usage: 100.00
Write hit 46.65%
Read hit 99.99%

__ Questions __________________________________________________ _________
Total 16.85M 70.9/s
DMS 9.05M 38.1/s %Total: 53.72
QC Hits 6.15M 25.9/s 36.53
Com_ 966.89k 4.1/s 5.74
COM_QUIT 674.14k 2.8/s 4.00
+Unknown 1.14k 0.0/s 0.01
Slow 352 0.0/s 0.00 %DMS: 0.00
DMS 9.05M 38.1/s 53.72
SELECT 5.92M 24.9/s 35.17 65.47
UPDATE 2.35M 9.9/s 13.95 25.97
DELETE 334.35k 1.4/s 1.98 3.69
INSERT 287.95k 1.2/s 1.71 3.18
REPLACE 153.14k 0.6/s 0.91 1.69
Com_ 966.89k 4.1/s 5.74
change_db 701.74k 3.0/s 4.17
begin 114.16k 0.5/s 0.68
commit 90.90k 0.4/s 0.54

__ SELECT and Sort __________________________________________________ ___
Scan 1.02M 4.3/s %SELECT: 17.28
Range 737.71k 3.1/s 12.45
Full join 17.45k 0.1/s 0.29
Range check 0 0/s 0.00
Full rng join 330 0.0/s 0.01
Sort scan 379.47k 1.6/s
Sort range 1.04M 4.4/s
Sort mrg pass 53.50k 0.2/s

__ Query Cache __________________________________________________ _______
Memory usage 59.44M of 128.00M %Used: 46.44
Block Fragmnt 18.45%
Hits 6.15M 25.9/s
Inserts 5.86M 24.6/s
Insrt:Prune 16.01:1 23.1/s
Hit:Insert 1.05:1

__ Table Locks __________________________________________________ _______
Waited 43.52k 0.2/s %Total: 0.30
Immediate 14.55M 61.2/s

__ Tables __________________________________________________ ____________
Open 2198 of 2200 %Cache: 99.91
Opened 5.45k 0.0/s

__ Connections __________________________________________________ _______
Max used 154 of 500 %Max: 30.80
Total 674.17k 2.8/s

__ Created Temp __________________________________________________ ______
Disk table 79.98k 0.3/s
Table 258.89k 1.1/s
File 43.03k 0.2/s

__ Threads __________________________________________________ ___________
Running 1 of 1
Cached 153 of 384 %Hit: 99.98
Created 154 0.0/s
Slow 0 0/s

__ Aborted __________________________________________________ ___________
Clients 38 0.0/s
Connects 12 0.0/s

__ Bytes __________________________________________________ _____________
Sent 2.85G 12.0k/s
Received 114.79M 483.0/s

__ InnoDB Buffer Pool __________________________________________________
Usage 0 of 0 %Used: 0.00
Read ratio 0.000
Pages
Free 0 %Total: 0.00
Data 0 0.00 %Drty: 0.00
Misc 0 0.00
Latched 0 0.00
Reads 0 0/s
From file 0 0/s 0.00
Ahead Rnd 0 0/s
Ahead Sql 0 0/s
Writes 0 0/s
Flushes 0 0/s
Wait Free 0 0/s

__ InnoDB Lock __________________________________________________ _______
Waits 0 0/s
Current 0
Time acquiring
Total 0 ms
Average 0 ms
Max 0 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
Reads 0 0/s
Writes 0 0/s
fsync 0 0/s
Pending
Reads 0
Writes 0
fsync 0

Pages
Created 0 0/s
Read 0 0/s
Written 0 0/s

Rows
Deleted 0 0/s
Inserted 0 0/s
Read 0 0/s
Updated 0 0/s




9. is your vB the only thing on the server? or other scripts & sites which utilise php and mysql?

vb plus a few smaller sites but only small things that do php and mysql.

10. how many average and max concurrent users on your vB forum ? and what your cookie timeout is ?

cookies timeout is 1800 for both and 200-300 on each at peak times. so 600 total.

11. create a file named phpinfo.php and place this code in it and post the url/link to it from your web site

http://www.toontowncentral.com/phpinfo.php


12. if you run Apache and you have your own dedicated server or access to your httpd.conf (apache configuration file) can you post the values you have set for the following :


Timeout 300
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15

others not set but seems to be set in other <> areas

13. check to see if any files i.e. apache log files are hitting 2GB or 4GB max file size limits i.e. see if you have max file size exceeded messages in apache error log

find: /proc/17888/task: No such file or directory
find: /proc/17893: No such file or directory
find: /proc/18233/task/18233/fd/4: No such file or directory
/home/admin/user_backups/ttcmain.tar.gz

14. Post output from these 3 commands

Linux naf-server03.com 2.6.9-39.ELsmp #1 SMP Wed Jun 14 14:34:18 CDT 2006 i686 athlon i386 GNU/Linux

core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
pending signals (-i) 1024
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 32754
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited


processor : 0
vendor_id : AuthenticAMD
cpu family : 15
model : 37
model name : AMD Opteron(tm) Processor 246
stepping : 1
cpu MHz : 2009.412
cache size : 1024 KB
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 1
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 syscall nx mmxext lm 3dnowext 3dnow pni
bogomips : 4021.35

processor : 1
vendor_id : AuthenticAMD
cpu family : 15
model : 37
model name : AMD Opteron(tm) Processor 246
stepping : 1
cpu MHz : 2009.412
cache size : 1024 KB
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 1
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 syscall nx mmxext lm 3dnowext 3dnow pni
bogomips : 4017.97

eva2000
Thu 28th Jun '07, 11:53am
Try the following in this exact order. You can ignore any of the suggestions that you have already done.

1. Upgrade MySQL server to 4.0.27
2. Edit /etc/my.cnf and place the following mysql server settings in /etc/my.cnf and restart mysql server afterwards


[client]
socket=/var/lib/mysql/mysql.sock

[mysqld]
safe-show-database
skip-innodb
max_connections = 500
key_buffer = 360M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 4000
thread_cache_size = 384
wait_timeout = 35
connect_timeout = 10
tmp_table_size = 128M
max_heap_table_size = 128M
max_allowed_packet = 64M
max_connect_errors = 10
thread_concurrency = 4
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
query_cache_limit = 6M
query_cache_size = 96M
query_cache_type = 1
query_prealloc_size = 131072
query_alloc_block_size = 65536

[mysqld_safe]
nice = -5
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

[mysqlhotcopy]
interactive-timeout


3. Install APC Cache lastest version from http://pecl.php.net/package/APC. Read install guide at http://www.vbulletin.com/forum/showthread.php?t=165367 - please remove Zend Optimizer from php.ini before installing APC Cache. OR try the alternative PHP caching software, Xcache which seems to be a bit faster than APC Cache - http://www.vbulletin.com/forum/showthread.php?t=213267

4. If you just upgraded to vB 3.5.x/3.6.x try to disable these 4 options:

Admin CP -> vBulletin Options -> Forums Home Page Options -> Display Logged in Users?

Admin CP -> vBulletin Options -> Forum Display Options (forumdisplay) -> Show Users Browsing Forums

Admin CP -> vBulletin Options -> Thread Display Options -> Show Users Browsing Thread

Admin CP -> vBulletin Options -> Message Searching Options -> Automatic Similar Thread search


Regarding httpd.conf values - for other values you'd need to scroll further down in httpd.conf file for ones enclosed in prefork tags

NickFritz
Sat 30th Jun '07, 6:30am
Do I keep bumping key_buffer if i keeps getting full?

How much is too much?

eva2000
Sat 30th Jun '07, 10:06am
you can try bumping it up but keep an eye on swapping to disk etc.. if you swap to disk the buffer or add more memory to server :)