Good afternoon,
I have recently converted a large table from MyISAM to InnoDB and am
experiencing severe performance issues because of it. HTTP response
times have gone from avg .25 seconds to avg 2-3 seconds. Details follow:
PHP/MySQL website, no memcached, 3 web nodes that interact with DB, one
that serves images, one master DB that serves all reads/writes, backup
DB that only serves for backup/failover at this time (app being changed
to split reads/writes, not yet).
The one table that I converted is 130M rows, around 10GB data MyISAM to
22GB InnoDB. There are around 110 tables on the DB total.
My.cnf abbreviated settings:
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 3G
sort_buffer_size = 45M
max_allowed_packet = 16M
table_cache = 2048
tmp_table_size = 512M
max_heap_table_size = 512M
myisam_sort_buffer_size = 512M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
thread_cache_size = 300
query_cache_type = 1
query_cache_limit = 1M
query_cache_size = 600M
thread_concurrency = 8
max_connections = 2048
sync_binlog = 1
innodb_buffer_pool_size = 14G
innodb_log_file_size = 20M
innodb_flush_log_at_trx_commit=1
innodb_flush_method = O_DIRECT
skip-innodb-doublewrite
innodb_support_xa = 1
innodb_autoextend_increment = 16
innodb_data_file_path = ibdata1:40G:autoextend
We're seeing a significantly higher percentage of IO wait on the system,
averaging 20% now with the majority of that being user IO. The system
is not swapping at all.
Any ideas for what to check or modify to increase the performance here
and let MyISAM and InnoDB play better together? The plan is to convert
all tables to InnoDB which does not seem like a great idea at this
point, we're considering moving back to MyISAM.
Thanks!
Josh Miller, RHCE
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
