mysql built in query cache

7 posts / 0 new
Last post
akshah123
akshah123's picture
Offline
Last seen: 1 year 10 months ago
Joined: Mar 25 2011
Junior Boarder

Posts: 9

Ankit Shah
mysql built in query cache

How do I turn on mysql's built in query cache for infinidb? I tried turning on the query result cache by setting query_cache_size & other options provided here: http://dev.mysql.com/doc/refman/5.1/en/query-cache-configuration.html

However, it seems like it does not work with infinidb storage engine. Is that true?

If so, what sort of solution is suggested for caching the results of a query?

Thanks,

Ankit.

radams
radams's picture
Offline
Last seen: 9 hours 2 min ago
Joined: Jan 3 2011
Administrator

Posts: 487

Robert Adams
Re: mysql built in query cache

Hi Ankit,

Thanks for checking out infiniDB.

Please take some time and check out our documentation. The Concepts Guide explains how infiniDB works and how we use cache.

http://www.infinidb.org/downloads/cat_view/191-infinidb-community-softwa...

You can use "select calgetstats();" as a tool to see Cache I/O and Physical I/O.

mysql> select count(*) from lineorder;
+----------+
| count(*) |
+----------+
| 6001215 |
+----------+
1 row in set (0.25 sec)

mysql> select calgetstats();
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| calgetstats() |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Stats: MaxMemPct-0; NumTempFiles-0; TempFileSpace-0MB; ApproxPhyI/O-734; CacheI/O-740; BlocksTouched-734; PartitionBlocksEliminated-0; MsgBytesIn-0MB; MsgBytesOut-0MB; Mode-Distributed| 1320789008 940847 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> select count(*) from lineorder;
+----------+
| count(*) |
+----------+
| 6001215 |
+----------+
1 row in set (0.09 sec)

mysql> select calgetstats();
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| calgetstats() |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Stats: MaxMemPct-0; NumTemp

akshah123
akshah123's picture
Offline
Last seen: 1 year 10 months ago
Joined: Mar 25 2011
Junior Boarder

Posts: 9

Ankit Shah
Re: mysql built in query cache

Hi Radams,

Thanks for your quick reply. I had seen the document you mentioned and read a bit about the use of data buffer cache.

Forgive the over simplification but it seems this caching is more similar to innodb index cache in concept. Basically, the calculation does happen but it is able to find the underlying data for that calculation pretty quickly.

I was looking for something that caches the results themselves. i.e. there is no calculation the second time as it simply just returns the results from previous query.

So, suppose I have three queries that are run on the landing of my module. Each query takes about 3-4 seconds to run as I am showing top 10 results on the page. (by the way, infinidb works great here as mysql was taking 20-30 seconds).

However, the data on the table only changes once a day. So, the user going to the page multiple times does not get hit with it every time. Now, for infinidb the total load time stays constant at 10-12 seconds as opposed to 1-2 minutes the first time and within 1 second subsequent times.

I would like to enable this result set caching as well.

bdempsey
bdempsey's picture
Offline
Last seen: 5 days 11 hours ago
Joined: Oct 27 2009
Platinum Boarder

Posts: 194

Robert Dempsey
Re: mysql built in query cache

Currently InfiniDB does not support the MySQL query cache. You will have build this behavior into your client application.

The main issues are that our bulk loader runs independently of MySQL and could cause a modification to a table that was cached and we use temporary tables to accomplish our SQL re-writing.

akshah123
akshah123's picture
Offline
Last seen: 1 year 10 months ago
Joined: Mar 25 2011
Junior Boarder

Posts: 9

Ankit Shah
Re: mysql built in query cache

Hi,

Thanks for the reply. Though not an answer I wanted, it def saves me time :P .

I am guessing other users have had need for such a mechanism.

Do you have any recommendation or perhaps a suggestion on how should I handle this on client side?

Do you know of any third party tool/solution that can come in handy?

gsaxena
gsaxena's picture
Offline
Last seen: 1 year 5 months ago
Joined: Oct 20 2010
Junior Boarder

Posts: 13

Gautam Saxena
Re: mysql built in query cache

Are there any plans to support MySQL's "query cache" in the near future? If yes, will it be smart enough to store the most recently used stuff in RAM but maybe older stuff on filesystem?

radams
radams's picture
Offline
Last seen: 9 hours 2 min ago
Joined: Jan 3 2011
Administrator

Posts: 487

Robert Adams
Re: mysql built in query cache

Hi,

We currently do not have plans to add support for MySQL's "query cache".

Thanks,

Robert
Calpont