I'll try to look at Roland Bouman's I_S plugin and see if we could bundle it

The patch is, but fetching the table locks QC and so all queries on the server. In case of big QC or high rate of queries to the table it could be cause big performance problem. IMHO it should be mentioned some how so that people understand what they pay for the information.

Lock time does not depend on connection speed, it depend on number of elements in the cache. For some big caches it could be huge performance impact (we specially made lock free switching off QC for that case). On the other hand there is no other way to get this information... Maybe we should require superuser privileges to access this table, to prevent DoS attack from normal user connection?

1) there is no way to keep QC safe without the mutex lock (otherwise it will change under you feet), it is no good but design of the QC is very old and never changed.
2) QC check (so lock) happened before SELECT execution, so no optimizer (or even parser) could be involved
3) when we lock we do not know why (there are several case when we do try to lock cache, but I am speaking about general situation).

So All this require major or relatively big QC rewrite to be fixed (i.e. only can be done in some alpha).

The real solution is to request superuser privileges for the table (IMHO it is not difficult, in worst case return empty table and lock nothing if it requested by non-privileged user).

is this request finished?

not quite

\o/ finally done?!?! =D
in what version could i test it?

pushed in 5.5 branch, will be in 5.5.31 release

any idea when it's will be in mariadb 10.x?

in 10.0.2

i will test 5.5.31 as soon as possible
thanks!

i have downloaded the 5.5.31 for windows, and didn't found the plugin for query cache information
any help? i will test later with linux

sorry founded =)

install plugin query_cache_info soname 'query_cache_info.dll'

uhm... some tests in windows, and it have some errors.. but it works sometimes

SET global query_cache_size=0;

EXECUTE SOME QUERIES..... (without use query cache)

SELECT `STATEMENT_SCHEMA`, `STATEMENT_TEXT`, `RESULT_BLOCKS_COUNT`, `RESULT_BLOCKS_SIZE`, `RESULT_BLOCKS_SIZE_USED` FROM `information_schema`.`QUERY_CACHE_INFO` LIMIT 1000;
/* Erro SQL (1105): Unknown error */
select * from query_cache_info;
/* Erro SQL (1105): Unknown error */

"Variable_name" "Value"
"Qcache_free_blocks" "0"
"Qcache_free_memory" "0"
"Qcache_hits" "0"
"Qcache_inserts" "0"
"Qcache_lowmem_prunes" "0"
"Qcache_not_cached" "0"
"Qcache_queries_in_cache" "0"
"Qcache_total_blocks" "0"

======================================
when using query cache, like:
set global query_cache_size=100000;

...execute some non cacheable queries..

SELECT * FROM `information_schema`.`QUERY_CACHE_INFO`; (RETURN 0 ROWS)
SHOW CREATE TABLE `information_schema`.`QUERY_CACHE_INFO`;
/* Erro SQL (6): Error on delete of 'C:\Users\Beto\AppData\Local\Temp#sqlc24_6_16a.MAD' (Errcode: 13) */

? ERROR (13) ON DELETE ?

Qcache_queries_in_cache=0

=================================
now...
...execute some cacheable query....

Qcache_queries_in_cache=1

SELECT * FROM `information_schema`.`QUERY_CACHE_INFO`;
RETURN 1 ROW =)

"STATEMENT_SCHEMA" "STATEMENT_TEXT" "RESULT_BLOCKS_COUNT" "RESULT_BLOCKS_SIZE" "RESULT_BLOCKS_SIZE_USED"
"test" "select SQL_CACHE * from a where b like 'ab%'" "1" "23936" "23935"

VERRRRRY NICE
=========================
but the problem still occurs when

SHOW CREATE TABLE `information_schema`.`QUERY_CACHE_INFO`;
SELECT `STATEMENT_SCHEMA`, `STATEMENT_TEXT`, `RESULT_BLOCKS_COUNT`, `RESULT_BLOCKS_SIZE`, `RESULT_BLOCKS_SIZE_USED` FROM `information_schema`.`QUERY_CACHE_INFO` LIMIT 1000;
/* Erro SQL (6): Error on delete of 'C:\Users\Beto\AppData\Local\Temp#sqlc24_7_15b.MAD' (Errcode: 13) */

i'm using heidisql and pressing f5 to reload the query cache information, i'm executing about 20 queries per second like show create table and select * from query cache, to get this error
maybe a problem cleaning a temporary table?

only running
select * from query_cache_info;
/* Affected rows: 0 Registros encontrados: 1 Avisos: 0 Duração de 1 query: 0,015 sec. */
select * from query_cache_info;
/* Erro SQL (6): Error on delete of 'C:\Users\Beto\AppData\Local\Temp#sqlc24_7_1a6.MAD' (Errcode: 13) */

very fast, i get the same error sql(6), errcode:13

show warnings
"Level" "Code" "Message"
"Error" "6" "Error on delete of 'C:\Users\Beto\AppData\Local\Temp#sqlc24_7_1a6.MAD' (Errcode: 13)"

flush query cache; stop working...

Qcache_queries_in_cache=1
select * from query_cache_info -> return 1 row
FLUSH QUERY CACHE;
Qcache_queries_in_cache=1
select * from query_cache_info -> return 1 row (the same row)

flush tables work

Qcache_queries_in_cache=1
select * from query_cache_information -> return 1 rows

flush tables
Qcache_queries_in_cache=0
select * from query_cache_information -> return 0 rows

DELETE FROM `QUERY_CACHE_INFO` WHERE `STATEMENT_SCHEMA`='information_schema' AND `STATEMENT_TEXT`='select SQL_CACHE * from test.a where b like \'ab%\'' AND `RESULT_BLOCKS_COUNT`=1 AND `RESULT_BLOCKS_SIZE`=23936 AND `RESULT_BLOCKS_SIZE_USED`=23935 LIMIT 1;

don't work... but i think it will not work since delete isn't implemented

well =) very nice job guys!!!

please disconsider this comments
putting they in MDEV-4516

thanks!