help interpreting output for log_slow_verbosity = full

MySQL & MariaDB Other MySQL® Questions
1

I have a slow query I’d like to optimize.

When I run the query in question, I can see this output in the slow query log:


# Time: 150504 15:42:27
# User@Host: root[root] @ localhost [] Id: 37
# Query_time: 6.167118 Lock_time: 0.000311 Rows_sent: 490 Rows_examined: 2792 Rows_affected: 0
# Bytes_sent: 9664 Tmp_tables: 1 Tmp_disk_tables: 0 Tmp_table_sizes: 126992
# InnoDB_trx_id: 2F6CC547
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: Yes Tmp_table_on_disk: No
# Filesort: Yes Filesort_on_disk: No Merge_passes: 0
# InnoDB_IO_r_ops: 1623 InnoDB_IO_r_bytes: 26591232 InnoDB_IO_r_wait: 6.007482
# InnoDB_rec_lock_wait: 0.000000 InnoDB_queue_wait: 0.000000
# InnoDB_pages_distinct: 2016

What I can see is the value for InnoDB_IO_r_wait is too high: above 6 seconds.

What does this mean? How can I optimize it?

The query in question is a join of two tables, one with about 6 million records the other with about 65 million rows.

Server version: 5.6.22-72.0-log Percona Server (GPL), Release 72.0, Revision 738
Machine: virtual box with 10 cores and 16 GB RAM.

2

Hi jogaco;

InnoDB_IO_r_wait is the time it takes to read from storage. So you would optimize the query like any other query; generally by adding indexes and potentially modifying the query if it is inefficient by design. If you post the query, EXPLAIN plan, SHOW CREATE TABLE for each table involved, and SHOW INDEXES from each table involved, we can take a look to see if there are any obvious tuning opportunities.

-Scott