Re: Help needed to tune Innodb on ZFS (on Solaris)
Hi all, You can read this article, written by a SUN benchmarking guru (hi Dimitri :) ). Best regards. Web link : http://dimitrik.free.fr/db_STRESS_BMK_Part2_ZFS.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help needed to tune Innodb on ZFS (on Solaris)
Hi, I installed Mysql 5.0.45 on Solaris 10 01/08 configured to run the datadir on a ZFS filesystem on a Raid1 pool. Both myisam and innodb tables are on the same filesystem. Innodb is configured to run with a buffer_pool_size=256M, with doublewrite set to OFF and with file_per_table to Off. The log files are 2 x 48 MB each. The problem I have is that importing the same 7 MB sql dump takes 9 seconds if engine=Myisam and 98 when engine is Innodb. Following some advice found in various bloggers I created the zfs filesystem I created with a recordsize of 16K, and set flush_log_at_trx_commit to 0. In some way the benchmark have improved, but I still find Innodb much slower than Myisam. Does anyone have any experience on this particular configuration? Is there any other trick to follow to improve Innodb's performance on ZFS? Three more things that I noticed: - setting innodb_flush_method=O_DIRECT causes mysql to complain that the directio() is not implemented. - dropping the db and reimporting it from the dump is slower at every iteration. Is there any way to keep the next reimports as fast as the first one without recreating from scratch the ibdata files? - why using innodb_file_per_tables is so much slower than running innodb with a single table space? Thanks in advance for any help, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help needed to tune Innodb on ZFS (on Solaris)
On Wed, Apr 9, 2008 at 5:51 AM, Nico Sabbi [EMAIL PROTECTED] wrote: The problem I have is that importing the same 7 MB sql dump takes 9 seconds if engine=Myisam and 98 when engine is Innodb. Is autocommit turned off? http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html When importing data into InnoDB, make sure that MySQL does not have autocommit mode enabled because that requires a log flush to disk for every insert. To disable autocommit during your import operation, surround it with SET AUTOCOMMIT and COMMIT statements: SET AUTOCOMMIT=0; ... SQL import statements ... COMMIT; There is also a note in there about forcedirectio and certain solaris setups. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help needed to tune Innodb on ZFS (on Solaris)
Il Wednesday 09 April 2008 19:40:32 Rob Wultsch ha scritto: On Wed, Apr 9, 2008 at 5:51 AM, Nico Sabbi [EMAIL PROTECTED] wrote: The problem I have is that importing the same 7 MB sql dump takes 9 seconds if engine=Myisam and 98 when engine is Innodb. Is autocommit turned off? http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html When importing data into InnoDB, make sure that MySQL does not have autocommit mode enabled because that requires a log flush to disk for every insert. To disable autocommit during your import operation, surround it with SET AUTOCOMMIT and COMMIT statements: SET AUTOCOMMIT=0; ... SQL import statements ... COMMIT; yes, autocommit is off There is also a note in there about forcedirectio and certain solaris setups. I read it, but directio seems to be unavailable on ZFS (as fas as I can tell) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
