Tag: Development

opeark-kit revision 196 released

MySQL

This is a long due maintenance release of openark-kit. This release includes bugfixes and some enhancements, mainly to oak-online-alter-table.

oak-online-alter-table Changes / bug fixes include:

  • Support for keyword-named columns
  • Use of FORCE INDEX due to lack of MySQL’s ability for figure out the chunking key at all times
  • –sleep-ratio option added; allows for sleep time proportional to execution time (as opposed to constant sleep time with –sleep)
  • Support for chunk-retry (in case of deadlock) via –max-lock-retries)
  • Fixed order of cleanup
  • Fixed bug with verbose messages with non-integer chunking key
  • Fixed bug with single-row tables (people, no need for this tool for single row tables :))
  • Friendly verbose messages to remind you what’s being executed

oak-chunk-update changes includes:

  • Verbosing query comment if exists (friendly printing of what’s being executed)

Continue reading » “opeark-kit revision 196 released”

common_schema 2.0.0-alpha: rdebug, GPL

MySQL

A new release for common_schema: an alpha version of rdebug: MySQL Debugger and Debugging API is now included with common_schema.

With a different license in mind for rdebug, common_schema changes license to GPL (2 or above).

common_schema 2.0 is ready for download. All things rdebug, it is alpha — otherwise it’s a stable release.

rdebug

I’m very happy to release this alpha version of rdebug, and urge everyone to try it out.

The idea is to have an open, free, server side debugger and debugging API for MySQL stored routines. To elaborate:

  • It’s server side by that it’s implemented by stored routines. Not by a connector; not an emulator; not a GUI tool hack. The entire functionality lies within common_schema, a schema in your server.
  • It’s a debugger: you can debug your own stored routines (with limitations)
  • It’s a debugging API: there’s a distinct specification and a set of calls which makes for a debugging process
  • It’s open since the source code is yours to browse.
  • It’s free as in free beer.
  • It’s free as it makes you independent of a specific debugger. It provides an API that anyone can use. You can run the API yourself from the command line; or plugins for your favorite GUI editor can be developed to use this API.

On a separate blog post I will take you to a rdebug test drive.

As always, nothing is released before extensive documentation is in place.

I’d love to get input on this. Continue reading » “common_schema 2.0.0-alpha: rdebug, GPL”

common_schema: 1.3: security goodies, parameterized split(), json-to-xml, query checksum

MySQL

common_schema 1.3 is released and is available for download. New and noteworthy in this version:

  • Parameterized split(): take further control over huge transactions by breaking them down into smaller chunks, now manually tunable if needed
  • duplicate_grantee(): copy+paste existing accounts along with their full set of privileges
  • similar_grants: find which accounts share the exact same set of privileges (i.e. have the same role)
  • json_to_xml(): translate any valid JSON object into its equivalent XML form
  • extract_json_value(): use XPath notation to extract info from JSON data, just as you would from XML
  • query_checksum(): given a query, calculate a checksum on the result set
  • random_hash(): get a 40 hexadecimal digits random hash, using a reasonably large changing input

Let’s take a closer look at the above:

Parameterized split()

split takes your bulk query and automagically breaks it down into smaller pieces. So instead of one huge UPDATE or DELETE or INSERT..SELECT transaction, you get many smaller transactions, each with smaller impact on I/O, locks, CPU.

As of 1.3, split() gets more exposed: you can have some control on its execution, and you also get a lot of very interesting info during operation.

Here’s an example of split() control:

set @script := "
  split({start:7015, step:2000} : UPDATE sakila.rental SET return_date = return_date + INTERVAL 1 DAY) 
    throttle 1;
";
call common_schema.run(@script);

In the above we choose a split size of 2,000 rows at a time; but we also choose to only start with 7015, skipping all rows prior to that value. Just what is that value? It depends on the splitting key (and see next example for just that); but in this table we can safely assume this is the rental_id PRIMARY KEY of the table.

You don’t have to use these control parameters. But they can save you some time and effort. Continue reading » “common_schema: 1.3: security goodies, parameterized split(), json-to-xml, query checksum”

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

MySQL

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: Continue reading » “common_schema 1.1 released: split(), try-catch, killall(), profiling”

sphinx, sphinx_snippets() & MySQL 5.5

MySQL

I’ve written a patch which completes Sphinx’s integration with MySQL 5.5.

Up until a couple months ago, Sphinx would not compile with MySQL 5.5 at all. This is, thankfully, resolved as of Sphinx 2.0.3.

However, to my disdain, I’ve found out that it only partially work: the sphinx_snippets() user defined function is not included within the plugin library. After some quick poking I discovered that it was not added to the build, and when added, would not compile.

I rely on sphinx_snippets() quite a lot, and like it. Eventually I wrote the fix to the snippets_udf.cc which allows it to run in a MySQL 5.5 server.

Here are the changes for the 2.0.4 version of Sphinx:

Replace your 2.0.4 files with these two and get on compiling your MySQL server.

Compilation guide

For completeness, here’s how to compile Percona Server 5.5 with Sphinx 2.0.4 including the above patches: Continue reading » “sphinx, sphinx_snippets() & MySQL 5.5”

common_schema rev. 218: QueryScript, throttling, processes, documentation

MySQL

common_schema, revision 218 is released, with major new features, top one being server side scripting. Here are the highlights:

  • QueryScript: server side scripting is now supported by common_schema, which acts as an interpreter for QueryScript code.
  • Throttling for queries is now made available via the throttle() function.
  • Enhancements to processlist-related views, including the new slave_hosts view.
  • Inline documentation/help is available via the help() routine.
  • more…

QueryScript

common_schema makes for a QueryScript implementation for MySQL. You can run server side scripts, interpreted by common_schema, which allow for easy syntax and greater power than was otherwise previously available on the MySQL server. For example:

foreach($table, $schema, $engine: table like '%')
  if ($engine = 'ndbcluster')
    ALTER ONLINE TABLE :$schema.:$table REORGANIZE PARTITION;

QueryScript includes flow control, conditional branching, variables & variable expansion, script throttling and more.

Read more on common_schema’s QueryScript implementation.

Continue reading » “common_schema rev. 218: QueryScript, throttling, processes, documentation”

QueryScript: SQL scripting language

MySQL

Introducing QueryScript: a programming language aimed for SQL scripting, seamlessly combining scripting power such as flow control & variables with standard SQL statements or RDBMS-specific commands.

QueryScript is available fro MySQL via common_schema, which adds MySQL-specific usage.

What does QueryScript look like? Here are a few code samples:

Turn a bulk DELETE operation into smaller tasks. Throttle in between.

while (DELETE FROM archive.events WHERE ts < CURDATE() LIMIT 1000)
{
  throttle 2;
}

Convert all InnoDB tables in the ‘sakila’ database to compressed format:

foreach ($table, $schema, $engine: table in sakila)
{
  if ($engine = 'InnoDB')
    ALTER TABLE :$schema.:$table ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
}

Shard your data across multiple schemata:

foreach($shard: {USA, GBR, JAP, FRA})
{
  CREATE DATABASE db_:$shard;
  CREATE TABLE db_:$shard.city LIKE world.City;
  INSERT INTO db_:$shard.city SELECT * FROM world.City WHERE CountryCode = $shard;
}

Continue reading » “QueryScript: SQL scripting language”

Oracle “Technologist of the Year: Developer” Award

MySQL

I am honored to receive Oracle’s Technologist of the Year: Developer award, formerly Oracle Magazine Editors’ Choice Awards.

Technologist of the Year Award is given for individuals for their technical achievements with regard to Oracle products.

As opposed to community based awards, to win this award one must be nominated by himself or his company. There are several award categories: Developer, DBA, IT Manager etc., and many nominations per category. I have been nominated by my company and am happy to have won the award in the Developer category.

Allow me to take the pleasant opportunity to make some acknowledgements. Continue reading » “Oracle “Technologist of the Year: Developer” Award”

common_schema, rev. 178: foreach(), repeat_exec(), Roland Bouman, query analysis

MySQL

common_schema, revision 178 is now released, with major additions. This revision turns common_schema into a framework, rather than a set of views and functions.

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.

There’s no Perl nor Python, and no dependencies to install. It’s just a schema.

Some highlights for the new revision:

  • foreach(), aka $(): loop through a collection, execute callback commands per element.
  • repeat_exec(): a repeat-until device: execute queries until some condition holds.
  • exec_file(): execute files a-la SOURCE, but on server side
  • Query analysis: analyze query text, view or routine definitions to detect dependency objects.
  • Improvements to views and routines, new routines introduced.

Let’s take a closer look:

rpbouman

I’m very happy to have Roland Bouman working on this project. He introduced some sophisticated code without which some functionality could not take place. I’m sure I don’t need to introduce his great capabilities; I’ll just pass the note that it is very good working with him!

foreach()

Introducing a looping device which can iterate a collection and execute callback commands.

What’s a collection? A range of numbers; a set of constants; the result set of a SELECT query; tables in your database and more.

What is a callback? A query or set of queries to invoke on the specific elements in the collection. For example:

call foreach('table in sakila', 'ALTER TABLE ${schema}.${table} ENGINE=InnoDB ROW_FORMAT=COMPRESSED');

I’ll publish dedicated posts on foreach(), aka $(), following this post. Official documentation is here.

repeat_exec()

Repeat executing queries in a given interval, until some condition holds.

What kind of condition? You can loop forever, or until a given time has passed, a given number of iteration has passed. Continue reading » “common_schema, rev. 178: foreach(), repeat_exec(), Roland Bouman, query analysis”

Test-driven SQL development

Development MySQL

I’m having a lot of fun writing common_schema, an SQL project which includes views, tables and stored routines.

As the project grows (and it’s taking some interesting directions, in my opinion) more dependencies are being introduced, and a change to one routine or view may affect many others. This is why I’ve turned the development on common_schema to be test driven.

Now, just how do you test drive an SQL project?

Well, much like the way you test any other project in your favorite programming language. If its functions you’re testing, that’s all too familiar: functions get some input and provide some output. Hmmm, they might be changing SQL data during that time. With procedures it’s slightly more complex, since they do not directly return output but result sets.

Here’s the testing scheme I use: Continue reading » “Test-driven SQL development”