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 …

The new stable version of innotop is now released. Version 1.8.1 is a bug-fix-only release, with no new features. It’s available for immediate download.

Further Reading:

• Version 1.5.2 of the innotop MySQL monitor released
• Version 0.1.149 of innotop released
• Version 1.6.0 of the innotop monitor for MySQL released
• …

This is a cross-post from the MySQL Performance Blog. I thought it would be interesting to users of PostgreSQL, Redis, Memcached, and $system-of-interest as well.

For about the past year I’ve been formulating a series of tools and practices that can provide deep insight into system performance simply by looking at TCP packet headers, and when they arrive and depart from a system. This works for MySQL as well as a lot of other types of systems, because it doesn’t require any of the contents of the packet. Thus, it works without knowledge of what the server and client are conversing about. Packet headers contain only information that’s usually regarded as non-sensitive (IP address, port, TCP flags, etc), so it’s also very easy to get access to this data even in highly secure environments.

I’ve …

“Legacy MySQL does not scale well on a single node, which forces granular sharding and explicit application code changes to make them sharding-aware and results in low utilization of severs”– Dr. John Busch, Schooner Information Technology A super-set of MySQL suitable for Big Data? On this subject, I have interviewed Dr. John Busch, Founder, Chairman, [...]