One of the things I think we did right with Maatkit (and now with Percona Toolkit) is making the documentation part of the code itself. So much redundancy and wrong documentation has been eliminated by making the tool actually read its own documentation when it starts up. As an example, the default value of the –shorten option is defined in the documentation (it’s Perldoc) like this:

=item --shorten

type: int; default: 1024

Not only is the documentation part of the code, but the tool’s –help output is generated from it too. The existence, type, defaults, and even the behavior of the command-line options is defined in the documentation. If I execute the tool with the –help option, you can see that default value:

[baron@ginger bin]$ ./pt-query-digest --help | grep  -- --shorten
  --shorten=i                    Shorten long statements in reports (default
  --shorten                      1024 …

Next Tuesday I’ll be speaking at the DevOpsDC meetup at CustomInk’s offices. I’ll talk about why MySQL downtime happens and what you can do to avoid or prevent it. This is a research-based talk (but it’s not scientific… they’re different) that draws on hundreds of downtime issues I’ve studied. The related white papers are available on Percona’s website. Hope to see you there!

Further Reading:

• Speaking at MySQL Meetup in Northern Virginia
• …

This is all too simple. Here's how you can create an easy jOOQ / Google Cloud SQL integration example: Sign up with Google App Engine Sign up with Google Cloud SQL Create a Google App Engine project (preferably with Eclipse) Add jOOQ to your project Add your generated schema to your project Done Google Cloud … Continue reading jOOQ and Google Cloud SQL Example →

Blackhole tables are often used on a so-called “relay slave” where some operation needs to happen but no data needs to exist. This used to have a bug that prevented AUTO_INCREMENT columns from propagating the right values through replication, but that was fixed. It turns out there’s another bug, though, that has the same effect. This one is caused when there is an INSERT into a Blackhole table, where the source data is SELECT-ed from another Blackhole table.

I think it’s wise to keep it simple. MySQL has tons of cool little features that theoretically suit edge-case uses and make ninja tricks possible, but I really trust the core plain-Jane functionality so much more than these edge-case features. That’s precisely because they often have some edge-case bugs, especially with replication.

For me, next week’s Percona Live is a lot like other events: I know I will be busy meeting and greeting and helping unpack boxes and so forth, but I’ll also be talking and attending other talks. With five concurrent tracks, it’s tough to decide. This is roughly the equivalent of the MySQL conference every year, which has more tracks, but historically it’s been easy to cross off some talks as sales pitches. Not so with this event! Here’s what I want to see the most:

• Clustrix’s keynote. I’ve watched the progress of our tests with Clustrix very closely. If you’re coming to the conference, don’t oversleep.
• Henrik’s …

As I'm mostly using Oracle for work projects, the concept of the DUAL dummy table has become quite intuitive. I hardly ever think about the days when I was playing around with that table to find out its purpose (e.g. writing into it when DUAL was still a physical object, and thus killing the whole … Continue reading SQL Trouble with dummy tables →

I’ll be presenting a free one-hour webinar on preventing downtime in production MySQL servers, in conjunction with the ODTUG. It is scheduled on Thursday, November 10, 2011 3:00 PM – 4:00 PM EST, and you can register for free.

Here’s an abstract of what you’ll learn:

Everyone wants to prevent database downtime by being proactive, but how effective are the common measures such as inspecting logs and analyzing SQL? To be truly proactive, one must prevent problems, which requires studying and understanding the reasons for downtime. We have analyzed a selection of emergency issues that we have solved, to better understand what types of problems really occur in production environments. The results are somewhat surprising, and will be detailed in this talk. Most incidents we found were not MySQL-specific and will be familiar to Oracle DBAs as well as …

A few people have asked me how it’s going, so I thought I’d just share it with everyone. Things are going great. I’m writing much more quickly than I thought I would be, and as a result I’m finding I have time to do more changes than I thought I could, which makes me happy. I should be finished drafting the chapters by the end of the year.

In particular, the faster than expected pace is giving me a chance to address one of the big weaknesses of the second edition. In many places, the second edition is a collection of facts and experiences. It says what and why, but it doesn’t convey a process or method, and it doesn’t teach you how to think about things and apply the results to situations beyond what the book covers. I’m finding a number of key areas to remedy that: performance optimization, profiling, indexing, high availability, scalability, and so on. I’m adding an explanation of the principles and consequences, and …

I had some conversations with a few people at Oracle Open World about features that would be beneficial in InnoDB. They encouraged me to blog my thoughts, so I am.

Someday I’d like to have a clear mental list of features I want in MySQL in general, but that is a much harder list to organize and prioritize. InnoDB is an easier place to get started.

First, I’d like truly online, nonblocking DDL. I see two ways this could work: reading the old version of rows and writing the new version, or doing a shadow-copy and building the new table in the background. The first way has the advantage of being lazy, so the schema change is instantaneous, and you really never pay any additional cost. However, it has the disadvantage that it might be hard to implement multiple schema changes if a previous change isn’t fully finished, so to speak (I can see a lot of bugs if there are more than 2 versions of the schema at a time). This is a …

I was looking into the history about why SYSDATE() and NOW() behave differently in MySQL, and it looks like in 4.0 and 4.1 they used to be the same. But as of MySQL 5.0, SYSDATE() was changed to emulate Oracle’s behavior, that is, it returns the time as of the function execution, not as of the statement start.

There are a number of bug reports related to this: 15101, 12480, 12481, and 12562.

I am not an Oracle expert. Does NOW() return a constant result within an Oracle query, like NOW() in MySQL does? Or is there no NOW() in Oracle, and you use SYSDATE() instead? Why is Oracle’s SYSDATE() functionality worth emulating? It looks to me like some …