Hi federico, sorry i didn't asked right Spider use sql_cache/sql_no_cache at remote servers? 2014-05-29 6:10 GMT-03:00 Federico Razzoli <federico_raz@yahoo.it>:

How could Spider invalidate the query cache when necessary? The remote servers don't know about your Spider table. Consider using the query cache on the remote server, if it's really necessary.

Federico

-------------------------------------------- El jue, 29/5/14, Roberto Spadim <roberto@spadim.com.br> escribió:

Asunto: [Maria-discuss] Spider and query cache Para: "Maria Discuss" <maria-discuss@lists.launchpad.net> Fecha: jueves, 29 de mayo, 2014 04:23

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?

-- Roberto Spadim SPAEmpresarialEng. Automação e Controle

-----Adjunto en línea a continuación-----

_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

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

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@mariadb.org>:

On 29 May 2014, at 10:23, Roberto Spadim <roberto@spadim.com.br> 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

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@gmail.com>:

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@spadim.com.br>:

...

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@mariadb.org>:

...

On 29 May 2014, at 10:23, Roberto Spadim <roberto@spadim.com.br> 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

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

nice :) i will test it 2014-06-18 12:57 GMT-03:00 kentoku <kentokushiba@gmail.com>:

Hi Roberto,

I added option to Spider 3.2.4 which bundled in MariaDB 10.0.12. Please try it!

- query_cache_sync(qcs) Add same query cache option that executed for Spider table. 0 : It doesn't give it. 1 : Add sql_cache, if SQL(SELECT) is executed with sql_cache for Spider table. 2 : Add sql_no_cache, if SQL(SELECT) is executed with sql_no_cache for Spider table. 3 : 1 & 2 The default value is 0

Thanks, Kentoku

2014-06-04 5:37 GMT+09:00 Roberto Spadim <roberto@spadim.com.br>:

...

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@gmail.com>:

...

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@spadim.com.br>:

...

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@mariadb.org>:

...

On 29 May 2014, at 10:23, Roberto Spadim <roberto@spadim.com.br> 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

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

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

Hi again kentoku! i'm using spider :) thanks for the last feature now i have another idea about query cache =) i'm starting test of VP (vertical partitioning) for example i have 3 tables: 1) vp table 2) data1 table (many select) 3) data2 table (many update) both table will not be used outside (1), for example, i'm considering that we will only have select/update/delete from table (1) and never have an update data1, or update data2, in this case user must invalidate query cache what will happen? i will give examples.... 1) insert/delete insert into vp values ( ... ) delete from vp where (...) for now this always invalidate query cache maybe in future only partition that receive this row, for example if we have an horizontal partition and only one partition changed 2) select 2.1)select (fields from data1) from vp where (no fields from data2) this kind of query will only use data1 table, in this case it's a table that never change (it have low writes and many reads) we can query cache it, but instead of leaving query cache table name as "vp", we mark it as "vp-data1" 2.2) select (fields from data1 and data2) from vp where (fields from data1 and data2) this kind of query use both tables, here the second table have many updates if we include this query to query cache we will have a query cache invalidation very soon, this isn't a nice query to cache, but if we cache it we could call table name as "vp" or "vp-data1 + vp-data2" 3) update here is the magic... 3.1) update vp set fields_from_data2=... where fields_fromdata_1_or_data2=... here we use fields from data1 and data2 to "find" rows, but we only modify fields from data2 (the write intensive table) in this case we will only invalidate queries from table "vp-data2" and not "vp-data1", in other words, all queries that use (2.1) will not be invalidate, a better query cache hit rate =) --- observations since vp-data2 change many many times, could be nice to block query cache of selects that use this table i'm considering it as a parameter of vp table, something like comment 'query cache data1' in this case only data1 will be query cache, if we receive a query that use data2 we will not cache it sounds crazy? :) ok i know that query cache have some problems with mutex, but it's another history we could have a better hit rate with this feature (at least when correctly used) check that query cache become inconsistent if we change data1 or data2 outside vp table well i think that's all :) thanks!

Hi! Well i'm checking vp isn't part of mariadb today :/ maybe we should port it first and after jmplement new features, right? Em domingo, 21 de setembro de 2014, Oleksandr Byelkin < sanja@montyprogram.com> escreveu:

Hi!

On 21.09.14 05:06, Roberto Spadim wrote: [skip]

...

sounds crazy? :) ok i know that query cache have some problems with mutex, but it's another history we could have a better hit rate with this feature (at least when correctly used) check that query cache become inconsistent if we change data1 or data2 outside vp table

It is doable if put a lot of hooks of asking engine about tables.

There is already hooks now used in table invalidation. But it works slowly (relatively) because having no open table difficult to give engine full information and so each such hook will cause semi-open process and so if we have miss it looks like double opening overhead.

So, in price of total overhead we can make it, the question is if it help so much that gain will be more then overhead in average... As usual I have doubts about it (need a proof).

[skip]

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

hi kentoku! i will try with mysql server but i got one question, why mariadb don't support VP? is something different that block using VP ? 2014-09-23 14:44 GMT-03:00 kentoku <kentokushiba@gmail.com>:

Hi Roberto,

Thank you for testing VP. I'll think about this feature.

Thanks, Kentoku

...

Hi! Well i'm checking vp isn't part of mariadb today :/ maybe we should

2014-09-21 18:10 GMT+09:00 Roberto Spadim <roberto@spadim.com.br>: port

...

it first and after jmplement new features, right?

Em domingo, 21 de setembro de 2014, Oleksandr Byelkin <sanja@montyprogram.com> escreveu:

...

Hi!

On 21.09.14 05:06, Roberto Spadim wrote: [skip]

...

sounds crazy? :) ok i know that query cache have some problems with mutex, but it's another history we could have a better hit rate with this feature (at

least

...

when correctly used) check that query cache become inconsistent if we change data1 or data2 outside vp table

It is doable if put a lot of hooks of asking engine about tables.

There is already hooks now used in table invalidation. But it works slowly (relatively) because having no open table difficult to give engine full information and so each such hook will cause semi-open process and so if we have miss it looks like double opening overhead.

So, in price of total overhead we can make it, the question is if it help so much that gain will be more then overhead in average... As usual I have doubts about it (need a proof).

[skip]

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

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

...

hi kentoku! i will try with mysql server but i got one question, why mariadb don't support VP? is something different that block using VP ?

It is need some changes for MariaDB.

now i saw 10.0.9 at 'last' name, i will try it :)

Please use this for now. source code http://spiderformysql.com/downloads/spider-3.2/mariadb-10.0.13-spider-3.2-vp... binary for linux x86_64 http://spiderformysql.com/downloads/spider-3.2/mariadb-10.0.13-spider-3.2-vp... Thanks, Kentoku 2014-09-24 3:06 GMT+09:00 Roberto Spadim <roberto@spadim.com.br>:

https://launchpad.net/vpformysql/trunk/1.1-for-5.5.34-10.0.9/+download/vp-sr...

now i saw 10.0.9 at 'last' name, i will try it :) news i post here

2014-09-23 14:54 GMT-03:00 Roberto Spadim <roberto@spadim.com.br>:

...

hi kentoku! i will try with mysql server but i got one question, why mariadb don't support VP? is something different that block using VP ?

2014-09-23 14:44 GMT-03:00 kentoku <kentokushiba@gmail.com>:

...

Hi Roberto,

Thank you for testing VP. I'll think about this feature.

Thanks, Kentoku

2014-09-21 18:10 GMT+09:00 Roberto Spadim <roberto@spadim.com.br>:

...

Hi! Well i'm checking vp isn't part of mariadb today :/ maybe we should port it first and after jmplement new features, right?

Em domingo, 21 de setembro de 2014, Oleksandr Byelkin <sanja@montyprogram.com> escreveu:

...

Hi!

On 21.09.14 05:06, Roberto Spadim wrote: [skip]

...

sounds crazy? :) ok i know that query cache have some problems with mutex, but it's another history we could have a better hit rate with this feature (at least when correctly used) check that query cache become inconsistent if we change data1 or data2 outside vp table

It is doable if put a lot of hooks of asking engine about tables.

There is already hooks now used in table invalidation. But it works slowly (relatively) because having no open table difficult to give engine full information and so each such hook will cause semi-open process and so if we have miss it looks like double opening overhead.

So, in price of total overhead we can make it, the question is if it help so much that gain will be more then overhead in average... As usual I have doubts about it (need a proof).

[skip]

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

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

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

hum, i'm reading source... some changes to handler and something at sql_class related to xid_? plugins, fields soon i will compile and test