2013/8/14 Andy Wallace <awall...@ihouseweb.com> > 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 >
Can you provide the whole show create table for that table? > > 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 > Have you done tests with query cache disabled? Depending on your workload you might get some better performance. > # 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 > Why only 2GB if you have 16GB in the machine? If possible try to allocate more memory for MySQL. > 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 > This can be a performance killer, try to set it to 0 and make sure you understand what it means ( http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit ) > innodb_lock_wait_timeout=50 > innodb=on > > > Do you have innodb_file_per_table enabled? Cheers, Manuel
