List:Internals« Previous MessageNext Message »
From:Amila Liyanaarachchi Date:January 20 2010 2:52pm
Subject:Re: query cache extension
View as plain text  
Hi Sergei,

First I tried to parse the incoming query before comparing with the
QC, my intention was to keep the select list (with table aliases) and
the column list in the where clause again with the table aliases. For
simplicity I didn't handle the rest of the things possible to come is
a query.
But I couldn't finish the implementation due to the tightly couple and
complex nature of the QC implementation.

Could you please give me few tips to go try it similar to what
libmysqlclient does (as you suggested in your previous email).

Thanks a lot,
Amila


>
> On Fri, Nov 13, 2009 at 12:32 AM, Sergei Golubchik <serg@stripped> wrote:
>>
>> Hi, Amila!
>>
>> On Nov 11, Amila Liyanaarachchi wrote:
>> > 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
>> > 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.
>> > 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.
>> ...
>> > Since I'm a beginner in this area I would like to know whether this is
>> > doable and any problems that may arise ....
>>
>> Everything's doable, the question is only how much work it'll require.
>>
>> 1. That's reasonably easy, but it will produce incorrect results, if
>> imeplented exactly as you described. For example:
>>
>>   SELECT a FROM t1 WHERE b=5;
>>   ---
>>   UPDATE t1 SET b=6;
>>
>> as you see, the second UPDATE does not update any columns in the select
>> list, but it definitely affects the results of the whole SELECT. That
>> is, you need to take WHERE condition into account. And probably GROUP
>> BY, ORDER BY, HAVING. And triggers, and foreign keys.
>>
>> 2. and
>> 3.
>>
>> The problem here is that query cache caches query result in the form of
>> network packets, basically what you would see with a network sniffer
>> like tcpdump.
>>
>> To do any transformations of the cached entries you need either to
>> change the format of cached data - that is, store not the network
>> packets, but higher level structures. Which will inevitably make query
>> cache responses slower, as you'll need to convert your structures to
>> network packets on every cache hit. But one cache entry will be able to
>> answer all queries with any subset of columns in any order.
>>
>> Or you can of course do transformations on the network packets, for this
>> you'll need to parse them (similar to what libmysqlclient does) massage
>> the data (e.g. remove columns) and assemble network packets again.
>>
>> Regards / Mit vielen Grüßen,
>> Sergei
>>
>> --
>>   __  ___     ___ ____  __
>>  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik
> <serg@stripped>
>>  / /|_/ / // /\ \/ /_/ / /__  Principal Software Engineer/Server
> Architect
>> /_/  /_/\_, /___/\___\_\___/  Sun Microsystems GmbH, HRB München
> 161028
>>       <___/              
>    Sonnenallee 1, 85551 Kirchheim-Heimstetten
>> Geschäftsführer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
>> Vorsitzender des Aufsichtsrates: Martin Häring
>
Thread
query cache extensionAmila Liyanaarachchi11 Nov
  • Re: query cache extensionSergei Golubchik12 Nov
    • Re: query cache extensionAmila Liyanaarachchi13 Nov
      • Re: query cache extensionSergei Golubchik13 Nov
        • Re: query cache extensionAmila Liyanaarachchi11 Dec
          • Re: query cache extensionRick James16 Jan
Re: query cache extensionAmila Liyanaarachchi20 Jan
  • Re: query cache extensionSergei Golubchik20 Jan