The latest from the MySQL community

As I’ve become a better shell programmer over the last year or two, I’ve been surprised to discover some tools I didn’t know about. It eventually dawned on me, as I did more and more brute-force processing of large datasets, as well as some of the more delicate things that went into Aspersa -> Percona Toolkit, that many tasks I used to do with SQL and spreadsheets can be accomplished easily with well-structured text files and Unix utilities. And they don’t require loading data into a database or spreadsheet (the latter of which almost always performs terribly).

To give an idea, here are some of the relational operations (in SQL speak) you can perform:

1. SELECT col1, col2… can be implemented with several variants of Unix utilities: cut and awk are the two most obvious. I tend to use awk only when needed, or when it’s more convenient to combine …

Mark Leith writes that it’s time to say goodbye to SHOW PROCESSLIST in MySQL 5.6, and use the Performance Schema replacement for it instead, because the older tools cause some blocking, and the Performance Schema replacement is completely non-blocking.

On the face of it that’s a good thing, but I wonder whether we’ll want to keep some blocking functionality around anyway. Inspecting systems that are doing concurrent work can be hard unless you can see a variety of views on them. One such is looking at the state of all the concurrent work at an instant in time. This is sometimes indispensable for troubleshooting: you will see causes and effects you’ll never see in counters and metrics, no matter how many you capture or how sophisticated the analysis.

I haven’t seen MySQL 5.6 in production usage …

If you haven’t submitted your proposals for April’s Percona Live MySQL conference (the big annual one in Santa Clara California), now’s the time: the CFP closes in a couple of days!

Further Reading:

• One-day Percona Live conference in San Francisco
• Speaking at Percona Live in London this October
• Percona Live from a community insider’s perspective
• …

I was looking through James Day’s post on upcoming changes to MySQL configuration defaults in version 5.6, and one caught my eye in particular: innodb_autoextend_increment changes from 8 to 64MB, and now affects *.ibd files.

I don’t see any further documentation on this yet; I assume that’s in the works. I’m curious how this will actually behave. What will be the initial size of an empty InnoDB table using innodb_file_per_table? There might be some unintended consequences. Here’s a couple I can think of:

1. Create a table and it’s 64MB from the start. This would cause some people to be unable to use InnoDB. In fact, as it currently stands, an empty table is 16kb, and I’ve heard some people say that’s a problem for them because they create many small tables and the 16kb …

Given that most people don’t know what the cloud is, I guess we shouldn’t be surprised that most people can’t articulate the benefits of cloud computing clearly. I commonly hear “Scalability!” “Easy provisioning!” “Flexibility!” and so on. Of course, all of these are benefits you can get without the cloud. Theo says it pretty well, so I won’t belabor that point.

What do you think are the benefits of cloud computing?

In the end I can think of only one really unique benefit, and it’s indirect. It is this: the rise of cloud computing is creating a social phenomenon among engineers. The benefit isn’t the technology, or the economics, or any of that stuff. It’s the change …

I’ve just finished presenting my work on adaptive fault detection at Surge and Percona Live NYC. My slides are available on Slideshare, and embedded below.

Adaptive Fault Detection from xaprb

Further Reading:

• Products that scale linearly to hundreds of servers
• …

MySQL 5.6 will have “checksums in the binary log,” which can be variously described, but one phrase I’ve heard a few times is, loosely, that it helps ensure replication integrity. This isn’t specific enough to make it clear what it does, and when I’ve talked about pt-table-checksum and its purpose (for example, on webinars), people often ask whether pt-table-checksum will be obsoleted by replication checksums in MySQL 5.6. The answer is no, they do completely different things. But it’s kind of confusing, a bit like semi-synchronous replication in that regard.

pt-table-checksum ensures that your replicas have the same logical dataset as their masters. They can drift for any number …

Have you seen Damn Cool Algorithms: Cardinality Estimation yet? If not, take a few minutes and read through it. Now, what if we try using that approach instead of COUNT(DISTINCT) in MySQL to see how many distinct values there are in a column?

I recently needed this information in real life, and the table is large with many duplicate values. The column is some 32-character hex string, a hash value that represents a session ID. I’ll call the column sess_id. I wanted to know how many distinct values it had, but I thought it would be cool (damn cool, really) to try this approach and see what happened.

I read the blog post, convinced myself that it made sense, and tried to code it. Here’s my rough translation of the algorithm into MySQL-speak. Note that I’m using crc32(), which may not be a great choice …

High Performance MySQL has a long discussion on “sharding,” examining many options and their benefits and drawbacks. What does sharding look like in the real world?

Years ago I helped shard a MySQL-based application, partitioning its data across multiple database servers. It was already pretty large and significantly complex, so as usual for applications that aren’t designed with sharding in mind from day one, a major consideration for sharding was to make the migration strategy workable and minimize the disruption to the application code. It’s never easy to build this in after the fact, but there is a clear line between approaches that keep the business running and those that don’t.

We chose to shard by client. Each of the major tables had a client column already, and clients could be grouped onto servers without much effort. In addition, we maintained a one-to-one mapping of application servers with the database servers, …

After Gavin Towey’s recent blog post about Percona Server’s buffer pool dump locking the server for the duration of the operation, I thought I should re-examine MySQL 5.6′s implementation of a similar feature. When InnoDB engineers first announced the feature, I didn’t think it was complete enough to serve a DBA’s needs fully.

If you’re not familiar with this topic, MySQL 5.6 will allow the DBA to save the IDs of the database pages that are in the buffer pool, and reload the pages later. This technique can help a server to warm up in minutes instead of hours after a restart or failover.

I read through the documentation, and it looks good. I still think it might be good to have a built-in …