dbembibre
Tue 26th Jun '07, 11:49am
1 - Dedicated Server
2 - Dell PowerEdge 1950 Dual Dual Xeon 5130 @ 2GHZ
4GB of RAM 2 X 160 GB SAS discs
SO: Centos 4.5
Apache 2.0.52, php 5.2.3, MySQL 5.0.42, xCache 1.2.1RC1 and Zend Optimizer 3.2.8
3 - vBulletin 3.6.7PL1 + VBSEO
4 - No innodb tables
5 - MySQL was rebuilded from Jason|xoxide source rpm, php was compiled by hand and recompiled every time that i rebuild MySQL, i have xCache compiled by hand with Zend Optimizer for VBSeo.
I test memcached in a vmware enviroment with good results and planning add it to my server.
Another disabled 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 -
top - 16:16:26 up 18 days, 19:29, 1 user, load average: 0.91, 0.76, 0.81
Tasks: 173 total, 1 running, 171 sleeping, 0 stopped, 1 zombie
Cpu(s): 7.4% us, 1.1% sy, 0.0% ni, 91.5% id, 0.0% wa, 0.0% hi, 0.0% si
Mem: 4149124k total, 3918016k used, 231108k free, 55320k buffers
Swap: 2096472k total, 38656k used, 2057816k free, 2980008k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
5627 apache 15 0 67004 18m 7452 S 9 0.5 0:00.43 httpd
5652 apache 15 0 67088 18m 7120 S 9 0.5 0:00.44 httpd
5665 apache 15 0 72744 23m 7632 S 8 0.6 0:00.70 httpd
15385 mysql 10 -5 477m 392m 4484 S 2 9.7 1113:57 mysqld
5427 apache 16 0 67040 18m 7852 S 2 0.5 0:01.72 httpd
5439 apache 16 0 67048 19m 8428 S 1 0.5 0:02.91 httpd
5674 apache 15 0 0 0 0 Z 1 0.0 0:00.36 httpd <defunct>
5360 apache 15 0 67096 20m 9352 S 0 0.5 0:02.79 httpd
5550 apache 15 0 67116 19m 8284 S 0 0.5 0:01.59 httpd
5600 apache 15 0 67004 18m 7048 S 0 0.4 0:00.26 httpd
5670 apache 15 0 66984 18m 7208 S 0 0.5 0:00.33 httpd
5676 apache 16 0 67036 18m 7808 S 0 0.5 0:00.21 httpd
1 root 16 0 1764 512 436 S 0 0.0 0:01.77 init
2 root RT 0 0 0 0 S 0 0.0 0:00.72 migration/0
3 root 34 19 0 0 0 S 0 0.0 0:01.83 ksoftirqd/0
4 root RT 0 0 0 0 S 0 0.0 0:00.55 migration/1
7 -
[daniel@srv01 ~]$ cat /etc/my.cnf
[mysql]
socket =/var/lib/mysql/mysql.sock
[mysqld]
set-variable=local-infile=0
user=mysql
socket = /var/lib/mysql/mysql.sock
datadir=/var/lib/mysql
#tmpdir = /mnt/sdb1/tmp
safe-show-database
old_passwords
back_log = 75
skip-innodb
skip-networking
max_connections = 650
key_buffer = 192M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 2000
thread_cache_size = 384
wait_timeout = 30
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
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
[mysql.server]
user=mysql
basedir=/mnt/sdb1/mysql
pid-file=/var/run/mysqld/mysqld.pid
[mysqladmin]
socket=/var/lib/mysql/mysql.sock
[mysqld_safe]
err-log=/var/log/mysqld.log
open_files_limit = 8192
nice = -5
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer=256M
sort_buffer=256M
read_buffer=2M
write_buffer=2M
[mysqlhotcopy]
interactive-timeout
8 -
+---------------------------------+--------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 75 |
| basedir | /usr/ |
| bdb_cache_size | 8388600 |
| bdb_home | /var/lib/mysql/ |
| bdb_log_buffer_size | 1024000 |
| bdb_logdir | |
| bdb_max_lock | 10000 |
| bdb_shared_data | OFF |
| bdb_tmpdir | /tmp/ |
| 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 | NO |
| have_bdb | YES |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | YES |
| have_csv | NO |
| have_dynamic_loading | YES |
| have_example_engine | NO |
| have_federated_engine | NO |
| have_geometry | YES |
| have_innodb | DISABLED |
| have_isam | NO |
| have_merge_engine | YES |
| have_ndbcluster | NO |
| have_openssl | DISABLED |
| have_ssl | DISABLED |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| hostname | srv01.bmwfaq.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 | 1044480 |
| key_buffer_size | 201326592 |
| 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 | OFF |
| 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 | 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 | 67108864 |
| max_insert_delayed_threads | 20 |
| max_join_size | 4294967295 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_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 |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | ON |
| open_files_limit | 4660 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /var/run/mysqld/mysqld.pid |
| port | 0 |
| preload_buffer_size | 32768 |
| 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 |
| secure_file_priv | |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | ON |
| 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 | CEST |
| table_cache | 2000 |
| table_lock_wait_timeout | 50 |
| table_type | MyISAM |
| thread_cache_size | 384 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmp_table_size | 67108864 |
| tmpdir | /tmp/ |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| updatable_views_with_limit | YES |
| version | 5.0.42 |
| version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (May 14, 2007) |
| version_comment | Source distribution |
| version_compile_machine | i686 |
| version_compile_os | redhat-linux-gnu |
| wait_timeout | 30 |
+---------------------------------+--------------------------------------------------------+
+-----------------------------------+-------------+
| Variable_name | Value |
+-----------------------------------+-------------+
| Aborted_clients | 6164 |
| Aborted_connects | 2925 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 4145121837 |
| Bytes_sent | 737844850 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 11719 |
| Com_change_db | 4350575 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 11719 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 490 |
| Com_create_user | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 207636 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 490 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 40 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 1815756 |
| Com_insert_select | 490 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 102 |
| Com_optimize | 2659 |
| 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 | 110907 |
| Com_replace_select | 10 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 20581565 |
| Com_set_option | 151207 |
| 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 | 30 |
| Com_show_create_table | 3060 |
| Com_show_databases | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 3060 |
| 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 | 13 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 850 |
| Com_show_triggers | 3060 |
| Com_show_variables | 73828 |
| 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 | 490 |
| Com_unlock_tables | 102 |
| Com_update | 6717156 |
| Com_update_multi | 490 |
| 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 | 5223672 |
| Created_tmp_disk_tables | 1596199 |
| Created_tmp_files | 18848 |
| Created_tmp_tables | 1923908 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 637129 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 216080 |
| Handler_read_key | 1057069496 |
| Handler_read_next | 1668620008 |
| Handler_read_prev | 6724810 |
| Handler_read_rnd | 337651803 |
| Handler_read_rnd_next | 3333884524 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 7302210 |
| Handler_write | 137342582 |
| 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 | 174450 |
| Key_read_requests | 10612406249 |
| Key_reads | 17954962 |
| Key_write_requests | 9153642 |
| Key_writes | 7226925 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 328 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 1072 |
| Open_streams | 0 |
| Open_tables | 932 |
| Opened_tables | 26126 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 5824 |
| Qcache_free_memory | 13621288 |
| Qcache_hits | 24913400 |
| Qcache_inserts | 20393798 |
| Qcache_lowmem_prunes | 1975837 |
| Qcache_not_cached | 259415 |
| Qcache_queries_in_cache | 10882 |
| Qcache_total_blocks | 37035 |
| Questions | 64180746 |
| Rpl_status | NULL |
| Select_full_join | 1090 |
| Select_full_range_join | 0 |
| Select_range | 4574438 |
| Select_range_check | 0 |
| Select_scan | 4067668 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 377 |
| Sort_merge_passes | 9379 |
| Sort_range | 5477036 |
| Sort_rows | 600646454 |
| Sort_scan | 1974565 |
| 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 | 57981471 |
| Table_locks_waited | 88415 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 324 |
| Threads_connected | 4 |
| Threads_created | 328 |
| Threads_running | 2 |
| Uptime | 880157 |
+-----------------------------------+-------------+
Uptime: 880157 Threads: 4 Questions: 64180747 Slow queries: 377 Opens: 26126 Flush tables: 1 Open tables: 932 Queries per second avg: 72.920
mysqladmin Ver 8.41 Distrib 5.0.42, for redhat-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.42
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 10 days 4 hours 29 min 17 sec
Threads: 4 Questions: 64180747 Slow queries: 377 Opens: 26126 Flush tables: 1 Open tables: 932 Queries per second avg: 72.920
8 - mysqlreport
MySQL 5.0.42 uptime 10 4:40:36 Tue Jun 26 16:31:28 2007
__ Key __________________________________________________ _______________
Buffer used 170.36M of 192.00M %Used: 88.73
Current 192.00M %Usage: 100.00
Write hit 21.04%
Read hit 99.83%
__ Questions __________________________________________________ _________
Total 64.23M 72.9/s
DMS 29.46M 33.4/s %Total: 45.86
QC Hits 24.93M 28.3/s 38.82
COM_QUIT 5.23M 5.9/s 8.14
Com_ 4.62M 5.2/s 7.19
-Unknown 752 0.0/s 0.00
Slow 377 0.0/s 0.00 %DMS: 0.00
DMS 29.46M 33.4/s 45.86
SELECT 20.60M 23.4/s 32.07 69.93
UPDATE 6.72M 7.6/s 10.46 22.82
INSERT 1.82M 2.1/s 2.83 6.17
DELETE 207.86k 0.2/s 0.32 0.71
REPLACE 111.01k 0.1/s 0.17 0.38
Com_ 4.62M 5.2/s 7.19
change_db 4.35M 4.9/s 6.78
set_option 151.41k 0.2/s 0.24
show_variab 73.94k 0.1/s 0.12
__ SELECT and Sort __________________________________________________ ___
Scan 4.07M 4.6/s %SELECT: 19.77
Range 4.58M 5.2/s 22.24
Full join 1.09k 0.0/s 0.01
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 1.98M 2.2/s
Sort range 5.48M 6.2/s
Sort mrg pass 9.38k 0.0/s
__ Query Cache __________________________________________________ _______
Memory usage 82.80M of 96.00M %Used: 86.24
Block Fragmnt 19.54%
Hits 24.93M 28.3/s
Inserts 20.41M 23.2/s
Insrt:Prune 10.32:1 20.9/s
Hit:Insert 1.22:1
__ Table Locks __________________________________________________ _______
Waited 88.45k 0.1/s %Total: 0.15
Immediate 58.03M 65.9/s
__ Tables __________________________________________________ ____________
Open 933 of 2000 %Cache: 46.65
Opened 26.13k 0.0/s
__ Connections __________________________________________________ _______
Max used 328 of 650 %Max: 50.46
Total 5.23M 5.9/s
__ Created Temp __________________________________________________ ______
Disk table 1.60M 1.8/s
Table 1.93M 2.2/s
File 18.85k 0.0/s
__ Threads __________________________________________________ ___________
Running 1 of 2
Cached 326 of 384 %Hit: 99.99
Created 328 0.0/s
Slow 0 0/s
__ Aborted __________________________________________________ ___________
Clients 6.17k 0.0/s
Connects 2.93k 0.0/s
__ Bytes __________________________________________________ _____________
Sent 1.15G 1.3k/s
Received 4.16G 4.7k/s
__ InnoDB Buffer Pool __________________________________________________
Usage 0 of 0 %Used: 0.00
Read ratio 0.000
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 - Only vBulletin + vBGallery + vBSeo
10 Around 1700 users with a 1800 cookie timeout
11 -
http://www.bmwfaq.com/inf.php
12 -
KeepAlive On
#
# MaxKeepAliveRequests: The maximum number of requests to allow
# during a persistent connection. Set to 0 to allow an unlimited amount.
# We recommend you leave this number high, for maximum performance.
#
MaxKeepAliveRequests 100
#
# KeepAliveTimeout: Number of seconds to wait for the next request from the
# same client on the same connection.
#
KeepAliveTimeout 5
<IfModule prefork.c>
StartServers 10
MinSpareServers 10
MaxSpareServers 15
ServerLimit 320
MaxClients 320
MaxRequestsPerChild 1000
#ServerLimit 320
</IfModule>
13 - No files of more than 2GB
14 -
Linux srv01.bmwfaq.com 2.6.9-55.ELsmp #1 SMP Wed May 2 14:28:44 EDT 2007 i686 i686 i386 GNU/Linux
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
pending signals (-i) 1024
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 77824
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
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.079
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 tm2 xtpr
bogomips : 3992.55
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.079
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 tm2 xtpr
bogomips : 3990.08
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.079
cache size : 4096 KB
physical id : 3
siblings : 2
core id : 6
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 tm2 xtpr
bogomips : 3990.06
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.079
cache size : 4096 KB
physical id : 3
siblings : 2
core id : 7
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 tm2 xtpr
bogomips : 3990.05
2 - Dell PowerEdge 1950 Dual Dual Xeon 5130 @ 2GHZ
4GB of RAM 2 X 160 GB SAS discs
SO: Centos 4.5
Apache 2.0.52, php 5.2.3, MySQL 5.0.42, xCache 1.2.1RC1 and Zend Optimizer 3.2.8
3 - vBulletin 3.6.7PL1 + VBSEO
4 - No innodb tables
5 - MySQL was rebuilded from Jason|xoxide source rpm, php was compiled by hand and recompiled every time that i rebuild MySQL, i have xCache compiled by hand with Zend Optimizer for VBSeo.
I test memcached in a vmware enviroment with good results and planning add it to my server.
Another disabled 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 -
top - 16:16:26 up 18 days, 19:29, 1 user, load average: 0.91, 0.76, 0.81
Tasks: 173 total, 1 running, 171 sleeping, 0 stopped, 1 zombie
Cpu(s): 7.4% us, 1.1% sy, 0.0% ni, 91.5% id, 0.0% wa, 0.0% hi, 0.0% si
Mem: 4149124k total, 3918016k used, 231108k free, 55320k buffers
Swap: 2096472k total, 38656k used, 2057816k free, 2980008k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
5627 apache 15 0 67004 18m 7452 S 9 0.5 0:00.43 httpd
5652 apache 15 0 67088 18m 7120 S 9 0.5 0:00.44 httpd
5665 apache 15 0 72744 23m 7632 S 8 0.6 0:00.70 httpd
15385 mysql 10 -5 477m 392m 4484 S 2 9.7 1113:57 mysqld
5427 apache 16 0 67040 18m 7852 S 2 0.5 0:01.72 httpd
5439 apache 16 0 67048 19m 8428 S 1 0.5 0:02.91 httpd
5674 apache 15 0 0 0 0 Z 1 0.0 0:00.36 httpd <defunct>
5360 apache 15 0 67096 20m 9352 S 0 0.5 0:02.79 httpd
5550 apache 15 0 67116 19m 8284 S 0 0.5 0:01.59 httpd
5600 apache 15 0 67004 18m 7048 S 0 0.4 0:00.26 httpd
5670 apache 15 0 66984 18m 7208 S 0 0.5 0:00.33 httpd
5676 apache 16 0 67036 18m 7808 S 0 0.5 0:00.21 httpd
1 root 16 0 1764 512 436 S 0 0.0 0:01.77 init
2 root RT 0 0 0 0 S 0 0.0 0:00.72 migration/0
3 root 34 19 0 0 0 S 0 0.0 0:01.83 ksoftirqd/0
4 root RT 0 0 0 0 S 0 0.0 0:00.55 migration/1
7 -
[daniel@srv01 ~]$ cat /etc/my.cnf
[mysql]
socket =/var/lib/mysql/mysql.sock
[mysqld]
set-variable=local-infile=0
user=mysql
socket = /var/lib/mysql/mysql.sock
datadir=/var/lib/mysql
#tmpdir = /mnt/sdb1/tmp
safe-show-database
old_passwords
back_log = 75
skip-innodb
skip-networking
max_connections = 650
key_buffer = 192M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 2000
thread_cache_size = 384
wait_timeout = 30
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
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
[mysql.server]
user=mysql
basedir=/mnt/sdb1/mysql
pid-file=/var/run/mysqld/mysqld.pid
[mysqladmin]
socket=/var/lib/mysql/mysql.sock
[mysqld_safe]
err-log=/var/log/mysqld.log
open_files_limit = 8192
nice = -5
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer=256M
sort_buffer=256M
read_buffer=2M
write_buffer=2M
[mysqlhotcopy]
interactive-timeout
8 -
+---------------------------------+--------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 75 |
| basedir | /usr/ |
| bdb_cache_size | 8388600 |
| bdb_home | /var/lib/mysql/ |
| bdb_log_buffer_size | 1024000 |
| bdb_logdir | |
| bdb_max_lock | 10000 |
| bdb_shared_data | OFF |
| bdb_tmpdir | /tmp/ |
| 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 | NO |
| have_bdb | YES |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | YES |
| have_csv | NO |
| have_dynamic_loading | YES |
| have_example_engine | NO |
| have_federated_engine | NO |
| have_geometry | YES |
| have_innodb | DISABLED |
| have_isam | NO |
| have_merge_engine | YES |
| have_ndbcluster | NO |
| have_openssl | DISABLED |
| have_ssl | DISABLED |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| hostname | srv01.bmwfaq.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 | 1044480 |
| key_buffer_size | 201326592 |
| 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 | OFF |
| 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 | 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 | 67108864 |
| max_insert_delayed_threads | 20 |
| max_join_size | 4294967295 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_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 |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | ON |
| open_files_limit | 4660 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /var/run/mysqld/mysqld.pid |
| port | 0 |
| preload_buffer_size | 32768 |
| 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 |
| secure_file_priv | |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | ON |
| 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 | CEST |
| table_cache | 2000 |
| table_lock_wait_timeout | 50 |
| table_type | MyISAM |
| thread_cache_size | 384 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmp_table_size | 67108864 |
| tmpdir | /tmp/ |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| updatable_views_with_limit | YES |
| version | 5.0.42 |
| version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (May 14, 2007) |
| version_comment | Source distribution |
| version_compile_machine | i686 |
| version_compile_os | redhat-linux-gnu |
| wait_timeout | 30 |
+---------------------------------+--------------------------------------------------------+
+-----------------------------------+-------------+
| Variable_name | Value |
+-----------------------------------+-------------+
| Aborted_clients | 6164 |
| Aborted_connects | 2925 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 4145121837 |
| Bytes_sent | 737844850 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 11719 |
| Com_change_db | 4350575 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 11719 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 490 |
| Com_create_user | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 207636 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 490 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 40 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 1815756 |
| Com_insert_select | 490 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 102 |
| Com_optimize | 2659 |
| 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 | 110907 |
| Com_replace_select | 10 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 20581565 |
| Com_set_option | 151207 |
| 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 | 30 |
| Com_show_create_table | 3060 |
| Com_show_databases | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 3060 |
| 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 | 13 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 850 |
| Com_show_triggers | 3060 |
| Com_show_variables | 73828 |
| 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 | 490 |
| Com_unlock_tables | 102 |
| Com_update | 6717156 |
| Com_update_multi | 490 |
| 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 | 5223672 |
| Created_tmp_disk_tables | 1596199 |
| Created_tmp_files | 18848 |
| Created_tmp_tables | 1923908 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 637129 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 216080 |
| Handler_read_key | 1057069496 |
| Handler_read_next | 1668620008 |
| Handler_read_prev | 6724810 |
| Handler_read_rnd | 337651803 |
| Handler_read_rnd_next | 3333884524 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 7302210 |
| Handler_write | 137342582 |
| 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 | 174450 |
| Key_read_requests | 10612406249 |
| Key_reads | 17954962 |
| Key_write_requests | 9153642 |
| Key_writes | 7226925 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 328 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 1072 |
| Open_streams | 0 |
| Open_tables | 932 |
| Opened_tables | 26126 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 5824 |
| Qcache_free_memory | 13621288 |
| Qcache_hits | 24913400 |
| Qcache_inserts | 20393798 |
| Qcache_lowmem_prunes | 1975837 |
| Qcache_not_cached | 259415 |
| Qcache_queries_in_cache | 10882 |
| Qcache_total_blocks | 37035 |
| Questions | 64180746 |
| Rpl_status | NULL |
| Select_full_join | 1090 |
| Select_full_range_join | 0 |
| Select_range | 4574438 |
| Select_range_check | 0 |
| Select_scan | 4067668 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 377 |
| Sort_merge_passes | 9379 |
| Sort_range | 5477036 |
| Sort_rows | 600646454 |
| Sort_scan | 1974565 |
| 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 | 57981471 |
| Table_locks_waited | 88415 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 324 |
| Threads_connected | 4 |
| Threads_created | 328 |
| Threads_running | 2 |
| Uptime | 880157 |
+-----------------------------------+-------------+
Uptime: 880157 Threads: 4 Questions: 64180747 Slow queries: 377 Opens: 26126 Flush tables: 1 Open tables: 932 Queries per second avg: 72.920
mysqladmin Ver 8.41 Distrib 5.0.42, for redhat-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.42
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 10 days 4 hours 29 min 17 sec
Threads: 4 Questions: 64180747 Slow queries: 377 Opens: 26126 Flush tables: 1 Open tables: 932 Queries per second avg: 72.920
8 - mysqlreport
MySQL 5.0.42 uptime 10 4:40:36 Tue Jun 26 16:31:28 2007
__ Key __________________________________________________ _______________
Buffer used 170.36M of 192.00M %Used: 88.73
Current 192.00M %Usage: 100.00
Write hit 21.04%
Read hit 99.83%
__ Questions __________________________________________________ _________
Total 64.23M 72.9/s
DMS 29.46M 33.4/s %Total: 45.86
QC Hits 24.93M 28.3/s 38.82
COM_QUIT 5.23M 5.9/s 8.14
Com_ 4.62M 5.2/s 7.19
-Unknown 752 0.0/s 0.00
Slow 377 0.0/s 0.00 %DMS: 0.00
DMS 29.46M 33.4/s 45.86
SELECT 20.60M 23.4/s 32.07 69.93
UPDATE 6.72M 7.6/s 10.46 22.82
INSERT 1.82M 2.1/s 2.83 6.17
DELETE 207.86k 0.2/s 0.32 0.71
REPLACE 111.01k 0.1/s 0.17 0.38
Com_ 4.62M 5.2/s 7.19
change_db 4.35M 4.9/s 6.78
set_option 151.41k 0.2/s 0.24
show_variab 73.94k 0.1/s 0.12
__ SELECT and Sort __________________________________________________ ___
Scan 4.07M 4.6/s %SELECT: 19.77
Range 4.58M 5.2/s 22.24
Full join 1.09k 0.0/s 0.01
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 1.98M 2.2/s
Sort range 5.48M 6.2/s
Sort mrg pass 9.38k 0.0/s
__ Query Cache __________________________________________________ _______
Memory usage 82.80M of 96.00M %Used: 86.24
Block Fragmnt 19.54%
Hits 24.93M 28.3/s
Inserts 20.41M 23.2/s
Insrt:Prune 10.32:1 20.9/s
Hit:Insert 1.22:1
__ Table Locks __________________________________________________ _______
Waited 88.45k 0.1/s %Total: 0.15
Immediate 58.03M 65.9/s
__ Tables __________________________________________________ ____________
Open 933 of 2000 %Cache: 46.65
Opened 26.13k 0.0/s
__ Connections __________________________________________________ _______
Max used 328 of 650 %Max: 50.46
Total 5.23M 5.9/s
__ Created Temp __________________________________________________ ______
Disk table 1.60M 1.8/s
Table 1.93M 2.2/s
File 18.85k 0.0/s
__ Threads __________________________________________________ ___________
Running 1 of 2
Cached 326 of 384 %Hit: 99.99
Created 328 0.0/s
Slow 0 0/s
__ Aborted __________________________________________________ ___________
Clients 6.17k 0.0/s
Connects 2.93k 0.0/s
__ Bytes __________________________________________________ _____________
Sent 1.15G 1.3k/s
Received 4.16G 4.7k/s
__ InnoDB Buffer Pool __________________________________________________
Usage 0 of 0 %Used: 0.00
Read ratio 0.000
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 - Only vBulletin + vBGallery + vBSeo
10 Around 1700 users with a 1800 cookie timeout
11 -
http://www.bmwfaq.com/inf.php
12 -
KeepAlive On
#
# MaxKeepAliveRequests: The maximum number of requests to allow
# during a persistent connection. Set to 0 to allow an unlimited amount.
# We recommend you leave this number high, for maximum performance.
#
MaxKeepAliveRequests 100
#
# KeepAliveTimeout: Number of seconds to wait for the next request from the
# same client on the same connection.
#
KeepAliveTimeout 5
<IfModule prefork.c>
StartServers 10
MinSpareServers 10
MaxSpareServers 15
ServerLimit 320
MaxClients 320
MaxRequestsPerChild 1000
#ServerLimit 320
</IfModule>
13 - No files of more than 2GB
14 -
Linux srv01.bmwfaq.com 2.6.9-55.ELsmp #1 SMP Wed May 2 14:28:44 EDT 2007 i686 i686 i386 GNU/Linux
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
pending signals (-i) 1024
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 77824
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
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.079
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 tm2 xtpr
bogomips : 3992.55
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.079
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 tm2 xtpr
bogomips : 3990.08
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.079
cache size : 4096 KB
physical id : 3
siblings : 2
core id : 6
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 tm2 xtpr
bogomips : 3990.06
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.079
cache size : 4096 KB
physical id : 3
siblings : 2
core id : 7
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 tm2 xtpr
bogomips : 3990.05