baraja
Tue 26th Dec '06, 3:45pm
Hello,
I recently upgraded from this server:
Old Server Optimization Request http://www.vbulletin.com/forum/showthread.php?t=181138
So after the Christmas vacation, I'm trying to setup the new server. The idea would it be that the new server worked right with about 200-250 users connected at the same time and peaks of 500 users, but I'm not sure if it will be enough.
I just reinstalled APC and it seems it is working quite right. Anywise, here it is the info about the new server, politely asking again for optimization :)
Thanks for all your help, eva2000 :)
1. Dedicated server
2. Server specs
Dual Xeon 2.8 GHz / 2 MB cache
1024 RAM
200 GB
CentOS 4.4 i686
Apache 1.3.37
PHP 4.4.4
MySQL 4.1.21-standard
4. WHM
5. top stats
top - 10:53:50 up 3 days, 17:05, 1 user, load average: 1.61, 2.12, 2.74
Tasks: 173 total, 1 running, 172 sleeping, 0 stopped, 0 zombie
Cpu(s): 19.6% us, 0.7% sy, 0.0% ni, 76.7% id, 3.0% wa, 0.0% hi, 0.0% si
Mem: 1034416k total, 660384k used, 374032k free, 4992k buffers
Swap: 2096472k total, 285264k used, 1811208k free, 75184k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
12396 nobody 15 0 54732 11m 6124 S 15 1.2 0:08.35 httpd
10557 nobody 16 0 54732 13m 7916 S 9 1.4 0:23.30 httpd
10556 nobody 15 0 54724 12m 6964 S 8 1.3 0:19.88 httpd
10549 nobody 15 0 55384 12m 7204 S 7 1.3 0:12.32 httpd
12147 nobody 15 0 55652 11m 5676 S 7 1.2 0:03.07 httpd
10533 nobody 15 0 56956 17m 9.9m S 7 1.8 0:21.62 httpd
10532 nobody 16 0 55864 14m 8340 S 5 1.4 0:19.33 httpd
10521 nobody 16 0 55784 14m 8444 S 4 1.4 0:19.21 httpd
10544 nobody 16 0 55448 13m 8324 S 4 1.4 0:17.98 httpd
10558 nobody 16 0 54728 13m 7784 S 4 1.3 0:25.80 httpd
10546 nobody 16 0 55728 14m 8852 S 4 1.4 0:19.58 httpd
12467 nobody 15 0 55644 11m 6120 S 4 1.2 0:08.68 httpd
4056 mysql 15 0 174m 101m 2024 S 2 10.1 70:14.84 mysqld
10537 nobody 15 0 56684 17m 9844 S 0 1.7 0:22.32 httpd
1 root 16 0 2440 372 344 S 0 0.0 0:09.34 init
2 root RT 0 0 0 0 S 0 0.0 0:01.42 migration/0
3 root 34 19 0 0 0 S 0 0.0 0:00.01 ksoftirqd/0
6. your mysql configuration variables
[mysqld]
safe-show-database
skip-innodb
max_connections = 500
key_buffer = 32M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size = 384
wait_timeout = 180
connect_timeout = 10
tmp_table_size = 32M
max_heap_table_size = 32M
max_allowed_packet = 64M
max_connect_errors = 10
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
query_cache_limit = 2M
query_cache_size = 64M
query_cache_type = 1
query_prealloc_size = 163840
query_alloc_block_size = 32768
[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
7. MySQL stats from ssh telnet as root user type:
+---------------------------------+------------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------------------+
| back_log | 50 |
| basedir | / |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| concurrent_insert | ON |
| connect_timeout | 10 |
| datadir | /var/lib/mysql/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | NO |
| have_bdb | NO |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | YES |
| have_csv | NO |
| have_example_engine | NO |
| have_geometry | YES |
| have_innodb | DISABLED |
| have_isam | NO |
| have_ndbcluster | NO |
| have_merge_engine | YES |
| have_openssl | NO |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_data_file_path | |
| innodb_data_home_dir | |
| innodb_fast_shutdown | ON |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| interactive_timeout | 28800 |
| join_buffer_size | 1044480 |
| key_buffer_size | 33554432 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| lc_time_names | en_US |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_error | |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_update | OFF |
| log_warnings | 1 |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 67107840 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 500 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 33553408 |
| max_insert_delayed_threads | 20 |
| max_join_size | 4294967295 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| myisam_data_pointer_size | 4 |
| myisam_max_extra_sort_file_size | 2147483648 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 67108864 |
| myisam_stats_method | nulls_unequal |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | OFF |
| open_files_limit | 4110 |
| pid_file | /var/lib/mysql/newserver.xtasis.org.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| prepared_stmt_count | 0 |
| protocol_version | 10 |
| query_alloc_block_size | 32768 |
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 67108864 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 163840 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 1044480 |
| read_only | OFF |
| read_rnd_buffer_size | 520192 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_net_timeout | 3600 |
| slave_transaction_retries | 0 |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer_size | 2097144 |
| sql_mode | |
| sql_notes | ON |
| sql_warnings | ON |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| sync_replication | 0 |
| sync_replication_slave_id | 0 |
| sync_replication_timeout | 0 |
| system_time_zone | PST |
| table_cache | 1800 |
| table_type | MyISAM |
| thread_cache_size | 384 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| tmp_table_size | 33554432 |
| tmpdir | |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| version | 4.1.21-standard |
| version_comment | MySQL Community Edition - Standard (GPL) |
| version_compile_machine | i686 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 180 |
+---------------------------------+------------------------------------------+
+----------------------------+------------+
| Variable_name | Value |
+----------------------------+------------+
| Aborted_clients | 498 |
| Aborted_connects | 11 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 2629279153 |
| Bytes_sent | 708108378 |
| Com_admin_commands | 1 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 964672 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 1 |
| Com_create_function | 0 |
| Com_create_index | 1 |
| Com_create_table | 2 |
| Com_dealloc_sql | 0 |
| Com_delete | 20890 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 1 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 12 |
| Com_grant | 2 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 812894 |
| Com_insert_select | 1058 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 60607 |
| Com_optimize | 1 |
| 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 | 9481 |
| 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 | 2339307 |
| Com_set_option | 1668 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 2 |
| Com_show_charsets | 11 |
| Com_show_collations | 11 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 384 |
| Com_show_databases | 9 |
| Com_show_errors | 0 |
| Com_show_fields | 388 |
| Com_show_grants | 17 |
| 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 | 1076 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 428 |
| Com_show_variables | 70 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 60611 |
| Com_update | 412311 |
| Com_update_multi | 15 |
| Connections | 967639 |
| Created_tmp_disk_tables | 30 |
| Created_tmp_files | 8 |
| Created_tmp_tables | 23840 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 40182 |
| Handler_discover | 0 |
| Handler_read_first | 12480 |
| Handler_read_key | 23206614 |
| Handler_read_next | 16473610 |
| Handler_read_prev | 2607737 |
| Handler_read_rnd | 1082063 |
| Handler_read_rnd_next | 97890944 |
| Handler_rollback | 0 |
| Handler_update | 1608720 |
| Handler_write | 966764 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 26694 |
| Key_blocks_used | 28995 |
| Key_read_requests | 73672889 |
| Key_reads | 146402 |
| Key_write_requests | 1173863 |
| Key_writes | 1091399 |
| Max_used_connections | 156 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 373 |
| Open_streams | 0 |
| Open_tables | 274 |
| Opened_tables | 1487 |
| Qcache_free_blocks | 463 |
| Qcache_free_memory | 63779224 |
| Qcache_hits | 4468147 |
| Qcache_inserts | 2328403 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 10658 |
| Qcache_queries_in_cache | 323 |
| Qcache_total_blocks | 1222 |
| Questions | 10121488 |
| Rpl_status | NULL |
| Select_full_join | 5 |
| Select_full_range_join | 0 |
| Select_range | 340103 |
| Select_range_check | 0 |
| Select_scan | 117462 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 4 |
| Slow_queries | 5382 |
| Sort_merge_passes | 4 |
| Sort_range | 350819 |
| Sort_rows | 6045581 |
| Sort_scan | 24172 |
| Table_locks_immediate | 7459780 |
| Table_locks_waited | 41998 |
| Threads_cached | 155 |
| Threads_connected | 1 |
| Threads_created | 156 |
| Threads_running | 1 |
| Uptime | 321655 |
+----------------------------+------------+
Uptime: 321656 Threads: 1 Questions: 10121499 Slow queries: 5382 Opens: 1487 Flush tables: 1 Open tables: 274 Queries per second avg: 31.467
mysqladmin Ver 8.41 Distrib 4.1.21, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 4.1.21-standard
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 3 days 17 hours 20 min 56 sec
Threads: 1 Questions: 10121499 Slow queries: 5382 Opens: 1487 Flush tables: 1 Open tables: 274 Queries per second avg: 31.467
8. is your vB the only thing on the server? Yes, only the vBulletin installation.
9. how many average and max concurrent users on your vB forum ?
Average: 100
Max concurrent users: 325
and what your cookie timeout is ? 600 seconds
10. http://www.xtasis.org/phpinfo.php
11. httpd.conf
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 10
StartServers 5
MaxClients 150
MaxRequestsPerChild 0
12. 3.5.3 with security patches
13. No files +2GB
14.
uname -a
Linux newserver.xtasis.org 2.6.9-34.ELsmp #1 SMP Wed Mar 8 00:27:03 CST 2006 i686 i686 i386 GNU/Linux
ulimit -aH
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) 16380
virtual memory (kbytes, -v) 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 : 2801.828
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 pni monitor ds_cpl cid xtpr
bogomips : 5605.98
processor : 1
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 3
cpu MHz : 2801.828
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 pni monitor ds_cpl cid xtpr
bogomips : 5599.46
processor : 2
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 3
cpu MHz : 2801.828
cache size : 2048 KB
physical id : 3
siblings : 2
core id : 3
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 pni monitor ds_cpl cid xtpr
bogomips : 5599.44
processor : 3
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 3
cpu MHz : 2801.828
cache size : 2048 KB
physical id : 3
siblings : 2
core id : 3
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 pni monitor ds_cpl cid xtpr
bogomips : 5599.50
I recently upgraded from this server:
Old Server Optimization Request http://www.vbulletin.com/forum/showthread.php?t=181138
So after the Christmas vacation, I'm trying to setup the new server. The idea would it be that the new server worked right with about 200-250 users connected at the same time and peaks of 500 users, but I'm not sure if it will be enough.
I just reinstalled APC and it seems it is working quite right. Anywise, here it is the info about the new server, politely asking again for optimization :)
Thanks for all your help, eva2000 :)
1. Dedicated server
2. Server specs
Dual Xeon 2.8 GHz / 2 MB cache
1024 RAM
200 GB
CentOS 4.4 i686
Apache 1.3.37
PHP 4.4.4
MySQL 4.1.21-standard
4. WHM
5. top stats
top - 10:53:50 up 3 days, 17:05, 1 user, load average: 1.61, 2.12, 2.74
Tasks: 173 total, 1 running, 172 sleeping, 0 stopped, 0 zombie
Cpu(s): 19.6% us, 0.7% sy, 0.0% ni, 76.7% id, 3.0% wa, 0.0% hi, 0.0% si
Mem: 1034416k total, 660384k used, 374032k free, 4992k buffers
Swap: 2096472k total, 285264k used, 1811208k free, 75184k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
12396 nobody 15 0 54732 11m 6124 S 15 1.2 0:08.35 httpd
10557 nobody 16 0 54732 13m 7916 S 9 1.4 0:23.30 httpd
10556 nobody 15 0 54724 12m 6964 S 8 1.3 0:19.88 httpd
10549 nobody 15 0 55384 12m 7204 S 7 1.3 0:12.32 httpd
12147 nobody 15 0 55652 11m 5676 S 7 1.2 0:03.07 httpd
10533 nobody 15 0 56956 17m 9.9m S 7 1.8 0:21.62 httpd
10532 nobody 16 0 55864 14m 8340 S 5 1.4 0:19.33 httpd
10521 nobody 16 0 55784 14m 8444 S 4 1.4 0:19.21 httpd
10544 nobody 16 0 55448 13m 8324 S 4 1.4 0:17.98 httpd
10558 nobody 16 0 54728 13m 7784 S 4 1.3 0:25.80 httpd
10546 nobody 16 0 55728 14m 8852 S 4 1.4 0:19.58 httpd
12467 nobody 15 0 55644 11m 6120 S 4 1.2 0:08.68 httpd
4056 mysql 15 0 174m 101m 2024 S 2 10.1 70:14.84 mysqld
10537 nobody 15 0 56684 17m 9844 S 0 1.7 0:22.32 httpd
1 root 16 0 2440 372 344 S 0 0.0 0:09.34 init
2 root RT 0 0 0 0 S 0 0.0 0:01.42 migration/0
3 root 34 19 0 0 0 S 0 0.0 0:00.01 ksoftirqd/0
6. your mysql configuration variables
[mysqld]
safe-show-database
skip-innodb
max_connections = 500
key_buffer = 32M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size = 384
wait_timeout = 180
connect_timeout = 10
tmp_table_size = 32M
max_heap_table_size = 32M
max_allowed_packet = 64M
max_connect_errors = 10
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
query_cache_limit = 2M
query_cache_size = 64M
query_cache_type = 1
query_prealloc_size = 163840
query_alloc_block_size = 32768
[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
7. MySQL stats from ssh telnet as root user type:
+---------------------------------+------------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------------------+
| back_log | 50 |
| basedir | / |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| concurrent_insert | ON |
| connect_timeout | 10 |
| datadir | /var/lib/mysql/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | NO |
| have_bdb | NO |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | YES |
| have_csv | NO |
| have_example_engine | NO |
| have_geometry | YES |
| have_innodb | DISABLED |
| have_isam | NO |
| have_ndbcluster | NO |
| have_merge_engine | YES |
| have_openssl | NO |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_data_file_path | |
| innodb_data_home_dir | |
| innodb_fast_shutdown | ON |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| interactive_timeout | 28800 |
| join_buffer_size | 1044480 |
| key_buffer_size | 33554432 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| lc_time_names | en_US |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_error | |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_update | OFF |
| log_warnings | 1 |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 67107840 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 500 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 33553408 |
| max_insert_delayed_threads | 20 |
| max_join_size | 4294967295 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| myisam_data_pointer_size | 4 |
| myisam_max_extra_sort_file_size | 2147483648 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 67108864 |
| myisam_stats_method | nulls_unequal |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | OFF |
| open_files_limit | 4110 |
| pid_file | /var/lib/mysql/newserver.xtasis.org.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| prepared_stmt_count | 0 |
| protocol_version | 10 |
| query_alloc_block_size | 32768 |
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 67108864 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 163840 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 1044480 |
| read_only | OFF |
| read_rnd_buffer_size | 520192 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_net_timeout | 3600 |
| slave_transaction_retries | 0 |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer_size | 2097144 |
| sql_mode | |
| sql_notes | ON |
| sql_warnings | ON |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| sync_replication | 0 |
| sync_replication_slave_id | 0 |
| sync_replication_timeout | 0 |
| system_time_zone | PST |
| table_cache | 1800 |
| table_type | MyISAM |
| thread_cache_size | 384 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| tmp_table_size | 33554432 |
| tmpdir | |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| version | 4.1.21-standard |
| version_comment | MySQL Community Edition - Standard (GPL) |
| version_compile_machine | i686 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 180 |
+---------------------------------+------------------------------------------+
+----------------------------+------------+
| Variable_name | Value |
+----------------------------+------------+
| Aborted_clients | 498 |
| Aborted_connects | 11 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 2629279153 |
| Bytes_sent | 708108378 |
| Com_admin_commands | 1 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 964672 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 1 |
| Com_create_function | 0 |
| Com_create_index | 1 |
| Com_create_table | 2 |
| Com_dealloc_sql | 0 |
| Com_delete | 20890 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 1 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 12 |
| Com_grant | 2 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 812894 |
| Com_insert_select | 1058 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 60607 |
| Com_optimize | 1 |
| 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 | 9481 |
| 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 | 2339307 |
| Com_set_option | 1668 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 2 |
| Com_show_charsets | 11 |
| Com_show_collations | 11 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 384 |
| Com_show_databases | 9 |
| Com_show_errors | 0 |
| Com_show_fields | 388 |
| Com_show_grants | 17 |
| 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 | 1076 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 428 |
| Com_show_variables | 70 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 60611 |
| Com_update | 412311 |
| Com_update_multi | 15 |
| Connections | 967639 |
| Created_tmp_disk_tables | 30 |
| Created_tmp_files | 8 |
| Created_tmp_tables | 23840 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 40182 |
| Handler_discover | 0 |
| Handler_read_first | 12480 |
| Handler_read_key | 23206614 |
| Handler_read_next | 16473610 |
| Handler_read_prev | 2607737 |
| Handler_read_rnd | 1082063 |
| Handler_read_rnd_next | 97890944 |
| Handler_rollback | 0 |
| Handler_update | 1608720 |
| Handler_write | 966764 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 26694 |
| Key_blocks_used | 28995 |
| Key_read_requests | 73672889 |
| Key_reads | 146402 |
| Key_write_requests | 1173863 |
| Key_writes | 1091399 |
| Max_used_connections | 156 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 373 |
| Open_streams | 0 |
| Open_tables | 274 |
| Opened_tables | 1487 |
| Qcache_free_blocks | 463 |
| Qcache_free_memory | 63779224 |
| Qcache_hits | 4468147 |
| Qcache_inserts | 2328403 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 10658 |
| Qcache_queries_in_cache | 323 |
| Qcache_total_blocks | 1222 |
| Questions | 10121488 |
| Rpl_status | NULL |
| Select_full_join | 5 |
| Select_full_range_join | 0 |
| Select_range | 340103 |
| Select_range_check | 0 |
| Select_scan | 117462 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 4 |
| Slow_queries | 5382 |
| Sort_merge_passes | 4 |
| Sort_range | 350819 |
| Sort_rows | 6045581 |
| Sort_scan | 24172 |
| Table_locks_immediate | 7459780 |
| Table_locks_waited | 41998 |
| Threads_cached | 155 |
| Threads_connected | 1 |
| Threads_created | 156 |
| Threads_running | 1 |
| Uptime | 321655 |
+----------------------------+------------+
Uptime: 321656 Threads: 1 Questions: 10121499 Slow queries: 5382 Opens: 1487 Flush tables: 1 Open tables: 274 Queries per second avg: 31.467
mysqladmin Ver 8.41 Distrib 4.1.21, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 4.1.21-standard
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 3 days 17 hours 20 min 56 sec
Threads: 1 Questions: 10121499 Slow queries: 5382 Opens: 1487 Flush tables: 1 Open tables: 274 Queries per second avg: 31.467
8. is your vB the only thing on the server? Yes, only the vBulletin installation.
9. how many average and max concurrent users on your vB forum ?
Average: 100
Max concurrent users: 325
and what your cookie timeout is ? 600 seconds
10. http://www.xtasis.org/phpinfo.php
11. httpd.conf
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 10
StartServers 5
MaxClients 150
MaxRequestsPerChild 0
12. 3.5.3 with security patches
13. No files +2GB
14.
uname -a
Linux newserver.xtasis.org 2.6.9-34.ELsmp #1 SMP Wed Mar 8 00:27:03 CST 2006 i686 i686 i386 GNU/Linux
ulimit -aH
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) 16380
virtual memory (kbytes, -v) 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 : 2801.828
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 pni monitor ds_cpl cid xtpr
bogomips : 5605.98
processor : 1
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 3
cpu MHz : 2801.828
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 pni monitor ds_cpl cid xtpr
bogomips : 5599.46
processor : 2
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 3
cpu MHz : 2801.828
cache size : 2048 KB
physical id : 3
siblings : 2
core id : 3
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 pni monitor ds_cpl cid xtpr
bogomips : 5599.44
processor : 3
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 2.80GHz
stepping : 3
cpu MHz : 2801.828
cache size : 2048 KB
physical id : 3
siblings : 2
core id : 3
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 pni monitor ds_cpl cid xtpr
bogomips : 5599.50