← Back to team overview

maria-discuss team mailing list archive

Re: Spider and query cache

 

ok kentoku
the idea is, if the client (the mysql user) use SQL_CACHE, send it to
spider external servers, if SQL_NO_CACHE is used, send it to spider
external servers, if SQL_CACHE and SQL_NO_CACHE aren't used, use
'query_cache_type' variable (
https://mariadb.com/kb/en/server-system-variables/#query_cache_type )

here i develop system considering a good query cache use, and i try to
not depend at memcache to have a good performace (but sometimes it's
very usefull), i'm testing spider engine yet, my first use is
partitioning, and now i'm testing external servers as shard option,
just to study, not a production use yet

if you have time to create it :) no problem, could you report after a
patch? i like to read mariadb internal code to understand how to use
it better

thanks kentoku =), tell me if i can help you

2014-06-03 16:30 GMT-03:00 kentoku <kentokushiba@xxxxxxxxx>:
> Hi Roberto,
>
>> nice Colin, I read manual and got this doubt, about the query_cache option
>>
>> all queries have sql_cache, or just queries that should be cached?
>> for example...
>>
>> (1)mysql user -> (2)mysql database with spider -> (3)external database
>> considering at (1 session) and (2 global), spider query_cache = 1 (sql_cache
>> option is passed.)
>>
>> if (1) send: SELECT SQL_NO_CACHE * FROM spider_table   (should not be
>> cached)
>> (2) will send to (3):  SELECT SQL_CACHE * FROM external_spider_table
>> (the wrong option)
>>  or : SELECT SQL_NO_CACHE * FROM spider_table  ?   (the right option)
>
> Spider's query_cache option is option for each Spider table. It works
> adding SQL_CACHE and SQL_NO_CACHE statically, and it does not use
> SQL_CACHE and SQL_NO_CACHE option from user query.
>
>> maybe a "3" should be created? use SQL_CACHE option from user query?
>
> I think it is better for creating another option for using SQL_CACHE
> option from user query rather than creating "3", because these options
> can be combined.
> I'll try to create it, if you need it!
>
> Thanks,
> Kentoku
>
>
> 2014-05-30 23:24 GMT+09:00 Roberto Spadim <roberto@xxxxxxxxxxxxx>:
>> nice Colin, I read manual and got this doubt, about the query_cache option
>>
>> all queries have sql_cache, or just queries that should be cached?
>> for example...
>>
>> (1)mysql user -> (2)mysql database with spider -> (3)external database
>> considering at (1 session) and (2 global), spider query_cache = 1 (sql_cache
>> option is passed.)
>>
>> if (1) send: SELECT SQL_NO_CACHE * FROM spider_table   (should not be
>> cached)
>> (2) will send to (3):  SELECT SQL_CACHE * FROM external_spider_table
>> (the wrong option)
>>  or : SELECT SQL_NO_CACHE * FROM spider_table  ?   (the right option)
>>
>>
>> maybe a "3" should be created? use SQL_CACHE option from user query?
>>
>>
>>
>>
>> 2014-05-30 8:25 GMT-03:00 Colin Charles <colin@xxxxxxxxxxx>:
>>
>>>
>>> On 29 May 2014, at 10:23, Roberto Spadim <roberto@xxxxxxxxxxxxx> wrote:
>>>
>>> > Hi guys! I'm with a doubt, spider engine don't allow query cache right?
>>> > But does it use sql_cache sql_no_cache when possible?
>>> >
>>>
>>> spider has a table parameter called query_cache that sends query from
>>> spider to remote server - see:
>>> https://mariadb.com/kb/en/spider-table-system-variables/#query_cache
>>> Default is 0, that is no query cache when a select is issued on a remote
>>> server
>>> =1 passes sql_cache - result can be cached
>>> =2 sql_no_cache is passed - query result not cached
>>> that's all documented:
>>> http://dev.mysql.com/doc/refman/5.5/en/query-cache-in-select.html
>>>
>>> I notice that in this article, https://mariadb.com/kb/en/query-cache/, the
>>> limitations do state: "      • The query cache is not used by the Spider
>>> storage engine (amongst others)."
>>>
>>> And then we have this article,
>>> https://mariadb.com/kb/en/spider-feature-matrix/ that also states spider
>>> supports the query cache - "Query Cache tuning per table of the on remote
>>> backend" - Yes
>>>
>>> So, docs need to be clearer/in sync, since spider can't use the query
>>> cache on a spider node, but it can on a remote server
>>> --
>>> Colin Charles, Chief Evangelist, SkySQL - The MariaDB Company
>>> blog: http://bytebot.net/blog/| t: +6-012-204-3201 | Skype: colincharles
>>>
>>
>>
>>
>> --
>> Roberto Spadim
>> SPAEmpresarial
>> Eng. Automação e Controle
>>
>> _______________________________________________
>> Mailing list: https://launchpad.net/~maria-discuss
>> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~maria-discuss
>> More help   : https://help.launchpad.net/ListHelp
>>



-- 
Roberto Spadim
SPAEmpresarial
Eng. Automação e Controle


Follow ups

References