[Maria-developers] Query cache information plugin - MDEV-4516
Hi guys i'm reading query cache code, and i have some doubts... mariadb-10.0.1 source: 1)PROTOCOL_LOCAL: at line: 1412 (Query_cache::store_query) we have this comment: /* PROTOCOL_LOCAL results are not cached. */ what's PROTOCOL_LOCAL ? it's the unix socket? windows pipe? embedded? memcache? mysql? another thing like this? or it's the internal mariadb source running queries? 2)the query_size=0 (MDEV-4516) i was reading at line 1680 if (is_disabled() || thd->locked_tables_mode || thd->variables.query_cache_type == 0) goto err; and line : 1694 DBUG_ASSERT(query_cache_size != 0); // otherwise cache would be disabled why it's isn't used in query_cache_information plugin? *check that in line 1680 it test the "query_cache_type" too, * *in MDEV-4516 report about size=0 we didn't tested this case!!!* 3) i was at line 1886: DBUG_PRINT("qcache", ("\ long %d, 4.1: %d, bin_proto: %d, more results %d, pkt_nr: %d, \ CS client: %u, CS result: %u, CS conn: %u, limit: %lu, TZ: 0x%lx, \ sql mode: 0x%llx, sort len: %lu, conncat len: %lu, div_precision: %lu, \ def_week_frmt: %lu, in_trans: %d, autocommit: %d", is this the "primary key" of the query cache!?!?!?!?!?!?! could we use a sha1 or another "primary key" method (index [] of the query in cache + '';'+ 5 bytes of a hash key to compare if the index still with the same query) to easly show query entry "number", and allow a "delete from query_cache_information where key=xxx" function? at line 1938 we have this maybe the query position on cache ("primary key of query cache")?: DBUG_PRINT("qcache", ("Query have result 0x%lx", (ulong) query)); 4) there's some more ideas about query cache information plugin at MDEV-4571, if anyone could read it =) well that's all =] -- Roberto Spadim
in query cache information plugin file (qc_info.cc), line 69: {"RESULT_BLOCKS_COUNT", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, 0, 0}, {"RESULT_BLOCKS_SIZE", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, 0, 0, 0}, why we use the same "MY_INT32_NUM_DECIMAL_DIGITS" for "MYSQL_TYPE_LONG" and "MYSQL_TYPE_LONGLONG" ?
Hi, Roberto! On May 24, Roberto Spadim wrote:
in query cache information plugin file (qc_info.cc), line 69: {"RESULT_BLOCKS_COUNT", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, 0, 0}, {"RESULT_BLOCKS_SIZE", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, 0, 0, 0},
why we use the same "MY_INT32_NUM_DECIMAL_DIGITS" for "MYSQL_TYPE_LONG" and "MYSQL_TYPE_LONGLONG" ?
It's the display width of the integer column. Like declaring CREATE TABLE ... ( ... RESULT_BLOCKS_COUNT INT(11), RESULT_BLOCKS_SIZE BIGINT(11), It's not a constraint. Regards, Sergei
nice =) i'm putting a new mdev and a patch with table inside query cache ,and all flags (i think, two flags i don't know how to add) 2013/5/25 Sergei Golubchik <serg@askmonty.org>
Hi, Roberto!
On May 24, Roberto Spadim wrote:
in query cache information plugin file (qc_info.cc), line 69: {"RESULT_BLOCKS_COUNT", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, 0, 0}, {"RESULT_BLOCKS_SIZE", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, 0, 0, 0},
why we use the same "MY_INT32_NUM_DECIMAL_DIGITS" for "MYSQL_TYPE_LONG" and "MYSQL_TYPE_LONGLONG" ?
It's the display width of the integer column. Like declaring
CREATE TABLE ... ( ... RESULT_BLOCKS_COUNT INT(11), RESULT_BLOCKS_SIZE BIGINT(11),
It's not a constraint.
Regards, Sergei
-- Roberto Spadim SPAEmpresarial
done! :) https://mariadb.atlassian.net/browse/MDEV-4580 2013/5/25 Roberto Spadim <roberto@spadim.com.br>
nice =) i'm putting a new mdev and a patch with table inside query cache ,and all flags (i think, two flags i don't know how to add)
2013/5/25 Sergei Golubchik <serg@askmonty.org>
Hi, Roberto!
On May 24, Roberto Spadim wrote:
in query cache information plugin file (qc_info.cc), line 69: {"RESULT_BLOCKS_COUNT", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONG, 0, 0, 0, 0}, {"RESULT_BLOCKS_SIZE", MY_INT32_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, 0, 0, 0},
why we use the same "MY_INT32_NUM_DECIMAL_DIGITS" for "MYSQL_TYPE_LONG" and "MYSQL_TYPE_LONGLONG" ?
It's the display width of the integer column. Like declaring
CREATE TABLE ... ( ... RESULT_BLOCKS_COUNT INT(11), RESULT_BLOCKS_SIZE BIGINT(11),
It's not a constraint.
Regards, Sergei
-- Roberto Spadim SPAEmpresarial
-- Roberto Spadim SPAEmpresarial
*-* my first mariadb patch *-* please accept it hahaha what's the difference of mariadb 10 and mariadb 5 I_S plugin?
hi sergei =D work done :D i put a last diff with tables used by the query i used a char[] since i don't know how to use string or others function in c :) now query cache information is very very nice and beautiful =D i'm putting code here too it's mariadb 5.5.31
ops... please change ´ with ` attached again thanks guy!! :D 2013/5/25 Roberto Spadim <roberto@spadim.com.br>
hi sergei =D work done :D i put a last diff with tables used by the query i used a char[] since i don't know how to use string or others function in c :)
now query cache information is very very nice and beautiful =D i'm putting code here too
it's mariadb 5.5.31
-- Roberto Spadim SPAEmpresarial
sergei, there's a public contribuition system like github for mariadb? i put more information to query cache: query_hits (per query) rows_read (todo) expend_time (todo) attached patchs diff -u maria_db_5.5.31_original_file.xx my_changed_file.xx todo work: get rows_read from query and put this information in qc get expend_time running query and put this information in qc 2013/5/25 Roberto Spadim <roberto@spadim.com.br>
ops... please change ´ with ` attached again
thanks guy!! :D
2013/5/25 Roberto Spadim <roberto@spadim.com.br>
hi sergei =D work done :D i put a last diff with tables used by the query i used a char[] since i don't know how to use string or others function in c :)
now query cache information is very very nice and beautiful =D i'm putting code here too
it's mariadb 5.5.31
-- Roberto Spadim SPAEmpresarial
-- Roberto Spadim SPAEmpresarial
sorry wrong files now diff files =) 2013/5/25 Roberto Spadim <roberto@spadim.com.br>
sergei, there's a public contribuition system like github for mariadb?
i put more information to query cache: query_hits (per query) rows_read (todo) expend_time (todo)
attached patchs diff -u maria_db_5.5.31_original_file.xx my_changed_file.xx
todo work: get rows_read from query and put this information in qc get expend_time running query and put this information in qc
2013/5/25 Roberto Spadim <roberto@spadim.com.br>
ops... please change ´ with ` attached again
thanks guy!! :D
2013/5/25 Roberto Spadim <roberto@spadim.com.br>
hi sergei =D work done :D i put a last diff with tables used by the query i used a char[] since i don't know how to use string or others function in c :)
now query cache information is very very nice and beautiful =D i'm putting code here too
it's mariadb 5.5.31
-- Roberto Spadim SPAEmpresarial
-- Roberto Spadim SPAEmpresarial
-- Roberto Spadim SPAEmpresarial
Hi, Roberto! On May 25, Roberto Spadim wrote:
sergei, there's a public contribuition system like github for mariadb?
MariaDB sources are on launchpad. To porpose a code contribution, you can submit a merge request on launchpad: https://help.launchpad.net/Code/Review Regards, Sergei
nice, about how to get expend time in query and rows read, how could i do? thd->examined_row_count and thd->start_time return 0?! at function in sql_cache.cc line ~ 1560 void Query_cache::store_query(THD *thd, TABLE_LIST *tables_used) 2013/5/25 Sergei Golubchik <serg@askmonty.org>
Hi, Roberto!
On May 25, Roberto Spadim wrote:
sergei, there's a public contribuition system like github for mariadb?
MariaDB sources are on launchpad. To porpose a code contribution, you can submit a merge request on launchpad: https://help.launchpad.net/Code/Review
Regards, Sergei
-- Roberto Spadim SPAEmpresarial
foudn =) hehehei should do it at line ~1200 2013/5/25 Roberto Spadim <roberto@spadim.com.br>
nice, about how to get expend time in query and rows read, how could i do?
thd->examined_row_count and thd->start_time
return 0?!
at function in sql_cache.cc line ~ 1560 void Query_cache::store_query(THD *thd, TABLE_LIST *tables_used)
2013/5/25 Sergei Golubchik <serg@askmonty.org>
Hi, Roberto!
On May 25, Roberto Spadim wrote:
sergei, there's a public contribuition system like github for mariadb?
MariaDB sources are on launchpad. To porpose a code contribution, you can submit a merge request on launchpad: https://help.launchpad.net/Code/Review
Regards, Sergei
-- Roberto Spadim SPAEmpresarial
-- Roberto Spadim SPAEmpresarial
hi sergey all that i want is done now !! how! very very nice informations about qc =) just two flags that i don't understand how to show it : LC_TIME_NAMES and TIME_ZONE all others informations are displayed (32 columns now! maybe we could remove flags?) some fields maybe should be human readable but idon't know how to do it, like: COLUMN_FLAGS_CHARACTER_SET_CLIENT_NUM, COLUMN_FLAGS_CHARACTER_SET_RESULTS_NUM, COLUMN_FLAGS_COLLATION_CONNECTION_NUM, COLUMN_FLAGS_SQL_MODE, COLUMN_FLAGS_DEFAULT_WEEK_FORMAT maybe in future.. someone do it =) well, how could i put this code in launchpad, any fast howto? i know how to work with git / github, there's something similar? it's bazaar based?
updated files at https://mariadb.atlassian.net/browse/MDEV-4581 too =) i will study lauchpad in some time now, but if possible review the patch could be nice i don't know if string concats are ok, and if i done some overflow, or bad casts... must test thank :)
ops :( i done tab-space conversion and sql_cache.cc and sql_cache.h are very big now :( sending the patch now again 2013/5/25 Roberto Spadim <roberto@spadim.com.br>
updated files at https://mariadb.atlassian.net/browse/MDEV-4581 too =) i will study lauchpad in some time now, but if possible review the patch could be nice i don't know if string concats are ok, and if i done some overflow, or bad casts... must test
thank :)
-- Roberto Spadim SPAEmpresarial
sergei, i will port it to mariadb 10.0.2, any work was done? 2013/5/25 Roberto Spadim <roberto@spadim.com.br>
ops :( i done tab-space conversion and sql_cache.cc and sql_cache.h are very big now :( sending the patch now again
2013/5/25 Roberto Spadim <roberto@spadim.com.br>
updated files at https://mariadb.atlassian.net/browse/MDEV-4581 too =) i will study lauchpad in some time now, but if possible review the patch could be nice i don't know if string concats are ok, and if i done some overflow, or bad casts... must test
thank :)
-- Roberto Spadim SPAEmpresarial
-- Roberto Spadim SPAEmpresarial
well =) done https://mariadb.atlassian.net/browse/MDEV-4581 bye =) now i will study launchpad =)
hi sergei with last patch, i think the job on https://mariadb.atlassian.net/browse/MDEV-4581 could be reviewed, and maybe considered to include in mariadb todo: better flags values (sql_mode is int, should be converted to string, connection charsets too, and add flag timezone informations) well that's all , i will focus on some qc features: =============================================================== QUERY CACHE 1) Query cache SQL interface (MDEV-4584) QUERY CACHE LOCK <TIMEOUT> QUERY CACHE UNLOCK QUERY CACHE REMOVE <ID> QUERY CACHE REMOVE TABLE LIKE <TABLE> internally add a query_cache.lock_current_connection with connection id show in processlist who have QUERY_CACHE_OWNER and FLUSH_TABLES_WITH_READ_LOCK obs: here i don't know how to change parser to understand the new command "QUERY CACHE" 2) Query cache prunes (MDEV-4582) session_var: query_cache_min_expend_time query_cache_min_result_rows query_cache_min_read_rows obs: i think it's the easiest feature to include (maybe i will start here) 3) Per table max queries in cache (MDEV-4588) ALTER TABLE xxxx query_cache_max_queries=99999 obs: here in don't know how to change .frm file to include this information and i don't know how to change parser to understand query_cache_max_queries as a parameter like COMMENT='some comment' 4) Cleanup, Remove unused query cache (MDEV-4589) add query_cache_query.total_qc_search_time add session_var: query_cache_cleanup_stats_hits when FLUSH QUERY CACHE: (or maybe when low mem, i will think about where to put this automatic remove procedure) remove queries with: ((total_qc_search_time/hits)>(result_expend_time) && hits>query_cache_cleanup_stats_hits) there's some others prune methods i will consider when implementing this 5) Cleanup, mode (MDEV-4583) internal, user procedure (in mysql schema) obs: i don't know how to call a user procedure inside mariadb code =============================================================== if i have some doubts could i ask here?
Hi, Roberto! On May 24, Roberto Spadim wrote:
Hi guys i'm reading query cache code, and i have some doubts... mariadb-10.0.1 source:
1)PROTOCOL_LOCAL: at line: 1412 (Query_cache::store_query) we have this comment: /* PROTOCOL_LOCAL results are not cached. */
what's PROTOCOL_LOCAL ? it's the unix socket? windows pipe? embedded? memcache? mysql? another thing like this? or it's the internal mariadb source running queries?
It's not used. Dead code.
2)the query_size=0 (MDEV-4516) i was reading at line 1680
if (is_disabled() || thd->locked_tables_mode || thd->variables.query_cache_type == 0) goto err;
and line : 1694 DBUG_ASSERT(query_cache_size != 0); // otherwise cache would be disabled
why it's isn't used in query_cache_information plugin? *check that in line 1680 it test the "query_cache_type" too, * *in MDEV-4516 report about size=0 we didn't tested this case!!!*
query_cache_information plugin uses qc->is_disabled() this should cover all cases.
3) i was at line 1886: DBUG_PRINT("qcache", ("\ long %d, 4.1: %d, bin_proto: %d, more results %d, pkt_nr: %d, \ CS client: %u, CS result: %u, CS conn: %u, limit: %lu, TZ: 0x%lx, \ sql mode: 0x%llx, sort len: %lu, conncat len: %lu, div_precision: %lu, \ def_week_frmt: %lu, in_trans: %d, autocommit: %d",
is this the "primary key" of the query cache!?!?!?!?!?!?! could we use a sha1 or another "primary key" method (index [] of the query in cache + '';'+ 5 bytes of a hash key to compare if the index still with the same query) to easly show query entry "number", and allow a "delete from query_cache_information where key=xxx" function?
at line 1938 we have this maybe the query position on cache ("primary key of query cache")?: DBUG_PRINT("qcache", ("Query have result 0x%lx", (ulong) query));
No, information_schema tables are read-only. Regards, Sergei
participants (2)
-
Roberto Spadim -
Sergei Golubchik