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.
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 BSD license.
Copyright (c) 2011 - 2012, Shlomi Noach All rights reserved. Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met: * Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer. * Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution. * Neither the name of the organization nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission. THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
AUTHOR
Shlomi Noach