common_schema 1.1 released: split(), try-catch, killall(), profiling

I’m very happy to announce the release of common_schema, version 1.1 (revision 300).

This version boasts with compelling new features: innovative QueryScript syntax, libraries, views which add to your skills as a DBA, making some maintenance and management tasks a breeze.

• QueryScript, split statement: automagically break long queries into smaller chunks, avoid long locks and reduce query/transaction overhead
• QueryScript, try-catch statement: just try { something; } catch { act_on_error; }.
• killall(): quickly kill connections based on grantee/user/host information.
• profiling/profiling_last: utility views to assist in query profiling diagnostics
• 1 size fits all: a single installer which auto-recognizes available server features and enables respective common_schema features accordingly.
• QueryScript performance boost
• much much more…

Not familiar with common_schema? It allows you to do stuff on server side, by selecting from views, calling upon useful routines or writing easy-to-manage scripts.

I’m suggesting that common_schema should be a really-should-have tool to accompany your MySQL install. Did I say “tool”? It’s merely a schema. But it makes for a great framework:

In High Performance MySQL, 3rd edition, Baron Schwartz describes common_schema:

The common_schema is to MySQL as jQuery is to javaScript

Reviewing highlights for version 1.1:

QueryScript

QueryScript is a scripting language. It sees some major improvements here. I’ve made some speed boosts by avoiding using temporary tables, and by using string parsing instead.

Without doubt the two most handy statements added to QueryScript are:

• split: automagically break a long query into smaller, distinct chunks, and execute those iteratively. Just write your query; split will parse it, analyze it, rewrite it, break your table into parts, iterate your table and apply query for each chunk of rows. You can reduce lock time, avoid huge transactions and give your server room to breathe on operations such as massive updates of rows, transferring of rows between tables, massive purging of rows etc. Consider: the following query will execute 1,000 rows at a time, and the script will throttle execution so as to sleep in between chunks. And you need know nothing about how it works internally (though it’s quite interesting):

create table world.City_dup like world.City;
split (insert into world.City_dup select * from world.City)
{
  throttle 2;
}

• try-catch: if, like me, you are frustrated with stored routines way of handling errors, QueryScript now offers you familiar (yet enhanced) form of try something catch do_something_on_error. It is limited in that you cant have a catch for particular error codes – MySQL does not provide such info on server side. Nevertheless, consider:

while (true)
{
  try
  {
    -- Attempt query which is expected to abort on deadlock:
    UPDATE some_table SET some_column = 1 WHERE some_condition;
    -- Got here? This means query is successful! We can leave now.
    break;
  }
  catch
  {
    -- Apparently there was a deadlock. Rest, then loop again until succeeds
    sleep 1;
  }
}

QueryScript also adds:

• eval: evaluate SQL statements on the fly. I’ve got some very cool use cases already in production.
• sleep: just… sleep.
• pass: similar to Python’s pass statement, this statement does nothing and makes for a placeholder.

QueryScript variables now support:

• Declare & assign syntax: var $sum := 0
• New expansion syntax: DELETE FROM t LIMIT :${number_of_rows}, or CREATE TABLE customer_:${shard_number}_details
  
• Support for expanded variables in expressions, throttle, sleep, throw statements.

Routines:

Plenty of new routines. Most notable:

• killall(): much like Unix killall command, this routine kills connections based on names, rather than process IDs. Names are grantee name, or just the user part, or just the host part. Which allows for quick killing of all connections coming from a specific user or host:

CALL killall('host3.analytics.mycompany.com');
CALL killall('reporting_user');

• table_exists(): test for (isn’t it clear?) table existence. This uses INFORMATION_SCHEMA optimizations: it’s a lightweight query.

SELECT table_exists('sakila', 'rental') AS does_it_exist;

We also have text manipulation routines: encode_xml() and decode_xml(), strip_urls(), prettify_message()

Views

• Profiling views, inspired by How to convert MySQL’s SHOW PROFILES into a real profile: query_profiling & last_query_profiling:

mysql> SET PROFILING := 1;

mysql> SELECT COUNT(*) FROM sakila.nicer_but_slower_film_list INTO @dummy;

mysql> SELECT * FROM last_query_profiling;
+----------+----------------------+-------------+--------------------+-------------------------+--------------------+------------+
| QUERY_ID | STATE                | state_calls | state_sum_duration | state_duration_per_call | state_duration_pct | state_seqs |
+----------+----------------------+-------------+--------------------+-------------------------+--------------------+------------+
|       41 | checking permissions |           5 |           0.000320 |            0.0000640000 |               0.33 | 5,6,7,8,9  |
|       41 | cleaning up          |           1 |           0.000007 |            0.0000070000 |               0.01 | 31         |
|       41 | closing tables       |           1 |           0.000016 |            0.0000160000 |               0.02 | 29         |
|       41 | Copying to tmp table |           1 |           0.042363 |            0.0423630000 |              44.34 | 15         |
|       41 | Creating tmp table   |           1 |           0.000123 |            0.0001230000 |               0.13 | 13         |
|       41 | end                  |           1 |           0.000004 |            0.0000040000 |               0.00 | 23         |
|       41 | executing            |           2 |           0.000014 |            0.0000070000 |               0.01 | 14,22      |
|       41 | freeing items        |           2 |           0.000216 |            0.0001080000 |               0.23 | 25,27      |
|       41 | init                 |           1 |           0.000012 |            0.0000120000 |               0.01 | 20         |
|       41 | logging slow query   |           1 |           0.000004 |            0.0000040000 |               0.00 | 30         |
|       41 | Opening tables       |           1 |           0.028909 |            0.0289090000 |              30.26 | 2          |
|       41 | optimizing           |           2 |           0.000026 |            0.0000130000 |               0.03 | 10,21      |
|       41 | preparing            |           1 |           0.000018 |            0.0000180000 |               0.02 | 12         |
|       41 | query end            |           1 |           0.000004 |            0.0000040000 |               0.00 | 24         |
|       41 | removing tmp table   |           3 |           0.000130 |            0.0000433333 |               0.14 | 18,26,28   |
|       41 | Sending data         |           2 |           0.016823 |            0.0084115000 |              17.61 | 17,19      |
|       41 | Sorting result       |           1 |           0.006302 |            0.0063020000 |               6.60 | 16         |
|       41 | starting             |           1 |           0.000163 |            0.0001630000 |               0.17 | 1          |
|       41 | statistics           |           1 |           0.000048 |            0.0000480000 |               0.05 | 11         |
|       41 | System lock          |           1 |           0.000017 |            0.0000170000 |               0.02 | 3          |
|       41 | Table lock           |           1 |           0.000018 |            0.0000180000 |               0.02 | 4          |
+----------+----------------------+-------------+--------------------+-------------------------+--------------------+------------+

common_schema meta info is in the status view, which can be used, for example, in bug reports. It indicated version, revision, time and status of installation process.

Installer

common_schema comes with an invisible installer. It’s just a SQL file, imported via SOURCE command or your favorite import method. But, once base components are installed, it activates itself to spawn a smart-mode install phase, where it checks upon existing MySQL server features, and adding respective common_schema features. So, if InnoDB plugin is present, you get the InnoDB plugin views in common_schema. If this is a Percona Server, you also get those related views. This makes for a single distribution file, as opposed to 3 different distributions in previous versions.

Documentation

There are no compromises here. Documenting common_schema takes more time than writing & testing it. But everything is well documented. You can read the documentation online, or download a bundle, or call for help() from within common_schema: the documentation is internal, too.

root@mysql-5.1.51> CALL help('try');
+--------------------------------------------------------------------------------+
| help                                                                           |
+--------------------------------------------------------------------------------+
| QueryScript Flow Control: try-catch statement                                  |
|                                                                                |
| SYNOPSIS                                                                       |
|                                                                                |
|                                                                                |
|                                                                                |
|        try                                                                     |
|          statement;                                                            |
|        catch                                                                   |
|          statement;                                                            |
|                                                                                |
|                                                                                |
|                                                                                |
| DESCRIPTION                                                                    |
|                                                                                |
| try-catch is an error handling flow control structure. Flow is determined      |
| based on the appearance or non-appearance of execution errors.                 |
| The try statement (or block of statements) is executed. If no error occurs, it |
| completes, and the catch statement is never executed.                          |
| If an error is detected within execution of the try statement, the try         |
| statement is aborted at the point of error (i.e. all statements following the  |
| point of error are discarded), and the catch statement (or block of            |
| statements) is executed.                                                       |
...
+--------------------------------------------------------------------------------+

Tests

With over 350 tests and counting, common_schema and QueryScript are well tested. There are still tests to write, the cover is not complete, and I’m working on it.

Bugfixes

Changed view definitions affected by MySQL bug #65388.

Download

Download common_schema. You will find it is rich and smart.