PDA

View Full Version : [Request] Server Optimization


f0x
Mon 25th Jun '07, 6:21am
1. Dedicated Server
2. Dual XEON 2.8GHz / 4096MB RAM / 2 x 80GB SATA disk. Apache v1.3.37, PHP v5.2.1, MySQL v5.0.37. Fedora Core 5.
3. 3.6.7
4. NO innodb
5. Linux (non RPM, Intel C/C++ compiled, glibc-2.3)
6. top

top - 15:55:39 up 4 days, 1:44, 1 user, load average: 1.49, 1.95, 1.87
Tasks: 217 total, 2 running, 214 sleeping, 0 stopped, 1 zombie
Cpu(s): 25.6% us, 2.4% sy, 0.0% ni, 71.6% id, 0.0% wa, 0.1% hi, 0.3% si
Mem: 4136972k total, 2459964k used, 1677008k free, 196296k buffers
Swap: 4096440k total, 0k used, 4096440k free, 1642684k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
22499 daemon 15 0 14896 8488 3080 S 11 0.2 0:24.67 httpd
22851 daemon 16 0 14860 7980 2600 S 10 0.2 0:14.74 httpd
22101 daemon 16 0 17232 10m 3080 S 9 0.3 0:27.49 httpd
2527 mysql 5 -10 563m 196m 4508 S 9 4.9 550:41.26 mysqld
21550 daemon 16 0 14912 8484 3076 S 8 0.2 0:27.79 httpd
23513 daemon 15 0 14860 8424 3064 S 8 0.2 0:07.58 httpd
22947 daemon 15 0 14888 8444 3064 S 7 0.2 0:13.58 httpd
22721 daemon 15 0 14884 8464 3076 S 7 0.2 0:14.09 httpd
22482 daemon 16 0 19800 11m 2596 S 6 0.3 0:19.08 httpd
23769 daemon 16 0 14880 8408 3036 S 6 0.2 0:06.70 httpd
21810 daemon 15 0 14884 8460 3064 S 4 0.2 0:18.20 httpd
21869 daemon 15 0 14928 8504 3076 S 4 0.2 0:23.54 httpd
23199 daemon 15 0 14896 8448 3044 S 4 0.2 0:10.40 httpd
22251 daemon 16 0 17260 10m 3076 S 3 0.3 0:25.54 httpd
21273 daemon 15 0 17200 9m 2624 R 3 0.2 0:30.79 httpd
22079 daemon 17 0 14868 7984 2588 S 3 0.2 0:19.41 httpd
22948 daemon 15 0 14872 8456 3076 S 2 0.2 0:14.26 httpd
7. /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-name-resolve
old_passwords
back_log = 50
skip-innodb
max_connections = 500
key_buffer = 384M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 3M
table_cache = 1800
thread_cache_size = 384
wait_timeout = 35
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 256M
max_allowed_packet = 256M
max_connect_errors = 10
thread_concurrency = 4
concurrent_insert = 2
table_lock_wait_timeout = 35
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 8M
query_cache_limit = 4M
query_cache_size = 64M
query_cache_type = 1
query_prealloc_size = 131072
query_alloc_block_size = 65536
default-storage-engine = MyISAM

[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 = 16M8. MySQL Stats
Aborted_clients 712571
Aborted_connects 0
Binlog_cache_disk_use 0
Binlog_cache_use 0
Bytes_received 135721
Bytes_sent 1298205
Com_admin_commands 4
Com_alter_db 0
Com_alter_table 0
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_change_db 5
Com_change_master 0
Com_check 0
Com_checksum 0
Com_commit 0
Com_create_db 0
Com_create_function 0
Com_create_index 0
Com_create_table 0
Com_create_user 0
Com_dealloc_sql 0
Com_delete 1
Com_delete_multi 0
Com_do 0
Com_drop_db 0
Com_drop_function 0
Com_drop_index 0
Com_drop_table 0
Com_drop_user 0
Com_execute_sql 0
Com_flush 0
Com_grant 0
Com_ha_close 0
Com_ha_open 0
Com_ha_read 0
Com_help 0
Com_insert 4
Com_insert_select 0
Com_kill 0
Com_load 0
Com_load_master_data 0
Com_load_master_table 0
Com_lock_tables 0
Com_optimize 0
Com_preload_keys 0
Com_prepare_sql 0
Com_purge 0
Com_purge_before_date 0
Com_rename_table 0
Com_repair 0
Com_replace 8
Com_replace_select 0
Com_reset 0
Com_restore_table 0
Com_revoke 0
Com_revoke_all 0
Com_rollback 0
Com_savepoint 0
Com_select 27
Com_set_option 0
Com_show_binlog_events 0
Com_show_binlogs 0
Com_show_charsets 0
Com_show_collations 0
Com_show_column_types 0
Com_show_create_db 0
Com_show_create_table 0
Com_show_databases 0
Com_show_errors 0
Com_show_fields 0
Com_show_grants 0
Com_show_innodb_status 0
Com_show_keys 0
Com_show_logs 0
Com_show_master_status 0
Com_show_ndb_status 0
Com_show_new_master 0
Com_show_open_tables 0
Com_show_privileges 0
Com_show_processlist 0
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 1
Com_show_storage_engines 0
Com_show_tables 0
Com_show_triggers 0
Com_show_variables 0
Com_show_warnings 0
Com_slave_start 0
Com_slave_stop 0
Com_stmt_close 0
Com_stmt_execute 0
Com_stmt_fetch 0
Com_stmt_prepare 0
Com_stmt_reset 0
Com_stmt_send_long_data 0
Com_truncate 0
Com_unlock_tables 0
Com_update 20
Com_update_multi 0
Com_xa_commit 0
Com_xa_end 0
Com_xa_prepare 0
Com_xa_recover 0
Com_xa_rollback 0
Com_xa_start 0
Compression OFF
Connections 441823
Created_tmp_disk_tables 0
Created_tmp_files 0
Created_tmp_tables 2
Delayed_errors 0
Delayed_insert_threads 0
Delayed_writes 0
Flush_commands 1
Handler_commit 0
Handler_delete 0
Handler_discover 0
Handler_prepare 0
Handler_read_first 3
Handler_read_key 1703
Handler_read_next 39
Handler_read_prev 19
Handler_read_rnd 1571
Handler_read_rnd_next 188718
Handler_rollback 0
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 28
Handler_write 1696
Innodb_buffer_pool_pages_data 0
Innodb_buffer_pool_pages_dirty 0
Innodb_buffer_pool_pages_flushed 0
Innodb_buffer_pool_pages_free 0
Innodb_buffer_pool_pages_latched 0
Innodb_buffer_pool_pages_misc 0
Innodb_buffer_pool_pages_total 0
Innodb_buffer_pool_read_ahead_rnd 0
Innodb_buffer_pool_read_ahead_seq 0
Innodb_buffer_pool_read_requests 0
Innodb_buffer_pool_reads 0
Innodb_buffer_pool_wait_free 0
Innodb_buffer_pool_write_requests 0
Innodb_data_fsyncs 0
Innodb_data_pending_fsyncs 0
Innodb_data_pending_reads 0
Innodb_data_pending_writes 0
Innodb_data_read 0
Innodb_data_reads 0
Innodb_data_writes 0
Innodb_data_written 0
Innodb_dblwr_pages_written 0
Innodb_dblwr_writes 0
Innodb_log_waits 0
Innodb_log_write_requests 0
Innodb_log_writes 0
Innodb_os_log_fsyncs 0
Innodb_os_log_pending_fsyncs 0
Innodb_os_log_pending_writes 0
Innodb_os_log_written 0
Innodb_page_size 0
Innodb_pages_created 0
Innodb_pages_read 0
Innodb_pages_written 0
Innodb_row_lock_current_waits 0
Innodb_row_lock_time 0
Innodb_row_lock_time_avg 0
Innodb_row_lock_time_max 0
Innodb_row_lock_waits 0
Innodb_rows_deleted 0
Innodb_rows_inserted 0
Innodb_rows_read 0
Innodb_rows_updated 0
Key_blocks_not_flushed 0
Key_blocks_unused 326568
Key_blocks_used 22393
Key_read_requests 1442971596
Key_reads 34482
Key_write_requests 1615358
Key_writes 1492614
Last_query_cost 0.000000
Max_used_connections 185
Ndb_cluster_node_id 0
Ndb_config_from_host
Ndb_config_from_port 0
Ndb_number_of_data_nodes 0
Not_flushed_delayed_rows 0
Open_files 665
Open_streams 0
Open_tables 582
Opened_tables 0
Prepared_stmt_count 0
Qcache_free_blocks 8506
Qcache_free_memory 32640496
Qcache_hits 7913222
Qcache_inserts 4503990
Qcache_lowmem_prunes 13613
Qcache_not_cached 26136
Qcache_queries_in_cache 22966
Qcache_total_blocks 54762
Questions 16411462
Rpl_status NULL
Select_full_join 0
Select_full_range_join 0
Select_range 4
Select_range_check 0
Select_scan 6
Slave_open_temp_tables 0
Slave_retried_transactions 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 0
Sort_merge_passes 0
Sort_range 3
Sort_rows 1585
Sort_scan 1
Table_locks_immediate 13993899
Table_locks_waited 35457
Tc_log_max_pages_used 0
Tc_log_page_size 0
Tc_log_page_waits 0
Threads_cached 62
Threads_connected 123
Threads_created 185
Threads_running 1
Uptime 351791
Uptime_since_flush_status 3517919. vBulletin only.
10. Cookie Timeout : 900 sec. Normal times: 1000-1500 visitors Max Times:1800-2500 visitors.
11. PHPInfo: http://forum.gate.vn/phpinfo/phpinfo.php
12. Apache httpd.conf

KeepAlive On
MaxKeepAliveRequests 500
KeepAliveTimeout 4
MinSpareServers 10
MaxSpareServers 15
StartServers 10
MaxClients 200
Maxrequestsperchild 800
13. No such file or directory.
14.
uname -a
Linux localhost.localdomain 2.6.15-1.2054_FC5smp #1 SMP Tue Mar 14 16:05:46 EST 2006 i686 i686 i386 GNU/Linux
ulimit -aH

core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
max nice (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 81920
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
max rt priority (-r) 0
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 81920
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
file locks (-x) unlimited
cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 3
cpu MHz : 2793.498
cache size : 2048 KB
physical id : 0
siblings : 2
core id : 0
cpu cores : 1
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe lm constant_tsc pni monito r ds_cpl cid cx16 xtpr
bogomips : 5596.03

processor : 1
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 3
cpu MHz : 2793.498
cache size : 2048 KB
physical id : 3
siblings : 2
core id : 0
cpu cores : 1
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe lm constant_tsc pni monito r ds_cpl cid cx16 xtpr
bogomips : 5586.44

processor : 2
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 3
cpu MHz : 2793.498
cache size : 2048 KB
physical id : 0
siblings : 2
core id : 0
cpu cores : 1
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe lm constant_tsc pni monito r ds_cpl cid cx16 xtpr
bogomips : 5586.44

processor : 3
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 3
cpu MHz : 2793.498
cache size : 2048 KB
physical id : 3
siblings : 2
core id : 0
cpu cores : 1
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe lm constant_tsc pni monito r ds_cpl cid cx16 xtpr
bogomips : 5586.46


Thanks for your help.

eva2000
Mon 25th Jun '07, 8:20am
Try the following in this exact order. You can ignore any of the suggestions that you have already done.

1. Upgrade MySQL server to latest MySQL 5.x server = 5.0.41
2. Upgrade PHP to 5.2.3
3. Edit /etc/my.cnf and place the following mysql server settings in /etc/my.cnf and restart mysql server afterwards


[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords
back_log = 50
skip-innodb
max_connections = 500
key_buffer = 64M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 3M
table_cache = 1800
thread_cache_size = 384
wait_timeout = 30
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 10
thread_concurrency = 4
concurrent_insert = 2
table_lock_wait_timeout = 35
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
default-storage-engine = MyISAM

[mysql.server]
user=mysql
basedir=/var/lib

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


4. 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 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

5. 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?

6. Edit httpd.conf values to following and restart apache

KeepAlive: On
MaxKeepAliveRequests: 100
KeepAliveTimeout: 1
MinSpareServers: 10
MaxSpareServers: 15
StartServers: 10
MaxClients: 180
MaxRequestsPerChild: 1000