Re: [Fwd: query cache extension]

[Amila Liyanaarachchi]

Hi Kristofer,

Thanks a lot for your comments.

Probably I'll go with ideas 1, 3 of my previous post. Even though I'm
interested in your idea about scaling the QC on multiple cores, it is
not feasible for me to attend that at this moment (I'm doing this as a
part of my course work so they won't accept any work on a MySQL
specific issue, rather they recommend to do something which is common
among dbmss).

Best regards,
Amila

On Thu, Nov 12, 2009 at 7:48 PM, Kristofer Pettersson
<Kristofer.Pettersson@stripped> wrote:
> Mattias Jonsson skrev:
>>
>>
>> -------- Original Message --------
>> Subject: query cache extension
>> Date: Wed, 11 Nov 2009 09:21:22 +0530
>> From: Amila Liyanaarachchi <amila.uoc@stripped>
>> To: internals@stripped
>>
>> Hi,
>>
>> I'm thinking of extending the query cache to deal with following
>> scenarios,
>>
>> 1. stop invalidating the cache entries when updates take place for
>> columns which are not in the select list of the cached queries
>
> You could add a valid column list to the query cache table objects and check
> against this list on each invalidation.
>
> I'm thinking that maybe it would be better to make views work better with
> the QC instead of adding a special column list.
>>
>> 2. when a second query contains a subset of columns from a first query
>> (which has cached results) obtain the results from the cache, this
>> will not happen due to the difference in the query, but since the
>> select list of the second query is a subset of the select list of the
>> first query (and the where clause should be identical) we can obtain
>> the results from the cache.
>
> We're currently not performing any parsing on the query when we try to
> determine if it is a cached query or not. How can we obtain the information
> needed to compare two different query strings?
> ie
> SELECT * FROM t2; <-- cached
> SELECT t1.c1 FROM t1;  <-- How will we use this to for a key to find the
> cached query?
>>
>> 3. identify the queries with identical select lists but having column
>> names in different orders and process them as identical queries and
>> take the advantage of query cache.
>
> This is interesting. We could aggregate a checksum when storing the result
> sets and incorporate this functionality when we do a FLUSH QUERY CACHE. I
> think this is worth investigating.
>
>>
>> Still this is in idea level and I haven't done any coding yet. I just
>> wanted to try out this as an experiment and check the performance on a
>> database where the updates are relatively less.
>>
>> I know that this needs some more memory for the query cache, and need
>> some additional query parsing.
>>
>> Since I'm a beginner in this area I would like to know whether this is
>> doable and any problems that may arise ....
>>
>> Thanks,
>> Amila
>>
>
> I think one of the major issues with the QC today is that it isn't scaling
> very well with multiple cores. If you are looking for something important to
> work with, figuring out how to use the existing code with parallel threads
> would be a huge step forward!
>
> Feel free to email me any time you have any questions. I've just started to
> subscribe to hte Internals list so you can continue to post progress there
> if you like.
>
> Nice to see that there are some nice ideas out there!
>
> Regards,
> Kristofer
>
>