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_size48M 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. awallace@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
Aug 14
Manuel Arostegui Re: Performance hiccoughs..
Aug 14, 2013; 19:46
Manuel Arostegui
Re: Performance hiccoughs..
Aug 14
Andy Wallace Re: Performance hiccoughs..
Aug 14, 2013; 10:52
Andy Wallace
Re: Performance hiccoughs..
Search
Lasso Programming
This site manages and broadcasts several email lists pertaining to Lasso Programming and technologies related and used by Lasso developers. Sign up today!