Bug #80778 FR: please make frequency of checks for buf_dbg_counter configurable
Submitted: 17 Mar 2016 11:16
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:8.0.0 OS:Any
Assigned to: CPU Architecture:Any

[17 Mar 2016 11:16] Shane Bester 
Description:
The buffer pool validation code on debug builds makes running with a larger innodb_buffer_pool_size too slow as buf_validate() is called fairly often.

This is an extension of the original bug:
Bug 16068056 - INNODB CALLS BUF_VALIDATE() TOO OFTEN WITH UNIV_DEBUG 

How to repeat:
On a DEBUG build run this testcase with a 8M innodb_buffer_pool_size and compare it to a larger 8G innodb_buffer_pool_size:

drop table if exists t;
create table t(a int unsigned not null, b int, primary key(a),key(b))engine=innodb;
set @a:=10;
insert ignore into t(a,b) values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);
insert ignore into t(a,b) select @a:=@a+1,t.b from t,t a,t b,t c,t d,t e;

Suggested fix:
Can occurrences of 5771 settable by some global variable or at least a cmake variable, instead of hardcoded?   Or make buf_validate() faster, somehow..

E:\git\mysql-trunk\storage\innobase\buf\buf0buf.cc (9 hits)
	Line 348: static ulint	buf_dbg_counter	= 0;
	Line 3055: 		ut_a(++buf_dbg_counter % 5771 || buf_validate());
	Line 3120: 	ut_a(++buf_dbg_counter % 5771 || buf_validate());
	Line 3583: 		     || ++buf_dbg_counter % 5771
	Line 3947: 	     || ++buf_dbg_counter % 5771
	Line 4107: 	     || ++buf_dbg_counter % 5771
	Line 4214: 	ut_a(++buf_dbg_counter % 5771 || buf_validate());
	Line 4312: 	     || ++buf_dbg_counter % 5771
	Line 4919: 	ut_a(++buf_dbg_counter % 5771 || buf_validate());
[17 Mar 2016 11:18] MySQL Verification Team 
the perf output:

    68.49%    67.72%   mysqld  mysqld               [.] buf_LRU_validate                                                                                                                                                          
             |
             --- buf_LRU_validate
                |          
                |--100.00%-- buf_pool_validate_instance
                |          |          
                |          |--99.12%-- buf_page_get_gen
                |          |          |          
                |          |          |--79.65%-- btr_cur_search_to_nth_level
                |          |          |          |          
                |          |          |          |--54.82%-- btr_pcur_open_low
                |          |          |          |          row_ins_clust_index_entry_low
                |          |          |          |          row_ins_clust_index_entry
                |          |          |          |          row_ins_step
                |          |          |          |          row_insert_for_mysql
                |          |          |          |          ha_innobase::write_row
                |          |          |          |          handler::ha_write_row
                |          |          |          |          write_record
                |          |          |          |          Query_result_insert::send_data
                |          |          |          |          end_send
                |          |          |          |          evaluate_join_record
                |          |          |          |          QEP_tmp_table::end_send
                |          |          |          |          sub_select_op
                |          |          |          |          sub_select_op
                |          |          |          |          sub_select_op
                |          |          |          |          sub_select_op
                |          |          |          |          sub_select_op
                |          |          |          |          sub_select_op
                |          |          |          |          sub_select
                |          |          |          |          JOIN::exec
                |          |          |          |          handle_query
                |          |          |          |          Sql_cmd_insert_select::execute
                |          |          |          |          mysql_execute_command
                |          |          |          |          mysql_parse
                |          |          |          |          dispatch_command
                |          |          |          |          do_command
                |          |          |          |          handle_connection
                |          |          |          |          pfs_spawn_thread
                |          |          |          |          start_thread
                |          |          |          |          
                |          |          |          |--45.18%-- row_ins_sec_index_entry_low
                |          |          |          |          row_ins_sec_index_entry
                |          |          |          |          row_ins_step
                |          |          |          |          row_insert_for_mysql
                |          |          |          |          ha_innobase::write_row
                |          |          |          |          handler::ha_write_row
                |          |          |          |          write_record
                |          |          |          |          Query_result_insert::send_data
                |          |          |          |          end_send
                |          |          |          |          evaluate_join_record
                |          |          |          |          QEP_tmp_table::end_send
                |          |          |          |          sub_select_op
                |          |          |          |          sub_select_op
                |          |          |          |          sub_select_op
                |          |          |          |          sub_select_op
                |          |          |          |          sub_select_op
                |          |          |          |          sub_select_op
                |          |          |          |          sub_select
                |          |          |          |          JOIN::exec
                |          |          |          |          handle_query
                |          |          |          |          Sql_cmd_insert_select::execute
                |          |          |          |          mysql_execute_command
                |          |          |          |          mysql_parse
                |          |          |          |          dispatch_command
                |          |          |          |          do_command
                |          |          |          |          handle_connection
                |          |          |          |          pfs_spawn_thread
                |          |          |          |          start_thread
                |          |          |           --0.00%-- [...]
                |          |          |          
                |          |          |--19.29%-- trx_undo_report_row_operation
                |          |          |          btr_cur_ins_lock_and_undo
                |          |          |          btr_cur_optimistic_insert
                |          |          |          row_ins_clust_index_entry_low
                |          |          |          row_ins_clust_index_entry
                |          |          |          row_ins_step
                |          |          |          row_insert_for_mysql
                |          |          |          ha_innobase::write_row
                |          |          |          handler::ha_write_row
                |          |          |          write_record
                |          |          |          Query_result_insert::send_data
                |          |          |          end_send
                |          |          |          evaluate_join_record
                |          |          |          QEP_tmp_table::end_send
                |          |          |          sub_select_op
                |          |          |          sub_select_op
                |          |          |          sub_select_op
                |          |          |          sub_select_op
                |          |          |          sub_select_op
                |          |          |          sub_select_op
                |          |          |          sub_select
                |          |          |          JOIN::exec
                |          |          |          handle_query
                |          |          |          Sql_cmd_insert_select::execute
                |          |          |          mysql_execute_command
                |          |          |          mysql_parse
                |          |          |          dispatch_command
                |          |          |          do_command
                |          |          |          handle_connection
                |          |          |          pfs_spawn_thread
                |          |          |          start_thread
                |          |          |
[18 Jun 2016 21:36] Omer Barnir 
Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0