Skip to content

# Month: October 2012

## Further experiments with MySQL 5.6.7-RC: submit your bugs

MySQL New Features Opinions October 22, 2012

Here’s the background: I’m testing many features of MySQL 5.6.7-RC due to two reasons:

- I’m verifying my common_schema installs and works properly on 5.6
- I promised I would present a 45 minute “what’s new in MySQL 5.6” seminar in the upcoming OracleWeek (Israel)

In the case of common_schema, I have managed to find one weird bug (a behavior regression from 5.5) and one server-crashing bug, by merely running the project’s tests, known to pass on 5.1 and 5.5 (and not utilizing any 5.6 features).

In the case of my presentation, I’m getting acquainted with new syntax and functionality, and am getting unexpected results. I’ve hit replication issues and locking issues in online DDL.

If I am able to find these bugs within a few hours of experimenting & testing, then it is safe to assume we can extrapolate to many more bugs. This is not surprising; 5.6 boasts some 120 new features (I didn’t actually count them). Of course they would introduce new bugs.

The moral is this: if you’re interested in 5.6 as I am – and there’s a lot to wait for – please consider experimenting with it, and report as many bugs to bugs.mysql.com as possible. Reporting bugs is the user’s authoritative way of improving an open source product and pushing towards a stable release. Provide your feedback today!

## InnoDB DDL: kudos to quick responders on bugs.mysql.com

MySQL InnoDB New Features October 18, 2012

Continuing my experiments with 5.6 InnoDB online DDL, a bug which I’ve opened, and another which I commented on were quickly answered and explained by the Oracle/MySQL team.

On both accounts I’m happy to acknowledge the issue is resolved; in both cases I failed to produce a real bug scenario. Good lesson. Kudos for quick and informative responses!

What’s left of my experiment, then? Still a lot to check.

I am mainly still confused with which operations exactly can use LOCK=NONE (allowing for updated to table while ALTERing). So far I am only able to produce ALTERs with LOCK=SHARED, meaning table is readable, but cannot be updated.

I will want to test speeds. I’ve so far been content with slow response times for queries over altered tables. How well will that endure under heavy load?

## Experimenting with 5.6 InnoDB Online DDL (bugs included)

MySQL InnoDB New Features openark kit Opinions scripts October 18, 2012October 18, 2012

MySQL 5.6 offers the groundbreaking online DDL operations for InnoDB. Most common use cases will enjoy this feature, and the need for online alter table scripts will decrease. This is a killer feature!

I’ve put this new feature to the usability test. How did it go? Not too well, I’m afraid.

[Updates to this text inline], also see this followup.

#### sakila & DDL

sakila is still a very useful database. I say “still” because it is not very large, and computing power is getting stronger; yet on my laptop some operations can still take many seconds to complete, which is just fine for my tests.

Sakila tables are mostly InnoDB, and rental being the largest, I do:

>     node1 (sakila) > alter table sakila.rental engine=InnoDB;
>     Query OK, 16044 rows affected (6.94 sec)
>     Records: 16044  Duplicates: 0  Warnings: 0

So what can be executed during these 6.94 seconds? In a second terminal, I try the following: Continue reading » “Experimenting with 5.6 InnoDB Online DDL (bugs included)”

## Percona Live MySQL Conference and Expo 2013, Call for Papers

MySQL conference PerconaLive October 16, 2012

I’m honored to accept the role of conference chairman in the upcoming Percona Live MySQL Conference and Expo, April 22th-25th, 2013, Santa Clara, CA. Here are a few administrative announcements:

#### Call for Papers

Call for papers is extended until Oct 31st. We’ve already received a good number of submissions, but wish to provide with another chance for latecomers. Now is a good time to submit!

The committee will begin the long reviewing process shortly.

#### Committee

Members of the reviewing committee are:

- Roland Bouman (Pentaho)
- Ronald Bradford (Continuent)
- Laine Campbell (PalominoDB)
- Colin Charles (MontyProgram)
- Jeremey Cole (Twitter)
- Patrick Galbraith (HP)
- Henrik Ingo (Nokia)
- Jay Janssen (Percona)
- Giuseppe Maxia (Continuent)
- Domas Mituzas (Facebook)
- Ivan Zoratti (SkySQL)

Continue reading » “Percona Live MySQL Conference and Expo 2013, Call for Papers”

## Thoughts on MySQL 5.6 new replication features

MySQL Configuration InnoDB New Features Opinions Replication October 15, 2012

After playing a little bit with MySQL 5.6 (RC), and following closely on Giuseppe’s MySQL 5.6 replication gotchas (and bugs), I was having some thoughts.

These are shared for a few reasons:

- Maybe I didn’t understand it well, and someone could correct me
- Or I understood it well, and my input could be of service to the developers
- Or it could be of service to the users

#### InnoDB tables in mysql schema

The introduction of InnoDB tables in mysql makes for crash-safe replication information: the exact replication position (master log file+pos, relay log file+pos etc.) is updated on InnoDB tables; with innodb_flush_logs_at_trx_commit=1 this means replication status is durable and consistent with server data. This is great news!

However, the introduction of InnoDB tables to the mysql schema also breaks some common usage on installation and setup of MySQL servers. You can’t just drop your ib_data1 file upon dump+restore, since it also contains internal data. Giuseppe outlines the workaround for that.

I was thinking: would it be possible to have a completely different tablespace for MySQL’s internal InnoDB tables? That could be a single tablespace file (who cares about file-per-table on a few internal tables). And I’m throwing an idea without being intimate with the internals: you know how it is possible to span the shared tablespace across multiple files, as in: Continue reading » “Thoughts on MySQL 5.6 new replication features”

## Your magical RANGE partitioning maintenance query

MySQL common_schema partitioning October 9, 2012

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 ***************************
>                 table_schema: test
>                   table_name: city
>     sql_drop_first_partition: alter table `test`.`city` drop partition `p3`
>       sql_add_next_partition: alter table `test`.`city` add partition (partition `p_20160101000000` values less than (736329) /* 2016-01-01 00:00:00 */ )
>     *************************** 2. row ***************************
>                 table_schema: test
>                   table_name: quarterly_report_status
>     sql_drop_first_partition: alter table `test`.`quarterly_report_status` drop partition `p3`
>       sql_add_next_partition: alter table `test`.`quarterly_report_status` reorganize partition `p_maxvalue` into (partition `p_20110401000000` values less than (1301608800) /* 2011-04-01 00:00:00 */ , partition p_maxvalue values less than MAXVALUE)

#### A closer look at why this is magic

This query just gave you the DROP PARTITION and ADD PARTITION for all tables in your databases that use a RANGE partitioning scheme. But, consider: Continue reading » “Your magical RANGE partitioning maintenance query”

## MySQL 5.6 new features: the user’s perspective

MySQL InnoDB memcached New Features Opinions Replication October 5, 2012

This is a yet-another compilation of the new MySQL 5.6 feature set. It is not a complete drill down. This list reflects what I believe to be the interesting new features user and usability -wise.

For example, I won’t be listing InnoDB’s split of kernel mutex. I’m assuming it can have a great impact on overall performance due to reducing lock contention; but usability-wise, this is very internal.

The complication is an aggregate of the many announcements and other complications published earlier on. See a reference at the end of this post.

Do note I am not using 5.6 as yet; it is in RC, not GA. I am mostly excited just to write down this list.

#### InnoDB

- Online ALTER TABLE: if there is one major new feature in 5.6 you would want to upgrade for, this would be it. Add columns, drop columns, rename columns, add indexes, drop indexes – now online, while your SELECT, INSERT, UPDATE and DELETE statements are running.
- Transportable tablespace files: copy+paste your_table.ibd files with FLUSH TABLE FOR EXPORT and ALTER TABLE … IMPORT TABLESPACE.
- FULLTEXT: for many, the one thing holding them back from leaving MyISAM behind. Now available in InnoDB with same syntax as with MyISAM.
- Memcached API: access InnoDB data via memcahced protocol, and skip the SQL interface.
- User defined table location: place your tables in your pre-defined location. Place other tables elsewhere. This is something I’ve been asked about for ages.

#### Continue reading » “MySQL 5.6 new features: the user’s perspective”