The latest from the MySQL community

I was trying to parse the data from the common platform enumeration which is a big dictionary of all or most of commercially or freely available applications, operating systems and hardware (the hardware part I didn't really understand).

Here is an example for "Microsoft Access 2000 sp2"
cpe:/a:microsoft:access:2000:sp2

As you can see, the data is separated by colons.
To get the parts that I want in that string, I used the following SQL statement:

*edit from Scot's comment*
mysql> set @v="cpe:/a:microsoft:access:2000:sp2";
Query OK, 0 rows affected (0.00 sec)mysql> set @p=2 /*the part I want */;
Query OK, 0 rows affected (0.00 sec)mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@v,':',@p),':',-1);
+---------------------------------------------------------------------+
| SELECT …

We’re running Birds of a Feather (BoF) sessions and lightning talks this year at the MySQL conference, similarly to previous years. You can help by submitting proposals ASAP. The deadline is Monday, and it is a hard deadline.

Submit a BoF Session • Submit a Lightning Talk

Why is this a big deal? Why the hard deadline? It turns out that this is one of the things I always underestimated. Planning for these is actually quite difficult and they are very expensive. That’s because we have to ensure that there is room for them to take place, doors are unlocked, and the lights are turned on. This really surprised me — I always thought it was simple to let people gather together in the evening. Well, nothing is simple at …

The third edition is now available in ePub, Mobi, and PDF formats. Print format should be available early next week.

Further Reading:

• High Performance MySQL, Third Edition
• High Performance MySQL, Second Edition: Replication, Scaling and High Availability
• High Performance MySQL 2nd Edition gets revised and translated
• …

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: