The latest from the MySQL community

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, [...]

The Northern California Oracle Users Group (NoCOUG) just published an interview with me in their journal. It’s titled “A Whole New World of MySQL”. You can read it in the full journal (PDF), or on Iggy Fernandez’s blog. The full journal is well worth a read; there’s a lot of good information.

Thanks to Iggy for such a thought-provoking interview, and for inviting me to participate!

Further Reading:

• Wishing I could be at ODTUG
• …

Want a free copy of High Performance MySQL, Third Edition? If you register before the early-bird pricing expires for the MySQL Conference in April, and use the discount code PL-Book, you’ll get to take a free copy home from the conference!

And now, a status update: I’m currently proofing the QC2 (quality control #2) revision of the book; after this, the book goes to manufacturing. The PDF is now 820 pages, which is a lot of work to proofread. You can imagine how much more in-depth we’ve been able to go with so many more pages.

The free sample online is …

70x Higher JOIN Performance, NoSQL Key-Value API & Cross Data Center Sharding with Synchronous Replication 

Oracle is delighted to announce the immediate availability of the production-ready, GA release of MySQL Cluster 7.2, available for download under the GPL, and as part of the commercial MySQL Cluster Carrier Grade Edition, including management tools, product certifications and 24x7 global support.

1 Billion Queries per Minute

MySQL Cluster delivered 1 billion queries per minute (17.6m million queries per second), scaled-out across 8x commodity Intel x86 server nodes, accessed by the NoSQL C++ NDB API.

It …