Bug #68180 Storage engine call in join_read_last_key() bypasses P_S.

• Submitted: 25 Jan 2013 12:03
• Modified: 29 Jan 2013 18:47
• Reporter: Sergey Petrunya
• Status: Closed
• Category: MySQL Server: Performance Schema
• Severity: S3 (Non-critical)
• Version: 5.6.9
• OS: Any
• Assigned to: Marc ALFF
• CPU Architecture: Any

[25 Jan 2013 12:03] Sergey Petrunya

Description:
Reading the source code:

join_read_last_key(JOIN_TAB *tab)
{
  int error;
  TABLE *table= tab->table;

  if (!table->file->inited &&
      (error= table->file->ha_index_init(tab->ref.key, tab->sorted)))
  ...

  if ((error=table->file->index_read_last_map(table->record[0],
                                              tab->ref.key_buff,
                                              make_prev_keypart_map(tab->ref.key_parts))))
  {
...

Note the use of index_read_last_map() call.

Suppose, join_read_last_key() is invoked for an innodb table.  The call will invoke handler::index_read_last_map() which will invoke ha_innobase::index_read_last().  None of the called functions will make any records in P_S.

The function that does make records in PS is called 
handler::ha_index_read_last(). There is no handler::ha_read_index_last_map(). 

How to repeat:
n/a

Suggested fix:
Introduce handler::ha_read_index_last_map().

Make join_read_last_key() use a ha_read_index... function.

[25 Jan 2013 12:38] MySQL Verification Team

I think this shows the problem:

drop table if exists t1;
create table t1(a int, b int, key(a,b))engine=innodb;

select * from t1 where a=1 order by a desc,b desc; #no times recorded in below:
select * from performance_schema.table_io_waits_summary_by_index_usage where object_schema='test' and object_name='t1'\G

select * from t1 where a=1 order by a,b;
select * from performance_schema.table_io_waits_summary_by_index_usage where object_schema='test' and object_name='t1'\G

[28 Jan 2013 9:55] Marc ALFF

Sergei,

Good catch, thanks for the bug report.

-- Marc Alff

[29 Jan 2013 18:47] Paul DuBois

Noted in 5.6.11, 5.7.1 changelogs.

Some table I/O performed by the server when calling a storage engine
were missing from the statistics collected by the Performance Schema.