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 Email Updates:
Status: Closed Impact on me:
None 
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] Shane Bester
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.