Eva if you could optimize please

[GMTalk]

hey Eva. here is another server. Seems our dual amd2800 kicked over and died a few weeks back. I got this as a temp until I get the new sun server in. Any help is appreciated. We are running into huge loads in the 30s. I am running Turck MMCache and it helped out some, but we are getting hammered.


1. dedicated or shared virual server

--- Own colo

2. your server specs. For example:

Dual Xeon 2.4 HT
2gb ram
40gb eide and 250 eide
RH Fedora Core 1
apache 1.3.29
PHP 4.3.4
mysql 4.0.18-standard


3. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server ?
4. if possible how mysql was compiled/installed

--- installed when installing Fedora Core 1

5. your top stats


23:34:32 up 8:51, 3 users, load average: 5.91, 6.02, 6.96
262 processes: 260 sleeping, 2 running, 0 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 60.8% 0.0% 31.2% 0.0% 0.0% 0.0% 307.6%
cpu00 17.1% 0.0% 6.2% 0.0% 0.0% 0.0% 76.5%
cpu01 21.0% 0.0% 1.5% 0.0% 0.0% 0.0% 77.3%
cpu02 17.9% 0.0% 13.2% 0.0% 0.0% 0.0% 68.7%
cpu03 4.6% 0.0% 10.1% 0.0% 0.0% 0.0% 85.1%
Mem: 2068240k av, 2016472k used, 51768k free, 0k shrd, 24496k buff
1036544k active, 908124k inactive
Swap: 3478032k av, 1007364k used, 2470668k free 243832k cached



6.

mysqladmin -u root -p variables

| convert_character_set | |
| datadir | /var/lib/mysql/ |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_min_word_len | 4 |
| ft_max_word_len | 254 |
| ft_max_word_len_for_sort | 20 |
| ft_stopword_file | (built-in) |
| have_bdb | NO |
| have_crypt | YES |
| have_innodb | DISABLED |
| have_isam | YES |
| have_raid | NO |
| have_symlink | YES |
| have_openssl | NO |
| have_query_cache | YES |
| init_file | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_buffer_pool_size | 8388608 |
| innodb_data_file_path | |
| innodb_data_home_dir | |
| innodb_file_io_threads | 4 |
| innodb_force_recovery | 0 |
| innodb_thread_concurrency | 8 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_fast_shutdown | ON |
| innodb_flush_method | |
| innodb_lock_wait_timeout | 50 |
| 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_mirrored_log_groups | 1 |
| innodb_max_dirty_pages_pct | 90 |
| interactive_timeout | 28800 |
| join_buffer_size | 2093056 |
| key_buffer_size | 100663296 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_update | OFF |
| log_bin | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | OFF |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 16776192 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connections | 650 |
| max_connect_errors | 10 |
| max_delayed_threads | 20 |
| max_heap_table_size | 16777216 |
| max_join_size | 4294967295 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_user_connections | 0 |
| max_tmp_tables | 32 |
| max_write_lock_count | 4294967295 |
| myisam_max_extra_sort_file_size | 268435456 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_repair_threads | 1 |
| myisam_recover_options | OFF |
| myisam_sort_buffer_size | 67108864 |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| open_files_limit | 3260 |
| pid_file | /var/lib/mysql/afc.egihosting.com.pid |
| log_error | |
| port | 3306 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_size | 33554432 |
| query_cache_type | ON |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 2093056 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| rpl_recovery_rank | 0 |
| server_id | 0 |
| slave_net_timeout | 3600 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer_size | 4194296 |
| sql_mode | 0 |
| table_cache | 1024 |
| table_type | MYISAM |
| thread_cache_size | 64 |
| thread_stack | 126976 |
| tx_isolation | REPEATABLE-READ |
| timezone | EST |
| tmp_table_size | 33554432 |
| tmpdir | /tmp/ |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| version | 4.0.18-standard |
| version_comment | Official MySQL RPM |
| wait_timeout | 900 |
+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+



7. mysqladmin -u root -p extended-status

+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 2 |
| Aborted_connects | 1 |
| Bytes_received | 18012389 |
| Bytes_sent | 1119846439 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 11209 |
| 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 | 162 |
| 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 | 5275 |
| Com_insert_select | 21 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 181 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 214 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 22566 |
| 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 | 4 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 4 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 0 |
| Com_show_variables | 2 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 181 |
| Com_update | 5738 |
| Connections | 11028 |
| Created_tmp_disk_tables | 159 |
| Created_tmp_tables | 1567 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 310 |
| Handler_read_first | 3135 |
| Handler_read_key | 1476646 |
| Handler_read_next | 9029615 |
| Handler_read_prev | 36630 |
| Handler_read_rnd | 2396621 |
| Handler_read_rnd_next | 6847586 |
| Handler_rollback | 0 |
| Handler_update | 7511 |
| Handler_write | 261379 |
| Key_blocks_used | 17336 |
| Key_read_requests | 4562211 |
| Key_reads | 17102 |
| Key_write_requests | 13375 |
| Key_writes | 10811 |
| Max_used_connections | 73 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 653 |
| Open_files | 807 |
| Open_streams | 0 |
| Opened_tables | 659 |
| Questions | 88261 |
| Qcache_queries_in_cache | 1586 |
| Qcache_inserts | 21790 |
| Qcache_hits | 31695 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 776 |
| Qcache_free_memory | 22147912 |
| Qcache_free_blocks | 866 |
| Qcache_total_blocks | 4407 |
| Rpl_status | NULL |
| Select_full_join | 11 |
| Select_full_range_join | 24 |
| Select_range | 4485 |
| Select_range_check | 0 |
| Select_scan | 4166 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 51 |
| Sort_merge_passes | 0 |
| Sort_range | 2837 |
| Sort_rows | 11527671 |
| Sort_scan | 2246 |
| Table_locks_immediate | 58255 |
| Table_locks_waited | 3218 |
| Threads_cached | 53 |
| Threads_created | 74 |
| Threads_connected | 14 |
| Threads_running | 5 |
| Uptime | 976 |
+--------------------------+------------+


8. oh and is your vB the only thing on the server? or other scripts? sites?


--- 4vb sites, adserving software, hivemail and photopost

9. how many average and max concurrent users on your vB forum ?

--- on all sites we have a combined average 1600 concurrent users


10. create a file named phpinfo.php and place this code in it and post the url/link to it from your web site


http://www.s10forum.com/boom/phpinfo.php

11.

KeepAlive =========== Off
MaxKeepAliveRequests = 100
KeepAliveTimeout ===== 15
MinSpareServers ====== 5
MaxSpareServers ===== 25
StartServers ========= 15
MaxClients =========== 300

12. what version of vB are you running ?

vb3rc2 patched on all.

[eva2000]

can you post url to mysqlinfo.php as the output you posted only has mysql uptime of 976 seconds not long enough to guage mysql server activity properly

really, with 1600 users you will need 2 servers one for web serving and one for mysql server

also the EIDE disks probably are slowing you down when you have 1600 users

[GMTalk]

eva: 1600 was in error. We can see as high as 600 not 1600.

Here are the links you should need

http://www.fordforums.com/mysqlinfo.php
http://www.fordforums.com/phpinfo.php

[eva2000]

from mysqlinfo and mysql variables output looks like you set your keybuffer too high causing server to swap to disk ALOT

change my.cnf to below and restart mysql

[mysqld]
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 = 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

[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


and restart maxclients in httpd.conf from 300 to 200 and restart apache

[GMTalk]

from mysqlinfo and mysql variables output looks like you set your keybuffer too high causing server to swap to disk ALOT

change my.cnf to below and restart mysql


and restart maxclients in httpd.conf from 300 to 200 and restart apache
Made the changes as described and running into high loads. we are getting average of 5's during the slow times and 20s during peak times.

We just got a notice "Apache has reached the MaxClients limit. You should edit httpd.conf and change the MaxClients to something higher."

[GMTalk]

actually loads today are in the teens and have been for the last 2 hours. Bad part is that we are fixing to go into peak times. :/

Also update is that we are running vb3 gold on all but one.

[eva2000]

could be the ide disk consuming cpu time

i read about fedora sometimes not utilising dual cpus properly only accessing 1 of the dual cpus for work load ?

[GMTalk]

could be the ide disk consuming cpu time

i read about fedora sometimes not utilising dual cpus properly only accessing 1 of the dual cpus for work load ?
I am still investigating this one myself. We have been playing and tweaking on a live server for a few days now. I have a Dual AMD 2400/4gb/dual 73 and dual 36 gb 15k drives I am about to load up with Fedora again and ship off. May make this the sql box and the live server now the http and sendmail box. Not sure yet. May just say f**k it and yank that dual xeon and just sell it.