Bug #71014 two many times of memset decreate the performance under heavy insert
Submitted: 26 Nov 2013 14:25 Modified: 1 Apr 2014 13:50
Reporter: Fangxin Flou (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:All Version OS:Any
Assigned to: CPU Architecture:Any
Tags: Memory, performance

[26 Nov 2013 14:25] Fangxin Flou
Description:
for each inserted row, when allocate memory for row header, it need 3 times memset call, which cause high user cpu usage, by combine it into one memset call, about 9% percent of CPU usage can be saveed under heavy insert activity.

How to repeat:
N/A

Suggested fix:
diff -rc --exclude=sql_yacc.cc --exclude=sql_yacc.h --exclude='*.orig' mysql-5.7.2/storage/innobase/row/row0ins.cc mysql-5.7.2-patched/storage/innobase/row/row0ins.cc
*** mysql-5.7.2/storage/innobase/row/row0ins.cc	2013-09-11 20:10:40.000000000 +0800
--- mysql-5.7.2-patched/storage/innobase/row/row0ins.cc	2013-11-25 20:37:27.000000000 +0800
***************
*** 143,148 ****
--- 143,149 ----
  	const dict_col_t*	col;
  	dfield_t*		dfield;
  	byte*			ptr;
+ 	byte*			ptr2;
  
  	row = node->row;
  	table = node->table;
***************
*** 157,163 ****
  
  	dfield = dtuple_get_nth_field(row, dict_col_get_no(col));
  
! 	ptr = static_cast<byte*>(mem_heap_zalloc(heap, DATA_ROW_ID_LEN));
  
  	dfield_set_data(dfield, ptr, DATA_ROW_ID_LEN);
  
--- 158,165 ----
  
  	dfield = dtuple_get_nth_field(row, dict_col_get_no(col));
  
! 	ptr2 = static_cast<byte*>(mem_heap_zalloc(heap, DATA_ROW_ID_LEN + DATA_TRX_ID_LEN + DATA_ROLL_PTR_LEN));
! 	ptr = ptr2;
  
  	dfield_set_data(dfield, ptr, DATA_ROW_ID_LEN);
  
***************
*** 168,174 ****
  	col = dict_table_get_sys_col(table, DATA_TRX_ID);
  
  	dfield = dtuple_get_nth_field(row, dict_col_get_no(col));
! 	ptr = static_cast<byte*>(mem_heap_zalloc(heap, DATA_TRX_ID_LEN));
  
  	dfield_set_data(dfield, ptr, DATA_TRX_ID_LEN);
  
--- 170,177 ----
  	col = dict_table_get_sys_col(table, DATA_TRX_ID);
  
  	dfield = dtuple_get_nth_field(row, dict_col_get_no(col));
! 	/* ptr = static_cast<byte*>(mem_heap_zalloc(heap, DATA_TRX_ID_LEN)); */
! 	ptr = ptr2 + DATA_ROW_ID_LEN;
  
  	dfield_set_data(dfield, ptr, DATA_TRX_ID_LEN);
  
***************
*** 179,185 ****
  	col = dict_table_get_sys_col(table, DATA_ROLL_PTR);
  
  	dfield = dtuple_get_nth_field(row, dict_col_get_no(col));
! 	ptr = static_cast<byte*>(mem_heap_zalloc(heap, DATA_ROLL_PTR_LEN));
  
  	dfield_set_data(dfield, ptr, DATA_ROLL_PTR_LEN);
  }
--- 182,189 ----
  	col = dict_table_get_sys_col(table, DATA_ROLL_PTR);
  
  	dfield = dtuple_get_nth_field(row, dict_col_get_no(col));
! 	/* ptr = static_cast<byte*>(mem_heap_zalloc(heap, DATA_ROLL_PTR_LEN)); */
! 	ptr = ptr2 + DATA_ROW_ID_LEN + DATA_TRX_ID_LEN;
  
  	dfield_set_data(dfield, ptr, DATA_ROLL_PTR_LEN);
  }
[26 Nov 2013 14:31] Fangxin Flou
previous file is empty, upload new one

Attachment: twomanymemset.log (application/octet-stream, text), 2.13 KiB.

[26 Nov 2013 16:28] MySQL Verification Team
I have inspected a code very seriously and found out that this small optimization is legitimate. The only thing that I had to check is the deallocation of the memory, but as local heap is deallocated in it's entirety, and not pointer by pointer, there is no need for the additional research and code changes.
[1 Apr 2014 13:50] Daniel Price
Fixed as of 5.5.38, 5.6.18, 5.7.5, and here's the changelog entry:

For each insert, "memset" would be called three times to allocate memory
for system fields. To reduce CPU usage, the three "memset" calls are now
combined into a single call. 

Thank you for the bug report.
[31 May 2014 13:52] Laurynas Biveinis
$ bzr log -r 4614
------------------------------------------------------------
revno: 4614
committer: Thirunarayanan B<thirunarayanan.balathandayuth@oracle.com>
branch nick: mysql-5.5
timestamp: Tue 2014-04-01 10:46:13 +0530
message:
  Bug #17858679	TOO MANY TIMES OF MEMSET DECREASE
  		THE PERFORMANCE UNDER HEAVY INSERT
  Problem:
  	There are three memset call to allocate memory for system fields
  in each insert.
  
  Solution:
  	Instead of calling it in 3 times, we can combine it into
  one memset call. It will reduce the CPU usage under heavy insert.
  
  	Approved by Marko rb-4916
[31 May 2014 13:52] Laurynas Biveinis
$ bzr log -r 4615
------------------------------------------------------------
revno: 4615
committer: Thirunarayanan B<thirunarayanan.balathandayuth@oracle.com>
branch nick: mysql-5.5
timestamp: Tue 2014-04-01 11:36:58 +0530
message:
  Bug #17858679   TOO MANY TIMES OF MEMSET DECREASE
  		THE PERFORMANCE UNDER HEAVY INSERT
  	Fixing the build problem in 5.5.