New Server Setup needs Optimization

[danrak]

Last month we worked on a server for one of my sites. I've got a dedicated MYSQL Server setup now with it. Connected through a 100mb crossover cable on seperate nics. Some of my users are stating it is still slowing down some though. The thread on the old server optimzation is http://www.vbulletin.com/forum/showthread.php?t=130504

Here is the info on the mysql server.

1. Dedicated

2. Duel Xeon 2.66 Hyperthreaded
4GB DDR Registered ECC
73GB 10,000 RPM SCSI 8 MB Cache
Linux - Fedora Core 2
Apache version 1.3.33 (Unix)
Kernel version 2.6.10-1.14_FC2smp
PHP version 4.3.10
MySQL version 4.0.23-standard
3. NA
4. NA
5.
Tasks: 139 total, 4 running, 135 sleeping, 0 stopped, 0 zombie
Cpu(s): 50.8% us, 18.3% sy, 0.0% ni, 30.4% id, 0.0% wa, 0.1% hi, 0.5% si
Mem: 2075088k total, 1809824k used, 265264k free, 211068k buffers
Swap: 2040244k total, 0k used, 2040244k free, 1222052k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1045 mysql 25 0 85884 40m 2288 R 35.2 2.0 0:01.06 mysqld
1046 mysql 21 0 85884 40m 2288 R 18.6 2.0 0:00.56 mysqld
1047 mysql 19 0 85884 40m 2288 R 14.9 2.0 0:00.45 mysqld
1026 root 16 0 3652 936 724 R 0.3 0.0 0:00.05 top
1 root 16 0 1552 460 392 S 0.0 0.0 0:02.38 init
2 root RT 0 0 0 0 S 0.0 0.0 0:00.53 migration/0
3 root 34 19 0 0 0 S 0.0 0.0 0:00.12 ksoftirqd/0
4 root RT 0 0 0 0 S 0.0 0.0 0:00.59 migration/1
5 root 34 19 0 0 0 S 0.0 0.0 0:00.06 ksoftirqd/1
6 root RT 0 0 0 0 S 0.0 0.0 0:06.10 migration/2
7 root 34 19 0 0 0 S 0.0 0.0 0:00.13 ksoftirqd/2
8 root RT 0 0 0 0 S 0.0 0.0 0:00.75 migration/3
9 root 34 19 0 0 0 S 0.0 0.0 0:00.08 ksoftirqd/3
10 root 5 -10 0 0 0 S 0.0 0.0 1:48.24 events/0
11 root 5 -10 0 0 0 S 0.0 0.0 0:15.49 events/1
12 root 5 -10 0 0 0 S 0.0 0.0 0:12.69 events/2
13 root 5 -10 0 0 0 S 0.0 0.0 0:10.48 events/3
7.
[mysqld]
set-variable = max_connections=500
safe-show-database
7.
http://67.43.11.238/mysql.php
8. Yes
9. 900-1000
10. http://67.43.11.238/phpinfo.php
11.
KeepAlive ON
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 10
StartServers 5
MaxClients 150
12. vb 3.0.5 hacked to 3.0.7

13. None Found

[eva2000]

congrats on new server but that cpu load in mysql.php link is high seems to be web end probs ?

for db server

try this new /etc/my.cnf below and restart mysql server


[mysqld]
safe-show-database
skip-innodb
max_connections = 800
key_buffer = 96M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 64
wait_timeout = 7200
connect_timeout = 10
tmp_table_size = 64M
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 4M
query_cache_size = 128M
query_cache_type = 1

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

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


for web server ensure install eaccelerator from http://eaccelerator.net/InstallEacceleratorUk read http://www.vbulletin.com/forum/showpost.php?p=805040&postcount=441

[danrak]

Ok. I made the changes. The server load on the db server is still high. Today we did reach our new highest limit, just under 1,300 users online. The server load was up to 120 today. The file server load is low, I haven't seen it hit 1.00 yet.


Forumdisplay is getting 21 queries (1 queries for uncached templates)
index is getting 15 queries (1 queries for uncached templates)


vbsettings are as follows:

Time-Out for Cookie 900
GZIP Compression Level 1
Remove Redirection Message Pages yes
Add No-Cache HTTP Headers no
Show Last Post on Profile Page no
View Attached Images Inline no
Automatic Similar Thread Search no
Display Calendar Events? 0
Show Users Browsing Forums no
Who's Online Resolve IP Addresses no
Disable No-Cache HTTP Headers & Redirection
Cached Posts Lifespan 30
Update Thread Views Immediately no
Update Attachment Views Immediately no
Duplicate Search Index Information on Thread Copy? No
Store CSS Stylesheets as Files Yes

Not sure if there are other settings you may need.

I've also moddified the index page with this hack http://www.vbulletin.com/forum/showthread.php?t=117611 to cut down on the tables.


Is there anything else I can do to cut down on the db server load? There are no web files there. Am I going to be stuck having to do some type of server cluster?

[eva2000]

so that's 900+ users over 900 second cookie time out right ?

your http://67.43.11.238/mysql.php doesn't show any info i need so can you copy and paste output from these 2 commands in ssh telnet

mysqladmin -u root -p variables

mysqladmin -u root -p extended-status