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 ran a few commands to see whether MySQL is caching queries/result sets. It looks like this is not the case.

mysql> show variables like 'query_cache_size';
--------------
show variables like 'query_cache_size'
--------------

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_size | 0     |
+------------------+-------+
1 row in set (0.01 sec)


mysql> show status like 'Qcache%';
--------------
show status like 'Qcache%'
--------------

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_free_blocks      | 0     |
| Qcache_free_memory      | 0     |
| Qcache_hits             | 0     |
| Qcache_inserts          | 0     |
| Qcache_lowmem_prunes    | 0     |
| Qcache_not_cached       | 0     |
| Qcache_queries_in_cache | 0     |
| Qcache_total_blocks     | 0     |
+-------------------------+-------+
8 rows in set (0.00 sec)

At the same time, I can run the same query at one given time, and it will take 42 seconds, and at another given time, and it will take 1.10 seconds.

If the MySQL server is not doing the caching, what is? Is it the mysql client? Is it my operating system (OS X)? Is it some service on the server that's not MySQL? Are all of these possibilities, or only one?

share|improve this question
    
Are your tables all MyISAM, all InnoDB, or a mixture of both ??? –  RolandoMySQLDBA Apr 8 '13 at 19:10
    
They're all InnoDB –  ktm5124 Apr 8 '13 at 19:23

1 Answer 1

up vote 1 down vote accepted

You have two caches, one for each Storage Engine.

MyISAM

Only caches index pages in the key buffer (sized of key_buffer_size). All data pages are read from the .MYD of the MyISAM table.

InnoDB

Three Components

  • This would be the InnoDB Buffer Pool (sized by innodb_buffer_pool_size). It is used to collect all data and index pages you have read to accomplish all your queries.
  • You also have the InnoDB's Transaction Logs catching transactional changes on disk (ib_logfile0, ib_logfile1)
  • The changes are written redundantly in ibdata1 in the event of a crash so InnoDB Crash Recovery can bring the data back to a consistent state.

See the Pictorial Representation of InnoDB's Architecture to see the Buffer Pool and Transaction Logs

Every time you read from or write to an InnoDB table, the .frm is checked (disk I/O), the data dictionary in ibdata1 is checked (disk I/O), and MVCC for transaction isolation is written in ibdata1 and transaction logs if there are incoming writes to the same table (disk I/O). Even a read-heavy InnoDB environment incurs disk I/O.

EPILOGUE

You could easily expect some pages to be rotated out. This is especially the case when the caches are configured too small. In the case of MyISAM, since data pages are read from disk each time, index pages can rotate in and out. As for InnoDB, it's data and index pages.

The result sets may have to be recreated each time. Sometime, this contribute to all caches activity.

share|improve this answer
    
Is there also caching on the level of the operating system? In terms of disk I/O? Or is all caching done by the engine? –  ktm5124 Apr 8 '13 at 19:44

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.