I frequently encounter MySQL servers with intermittent problems that don’t happen when I’m watching the server. Gathering good diagnostic data when the problem happens is a must. Aspersa includes two utilities to make this easier.

The first is called ’stalk’. It would be called ‘watch’ but that’s already a name of a standard Unix utility. It simply watches for a condition to happen and fires off the second utility.

This second utility does most of the work. It is called ‘collect’ and by default, it gathers stats on a number of things for 30 seconds. It names these statistics according to the time it was started, and places them into a directory for analysis.

Here’s a sample of how to use the tools. In summary: get them and make them executable, then configure them; then start a screen session and run the ’stalk’ utility as root. Go do …

Ronald asked me if I could present at ODTUG’s Kaleidoscope conference, which is only a couple hours from me, but I’ll be at the Netways OSDC that week. Matt Yonkovit will be there representing Percona. I wish I could go: I would really like to mingle with more Oracle users and developers, and I think that participation is the key to building relationships between MySQL and Oracle users — two groups of people who are going to be overlapping more in the future. If you can attend, I hope you will — and blog about it so I can read.

Related posts:

1. OpenSQL Camp events in 2009 There are
2. …

The May release of Maatkit included a new feature in mk-query-digest. This allows you to process queries in many pieces, write out intermediate results, and then combine the pieces in a separate step. Maybe it’s not exactly map-reduce, but it makes a good headline.

The purpose is to enable query analysis across an arbitrarily large group of servers. Process queries on all of them, ship the results to a central place, and then combine them together. Pre-processing the results has some nice benefits, such as reduced bandwidth requirements, speeding up processing by doing it in parallel, and reducing the workload on the central aggregator. One Percona customer with many MySQL instances is trying this out.

The --save-results option on mk-query-digest saves the digested results to a file, stopping just before the final stages of the query event pipeline. There is a tool in Subversion trunk, tentatively called …

Forecasting Oracle Performance

Forecasting Oracle Performance. By Craig Shallahamer, Apress 2007. Page count: about 250 pages. (Here’s a link to the publisher’s site). Short version: buy it and read it, but make sure you don’t rely on it alone; deepen your knowledge through other sources.

I bought and read this book because I’m interested in performance, performance forecasting, and capacity planning. I’m not interested in forecasting Oracle performance per se. However, I have noticed that there is a lot of good literature in the Oracle arena that can apply to other databases (*cough* …

A now-famous quote that I probably don’t need to attribute: “First they ignore you, then they laugh at you, then they fight you, then you win.”

Where is Drizzle in this lifecycle? I’ve been hearing and reading some comments to the tune of “those Drizzle guys think it’s easy to rip MySQL stuff out and start over, wait till they see how hard it’s going to get when the real world sinks in.” Maybe, maybe. But maybe not, too. Maybe not.

I’ve seen more than one software project that was belittled as “never gonna amount to anything, save your time” and went on to do quite well. Never underestimate the power of a handful of passionate and talented people. I personally feel that Drizzle has a bright future.

Related posts:

1. Please re-license the MySQL documentation In the pas …

A lot of systems are relatively easy to make HA (highly available). You just slap them into a well-known HA framework such as Linux-HA and you’re done. But databases are different, especially replicated databases, especially replicated MySQL.

The reason has to do with some properties that hold for many systems, but not for most databases. Most systems that you want to make HA are relatively lightweight and interchangeable, with little to zero statefulness, easy to start, easy to stop, don’t care a lot about storage (or at least don’t write a lot of data; that’s usually delegated to the database), and there’s little or no harm done if you ruthlessly behead them. The classic example is a web server or even most application servers. Most of the time these things are all about CPU power and network bandwidth. If I were to compare them to a car, I’d say they are like matchbox cars: there are many of them, and they are cheap …

Sheeri has posted an awesome list of talks with links to videos and slides from the MySQL conference. She did a lot of the video recording herself. I don’t know how she could have posted them so fast — it’s a lot of work.

Related posts:

1. Slides from my session on mk-query-digest at PgEast 2010 After two
2. I’ll be speaking at the O’Reilly MySQL Conference 2010 I’m
3. Submit your proposals for MySQL …

Ok. So it all begins with somebody who is using INSERT ON DUPLICATE KEY UPDATE. That guy wants to count the number of UPDATE actions that statement has taken, as opposed to INSERT actions.

We could have been using mysql_info() to fetch that information. But instead we rig the UPDATE clause: CODE:root@localhost [kris]> create table t ( 
  id integer unsigned not null primary key, 
  d integer unsigned not null 
) engine = innodb;
Query OK, 0 rows affected (0.16 sec)

root@localhost [kris]> insert into t values ( 1, 1), (2,2), (3,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
…

Much has been made of certain limitations of MySQL’s query optimizer (“planner”). Subqueries, lack of sort-merge joins, and so on. This is not MySQL-bashing and no one should be offended. Some people have worked to make things better, and that code is in branches that were intended for future releases. But if that work were available right now, would you trust it?

This question is important because the optimizer is complex and full of compromises and black magic. Even minor changes occasionally have weird edge cases that cause a regression in some workload. Are major changes trustworthy?

I’ll give a specific example. In version 5.0, MySQL gained the ability to use more than one index for a query. This is called index_merge in EXPLAIN, and sometimes people think it’s the best thing ever. In practice, I can say two general things about queries that use an index_merge plan:

1. If the optimizer chooses an …

The video of my 5-minute Ignite talk on XtraDB is up on YouTube. Ignite talks have exactly 20 slides on auto-advance every 15 seconds. “Enlighten us, and make it fast!” It’s better than a lightning talk. I explained the difference between Percona XtraDB and other technologies such as standard MySQL, OurDelta, and MariaDB.

Related posts:

1. Xtrabackup is for InnoDB tables too, not just XtraDB Just thoug
2. Recap of CPOSC 2009, plus slides Yesterday
3. Learn about mk-query-digest …