The tools we’ve redesigned in Percona Toolkit recently have moved away from a legacy technique for operating on small numbers of rows at a time, towards a more reliable and predictable method. We call the old version “chunking” and the new version “nibbling.” Many other MySQL tools I’ve seen either operate on entire tables, or use the “chunking” technique and are exposed to the problems it creates. I’ll compare the two briefly to explain the differences.

Chunking attempts to divide a table into ranges of rows of a desired size, such as 1000 rows. It does this by examining the minimum and maximum value of the primary key (or other suitable index), estimating the number of rows in the table, and dividing one by the other to create a list of boundary values. Suppose that the minimum value is 1 and the maximum is 1000000, and there are an estimated 100000 rows in the table. The chunk boundaries will fall on intervals of 10000. …

Please join me tomorrow (Wednesday) for a webinar about the new version of pt-online-schema-change, Percona Toolkit’s tool for performing nonblocking ALTER TABLE operations. Registration is free. I will explain how the tool works and cover some typical use scenarios. This might be helpful if you’re trying to decide whether the tool is right for you. I am also planning to leave plenty of time for questions!

Further Reading:

• Free webinar on MySQL performance this Thursday
• …

I have previously posted about the SQL MERGE statement, and how powerful it is here: https://blog.jooq.org/arcane-magic-with-the-sql2003-merge-statement/ Unfortunately, not all databases support this statement. Also, very often it is quite a bit of overkill, when what you want to do is to simply INSERT or UPDATE a single record, depending on whether it already exists. MySQL's … Continue reading How to simulate MySQL’s INSERT statement extensions →

As most people still do I learned to read using books. WhooHoo!

Books are nice. Besides reading them they are also a nice decoration on your shelf. There is a brilliant TED talk by Chip Kidd on this subject.

But sometimes books have drawbacks. This is where I have to start the comparison with vinyl records (Yes, you're still reading a database oriented blog). Vinyl records look nice and are still being sold and yes I also still use them. The drawback is that car dealers start to look puzzeled if you ask them if your new multimedia system in your car is able to play your old Led Zeppelin records. The market for portable record players is small, and that's for a good reason.

The problem with books about databases is that they get old very soon. …

In the last few weeks I’ve been caught off guard by the number of people who’ve told me they have been evaluating Percona XtraDB Cluster (link), and even more surprised at the projects they’re considering it for. Yesterday alone I spoke to several people who have been evaluating it for large, mission-critical enterprise deployments. Some new, some to replace existing systems that use standard MySQL replication. What was interesting is that some people said they’ve been putting it through its paces for months — before we even released it as GA.

Another person said he was evaluating it and tried a bunch of things like killing nodes, and it “just worked.” He sounded like he’d been suspicious: had it REALLY worked? But then, on further investigation, he was able to confirm that yes, it had just worked. The node went away; the cluster as a whole …

I don’t think there is a single good-quality MySQL init script for a Unix-like operating system. On Windows, there is the service facility, and I used to write Windows services. Based on that, I believe Windows has a pretty good claim to better reliability for start/stop services with MySQL.

What’s wrong with the init scripts? Well, let me count the reasons! Wait, I’m out of fingers and toes.

I’ll just mention the two annoying ones that I’ve run into most recently. Both are on Debian, though there is nothing especially broken about Debian’s init scripts. The first one comes from parsing my.cnf wrong and not treating pid-file and pid_file identically. The server treats them identically, thus, so should any other program that reads the my.cnf file (there’s this program called my_print_defaults… use it!). The second bug is because Debian uses two configuration files for start/stop services: the init script reads …

It’s nice that this happened again this year:

The question I ask myself every year is, When am I going to get time to study all of these great blog posts?

Further Reading:

• The first guest post on Xaprb blog
• Schedule for MySQL-and-beyond conference is live
• MySQL conference Ignite call for proposals
• …

What are your pet peeves about the tools? I have a lot. For example, pt-table-sync doesn’t let me do things I want it to do sometimes (and I know it could be made to do them). Another example: pt-query-digest’s tcpdump parser doesn’t pay attention to TCP sequence numbers, so sometimes it invents a really long-running query where there isn’t one. Post your gripes, big and small, in the comments!

(This blog post is an attempt to do a 37signals approach to prioritizing: the stuff people complain about repeatedly is what you should focus on. No promises about fixing the gripes — I just want to hear them!)

Further Reading:

• I need your advice on how to package MySQL Toolkit as one file
• …

Oracle has released another huge batch of improvements to MySQL slated for MySQL 5.6. The pace of innovation in MySQL is accelerating rapidly and the improvements are impressive. I have not read all of the blog posts thoroughly yet, but I’ve skimmed and it looks fantastic.

The link posted above is ONE of the sets of improvements: there are also a bunch of other posts about new improvements to the query optimizer, InnoDB, and probably more. But I don’t see a single table-of-contents blog post for those that I can link to. Hence I recommend that you look at Planet MySQL to find these posts.

Further Reading:

• Great things afoot in MySQL …

MySQL became wildly successful in part because it had built-in, simple replication. Sure, it had lots of interesting failure scenarios and was not great at first — it is much better these days — but it was nevertheless successful because there was a single, out-of-the-box, not-very-complex way to do replication. I have opined many times before that this was one of the killer features missing from PostgreSQL. I think that can large explain why MySQL became more popular more quickly.

The new killer feature is automatic sharding, in my opinion. If you’re not accustomed to the word, “sharding” means partitioning of a large dataset across many servers.

It is easy to poke fun at MongoDB’s current limitations, but for all that, it has a story to tell about sharding. There is One Right Way To Do It in MongoDB, and it’s a part of the product.

I don’t see sharding …