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.
[email protected]
(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
