I presented at this year’s Hotsos Symposium. I am searching for a claim to specialness, and I think it may be that I am the first Hotsos presenter who’s specifically focused on MySQL. True? I don’t know, but I’ll run with it for now.

My topic was on extracting black-box performance metrics from TCP packet headers and timestamps and finding hidden performance problems in the system, without any knowledge of what the client and server are talking to each other about. I then extended the same data to performance and scalability modeling, which you can use for purposes such as forecasting, capacity planning, and bottleneck analysis.

This technique works on MySQL because its TCP protocol is half-duplex, and it’ll work for any system with a half-duplex protocol. Does it work on Oracle Database? I am not sure, and no one else I’ve spoken to yet has been …

Thinking about a new career? Percona is hiring for a variety of positions. Sales, server engineering, training, consulting, support, and occasionally other things too.

It’s a great place to work. I’m totally biased, but it’s still a great place to work.

Further Reading:

• Want to hack Maatkit and Aspersa? We’re hiring
• Percona wants to hire a Maatkit developer
• I have joined Percona
• …

Someone asked me yesterday why there are no TPC benchmarks for MySQL. By “benchmarks” I don’t mean “benchmark tools” but rather “official benchmark results.” For example, see the current TPC-C benchmarks. Any true TPC benchmark has to be certified by the TPC and is an arduous and expensive process. Is it time for MySQL to play in this theater? Or does it simply not matter?

Further Reading:

• Benchmarks for DATE operations in MySQL
• JavaScript date formatting benchmarks
• …

I’m at Hotsos Symposium this week, and it suddenly occurred to me that a lot of Oracle DBAs who are beginning to manage MySQL servers might have some things to share with others in a similar role shift:

• Familiar, comfortable tools and techniques, or capabilities of the Oracle Database, that you miss in MySQL
• Equivalents or replacements for the aforementioned

Do you have anything to share with your fellow DBAs going through ODT (Oracle Delerium Tremens)? Do you have any wishes that you haven’t satisfied yet? Post in the comments and let’s see if we can create a sort of forum for sharing and/or a wishlist in case someone gets an urge to fill in a missing piece!

Further Reading:

• Making …

At ideeli, there is an asynchronous process that allows internal users to import SKUs into our MySQL database. As ideeli has grown, more people are doing more of the same thing at the same time and the SKU import process had become a pain point. At its core, it simply inserts records into a handful of tables from an uploaded file within a transaction. Subsequently, a cleanup process deletes some data from the same tables. Each import ordinarily takes a few minutes, but when multiple SKU imports are running simultaneously, everything grinds to a near-halt. If an internal user didn’t complain first, Technical Operations would get paged for multiple long running MySQL transactions in the several hour(!!) range. We would kill off the longest running SKU import and the other imports would eventually complete as the locks it held were released. This annoyed people at many levels of the business, but it was a reasonably manageable problem…until it …

The first step in creating an effective import plan for comma-separated value (CSV) files is recognizing your options in a database. There are several options in an Oracle database. You can read the file with Java, C/C++, C#, PL/SQL (through the UTL_FILE package), PHP, Perl, or any other C-callable programming language; or you can use SQL*Loader as a standalone utility or through externally managed tables (known as external tables). The most convenient and non-programming solution is using external tables.

Adopting external tables as your import solution should drive you to consider how to manage the security surrounding this type of methodology. Host hardening is a critical security step because it shuts down most, hopefully all, unauthorized use of the operating system where the database and external files reside. Next, you need to manage the access to the external tables and ensure that exposure of business sensitive information …

When people ask me about high availability, I often suggest that it’s helpful to understand whether you’re most interested in service availability, data availability, or both. Of course, you want both — but if cost is an object, you may end up relaxing your requirements.

The typical example of an application that needs service availability but doesn’t have strong data availability requirements is something that’s ad-supported. Who cares if the last few comments on funny cat photos are lost? What’s important is that the users can see the photos, and the ads next to them.

On the other hand, if you’re selling something, it’s a big deal to lose records about orders or payments. In this case, if you can’t have both kinds of availability, you might prefer downtime over data loss.

Here’s a quick comparison of some MySQL high availability technologies. All opinions are mine alone:

If you haven’t booked your hotel room yet, you should do it ASAP. The hotel is going to raise their rates at the same time the early-bird conference rate expires. This is a coincidence: Percona has no control over hotel prices. (There is a group discount rate, but we don’t call the shots on that.)

A good rule of thumb is that if you’re thinking about traveling somewhere, you should book your hotel immediately, and cancel if you change your mind. You can save a lot of money on booking the hotel early, and hotels allow cancellations without a charge. So there is no downside and a lot of potential upside! Sometimes I book several hotels and then cancel all but one of them.

With regard to this event, I have always found that staying in the hotel (as opposed to a nearby hotel) increases the value a lot. Traveling …

There are four SQL transaction isolation levels supported by InnoDB: REPEATABLE READ, READ COMMITTED, READ UNCOMMITTED, and SERIALIZABLE. Because READ UNCOMMITTED and SERIALIZABLE are rarely used, I am going to outline the distinction between READ COMMITTED and REPEATABLE READ. Perhaps I will follow up with SERIALIZABLE and READ UNCOMMITTED if there is interest.

REPEATABLE READ:
The state of the database is maintained from the start of the transaction. If you retrieve a value in session1, then update that value in session2, retrieving it again in session1 will return the same results. Reads are repeatable. Repeatable Read.

session1> BEGIN;
session1> SELECT firstname FROM names WHERE id = 7;
Aaron

session2> BEGIN;
session2> SELECT firstname FROM names WHERE id = 7;
Aaron
session2> UPDATE …

MySQL 5.6 is going to have a lot of improvements. I want to mention a few things I think are uniquely valuable for a large set of typical MySQL users.

The first is index condition pushdown. If you’ve read High Performance MySQL, you saw a lengthy “indexing case study” in the chapter on indexing. It’s many pages of if-this-then-that kinds of things: you have to include this column in the index, but then in that order, but only for this query because for this other query you need these other indexes with columns in these orders, and this index can be suitable for these queries if you combine some other things, and it just goes on and on. Why? Largely because of something that we call the left-prefix rule. It imposes a lot of …