From: Jorge Bastos Date: January 16 2006 11:21pm Subject: Fw: Need Suggestions for my.ini List-Archive: http://lists.mysql.com/win32/17989 Message-Id: <005c01c61af3$9624b140$0101a8c0@pcjorge> MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit In the start of mysqld-nt give the parameter "--skip-name-resolve" It will help alot. Jorge ----- Original Message ----- From: "Shaun Adams" To: Sent: Monday, January 16, 2006 7:57 PM Subject: Need Suggestions for my.ini > 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 > > >