2013/8/14 Andy Wallace <awallace@stripped>
> 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 =
> 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
Have you done tests with query cache disabled? Depending on your workload
you might get some better performance.
> # 2 x numcpus
> # You can set .._buffer_pool_size up to 50 - 80 %
> # of RAM but beware of setting memory usage too high
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
This can be a performance killer, try to set it to 0 and make sure you
understand what it means (
Do you have innodb_file_per_table enabled?