Here are a couple ideas: * Decrease innodb_autoextend_increment to 8 or even 4. You may see additional IO wait because you're pre-allocating space in chunks disproportinate to what you immediately need, causing bursty performance. * If your remaining MyISAM tables don't need it, take 2GB of the key_buffer alocation and put it towards the innodb buffer pool
What are the system's specs? What's it's underlying storage? What flags were used when you created the filesystem(s)? What OS/Version of MySQL are you running? Could you send us some iostat output? Thanks and good luck, -Aaron On Thu, Sep 4, 2008 at 1:26 PM, Josh Miller <[EMAIL PROTECTED]>wrote: > 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] > >
