common_schema, 2013

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.

This session highlights some of common_schema's components.

http://shlomi-noach.github.io/common_schema/introduction.html

Transcript

1. common_schema 2.2 D B A ' s f r a m e w o r k f o r M y S Q L S h l o m i N o a c h h t t p : / / o p e n a r k . o r g
2. Copyright © 2013, Shlomi Noach common_schema D B A ' s f r a m e w o r k f o r M y S Q L common_schema 2.2 D B A ' s f r a m e w o r k f o r M y S Q L  About  Views & eval()  Routines  QueryScript  Introducing rdebug
3. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach About myself  Shlomi Noach, geek  Software engineer, DBA  Author of open source tools common_schema, openark kit, mycheckpoint, others.  Blog on all things MySQL http://openark.org  Employed by
4. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach About common_schema  common_schema is an open source project, licensed under the GPL License.  Authored by Shlomi Noach http://openark.org  Major contributions by Roland Bouman http://rpbouman.blogspot.com  Several contributions & suggestions by the community (Thanks!)
5. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach About common_schema  common_schema is a framework which includes: – Views: analytics, security, action-taking, ... – Routines: text, temporal, process, security, ... – QueryScript: an SQL oriented scripting language & interpreter – rdebug: debugger and debugging API for MySQL stored routines (alpha).
6. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach About common_schema  It is a schema that lies next to INFORMATION_SCHEMA.  It lies completely within the MySQL server, and does not require external packages or dependencies. No Perl scripts nor UDFs or plugins.
7. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Getting & Installing  common_schema distribution is a SQL file.  Currently hosted on Google Code: http://code.google.com/p/common-schema/  Install by importing SQL file into MySQL: bash$ mysql < /tmp/common_schema-2.2.sql complete - Base components: installed - InnoDB Plugin components: installed - Percona Server components: not installed - TokuDB components: partial install: 1/2 Installation complete. Thank you for using common_schema!
8. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Views  Views providing non-trivial information about your table design, keys, grants, processes, transactions, locks, ...  While INFORMATION_SCHEMA provides with complete info, it is ofter difficult to aggregate. It is sometimes too normalized, and at other times too de-normalized.  Some metadata is simply not available in INFORMATION_SCHEMA.
9. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Views  common_schema's views will present with valuable knowledge  And will typically offer recommendations or possible actions based on that knowledge.  common_schema also provides the mechanism to apply those recommendations.
10. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Views: redundant_keys  Find duplicate/redundant keys in your schema. Recommend DROP statements. http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/redundant_keys.html – Similar to pt-duplicate-key-checker mysql> select * from redundant_keys where table_schema='sakila'\G table_schema: sakila table_name: rental redundant_index_name: rental_date_2 redundant_index_columns: rental_date redundant_index_non_unique: 1 dominant_index_name: rental_date dominant_index_columns: rental_date, inventory_id, customer_id dominant_index_non_unique: 0 sql_drop_index: ALTER TABLE `sakila`.`rental` DROP INDEX `rental_date_2`
11. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Views: sql_range_partitions  Analyze your range partitioned tables. Offers the ADD/REORGANIZE PARTITION statements to roll partitions: mysql> create table test.report … partition by range (…) ; mysql> select * from sql_range_partitions where table_name='report' \G table_schema: test table_name: report count_partitions: 7 sql_drop_first_partition: alter table `test`.`report` drop partition `p0` sql_add_next_partition: alter table `test`.`report` reorganize partition `p6` into ( partition `p_20090701000000` values less than (1246395600) /* 2009-07-01 00:00:00 */ , partition p_maxvalue values less than MAXVALUE )
12. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Views: sql grants  Provide SQL based access to user accounts and their grants.  Offers the relevant GRANT, REVOKE and DROP statements for such grants: GRANTEE: 'world_user'@'localhost' user: world_user host: localhost priv_level: `world`.* priv_level_name: schema current_privileges: INSERT, SELECT, UPDATE sql_grant: GRANT INSERT, SELECT, UPDATE ON `world`.* TO 'world_user'@'%' sql_revoke: REVOKE INSERT, SELECT, UPDATE ON `world`.* FROM 'world_user'@'%'
13. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach SQL generation & eval()  Views above present with “SQL columns”, offering a statement to execute.  This is at the heart of common_schema's views, and is part of the server-side mechanism the framework strongly supports.  The eval() routine accepts such SQL columns and executes (evaluates) them.
14. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach eval()  Accepts a query returning a text column. Column data is expected to be SQL statements to be executed. mysql> call eval(" SELECT sql_add_next_partition FROM sql_range_partitions WHERE table_schema='webprod'"); Query OK, 0 rows affected -- A new partition has just been added on -- all range-partitioned tables in `webprod`
15. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach eval() KILL QUERY 123 KILL QUERY 129 KILL QUERY 598 KILL QUERY 620 KILL QUERY 702 Query eval() Execute query Execute statements
16. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Views: innodb_transactions  Which transactions are running? – For how long? – How long are they being idle? Locked? – What queries are they issuing?  Recommend KILL, apply with eval() mysql> call eval(" SELECT sql_kill_query FROM innodb_transactions WHERE trx_idle_seconds >= 30 "); -- All idle transactions have just been killed
17. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Views: others  processlist_grantees: identifies the user connection with its associated account (missing info in MySQL)  auto_increment_columns: find “free space” or “usage” for AUTO_INCREMENT values  slave_status: get Seconds_behind_master via real SQL query  last_query_profiling :aggregated profile for last executed query
18. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Views: processlist_grantees mysql> select * from processlist_grantees\G ID: 41165491 USER: dbuser HOST: web00.myapp:40049 DB: profile COMMAND: Query TIME: 0 STATE: updating INFO: DELETE FROM locks WHERE id = 'helper' AND dt < '2013-04-17 15:09:50' GRANTEE: 'dbuser'@'%.myapp' grantee_user: dbuser grantee_host: %.myapp is_super: 0 is_repl: 0 is_current: 0 sql_kill_query: KILL QUERY 41165491 sql_kill_connection: KILL 41165491
19. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Views: others  sql_accounts: block/release accounts  similar_grants: detect security roles  global_status_diff_nonzero: detect status change  innodb_locked_transactions: who locks who, on which query? Recommend KILL  more... http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/process_views.html http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/innodb_plugin_views.html http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/security_views.html http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/schema_analysis_views.html
20. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Routines  common_schema offers more than 60 useful stored routines, part of the framework's function library.  From text parsing & manipulation, through process diagnostics, query analysis & dynamic execution, to security routines, the function library extends and complements MySQL's own functions. http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/execution_routines.html http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/text_routines.html http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/security_routines.html http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/process_routines.html
21. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Routines: security  killall() kills connections by matching text with user, host or grantee.  security_audit() audits server's privileges tables and configuration to detect such threats as empty or duplicate passwords, excessive privileges, permissive hosts etc.  duplicate_grantee(): duplicates an account mysql> call killall('analytics'); mysql> call killall('localhost');
22. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Routines: text  extract_json_value(): Extract value from JSON notation via Xpath.  replace_sections(): Search and replace text appearing between section.  get_num_tokens(): Return number of tokens in delimited text. mysql> select common_schema.get_num_tokens( 'the quick brown fox', ' '); => 4 mysql> select common_schema.split_token( 'the quick brown fox', ' ', 3); => 'brown'
23. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Routines: text mysql> set @json := '{ "menu": { "id": "file", "value": "File", "popup": { "menuitem": [ {"value": "New", "onclick": "create()"}, {"value": "Open", "onclick": "open()"}, {"value": "Close", "onclick": "close()"} ] } } }'; mysql> select extract_json_value(@json, '//id') AS result; +--------+ | result | +--------+ | file | +--------+
24. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Routines: general  query_checksum(): Checksum the result set of a query.  crc64(): Return a 64 bit CRC of given input, as unsigned big integer.  random_hash(): Return a 64 bit CRC of given input, as unsigned big integer. mysql> call query_checksum( 'select code, name from world.Country order by code'); +----------------------------------+ | checksum | +----------------------------------+ | 79221e8d040e33cd3262f1680b4c8e54 | +----------------------------------+
25. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Routines: SQL & execution  eval() evaluates the queries generated by a given query.  exec(), exec_file() dynamically executes a given query or semicolon delimited list of queries.  run(), run_file() execute QueryScript code. mysql> call exec(' CREATE TABLE test.t(id INT); INSERT INTO test.t VALUES (2),(3),(5); ');
26. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach QueryScript  A SQL oriented scripting language, offering tight integration with SQL commands, easy and familiar control flow syntax and high level abstraction of complex tasks.  common_schema implements QueryScript via interpreter, based on stored routines.  This makes QueryScript suitable for administration and bulk tasks, not for OLTP tasks. http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/query_script.html
27. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Why QueryScript?  Stored routine programming is a pain: – Requires one to actually store the routine within the schema: can't just run something. – Syntax is cumbersome (ANSI:SQL). – Does not offer deeper insight into MySQL's limitations and bottlenecks. – Does not provide with syntax for oh-so- common tasks – Verbose. Can't see the forest for the trees.
28. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Compare: stored routine DELIMITER $$ DROP PROCEDURE IF EXISTS some_proc $$ CREATE PROCEDURE some_proc() READS SQL DATA SQL SECURITY INVOKER begin declare some_id bigint unsigned default null; declare done tinyint default 0; declare my_cursor cursor for SELECT some_id FROM some_table; declare continue handler for NOT FOUND set done = 1; open my_cursor; read_loop: loop fetch my_cursor into some_id; if done then leave read_loop; end if; -- do something with some_id end loop; close my_cursor; end $$ DELIMITER ;
29. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Compare: QueryScript set @script := ' foreach ($some_id: select some_id from some_table) { -- do something with $some_id } '; call run(@script);  Significantly less overhead  Familiar C-family syntax  No need to store the code in schema  Can execute script directly from file call run('/path/to/script.qs');
30. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach QueryScript: variables  Create variables which are known to initialize as NULL and are known to clean up as they exit scope.  Can be expanded and used where MySQL does not allow variables. var $count := 20; var $pop := 1000000; var $tbl := 'City'; select * from world.:${tbl} where population >= $pop limit :${count};
31. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach QueryScript: conditions  Familiar if, while or loop-while statements.  But conditions are also tightly integrated with SQL, and so queries make for valid conditions. if (@val > 3) { pass; } while (delete from world.Country where Continent = 'Asia' limit 10) { throttle 2; }
32. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach QueryScript: foreach  A sophisticated looping device, allowing iteration over queries, sets, numbers range, databases, tables...  But also acknowledges MySQL limitations and provides with a safer access method.  Table iteration uses INFORMATION_SCHEMA optimizations to avoid excessive locks: foreach($tbl, $scm: table like wp_posts) alter table :${scm}.:${tbl} add column post_geo_location VARCHAR(128);
33. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach QueryScript: foreach foreach($year: 2001:2009) delete from sakila.rental where rental_date >= CONCAT($year, '-07-01') and rental_date < CONCAT($year, '-09-01'); foreach($shard: {US, GB, Japan, FRA}) create database dbshard_:${shard}; foreach($scm: schema like wp%) { create table :$scm.wp_likes( id int, data VARCHAR(128)); } foreach ($some_id: select some_id from some_table) {... }
34. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach QueryScript: split  Automagically breaks a bulk operation into smaller chunks.  Supports DELETE, UPDATE, INSERT...SELECT, REPLACE...SELECT  Supports single and multi table statements split (delete from sakila.rental where rental_date < NOW() - interval 5 year); split (insert into world.City_duplicate select * from world.City) { throttle 1; }
35. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach QueryScript: split split(update sakila.film_actor set last_update=now() where actor_id != 4) { select $split_columns as columns, $split_range_start as range_start, $split_range_end as range_end, $split_total_rowcount as total; } +----------------------+-------------+------------+-------+ | columns | range_start | range_end | total | +----------------------+-------------+------------+-------+ | `actor_id`,`film_id` | '1','1' | '39','293' | 978 | +----------------------+-------------+------------+-------+ +----------------------+-------------+------------+-------+ | columns | range_start | range_end | total | +----------------------+-------------+------------+-------+ | `actor_id`,`film_id` | '39','293' | '76','234' | 1978 | +----------------------+-------------+------------+-------+ +----------------------+-------------+-------------+-------+ | columns | range_start | range_end | total | +----------------------+-------------+-------------+-------+ | `actor_id`,`film_id` | '76','234' | '110','513' | 2978 | +----------------------+-------------+-------------+-------+
36. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach QueryScript: split, example  Real world use case: need to copy 120,000,000 rows belonging to a specific partition from range-partitioned table 2013-02 2013-03 2013-04 2013-05 2013-06 2013-05
37. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach QueryScript: split, example split ( { index: PRIMARY, start: '2013-05-01', step: 20000 } : insert into one_month_sample select * from table_with_many_month_partitions where entry_date < DATE('2013-05-01') ) { if ($split_range_start >= DATE('2013-05-01')) Break; throttle 1; }  We provide hints to the split statement: choosing index, start position, chunk size.  Terminate when out of range.
38. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach QueryScript: example  Create shards & tables, and copy row data: -- iterate shards foreach ($shard: {US, GB, Japan, FRA}) { create database dbshard_:${shard}; -- iterate tables foreach ($tbl: table in original_shard) { create table dbshard_:${shard}.:${tbl} like original_shard.:$tbl; -- copy table data split (insert into dbshard_:${shard}.:${tbl} select * from original_shard.:$tbl) { -- show progress select $shard, $tbl, $split_total_rowcount; throttle 1; } } }
39. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach QueryScript: more goodies  throttle statement controls script execution time, reduces server load  Local variables auto-cleanup, can be used (expanded) in statements where variables not allowed (table names, LIMIT value etc.)  try-catch statement is available: easy error handling mechanism  echo, eval, throw statements make for easy development
40. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach rdebug  common_schema introduces rdebug: debugger and debugging API for MySQL stored routines.  A server side solution, based in itself on stored routines.  Provides routine API calls to manage debugging sessions of other routines.  At this time (April 2013) in alpha stage http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/rdebug.html
41. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach rdebug  Uses code injection; one must “compile” her routine with/out debug code. – Does not emulate code; retains logic of one's routines, with some limitations.  Supports: – Step into/over/out – Watch variables, modify variables – Set breakpoint, run to breakpoint – Watch stack state, statements
42. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach rdebug  common_schema provides with stored routine API. – One may debug on command line using mysql client – Or one may wrap with GUI tool to call upon routine API  Awaiting GUI client implementations!
43. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach rdebug mysql [debugger]> call rdebug_step_into(); +-------------+----------------+---------------+--------------+---------------------+ | stack_level | routine_schema | routine_name | statement_id | entry_time | +-------------+----------------+---------------+--------------+---------------------+ | 1 | test | review_colors | 145 | 2013-04-08 15:41:28 | +-------------+----------------+---------------+--------------+---------------------+ +----------------+---------------+---------------+---------------+----------------+ | routine_schema | routine_name | variable_name | variable_type | variable_value | +----------------+---------------+---------------+---------------+----------------+ | test | review_colors | current_color | local | white | | test | review_colors | current_count | local | 10 | | test | review_colors | done | local | 0 | +----------------+---------------+---------------+---------------+----------------+ +----------------+---------------+--------------+-----------------------------------------+ | routine_schema | routine_name | statement_id | statement | +----------------+---------------+--------------+-----------------------------------------+ | test | review_colors | 145 | call review_single_color(current_color) | +----------------+---------------+--------------+-----------------------------------------+ mysql [debugger]> call rdebug_set_variable('current_color', 'orange'); mysql [debugger]> call rdebug_step_over();
44. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach call for help()  common_schema documentation is available in these formats: – Online documentation (directly in code repository) – Bundled HTML download – Inline: Just call for help(): call help('split'); +--------------------------------------------------------------------------------+ | help | +--------------------------------------------------------------------------------+ | QueryScript Flow Control: split statement | | | | SYNOPSIS | | | | Single table operations, autodetect mode:
45. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Roadmap & future plans  Everything is developed by demand. There are a lot of pending ideas.  Priority given to solutions I need today.  Nevertheless: – QueryScript will evolve. Already pending release are functions() – QueryScript debugging on roadmap – Other ideas
46. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Support common_schema  Download and try it out  Report issues http://code.google.com/p/common-schema/issues/list  Happy to receive ideas and contributions http://bit.ly/UPC3vh http://code.google.com/p/common-schema/issues/entry? template=Request%20for%20new%20component  Above all else: spread the word!
47. D B A ' s f r a m e w o r k f o r M y S Q L common_schema Copyright © 2013, Shlomi Noach Thank you!  Visit http://openark.org for news & updates.  Other open source projects: – openark kit http://openarkkit.googlecode.com/svn/trunk/openarkkit/doc/html/introduction.html – mycheckpoint http://code.openark.org/forge/mycheckpoint