Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm used to having a showplan in Microsoft SQL and have gotten lazy with using tools to tune MySQL queries. How can I determine physical/logical I/O for a query in MySQL? (EXPLAIN shows the index selection but I want more information from the MySQL server, if possible).

share|improve this question
2  
I presume you've seen this on SO? does it help? stackoverflow.com/questions/124925/… If not, why not? –  jcolebrand Jan 7 '11 at 1:17
    
Not quite your question, but you might like this blog post: tocker.ca/2013/05/06/when-does-mysql-perform-io.html –  Morgan Tocker Oct 20 '13 at 21:42

4 Answers 4

I recommend using Percona Server, for the slow query log enhancements. I answered a similar thread to this on Stack Overflow:

http://stackoverflow.com/questions/3393206/what-is-the-equivalent-query-in-mysql/3443944#3443944

Take a look at this in particular (from http://www.percona.com/docs/wiki/percona-server:features:slow_extended?redirect=1#changes_to_the_log_format):

# User@Host: mailboxer[mailboxer] @  [192.168.10.165]
# Thread_id: 11167745  Schema: board
# QC_Hit: No  Full_scan: No  Full_join: No  Tmp_table: Yes  Disk_tmp_table: No
# Filesort: Yes  Disk_filesort: No  Merge_passes: 0
# Query_time: 0.000659  Lock_time: 0.000070  Rows_sent: 0  Rows_examined: 30  Rows_affected: 0  Rows_read: 30
#   InnoDB_IO_r_ops: 1  InnoDB_IO_r_bytes: 16384  InnoDB_IO_r_wait: 0.028487
#   InnoDB_rec_lock_wait: 0.000000  InnoDB_queue_wait: 0.000000
#   InnoDB_pages_distinct: 5
select count(distinct author_id) from art87.article87 force index (forum_id) where forum_id = 240215 and thread_id = '710575' 
share|improve this answer

You can estimate query performance by looking at the number of disk seeks.

The Optimization portion of the MySQL manual explains one of the more useful ways of estimating I/O and subsequent tuning requirements. Exploring the number of reads, and estimating hardware IO should give you a fairly clear indication of disk-access requirements. Furthermore, by adjusting buffer sizes, you'll be able to see the implication of memory and caching on your query.

share|improve this answer

It's not perfect, but you should be able to do

mysql> show variables where variable_name like '%innodb%';

For the number of physical reads, look at the Innodb_buffer_pool_reads counter, and for logical reads look at Innodb_buffer_pool_read_requests. To be useful, you would need to make a note of these numbers, then run a representative workload, then divide the number of logical reads to find logical reads per queries. If you are lucky, the number of physical reads will increase near the beginning of your test run and then remain constant.

share|improve this answer
    
Useful but be aware that this is global: If you study one statement you'd better be alone. Also it is sometimes misleading if you re-read multiple times the same bloc, it is counted as logical read each time (example: join 1000 rows on 1 row => 1000 logical reads, although the whole set is perhaps 100 blocs) I still use this to spot some heavy SQL though... –  phil_w Sep 3 at 17:38

With some work, you can see how much "work" your query takes.

mysql> FLUSH STATUS;           -- In newer MySQLs, this clears the "SESSION" values
Query OK, 0 rows affected (0.00 sec)    

mysql> SHOW SESSION STATUS LIKE 'Handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 0     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 0     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
15 rows in set (0.00 sec)

mysql> SELECT * FROM ts;
+----+---------------------+
| id | ts                  |
+----+---------------------+
|  1 | 2011-03-24 20:47:38 |
|  2 | 2011-03-24 20:46:29 |
|  3 | 2011-03-24 20:46:43 |
+----+---------------------+
3 rows in set (0.00 sec)

mysql> SHOW SESSION STATUS LIKE 'Handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 1     |
| Handler_read_key           | 2     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 4     |  <-- 3 rows, plus 1 to realize it is finished
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
15 rows in set (0.00 sec)

mysql> SHOW STATUS LIKE 'Up%';
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| Uptime                    | 3287334 |
| Uptime_since_flush_status | 68      |
+---------------------------+---------+
2 rows in set (0.00 sec)

Well, that does not give you the cached vs non-cached answers, but it does give you some interesting clues.

Alas, the InnoDB STATUSes are global. So you need to capture them before and after. Worse yet, other queries will be bumping the values, too.

mysql> SHOW SESSION STATUS LIKE 'Innodb_buffer_pool%';
+---------------------------------------+-------------+
| Variable_name                         | Value       |
+---------------------------------------+-------------+
| Innodb_buffer_pool_pages_data         | 279861      |
| Innodb_buffer_pool_pages_dirty        | 52          |
| Innodb_buffer_pool_pages_flushed      | 15992658    |
| Innodb_buffer_pool_pages_free         | 96          |
| Innodb_buffer_pool_pages_misc         | 8043        |
| Innodb_buffer_pool_pages_total        | 288000      |
| Innodb_buffer_pool_read_ahead         | 2567477     |
| Innodb_buffer_pool_read_ahead_evicted | 262237      |
| Innodb_buffer_pool_read_requests      | 26565709230 |
| Innodb_buffer_pool_reads              | 1339717     |
| Innodb_buffer_pool_wait_free          | 0           |
| Innodb_buffer_pool_write_requests     | 865548882   |
+---------------------------------------+-------------+
12 rows in set (0.00 sec)

mysql> SELECT * FROM ts;
+----+---------------------+
| id | ts                  |
+----+---------------------+
|  1 | 2011-03-24 20:47:38 |
|  2 | 2011-03-24 20:46:29 |
|  3 | 2011-03-24 20:46:43 |
+----+---------------------+
3 rows in set (0.00 sec)

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool%';
+---------------------------------------+-------------+
| Variable_name                         | Value       |
+---------------------------------------+-------------+
| Innodb_buffer_pool_pages_data         | 279865      |
| Innodb_buffer_pool_pages_dirty        | 8           |
| Innodb_buffer_pool_pages_flushed      | 15992842    |
| Innodb_buffer_pool_pages_free         | 92          |
| Innodb_buffer_pool_pages_misc         | 8043        |
| Innodb_buffer_pool_pages_total        | 288000      |
| Innodb_buffer_pool_read_ahead         | 2567477     |
| Innodb_buffer_pool_read_ahead_evicted | 262237      |
| Innodb_buffer_pool_read_requests      | 26565712655 |  <-- This went up
| Innodb_buffer_pool_reads              | 1339717     |  <-- No chg = no disk reads
| Innodb_buffer_pool_wait_free          | 0           |
| Innodb_buffer_pool_write_requests     | 865550805   |
+---------------------------------------+-------------+
12 rows in set (0.00 sec)

My mantra "Count the disk hits".

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.