Need Suggestions for my.ini
"Shaun Adams" <[email protected]>
| Newsgroups | gmane.comp.db.mysql.windows |
|---|---|
| Organization | Hypraze Solutions, LLC |
| Message-ID | <[email protected]> |
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