Category: MySQL

MySQL related posts

State of InnDB Online DDL in MySQL 5.6.9-RC (good news included)

MySQL

5.6.9-RC is out, and I was curious to see how the online DDL has improved since my 5.6.8 review. I also owe James Day this review, since he came up with results inconsistent with my own.

We both agreed the dataset I was using was too small, but I got similar results even on larger scale. Then some time passed, and 5.6.9 was announced.

So for the 5.6.9 test I took one of my real tables on production. It is not extremely large: it’s a ~ 300MB .ibd file, in the following format:

mysql> show create table tbl \G

CREATE TABLE `tbl` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `a` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `w` smallint(11) NOT NULL DEFAULT '0',
  `d` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `icount` smallint(5) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`) KEY_BLOCK_SIZE=8,
  UNIQUE KEY `u_idx` (`a`,`w`,`d`) KEY_BLOCK_SIZE=8,
  KEY `d` (`d`) KEY_BLOCK_SIZE=8
) ENGINE=InnoDB AUTO_INCREMENT=16960441 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16

Got some 2.5M rows in the table; desktop machine, 64 bit Linux, mysqlsandbox.

I have crossed several DDL statements with several DML statements. The DDL statements in this test are (ALTER TABLE…): Continue reading » “State of InnDB Online DDL in MySQL 5.6.9-RC (good news included)”

Notes on “5 Tips for migrating your MySQL server to a Galera Cluster”

MySQL

This post comes as response to 5 Tips for migrating your MySQL server to a Galera Cluster by Henrik Ingo (Galera), which is a good read. This post is a bit lengthy which is one of the reasons why I didn’t just comment on said post.

The Galera talk in London was indeed well crowded, judging from my own chair. I think there is an obvious interest in Galera, as well as a general thirst for information from real-life experience. I am personally very interested in production stories; either “war stories” or “peace stories” – anything to shed more light on the adoption of this technology.

For disclosure, I have not tried Galera myself as yet. Not on production nor on staging machines. But here’s a couple thoughts on Henrik’s post: Continue reading » “Notes on “5 Tips for migrating your MySQL server to a Galera Cluster””

common_schema over traditional scripts

MySQL

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 of command line options,
  • Is limited by whatever command line options are provided.
  • Has to invoke that script (duh!) to get the work done.

This last bullet is not so trivial: it means you can’t work some operation with your favorite GUI client, because it has no notion of your Perl script; does not run on the same machine where your Python code resides; simply can’t run those scripts for you.

With server-side code, functionality is accessible via any client. You run your operation via a query (e.g. CALL some_procedure). That can be done from your GUI client, your command line client, your event scheduler, your cronjob, all equally. You only need access to your MySQL server, which is trivial.

Of course, server side scripting is limited. Some stuff simply can’t be written solely on server side. If you want to consult your replicating slave; gracefully take action on user’s Ctrl+C, send data over the web, you’ll have to do it with an external tool. There are actually a lot of surprising limitations to things one would assume are possible on server side. You may already know how frustrated I am by the fact one can hardly get info from SHOW commands.

But, when it works, it shines

Let’s review a couple examples. The first one is nearly trivial. The second less so. Continue reading » “common_schema over traditional scripts”

Percona Live 2013: initial list of sessions

MySQL

Committee‘s reviewing process for session proposals for Percona Live 2013 conference is now complete. It is now time for scheduling, checking, double checking, organizing, categorizing and publishing the session schedule. This will take some time. However, we’ve put together a list of 10 sessions already approved.

Now, just building a list of 10 sessions is quite the task: the sessions must be rated high, present a broad range of topics, target audience, speakers… This list is not necessarily “the top 10 sessions you will ever see in the conference”, but rather “a list of 10 great sessions you might want to see in the conference”.

After all, any single attendee cannot see all possible sessions; but we sure want the attendee to have a great selection of talks!

So, without further ado, here are 10 approved sessions for the Percona Live MySQL Conference & Expo 2013.

Killing InnoDB idle transactions

MySQL

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 can:

SELECT sql_kill_query FROM innodb_transactions WHERE trx_idle_seconds >= 10; 
+-------------------+
| sql_kill_query    |
+-------------------+
| KILL QUERY 292509 |
| KILL QUERY 292475 |
+-------------------+

common_schema‘s useful eval() routine allows us to actually invoke those KILL statements, all in a one-liner:

call eval('SELECT sql_kill_query FROM innodb_transactions WHERE trx_idle_seconds >= 10');

Technical details Continue reading » “Killing InnoDB idle transactions”

Speaking in London: common_schema, DBA’s framework for MySQL

MySQL

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, column, function, parameter and script

What I will do:

  • Explain the nature of common_schema, show you the concepts behind it (and it is built with concepts in mind)
  • Provide with selected examples (schema analysis, security, processes & transactions) — there’s much to go by, but no boring drill down into all details.
  • Expose some cool hacks with QueryScript
  • Do very brief (as time allows) live demos.

common_schema is an open source project and released under the BSD license.

Last April Roland Bouman gave a great talk about common_schema. This will not be the same talk; a lot has been developed by this time.

I’ll be speaking at a very challenging time slot; the three other talks running concurrent to my own are all great ones; I actually wanted to attend all three of them. Heck, Peter wants to (though contrary to belief he can’t fork() himself). So, no hard feelings if you choose another talk during this time slot. But I do know where you live.

State of InnDB Online DDL in MySQL 5.6.8-RC

MySQL

5.6.8-rc is out, and so I’m following up on InnoDB’s online DDL new feature: the ability to SELECT, INSERT, DELETE, UPDATE a table even while an ALTER TABLE is executing on same table.

The brief summary

Not as advertised; many things can’t be done.

The longer review

I’m using 5.6.8-rc 64bit binary distribution for Linux, installed via mysqlsandbox. My hardware is irrelevant, but the fact I’m testing on my laptop assists me in that ALTER TABLE operations take a while, so that I’m able to easily type commands in two terminals and have the time to watch them being executed. Query cache is disabled. Continue reading » “State of InnDB Online DDL in MySQL 5.6.8-RC”

Percona Live 2013: Tutorials schedule released!

MySQL

I’m pleased to announce the availability of the Tutorials Schedule for Percona Live MySQL Conference and Expo, 2013.

This schedule comes as the outcome of hard work put by the reviewing committee members. Thanks! This work is much appreciated!

We got a good number of submissions. We’ve also reached out to prominent speakers and companies we felt would give great tutorials. All were reviewed, rated, discussed, compared; re-reviewed; some required further feedback from speakers.

Thank you to all speakers for submitting their proposals! I’m very sorry if your tutorial didn’t make it through — we have good tutorials left out — there’s only so much time in one day!

What can you expect to see in the tutorials?

The single day tutorials should appeal to beginners, seasonal DBAs, developers and database experts alike. Talks target different levels, in 6 different tracks.

I think the king & queen of the 2013 conference will be High Availability and the Cloud. This follows the trend of maturing HA solutions for MySQL and the emergence of MySQL cloud offerings or opportunities.

  • We will have MySQL on Amazon, NDB Cluster, Tungsten Replicator, XtraDB & Galera tutorials, as well as a HA evaluation tutorial. Pick your HA solution: chances are there’s a good tutorial on the subject. If not – don’t worry, lot’s of HA sessions expected as well.
  • Not everything is HA. You will get the chance to learn about InnoDB architecture and tuning, Xtrabackup old and new features, and a full day operational DBA.
  • Down to basics, learn about how to Build your indexes, get to know your Sphinx search capabilities, or learn about TokuDB implementation.
  • Or you could learn about Debugging and crash testing, or Advanced query optimizer tuning.

The tutorials day is in particular important to settle as early as possible; it has a separate pricing model than the rest of the conference, and offers more in-depth drill downs into the particular technologies. So we’re happy to make it through, and encourage you to register. Thank you!

MySQL 5.6 RC: further thoughts and questions

MySQL

Here are a few questions I came up with while experimenting with MySQL 5.6.7 & 5.6.8. They are the impressions of a first-time encounter with 5.6, which is a single opportunity for a person to point out the things that strike as odd.

Bugs-wise, just submitted another crashing bug for 5.6.8. I’m just one man, so I extrapolate to realize there is still much work to be done.

The below list does not necessarily make for a bug list; mostly things that puzzle me. I hope it can stir some additional thinking.

  1. Transportable tablespace: what’s the difference between FLUSH TABLES my_table WITH READ LOCK and FLUSH TABLES my_table FOR EXPORT? Both create the .cfg file, and both seem to operate just as well. One document says READ LOCK, another says FOR EXPORT.
  2. What’s the ALGORITHM=? flag in online ALTER TABLE? Apparently one can write to altered table even on ALGORITHM=COPY. There’s not enough documentation to explain.
  3. How come there’s not a single example of online InnoDB DDL in official docs?
  4. Why the inconsistency of putting ALGORITHM=…, LOCK=… in between commas, as opposed to other flags/commands not between commas? For example: ALTER TABLE my_table ADD COLUMN i INT, ALGORITHM=COPY, LOCK=SHARED, ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
  5. Why would anyone care about FULLTEXT search word proximity by bytes? Typically, one would want proximity by words. I can find the excuse for proximity by characters. By bytes? A user is not interested in the low level representation of the text!
  6. Could we get a distinct tablespace for the mysql internal InnoDB tables? (I understand there’s a separate tablespace for UNDO logs)
  7. Why the need to configure gtid_mode=ON as well as disable-gtid-unsafe-statements so as to enable GTID replication? If only the first is set, an error is produced upon CHANGE MASTER TO MASTER_AUTO_POSITION=1
  8. And when said error is produced, why does it not mention disable-gtid-unsafe-statements, and instead read out a cryptic message? Also note this post by Giuseppe Maxia.