List:General Discussion« Previous MessageNext Message »
From:Andy Wallace Date:August 14 2013 5:08pm
Subject:Performance hiccoughs..
View as plain text  
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.
awallace@stripped
(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
Thread
Performance hiccoughs..Andy Wallace14 Aug
  • Re: Performance hiccoughs..Manuel Arostegui14 Aug
    • Re: Performance hiccoughs..Andy Wallace14 Aug