Bug #53773 InnoDB extremely slow when compared with MyISAM for a simple stored procedure

• Submitted: 19 May 2010 4:14
• Modified: 19 May 2010 4:54
• Reporter: Roel Van de Paar
• Status: Not a Bug
• Category: MySQL Server: Stored Routines
• Severity: S1 (Critical)
• Version: 5.1.46
• OS: Any
• Assigned to: Roel Van de Paar
• CPU Architecture: Any

[19 May 2010 4:14] Roel Van de Paar

Description:
mysql> CALL setuplarge(1000000);       /* MyISAM */
Query OK, 1 row affected (37.97 sec)

mysql> CALL setuplarge(1000000);       /* InnoDB */
Query OK, 1 row affected (35 min 38.70 sec)  - give or take, machine used for other things, paused for few seconds etc.

How to repeat:
DELIMITER //
DROP PROCEDURE IF EXISTS setuplarge//
CREATE PROCEDURE setuplarge(IN numrows INT)
BEGIN
 DECLARE a INT;
 SET a = 1;
 DROP TABLE IF EXISTS a;
 CREATE TABLE `a` (`id` int,`p` char(20),PRIMARY KEY(`id`)) ENGINE=MyISAM;
 WHILE (a < numrows) DO
  INSERT INTO a VALUES (a,"       empty         ");
  SET a=a+1; 
 END WHILE;
END;
//
DELIMITER ;
CALL setuplarge(1000000);

Then s/ENGINE=MyISAM/ENGINE=InnoDB/ and re-run

[mysqld]
log_warnings=2
external-locking
connect_timeout = 1
log=general_log.txt
innodb_file_per_table
skip-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 512K
log-bin=log-bin.log
binlog_format=statement

[19 May 2010 4:36] Roel Van de Paar

mysql> set @@global.innodb_flush_log_at_trx_commit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL setuplarge(1000000); /* InnoDB */
Query OK, 1 row affected (48.67 sec)

[19 May 2010 4:54] Roel Van de Paar

Not a bug. Problem is that it's writing each row individually. Same problem can be created on MyISAM when using FLUSH TABLES after each row insert.

Painful, but ACID compliant.

http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_flush_log_at_t...