Hello
We have a database with mysql 5.1 on windows 2003 server with 2 Athlon Dual Core an 3 Gb of RAM.
Most of the query are select ones. Our bigger table is MyISAM with 311.000 rows and 730 Mb of size. 9.1 Mb on indexes.
Sometimes our load clim up to 100% for Mysql with enough free memory and keeps on that for 10 or more minutes. This make impossible to make INSERTS or UPDATES on our database. Have tried a lot of options without any result.
I’ve checked user and connections and doesn’t seem so high neither number of queriers (below 200). Also tried to stop service and startup again without possiitive result.
Our my.ini is the following one
default-storage-engine=INNODBsql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"max_connections=800query_cache_size=166Mtable_cache=1520tmp_table_size=59Mthread_cache_size=38myisam_max_sort_file_size=100Gmyisam_sort_buffer_size=59Mkey_buffer_size=258Mread_buffer_size=64Kread_rnd_buffer_size=256Ksort_buffer_size=256Kinnodb_additional_mem_pool_size=11Minnodb_flush_log_at_trx_commit=2innodb_log_buffer_size=6Minnodb_buffer_pool_size=500Minnodb_log_file_size=10Minnodb_thread_concurrency=5
And the shows status is
Variable_name ValueAborted_clients 0Aborted_connects 0Binlog_cache_disk_use 0Binlog_cache_use 0Bytes_received 275Bytes_sent 362Compression OFFConnections 21741Created_tmp_disk_tables 0Created_tmp_files 112Created_tmp_tables 0Delayed_errors 0Delayed_insert_threads 0Delayed_writes 0Flush_commands 1Innodb_buffer_pool_pages_data 1680Innodb_buffer_pool_pages_dirty 7Innodb_buffer_pool_pages_flushed 6187Innodb_buffer_pool_pages_free 29912Innodb_buffer_pool_pages_misc 408Innodb_buffer_pool_pages_total 32000Innodb_buffer_pool_read_ahead_rnd 8Innodb_buffer_pool_read_ahead_seq 20Innodb_buffer_pool_read_requests 15026092Innodb_buffer_pool_reads 908Innodb_buffer_pool_wait_free 0Innodb_buffer_pool_write_requests 31950Innodb_data_fsyncs 5236Innodb_data_pending_fsyncs 0Innodb_data_pending_reads 0Innodb_data_pending_writes 0Innodb_data_read 31789056Innodb_data_reads 1817Innodb_data_writes 15799Innodb_data_written 208686592Innodb_dblwr_pages_written 6187Innodb_dblwr_writes 714Innodb_log_waits 0Innodb_log_write_requests 2778Innodb_log_writes 8506Innodb_os_log_fsyncs 3639Innodb_os_log_pending_fsyncs 0Innodb_os_log_pending_writes 0Innodb_os_log_written 5750272Innodb_page_size 16384Innodb_pages_created 1Innodb_pages_read 1679Innodb_pages_written 6187Innodb_row_lock_current_waits 0Innodb_row_lock_time 31Innodb_row_lock_time_avg 15Innodb_row_lock_time_max 31Innodb_row_lock_waits 2Innodb_rows_deleted 124Innodb_rows_inserted 18Innodb_rows_read 13774215Innodb_rows_updated 5972Key_blocks_not_flushed 3Key_blocks_unused 207913Key_blocks_used 23456Key_read_requests 577318009Key_reads 39541Key_write_requests 760717Key_writes 877Last_query_cost 0.000000Max_used_connections 106Not_flushed_delayed_rows 0Open_files 683Open_streams 0Open_table_definitions 166Open_tables 603Opened_files 165561Opened_table_definitions 0Opened_tables 0Prepared_stmt_count 0Qcache_free_blocks 20591Qcache_free_memory 138828912Qcache_hits 1057840Qcache_inserts 157586Qcache_lowmem_prunes 0Qcache_not_cached 168364Qcache_queries_in_cache 25220Qcache_total_blocks 71203Queries 2862624Questions 6Rpl_status NULLSelect_full_join 0Select_full_range_join 0Select_range 0Select_range_check 0Select_scan 0Slave_open_temp_tables 0Slave_retried_transactions 0Slave_running OFFSlow_launch_threads 170Slow_queries 0Sort_merge_passes 0Sort_range 0Sort_rows 0Sort_scan 0Table_locks_immediate 1592928Table_locks_waited 15526Tc_log_max_pages_used 0Tc_log_page_size 0Tc_log_page_waits 0Threads_cached 15Threads_connected 28Threads_created 549Threads_running 15Uptime 4749Uptime_since_flush_status 4749
Any advice before jumping from a bridge