The recently released MySQL 5.6 gets a lot of attention, but for those who are still on 5.5 there is also good news: There are two new features in 5.5.

The first feature is that there are more INFORMATION_SCHEMA tables for InnoDB. This means that it's possible to 'see' what's in the buffer pool. It also makes it possible to get more information about the LRU list.

From the 5.5.28 changelog:
InnoDB: Certain information_schema tables originally introduced in MySQL 5.6 are now also available in MySQL 5.5 and MySQL 5.1: INNODB_BUFFER_PAGE, INNODB_BUFFER_PAGE_LRU, and INNODB_BUFFER_POOL_STATS. (Bug #13113026)

This is in the "Bugs Fixed" section instead of the "Functionality Added or Changed" section, which is a bit weird in my opinion.

The second feature is a








  [Read more...]

Tungsten Replicator 2.0.7 was released today. In addition to a large number of bug fixes, this release adds several improvements for multi-master management, and support for Amazon RDS (as a slave).

While the Release Notes show a long list of improvements, I would like to focus on some of them that improve the handling of multi-master deployments.

When we released version 2.0.6, we added the first revision of the cookbook recipes in the build. That was still a green addition, which caused several bug reports. But since then, we have integrated the cookbook in our internal testing, making these recipes more robust and reliable. We are also planning to improve

  [Read more...]

The latest releases of MySQL Sandbox, in addition to deal with minor bugs, have mostly been necessary because of compatibility issues in MySQL, both 5.5 and 5.6.

When I found that MySQL 5.6 has some InnoDB tables inside the 'mysql' schema, I had to change the way that the sandbox used to remove all contents (the ./clear command.)

To achieve a smooth clean up, MySQL Sandbox now performs a dump of the mysql schema, and uses that saved data to restore the schema after a complete wipeout.

Unfortunately, when 5.5.30 was released, this operation resulted in a warning, due to a behavioral change.

After a careful change, and about 1200 unit tests, the latest version of MySQL Sandbox should work well with every MySQL release from 5.0 to 5.6.

The blog title says it all: Do we need a MySQL Cookbook? I tend to think so.

This seems to be something that is missing with current MySQL documentation. There is lots of information available but finding the appropriate bit can be quite tedious and it often requires looking in multiple places.

A lot of other software has such books, but for some reason MySQL seems to be missing one.

A recent example comes from a “documentation feature request” I posted today: http://bugs.mysql.com/bug.php?id=68171. MySQL 5.6 provides a way to “move InnoDB tables” from one server to another. There are many reasons why you may want to do it, but the documentation is currently rather sparse. A simple “example recipe” for this would be good, as would an equivalent recipe for other

  [Read more...]

The Percona Live MySQL Conference and Expo 2012 is over. Together with the SkySQL solutions day, it has kept me occupied for 4 full days, from early morning to late at night.

I have to say that I am pleased. The quality of the organization was very high, with a very good lineup of speakers and an excellent technical support.

As usual, I have learned a lot during this week, either directly, by attending talks, or indirectly, by meeting people who told me what was juicy at the talks that I had missed. And I have met new interesting people, and caught up with the people that I know already.

This conference was particularly intense also because I got myself involved in 5 talks, which was probably more than I should have. How did

  [Read more...]

On March 21st I will be in Paris, to attend the OTN MySQL Developers Day. Oracle is organizing these events all over the world, and although the majority are in the US, some of them are touching the good old European continent. Previous events were an all-Oracle show. Recently, the MySQL Community team has been asking for cooperation from the community, and in such capacity I am also presenting at the event, on the topic of testing early releases of MySQL in a sandbox. Of course, this is one of my favorite topics, but it is quite appropriate in this period, when Oracle has released a whole lot of preview features in its MySQL Labs. Which is another favorite topic of mine, since I was the one who  [Read more...]

I see that MySQL 5.5.21 has just been released. This sounds interesting. I’m mainly running 5.5.16 which has been broadly stable, but I have been caught by a few important issues which 5.5.21 fixes according to the change list:

• Bug #57480
• Bug #61104
• Bug #54035

These together with a few earlier fixes after 5.5.16 are certainly interesting to me, so I’m eager to try out 5.5.21 and see how it fairs.

MySQL, like a lot of other software, has many knobs you can tweak. Most of these knobs may affect behaviour, but more importantly most affect the memory usage of the server, so getting these settings right is very important.

Most of MySQL’s memory is really used just as a cache, in one form or another, information that otherwise is on disk. So ensuring you have as large a cache as possible is important. However, making these memory sizes too large will trigger the server to start swapping and possibly can cause it to crash or cause the kernel to kill the process when it runs out of memory.  So that’s something we want to avoid.

Certain settings affect memory allocation on a per connection/thread basis, being bounded by thread_cache_size and max_connections.  If you configure for the worst behaviour (max_connections) you may end up not actually using all

  [Read more...]

For those who saw my previous post about the crashing (regression) bug with SELECT COUNT(DISTINCT) on InnoDB with Primary Key (PK), you’ll be interested to know my test case does not crash in 5.5.18 (which was just released).

I’ve only tested my test case thus far, but it seems fine.

Unfortunately, the fix is not mentioned in the 5.5.18 changelogs though.

And there is no mention (yet, anyway) of a fix in the bug report I filed (though it was designated a ‘duplicate’, so it wouldn’t necessarily be updated).

I’m trying to get confirmation from the MySQL Dev Team on this (via the bug report), and will update this post if/when I hear anything.

I’ll also perform some of

  [Read more...]

In 5.5, a crashing, regression bug exists if you use SELECT COUNT(DISTINCT) *and* one of the WHERE operands is in the Primary Key (or just a unique index).

This simple crash (if only one row is in the table) will crash mysqld.

Of course I’ve filed a bug report, but that has been nearly 3 months and no updates yet.

Here is the bug I filed (which you won’t be able to view):

http://bugs.mysql.com/bug.php?id=61842

Really, the only thing that happened to my bug report was that it was designated a duplicate of another bug (which we also cannot view):

http://bugs.mysql.com/bug.php?id=61101

Based on the id, and the submitted dates of bugs 61100 and 61102, this initial bug (61101) was filed on May 9, 2011. So, in fact, this bug has been present for over 5

  [Read more...]

I recently gave a presentation at the New York Effective MySQL Meetup on the new features of, and some of the compelling reasons to upgrade to MySQL 5.5. There are also a number of new MySQL variables that can have a dramatic effect on performance in a highly transactional environment, innodb_buffer_pool_instances and innodb_purge_threads are just two to consider.

For more information on all the new variables, status, reserved words and benchmarks of new features you can Download Presentation Slides.

Reading MySQL security: inconsistencies I remembered a few related experiments that I did several years ago when I was studying for the MySQL certification. The first fact that came to mind is about the clause "WITH GRANT OPTION", which can only be given on the full set of options, not on a single grant. For example

GRANT INSERT,DELETE,UPDATE on world.* to myuser identified by 'mypass';
GRANT SELECT on world.* to myuser identified by 'mypass' WITH GRANT OPTION;
show grants for myuser\G
*************************** 1. row ***************************
Grants for myuser@%: GRANT USAGE ON *.* TO 'myuser'@'%' IDENTIFIED BY PASSWORD '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4'
*************************** 2. row ***************************
Grants for myuser@%:

  [Read more...]

Some time ago I wrote about MySQL 5.5 semi-synchronous replication. Since then, I have wanted to benchmark the overhead of semi-synchronous replication with a decent server. Now the occasion presented itself, thanks to some related business that I had to benchmark, and thus I did a few simple runs with and without semi-synchronous replication enabled, to see the impact of this feature on performance. If you haven't read the article on semi-synchronous replication, the bottom line is that, with this feature enabled, the master waits until at least one slave has acknowledged receipt for the data before returning a positive result to the client. This means that for each commit there are two network calls between master and slave. My gut feeling was that this feature would be costly  [Read more...]

  I am excited to see that MySQL 5.5 GA (5.5.8) has been finally released . All of my ex-colleagues at MySQL/Oracle have done amazing jobs. I was also positively surprised that 5.5 was released on schedule:). So far I have felt that Oracle is leading MySQL development and product management very well.
  5.5 has lots of practical features not only for web services providers, but also for enterprise users. Especially I like the following features.

Improved concurrency

  Very often lots of concurrent sessions access to the same table. In such cases, one of MySQL internal global mutexes (LOCK_open) becomes very hot and serious concurrency problems have happened. Increasing table_cache have caused even negative impacts (because MySQL has to do linear search here. See bug#33948 for

  [Read more...]

The announcement of MySQL 5.5 released as GA has outlined the improvements in this version, which indeed has enough good new features to excite most any user.
There are two additions, though, that were lost in the noise of the bigger features, and I would like to spend a few words for each of them.The first addition is something that users of stored routines have been waiting for since MySQL 5.0. No, it is not SIGNAL and its close associate RESIGNAL, which have been publicized enough. I am talking about the stored routine parameters, for which now there is a dedicated table in the information_schema.
Let's see

  [Read more...]

I have been looking forward to the general availability (GA) release of MySQL 5.5 since is was publically announced in September that we would see this in 2010. While I already have a production client with 5.5.7rc, the badge of general availability is a great way to promote why environments should consider moving to using MySQL 5.5. Here is my quick short list of why I’d promote moving to MySQL 5.5.

1. Improved integration

The first significant improvement is that InnoDB is now again firmly a default included storage engine. The InnoDB plugin 1.1.x is now the builtin version of the engine, not a plugin version. Also the 1.1.x version has continued improvements over the 1.0.x version available as an included but not enabled plugin in current MySQL 5.1.x versions. Removing the complexity for end users over the choice of InnoDB and the necessary

  [Read more...]

An update of MySQL from 5.0 to 5.5 on CentOS 5.5 64bit has not resulted in a good experience. The mysqld process would then crash every few minutes with the following message.

101120 8:29:27 InnoDB: Operating system error number 22 in a file operation.
InnoDB: Error number 22 means ‘Invalid argument’.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/operating-system-error-codes.html
InnoDB: File name /tmpfs/#sql6cf3_5c_0.ibd
InnoDB: File operation call: ‘aio write’.
InnoDB: Cannot continue operation.

The work around was to change the tmpdir=/tmpfs (which was a 16G tmpfs filesystem) to a physical disk.

The referenced URL didn’t provide any more information of help. Unlike Bug #26662 O_DIRECT is not specified as the flush method.

Yesterday I was testing a branch of MySQL 5.5 to help a colleague, and I was set aback at discovering that, with the default build options, the server did not include the Archive engine.
In other times, I would have to dig into the build scripts or to examine the output of ./configure --help, but that is no longer necessary. MySQL 5.5 is built using cmake, the cross platform make.
Why does this change make me feel better? Because cmake configuration is more user friendly than the old autoconf/automake/libtools horror syntax. Not only that, but there is a GUI!
I am a command line guy, as you probably know, but when the purpose of a GUI


  [Read more...]

Half a day into my vacation, I managed to finish an article on a topic that has been intriguing me for a while.
Since several colleagues were baffled by the semantics of the new enhancements of MySQL 5.5 partitions, after talking at length with the creator and the author of the manual pages, I produced this article: A deep look at MySQL 5.5 partitioning enhancements.
Happy holidays!

UPDATE This matter was more tricky than it appeared at first sight. As Bug#49861 shows, several MySQL engineers were






  [Read more...]

Some time go, we announced a new release model for MySQL. As all new things, it had some initial hiccups (with MySQL 5.4 we were still getting acquainted with the new model), but now it seems to be in full swing.
By the time you read these lines, MySQL 5.5 will be available. If the mirrors aren't seeded yet, the impatient can compile and use the new version from the launchpad source tree..

Overview

What's this new release anyway? I'll leave it




  [Read more...]