منتدى استراحات زايد - Server Optimization request

Hi,

Please help me to optimize this new database server.

1. This server is dedicated mysql server
2.
cpu speed/type single or dual cpus): Dual Quad Core Xeon Nehalem 5520 ( 16 cores )
how much memory installed: 12GB RAM
hard drive type/configuration: 4x73GB SCSI 15,000 rpm RAID 10
linux distributor or windows version: CentOS 5.3
apache/IIS version: not installed
PHP version: not installed
MySQL version: 5.0.79-log

3. what version of vB are you running 3.8.2?

4. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server ?
5. if possible how mysql was compiled/installed: yum update mysql*
6. your top stats. So log into your server via ssh telnet as root user and type the command in bold - top

Quote:
top - 19:01:30 up 1 day, 19:20, 1 user, load average: 0.66, 0.40, 0.23
Tasks: 326 total, 1 running, 325 sleeping, 0 stopped, 0 zombie
Cpu0 : 0.1%us, 0.1%sy, 0.0%ni, 99.8%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu1 : 0.1%us, 0.1%sy, 0.0%ni, 99.8%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu2 : 0.3%us, 0.0%sy, 0.0%ni, 99.0%id, 0.6%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu3 : 0.1%us, 0.0%sy, 0.0%ni, 99.8%id, 0.1%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu4 : 0.2%us, 0.0%sy, 0.0%ni, 99.5%id, 0.3%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu5 : 0.1%us, 0.0%sy, 0.0%ni, 99.9%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu6 : 0.1%us, 0.0%sy, 0.0%ni, 99.8%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu7 : 0.1%us, 0.0%sy, 0.0%ni, 99.9%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu8 : 0.1%us, 0.0%sy, 0.0%ni, 99.9%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu9 : 0.1%us, 0.1%sy, 0.0%ni, 99.9%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu10 : 0.3%us, 0.3%sy, 0.0%ni, 99.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu11 : 0.2%us, 0.2%sy, 0.0%ni, 99.6%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu12 : 0.1%us, 0.1%sy, 0.0%ni, 99.8%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu13 : 0.1%us, 0.1%sy, 0.0%ni, 99.8%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu14 : 0.1%us, 0.0%sy, 0.0%ni, 99.9%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu15 : 0.1%us, 0.0%sy, 0.0%ni, 99.9%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 12290980k total, 4210828k used, 8080152k free, 308420k buffers
Swap: 1052248k total, 0k used, 1052248k free, 3104464k cached

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).
PHP Code:

cat /etc/my.cnf
[mysqld]
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
max_connections = 180
key_buffer = 256M
myisam_sort_buffer_size = 64M
join_buffer_size = 3M
read_buffer_size = 3M
sort_buffer_size = 9M
table_cache = 8000
thread_cache_size = 512
wait_timeout = 25
connect_timeout = 10
tmp_table_size = 128M
max_heap_table_size = 128M
max_allowed_packet = 128M
net_buffer_length = 16384
max_connect_errors = 100000
thread_concurrency = 16
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 16M
query_cache_limit = 8M
query_cache_size = 128M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = MyISAM
# bind-address=127.0.0.1
long_query_time = 5
log-slow-queries = /var/log/mysql-slow.log
[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
nice = -10
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

8. MySQL stats from ssh telnet as root user type:
This is server is under testing so there is no much load yet. we will put in production after optimization.
http://toshibalcd.com/mysqlstat.txt

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

10. how many average and max concurrent users on your vB forum ? and what your cookie timeout is ? Average 2000, Max 5500, Expect to handle 7-8000 at peak with this new server.

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

You can use this command to find such files
Quote:
find / -size +2000000k
14. Post output from these 3 commands

uname -a
Linux server2.myserver.com 2.6.18-128.1.6.el5 #1 SMP Wed Apr 1 09:10:25 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux

ulimit -aH

core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 106496
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
real-time priority (-r) 0
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 106496
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited

cat /proc/cpuinfo
cat /proc/cpuinfo | grep Xeon
model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHz
model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHz
model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHz
model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHz
model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHz
model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHz
model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHz
model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHz
model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHz
model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHz
model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHz
model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHz
model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHz
model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHz
model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHz
model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHz