Bug #68725 UNIV_MEM_DEBUG needlessly slow, especially with UNIV_ZIP_DEBUG

• Submitted: 20 Mar 2013 8:30
• Modified: 24 Mar 2016 11:28
• Reporter: Laurynas Biveinis (OCA)
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S5 (Performance)
• Version: all
• OS: Any
• CPU Architecture: Any

[20 Mar 2013 8:30] Laurynas Biveinis

Description:
A combination of UNIV_MEM_DEBUG and UNIV_ZIP_DEBUG slows down the server for writes to compressed tables significantly and apparently needlessly, as there are low-hanging-fruit optimization opportunities there.

UNIV_MEM_DEBUG enables mem_init_buf() and mem_erase_buf(), which both look:

for each byte
       if (ut_rnd_gen_ibool()) {
                       *ptr = value1;
               } else {
                       *ptr = value2;
       }

That's a lot of pseudo-random bools generated, probably defeats the CPU branch predictor, and probably not that good cache behavior too.

Strictly speaking, it's UNIV_MEM_DEBUG-only issue, but UNIV_ZIP_DEBUG compounds it significantly.

How to repeat:
Profile the following MTR testcase. I did it on 5.6, bzr history suggests the issue should have been here since day zero.

--innodb_strict_mode --innodb_file_per_table --innodb_file_format=Barracuda

CREATE TABLE t1 (a INT, b INT, INDEX b(b), INDEX a(a)) ENGINE=INNODB ROW_FORMAT=compressed KEY_BLOCK_SIZE=16;

let $1=1000;

BEGIN;
--disable_query_log
while ($1)
{
  eval INSERT INTO t1 (a, b) VALUES ($1, $1);
  dec $1;
}
--enable_query_log
COMMIT;

SELECT COUNT(*) FROM t1;

DROP TABLE t1;

Suggested fix:
Replacing these mem_init_buf() and mem_erase_buf() loops with simple memset cuts the testcase running time 3x, measured very non-scientifically.

[2 Jan 2014 4:12] MySQL Verification Team

Hello Laurynas,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh

[24 Mar 2016 11:28] Laurynas Biveinis

Fixed in 5.7.4 by removing UNIV_MEM_DEBUG under

commit e5d9961b637f871b34d7741b9f3db336c59ddec4
Author: [e-mail] <>
Date:   Tue Feb 25 23:42:26 2014 -0600

    WL#7628 - Remove innodb_use_sys_malloc & innodb_additional_mem_pool_size
    system variables in 5.7

Leaving this as Verified in case you want to add a release note entry.