yet again another Server optimization request
Posted on March 11th, 2010 by rose
1: Deicated server Only running vbulletin
2: P4 3.2ghz Hyper threaded
2000gb ram
2 18gig scsi 10,000rpm drives
freebsd 4.9 stable
apache_1.3.29
php-4.3.6
Mysql v4.0.18
3.n/a
4. Used the port and compliled with lunix-threads
5.
last pid: 6411; load averages: 10.35, 17.70, 13.61 up 0+00:16:11 13:02:18
553 processes: 1 running, 552 sleeping
CPU states: 54.5% user, 2.3% nice, 20.5% system, 4.9% interrupt, 17.8% idle
Mem: 1545M Active, 134M Inact, 272M Wired, 56M Cache, 199M Buf, 3004K Free
Swap: 512M Total, 232M Used, 280M Free, 45% Inuse, 1468K In, 4320K Out
6.my.cnf
# The MySQL server
[mysqld]
port = 3306
bind-address = 127.0.0.1
skip-name-resolve
safe-show-database
socket = /tmp/mysql.sock
skip-locking
set-variable = key_buffer=256M
set-variable = max_allowed_packet=1M
set-variable = table_cache=256
set-variable = sort_buffer=8M
set-variable = record_buffer=8M
set-variable = myisam_sort_buffer_size=64M
set-variable = thread_cache=8
# Try number of CPU's*2 for thread_concurrency
set-variable = thread_concurrency=4
log-bin
server-id = 1
# Uncomment the following if you are using BDB tables
#set-variable = bdb_cache_size=64M
#set-variable = bdb_max_lock=100000
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/db/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/db/mysql/
#innodb_log_arch_dir = /var/db/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#set-variable = innodb_buffer_pool_size=256M
#set-variable = innodb_additional_mem_pool_size=20M
# Set .._log_file_size to 25 % of buffer pool size
#set-variable = innodb_log_file_size=64M
#set-variable = innodb_log_buffer_size=8M
#innodb_flush_log_at_trx_commit=1
#set-variable = innodb_lock_wait_timeout=50
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname
[mysqldump]
quick
set-variable = max_allowed_packet=16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=8M
set-variable = write_buffer=8M
[myisamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=8M
set-variable = write_buffer=8M
[mysqlhotcopy]
interactive-timeout
7.
eagle# mysqladmin extended-status
+--------------------------+-----------+
Variable_name Value
+--------------------------+-----------+
Aborted_clients 0
Aborted_connects 0
Bytes_received 10443905
Bytes_sent 579044749
Com_admin_commands 0
Com_alter_table 0
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_change_db 4828
Com_change_master 0
Com_check 0
Com_commit 0
Com_create_db 0
Com_create_function 0
Com_create_index 0
Com_create_table 0
Com_delete 65
Com_delete_multi 0
Com_drop_db 0
Com_drop_function 0
Com_drop_index 0
Com_drop_table 0
Com_flush 0
Com_grant 0
Com_ha_close 0
Com_ha_open 0
Com_ha_read 0
Com_insert 1249
Com_insert_select 2
Com_kill 0
Com_load 0
Com_load_master_data 0
Com_load_master_table 0
Com_lock_tables 0
Com_optimize 0
Com_purge 0
Com_rename_table 0
Com_repair 0
Com_replace 23
Com_replace_select 0
Com_reset 0
Com_restore_table 0
Com_revoke 0
Com_rollback 0
Com_savepoint 0
Com_select 31112
Com_set_option 0
Com_show_binlog_events 0
Com_show_binlogs 0
Com_show_create 0
Com_show_databases 0
Com_show_fields 0
Com_show_grants 0
Com_show_keys 0
Com_show_logs 0
Com_show_master_status 0
Com_show_new_master 0
Com_show_open_tables 0
Com_show_processlist 0
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 1
Com_show_innodb_status 0
Com_show_tables 4
Com_show_variables 4
Com_slave_start 0
Com_slave_stop 0
Com_truncate 0
Com_unlock_tables 0
Com_update 3458
Connections 4831
Created_tmp_disk_tables 8
Created_tmp_tables 610
Created_tmp_files 0
Delayed_insert_threads 0
Delayed_writes 0
Delayed_errors 0
Flush_commands 1
Handler_commit 0
Handler_delete 105
Handler_read_first 3876
Handler_read_key 1864861
Handler_read_next 1664668
Handler_read_prev 21063
Handler_read_rnd 230009
Handler_read_rnd_next 3643415
Handler_rollback 0
Handler_update 3182
Handler_write 167411
Key_blocks_used 5959
Key_read_requests 2529219
Key_reads 5944
Key_write_requests 1684
Key_writes 1636
Max_used_connections 100
Not_flushed_key_blocks 0
Not_flushed_delayed_rows 0
Open_tables 256
Open_files 279
Open_streams 0
Opened_tables 1627
Questions 45523
Qcache_queries_in_cache 0
Qcache_inserts 0
Qcache_hits 0
Qcache_lowmem_prunes 0
Qcache_not_cached 0
Qcache_free_memory 0
Qcache_free_blocks 0
Qcache_total_blocks 0
Rpl_status NULL
Select_full_join 7
Select_full_range_join 13
Select_range 7849
Select_range_check 0
Select_scan 7456
Slave_open_temp_tables 0
Slave_running OFF
Slow_launch_threads 134
Slow_queries 87
Sort_merge_passes 0
Sort_range 2041
Sort_rows 327615
Sort_scan 1172
Table_locks_immediate 48483
Table_locks_waited 2364
Threads_cached 4
Threads_created 862
Threads_connected 30
Threads_running 22
Uptime 535
+--------------------------+-----------+
8. vb only
9. its averaging around 500 now but thats becuase i cant get it to handle naymore
10. http://www.itshappening.com/phpinfo.php
11. apache
KeepAlive ON
MaxKeepAliveRequests 0
KeepAliveTimeout 10
MinSpareServers 128
MaxSpareServers 256
StartServers 128
MaxClients 1024
cough.. cough.. heh
Please tell me that you mean either 2048MB or 2GB...
You my.cnf and other settings need tweaking which will help a LITTLE, but for 500 vB users online on default 900 cookie timeout it still will be a hard ask for your current hardware.
1. httpd.conf maxclients is set way too high letting in too many users concurrently and killing the server and mysql in the process.. reduce maxclients to 256 and restart apache
2. change existing /etc/my.cnf and restart mysql (so as to disable mysql binary logging and lower that memory allocation as your buffers are allocated way too much memory)
[mysqld]
port = 3306
bind-address = 127.0.0.1
skip-name-resolve
safe-show-database
socket = /tmp/mysql.sock
skip-locking
max_connections = 800
key_buffer = 32M
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 = 1800
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
skip-innodb
tmp_table_size = 32M
read_rnd_buffer_size = 524288
[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
[mysqlhotcopy]
interactive-timeout
now these changes will help some, but for 500 vB users online and to anticipate 1000 vB users online, you would need to split to use 2 dedicated dual cpu servers with scsi disks..
something like
web/apache/php server
dual p4 2.66ghz or 2.8ghz xeon 533fsb L2 cache
2-3GB REG ECC PC2100 DDRAM
2x 18 or 36GB 10k rpm scsi disks (one disk dedicated to writing all apache and error logs)
mysql database server
dual p4 2.66ghz xeon 533fsb L2 cache
2-3GB REG ECC PC2100 DDRAM
3x 36GB 10k or 15k rpm scsi disks in raid 5
(motivation for recommending raid 5 redundacy comes from me recently loosing my 250GB Maxtor Maxline Plus2 PATA drive to the hardware heaven :( )
To give you an idea, my forums are temporarily sharing the same 2 server setup above except with P4 Dual 3.06ghz XEONs with Disboards.com av 1200 vB users online over 900s cookie timeout and my own forums 300-450 vB users over 30 min cookie timeout and load averages still hover around 1.5-2.0
Could you please enter your vBulletin.com forum registered email address on the form on this page to receive support for this issue: http://www.vbulletin.com/members/membersupport_priority.php
- you will need your vB license customer number and password to access this form.
OK i put my email in there. its 2gb ram by the way
Could you please enter your vBulletin.com forum registered email address on the form on this page to receive support for this issue: http://www.vbulletin.com/members/membersupport_priority.php
- you will need your vB license customer number and password to access this form.
#If you have any other info about this subject , Please add it free.# |