1

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?

Improve this question
2
  • Are your tables all MyISAM, all InnoDB, or a mixture of both ??? Commented Apr 8, 2013 at 19:10
  • They're all InnoDB Commented Apr 8, 2013 at 19:23

1 Answer 1

Reset to default
1

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.

Improve this answer
1
  • 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? Commented Apr 8, 2013 at 19:44

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.