Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 30 of 52 Next 22 Older Entries

Displaying posts with tag: common_schema (reset)

Speaking at Percona Live: common_schema, MySQL DevOps
+0 Vote Up -0Vote Down

In less than a month I'll be giving these two talks at Percona Live:

If you are still unfamiliar with common_schema, this will make for a good introduction. I'll give you multiple reasons why you would want to use it, and how it would come to immediate use at your company. I do mean immediate, as in previous common_schema presentations I happened to get feedback emails from attendees within the same or next day letting me know how common_schema solved an insistent problem of theirs or how it exposed an unknown status.

I'll review

  [Read more...]
Why delegating code to MySQL Stored Routines is poor engineering practice
+2 Vote Up -0Vote Down

I happen to use stored routines with MySQL. In fact, my open source project common_schema heavily utilizes them. DBA-wise, I think they provide with a lot of power (alas, the ANSI:SQL 2003 syntax feels more like COBOL than a sane programming language, which is why I use QueryScript instead).

However I wish to discuss the use of stored routines as integral part of your application code, which I discourage.

The common discussion on whether to user or not use stored routines typically revolves around data transfer (with stored routines you transfer less data since it's being processed on server side), security (with stored routines you can obfuscate/hide internal datasets, and

  [Read more...]
common_schema: speaking at Percona Live London, Nov. 2013
+0 Vote Up -0Vote Down

In one week's time I'll be presenting common_schema: DBA's framework for MySQL at Percona Live, London.

This talk introduces the rich toolset known as common_schema. It is free, open source, extremely useful in solving DBA & developer tasks, and is the next best thing ever invented next to SQL pie charts.

I'll introduce:

  • Views, with which you can detect and terminate idle transactions, blocking transactions; roll your range partitions; find duplicate keys; block/unblock accounts; get fine grained privileges per account; find AUTO_ICNREMENT free space; ...
  • Routines: do meta executions such as eval(); get query checksums; duplicating accounts; killing suspicious connections;
  [Read more...]
Converting an OLAP database to TokuDB, part 3: operational stuff
+1 Vote Up -0Vote Down

This is the third post in a series of posts describing our experience in migrating a large DWH server to TokuDB (see 1st and 2nd parts). This post discusses operations; namely ALTER TABLE operations in TokuDB. We ran into quite a few use cases by this time that we can shed light on.

Quick recap: we've altered one of out DWH slaves to TokuDB, with the goal of migrating most of out servers, including the master, to TokuDB.

Adding an index

Shortly after migrating our server to TokuDB we noticed an unreasonably disproportionate slave lag on our TokuDB slave (red line in chart below) as compared to other slaves.

  [Read more...]
Converting an OLAP database to TokuDB, part 2: the process of migration
+2 Vote Up -0Vote Down

This is a second in a series of posts describing our experience in migrating a large DWH server to TokuDB. This post discusses the process of migration itself.

As a quick recap (read part 1 here), we have a 2TB compressed InnoDB (4TB uncompressed) based DWH server. Space is running low, and we're looking at TokuDB for answers. Early experiments show that TokuDB's compression could make a good impact on disk space usage. I'm still not discussing performance -- keeping this till later post.

Those with weak hearts can skip right to the end, where we finally have a complete conversion. You can also peek at the very end to find out how much 4TB uncompressed InnoDB data is worth in TokuDB. But

  [Read more...]
Converting an OLAP database to TokuDB, part 1
+0 Vote Up -0Vote Down

This is the first in a series of posts describing my impressions of converting a large OLAP server to TokuDB. There's a lot to tell, and the experiment is not yet complete, so this is an ongoing blogging. In this post I will describe the case at hand and out initial reasons for looking at TokuDB.

Disclosure: I have no personal interests and no company interests; we did get friendly, useful and free advice from Tokutek engineers. TokuDB is open source and free to use, though commercial license is also available.

The case at hand

We have a large and fast growing DWH MySQL setup. This data warehouse is but one component in a larger data setup, which includes Hadoop, Cassandra and more. For online dashboards and most reports, MySQL is our service. We populate this warehouse mainly via Hive/Hadoop. Thus, we have an hourly load of data from Hive, as

  [Read more...]
common_schema 2.2: better QueryScript isolation & cleanup; TokuDB; table_rotate, split params
+2 Vote Up -0Vote Down

common_schema 2.2 is released. This is shortly after the 2.1 release; it was only meant as bug fixes release but some interesting things came up, leading to new functionality.

Highlights of the 2.2 release:

  • Better QueryScript isolation & cleanup: isolation improved across replication topology, cleanup done even on error
  • Added TokuDB related views
  • split with "index" hint (Ike, this is for you)
  [Read more...]
common_schema roadmap thoughts
+0 Vote Up -0Vote Down

I'm happy with common_schema; it is in fact a tool I use myself on an almost daily basis. I'm also happy to see that it gains traction; which is why I'm exposing a little bit of my thoughts on general future development. I'd love to get feedback.

Supported versions

At this moment, common_schema supports MySQL >= 5.1, all variants. This includes 5.5, 5.6, MySQL, Percona Server & MariaDB.

5.1 is today past end of line, and I'm really missing the SIGNAL/RESIGNAL syntax that I would like to use; I can do in the meanwhile with version-specific code such as /*!50500 ... */. Nevertheless, I'm wondering whether I will eventually have to:

  • Support
  [Read more...]
common_schema 2.1 released: advanced & improved split(), persistent script tables, more schema analysis, and (ahem) charts!
+2 Vote Up -0Vote Down

common_schema 2.1 is released! common_schema is your free & open source companion schema within your MySQL server, providing with a function library, scripting capabilities, powerful routines and ready-to-apply information and recommendations.

New and noteworthy in version 2.1:

  • Better QueryScript's split() functionality
  • Persistent tables for QueryScript: no long held temporary tables
  • Index creation analysis, further range partition analysis
  [Read more...]
common_schema & openark-kit in the media: #DBHangOps, OurSQL
+0 Vote Up -0Vote Down


I had the pleasure of joining into @DBHangOps today, and speak about common_schema and openark-kit. What was meant to be a 15 minute session turned to be 50 -- sorry, people, I don't talk as much at home, but when it comes to my pet projects...

I also realized I was missing on a great event: DBHangOps is a hangout where you can chat and discuss MySQL & related technologies with friends and colleagues, with whom you typically only meet at conferences. I will certainly want to attend future events.

Thanks to John Cesario and Geoffrey Anderson who invited me to talk, and to the friends and familiar faces who attended; I was happy to talk about my work, and very interested in

  [Read more...]
Easy SELECT COUNT(*) with split()
+0 Vote Up -0Vote Down

The two conservative ways of getting the number of rows in an InnoDB table are:

  • SELECT COUNT(*) FROM my_table:
    provides with an accurate number, but makes for a long running transaction which take ages on large tables. Long transactions make for locks
    Gives immediate response, but the value can be way off; it can be two times as large as real value, or half the value. For query execution plans this may be a "good enough" estimation, but typically you just can't trust it for your own purposes.

Get a good estimate using chunks

You can get a good estimate by calculating the total number of rows in steps. Walk the table 1,000 rows

  [Read more...]
Slides from my talk: common_schema, DBA's Framework for MySQL
+1 Vote Up -0Vote Down

I've just uploaded the slides from my talk: common_schema: DBA's framework for MySQL

My talk was well attended, and I was fortunate to have a warm and engaged audience. Thank you to all those who attended, and thank you for those who provided feedback! Was happy to be able to present my work a great group of people.

You can find my slide either on Percona Live's website, or on Slideshare, as well as embedded right here.

I set two aims to my talk:

  • To have the audience know how to download and install
  •   [Read more...]
    Speaking at Percona Live 2013: common_schema, lightning talks
    +3 Vote Up -0Vote Down

    In two weeks time I will be giving these talks at Percona Live:

    • common_schema: DBA's framework for MySQL: an introduction to common_schema, my evolving server side solutions project. This will be a revised version of the talk I gave at Percona Live London; I have felt some weaknesses during that talk, which I've thrown out, letting room for cool stuff. I will discuss common_schema's various views, interesting and useful routines, the power of QueryScript, and a brief intro to the newcomer
      [Read more...]
    Taking common_schema's rdebug to a test-drive
    +1 Vote Up -0Vote Down

    This is a simple step-by-step introduction to rdebug: Debugger and Debugging API for MySQL Stored Routines, as part of common_schema.

    In other words: let me take you through the steps for debugging your stored routines on your own server. We will step into, step over, step out, modify variables, set a breakpoint, run to breakpoint...

    Command line geeks, this one's for you. GUI lovers, this is actually an API; I am hoping for someone wrap it up with a plugin for your favorite GUI editor.


      [Read more...]
    common_schema 2.0.0-alpha: rdebug, GPL
    +1 Vote Up -0Vote Down

    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.


    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:

        [Read more...]
      MySQL Stored Routines Debugger & Debugging API: sneak preview II, video
      +0 Vote Up -0Vote Down

      This is the 2nd sneak preview of common_schema's rdebug: debugger & debugging API for MySQL stored routines (see 1st preview here).

      rdebug will be released as part of common_schema, free and open sourced.

      In this sneak preview I present:

      • Compiling multiple routines with debug info
      • Starting/stopping a debug session
      • Step-over, step-in, step-out
      • Showing stack trace
      • Showing the next-statement to execute
      • Viewing and manipulating local routine variables
      • Misc. meta routines

      The quick technical overview

        [Read more...]
      MySQL Stored Routines Debugger & Debugging API: sneak preview video
      +2 Vote Up -0Vote Down

      This is a sneak peek video introduction/preview of an in-development free and open source server side debugger & debugging API for MySQL stored routines.

      MySQL does not provide server side debugging capabilities for stored routines. Some tools exist, including MySQL's own, that assist in stored routine debugging. These are all GUI based and, to the best of my knowledge, MS Windows based. There is one solution in alpha stage that is developed for Java/eclipse; I did not look at the code. See discussion here and here.

      An ideal solution would be to have debugging API in the server itself - independently of your client, programming language or operating system. To the

        [Read more...]
      MySQL security tasks easily solved with common_schema
      +1 Vote Up -0Vote Down

      Here are three security tasks I handled, which I'm happy to say were easily solved with common_schema's views and routines (with no prior planning). Two are so easy, that I actually now integrated them into common_schema 1.3:

      • Duplicate a user (create new user with same privileges as another's)
      • Find users with identical set of grants (same roles)
      • Finding redundant users (users who only have privileges on non-existing objects); I was approached on this by Sheeri K. Cabral from Mozilla.

      Duplicate user

      How would you duplicate a grantee? That's easy! Just get the SHOW GRANTS output, then do text search and replace: replace the existing account (e.g. 'existing'@'localhost') with the new account (e.g.

        [Read more...]
      common_schema: 1.3: security goodies, parameterized split(), json-to-xml, query checksum
      +1 Vote Up -0Vote Down

      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
        [Read more...]
      common_schema over traditional scripts
      +1 Vote Up -0Vote Down

      If you are familiar with both openark kit and common_schema, you'll notice I've incorporated some functionality already working in openark kit into common_schema, essentially rewriting what used to be a Python script into SQL/QueryScript.

      What was my reasoning for rewriting good code? I wish to explain that, and provide with a couple examples.

      I'm generally interested in pushing as much functionality into the MySQL server. When using an external script, one:

      • Needs the right dependencies (OS, Perl/Python version, Perl/Python modules).
      • Needs to provide with connection params,
      • Needs to get acquainted with a lot
        [Read more...]
      Slides from my talk: common_schema, DBA's framework for MySQL
      +0 Vote Up -0Vote Down

      Thank you to all who attended my talk at Percona Live London!

      The slides from my talk are now made available, following: Slides: common_schema, DBA's framework for MySQL. Also available on slideshare.

      They should also be on the Percona Live site within a few days.

      Killing InnoDB idle transactions
      +0 Vote Up -0Vote Down

      The issue of terminating long-time idle open InnoDB transaction has been discussed recently by many. I wish to add my share, by proposing a quick and clean solution via common_schema.

      common_schema 1.2 provides with the innodb_transactions view, which relies on INNODB_TRX - one of the InnoDB Plugin views in INFORMATION_SCHEMA - as well as on PROCESSLIST, and so is able to determine with certainty that a transaction has been idle for a long time.

      innodb_transactions offers us with a sql_kill_query column, which produces a 'KILL QUERY 12345' type of value. So we

        [Read more...]
      Speaking in London: common_schema, DBA's framework for MySQL
      +1 Vote Up -0Vote Down

      I'm happy to be speaking about common_schema at Percona Live London, Dec 4th, 2012. This will be the first chance for me to speak about common_schema, what I believe to be an extremely useful companion to your MySQL server.

      I will show you how common_schema can leverage your SQL skills and give you new powers. Stuff you had to be SQL-Perl-super-expert to work through is easily attainable with common_schema and QueryScript. Some maintenance, security and auditing tasks are now a breeze. Writing server side scripts can be fun!

      What I won't do:

      • Read the fancy manual aloud
      • Walk you through every possible view,
        [Read more...]
      Purging old rows with QueryScript: three use cases
      +0 Vote Up -0Vote Down

      Problem: you need to purge old rows from a table. This may be your weekly/monthly cleanup task. The table is large, the amount of rows to be deleted is large, and doing so in one big DELETE is too heavy.

      You can use oak-chunk-update or pt-archiver to accomplish the task. You can also use server side scripting with QueryScript, offering a very simple syntax with no external scripting, dependencies and command line options.

      I wish to present three cases of row deletion, with three different solutions. In all cases we assume some TIMESTAMP column

        [Read more...]
      common_schema 1.2: security, partition management, processes, QueryScript goodies
      +1 Vote Up -0Vote Down

      common_schema 1.2 is released! This version comes shortly after 1.1, yet contains quite a few interesting goodies:

      • Account blocking
      • Security audit
      • RANGE partition management
      • Slave status
      • Better blocking and idle transaction management
      • QueryScript goodies:
        • echo, report
        • while-otherwise statement; foreach-otherwise statement
        • Better variable scope handling
        • Complete support for variable expansion
        • Transaction support within QueryScript
      • More summary info and SQL statements in processlist-related views

      A closer look at these follows:

      Account blocking

      A new view called

        [Read more...]
      Your magical RANGE partitioning maintenance query
      +0 Vote Up -0Vote Down

      If you use RANGE (or RANGE COLUMNS) partitioning, and in particular when partitioning by date/time, then your are subject to the following questions: how and when do you create the "next" partition? How and when do you drop your older partitions?

      Many people use in-house scripting to do that, and Giuseppe Maxia wrote Partition Helper. But I would like to take you one step forward, and provide with a query (based on views) which automagically understands which new partition you want to create, and provides you with the statement to do so. It looks somewhat like this (a demo follows later on):

      mysql> SELECT * FROM sql_range_partitions \G
      *************************** 1. row
        [Read more...]
      How common_schema installs itself
      +1 Vote Up -0Vote Down

      Up till common_schema version 1.1, the user would need to choose from distinct distribution files: an install compatible with MySQL 5.1, one compatible with InnoDB Plugin enabled servers, and one compatible with Percona Server. The difference between the three is the availability of certain INFORMATION_SCHEMA tables.

      With 1.1, this is no longer the case: common_schema auto-detects the server and available feature set, and installs accordingly.

      Wait, isn't common_schema just a SQL file?

      Yes. It's not like there's an installer like InstallShield or anything. Nevertheless, common_schema offers a smart way of conditional handling, which is uses in itself. It's called

        [Read more...]
      How common_schema split()s tables - internals
      +1 Vote Up -0Vote Down

      This post exposes some of the internals, and the SQL behind QueryScript's split. common_schema/QueryScript 1.1 introduces the split statement, which auto-breaks a "large" query (one which operates on large tables as a whole or without keys) into smaller queries, and executes them in sequence.

      This makes for easier transactions, less locks held, potentially (depending on the user) more idle time released back to the database. split has similar concepts to oak-chunk-update and

        [Read more...]
      Table split(...) for the masses
      +1 Vote Up -0Vote Down

      (pun intended)

      common_schema's new split statement (see release announcement) auto-splits complex queries over large tables into smaller ones: instead of issuing one huge query, split breaks one's query into smaller queries, each working on a different set of rows (a chunk).

      Thus, it is possible to avoid holding locks for long times, allowing for smaller transactions. It also makes for breathing space for the RDBMS, at times boosting operation speed, and at times prolonging operation speed at will.

      In this post I show how split exposes itself to the user, should the user wish so.

      split can

        [Read more...]
      common_schema 1.1 released: split(), try-catch, killall(), profiling
      +2 Vote Up -0Vote Down

      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
        [Read more...]
      Showing entries 1 to 30 of 52 Next 22 Older Entries

      Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

      Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.