New server is lagging very much (opt req) [Archive]

stormblast-

Wed 13th Dec '06, 10:12am

Server is live, brand new, and we really got a problem here..

1. dedicated
2. specs:

2x Xeon 5130
Dual Core
2,0 GHz
1333 MHz FSB
4 MB Cache
4GB FB RAM
2 x 146 GB
SAS HDD
Hardware RAID1

Debian Sarge
Apache2.x

3. MySQL 5.x (newest dotdeb)

4. dotdeb

5.

top - 15:07:33 up 20 min, 1 user, load average: 74.69, 70.30, 33.68
Tasks: 280 total, 42 running, 238 sleeping, 0 stopped, 0 zombie
Cpu(s): 85.9% us, 12.9% sy, 0.0% ni, 0.8% id, 0.2% wa, 0.0% hi, 0.2% si
Mem: 4148232k total, 2555600k used, 1592632k free, 19224k buffers
Swap: 1052248k total, 0k used, 1052248k free, 612128k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2728 mysql 10 -5 631m 225m 4516 S 99.9 5.6 9:45.00 mysqld
3324 www-data 18 0 63028 18m 5804 R 15.3 0.5 0:01.36 apache2
3178 www-data 15 0 60012 16m 6764 S 9.6 0.4 0:02.76 apache2
3227 www-data 16 0 54264 10m 6400 S 8.6 0.3 0:00.93 apache2
3419 www-data 15 0 54256 10m 6392 S 8.0 0.3 0:00.84 apache2
3161 www-data 15 0 54316 10m 6640 S 7.0 0.3 0:02.35 apache2
3361 www-data 16 0 63812 18m 5688 R 7.0 0.5 0:00.64 apache2
3381 www-data 15 0 60304 16m 6496 S 7.0 0.4 0:01.17 apache2
3399 www-data 16 0 62564 18m 6348 R 7.0 0.5 0:00.53 apache2
3274 www-data 16 0 60000 16m 6460 S 6.6 0.4 0:00.71 apache2
3351 www-data 15 0 54256 10m 6432 S 6.3 0.3 0:00.60 apache2
3194 www-data 16 0 64336 21m 7252 R 6.0 0.5 0:02.04 apache2
3282 www-data 16 0 64028 19m 5880 R 6.0 0.5 0:00.88 apache2
2865 www-data 16 0 64952 22m 8184 R 5.6 0.6 0:05.08 apache2
2897 www-data 24 0 63720 21m 8360 R 5.6 0.5 0:06.25 apache2
3201 www-data 16 0 54284 12m 8128 S 5.6 0.3 0:01.05 apache2
3335 www-data 15 0 54264 10m 6172 S 5.6 0.3 0:00.59 apache2
3355 www-data 15 0 61048 16m 6184 S 5.3 0.4 0:00.39 apache2
3044 www-data 15 0 62672 19m 7428 S 5.0 0.5 0:04.49 apache2
3273 www-data 16 0 63868 20m 6732 R 5.0 0.5 0:01.10 apache2
2948 www-data 21 0 64540 21m 7160 R 4.6 0.5 0:04.92 apache2
3217 www-data 15 0 60836 16m 6376 R 4.6 0.4 0:00.75 apache2
3303 www-data 16 0 63768 19m 5908 R 4.6 0.5 0:00.87 apache2
3400 www-data 16 0 54256 10m 6420 S 4.6 0.3 0:00.53 apache2
3183 www-data 16 0 63832 19m 6584 R 4.3 0.5 0:02.08 apache2
3211 www-data 16 0 54532 10m 5824 S 4.3 0.3 0:00.98 apache2
3272 www-data 16 0 64104 19m 6304 R 4.3 0.5 0:00.53 apache2
3281 www-data 15 0 54284 10m 6132 S 4.3 0.3 0:00.69 apache2
3242 www-data 16 0 63596 19m 6368 R 4.0 0.5 0:00.80 apache2
3299 www-data 15 0 59936 15m 5760 S 4.0 0.4 0:00.45 apache2
3346 www-data 17 0 63720 18m 5608 R 4.0 0.5 0:00.24 apache2
2861 www-data 15 0 54844 13m 8808 S 3.7 0.3 0:05.70 apache2
3117 www-data 16 0 64348 19m 6164 R 3.7 0.5 0:00.92 apache2
3269 www-data 15 0 63656 20m 7140 S 3.7 0.5 0:00.72 apache2
3347 www-data 16 0 63804 18m 5760 R 3.7 0.5 0:00.42 apache2
3415 www-data 16 0 64612 20m 6336 R 3.7 0.5 0:00.40 apache2
2819 www-data 16 0 64128 20m 7088 R 3.3 0.5 0:06.64 apache2
3177 www-data 16 0 63344 19m 6880 S 3.3 0.5 0:01.97 apache2
3221 www-data 15 0 54588 10m 6488 S 3.3 0.3 0:01.06 apache2
3164 www-data 16 0 63088 19m 6832 R 3.0 0.5 0:02.47 apache2
3202 www-data 16 0 60476 16m 6620 S 3.0 0.4 0:04.91 apache2
3223 www-data 16 0 63872 19m 5880 R 3.0 0.5 0:00.81 apache2
3248 www-data 15 0 54332 10m 5984 S 3.0 0.3 0:00.37 apache2
3424 www-data 16 0 59904 15m 6044 S 3.0 0.4 0:00.70 apache2
3210 www-data 16 0 64352 19m 5916 S 2.7 0.5 0:01.72 apache2
3240 www-data 16 0 64332 19m 5944 S 2.7 0.5 0:01.76 apache2
3316 www-data 15 0 64376 20m 6304 S 2.7 0.5 0:00.69 apache2
2837 www-data 15 0 64460 22m 8324 S 2.3 0.5 0:05.91 apache2
3016 www-data 16 0 64828 21m 7308 S 2.3 0.5 0:03.14 apache2
3017 www-data 16 0 64724 21m 7204 S 2.3 0.5 0:03.95 apache2
3169 www-data 15 0 64436 20m 6476 S 2.3 0.5 0:01.77 apache2
3225 www-data 15 0 62604 18m 6144 S 2.3 0.5 0:00.52 apache2
3296 www-data 16 0 62044 17m 5740 R 2.3 0.4 0:00.67 apache2
3338 www-data 15 0 54216 9.9m 5720 S 2.3 0.2 0:00.39 apache2
3421 www-data 16 0 59964 17m 7480 R 2.3 0.4 0:00.24 apache2
3568 www-data 16 0 61656 16m 5560 R 2.3 0.4 0:00.20 apache2
3085 www-data 16 0 60528 17m 7016 S 2.0 0.4 0:03.24 apache2
3352 www-data 15 0 59968 15m 6204 S 2.0 0.4 0:00.35 apache2
3571 www-data 15 0 60660 15m 5268 S 2.0 0.4 0:00.15 apache2
3013 www-data 15 0 60828 17m 7332 S 1.7 0.4 0:03.77 apache2 6.

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld]
bind-address = 127.0.0.1
user = mysql
port = 3306
skip-name-resolve
safe-show-database
back_log = 75
skip-innodb
max_connections = 650
key_buffer = 128M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size = 512
wait_timeout = 18
connect_timeout = 10
tmp_table_size = 192M
max_heap_table_size = 144M
max_allowed_packet = 64M
max_connect_errors = 10
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 = 163840
query_alloc_block_size = 32768
default-storage-engine = MyISAM
tmpdir = /tmp
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid
log-error = /var/log/mysql/mysqld.err
basedir = /usr
datadir = /var/lib/mysql

[mysqld_safe]
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

[mysqlhotcopy]
interactive-timeout
7. MySQL stats from ssh telnet as root user type:

+---------------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 75 |
| basedir | /usr/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| 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 |
| completion_type | 0 |
| concurrent_insert | 1 |
| 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 |
| div_precision_increment | 4 |
| engine_condition_pushdown | OFF |
| 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 | YES |
| have_bdb | NO |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | YES |
| have_csv | YES |
| have_dynamic_loading | YES |
| have_example_engine | NO |
| have_federated_engine | YES |
| have_geometry | YES |
| have_innodb | DISABLED |
| have_isam | NO |
| have_merge_engine | YES |
| have_ndbcluster | DISABLED |
| have_openssl | DISABLED |
| 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_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | |
| innodb_data_home_dir | |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| 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_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| innodb_thread_sleep_delay | 10000 |
| interactive_timeout | 28800 |
| join_buffer_size | 1044480 |
| key_buffer_size | 134217728 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| lc_time_names | en_US |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_error | /var/log/mysql/mysqld.err |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | 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 | 650 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 150993920 |
| max_insert_delayed_threads | 20 |
| max_join_size | 18446744073709551615 |
| 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_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 67108864 |
| myisam_stats_method | nulls_unequal |
| ndb_autoincrement_prefetch_sz | 32 |
| ndb_force_send | ON |
| ndb_use_exact_count | ON |
| ndb_use_transactions | OFF |
| ndb_cache_check_time | 0 |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | OFF |
| open_files_limit | 4260 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /var/run/mysqld/mysqld.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| prepared_stmt_count | 0 |
| protocol_version | 10 |
| query_alloc_block_size | 32768 |
| query_cache_limit | 6291456 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 100663296 |
| 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_compressed_protocol | OFF |
| slave_load_tmpdir | /tmp/ |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| slow_launch_time | 2 |
| socket | /var/run/mysqld/mysqld.sock |
| sort_buffer_size | 2097144 |
| sql_big_selects | ON |
| sql_mode | |
| sql_notes | ON |
| sql_warnings | OFF |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_key | |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| system_time_zone | CET |
| table_cache | 1800 |
| table_lock_wait_timeout | 50 |
| table_type | MyISAM |
| thread_cache_size | 512 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmp_table_size | 201326592 |
| tmpdir | /tmp |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| updatable_views_with_limit | YES |
| version | 5.0.30-Debian_0.dotdeb.1 |
| version_comment | Dotdeb Sarge backport |
| version_compile_machine | i386 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 18 |
+---------------------------------+-----------------------------+
+-----------------------------------+-----------+
| Variable_name | Value |
+-----------------------------------+-----------+
| Aborted_clients | 542 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 8901774 |
| Bytes_sent | 908394008 |
| Com_admin_commands | 2337 |
| Com_alter_db | 0 |
| Com_alter_table | 59 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 3310 |
| Com_change_master | 0 |
| Com_check | 167 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 14 |
| Com_dealloc_sql | 0 |
| Com_delete | 73 |
| 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 | 1 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 1753 |
| 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 | 183 |
| 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 | 9105 |
| Com_set_option | 6 |
| 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 | 1 |
| 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 | 2 |
| 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 | 1289 |
| Com_update_multi | 1 |
| 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 | 560 |
| Created_tmp_disk_tables | 3 |
| Created_tmp_files | 440 |
| Created_tmp_tables | 345 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 22 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 1465 |
| Handler_read_key | 286541 |
| Handler_read_next | 42310365 |
| Handler_read_prev | 9899 |
| Handler_read_rnd | 93978 |
| Handler_read_rnd_next | 3253189 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 2629 |
| Handler_write | 30460 |
| 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 | 96141 |
| Key_blocks_used | 19839 |
| Key_read_requests | 5273408 |
| Key_reads | 19910 |
| Key_write_requests | 5715 |
| Key_writes | 5205 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 276 |
| 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 | 748 |
| Open_streams | 0 |
| Open_tables | 608 |
| Opened_tables | 692 |
| Qcache_free_blocks | 874 |
| Qcache_free_memory | 85393080 |
| Qcache_hits | 10175 |
| Qcache_inserts | 8638 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 477 |
| Qcache_queries_in_cache | 2322 |
| Qcache_total_blocks | 5650 |
| Questions | 26156 |
| Rpl_status | NULL |
| Select_full_join | 1 |
| Select_full_range_join | 3 |
| Select_range | 2731 |
| Select_range_check | 0 |
| Select_scan | 2274 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 109 |
| Sort_merge_passes | 715 |
| Sort_range | 2127 |
| Sort_rows | 222572 |
| Sort_scan | 499 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 21748 |
| Table_locks_waited | 1376 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 0 |
| Threads_connected | 276 |
| Threads_created | 276 |
| Threads_running | 194 |
| Uptime | 459 |
+-----------------------------------+-----------+
Uptime: 460 Threads: 276 Questions: 26160 Slow queries: 109 Opens: 692 Flush tables: 1 Open tables: 608 Queries per second avg: 56.870
mysqladmin Ver 8.41 Distrib 5.0.30, for pc-linux-gnu on i386
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 5.0.30-Debian_0.dotdeb.1
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 7 min 40 sec

Threads: 276 Questions: 26160 Slow queries: 109 Opens: 692 Flush tables: 1 Open tables: 608 Queries per second avg: 56.870
8 Yes, the only thing is vB!

9 500-1000 standard cookie timeout.

10 http://www.musiker-board.de/testy.php

11apache2:

# Based upon the NCSA server configuration files originally by Rob McCool.
# Changed extensively for the Debian package by Daniel Stone <daniel@sfarc.net>
# and also by Thom May <thom@debian.org>.

# ServerRoot: The top of the directory tree under which the server's
# configuration, error, and log files are kept.
#
# NOTE! If you intend to place this on an NFS (or otherwise network)
# mounted filesystem then please read the LockFile documentation
# (available at <URL:http://www.apache.org/docs/mod/core.html#lockfile>);
# you will save yourself a lot of trouble.

ServerRoot "/etc/apache2"

# The LockFile directive sets the path to the lockfile used when Apache
# is compiled with either USE_FCNTL_SERIALIZED_ACCEPT or
# USE_FLOCK_SERIALIZED_ACCEPT. This directive should normally be left at
# its default value. The main reason for changing it is if the logs
# directory is NFS mounted, since the lockfile MUST BE STORED ON A LOCAL
# DISK. The PID of the main server process is automatically appended to
# the filename.

LockFile /var/lock/apache2/accept.lock

# PidFile: The file in which the server should record its process
# identification number when it starts.

PidFile /var/run/apache2.pid

# Timeout: The number of seconds before receives and sends time out.

KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 10
MinSpareServers 10
MaxSpareServers 15
StartServers 10
MaxClients 200
MaxRequestsPerChild 1000
Timeout 300

##
## Server-Pool Size Regulation (MPM specific)
##

# prefork MPM
# StartServers ......... number of server processes to start
# MinSpareServers ...... minimum number of server processes which are kept spare
# MaxSpareServers ...... maximum number of server processes which are kept spare
# MaxClients ........... maximum number of server processes allowed to start
# MaxRequestsPerChild .. maximum number of requests a server process serves
<IfModule prefork.c>
StartServers 5
MinSpareServers 5
MaxSpareServers 10
MaxClients 20
MaxRequestsPerChild 0
</IfModule>

# pthread MPM
# StartServers ......... initial number of server processes to start
# MaxClients ........... maximum number of server processes allowed to start
# MinSpareThreads ...... minimum number of worker threads which are kept spare
# MaxSpareThreads ...... maximum number of worker threads which are kept spare
# ThreadsPerChild ...... constant number of worker threads in each server process
# MaxRequestsPerChild .. maximum number of requests a server process serves
<IfModule worker.c>
StartServers 2
MaxClients 150
MinSpareThreads 25
MaxSpareThreads 75
ThreadsPerChild 25
MaxRequestsPerChild 0
</IfModule>

# perchild MPM
# NumServers ........... constant number of server processes
# StartThreads ......... initial number of worker threads in each server process
# MinSpareThreads ...... minimum number of worker threads which are kept spare
# MaxSpareThreads ...... maximum number of worker threads which are kept spare
# MaxThreadsPerChild ... maximum number of worker threads in each server process
# MaxRequestsPerChild .. maximum number of connections per server process (then it dies)
<IfModule perchild.c>
NumServers 5
StartThreads 5
MinSpareThreads 5
MaxSpareThreads 10
MaxThreadsPerChild 20
MaxRequestsPerChild 0
AcceptMutex fcntl
</IfModule>

User www-data
Group www-data

# The following directives define some format nicknames for use with
# a CustomLog directive (see below).
LogFormat "%h %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\"" combined
LogFormat "%h %l %u %t \"%r\" %>s %b" common
LogFormat "%{Referer}i -> %U" referer
LogFormat "%{User-agent}i" agent

# Global error log.
ErrorLog /var/log/apache2/error.log

# Include module configuration:
Include /etc/apache2/mods-enabled/*.load
Include /etc/apache2/mods-enabled/*.conf

# Include all the user configurations:
Include /etc/apache2/httpd.conf

# Include ports listing
Include /etc/apache2/ports.conf

# Include generic snippets of statements
Include /etc/apache2/conf.d/[^.#]*

#Let's have some Icons, shall we?
Alias /icons/ "/usr/share/apache2/icons/"
<Directory "/usr/share/apache2/icons">
Options Indexes MultiViews
AllowOverride None
Order allow,deny
Allow from all
</Directory>

# Set up the default error docs.
#
# Customizable error responses come in three flavors:
# 1) plain text 2) local redirects 3) external redirects
#
# Some examples:
#ErrorDocument 500 "The server made a boo boo."
#ErrorDocument 404 /missing.html
#ErrorDocument 404 "/cgi-bin/missing_handler.pl"
#ErrorDocument 402 http://www.example.com/subscription_info.html
#

#
# Putting this all together, we can Internationalize error responses.
#
# We use Alias to redirect any /error/HTTP_<error>.html.var response to
# our collection of by-error message multi-language collections. We use
# includes to substitute the appropriate text.
#
# You can modify the messages' appearance without changing any of the
# default HTTP_<error>.html.var files by adding the line;
#
# Alias /error/include/ "/your/include/path/"
#
# which allows you to create your own set of files by starting with the
# /usr/local/apache2/error/include/ files and
# copying them to /your/include/path/, even on a per-VirtualHost basis.
#

<IfModule mod_negotiation.c>
<IfModule mod_include.c>
Alias /error/ "/usr/share/apache2/error/"

<Directory "/usr/share/apache2/error">
AllowOverride None
Options IncludesNoExec
AddOutputFilter Includes html
AddHandler type-map var
Order allow,deny
Allow from all
LanguagePriority en es de fr
ForceLanguagePriority Prefer Fallback
</Directory>

ErrorDocument 400 /error/HTTP_BAD_REQUEST.html.var
ErrorDocument 401 /error/HTTP_UNAUTHORIZED.html.var
ErrorDocument 403 /error/HTTP_FORBIDDEN.html.var
ErrorDocument 404 /error/HTTP_NOT_FOUND.html.var
ErrorDocument 405 /error/HTTP_METHOD_NOT_ALLOWED.html.var
ErrorDocument 408 /error/HTTP_REQUEST_TIME_OUT.html.var
ErrorDocument 410 /error/HTTP_GONE.html.var
ErrorDocument 411 /error/HTTP_LENGTH_REQUIRED.html.var
ErrorDocument 412 /error/HTTP_PRECONDITION_FAILED.html.var
ErrorDocument 413 /error/HTTP_REQUEST_ENTITY_TOO_LARGE.html.var
ErrorDocument 414 /error/HTTP_REQUEST_URI_TOO_LARGE.html.var
ErrorDocument 415 /error/HTTP_SERVICE_UNAVAILABLE.html.var
ErrorDocument 500 /error/HTTP_INTERNAL_SERVER_ERROR.html.var
ErrorDocument 501 /error/HTTP_NOT_IMPLEMENTED.html.var
ErrorDocument 502 /error/HTTP_BAD_GATEWAY.html.var
ErrorDocument 503 /error/HTTP_SERVICE_UNAVAILABLE.html.var
ErrorDocument 506 /error/HTTP_VARIANT_ALSO_VARIES.html.var

</IfModule>
</IfModule>

DirectoryIndex index.html index.cgi index.pl index.php index.xhtml

# UserDir is now a module
#UserDir public_html
#UserDir disabled root

#<Directory /home/*/public_html>
# AllowOverride FileInfo AuthConfig Limit
# Options Indexes SymLinksIfOwnerMatch IncludesNoExec
#</Directory>

AccessFileName .htaccess

<Files ~ "^\.ht">
Order allow,deny
Deny from all
</Files>

UseCanonicalName Off

TypesConfig /etc/mime.types
DefaultType text/plain

HostnameLookups Off

IndexOptions FancyIndexing VersionSort

AddIconByEncoding (CMP,/icons/compressed.gif) x-compress x-gzip

AddIconByType (TXT,/icons/text.gif) text/*
AddIconByType (IMG,/icons/image2.gif) image/*
AddIconByType (SND,/icons/sound2.gif) audio/*
AddIconByType (VID,/icons/movie.gif) video/*

# This really should be .jpg.

AddIcon /icons/binary.gif .bin .exe
AddIcon /icons/binhex.gif .hqx
AddIcon /icons/tar.gif .tar
AddIcon /icons/world2.gif .wrl .wrl.gz .vrml .vrm .iv
AddIcon /icons/compressed.gif .Z .z .tgz .gz .zip
AddIcon /icons/a.gif .ps .ai .eps
AddIcon /icons/layout.gif .html .shtml .htm .pdf
AddIcon /icons/text.gif .txt
AddIcon /icons/c.gif .c
AddIcon /icons/p.gif .pl .py
AddIcon /icons/f.gif .for
AddIcon /icons/dvi.gif .dvi
AddIcon /icons/uuencoded.gif .uu
AddIcon /icons/script.gif .conf .sh .shar .csh .ksh .tcl
AddIcon /icons/tex.gif .tex
AddIcon /icons/bomb.gif core

AddIcon /icons/back.gif ..
AddIcon /icons/hand.right.gif README
AddIcon /icons/folder.gif ^^DIRECTORY^^
AddIcon /icons/blank.gif ^^BLANKICON^^

# This is from Matty J's patch. Anyone want to make the icons?
#AddIcon /icons/dirsymlink.jpg ^^SYMDIR^^
#AddIcon /icons/symlink.jpg ^^SYMLINK^^

DefaultIcon /icons/unknown.gif

ReadmeName README.html
HeaderName HEADER.html

IndexIgnore .??* *~ *# HEADER* RCS CVS *,t

AddEncoding x-compress Z
AddEncoding x-gzip gz tgz

AddLanguage da .dk
AddLanguage nl .nl
AddLanguage en .en
AddLanguage et .et
AddLanguage fr .fr
AddLanguage de .de
AddLanguage el .el
AddLanguage it .it
AddLanguage ja .ja
AddLanguage pl .po
AddLanguage ko .ko
AddLanguage pt .pt
AddLanguage no .no
AddLanguage pt-br .pt-br
AddLanguage ltz .ltz
AddLanguage ca .ca
AddLanguage es .es
AddLanguage sv .se
AddLanguage cz .cz
AddLanguage ru .ru
AddLanguage tw .tw
AddLanguage zh-tw .tw

LanguagePriority en da nl et fr de el it ja ko no pl pt pt-br ltz ca es sv tw

#AddDefaultCharset ISO-8859-1

AddCharset ISO-8859-1 .iso8859-1 .latin1
AddCharset ISO-8859-2 .iso8859-2 .latin2 .cen
AddCharset ISO-8859-3 .iso8859-3 .latin3
AddCharset ISO-8859-4 .iso8859-4 .latin4
AddCharset ISO-8859-5 .iso8859-5 .latin5 .cyr .iso-ru
AddCharset ISO-8859-6 .iso8859-6 .latin6 .arb
AddCharset ISO-8859-7 .iso8859-7 .latin7 .grk
AddCharset ISO-8859-8 .iso8859-8 .latin8 .heb
AddCharset ISO-8859-9 .iso8859-9 .latin9 .trk
AddCharset ISO-2022-JP .iso2022-jp .jis
AddCharset ISO-2022-KR .iso2022-kr .kis
AddCharset ISO-2022-CN .iso2022-cn .cis
AddCharset Big5 .Big5 .big5
# For russian, more than one charset is used (depends on client, mostly):
AddCharset WINDOWS-1251 .cp-1251 .win-1251
AddCharset CP866 .cp866
AddCharset KOI8-r .koi8-r .koi8-ru
AddCharset KOI8-ru .koi8-uk .ua
AddCharset ISO-10646-UCS-2 .ucs2
AddCharset ISO-10646-UCS-4 .ucs4
AddCharset UTF-8 .utf8

AddCharset GB2312 .gb2312 .gb
AddCharset utf-7 .utf7
AddCharset utf-8 .utf8
AddCharset big5 .big5 .b5
AddCharset EUC-TW .euc-tw
AddCharset EUC-JP .euc-jp
AddCharset EUC-KR .euc-kr
AddCharset shift_jis .sjis

#AddType application/x-httpd-php .php
#AddType application/x-httpd-php-source .phps

AddType application/x-tar .tgz

# To use CGI scripts outside /cgi-bin/:
#
#AddHandler cgi-script .cgi

# To use server-parsed HTML files
#
<FilesMatch "\.shtml(\..+)?$">
SetOutputFilter INCLUDES
</FilesMatch>

# If you wish to use server-parsed imagemap files, use
#
#AddHandler imap-file map

BrowserMatch "Mozilla/2" nokeepalive
BrowserMatch "MSIE 4\.0b2;" nokeepalive downgrade-1.0 force-response-1.0
BrowserMatch "RealPlayer 4\.0" force-response-1.0
BrowserMatch "Java/1\.0" force-response-1.0
BrowserMatch "JDK/1\.0" force-response-1.0

#
# The following directive disables redirects on non-GET requests for
# a directory that does not include the trailing slash. This fixes a
# problem with Microsoft WebFolders which does not appropriately handle
# redirects for folders with DAV methods.
#

BrowserMatch "Microsoft Data Access Internet Publishing Provider" redirect-carefully
BrowserMatch "^WebDrive" redirect-carefully
BrowserMatch "^gnome-vfs" redirect-carefully
BrowserMatch "^WebDAVFS/1.[012]" redirect-carefully

# Allow server status reports, with the URL of http://servername/server-status
# Change the ".your_domain.com" to match your domain to enable.
#
#<Location /server-status>
# SetHandler server-status
# Order deny,allow
# Deny from all
# Allow from .your_domain.com
#</Location>

# Allow remote server configuration reports, with the URL of
# http://servername/server-info (requires that mod_info.c be loaded).
# Change the ".your_domain.com" to match your domain to enable.
#
#<Location /server-info>
# SetHandler server-info
# Order deny,allow
# Deny from all
# Allow from .your_domain.com
#</Location>

# Include the virtual host configurations:
Include /etc/apache2/sites-enabled/[^.#]*
12. latest 3.6.4

13. No

14. Linux ds80-237-211-40.dedicated.hosteurope.de 2.6.18.3 #2 SMP Mon Nov 27 14:42:26 CET 2006 i686 GNU/Linux

core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
max locked memory (kbytes, -l) unlimited
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) unlimited
virtual memory (kbytes, -v) unlimited

cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Xeon(R) CPU 5130 @ 2.00GHz
stepping : 6
cpu MHz : 1995.197
cache size : 4096 KB
physical id : 0
siblings : 2
core id : 0
cpu cores : 2
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 10
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm constant_tsc pni monitor ds_cpl vmx tm2 cx16 xtpr lahf_lm
bogomips : 3993.18

processor : 1
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Xeon(R) CPU 5130 @ 2.00GHz
stepping : 6
cpu MHz : 1995.197
cache size : 4096 KB
physical id : 0
siblings : 2
core id : 1
cpu cores : 2
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 10
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm constant_tsc pni monitor ds_cpl vmx tm2 cx16 xtpr lahf_lm
bogomips : 3990.34

processor : 2
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Xeon(R) CPU 5130 @ 2.00GHz
stepping : 6
cpu MHz : 1995.197
cache size : 4096 KB
physical id : 3
siblings : 2
core id : 0
cpu cores : 2
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 10
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm constant_tsc pni monitor ds_cpl vmx tm2 cx16 xtpr lahf_lm
bogomips : 3990.37

processor : 3
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Xeon(R) CPU 5130 @ 2.00GHz
stepping : 6
cpu MHz : 1995.197
cache size : 4096 KB
physical id : 3
siblings : 2
core id : 1
cpu cores : 2
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 10
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm constant_tsc pni monitor ds_cpl vmx tm2 cx16 xtpr lahf_lm
bogomips : 3990.38
The server is brand new.. i'm really running in probs with this mysql 99.9% problem.. the board is really big, 2 mio posts and more, fulltext search enabled

www.musiker-board.de/vb (http://www.musiker-board.de/vb)

Eaccelator 32b is installed!

Thanks a lot in advance!

stormblast-

Wed 13th Dec '06, 10:33am

Ok,

since I added

KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 10
MinSpareServers 10
MaxSpareServers 15
StartServers 10
MaxClients 200
MaxRequestsPerChild 1000
Timeout 300
in apache2.conf instead of httpd.conf(httpd.conf is empty), added in the above post the complete apache2.conf, the server runs on a much lower load, but now the establishing of connections to the board is somewhat slow..

New top:

top - 15:32:25 up 44 min, 1 user, load average: 0.49, 6.79, 29.45
Tasks: 100 total, 1 running, 99 sleeping, 0 stopped, 0 zombie
Cpu(s): 13.3% us, 0.8% sy, 0.0% ni, 85.5% id, 0.3% wa, 0.0% hi, 0.0% si
Mem: 4148232k total, 970896k used, 3177336k free, 22660k buffers
Swap: 1052248k total, 0k used, 1052248k free, 786540k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
4131 www-data 16 0 54632 11m 7460 S 32.6 0.3 0:07.13 apache2
4125 www-data 15 0 54632 11m 7204 S 10.7 0.3 0:04.55 apache2
4124 www-data 15 0 54684 14m 9580 S 6.7 0.3 0:05.65 apache2
3957 mysql 10 -5 254m 57m 4504 S 4.0 1.4 0:37.82 mysqld
4141 www-data 16 0 54620 12m 7604 S 4.0 0.3 0:04.62 apache2
1 root 15 0 1592 520 456 S 0.0 0.0 0:01.75 init
2 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/0
I guess I need some tuning in the apache2 config?

stormblast-

Wed 13th Dec '06, 10:43am

Another change in apache2.conf:

KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 5
<IfModule prefork.c>
StartServers 15
MinSpareServers 15
MaxSpareServers 20
MaxClients 180
MaxRequestsPerChild 500
</IfModule>

Now I'm back to critical top stats:

top - 15:43:41 up 56 min, 1 user, load average: 45.98, 14.53, 19.12
Tasks: 203 total, 2 running, 201 sleeping, 0 stopped, 0 zombie
Cpu(s): 28.6% us, 14.4% sy, 0.0% ni, 56.6% id, 0.2% wa, 0.0% hi, 0.1% si
Mem: 4148232k total, 2218996k used, 1929236k free, 23756k buffers
Swap: 1052248k total, 0k used, 1052248k free, 865808k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
3957 mysql 10 -5 589m 325m 4532 S 99.9 8.0 3:37.96 mysqld
4365 www-data 15 0 54304 11m 6888 S 10.6 0.3 0:03.41 apache2
4555 www-data 16 0 60776 16m 6260 S 3.3 0.4 0:00.40 apache2
4650 www-data 21 0 61152 15m 5076 S 3.0 0.4 0:00.09 apache2
4277 www-data 16 0 60568 18m 8292 S 2.3 0.5 0:01.55 apache2
4534 www-data 25 0 59880 15m 6336 S 2.0 0.4 0:00.82 apache2
4557 www-data 17 0 60404 14m 5080 S 2.0 0.4 0:00.06 apache2
4315 www-data 16 0 60512 16m 6488 S 1.7 0.4 0:01.77 apache2
4528 www-data 17 0 59916 15m 5872 S 1.7 0.4 0:00.97 apache2
4538 www-data 25 0 60400 15m 5836 S 1.3 0.4 0:00.20 apache2
4560 www-data 16 0 59856 15m 6084 S 1.3 0.4 0:00.17 apache2
4370 www-data 15 0 54640 11m 7048 S 0.3 0.3 0:04.26 apache2
1 root 15 0 1592 520 456 S 0.0 0.0 0:01.75 init

jason|xoxide

Wed 13th Dec '06, 11:43am

In that first httpd conf file you posted you had two copies of the prefork config options. Delete the ones up top (except for the keep-alive lines) and then work off of the ones inside the "ifmodule" statement.

After that, try turning Keep-Alives off. Most vBulletin sites will run faster that way.

Next, you said you had the full-text search enabled. Do you also have boolean searches turned on? If so, try disabling it to see if the load goes away (your search results will be poor but don't worry about that for now). If it does, the only real way to get around that problem will be to get a second MySQL server and run a master/slave setup.

jason|xoxide

Wed 13th Dec '06, 11:46am

Also, why are you running an unreleased version of MySQL? You might try using mysqldump to backup your data, removing 5.0.30, reinstalling 5.0.27, and then restoring your data.

stormblast-

Wed 13th Dec '06, 12:14pm

I got this now in apache2.conf (no double prefork):

KeepAlive Off
MaxKeepAliveRequests 100
KeepAliveTimeout 5
<IfModule prefork.c>
StartServers 10
MinSpareServers 10
MaxSpareServers 15
MaxClients 60
MaxRequestsPerChild 60
</IfModule>
but my load is on 8.x and the server is lagging (currently 800 users)

The server is really loaded with very good hardware which should be enough in fact, I hope it's just a config issue..

jason|xoxide

Wed 13th Dec '06, 12:30pm

It's lagging because you're thrashing all your httpd processes by only allowing them to serve 60 requests before they quit. Change that to:

KeepAlive Off
MaxKeepAliveRequests 100
KeepAliveTimeout 5
<IfModule prefork.c>
StartServers 15
MinSpareServers 10
MaxSpareServers 20
MaxClients 256
MaxRequestsPerChild 512
</IfModule>After that, can you post the results from mysqlreport? Run it with the "--all" and "--tab" options (you'll probably also need "--password").

EDIT: mysqlreport and top are pretty useless if the server hasn't been running for a while. 12 hours is the absolute minimum I'd consider useful and 24 hours would be better (especially if that time included your peak forum usage).

stormblast-

Wed 13th Dec '06, 12:52pm

It's lagging because you're thrashing all your httpd processes by only allowing them to serve 60 requests before they quit. Change that to:

KeepAlive Off
MaxKeepAliveRequests 100
KeepAliveTimeout 5
<IfModule prefork.c>
StartServers 15
MinSpareServers 10
MaxSpareServers 20
MaxClients 256
MaxRequestsPerChild 512
</IfModule>

Hi there,

I changed apache2.conf to that now but here I'm experiencing the MySQL CPU Load of 99.9% problem..

Until now I were only able to keep MySQL low with the 60'ies settings, the load is then at ~5.x but the forum is more or less slow..

Edit: I have no chance with the above settings, MySQL stays at 99.9% CPU load and my load average is at 80+..

Edit2: I'm on 200+ average load now, shutdown apache2, but MySQL is still, 5 min after shutdown on 99.9% CPU load, load average still grows (230+)

Server version 5.0.30-Debian_0.dotdeb.1 (just using latest dotdeb)
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 8 min 2 sec

Threads: 154 Questions: 8185 Slow queries: 343 Opens: 732 Flush tables: 1 Open tables: 648 Queries per second avg: 16.981

Edit3: After 6 min the 99.9% of MySQL is finally gone..

Edit4: Restarted everything, back on MySQL 99.9%, load is going up..
Threads: 98 Questions: 17448 Slow queries: 529 Opens: 755 Flush tables: 1 Open tables: 671 Queries per second avg: 22.284

jason|xoxide

Wed 13th Dec '06, 1:20pm

Unless you've got a dozen or more plugins installed, the high load is almost certainly from the boolean full-text search. Have you tried disabling the boolean search to see if the load decreases to "normal" levels?

If that does help significantly, try adding the following to your my.cnf file and then rebuilding the full-text indicies (running a repair on your `post` and `thread` tables will do this). This will, if you're lucky, decrease the size of the index and make searching a bit faster. You can adjust the numbers up and down based on how large/small the words are that you want people to be able to search for (your settings are currently 4 and 84).

ft_min_word_len = 4
ft_max_word_len = 10

stormblast-

Wed 13th Dec '06, 1:25pm

Hi Jason,

thanks a lot for your efforts. In fact I'm running a very clean vBulletin atm.

I disabled almost every plugin and I disabled the SearchEngine completly. So at the moment that load doesn't come from fulltext search, I turned searching off in the morning already. However, I didn't shoot a repair so far, should I do it even if there's no searchengine running?

I assume all the index stuff is still there when I dumped the DB on one server and imported it on the other?

I'm really clueless what makes MySQL sticking to its 99.9% ..

jason|xoxide

Wed 13th Dec '06, 1:44pm

If the search is disabled and mysqld is still using that much processing power then don't bother with the repair to rebuild the indicies.

Can you open up mysql on the server and paste the output of the command below when the load is high?

SHOW PROCESSLIST;

EDIT: Oh, and index data isn't part of a sql dump, the indicies are rebuilt automatically when you import the sql file.

stormblast-

Wed 13th Dec '06, 1:47pm

stormblast-

Wed 13th Dec '06, 3:27pm

jason|xoxide

Wed 13th Dec '06, 4:47pm

Are you sure that you have the search disabled for all users (including the ones that let you search for posts by a member in the user profiles). The only times I've ever seen that many locked queries were when searches on a FT index were blocking updates.

Also, try adding the following to your my.cnf:

low_priority_updates = 1

stormblast-

Thu 14th Dec '06, 2:06am

Yes, search is completly disabled.
I added that line.

Right now everything is fine but just 400 users online now, if it goes up to 800 everything freaks out... I'm curious to which apache2 settings I should stick now, at the moment i'm using the 60'ies which (until now) made the server most stable but slow as soon as more users come up..

stormblast-

Thu 14th Dec '06, 4:42am

We just ordered a second server now for the database.. only problem is that the host will charge for traffic between those two dedicated servers, hope it won't be too much traffic between web and db server.... :o

eva2000

Sat 16th Dec '06, 10:02am

Nooooo use internal network link to two servers as bandwidth will be ALOT in terms of costs!

Hmmm have you tried MySQL 5.0.27 before using the enterprise 5.0.30 version ? what was previous mysql version on old server ?

1. Optional try downgrading to MySQL 5.0.27 and see how that works out
2. Downgrade to PHP 5.1.6 and upgrade to Apache 2.0.59 at least
3. Edit /etc/my.cnf and place the following mysql server settings in /etc/my.cnf and restart mysql server afterwards

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld]
bind-address = 127.0.0.1
user = mysql
port = 3306
skip-name-resolve
tmpdir = /tmp
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid
log-error = /var/log/mysql/mysqld.err
basedir = /usr
datadir = /var/lib/mysql
back_log = 150
skip-innodb
max_connections = 950
key_buffer = 192M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 4M
table_cache = 2500
thread_cache_size = 512
wait_timeout = 15
connect_timeout = 10
tmp_table_size = 192M
max_heap_table_size = 192M
max_allowed_packet = 96M
max_connect_errors = 10
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 8M
query_cache_limit = 6M
query_cache_size = 128M
query_cache_type = 1
query_prealloc_size = 65536
query_alloc_block_size = 131072
default-storage-engine = MyISAM

[mysqld_safe]
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

4. Edit httpd.conf values and change them from

KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 10
MinSpareServers 10
MaxSpareServers 15
StartServers 10
MaxClients 200
MaxRequestsPerChild 1000
Timeout 300

<IfModule prefork.c>
StartServers 5
MinSpareServers 5
MaxSpareServers 10
MaxClients 20
MaxRequestsPerChild 0
</IfModule>

to (remove lines in bold above)

KeepAlive On
MaxKeepAliveRequests 200
KeepAliveTimeout 5
Timeout 300

<IfModule prefork.c>
StartServers 20
MinSpareServers 20
MaxSpareServers 25
MaxClients 384
MaxRequestsPerChild 10000
</IfModule>

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?

Admin CP -> vBulletin Options -> Forum Display Options (forumdisplay) -> Show Users Browsing Forums

Admin CP -> vBulletin Options -> Thread Display Options -> Show Users Browsing Thread

Admin CP -> vBulletin Options -> Message Searching Options -> Automatic Similar Thread search

6. Might want to recompile a higher max open files limit as ulimit output shows you're max open files limit is at 1024, something like at least 4096 to 8192 would probably do.

stormblast-

Sat 16th Dec '06, 1:24pm

Thanks for the advice eva, I'll try it out right away. Can you give me a number on traffic when hosting such a big board and splitting db + www server without internal network link?

The www server has 8 TB of Traffic included and the DB server got unlimited Traffic.

It would cost additionally ~350€ per month if we put a managed switch in between those two servers that's why I'm asking.

However, seems like we won't make it without seperate database server..

Thanks and best regards,
Andreas