Revision 68 of common_schema is out, and includes some interesting features:
- eval(): Evaluates the queries generated by a given query
- match_grantee(): Match an existing account based on user+host
- processlist_grantees: Assigning of GRANTEEs for connected processes
- candidate_keys: Listing of prioritized candidate keys: keys which are UNIQUE, by order of best-use.
- easter_day(): Returns DATE of easter day in given DATETIME’s year.
Let’s take a slightly closer look at these:
eval()
I’ve dedicated this blog post on MySQL eval() to describe it. In simple summary: eval() takes a query which generates queries (most common use queries on INFORMATION_SCHEMA) and auto-evaluates (executes) those queries. Read more
match_grantee()
As presented in Finding CURRENT_USER for any user, I’ve developed the algorithm to match a connected user+host details (as presented with PROCESSLIST) with the grantee tables (i.e. the mysql.user table), in a manner which simulates the MySQL server account matching algorithm.
This is now available as a stored function: given a user+host, the function returns with the best matched grantee. Read more
processlist_grantees
This view relies on the above, and maps the entire PROCESSLIST onto GRANTEEs. The view maps each process onto the GRANTEE (MySQL account) which is the owner of that process. Surprisingly, MySQL does not provide one with such information.
The view also provides with the following useful metadata:
- Is said process executes under a SUPER privilege?
- Is this a replication thread, or serving a replicating client?
- Is this process the current connection (myself)?
In the spirit of common_schema, it provides with the SQL commands necessary to KILL and KILL QUERY for each process. A sample output:
mysql> SELECT * FROM common_schema.processlist_grantees; +--------+------------+---------------------+------------------------+--------------+--------------+----------+---------+-------------------+---------------------+ | ID | USER | HOST | GRANTEE | grantee_user | grantee_host | is_super | is_repl | sql_kill_query | sql_kill_connection | +--------+------------+---------------------+------------------------+--------------+--------------+----------+---------+-------------------+---------------------+ | 650472 | replica | jboss00.myweb:34266 | 'replica'@'%.myweb' | replica | %.myweb | 0 | 1 | KILL QUERY 650472 | KILL 650472 | | 692346 | openarkkit | jboss02.myweb:43740 | 'openarkkit'@'%.myweb' | openarkkit | %.myweb | 0 | 0 | KILL QUERY 692346 | KILL 692346 | | 842853 | root | localhost | 'root'@'localhost' | root | localhost | 1 | 0 | KILL QUERY 842853 | KILL 842853 | | 843443 | jboss | jboss03.myweb:40007 | 'jboss'@'%.myweb' | jboss | %.myweb | 0 | 0 | KILL QUERY 843443 | KILL 843443 | | 843444 | jboss | jboss03.myweb:40012 | 'jboss'@'%.myweb' | jboss | %.myweb | 0 | 0 | KILL QUERY 843444 | KILL 843444 | | 843510 | jboss | jboss00.myweb:49850 | 'jboss'@'%.myweb' | jboss | %.myweb | 0 | 0 | KILL QUERY 843510 | KILL 843510 | | 844559 | jboss | jboss01.myweb:37031 | 'jboss'@'%.myweb' | jboss | %.myweb | 0 | 0 | KILL QUERY 844559 | KILL 844559 | +--------+------------+---------------------+------------------------+--------------+--------------+----------+---------+-------------------+---------------------+
Finally, it is now possible to execute the following: “Kill all slow queries which are not executed by users with the SUPER privilege or are replication threads”. To just generate the commands, execute:
mysql> SELECT sql_kill_connection FROM common_schema.processlist_grantees WHERE is_super = 0 AND is_repl = 0;
Sorry, did you only want to kill the queries? Those which are very slow? Do as follows:
mysql> SELECT sql_kill_connection FROM common_schema.processlist_grantees JOIN INFORMATION_SCHEMA.PROCESSLIST USING(ID) WHERE TIME > 10 AND is_super = 0 AND is_repl = 0;
But, really, we don’t just want commands. We really want to execute this!
Good! Step in eval():
mysql> CALL common_schema.eval('SELECT sql_kill_query FROM common_schema.processlist_grantees JOIN INFORMATION_SCHEMA.PROCESSLIST USING(id) WHERE TIME > 10 AND is_super = 0 AND is_repl = 0');
candidate_keys
A view which lists the candidate keys for tables and provides ranking for those keys, based on some simple heuristics.
This view uses the same algorithm as that used by oak-chunk-update and oak-online-alter-table, tools in the openark kit. So it provides with a way to choose the best candidate key to walk through a table. At current, a table’s PRIMARY KEY is always considered to be best, because of InnoDB’s structure of clustered index. But I intend to change that as well and provide general recommendation about candidate keys (so for example, I would be able to recommend that the PRIMARY KEY is not optimal for some table).
Actually, after a discussion initiated by Giuseppe and Roland, starting here and continuing on mail, there are more checks to be made for candidate keys, and I suspect the next version of candidate_keys will be more informational.
easter_day()
Many thanks to Roland Bouman who suggested his code for calculating easter day for a given year. Weehee! This is the first contribution to common_schema! Read more
Get it
common_schema is an open source project. It is released under the BSD license.