Hey all - We have been focusing on performance in our systems a lot lately, and have made some pretty good progress. Upgrading the mySQL engine from 5.1 to 5.5 was eye-opening.
But there are still issues, and one in particular is vexing. It seems like a tuning problem for sure - I notice this even at the command-line interface. I will have a update command: update my_table set test_column = 'tester_value' where key_value = 'a-test-key'; key_value is the primary key for my_table, which is an INNODB table, about 50MB, 96K rows If I run this 10 times with different key values, most of the time, it will return pretty much instantaneously. But at least once, it will take 10, 20, 30 seconds to return. This affects our applications as well - operations that are generally fast will suddenly be very, very slow... and then back to fast. OS: SunOS 5.10 SQL version: 5.5.33-log MySQL Community Server (GPL) Hardware: Virtual Machine (VMWare), 4 cpus - 16GB RAM Tuning section of my.cnf: # tuning key_buffer_size=512M max_allowed_packet=16M table_open_cache=512 sort_buffer_size=10M read_buffer_size=10M read_rnd_buffer_size=8M myisam_sort_buffer_size=512M thread_cache_size=8 query_cache_type=1 query_cache_size=1024M query_cache_limit=10M # 2 x numcpus #thread_concurrency=4 #innodb_thread_concurrency=0 #innodb_read_io_threads=16 #innodb_write_io_threads=16 # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size=2048M innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size=100M innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_lock_wait_timeout=50 innodb=on -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- "Sometimes it pays to stay in bed on Monday, rather than spending the rest of the week debugging Monday's code." - Christopher Thompson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql