Bug #29475 InnoDB COMPACT format addition reduces INSERT performance by 10 %

• Submitted: 2 Jul 2007 4:28
• Modified: 17 Aug 2007 14:06
• Reporter: Lachlan Mulcahy
• Status: Won't fix
• Category: MySQL Server: InnoDB storage engine
• Severity: S5 (Performance)
• Version: 5.0
• OS: Any
• Assigned to: Marko Mäkelä
• CPU Architecture: Any
• Tags: performance innodb compact

[2 Jul 2007 4:28] Lachlan Mulcahy

Description:
The addition of the COMPACT row format to InnoDB in MySQL 5.0 has caused a significant performance decrease in INSERTs.

Raw INSERT performance can reduce by up to 30%+ in many cases.

The get_offsets() function seems to be the culprit.

How to repeat:
n/a

Suggested fix:
n/a

[17 Aug 2007 14:05] Heikki Tuuri

The latest tests from the user indicate that the performance regression in a test where MySQL-5.0 loads about 100 GB of tables is about 10 % in the InnoDB compact format, compared to a version of 5.0 where the compact format code has been stripped off.

But the user concluded that the 10 % - 20 % saving in disk space in the compact format is worth the performance regression for InnoDB.

Another matter is that the user measured that the load speed in 5.0 is about 30 % slower in 5.0 for MyISAM, compared to 4.0-64bit version. MySQL-5.0's parser is so much slower. There is a new bug report about this:
http://bugs.mysql.com/bug.php?id=29921

[17 Aug 2007 14:06] Heikki Tuuri

Putting this to status 'Won't fix', at least until we have resources to look into the C code optimization again...