List:MySQL on Win32« Previous MessageNext Message »
From:Shaun Adams Date:January 16 2006 7:57pm
Subject:Need Suggestions for my.ini
View as plain text  
Hi All,
 
I've got a big customer who is complaining about speed issues.  Could you
take a look at the information below and provide me with any suggestions for
modifying my mysql config.  We've got developers woking on optimizing the
code.  Upgrading to MySQL 4.1.x is not an option.  We are using 4.0.26.
I've got a Windows 2003 Web Edition running IIS 6.0 with 2Gigs of Ram.
 
If you could, please provide any suggestions for the same system running
4Gigs of RAM also.
 
Thanks in advance,
Shaun
 
-------------------------
SHOW STATUS:
 
Aborted_clients 484 
Aborted_connects 0 
Bytes_received 1250266299 
Bytes_sent 2891930113 
Com_admin_commands 475 
Com_alter_table 155 
Com_analyze 0 
Com_backup_table 0 
Com_begin 0 
Com_change_db 3050514 
Com_change_master 0 
Com_check 0 
Com_commit 0 
Com_create_db 0 
Com_create_function 0 
Com_create_index 0 
Com_create_table 25 
Com_delete 201562 
Com_delete_multi 0 
Com_drop_db 0 
Com_drop_function 0 
Com_drop_index 0 
Com_drop_table 25 
Com_flush 1 
Com_grant 0 
Com_ha_close 0 
Com_ha_open 0 
Com_ha_read 0 
Com_insert 593441 
Com_insert_select 25 
Com_kill 0 
Com_load 0 
Com_load_master_data 0 
Com_load_master_table 0 
Com_lock_tables 0 
Com_optimize 0 
Com_purge 0 
Com_rename_table 1 
Com_repair 0 
Com_replace 0 
Com_replace_select 0 
Com_reset 0 
Com_restore_table 0 
Com_revoke 0 
Com_rollback 0 
Com_savepoint 0 
Com_select 7387501 
Com_set_option 0 
Com_show_binlog_events 0 
Com_show_binlogs 0 
Com_show_create 150 
Com_show_databases 47 
Com_show_fields 484 
Com_show_grants 0 
Com_show_innodb_status 0 
Com_show_keys 215 
Com_show_logs 0 
Com_show_master_status 1 
Com_show_new_master 0 
Com_show_open_tables 0 
Com_show_processlist 1 
Com_show_slave_hosts 475 
Com_show_slave_status 0 
Com_show_status 3 
Com_show_tables 728 
Com_show_variables 47 
Com_slave_start 0 
Com_slave_stop 0 
Com_truncate 0 
Com_unlock_tables 1 
Com_update 4385530 
Com_update_multi 0 
Connections 161333 
Created_tmp_disk_tables 17134 
Created_tmp_files 2452 
Created_tmp_tables 40105 
Delayed_errors 0 
Delayed_insert_threads 0 
Delayed_writes 0 
Flush_commands 2 
Handler_commit 0 
Handler_delete 225909 
Handler_read_first 61787 
Handler_read_key 3478588635 
Handler_read_next 3961706275 
Handler_read_prev 170680 
Handler_read_rnd 342360509 
Handler_read_rnd_next 249523328 
Handler_rollback 0 
Handler_update 300532407 
Handler_write 301233540 
Key_blocks_used 373653 
Key_read_requests 2867123152 
Key_reads 3484548 
Key_write_requests 6523779 
Key_writes 5127892 
Max_used_connections 13 
Not_flushed_delayed_rows 0 
Not_flushed_key_blocks 0 
Open_files 79 
Open_streams 0 
Open_tables 42 
Opened_tables 41877 
Qcache_free_blocks 8258 
Qcache_free_memory 49483512 
Qcache_hits 37699474 
Qcache_inserts 7212384 
Qcache_lowmem_prunes 616712 
Qcache_not_cached 175112 
Qcache_queries_in_cache 15330 
Qcache_total_blocks 39067 
Questions 53482457 
Rpl_status NULL 
Select_full_join 8160 
Select_full_range_join 0 
Select_range 37368 
Select_range_check 0 
Select_scan 492781 
Slave_open_temp_tables 0 
Slave_running OFF 
Slow_launch_threads 1 
Slow_queries 166 
Sort_merge_passes 1244 
Sort_range 320864 
Sort_rows 80821426 
Sort_scan 263135 
Table_locks_immediate 32134520 
Table_locks_waited 2468 
Threads_cached 6 
Threads_connected 3 
Threads_created 35 
Threads_running 3 
Uptime 4382645 
 
-----------------------------
SHOW VARIABLES
 

back_log 50 
basedir d:\mysql\ 
binlog_cache_size 32768 
bulk_insert_buffer_size 8388608 
character_set latin1 
concurrent_insert ON 
connect_timeout 5 
convert_character_set   
datadir d:\mysql\data\ 
default_week_format 0 
delay_key_write ON 
delayed_insert_limit 100 
delayed_insert_timeout 300 
delayed_queue_size 1000 
flush OFF 
flush_time 1800 
ft_boolean_syntax + -><()~*:""&| 
ft_max_word_len 254 
ft_max_word_len_for_sort 20 
ft_min_word_len 4 
ft_stopword_file (built-in) 
have_bdb NO 
have_crypt NO 
have_innodb DISABLED 
have_isam YES 
have_openssl NO 
have_query_cache YES 
have_raid NO 
have_symlink YES 
init_file   
innodb_additional_mem_pool_size 1048576 
innodb_autoextend_increment 8 
innodb_buffer_pool_size 8388608 
innodb_data_file_path   
innodb_data_home_dir   
innodb_fast_shutdown ON 
innodb_file_io_threads 4 
innodb_flush_log_at_trx_commit 1 
innodb_flush_method   
innodb_force_recovery 0 
innodb_lock_wait_timeout 50 
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_table_locks ON 
innodb_thread_concurrency 8 
interactive_timeout 28800 
join_buffer_size 520192 
key_buffer_size 402653184 
language d:\mysql\share\english\ 
large_files_support ON 
license GPL 
local_infile ON 
log OFF 
log_bin ON 
log_error .\SVR01170.err 
log_slave_updates OFF 
log_slow_queries OFF 
log_update OFF 
log_warnings 1 
long_query_time 10 
low_priority_updates OFF 
lower_case_file_system OFF 
lower_case_table_names 1 
max_allowed_packet 1048576 
max_binlog_cache_size 4294967295 
max_binlog_size 1073741824 
max_connect_errors 10 
max_connections 100 
max_delayed_threads 20 
max_heap_table_size 16777216 
max_insert_delayed_threads 20 
max_join_size 4294967295 
max_relay_log_size 0 
max_seeks_for_key 4294967295 
max_sort_length 1024 
max_tmp_tables 32 
max_user_connections 0 
max_write_lock_count 4294967295 
myisam_max_extra_sort_file_size 107374182400 
myisam_max_sort_file_size 107374182400 
myisam_recover_options OFF 
myisam_repair_threads 1 
myisam_sort_buffer_size 214958080 
net_buffer_length 16384 
net_read_timeout 30 
net_retry_count 10 
net_write_timeout 60 
new OFF 
open_files_limit 0 
pid_file d:\mysql\data\SVR01170.pid 
port 3306 
protocol_version 10 
query_alloc_block_size 8192 
query_cache_limit 1048576 
query_cache_size 67108864 
query_cache_type ON 
query_cache_wlock_invalidate OFF 
query_prealloc_size 8192 
range_alloc_block_size 2048 
read_buffer_size 126976 
read_only OFF 
read_rnd_buffer_size 520192 
rpl_recovery_rank 0 
server_id 28 
skip_external_locking ON 
skip_networking OFF 
skip_show_database OFF 
slave_net_timeout 3600 
slow_launch_time 2 
sort_buffer_size 524280 
sql_mode 0 
table_cache 256 
table_type MYISAM 
thread_cache_size 8 
thread_stack 196608 
timezone US Eastern Standard Time 
tmp_table_size 108003328 
tmpdir C:\WINDOWS\TEMP\ 
transaction_alloc_block_size 8192 
transaction_prealloc_size 4096 
tx_isolation REPEATABLE-READ 
version 4.0.26-log 
version_comment Official MySQL binary 
version_compile_os Win32 
wait_timeout 28800 
 
 


Thread
Need Suggestions for my.iniShaun Adams16 Jan