pokesph
Mon 4th Feb '08, 11:00pm
[Server Optimization]: Required info for optimization requests [January 30, 2007]
In order to help you with server problems and optimization, please provide the following info in your thread (courtesy of eva2000):
1. Is this on dedicated or shared virual server. dedicated
2. your server specs. For example:
cpu speed/type: dualcore xeon 2.4ghz (conroe 3060)
how much memory installed: 2GB ram
hard drive type/configuration: 250GB sata /non raid
linux distributor or windows version: Redhat Enterprise server 4 linux
apache/IIS version: Apache 2.2.8
PHP version: php 5.2.5
MySQL version: mysql 5.0.45
3. what version of vB are you running? 3.6.8 pl2
4. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server ? none in use
5. if possible how mysql was compiled/installed: built by cPanel's easyapache3 install script
6. your top stats:
Threads: 116,523, Posts: 3,536,695, Members: 44,776, Active Members: 10,077 max users: 995
top - 03:23:34 up 17 days, 2:58, 1 user, load average: 1.58, 1.44, 1.04
Tasks: 106 total, 2 running, 104 sleeping, 0 stopped, 0 zombie
Cpu(s): 17.7% us, 1.2% sy, 0.0% ni, 78.9% id, 2.2% wa, 0.0% hi, 0.0% si
Mem: 2074696k total, 1934020k used, 140676k free, 50468k buffers
Swap: 4192880k total, 116960k used, 4075920k free, 1226828k cached
PID USER PR NI %CPU TIME+ %MEM VIRT RES SHR S COMMAND
4019 nobody 16 0 35 13:11.05 13.2 2327m 267m 4876 S /usr/local/apache/bin/httpd -k start -DSSL
5634 mysql 15 0 2 224:47.85 8.0 222m 162m 4120 S /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=
4450 root 16 0 0 0:02.59 0.1 2560 1044 784 R top
1 root 16 0 0 0:02.48 0.0 2620 548 468 S init [3]
2 root RT 0 0 0:00.28 0.0 0 0 0 S [migration/0]
3 root 34 19 0 0:00.06 0.0 0 0 0 S [ksoftirqd/0]
4 root RT 0 0 0:00.24 0.0 0 0 0 S [migration/1]
5 root 34 19 0 0:00.03 0.0 0 0 0 S [ksoftirqd/1]
6 root 5 -10 0 0:00.00 0.0 0 0 0 S [events/0]
7 root 5 -10 0 0:00.00 0.0 0 0 0 S [events/1]
8 root 5 -10 0 0:00.00 0.0 0 0 0 S [khelper]
9 root 15 -10 0 0:00.00 0.0 0 0 0 S [kacpid]
34 root 5 -10 0 0:00.00 0.0 0 0 0 S [kblockd/0]
35 root 5 -10 0 0:00.00 0.0 0 0 0 S [kblockd/1]
36 root 15 0 0 0:00.00 0.0 0 0 0 S [khubd]
53 root 15 0 0 0:00.24 0.0 0 0 0 S [pdflush]
55 root 15 0 0 1:34.41 0.0 0 0 0 S [kswapd0]
56 root 14 -10 0 0:00.00 0.0 0 0 0 S [aio/0]
57 root 10 -10 0 0:00.00 0.0 0 0 0 S [aio/1]
201 root 25 0 0 0:00.00 0.0 0 0 0 S [kseriod]
430 root 5 -10 0 0:00.00 0.0 0 0 0 S [ata/0]
431 root 5 -10 0 0:00.00 0.0 0 0 0 S [ata/1]
432 root 8 -10 0 0:00.00 0.0 0 0 0 S [ata_aux]
436 root 15 0 0 0:00.00 0.0 0 0 0 S [scsi_eh_0]
437 root 15 0 0 0:00.00 0.0 0 0 0 S [scsi_eh_1]
461 root 16 0 0 0:13.12 1.4 32976 28m 2060 S spamd child
467 root 15 0 0 0:48.40 0.0 0 0 0 S [kjournald]
978 root 15 0 0 0:00.01 0.1 3656 1324 1056 S /usr/lib/courier-imap/bin/couriertls -server -tcpd /usr/lib/courier-imap/sbin/
979 pokecomm 16 0 0 0:00.04 0.0 4088 768 620 S /usr/lib/courier-imap/bin/imapd /home/pokecomm/mail/pokecommunity.com/webmaste
1125 root 6 -10 0 0:00.00 0.0 0 0 0 S [kauditd]
1234 root 16 0 0 0:02.97 1.4 32520 28m 2048 S spamd child
1937 root 6 -10 0 0:00.01 0.0 1968 452 368 S udevd
2258 root 15 0 0 0:00.00 0.0 0 0 0 S [kjournald]
2259 root 15 0 0 0:39.69 0.0 0 0 0 S [kjournald]
2260 root 15 0 0 0:00.42 0.0 0 0 0 S [kjournald]
2261 root 15 0 0 1:25.99 0.0 0 0 0 S [kjournald]
2934 root 16 0 0 0:00.81 0.2 6676 4204 1320 S cpbandwd
3048 mailnull 16 0 0 0:00.46 0.1 8960 2436 1588 S eximstats
3072 root 16 0 0 0:03.12 0.2 10920 3728 1100 S cPhulkd - processor
7. your mysql configuration variables located at /etc/my.cnf or c:\my.cnf or my.ini so post the contents inside of my.cnf (minus any passwords of course).
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
#old_passwords=1
key_buffer = 16M
key_buffer_size = 32M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 64
query_cache_limit = 8M
query_cache_size = 64M
query_cache_type = 1
join_buffer_size = 512K
max_connections = 250
skip-bdb
skip-innodb
old-passwords = 1
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql.server]
user=mysql
#basedir=/var/lib
old-passwords = 1
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
8. MySQL stats from ssh telnet as root user type:
Quote:
mysqladmin -u root -p var ext stat ver proc
+---------------------------------+-------------------------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| 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_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 | 5 |
| 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 | YES |
| have_compress | YES |
| have_crypt | YES |
| have_csv | YES |
| have_dynamic_loading | YES |
| have_example_engine | YES |
| have_federated_engine | YES |
| have_geometry | YES |
| have_innodb | DISABLED |
| have_isam | NO |
| have_merge_engine | YES |
| have_ndbcluster | DISABLED |
| have_openssl | NO |
| have_ssl | NO |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| hostname | srv2.pokecommunity.com |
| 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_rollback_on_timeout | OFF |
| 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 | 520192 |
| 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 |
| 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 | |
| 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 | 16776192 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 250 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| 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 | 8388608 |
| myisam_stats_method | nulls_unequal |
| ndb_autoincrement_prefetch_sz | 32 |
| ndb_force_send | ON |
| ndb_use_exact_count | ON |
| ndb_use_transactions | ON |
| ndb_cache_check_time | 0 |
| ndb_connectstring | |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | ON |
| open_files_limit | 1250 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /var/lib/mysql/srv2.pokecommunity.com.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| profiling | OFF |
| profiling_history_size | 15 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 8388608 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 67108864 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| secure_file_priv | |
| 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/lib/mysql/mysql.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 | GMT |
| table_cache | 64 |
| table_lock_wait_timeout | 50 |
| table_type | MyISAM |
| thread_cache_size | 64 |
| thread_stack | 131072 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmp_table_size | 33554432 |
| tmpdir | /tmp/ |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| updatable_views_with_limit | YES |
| version | 5.0.45-community |
| version_comment | MySQL Community Edition (GPL) |
| version_compile_machine | i686 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 28800 |
+---------------------------------+-------------------------------------------+
+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Aborted_clients | 1070 |
| Aborted_connects | 106 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 40405549 |
| Bytes_sent | 154809899 |
| Com_admin_commands | 1 |
| Com_alter_db | 0 |
| Com_alter_table | 9 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 750549 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 4 |
| Com_create_function | 0 |
| Com_create_index | 4 |
| Com_create_table | 239 |
| Com_create_user | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 98299 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 2 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 232 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 5 |
| Com_grant | 5 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 494464 |
| Com_insert_select | 2902 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 13641 |
| Com_optimize | 6 |
| 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 | 113523 |
| 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 | 4022082 |
| Com_set_option | 9038 |
| 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 | 124 |
| Com_show_databases | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 2625 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 16 |
| 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 | 1450 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 59 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 2 |
| Com_show_triggers | 124 |
| Com_show_variables | 92 |
| 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 | 232 |
| Com_unlock_tables | 13641 |
| Com_update | 1054217 |
| Com_update_multi | 258 |
| 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 | 761806 |
| Created_tmp_disk_tables | 16295 |
| Created_tmp_files | 730 |
| Created_tmp_tables | 235939 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 741367 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 442973 |
| Handler_read_key | 278582905 |
| Handler_read_next | 707824582 |
| Handler_read_prev | 4249384 |
| Handler_read_rnd | 109850684 |
| Handler_read_rnd_next | 2187188919 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 2618881 |
| Handler_write | 52549626 |
| 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 | 0 |
| Key_blocks_used | 28995 |
| Key_read_requests | 977919281 |
| Key_reads | 10129426 |
| Key_write_requests | 3257537 |
| Key_writes | 1729070 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 251 |
| 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 | 110 |
| Open_streams | 0 |
| Open_tables | 64 |
| Opened_tables | 303566 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 2542 |
| Qcache_free_memory | 20370976 |
| Qcache_hits | 4069010 |
| Qcache_inserts | 3994490 |
| Qcache_lowmem_prunes | 67652 |
| Qcache_not_cached | 25903 |
| Qcache_queries_in_cache | 2595 |
| Qcache_total_blocks | 9834 |
| Questions | 11407138 |
| Rpl_status | NULL |
| Select_full_join | 1588 |
| Select_full_range_join | 0 |
| Select_range | 1017781 |
| Select_range_check | 0 |
| Select_scan | 806662 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 3283 |
| Sort_merge_passes | 381 |
| Sort_range | 903360 |
| Sort_rows | 314842615 |
| Sort_scan | 317710 |
| Table_locks_immediate | 11791639 |
| Table_locks_waited | 79462 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 58 |
| Threads_connected | 8 |
| Threads_created | 1270 |
| Threads_running | 1 |
| Uptime | 417231 |
| Uptime_since_flush_status | 417231 |
+-----------------------------------+------------+
Uptime: 417285 Threads: 3 Questions: 11409187 Slow queries: 3283 Opens: 303570 Flush tables: 1 Open tables: 64 Queries per second av
g: 27.341
mysqladmin Ver 8.41 Distrib 5.0.45, for pc-linux-gnu on i686
Copyright (C) 2000-2006 MySQL 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.45-community
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 4 days 19 hours 54 min 45 sec
Threads: 3 Questions: 11409187 Slow queries: 3283 Opens: 303570 Flush tables: 1 Open tables: 64 Queries per second avg: 27.341
+--------+--------------+-----------+--------------+---------+-------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+--------------+-----------+--------------+---------+-------+-------+------------------+
| 129983 | eximstats | localhost | eximstats | Sleep | 348 | | |
| 724284 | leechprotect | localhost | leechprotect | Sleep | 15003 | | |
| 761732 | root | localhost | | Query | 0 | | show processlist |
+--------+--------------+-----------+--------------+---------+-------+-------+------------------+
Also if you want to better see what's going on install mysqlreport at http://www.vbulletin.com/forum/showthread.php?t=175177 and run it by typing
Quote:
mysqlreport --all -tab /statistics.txt
in ssh telnet and then copy and paste the output you see or look into statistics.txt file located at root directory /
MySQL 5.0.45-community uptime 4 20:23:35 Tue Feb 5 02:42:16 2008
__ Key __________________________________________________ _______________
Buffer used 28.32M of 32.00M %Used: 88.49
Current 32.00M %Usage: 100.00
Write hit 46.98%
Read hit 98.96%
__ Questions __________________________________________________ _________
Total 11.47M 27.4/s
DMS 5.82M 13.9/s %Total: 50.72
QC Hits 4.09M 9.8/s 35.67
Com_ 796.40k 1.9/s 6.94
COM_QUIT 765.52k 1.8/s 6.67
-Unknown 983 0.0/s 0.01
Slow (10) 3.29k 0.0/s 0.03 %DMS: 0.06 Log: OFF
DMS 5.82M 13.9/s 50.72
SELECT 4.05M 9.7/s 35.26 69.52
UPDATE 1.06M 2.5/s 9.25 18.23
INSERT 499.84k 1.2/s 4.36 8.59
REPLACE 114.15k 0.3/s 1.00 1.96
DELETE 98.92k 0.2/s 0.86 1.70
Com_ 796.40k 1.9/s 6.94
change_db 754.73k 1.8/s 6.58
lock_tables 13.66k 0.0/s 0.12
unlock_tabl 13.66k 0.0/s 0.12
__ SELECT and Sort __________________________________________________ ___
Scan 811.39k 1.9/s %SELECT: 20.06
Range 1.02M 2.4/s 25.30
Full join 1.62k 0.0/s 0.04
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 319.56k 0.8/s
Sort range 908.34k 2.2/s
Sort mrg pass 384 0.0/s
__ Query Cache __________________________________________________ _______
Memory usage 46.38M of 64.00M %Used: 72.46
Block Fragmnt 24.84%
Hits 4.09M 9.8/s
Inserts 4.02M 9.6/s
Insrt:Prune 59.16:1 9.4/s
Hit:Insert 1.02:1
__ Table Locks __________________________________________________ _______
Waited 79.73k 0.2/s %Total: 0.67
Immediate 11.86M 28.3/s
__ Tables __________________________________________________ ____________
Open 64 of 64 %Cache: 100.00
Opened 304.77k 0.7/s
__ Connections __________________________________________________ _______
Max used 251 of 250 %Max: 100.40
Total 766.05k 1.8/s
__ Created Temp __________________________________________________ ______
Disk table 16.39k 0.0/s
Table 237.31k 0.6/s Size: 32.0M
File 736 0.0/s
__ Threads __________________________________________________ ___________
Running 1 of 3
Cached 63 of 64 %Hit: 99.83
Created 1.27k 0.0/s
Slow 0 0/s
__ Aborted __________________________________________________ ___________
Clients 1.07k 0.0/s
Connects 106 0.0/s
__ Bytes __________________________________________________ _____________
Sent 2.44G 5.8k/s
Received 64.23M 153.3/s
__ InnoDB Buffer Pool __________________________________________________
Usage 0 of 0 %Used: 0.00
Read hit 0.00%
Pages
Free 0 %Total: 0.00
Data 0 0.00 %Drty: 0.00
Misc 0 0.00
Latched 0 0.00
Reads 0 0/s
From file 0 0/s 0.00
Ahead Rnd 0 0/s
Ahead Sql 0 0/s
Writes 0 0/s
Flushes 0 0/s
Wait Free 0 0/s
__ InnoDB Lock __________________________________________________ _______
Waits 0 0/s
Current 0
Time acquiring
Total 0 ms
Average 0 ms
Max 0 ms
__ InnoDB Data, Pages, Rows ____________________________________________
Data
Reads 0 0/s
Writes 0 0/s
fsync 0 0/s
Pending
Reads 0
Writes 0
fsync 0
Pages
Created 0 0/s
Read 0 0/s
Written 0 0/s
Rows
Deleted 0 0/s
Inserted 0 0/s
Read 0 0/s
Updated 0 0/s
9. is your vB the only thing on the server? vB is only thing on this server currently
10. how many average and max concurrent users on your vB forum ? avg: 400 and max: 900+ and what your cookie timeout is? 900 secs
11. create a file named phpinfo.php and place this code in it and post the url/link to it from your web site: www.pokecommunity.com/phpinfo.php
12. if you run Apache and you have your own dedicated server or access to your httpd.conf (apache configuration file) can you post the values you have set for the following :
KeepAlive
MaxKeepAliveRequests
KeepAliveTimeout
MinSpareServers
MaxSpareServers
StartServers
MaxClients
AND Maxrequestsperchild value =
apache 2.2.x with mpm_worker:
ServerLimit 21
ThreadLimit 255
StartServers 2
MaxClients 500
MinSpareThreads 10
MaxSpareThreads 50
ThreadsPerChild 250
the following are not set in httpd.conf (or use apache's default):
KeepAlive
MaxKeepAliveRequests
KeepAliveTimeout
MaxClients
13. check to see if any files i.e. apache log files are hitting 2GB or 4GB max file size limits i.e. see if you have max file size exceeded messages in apache error log
find / -size +2000000k
none
14. Post output from these 3 commands
uname -a:
[root@srv2 ~]# uname -a
Linux srv2.pokecommunity.com 2.6.9-67.0.1.ELsmp #1 SMP Fri Nov 30 11:51:05 EST 2007 i686 i686 i386 GNU/Linux
ulimit -aH:
[root@srv2 ~]# ulimit -aH
core file size (blocks, -c) 1000000
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) 4096
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 14335
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
cat /proc/cpuinfo:
[root@srv2 ~]# cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Xeon(R) CPU 3060 @ 2.40GHz
stepping : 6
cpu MHz : 2400.109
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 est tm2 xtpr
bogomips : 4802.97
processor : 1
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Xeon(R) CPU 3060 @ 2.40GHz
stepping : 6
cpu MHz : 2400.109
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 est tm2 xtpr
bogomips : 4800.20
In order to help you with server problems and optimization, please provide the following info in your thread (courtesy of eva2000):
1. Is this on dedicated or shared virual server. dedicated
2. your server specs. For example:
cpu speed/type: dualcore xeon 2.4ghz (conroe 3060)
how much memory installed: 2GB ram
hard drive type/configuration: 250GB sata /non raid
linux distributor or windows version: Redhat Enterprise server 4 linux
apache/IIS version: Apache 2.2.8
PHP version: php 5.2.5
MySQL version: mysql 5.0.45
3. what version of vB are you running? 3.6.8 pl2
4. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server ? none in use
5. if possible how mysql was compiled/installed: built by cPanel's easyapache3 install script
6. your top stats:
Threads: 116,523, Posts: 3,536,695, Members: 44,776, Active Members: 10,077 max users: 995
top - 03:23:34 up 17 days, 2:58, 1 user, load average: 1.58, 1.44, 1.04
Tasks: 106 total, 2 running, 104 sleeping, 0 stopped, 0 zombie
Cpu(s): 17.7% us, 1.2% sy, 0.0% ni, 78.9% id, 2.2% wa, 0.0% hi, 0.0% si
Mem: 2074696k total, 1934020k used, 140676k free, 50468k buffers
Swap: 4192880k total, 116960k used, 4075920k free, 1226828k cached
PID USER PR NI %CPU TIME+ %MEM VIRT RES SHR S COMMAND
4019 nobody 16 0 35 13:11.05 13.2 2327m 267m 4876 S /usr/local/apache/bin/httpd -k start -DSSL
5634 mysql 15 0 2 224:47.85 8.0 222m 162m 4120 S /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=
4450 root 16 0 0 0:02.59 0.1 2560 1044 784 R top
1 root 16 0 0 0:02.48 0.0 2620 548 468 S init [3]
2 root RT 0 0 0:00.28 0.0 0 0 0 S [migration/0]
3 root 34 19 0 0:00.06 0.0 0 0 0 S [ksoftirqd/0]
4 root RT 0 0 0:00.24 0.0 0 0 0 S [migration/1]
5 root 34 19 0 0:00.03 0.0 0 0 0 S [ksoftirqd/1]
6 root 5 -10 0 0:00.00 0.0 0 0 0 S [events/0]
7 root 5 -10 0 0:00.00 0.0 0 0 0 S [events/1]
8 root 5 -10 0 0:00.00 0.0 0 0 0 S [khelper]
9 root 15 -10 0 0:00.00 0.0 0 0 0 S [kacpid]
34 root 5 -10 0 0:00.00 0.0 0 0 0 S [kblockd/0]
35 root 5 -10 0 0:00.00 0.0 0 0 0 S [kblockd/1]
36 root 15 0 0 0:00.00 0.0 0 0 0 S [khubd]
53 root 15 0 0 0:00.24 0.0 0 0 0 S [pdflush]
55 root 15 0 0 1:34.41 0.0 0 0 0 S [kswapd0]
56 root 14 -10 0 0:00.00 0.0 0 0 0 S [aio/0]
57 root 10 -10 0 0:00.00 0.0 0 0 0 S [aio/1]
201 root 25 0 0 0:00.00 0.0 0 0 0 S [kseriod]
430 root 5 -10 0 0:00.00 0.0 0 0 0 S [ata/0]
431 root 5 -10 0 0:00.00 0.0 0 0 0 S [ata/1]
432 root 8 -10 0 0:00.00 0.0 0 0 0 S [ata_aux]
436 root 15 0 0 0:00.00 0.0 0 0 0 S [scsi_eh_0]
437 root 15 0 0 0:00.00 0.0 0 0 0 S [scsi_eh_1]
461 root 16 0 0 0:13.12 1.4 32976 28m 2060 S spamd child
467 root 15 0 0 0:48.40 0.0 0 0 0 S [kjournald]
978 root 15 0 0 0:00.01 0.1 3656 1324 1056 S /usr/lib/courier-imap/bin/couriertls -server -tcpd /usr/lib/courier-imap/sbin/
979 pokecomm 16 0 0 0:00.04 0.0 4088 768 620 S /usr/lib/courier-imap/bin/imapd /home/pokecomm/mail/pokecommunity.com/webmaste
1125 root 6 -10 0 0:00.00 0.0 0 0 0 S [kauditd]
1234 root 16 0 0 0:02.97 1.4 32520 28m 2048 S spamd child
1937 root 6 -10 0 0:00.01 0.0 1968 452 368 S udevd
2258 root 15 0 0 0:00.00 0.0 0 0 0 S [kjournald]
2259 root 15 0 0 0:39.69 0.0 0 0 0 S [kjournald]
2260 root 15 0 0 0:00.42 0.0 0 0 0 S [kjournald]
2261 root 15 0 0 1:25.99 0.0 0 0 0 S [kjournald]
2934 root 16 0 0 0:00.81 0.2 6676 4204 1320 S cpbandwd
3048 mailnull 16 0 0 0:00.46 0.1 8960 2436 1588 S eximstats
3072 root 16 0 0 0:03.12 0.2 10920 3728 1100 S cPhulkd - processor
7. your mysql configuration variables located at /etc/my.cnf or c:\my.cnf or my.ini so post the contents inside of my.cnf (minus any passwords of course).
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
#old_passwords=1
key_buffer = 16M
key_buffer_size = 32M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 64
query_cache_limit = 8M
query_cache_size = 64M
query_cache_type = 1
join_buffer_size = 512K
max_connections = 250
skip-bdb
skip-innodb
old-passwords = 1
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql.server]
user=mysql
#basedir=/var/lib
old-passwords = 1
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
8. MySQL stats from ssh telnet as root user type:
Quote:
mysqladmin -u root -p var ext stat ver proc
+---------------------------------+-------------------------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| 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_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 | 5 |
| 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 | YES |
| have_compress | YES |
| have_crypt | YES |
| have_csv | YES |
| have_dynamic_loading | YES |
| have_example_engine | YES |
| have_federated_engine | YES |
| have_geometry | YES |
| have_innodb | DISABLED |
| have_isam | NO |
| have_merge_engine | YES |
| have_ndbcluster | DISABLED |
| have_openssl | NO |
| have_ssl | NO |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| hostname | srv2.pokecommunity.com |
| 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_rollback_on_timeout | OFF |
| 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 | 520192 |
| 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 |
| 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 | |
| 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 | 16776192 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 250 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| 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 | 8388608 |
| myisam_stats_method | nulls_unequal |
| ndb_autoincrement_prefetch_sz | 32 |
| ndb_force_send | ON |
| ndb_use_exact_count | ON |
| ndb_use_transactions | ON |
| ndb_cache_check_time | 0 |
| ndb_connectstring | |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | ON |
| open_files_limit | 1250 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /var/lib/mysql/srv2.pokecommunity.com.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| profiling | OFF |
| profiling_history_size | 15 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 8388608 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 67108864 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| secure_file_priv | |
| 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/lib/mysql/mysql.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 | GMT |
| table_cache | 64 |
| table_lock_wait_timeout | 50 |
| table_type | MyISAM |
| thread_cache_size | 64 |
| thread_stack | 131072 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmp_table_size | 33554432 |
| tmpdir | /tmp/ |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| updatable_views_with_limit | YES |
| version | 5.0.45-community |
| version_comment | MySQL Community Edition (GPL) |
| version_compile_machine | i686 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 28800 |
+---------------------------------+-------------------------------------------+
+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Aborted_clients | 1070 |
| Aborted_connects | 106 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 40405549 |
| Bytes_sent | 154809899 |
| Com_admin_commands | 1 |
| Com_alter_db | 0 |
| Com_alter_table | 9 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 750549 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 4 |
| Com_create_function | 0 |
| Com_create_index | 4 |
| Com_create_table | 239 |
| Com_create_user | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 98299 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 2 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 232 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 5 |
| Com_grant | 5 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 494464 |
| Com_insert_select | 2902 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 13641 |
| Com_optimize | 6 |
| 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 | 113523 |
| 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 | 4022082 |
| Com_set_option | 9038 |
| 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 | 124 |
| Com_show_databases | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 2625 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 16 |
| 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 | 1450 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 59 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 2 |
| Com_show_triggers | 124 |
| Com_show_variables | 92 |
| 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 | 232 |
| Com_unlock_tables | 13641 |
| Com_update | 1054217 |
| Com_update_multi | 258 |
| 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 | 761806 |
| Created_tmp_disk_tables | 16295 |
| Created_tmp_files | 730 |
| Created_tmp_tables | 235939 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 741367 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 442973 |
| Handler_read_key | 278582905 |
| Handler_read_next | 707824582 |
| Handler_read_prev | 4249384 |
| Handler_read_rnd | 109850684 |
| Handler_read_rnd_next | 2187188919 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 2618881 |
| Handler_write | 52549626 |
| 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 | 0 |
| Key_blocks_used | 28995 |
| Key_read_requests | 977919281 |
| Key_reads | 10129426 |
| Key_write_requests | 3257537 |
| Key_writes | 1729070 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 251 |
| 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 | 110 |
| Open_streams | 0 |
| Open_tables | 64 |
| Opened_tables | 303566 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 2542 |
| Qcache_free_memory | 20370976 |
| Qcache_hits | 4069010 |
| Qcache_inserts | 3994490 |
| Qcache_lowmem_prunes | 67652 |
| Qcache_not_cached | 25903 |
| Qcache_queries_in_cache | 2595 |
| Qcache_total_blocks | 9834 |
| Questions | 11407138 |
| Rpl_status | NULL |
| Select_full_join | 1588 |
| Select_full_range_join | 0 |
| Select_range | 1017781 |
| Select_range_check | 0 |
| Select_scan | 806662 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 3283 |
| Sort_merge_passes | 381 |
| Sort_range | 903360 |
| Sort_rows | 314842615 |
| Sort_scan | 317710 |
| Table_locks_immediate | 11791639 |
| Table_locks_waited | 79462 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 58 |
| Threads_connected | 8 |
| Threads_created | 1270 |
| Threads_running | 1 |
| Uptime | 417231 |
| Uptime_since_flush_status | 417231 |
+-----------------------------------+------------+
Uptime: 417285 Threads: 3 Questions: 11409187 Slow queries: 3283 Opens: 303570 Flush tables: 1 Open tables: 64 Queries per second av
g: 27.341
mysqladmin Ver 8.41 Distrib 5.0.45, for pc-linux-gnu on i686
Copyright (C) 2000-2006 MySQL 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.45-community
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 4 days 19 hours 54 min 45 sec
Threads: 3 Questions: 11409187 Slow queries: 3283 Opens: 303570 Flush tables: 1 Open tables: 64 Queries per second avg: 27.341
+--------+--------------+-----------+--------------+---------+-------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+--------------+-----------+--------------+---------+-------+-------+------------------+
| 129983 | eximstats | localhost | eximstats | Sleep | 348 | | |
| 724284 | leechprotect | localhost | leechprotect | Sleep | 15003 | | |
| 761732 | root | localhost | | Query | 0 | | show processlist |
+--------+--------------+-----------+--------------+---------+-------+-------+------------------+
Also if you want to better see what's going on install mysqlreport at http://www.vbulletin.com/forum/showthread.php?t=175177 and run it by typing
Quote:
mysqlreport --all -tab /statistics.txt
in ssh telnet and then copy and paste the output you see or look into statistics.txt file located at root directory /
MySQL 5.0.45-community uptime 4 20:23:35 Tue Feb 5 02:42:16 2008
__ Key __________________________________________________ _______________
Buffer used 28.32M of 32.00M %Used: 88.49
Current 32.00M %Usage: 100.00
Write hit 46.98%
Read hit 98.96%
__ Questions __________________________________________________ _________
Total 11.47M 27.4/s
DMS 5.82M 13.9/s %Total: 50.72
QC Hits 4.09M 9.8/s 35.67
Com_ 796.40k 1.9/s 6.94
COM_QUIT 765.52k 1.8/s 6.67
-Unknown 983 0.0/s 0.01
Slow (10) 3.29k 0.0/s 0.03 %DMS: 0.06 Log: OFF
DMS 5.82M 13.9/s 50.72
SELECT 4.05M 9.7/s 35.26 69.52
UPDATE 1.06M 2.5/s 9.25 18.23
INSERT 499.84k 1.2/s 4.36 8.59
REPLACE 114.15k 0.3/s 1.00 1.96
DELETE 98.92k 0.2/s 0.86 1.70
Com_ 796.40k 1.9/s 6.94
change_db 754.73k 1.8/s 6.58
lock_tables 13.66k 0.0/s 0.12
unlock_tabl 13.66k 0.0/s 0.12
__ SELECT and Sort __________________________________________________ ___
Scan 811.39k 1.9/s %SELECT: 20.06
Range 1.02M 2.4/s 25.30
Full join 1.62k 0.0/s 0.04
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 319.56k 0.8/s
Sort range 908.34k 2.2/s
Sort mrg pass 384 0.0/s
__ Query Cache __________________________________________________ _______
Memory usage 46.38M of 64.00M %Used: 72.46
Block Fragmnt 24.84%
Hits 4.09M 9.8/s
Inserts 4.02M 9.6/s
Insrt:Prune 59.16:1 9.4/s
Hit:Insert 1.02:1
__ Table Locks __________________________________________________ _______
Waited 79.73k 0.2/s %Total: 0.67
Immediate 11.86M 28.3/s
__ Tables __________________________________________________ ____________
Open 64 of 64 %Cache: 100.00
Opened 304.77k 0.7/s
__ Connections __________________________________________________ _______
Max used 251 of 250 %Max: 100.40
Total 766.05k 1.8/s
__ Created Temp __________________________________________________ ______
Disk table 16.39k 0.0/s
Table 237.31k 0.6/s Size: 32.0M
File 736 0.0/s
__ Threads __________________________________________________ ___________
Running 1 of 3
Cached 63 of 64 %Hit: 99.83
Created 1.27k 0.0/s
Slow 0 0/s
__ Aborted __________________________________________________ ___________
Clients 1.07k 0.0/s
Connects 106 0.0/s
__ Bytes __________________________________________________ _____________
Sent 2.44G 5.8k/s
Received 64.23M 153.3/s
__ InnoDB Buffer Pool __________________________________________________
Usage 0 of 0 %Used: 0.00
Read hit 0.00%
Pages
Free 0 %Total: 0.00
Data 0 0.00 %Drty: 0.00
Misc 0 0.00
Latched 0 0.00
Reads 0 0/s
From file 0 0/s 0.00
Ahead Rnd 0 0/s
Ahead Sql 0 0/s
Writes 0 0/s
Flushes 0 0/s
Wait Free 0 0/s
__ InnoDB Lock __________________________________________________ _______
Waits 0 0/s
Current 0
Time acquiring
Total 0 ms
Average 0 ms
Max 0 ms
__ InnoDB Data, Pages, Rows ____________________________________________
Data
Reads 0 0/s
Writes 0 0/s
fsync 0 0/s
Pending
Reads 0
Writes 0
fsync 0
Pages
Created 0 0/s
Read 0 0/s
Written 0 0/s
Rows
Deleted 0 0/s
Inserted 0 0/s
Read 0 0/s
Updated 0 0/s
9. is your vB the only thing on the server? vB is only thing on this server currently
10. how many average and max concurrent users on your vB forum ? avg: 400 and max: 900+ and what your cookie timeout is? 900 secs
11. create a file named phpinfo.php and place this code in it and post the url/link to it from your web site: www.pokecommunity.com/phpinfo.php
12. if you run Apache and you have your own dedicated server or access to your httpd.conf (apache configuration file) can you post the values you have set for the following :
KeepAlive
MaxKeepAliveRequests
KeepAliveTimeout
MinSpareServers
MaxSpareServers
StartServers
MaxClients
AND Maxrequestsperchild value =
apache 2.2.x with mpm_worker:
ServerLimit 21
ThreadLimit 255
StartServers 2
MaxClients 500
MinSpareThreads 10
MaxSpareThreads 50
ThreadsPerChild 250
the following are not set in httpd.conf (or use apache's default):
KeepAlive
MaxKeepAliveRequests
KeepAliveTimeout
MaxClients
13. check to see if any files i.e. apache log files are hitting 2GB or 4GB max file size limits i.e. see if you have max file size exceeded messages in apache error log
find / -size +2000000k
none
14. Post output from these 3 commands
uname -a:
[root@srv2 ~]# uname -a
Linux srv2.pokecommunity.com 2.6.9-67.0.1.ELsmp #1 SMP Fri Nov 30 11:51:05 EST 2007 i686 i686 i386 GNU/Linux
ulimit -aH:
[root@srv2 ~]# ulimit -aH
core file size (blocks, -c) 1000000
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) 4096
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 14335
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
cat /proc/cpuinfo:
[root@srv2 ~]# cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Xeon(R) CPU 3060 @ 2.40GHz
stepping : 6
cpu MHz : 2400.109
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 est tm2 xtpr
bogomips : 4802.97
processor : 1
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Xeon(R) CPU 3060 @ 2.40GHz
stepping : 6
cpu MHz : 2400.109
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 est tm2 xtpr
bogomips : 4800.20