Parker Clack
Mon 24th Apr '06, 7:51pm
The loads on the server are always low but the pages lag in loading.Any help would be appreciated.Thanks,Parker> 1. Is this on dedicated or shared virual server. If shared, > how many sites share this server (ask web host if needed)Dedicated.> 2. your server specs. For example:Dual Xeon 2.4GHz, 2GB, 36GB 15K RPM U320 SCSI (one for Web, one for SQL)Apache 1.3.34, PHP 4.4.1, MySQL 5.0.20> 3. if you use mysql 4.x instead of mysql 3.23.x, do you have > any innodb type databases/tables on your server ?I don't know how to find this out.> 4. if possible how mysql was compiled/installedRPM from mysql.com website.
5. your top stats Web Server:top - 23:10:12 up 4 days, 7:59, 1 user, load average: 0.48, 1.69, 1.48Tasks: 152 total, 1 running, 151 sleeping, 0 stopped, 0 zombie
Cpu(s): 8.8% us, 1.0% sy, 0.0% ni, 89.7% id, 0.5% wa, 0.0% hi, 0.0% si
Mem: 2335028k total, 1413424k used, 921604k free, 133756k buffers
Swap: 2048276k total, 144k used, 2048132k free, 582284k cached
MySQL Server:top - 23:09:08 up 4 days, 7:58, 1 user,
load average: 0.38, 1.79, 1.65Tasks: 74 total, 2 running, 72 sleeping, 0 stopped, 0 zombie
Cpu(s): 3.7% us, 2.6% sy, 0.0% ni, 90.1% id, 3.6% wa, 0.0% hi, 0.0% si
Mem: 2074940k total, 2048152k used, 26788k free, 13096k buffers
Swap: 2048276k total, 144k used, 2048132k free, 1774404k cached
6. 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]
set-variable = key_buffer=16M
set-variable = max_allowed_packet=16M
set-variable = thread_stack=128K
set-variable = tmp_table_size=512M
set-variable = max_connections=1000
set-variable = table_cache=512
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=8M
set-variable = thread_cache_size=512
set-variable = myisam_sort_buffer_size=64M
set-variable = wait_timeout=7200
set-variable = connect_timeout=10s
et-variable = max_connect_errors=9999999
set-variable = query_cache_size=100000000
set-variable = ft_min_word_len=3
## Here you can see queries with especially long duration#log-slow-queries = /var/lib/mysql/mysql-slow.log## The following can be used as easy to replay backup logs or for replication#server-id = 1#log-bin#binlog-do-db = include_database_name#binlog-ignore-db = include_database_name## Read the manual if you want to enable InnoDB!skip-innodb
[mysqldump]
quickset-variable = max_allowed_packet=1M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completitionset-variable = key_buffer=16M> 7. MySQL stats from ssh telnet as root user type:+-----+| Variable_name | Value|+----+|
auto_increment_increment | 1||
auto_increment_offset | 1||
automatic_sp_privileges | ON||
back_log | 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 | [I]/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 | 3||
ft_query_expansion_limit | 20||
ft_stopword_file | (built-in)||
group_concat_max_len | 1024||
have_archive | YES||
have_bdb | NO||
have_blackhole_engine | NO||
have_compress | YES||
have_crypt | YES||
have_csv | NO||
have_example_engine | NO||
have_federated_engine | NO||
have_geometry | YES||
have_innodb | DISABLED||
have_isam | NO||
have_ndbcluster | NO||
have_openssl | NO||
have_query_cache | YES||
have_raid | NO||
have_rtree_keys | YES||
have_symlink | YES||
init_connect ||| i
nit_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_commit_concurrency | 0||
innodb_concurrency_tickets | 500||
innodb_data_file_path |||
innodb_data_home_dir |||
innodb_doublewrite | ON||
innodb_fast_shutdown | 1||
innodb_file_io_threads | 4||
innodb_file_per_table | OFF||
innodb_flush_log_at_trx_commit | 1||
innodb_flush_method |||
innodb_force_recovery | 0||
innodb_lock_wait_timeout | 50||
innodb_locks_unsafe_for_binlog | OFF||
innodb_log_arch_dir |||
innodb_log_archive | OFF||
innodb_log_buffer_size | 1048576||
innodb_log_file_size | 5242880||
innodb_log_files_in_group | 2||
innodb_log_group_home_dir |||
innodb_max_dirty_pages_pct | 90||
innodb_max_purge_lag | 0||
innodb_mirrored_log_groups | 1||
innodb_open_files | 300||
innodb_support_xa | ON||
innodb_sync_spin_loops | 20||
innodb_table_locks | ON||
innodb_thread_concurrency | 0||
innodb_thread_sleep_delay | 10000||
interactive_timeout | 28800||
join_buffer_size | 1044480||
key_buffer_size | 16777216||
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||
license | GPL||
local_infile | ON||
locked_in_memory | OFF||
log | OFF||
log_bin | OFF||
log_bin_trust_function_creators | OFF||
log_error |||
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 | 9999999||
max_connections | 1000||
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_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| |
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 | 5010||
optimizer_prune_level | 1||
optimizer_search_depth | 62||
pid_file |/var/lib/mysql/sql.hometheaterforum.com.pid ||
port | 3306||
preload_buffer_size | 32768||
protocol_version | 10||
query_alloc_block_size | 8192||
query_cache_limit | 1048576||
query_cache_min_res_unit | 4096||
query_cache_size | 99999744||
query_cache_type | ON||
query_cache_wlock_invalidate | OFF||
query_prealloc_size | 8192||
range_alloc_block_size | 2048||
read_buffer_size | 1044480||
read_only | OFF||
read_rnd_buffer_size | 262144||
relay_log_purge | ON||
relay_log_space_limit | 0||
rpl_recovery_rank | 0||
secure_auth | OFF||
server_id | 0||
skip_external_locking | ON||
skip_networking | OFF||
skip_show_database | OFF||
slave_compressed_protocol | OFF||
slave_load_tmpdir | /tmp/||
slave_net_timeout | 3600||
slave_skip_errors | OFF||
slave_transaction_retries | 10||
slow_launch_time | 2||
socket | /var/lib/mysql/mysql.sock||
sort_buffer_size | 8388600||
sql_mode |||
sql_notes | ON||
sql_warnings | ON||
storage_engine | MyISAM||
sync_binlog | 0||
sync_frm | ON||
sync_replication | 0||
sync_replication_slave_id | 0||
sync_replication_timeout | 10||
system_time_zone | CDT||
table_cache | 512||
table_lock_wait_timeout | 50||
table_type | MyISAM||
thread_cache_size | 512||
thread_stack | 131072||
time_format | %H:%i:%s||
time_zone | SYSTEM||
timed_mutexes | OFF||
tmp_table_size | 536870912||
tmpdir |||
transaction_alloc_block_size | 8192||
tmpdir |||
transaction_alloc_block_size | 8192||
transaction_prealloc_size | 4096||
tx_isolation | REPEATABLE-READ||
updatable_views_with_limit | YES||
version | 5.0.20-standard||
version_comment | MySQL Community Edition - Standard (GPL)||
version_compile_machine | i686||
version_compile_os | pc-linux-gnu||
wait_timeout | 7200|
+---------+| Variable_name | Value |+-------+|
Aborted_clients | 4 ||
Aborted_connects | 0 ||
Binlog_cache_disk_use | 0 ||
Binlog_cache_use | 0 ||
Bytes_received | 708858713 ||
Bytes_sent | 133179820 ||
Com_admin_commands | 209169 ||
Com_alter_db | 0 ||
Com_alter_table | 13 ||
Com_analyze | 0 ||
Com_backup_table | 0 ||
Com_begin | 0 ||
Com_change_db | 621917 ||
Com_change_master | 0 ||
Com_check | 0 ||
Com_checksum | 0 ||
Com_commit | 0 ||
Com_create_db | 0 ||
Com_create_function | 0 ||
Com_create_index | 0 ||
Com_create_table | 4 ||
Com_dealloc_sql | 0 ||
Com_delete | 6746 ||
Com_delete_multi | 0 ||
Com_do | 0 ||
Com_drop_db | 0 ||
Com_drop_function | 0 ||
Com_drop_index | 0 ||
Com_drop_table | 4 ||
Com_drop_user | 0 ||
Com_execute_sql | 0 ||
Com_flush | 0 ||
Com_grant | 0 ||
Com_ha_close | 0 ||
Com_ha_open | 0 ||
Com_ha_read | 0 ||
Com_help | 0 ||
Com_insert | 25116 ||
Com_insert_select | 2 ||
Com_kill | 0 ||
Com_load | 0 ||
Com_load_master_data | 0 ||
Com_load_master_table | 0 ||
Com_lock_tables | 0 ||
Com_optimize | 0 ||
Com_preload_keys | 0 ||
Com_prepare_sql | 0 ||
Com_purge | 0 ||
Com_purge_before_date | 0 ||
Com_rename_table | 0 ||
Com_repair | 0 ||
Com_replace | 2362 ||
Com_replace_select | 0 ||
Com_reset | 0 ||
Com_restore_table | 0 ||
Com_revoke | 0 ||
Com_restore_table| 0 ||
Com_revoke | 0 ||
Com_revoke_all | 0 ||
Com_rollback | 0 ||
Com_savepoint | 0 ||
Com_select | 838249 ||
Com_set_option | 112 ||
Com_show_binlog_events | 0 ||
Com_show_binlogs | 4 ||
Com_show_charsets | 28 ||
Com_show_collations | 28 ||
Com_show_column_types | 0 ||
Com_show_create_db | 0 ||
Com_show_create_table | 3 ||
Com_show_databases | 12 ||
Com_show_errors | 0 ||
Com_show_fields | 133 ||
Com_show_grants | 10 ||
Com_show_innodb_status | 0 ||
Com_show_keys | 7 ||
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 | 5 ||
Com_show_storage_engines | 1 ||
Com_show_tables | 127 ||
Com_show_triggers | 0 ||
Com_show_variables | 208 ||
Com_show_warnings | 0 ||
Com_slave_start | 0 ||
Com_slave_stop | 0 ||
Com_stmt_close | 0 ||
Com_stmt_execute | 0 ||
Com_stmt_fetch | 0 ||
Com_stmt_prepare | 0 ||
Com_stmt_reset | 0 ||
Com_stmt_send_long_data | 0 ||
Com_truncate | 0 ||
Com_unlock_tables | 0 ||
Com_update | 652233 ||
Com_update_multi | 0 ||
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 | 392609 ||
Created_tmp_disk_tables | 884 ||
Created_tmp_files | 9 ||
Created_tmp_tables | 24801 ||
Delayed_errors | 0 ||
Delayed_insert_threads | 0 ||
Delayed_writes | 0 ||
Flush_commands | 1 ||
Handler_commit | 0 ||
Handler_delete | 24742 ||
Handler_discover | 0 ||
Handler_prepare | 0 ||
Handler_read_first | 97388 ||
Handler_read_key | 36767711 ||
Handler_read_next | 74040889 ||
Handler_read_prev | 512239 ||
Handler_read_next| 74040889 ||
Handler_read_prev | 512239 ||
Handler_read_rnd | 10090541 ||
Handler_read_rnd_next | 200216840 ||
Handler_rollback | 0 ||
Handler_savepoint | 0 ||
Handler_savepoint_rollback | 0 ||
Handler_update | 676007 ||
Handler_write | 15551448 ||
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 | 14497 ||
Key_read_requests | 220190031 ||
Key_reads | 932751 ||
Key_write_requests | 3330008 ||
Key_writes | 138208 ||
Last_query_cost | 0.000000 ||
Max_used_connections | 448 ||
Not_flushed_delayed_rows | 0 ||
Open_files | 640 ||
Open_streams | 0 ||
Open_tables | 512 ||
Opened_tables | 3450 ||
Qcache_free_blocks | 5091 ||
Qcache_free_memory | 69048104 ||
Qcache_hits | 2533125 ||
Qcache_inserts | 829903 ||
Qcache_hits| 2533125 ||
Qcache_inserts | 829903 ||
Qcache_lowmem_prunes | 125463 ||
Qcache_not_cached | 8867 ||
Qcache_queries_in_cache | 19733 ||
Qcache_total_blocks | 44893 ||
Questions | 5073002 ||
Rpl_status | NULL ||
Select_full_join | 15 ||
Select_full_range_join | 0 ||
Select_range | 114923 ||
Select_range_check | 0 ||
Select_scan | 158387 ||
Slave_open_temp_tables | 0 ||
Slave_retried_transactions | 0 ||
Slave_running | OFF ||
Slow_launch_threads | 0 ||
Slow_queries | 248 ||
Sort_merge_passes | 3 ||
Sort_range | 75371 ||
Sort_rows | 24865986 ||
Sort_scan | 30884 ||
Table_locks_immediate | 1902393 ||
Table_locks_waited | 7986 ||
Tc_log_max_pages_used | 0 ||
Tc_log_page_size | 0 ||
Tc_log_page_waits | 0 ||
Threads_cached | 369 ||
Threads_connected | 79 ||
Threads_created | 448 ||
Threads_running | 1 ||
Uptime | 76093 |
+-----------+Uptime: 76093 Threads: 79 Questions: 5073003
Slow queries: 0 Opens: 0
Flush tables: 1
Open tables: 512
Queries per second avg: 66.668
mysqladmin Ver 8.41 Distrib 5.0.20, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult ABThis software comes with ABSOLUTELY NO WARRANTY.
This is free software,and you are welcome to modify and redistribute it under the GPL licenseServer
version 5.0.20-standardProtocol version 10
Connection Localhost via UNIX
socketUNIX
socket /var/lib/mysql/mysql.sockUptime: 21 hours 8 min 13 secThreads: 79 Questions: 5073003 Slow queries: 0 Opens: 0 Flush tables: 1Open tables: 512 Queries per second avg: 66.668> 12. what version of vB are you running ?3.5.4> 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 filesize exceeded> messages in apache error logNone are.> 14. Post output from these 3 commands> uname -aLinux sql.hometheaterforum.com 2.6.9-34.ELsmp #1 SMP Wed Mar 8 00:27:03 CST2006 i686 i686 i386 GNU/Linux> ulimit -aHcore file size (blocks, -c)
unlimiteddata seg size (kbytes, -d)
unlimitedfile size (blocks, -f)
unlimitedpending signals (-i) 1024
max locked memory (kbytes, -l) 32max memory size (kbytes, -m)
unlimitedopen files (-n) 1024pipe size (512 bytes, -p)
8POSIX message queues (bytes, -q) 819200stack size (kbytes, -s) unlimitedcpu time (seconds, -t) unlimitedmax user processes (-u) 32767virtual memory (kbytes, -v) unlimitedfile locks (-x) unlimited> cat /proc/cpuinfoprocessor : 0vendor_id : GenuineIntelcpu family : 15
model : 2
model name : Intel(R) Xeon(TM) CPU 2.40GHz
stepping : 7cpu MHz : 2393.067cache size : 512 KBphysical id : 0
siblings : 2core id : 0cpu cores : 1
fdiv_bug : no
hlt_bug : no
f00f_bug : noc
oma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmovpat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe cid
bogomips : 4790.14
processor : 1
vendor_id : GenuineIntelcpu family : 15model : 2model name : Intel(R) Xeon(TM) CPU 2.40GHz
stepping : 7cpu MHz : 2393.067
cache size : 512 KB
physical id : 0
siblings : 2core
id : 0
cpu cores : 1
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2wp : yesf
lags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmovpat pse36 cl
flush dts acpi mmx fxsr sse sse2 ss ht tm pbe cid
bogomips : 4783.81
processor : 2
vendor_id : GenuineIntelcpu family : 15model : 2
model name : Intel(R) Xeon(TM) CPU 2.40GHzstepping : 7cpu MHz : 2393.067
cache size : 512 KB
physical id : 3
siblings : 2
core id : 3
cpu cores : 1
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmovpat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe cid
bogomips : 4783.88
processor : 3
vendor_id : GenuineIntelcpu family : 15model : 2
model name : Intel(R) Xeon(TM) CPU 2.40GHz
stepping : 7cpu MHz : 2393.067
cache size : 512 KB
physical id : 3
siblings : 2core
id : 3cpu
cores : 1f
div_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmovpat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe cid
bogomips : 4783.85
5. your top stats Web Server:top - 23:10:12 up 4 days, 7:59, 1 user, load average: 0.48, 1.69, 1.48Tasks: 152 total, 1 running, 151 sleeping, 0 stopped, 0 zombie
Cpu(s): 8.8% us, 1.0% sy, 0.0% ni, 89.7% id, 0.5% wa, 0.0% hi, 0.0% si
Mem: 2335028k total, 1413424k used, 921604k free, 133756k buffers
Swap: 2048276k total, 144k used, 2048132k free, 582284k cached
MySQL Server:top - 23:09:08 up 4 days, 7:58, 1 user,
load average: 0.38, 1.79, 1.65Tasks: 74 total, 2 running, 72 sleeping, 0 stopped, 0 zombie
Cpu(s): 3.7% us, 2.6% sy, 0.0% ni, 90.1% id, 3.6% wa, 0.0% hi, 0.0% si
Mem: 2074940k total, 2048152k used, 26788k free, 13096k buffers
Swap: 2048276k total, 144k used, 2048132k free, 1774404k cached
6. 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]
set-variable = key_buffer=16M
set-variable = max_allowed_packet=16M
set-variable = thread_stack=128K
set-variable = tmp_table_size=512M
set-variable = max_connections=1000
set-variable = table_cache=512
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=8M
set-variable = thread_cache_size=512
set-variable = myisam_sort_buffer_size=64M
set-variable = wait_timeout=7200
set-variable = connect_timeout=10s
et-variable = max_connect_errors=9999999
set-variable = query_cache_size=100000000
set-variable = ft_min_word_len=3
## Here you can see queries with especially long duration#log-slow-queries = /var/lib/mysql/mysql-slow.log## The following can be used as easy to replay backup logs or for replication#server-id = 1#log-bin#binlog-do-db = include_database_name#binlog-ignore-db = include_database_name## Read the manual if you want to enable InnoDB!skip-innodb
[mysqldump]
quickset-variable = max_allowed_packet=1M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completitionset-variable = key_buffer=16M> 7. MySQL stats from ssh telnet as root user type:+-----+| Variable_name | Value|+----+|
auto_increment_increment | 1||
auto_increment_offset | 1||
automatic_sp_privileges | ON||
back_log | 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 | [I]/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 | 3||
ft_query_expansion_limit | 20||
ft_stopword_file | (built-in)||
group_concat_max_len | 1024||
have_archive | YES||
have_bdb | NO||
have_blackhole_engine | NO||
have_compress | YES||
have_crypt | YES||
have_csv | NO||
have_example_engine | NO||
have_federated_engine | NO||
have_geometry | YES||
have_innodb | DISABLED||
have_isam | NO||
have_ndbcluster | NO||
have_openssl | NO||
have_query_cache | YES||
have_raid | NO||
have_rtree_keys | YES||
have_symlink | YES||
init_connect ||| i
nit_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_commit_concurrency | 0||
innodb_concurrency_tickets | 500||
innodb_data_file_path |||
innodb_data_home_dir |||
innodb_doublewrite | ON||
innodb_fast_shutdown | 1||
innodb_file_io_threads | 4||
innodb_file_per_table | OFF||
innodb_flush_log_at_trx_commit | 1||
innodb_flush_method |||
innodb_force_recovery | 0||
innodb_lock_wait_timeout | 50||
innodb_locks_unsafe_for_binlog | OFF||
innodb_log_arch_dir |||
innodb_log_archive | OFF||
innodb_log_buffer_size | 1048576||
innodb_log_file_size | 5242880||
innodb_log_files_in_group | 2||
innodb_log_group_home_dir |||
innodb_max_dirty_pages_pct | 90||
innodb_max_purge_lag | 0||
innodb_mirrored_log_groups | 1||
innodb_open_files | 300||
innodb_support_xa | ON||
innodb_sync_spin_loops | 20||
innodb_table_locks | ON||
innodb_thread_concurrency | 0||
innodb_thread_sleep_delay | 10000||
interactive_timeout | 28800||
join_buffer_size | 1044480||
key_buffer_size | 16777216||
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||
license | GPL||
local_infile | ON||
locked_in_memory | OFF||
log | OFF||
log_bin | OFF||
log_bin_trust_function_creators | OFF||
log_error |||
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 | 9999999||
max_connections | 1000||
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_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| |
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 | 5010||
optimizer_prune_level | 1||
optimizer_search_depth | 62||
pid_file |/var/lib/mysql/sql.hometheaterforum.com.pid ||
port | 3306||
preload_buffer_size | 32768||
protocol_version | 10||
query_alloc_block_size | 8192||
query_cache_limit | 1048576||
query_cache_min_res_unit | 4096||
query_cache_size | 99999744||
query_cache_type | ON||
query_cache_wlock_invalidate | OFF||
query_prealloc_size | 8192||
range_alloc_block_size | 2048||
read_buffer_size | 1044480||
read_only | OFF||
read_rnd_buffer_size | 262144||
relay_log_purge | ON||
relay_log_space_limit | 0||
rpl_recovery_rank | 0||
secure_auth | OFF||
server_id | 0||
skip_external_locking | ON||
skip_networking | OFF||
skip_show_database | OFF||
slave_compressed_protocol | OFF||
slave_load_tmpdir | /tmp/||
slave_net_timeout | 3600||
slave_skip_errors | OFF||
slave_transaction_retries | 10||
slow_launch_time | 2||
socket | /var/lib/mysql/mysql.sock||
sort_buffer_size | 8388600||
sql_mode |||
sql_notes | ON||
sql_warnings | ON||
storage_engine | MyISAM||
sync_binlog | 0||
sync_frm | ON||
sync_replication | 0||
sync_replication_slave_id | 0||
sync_replication_timeout | 10||
system_time_zone | CDT||
table_cache | 512||
table_lock_wait_timeout | 50||
table_type | MyISAM||
thread_cache_size | 512||
thread_stack | 131072||
time_format | %H:%i:%s||
time_zone | SYSTEM||
timed_mutexes | OFF||
tmp_table_size | 536870912||
tmpdir |||
transaction_alloc_block_size | 8192||
tmpdir |||
transaction_alloc_block_size | 8192||
transaction_prealloc_size | 4096||
tx_isolation | REPEATABLE-READ||
updatable_views_with_limit | YES||
version | 5.0.20-standard||
version_comment | MySQL Community Edition - Standard (GPL)||
version_compile_machine | i686||
version_compile_os | pc-linux-gnu||
wait_timeout | 7200|
+---------+| Variable_name | Value |+-------+|
Aborted_clients | 4 ||
Aborted_connects | 0 ||
Binlog_cache_disk_use | 0 ||
Binlog_cache_use | 0 ||
Bytes_received | 708858713 ||
Bytes_sent | 133179820 ||
Com_admin_commands | 209169 ||
Com_alter_db | 0 ||
Com_alter_table | 13 ||
Com_analyze | 0 ||
Com_backup_table | 0 ||
Com_begin | 0 ||
Com_change_db | 621917 ||
Com_change_master | 0 ||
Com_check | 0 ||
Com_checksum | 0 ||
Com_commit | 0 ||
Com_create_db | 0 ||
Com_create_function | 0 ||
Com_create_index | 0 ||
Com_create_table | 4 ||
Com_dealloc_sql | 0 ||
Com_delete | 6746 ||
Com_delete_multi | 0 ||
Com_do | 0 ||
Com_drop_db | 0 ||
Com_drop_function | 0 ||
Com_drop_index | 0 ||
Com_drop_table | 4 ||
Com_drop_user | 0 ||
Com_execute_sql | 0 ||
Com_flush | 0 ||
Com_grant | 0 ||
Com_ha_close | 0 ||
Com_ha_open | 0 ||
Com_ha_read | 0 ||
Com_help | 0 ||
Com_insert | 25116 ||
Com_insert_select | 2 ||
Com_kill | 0 ||
Com_load | 0 ||
Com_load_master_data | 0 ||
Com_load_master_table | 0 ||
Com_lock_tables | 0 ||
Com_optimize | 0 ||
Com_preload_keys | 0 ||
Com_prepare_sql | 0 ||
Com_purge | 0 ||
Com_purge_before_date | 0 ||
Com_rename_table | 0 ||
Com_repair | 0 ||
Com_replace | 2362 ||
Com_replace_select | 0 ||
Com_reset | 0 ||
Com_restore_table | 0 ||
Com_revoke | 0 ||
Com_restore_table| 0 ||
Com_revoke | 0 ||
Com_revoke_all | 0 ||
Com_rollback | 0 ||
Com_savepoint | 0 ||
Com_select | 838249 ||
Com_set_option | 112 ||
Com_show_binlog_events | 0 ||
Com_show_binlogs | 4 ||
Com_show_charsets | 28 ||
Com_show_collations | 28 ||
Com_show_column_types | 0 ||
Com_show_create_db | 0 ||
Com_show_create_table | 3 ||
Com_show_databases | 12 ||
Com_show_errors | 0 ||
Com_show_fields | 133 ||
Com_show_grants | 10 ||
Com_show_innodb_status | 0 ||
Com_show_keys | 7 ||
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 | 5 ||
Com_show_storage_engines | 1 ||
Com_show_tables | 127 ||
Com_show_triggers | 0 ||
Com_show_variables | 208 ||
Com_show_warnings | 0 ||
Com_slave_start | 0 ||
Com_slave_stop | 0 ||
Com_stmt_close | 0 ||
Com_stmt_execute | 0 ||
Com_stmt_fetch | 0 ||
Com_stmt_prepare | 0 ||
Com_stmt_reset | 0 ||
Com_stmt_send_long_data | 0 ||
Com_truncate | 0 ||
Com_unlock_tables | 0 ||
Com_update | 652233 ||
Com_update_multi | 0 ||
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 | 392609 ||
Created_tmp_disk_tables | 884 ||
Created_tmp_files | 9 ||
Created_tmp_tables | 24801 ||
Delayed_errors | 0 ||
Delayed_insert_threads | 0 ||
Delayed_writes | 0 ||
Flush_commands | 1 ||
Handler_commit | 0 ||
Handler_delete | 24742 ||
Handler_discover | 0 ||
Handler_prepare | 0 ||
Handler_read_first | 97388 ||
Handler_read_key | 36767711 ||
Handler_read_next | 74040889 ||
Handler_read_prev | 512239 ||
Handler_read_next| 74040889 ||
Handler_read_prev | 512239 ||
Handler_read_rnd | 10090541 ||
Handler_read_rnd_next | 200216840 ||
Handler_rollback | 0 ||
Handler_savepoint | 0 ||
Handler_savepoint_rollback | 0 ||
Handler_update | 676007 ||
Handler_write | 15551448 ||
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 | 14497 ||
Key_read_requests | 220190031 ||
Key_reads | 932751 ||
Key_write_requests | 3330008 ||
Key_writes | 138208 ||
Last_query_cost | 0.000000 ||
Max_used_connections | 448 ||
Not_flushed_delayed_rows | 0 ||
Open_files | 640 ||
Open_streams | 0 ||
Open_tables | 512 ||
Opened_tables | 3450 ||
Qcache_free_blocks | 5091 ||
Qcache_free_memory | 69048104 ||
Qcache_hits | 2533125 ||
Qcache_inserts | 829903 ||
Qcache_hits| 2533125 ||
Qcache_inserts | 829903 ||
Qcache_lowmem_prunes | 125463 ||
Qcache_not_cached | 8867 ||
Qcache_queries_in_cache | 19733 ||
Qcache_total_blocks | 44893 ||
Questions | 5073002 ||
Rpl_status | NULL ||
Select_full_join | 15 ||
Select_full_range_join | 0 ||
Select_range | 114923 ||
Select_range_check | 0 ||
Select_scan | 158387 ||
Slave_open_temp_tables | 0 ||
Slave_retried_transactions | 0 ||
Slave_running | OFF ||
Slow_launch_threads | 0 ||
Slow_queries | 248 ||
Sort_merge_passes | 3 ||
Sort_range | 75371 ||
Sort_rows | 24865986 ||
Sort_scan | 30884 ||
Table_locks_immediate | 1902393 ||
Table_locks_waited | 7986 ||
Tc_log_max_pages_used | 0 ||
Tc_log_page_size | 0 ||
Tc_log_page_waits | 0 ||
Threads_cached | 369 ||
Threads_connected | 79 ||
Threads_created | 448 ||
Threads_running | 1 ||
Uptime | 76093 |
+-----------+Uptime: 76093 Threads: 79 Questions: 5073003
Slow queries: 0 Opens: 0
Flush tables: 1
Open tables: 512
Queries per second avg: 66.668
mysqladmin Ver 8.41 Distrib 5.0.20, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult ABThis software comes with ABSOLUTELY NO WARRANTY.
This is free software,and you are welcome to modify and redistribute it under the GPL licenseServer
version 5.0.20-standardProtocol version 10
Connection Localhost via UNIX
socketUNIX
socket /var/lib/mysql/mysql.sockUptime: 21 hours 8 min 13 secThreads: 79 Questions: 5073003 Slow queries: 0 Opens: 0 Flush tables: 1Open tables: 512 Queries per second avg: 66.668> 12. what version of vB are you running ?3.5.4> 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 filesize exceeded> messages in apache error logNone are.> 14. Post output from these 3 commands> uname -aLinux sql.hometheaterforum.com 2.6.9-34.ELsmp #1 SMP Wed Mar 8 00:27:03 CST2006 i686 i686 i386 GNU/Linux> ulimit -aHcore file size (blocks, -c)
unlimiteddata seg size (kbytes, -d)
unlimitedfile size (blocks, -f)
unlimitedpending signals (-i) 1024
max locked memory (kbytes, -l) 32max memory size (kbytes, -m)
unlimitedopen files (-n) 1024pipe size (512 bytes, -p)
8POSIX message queues (bytes, -q) 819200stack size (kbytes, -s) unlimitedcpu time (seconds, -t) unlimitedmax user processes (-u) 32767virtual memory (kbytes, -v) unlimitedfile locks (-x) unlimited> cat /proc/cpuinfoprocessor : 0vendor_id : GenuineIntelcpu family : 15
model : 2
model name : Intel(R) Xeon(TM) CPU 2.40GHz
stepping : 7cpu MHz : 2393.067cache size : 512 KBphysical id : 0
siblings : 2core id : 0cpu cores : 1
fdiv_bug : no
hlt_bug : no
f00f_bug : noc
oma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmovpat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe cid
bogomips : 4790.14
processor : 1
vendor_id : GenuineIntelcpu family : 15model : 2model name : Intel(R) Xeon(TM) CPU 2.40GHz
stepping : 7cpu MHz : 2393.067
cache size : 512 KB
physical id : 0
siblings : 2core
id : 0
cpu cores : 1
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2wp : yesf
lags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmovpat pse36 cl
flush dts acpi mmx fxsr sse sse2 ss ht tm pbe cid
bogomips : 4783.81
processor : 2
vendor_id : GenuineIntelcpu family : 15model : 2
model name : Intel(R) Xeon(TM) CPU 2.40GHzstepping : 7cpu MHz : 2393.067
cache size : 512 KB
physical id : 3
siblings : 2
core id : 3
cpu cores : 1
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmovpat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe cid
bogomips : 4783.88
processor : 3
vendor_id : GenuineIntelcpu family : 15model : 2
model name : Intel(R) Xeon(TM) CPU 2.40GHz
stepping : 7cpu MHz : 2393.067
cache size : 512 KB
physical id : 3
siblings : 2core
id : 3cpu
cores : 1f
div_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmovpat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe cid
bogomips : 4783.85