introduction
common_schema is a framework for MySQL server administration.
common_schema provides with query scripting, analysis & informational views, and a function library, allowing for easier administration and diagnostics for MySQL. It introduces SQL based tools which simplify otherwise complex shell and client scripts, allowing the DBA to be independent of operating system, installed packages and dependencies.
common_schema's components are:
- Views library: set of powerful views, typically operating and presenting server metadata
- Routines library: set of complementary routines to MySQL, including metadata handling
- QueryScript: programming language aimed for SQL scripting, seamlessly combining scripting power such as flow control & variables with standard SQL statements or RDBMS-specific commands.
- rdebug: debugger and debugging API for MySQL stored routines
It is a self contained schema, compatible with all MySQL >= 5.1 servers. Installed by importing the schema into the server, there is no need to configure nor compile. No special plugins are required, and no changes to your configuration.
common_schema has a small footprint (well under 1MB).
What can common_schema do?
Here's a quick peek at some of common_schema's capabilities:
Query scripting & execution
Run QueryScript code:
foreach($table, $schema, $engine: table in sakila)
if ($engine = 'InnoDB')
ALTER TABLE :$schema.:$table ENGINE=InnoDB ROW_FORMAT=Compressed KEY_BLOCK_SIZE=8;
Throttle queries:
SELECT Id, Name, throttle(1) FROM world.City ORDER BY Population DESC; ...
Schema analysis
Detect duplicate keys:
SELECT redundant_index_name, sql_drop_index FROM redundant_keys; +----------------------+----------------------------------------------------------+ | redundant_index_name | sql_drop_index | +----------------------+----------------------------------------------------------+ | rental_date_2 | ALTER TABLE `sakila`.`rental` DROP INDEX `rental_date_2` | +----------------------+----------------------------------------------------------+
Monitoring
Show status change on your server over time:
mysql> SELECT * FROM common_schema.global_status_diff_nonzero; +-----------------------+------------------+------------------+---------------------+---------------------+------------------------+ | variable_name | variable_value_0 | variable_value_1 | variable_value_diff | variable_value_psec | variable_value_pminute | +-----------------------+------------------+------------------+---------------------+---------------------+------------------------+ | handler_read_rnd_next | 3871 | 4458 | 587 | 58.7 | 3522 | | handler_write | 10868 | 11746 | 878 | 87.8 | 5268 | | open_files | 39 | 37 | -2 | -0.2 | -12 | | select_full_join | 3 | 4 | 1 | 0.1 | 6 | | select_scan | 30 | 32 | 2 | 0.2 | 12 | +-----------------------+------------------+------------------+---------------------+---------------------+------------------------+
Process watch
Find the GRANTEE for active processes:
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 | +--------+------------+---------------------+------------------------+--------------+--------------+----------+---------+-------------------+---------------------+
See top running processes:
mysql> SELECT * FROM common_schema.processlist_top; +----------+-------------+--------------+-----------+-------------+---------+------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+------------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | TIME_MS | +----------+-------------+--------------+-----------+-------------+---------+------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+------------+ | 3598334 | system user | | NULL | Connect | 4281883 | Waiting for master to send event | NULL | 4281883102 | | 3598469 | replica | sql01:51157 | NULL | Binlog Dump | 4281878 | Has sent all binlog to slave; waiting for binlog to be updated | NULL | 4281877707 | | 31066726 | replica | sql02:48924 | NULL | Binlog Dump | 1041758 | Has sent all binlog to slave; waiting for binlog to be updated | NULL | 1041758134 | | 3598335 | system user | | NULL | Connect | 195747 | Has read all relay log; waiting for the slave I/O thread to upda | NULL | 0 | | 39946702 | store | app03:46795 | datastore | Query | 0 | Writing to net | SELECT * FROM store_location | 27 | | 39946693 | store | app05:51090 | datastore | Query | 0 | Writing to net | SELECT store.store_id, store_location.zip_code FROM store JOIN store_location USING (store_id) WHERE store_class = 5 | 54 | | 39946692 | store | sql01:47849 | datastore | Query | 0 | Writing to net | SELECT store.store_id, store_location.zip_code FROM store JOIN store_location USING (store_id) WHERE store_class = 34 | 350 | +----------+-------------+--------------+-----------+-------------+---------+------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+------------+
common_schema provides plenty of more functionality. The documentation is extensive!
RISKS
Please refer to the risks page.LICENSE
common_schema is released under the GPL license.common_schema - DBA's Framework for MySQL Copyright (C) 2011-2013, Shlomi Noach This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. A copy of the GNU General Public License is available at http://www.gnu.org/licenses/
AUTHOR
Shlomi Noach