MySQL Bugs: #104576: partition table access second index lead to cpu high load

Bug #104576	partition table access second index lead to cpu high load
Submitted:	10 Aug 2021 4:18	Modified:	8 Oct 2021 21:20
Reporter:	peng gao	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S5 (Performance)
Version:	8.0.26, 5.7.35	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi all:
  we find when partition table have a great many columns(like 300 cols),access second index,if needed data distribute in different partition, cpu high load in funcation build_template_field. like,

create table t(
    id1 int,
    id2 int
    primary key(id1),
    key(id2)
)engine=innodb
partition by range(id1)(
    partition p0 values less than(100),
    partition p1 values less than(200),
    partition p2 values less than(300));    

insert into tpar10col values(1,1);
insert into tpar10col values(101,1);
insert into tpar10col values(201,1);
insert into tpar10col values(2,2);
insert into tpar10col values(3,2);
insert into tpar10col values(4,2);
insert into tpar10col values(7,2);
insert into tpar10col values(8,2);
insert into tpar10col values(9,2);
insert into tpar10col values(10,2);

if we use statement "select * from t where id2=1",this statement will access p0,p1,p2。access every partition may call funcation ha_innobase::build_template。
ha_innobase::build_template loop every filed,if in write_set and read_set will call build_template_field funcation to find clust_rec_field_no(loop every field in clust index) and rec_field_no,because clust index include all filed ,so cpu high load here. perf top output like:

53.98%  mysqld               [.] build_template_field                                                                                                                                     
10.40%  [kernel]             [k] _raw_spin_unlock_irqrestore                                                                                                                              
 4.12%  [kernel]             [k] copy_user_generic_unrolled                                                                                                                               
 1.95%  mysqld               [.] ha_innobase::build_template                                                                                                                              
 1.48%  mysqld               [.] row_sel_store_mysql_field_func
 

can we save mysql_row_templ_t.clust_rec_field_no ,if next parition access,we copy it to new mysql_row_templ_t.clust_rec_field_no,not loop every field in clust index.in all parition this value maybe is same.

thanks.

How to repeat:
see the attachment

Suggested fix:
can we save mysql_row_templ_t.clust_rec_field_no ,if next parition access,we copy it to new mysql_row_templ_t.clust_rec_field_no,not loop every field in clust index.in all parition this value maybe is same.

How to repeat

Attachment: test.txt (text/plain), 39.98 KiB.

Hello peng gao,

Thank you for the report and feedback.

regards,
Umesh

Thanks for your verification

Documented fix as follows in the MySQL 5.7.37 and 8.0.28 changelogs:

    A query using an index other than the primary key of a
    partitioned table sometimes resulted in excessive CPU load.

Closed.

i am seeing this behaviour even on MYSQL 8.0.32 where we moved few heavily accessed tables with multiple json columns to RANE PARTITIONING and for 2 days the CPU is hitting almost 100% during peak business hours