Not the answer you need?
Register and ask your own question!

help interpreting output for log_slow_verbosity = full

jogacojogaco EntrantCurrent User Role Beginner
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.

Comments

  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    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
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.