A few months ago we got an upgrade on our server hardware. No more Celerons! In fact, we moved from two Celerons down to our current server. Our loads are fine, in fact: we usually hover around 0.60 to 0.70 at our peak, with occasional spikes, and the forum is usually pretty responsive. At any rate, I think we're on a "default" my.cnf file that our host provided, and I know it could use some tweaking to improve efficiency. Here's all the info requested.
============================================
1. Dedicated server.
2. Specs:
CPU: Core 2 Duo E4400 (2GHz)
Memory: 4GB
Hard drives: 2x 80 GB SATA hard drive (one for web server, the second for MySQL)
OS: FreeBSD 6.2
Apache 2.2.9
PHP version: 5.2.6
MySQL version: 5.0.45
We are also running APC and Memcached
3. vB version: 3.6.0 currently, but will be upgrading to 3.7.2 within the week, now that updates are available for our plugins (especially iTrader).
4. No InnoDB tables. All are currently MyISAM so we can use fulltext. But since I'm not at all happy with the poor results returned with fulltext, we're considering switching back and converting tables over to InnoDB. Until Sphinx is available natively in vB, we're not going to hack up our forum to run it.
5. Unknown re: compilation of MySQL (may display in phpinfo?)
6. Stats:
Threads: 149,932
Posts: 3,199,715
Members: 16,833
7. my.cnf:
[mysqld]
user = mysql
port = 3306
socket = /tmp/mysql.sock
log = /usr/local/var/logs/access_log
log-slow-queries= /usr/local/var/logs/slow_log
tmpdir = /usr/tmp
enable-locking
skip-name-resolve
skip-host-cache
set-variable = max_connections=450
set-variable = max_connect_errors=10
set-variable = back_log=128
set-variable = max_allowed_packet=64M
set-variable = wait_timeout=45
set-variable = table_cache=1800
set-variable = key_buffer_size=128M
set-variable = sort_buffer_size=1M
set-variable = read_buffer_size=1M
set-variable = query_cache_size=96M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer_size=1M
set-variable = thread_cache_size=384
set-variable = query_prealloc_size=163840
set-variable = connect_timeout=10
set-variable = tmp_table_size=128M
set-variable = query_cache_limit=4M
set-variable = query_alloc_block_size=32768
old-passwords
#customer request
set-variable = read_rnd_buffer_size=5M
set-variable = bulk_insert_buffer_size=8M
set-variable = thread_concurrency=2
set-variable = query_cache_type=1
set-variable = innodb_file_per_table=1
set-variable = innodb_data_file_path=ibdata1:50M:autoextend
set-variable = innodb_additional_mem_pool_size=10M
set-variable = innodb_log_buffer_size=8M
set-variable = innodb_flush_log_at_trx_commit=1
set-variable = innodb_thread_concurrency=8
set-variable = innodb_status_file=1
set-variable = innodb_buffer_pool_size=512M
max_heap_table_size = 256M
8. MySQL stats from mysqladmin:
+---------------------------------+----------------------------------+
Variable_name Value
+---------------------------------+----------------------------------+
auto_increment_increment 1
auto_increment_offset 1
automatic_sp_privileges ON
back_log 128
basedir /usr/local/
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/local/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 /usr/local/var/
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 NO
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 NO
have_ssl NO
have_query_cache YES
have_raid NO
have_rtree_keys YES
have_symlink YES
hostname qs816.pair.com
init_connect
init_file
init_slave
innodb_additional_mem_pool_size 10485760
innodb_autoextend_increment 8
innodb_buffer_pool_awe_mem_mb 0
innodb_buffer_pool_size 268435456
innodb_checksums ON
innodb_commit_concurrency 0
innodb_concurrency_tickets 500
innodb_data_file_path ibdata1:50M:autoextend
innodb_data_home_dir
innodb_doublewrite ON
innodb_fast_shutdown 1
innodb_file_io_threads 4
innodb_file_per_table ON
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 8388608
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
Microsoft SQL Server 2005 Training on Design near Nashville Tennessee::
Have experience reading and drawing entity relationship (ER) diagrams. Apply guidelines when planning for table optimization.
http://www.dataschenk.com/SQL-Training/MOC2782-SQL-Server-2005.aspxHOME |
join_buffer_size 1044480
key_buffer_size 134217728
key_cache_age_threshold 300
key_cache_block_size 1024
key_cache_division_limit 100
language /usr/local/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 ON
log_bin OFF
log_bin_trust_function_creators OFF
log_error
log_queries_not_using_indexes OFF
log_slave_updates OFF
log_slow_queries ON
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 450
max_delayed_threads 20
max_error_count 64
max_heap_table_size 268435456
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 1000000
net_write_timeout 60
new OFF
old_passwords ON
open_files_limit 7408
optimizer_prune_level 1
optimizer_search_depth 62
pid_file /usr/local/var/qs816.pid
port 3306
preload_buffer_size 32768
profiling OFF
profiling_history_size 15
protocol_version 10
query_alloc_block_size 32768
query_cache_limit 4194304
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 5238784
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 OFF
skip_networking OFF
skip_show_database OFF
slave_compressed_protocol OFF
slave_load_tmpdir /usr/tmp/
slave_net_timeout 3600
slave_skip_errors OFF
slave_transaction_retries 10
slow_launch_time 2
socket /tmp/mysql.sock
sort_buffer_size 1048568
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 EDT
table_cache 1800
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 134217728
tmpdir /usr/tmp
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
tx_isolation REPEATABLE-READ
updatable_views_with_limit YES
version 5.0.45-log
version_comment Source distribution
version_compile_machine i386
version_compile_os unknown-freebsd6.2
wait_timeout 45
+---------------------------------+----------------------------------+
+-----------------------------------+------------+
Variable_name Value
+-----------------------------------+------------+
Aborted_clients 7852
Aborted_connects 2827
Binlog_cache_disk_use 0
Designing Microsoft SQL Server 2005 Databases::
and drawing entity relationship (ER) diagrams. Apply guidelines when planning for table optimization. SQL Server 2008 New Language Features. 21-Feb-2008
http://www.solidqualitylearning.com.au/course.aspx?coursecode=2782HOME |
TOAD for SQL Server Free Download at CleanSofts.com - Toad for SQL ::
Toads SQL Optimization for SQL Server is the most comprehensive tuning solution Database & SQL softs. New Software Development / Database & SQL
http://www.cleansofts.com/get/200/475/TOAD_for_SQL_Server.htmlHOME |
Binlog_cache_use 0
Bytes_received 2678962761
Bytes_sent 2022685766
Com_admin_commands 3843
Com_alter_db 0
Com_alter_table 0
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_call_procedure 0
Com_change_db 3916190
Com_change_master 0
Com_check 0
Com_checksum 0
Com_commit 0
Com_create_db 0
Com_create_function 0
Com_create_index 2
Com_create_table 0
Com_create_user 0
Com_dealloc_sql 0
Com_delete 317170
Com_delete_multi 2
Com_do 0
Com_drop_db 0
Com_drop_function 0
Com_drop_index 0
Com_drop_table 0
Com_drop_user 0
Com_execute_sql 0
Com_flush 0
Com_grant 0
Com_ha_close 0
Com_ha_open 0
Com_ha_read 0
Com_help 0
Com_insert 2271050
Com_insert_select 6958
Com_kill 0
Com_load 0
Com_load_master_data 0
Com_load_master_table 0
Com_lock_tables 0
Com_optimize 123
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 376063
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 20729607
Com_set_option 15236
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 4
Com_show_databases 2
Com_show_errors 0
Com_show_fields 293
Com_show_grants 0
Com_show_innodb_status 0
Com_show_keys 26
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 2949
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 3289
Com_show_storage_engines 0
Com_show_tables 15236
Com_show_triggers 0
Com_show_variables 540
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 5894346
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 3938733
Created_tmp_disk_tables 25037
Created_tmp_files 100796
Created_tmp_tables 2450923
Delayed_errors 0
Delayed_insert_threads 0
Delayed_writes 0
Flush_commands 1
Handler_commit 0
Handler_delete 1148634
Handler_discover 0
Handler_prepare 0
Handler_read_first 4658998
Handler_read_key 1147251616
Handler_read_next 2411357616
Handler_read_prev 20888418
Handler_read_rnd 607482663
Handler_read_rnd_next 1062947767
Handler_rollback 0
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 6621597
Handler_write 619543456
Innodb_buffer_pool_pages_data 0
Innodb_buffer_pool_pages_dirty 0
Innodb_buffer_pool_pages_flushed 0
Innodb_buffer_pool_pages_free 16384
Innodb_buffer_pool_pages_latched 0
Innodb_buffer_pool_pages_misc 0
Innodb_buffer_pool_pages_total 16384
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 16384
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 115980
Key_read_requests 6530113110
Key_reads 16310652
Key_write_requests 11642725
Key_writes 6307675
Last_query_cost 0.000000
Max_used_connections 201
Not_flushed_delayed_rows 0
Open_files 1963
Open_streams 0
Open_tables 1800
Opened_tables 2425
Prepared_stmt_count 0
Qcache_free_blocks 12715
Qcache_free_memory 46801976
Qcache_hits 16414530
Qcache_inserts 20533694
Qcache_lowmem_prunes 521770
Qcache_not_cached 215053
Qcache_queries_in_cache 37322
Qcache_total_blocks 88123
Questions 53895559
Rpl_status NULL
Select_full_join 4232
Select_full_range_join 23
Select_range 5162272
Select_range_check 0
Select_scan 3677409
Slave_open_temp_tables 0
Slave_retried_transactions 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 789
Sort_merge_passes 68318
Sort_range 3887138
Sort_rows 1496623384
Sort_scan 2076872
Table_locks_immediate 58463683
Table_locks_waited 104662
Tc_log_max_pages_used 0
Tc_log_page_size 0
Tc_log_page_waits 0
Threads_cached 199
Threads_connected 2
Threads_created 201
Threads_running 1
Uptime 1803105
Uptime_since_flush_status 1803105
+-----------------------------------+------------+
Uptime: 1803105 Threads: 2 Questions: 53895560 Slow queries: 789 Opens: 2425 Flush tables: 1 Open tables: 1800 Queries per second avg: 29.890
mysqladmin Ver 8.41 Distrib 5.0.45, for unknown-freebsd6.2 on i386
(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-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 20 days 20 hours 51 min 45 sec
Threads: 2 Questions: 53895560 Slow queries: 789 Opens: 2425 Flush tables: 1 Open tables: 1800 Queries per second avg: 29.890
+---------+---------+-----------+----------------+---------+------+-------+------------------+
Id User Host db Command Time State Info
+---------+---------+-----------+----------------+---------+------+-------+------------------+
3934467 shtv_16 localhost shtv_shtvforum Sleep 0
3938732 shtv_16 localhost Query 0 show processlist
+---------+---------+-----------+----------------+---------+------+-------+------------------+
Output from mysqlreport:
#
# Beginning report, 0 0:0:0
#
MySQL 5.0.45-log uptime 20 21:25:7 Wed Jul 9 17:28:10 2008
__ Key __________________________________________________ _______________
Buffer used 113.26M of 128.00M %Used: 88.49
Current 128.00M %Usage: 100.00
Write hit 45.79%
Read hit 99.75%
__ Questions __________________________________________________ _________
Total 53.99M 29.9/s
DMS 29.65M 16.4/s %Total: 54.91
QC Hits 16.44M 9.1/s 30.45
Com_ 3.97M 2.2/s 7.35
COM_QUIT 3.94M 2.2/s 7.30
-Unknown 6.73k 0.0/s 0.01
Slow 10 s 789 0.0/s 0.00 %DMS: 0.00 Log: ON
DMS 29.65M 16.4/s 54.91
SELECT 20.76M 11.5/s 38.46 70.04
UPDATE 5.90M 3.3/s 10.94 19.92
INSERT 2.28M 1.3/s 4.23 7.70
REPLACE 376.62k 0.2/s 0.70 1.27
DELETE 317.85k 0.2/s 0.59 1.07
Com_ 3.97M 2.2/s 7.35
change_db 3.92M 2.2/s 7.27
set_option 15.26k 0.0/s 0.03
show_tables 15.25k 0.0/s 0.03
__ SELECT and Sort __________________________________________________ ___
Scan 3.69M 2.0/s %SELECT: 17.75
Range 5.17M 2.9/s 24.90
Full join 4.24k 0.0/s 0.02
Range check 0 0/s 0.00
Full rng join 23 0.0/s 0.00
Sort scan 2.08M 1.2/s
Sort range 3.89M 2.2/s
Sort mrg pass 68.33k 0.0/s
__ Query Cache __________________________________________________ _______
Memory usage 52.17M of 96.00M %Used: 54.34
Block Fragmnt 14.13%
Hits 16.44M 9.1/s
Inserts 20.57M 11.4/s
Insrt:Prune 39.42:1 11.1/s
Hit:Insert 0.80:1
__ Table Locks __________________________________________________ _______
Waited 104.96k 0.1/s %Total: 0.18
Immediate 58.56M 32.4/s
__ Tables __________________________________________________ ____________
Open 1800 of 1800 %Cache: 100.00
Opened 2.42k 0.0/s
__ Connections __________________________________________________ _______
Max used 201 of 450 %Max: 44.67
Total 3.95M 2.2/s
__ Created Temp __________________________________________________ ______
Disk table 25.11k 0.0/s
Table 2.46M 1.4/s Size: 128.0M
File 100.82k 0.1/s
__ Threads __________________________________________________ ___________
Running 1 of 2
Cached 199 of 384 %Hit: 99.99
Created 201 0.0/s
Slow 0 0/s
__ Aborted __________________________________________________ ___________
Clients 7.89k 0.0/s
Connects 2.83k 0.0/s
__ Bytes __________________________________________________ _____________
Sent 3.18G 1.8k/s
Received 2.71G 1.5k/s
__ InnoDB Buffer Pool __________________________________________________
Usage 0 of 256.00M %Used: 0.00
Read hit 0.00%
Pages
Free 16.38k %Total: 100.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
#
# Interval report 1, +0 0:0:10
#
MySQL 5.0.45-log uptime 20 21:25:17 Wed Jul 9 17:28:20 2008
__ Key __________________________________________________ _______________
Buffer used 113.26M of 128.00M %Used: 88.49
Current 128.00M %Usage: 100.00
Write hit 38.00%
Read hit 99.95%
__ Questions __________________________________________________ _________
Total 408 40.8/s
DMS 204 20.4/s %Total: 50.00
QC Hits 123 12.3/s 30.15
Com_ 52 5.2/s 12.75
COM_QUIT 28 2.8/s 6.86
+Unknown 1 0.1/s 0.25
Slow 10 s 0 0/s 0.00 %DMS: 0.00 Log: ON
DMS 204 20.4/s 50.00
SELECT 140 14.0/s 34.31 68.63
UPDATE 42 4.2/s 10.29 20.59
INSERT 15 1.5/s 3.68 7.35
REPLACE 6 0.6/s 1.47 2.94
DELETE 1 0.1/s 0.25 0.49
Com_ 52 5.2/s 12.75
change_db 29 2.9/s 7.11
show_status 11 1.1/s 2.70
show_proces 10 1.0/s 2.45
__ SELECT and Sort __________________________________________________ ___
Scan 35 3.5/s %SELECT: 25.00
Range 32 3.2/s 22.86
Full join 0 0/s 0.00
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 16 1.6/s
Sort range 19 1.9/s
Sort mrg pass 0 0/s
__ Query Cache __________________________________________________ _______
Memory usage 52.41M of 96.00M %Used: 54.59
Block Fragmnt 14.09%
Hits 123 12.3/s
Inserts 136 13.6/s
Insrt:Prune 136:1 13.5/s
Hit:Insert 0.90:1
__ Table Locks __________________________________________________ _______
Waited 5 0.5/s %Total: 1.14
Immediate 434 43.4/s
__ Tables __________________________________________________ ____________
Open 1800 of 1800 %Cache: 100.00
Opened 0 0/s
__ Connections __________________________________________________ _______
Max used 201 of 450 %Max: 44.67
Total 30 3.0/s
__ Created Temp __________________________________________________ ______
Disk table 0 0/s
Table 26 2.6/s Size: 128.0M
File 0 0/s
__ Threads __________________________________________________ ___________
Running 1 of 3
Cached 198 of 384 %Hit: 100
Created 0 0/s
Slow 0 0/s
__ Aborted __________________________________________________ ___________
Clients 0 0/s
Connects 0 0/s
__ Bytes __________________________________________________ _____________
Sent 5.66M 565.9k/s
Received 137.66k 13.8k/s
__ InnoDB Buffer Pool __________________________________________________
Usage 0 of 256.00M %Used: 0.00
Read hit 0.00%
Pages
Free 16.38k %Total: 100.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
Output from tuning_primer.sh:
-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -
MySQL Version 5.0.45-log i386
Uptime = 20 days 20 hrs 31 min 29 sec
Avg. qps = 29
Total Questions = 53835303
Threads Connected = 2
Server has been running for over 48hrs.
It should be safe to follow these recommendations
To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service
SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 10 sec.
You have 789 out of 53835324 that take longer than 10 sec. to complete
Your long_query_time may be too high, I typically set this under 5 sec.
BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html
WORKER THREADS
Current thread_cache_size = 384
Current threads_cached = 199
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine
MAX CONNECTIONS
Current max_connections = 450
Current threads_connected = 2
Historic max_used_connections = 201
The number of used connections is 44% of the configured maximum.
Your max_connections variable seems to be fine.
MEMORY USAGE
Max Memory Ever Allocated : 2 G
Configured Max Per-thread Buffers : 3 G
Configured Max Global Buffers : 498 M
Configured Max Memory Limit : 4 G
Physical Memory : 3.62 G
Max memory limit exceeds 90% of physical memory
KEY BUFFER
Current MyISAM index space = 2 G
Current key_buffer_size = 128 M
Key cache miss rate is 1 : 400
Key buffer fill ratio = 100.00 %
You could increase key_buffer_size
It is safe to raise this up to 1/4 of total system memory;
assuming this is a dedicated database server.
QUERY CACHE
Query cache is enabled
Current query_cache_size = 96 M
Current query_cache_used = 52 M
Current query_cache_limit = 4 M
Current Query cache Memory fill ratio = 54.27 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size
SORT OPERATIONS
Current sort_buffer_size = 1 M
Current read_rnd_buffer_size = 4 M
Sort buffer seems to be fine
JOINS
Current join_buffer_size = 1.00 M
You have had 4221 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.
Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.
OPEN FILES LIMIT
Current open_files_limit = 7408 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine
TABLE CACHE
Current table_cache value = 1800 tables
You have a total of 132 tables
You have 1800 open tables.
Current table_cache hit rate is 74%, while 100% of your table cache is in use
You should probably increase your table_cache
TEMP TABLES
Current max_heap_table_size = 256 M
Current tmp_table_size = 128 M
Of 2447062 temp tables, 1% were created on disk
Created disk tmp tables ratio seems fine
TABLE SCANS
Current read_buffer_size = 1020 K
Current table scan ratio = 51 : 1
read_buffer_size seems to be fine
TABLE LOCKING
Current Lock Wait ratio = 1 : 559
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.
9. Other apps use PHP and MySQL, but they are lightly used. We also have a "test" vB install that is private, very low traffic.
10. Currently Active Users: 356 (233 members and 123 guests) Most online at once was about 450 users. Cookie time out is 15 minutes.
11. I'll ship a PM with location and user/password for phpinfo() to George.
12. No access to httpd.conf .
13. Files larger than 2GB:
/usr/local/var_old/ibdata1
/snap/usr/2008-07-06.0300/local/var_old/ibdata1
/snap/usr/2008-07-09.0300/local/var_old/ibdata1
(The "snap" directories are snapshot backups, and these are actually old database files we're no longer using...the host is going to delete them for us.)
14. No access to dmesg on our server (this is available to 'root' only).
========================================
Let me know if there's anything else you need. Thanks!
yeah questions #7 & #8 output stats would help :)
yeah the tuning-primer script has been around for a while just some suggestions for vB use might not be ideal
Thanks for the update, but I unfortunately have to make a change. The fulltext hasn't worked all that well, so we're looking at changing to the Sphinx search indexer, and changing my post, thread, user and other tables back to InnoDB where we had them previously. I can use the my.cnf you provided until I make the change, though, so it's not any wasted effort.
Our performance is very good now, but I'm just tweaking to future-proof the server.
Do you need new stats output to look at, or is the above enough to work with? I know we had different settings for InnoDB tables in our current my.cnf, if I recall. When I use mytop, I occasionally see some brief locking issues...my only fear is they'll get worse as traffic grows, where we never had the problem with InnoDB tables.
BTW, have you ever seen the tuning-primer.sh script? I posted the output of it above in my original post...
http://www.day32.com/MySQL/
Try the following in this exact order. You can ignore any of the suggestions that you have already done.
1. Upgrade MySQL 5.0.45 or 5.0.51 if possible and then upgrade PHP to 5.2.5 / 5.2.6 or if you have problems install PHP 4.4.8 (in either case install as apache module and NOT CGI. For windows based PHP try FastCGI method or ISAPI method). Backup your databases prior to mysql upgrades where possible using mysqldump via ssh telnet and not via admincp backup options or phpmyadmin which in some cases of large databases can result in incomplete backups!
2. Edit mysql server's /etc/my.cnf or c:my.ini for windows and place the following mysql server settings in /etc/my.cnf and restart mysql server afterwards. Make sure to restart mysql server everytime you make changes to your my.cnf for the changes to take effect.
If mysql doesn't restart properly after my.cnf changes and you're on VPS server, make sure skip-innodb entry is removed or commented out from below my.cnf
[mysqld]
skip-name-resolve
user = mysql
port = 3306
socket = /tmp/mysql.sock
#log = /usr/local/var/logs/access_log
#log-slow-queries= /usr/local/var/logs/slow_log
tmpdir = /usr/tmp
safe-show-database
#old_passwords
back_log = 50
skip-innodb
max_connections = 450
key_buffer_size = 512M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 4000
thread_cache_size = 256
wait_timeout = 20
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
net_buffer_length = 16384
max_connect_errors = 10
thread_concurrency = 4
concurrent_insert = 2
table_lock_wait_timeout = 30
read_rnd_buffer_size = 786432
bulk_insert_buffer_size = 8M
query_cache_limit = 5M
query_cache_size = 80M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = MyISAM
max_write_lock_count = 16
[mysqld_safe]
nice = -5
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
If you get mysql server gone away error messages, then keep increasing wait_timeout value in my.cnf by 60 second increments, then restart mysql after my.cnf changes for it to take effect. Test for a few days and see if you get less or eliminate that error message. If it still occurs, then keep repeating the 60 second increment until the message goes away. Each vB forum and server will have different optimal wait_timeout values depending on your vB forum traffic patterns and server hardware specifications.
3. Remove Eaccelerator or APC Cache if they're installed (check phpinfo.php url of yours to see) and instead install Xcache v1.2.1 final release or Xcache v1.2.2 http://xcache.lighttpd.net/wiki/Release-1.2.2 which seems to be a bit faster than APC Cache - http://www.vbulletin.com/forum/showthread.php?t=213267. Xcache site http://xcache.lighttpd.net/, documentation http://trac.lighttpd.net/trac/wiki/Docs and forums http://forum.lighttpd.net/forum/4
Remember to set in php.ini the values for xcache.size to 32M or 64M and for xcache.count to a value of equal to number of processor cores you have so single core cpu = 1 or single dual core cpu = 2 or single quad core cpu = 4 or dual cpus each with dual core = 4 or dual cpus each with quad core = 8.
4. Upgrade to vB 3.0.17 http://www.vbulletin.com/forum/showthread.php?t=209720 if you're on vB 3.0.xx or upgrade to vB 3.5.8 http://www.vbulletin.com/forum/showthread.php?t=221903 if you're on vB 3.5.x. Or if on vB 3.6.x, upgrade to vB 3.6.10 PL3 http://www.vbulletin.com/forum/showthread.php?t=277945.
But ultimately, the latest stable vB 3.7.2 PL1 is highly recommended http://www.vbulletin.com/forum/showthread.php?postid=1585047. You can use my method of upgrading outlined at http://www.vbulletin.com/forum/showthread.php?t=187770 which is essentially same in that you make a copy of your live database and import it into a new empty database and point vB 3.7.0 config.php to that new imported database name, so you essentially do an upgrade on a copy of your database, leaving original database intact in case of any problems. This method also allows you to run the original database on a different directory so to run both original forum/database along side the upgraded forum/database so you can easily revert all changed templates on upgraded forum and then using old forum/database transfer or port your custom style/images etc to the new upgrade database.
Read each versions listed thread to understand the changes that have occured etc.
5. If you just upgraded to vB 3.5.x/3.6.x try to disable these 4 options:
Admin CP -> vBulletin Options -> Forums Home Page Options -> Display Logged in Users?
Admin CP -> vBulletin Options -> Forum Display Options (forumdisplay) -> Show Users Browsing Forums
Admin CP -> vBulletin Options -> Thread Display Options -> Show Users Browsing Thread
Admin CP -> vBulletin Options -> Message Searching Options -> Automatic Similar Thread search
Or relevant sections in vB 3.7.x
6. Check phpinfo.php url to see if you have mod_gzip (Apache 1.3.x) or mod_deflate (Apache 2.x) loaded/installed (on phpinfo.php url page in browser you can do CTRL+F to bring up find prompt and type in mod_gzip or mod_deflate to quickly see). If you have either mod_gzip or mod_deflate loaded, then ensure vB admincp gzip compression is disabled since it's the same thing as mod_gzip or mod_deflate and double compression will just increase cpu loads. If you don't have mod_gzip or mod_deflate installed, then DO NOT set vB admincp gzip compression to a level higher than 1. Higher than 1, will only increase cpu loads unncessarily.
 Where's The Advantage In Windows Genuine Advantage?
 Stocks Bounce After S&P Joins Bear Market |
HOME