List:General Discussion« Previous MessageNext Message »
From:Manuel Arostegui Date:August 14 2013 5:46pm
Subject:Re: Performance hiccoughs..
View as plain text  
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 =
> '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

Thread
Performance hiccoughs..Andy Wallace14 Aug
  • Re: Performance hiccoughs..Manuel Arostegui14 Aug
    • Re: Performance hiccoughs..Andy Wallace14 Aug