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) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S5 (Performance)
Version:all OS:Any
Assigned to: 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] Umesh Shastry
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: kevin.lewis@oracle.com <>
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.