Baron Schwartz
Dot-Org Pavilion at the Percona Live MySQL Conference
Are you involved with an open-source project that’s interesting to MySQL users, such as Nginx, PHPMyAdmin, Drupal, Jenkins, PHP, and so on? Percona just published the application form for dot-org groups to have a free expo hall booth in the Percona Live MySQL Conference in April. Please submit your applications now, and tell your friends about this, because a) the schedule for applying is very short, and b) space is limited.
For those of you who don’t know what this is, it’s another of the O’Reilly traditions we’re trying to continue. (We are trying very hard to make this event as close to a clone of O’Reilly’s as we can.) It’s a free table in the expo hall where people who participate in a non-commercial open source project can exhibit. I organized a Maatkit booth a few times in the past, and was always really grateful to O’Reilly for making the space available. Space in the expo hall is at a premium, but we think that these dot-org booths are even more valuable to the open-source projects and the conference attendees.
So, please tell your friends who care about open source, and ask them to tell their friends too. Let’s get some great open-source projects into the expo hall, alongside the commercial vendors!
Further Reading:
More details about SchoonerSQL performance, please!
Schooner has a blog post showing that one node of their product beats 9 nodes of Clustrix’s in throughput. But this reduces everything to a single number, and that’s not everything that matters. If you’ve looked at Vadim’s white paper about Clustrix’s (paid-for) performance evaluation with Percona, you see there is a lot of detail about how consistent the throughput and response time are.
I’d love to see that level of details in any product comparison. A single number often isn’t enough to judge how good the performance is — fast is not the only thing that matters.
I have absolutely no doubts that a single node of Schooner’s product can run like a deer. It isn’t doing any cross-node communication, after all, so it had better be faster than something that blends multiple nodes together into a virtual “single database server.” And I think if the full story were told, it would be a great knock-down drag-out fight. Give us more details, Schooner!
Further Reading:
Central Virginia MySQL Meetup has reached cruising altitude
The first Central Virginia MySQL Meetup was a nice little howdy-do, and as a test flight, I think it showed that the bird can get off the ground quite nicely. So, with the generous help of our meeting host Meddius, we’re going to do it regularly on the 3rd Wednesday of every month. The next event is already scheduled — I will be talking about high availability options for MySQL.
I’m interested in having outside speakers. Anyone who’d like to come and present something MySQL-relevant, please feel free to email me, or contact me via the Meetup page with the “suggest a Meetup” feature. If you’re traveling from outside the area, the airport is CHO, and it’s about 30 minutes away. Amtrak is also very convenient. I’m happy to chauffeur you, and can help you find lodging too.
I will not try to steer overly much, but I hope that this turns into a group where people introduce themselves, mention job openings and other news of interest, and so on.
There are also a couple of related meetups nearby that I want to promote: NOVA MySQL at AOL’s headquarters led by Mike DelNegro, DevOps DC at CustomInk’s offices led by Nathen Harvey, and one I haven’t been to yet but hope to attend soon, Shenandoah Ruby Users Group in Harrisonburg near Rosetta Stone’s headquarters, led by John Paul Ashenfelter.
Further Reading:
Free webinar Wednesday: verifying replication integrity
Join me Wednesday for a free webinar on using the new Percona Toolkit v2.0.3 to verify replication integrity. If you’re not familiar with this topic, it’s one of those must-do things that no one officially tells you is necessary with MySQL. The new tools in Percona Toolkit 2.0.3 make it much easier and less complicated — and safer — than before. Well worth an hour of your time.
Further Reading:
Organize to resist SOPA in DC
If you’re in the DC area, there’s an emergency meeting of the DC Tech Meetup to get involved in advocating against SOPA and PIPA legislation.
If you can’t make it in person, you can use the EFF’s online form to write your representatives about SOPA and PIPA.
Don’t wait until it’s too late. The Internet is a vital part of our freedom. Well-intentioned legislators could damage that greatly while trying to solve problems they don’t understand fully.
Further Reading:
High Performance MySQL Third Edition pre-order available
You can pre-order the new edition of High Performance MySQL now on Amazon or via O’Reilly’s website.
Eric Bergen called the second edition “the best MySQL book on the planet“. What will the third edition be called? The best in the solar system? This reminds me of a joke, supposed to be a true story: three pizza restaurants next to each other had signs saying “best pizza in town,” “best pizza in the world,” and “best pizza on this block.”
Further Reading:
Not if, but when
As a MySQL consultant, I spend a lot of time talking with people about their backups. More specifically, we talk a lot about recovery. I had an interesting incident myself, which illustrates some of the things that are bound to happen as time passes.
First, let me explain how I do my personal backups. I have a series of external hard drives, which are fully encrypted, as is my computer’s hard drive. I maintain a full mirror of my computer’s hard drive on these external hard drives. I occasionally switch the hard drives out, and carry one or more of them to a bank’s safe deposit box. I try to do this once a week, but sometimes it isn’t quite that often.
As a result, I have one hard drive located physically near my computer, which contains a very recent backup of all my work. I have at least one, usually 3 or more, other copies of my data in a slightly less fresh format, but durably stored in a bank.
While setting up a new computer recently, I somehow corrupted a GPG-encrypted file that I use quite often, and update frequently. (Perhaps a quantum bit flip or a solar flare — I don’t use ECC server-grade RAM, so this is actually possible/likely). As a result, I needed to get my most recent backup. I plugged in my external hard drive, and the drive physically failed. I spent some time doing diagnostics, and concluded that the drive really had failed. This reminded me that I had another hard drive, which I had set aside on a shelf couple of weeks ago, because it had also apparently failed. I pulled this drive off the shelf and ran diagnostics on it. It was also bad.
So I had lost my most recent copy of my file, as well as my most recent backup of it. it I could go to the bank and retrieve my previous backup of it, but that was a couple of weeks old, and I knew there were some changes that were not in that copy.
The happy ending to this story is that the corruption was only in the tail of the file, actually only in the last couple of bytes. I was able to decrypt everything except the last block or so, and then I retrieved that portion from my old backup. So in the end, I did not lose any data, but it was an interesting exercise.
The most interesting thing about this is the probability of several failures happening together. I think it is a natural human tendency to underestimate the probability of several different kinds of failures, or even several identical failures, happening at the same time. It quite commonly happens that hard drives fail at the same time, and we know that backups fail, and we know that files are corrupted or deleted, and it’s not a matter of if, but when these things happen together. This is why I have several copies of my backups in different places.
I’m still glad that I do backups the way that I do, keeping my own backups instead of relying on some online backup provider. I have heard many horror stories about them, and witnessed a few myself. I do not trust anyone else with my backups.
Further Reading:
New Central Virginia MySQL Meetup
I’ve created a Central Virginia MySQL Meetup group for those of you in the area. Our first event will be in a couple of weeks at Meddius’s headquarters. I’d like to meet and get to know more people in this area who use MySQL, so come out and we’ll have a good time together! Oh, and beer. And pizza. Of course.
Further Reading:
Percona Live MySQL Conference comes to Washington DC
If you are using or considering MySQL, and you’re in the Washington DC area, there’s an excellent MySQL conference coming up on January 11th. I know because I am helping organize it.
The event is Percona Live. It’s the latest installment in our series of regional events, which have been very well received in New York, London, and San Francisco. Take a look at the schedule of talks — this will be a very solid day of learning and networking.
Further Reading:
An opportunity to participate in MySQL research
I’m researching algorithms for automatic fault detection in MySQL (see my previous post for context). I need real-world data samples to test the algorithm. Can you help by sending me a bit of data from your production server?
The end goal is an open-source tool that will be a standard part of a typical MySQL installation. The problem I’m trying to solve for all MySQL users is this: something went wrong, what was it? Most of the time there’s no way to answer that; you have to set up a set of tools and hope you capture enough information to diagnose the problem next time. We need a tool that just runs all the time even when you don’t think anything is going to go wrong.
You can help build this tool. I need samples from a wide variety of healthy and sick servers, both heavily and lightly loaded. I need samples that are between a few hours and a week or so long. Here is a script that will gather what I need:
$ mysqladmin ext -i1 | awk ' /Queries/{q=$4-qp;qp=$4} /Threads_connected/{tc=$4} /Threads_running/{print q, tc, $4}'The output should look something like this:
2147483647 136 7 798 136 7 767 134 9 828 134 7 683 134 7 784 135 7 614 134 7 108 134 24 187 134 31 179 134 28 1179 134 7 1151 134 7 1240 135 7 1000 135 7Please save this output to a file, and contact me at moc.anocrep@norab (reversed) if you would like to offer a dataset for us to test on. If you need any help setting up the data collection, you can use the same email. I’d also appreciate if you’d help spread the word about this via Twitter or other means. Thanks very much!
Further Reading:
What does MariaDB’s user feedback feature report?
I was curious what information MariaDB’s “phone home” user feedback plugin sends. (It works on more than just MariaDB, by the way.)
It’s easy enough to find out: just load the plugin, then select from the INFORMATION_SCHEMA.FEEDBACK table. This returns a lot of rows that are obviously the status counters and variables, as well as the plugins loaded in the server. A quick exclusion join will eliminate those, and the result on my laptop is this:
select f.* from feedback as f left outer join global_variables as v on f.variable_name = v.variable_name left outer join global_status as s on f.variable_name = s.variable_name left outer join plugins as p on f.variable_name = p.plugin_name where s.variable_name is null and v.variable_name is null and p.plugin_name is null; +--------------------+--------------------------------------+ | VARIABLE_NAME | VARIABLE_VALUE | +--------------------+--------------------------------------+ | Cpu_count | 2 | | Mem_total | 4186529792 | | Uname_sysname | Linux | | Uname_release | 2.6.41.1-1.fc15.i686.PAE | | Uname_version | #1 SMP Fri Nov 11 21:43:42 UTC 2011 | | Uname_machine | i686 | | Uname_distribution | fedora: Fedora release 15 (Lovelock) | +--------------------+--------------------------------------+This actually isn’t all, though. If you check the output of SHOW VARIABLES you’ll see an extra few rows, one of which is this:
+---------------------+------------------------------+ | Variable_name | Value | +---------------------+------------------------------+ | feedback_server_uid | xlGYjFKJ0ivpSWAktGglpEgVTq8= | +---------------------+------------------------------+I’ll have to look into how that’s calculated. It might be useful.
Further Reading:
Automatically detecting abnormal behavior in MySQL
Over the course of years, I have observed that the three most sensitive indicators of MySQL having a server lockup are the queries per second, number of connections, and number of queries running. Here is a chart of those three on a production system. Find the bad spot:
I am currently working on developing an automated system that detects abnormal behavior in these three metrics, but doesn’t require any a priori inputs or thresholds, e.g. you don’t have to tell it “more than X is bad.” (It could be that during a low period of the day, X is different than during the peak load.)
It turns out that this is hard to do reliably, without a lot of false positives and without false negatives (not triggering during an incident). If there is existing literature on the mathematical techniques to do this, I’d be interested in not reinventing the wheel. Does anyone have references to share?
Further Reading:
Special mysqldump fingerprinting rule in pt-query-digest
The pt-query-digest tool has a number of special cases that affect how it “fingerprints” queries when it groups similar queries together to produce an aggregated report over the group. One of these is a special rule for queries that appear to come from mysqldump, of the following form:
SELECT /*!40001 SQL_NO_CACHE */ * FROM `users`All such queries will be fingerprinted together and presented in a single class of queries. I remember many instances where mysqldump queries crowded the report of the “most important” queries and just caused other queries to be excluded. Grouping them together made it obvious that mysqldump’s load on the server was a problem, but didn’t obliterate other interesting things we wanted to see in the report.
Further Reading:
Status update on High Performance MySQL
The third edition is nearly done. I’ve committed first drafts of all chapters, and all but one appendix. I need to do the last appendix and then rewrite the preface, which is a few days of work at my current pace. After that, it’s the usual tech review, copyediting, updates to figures, etc — and then it’s off to production.
I’m really pleased with this edition. I was planning on it just being a refresh of the second edition to reflect what’s new in MySQL-land, but it’s almost a complete rewrite. There’s a lot more focus on a logical approach throughout: what happens in the server, what are the limitations, why this matters, what are the practical consequences and applications, and therefore…. The “and therefore” is the real reason you buy a book such as this one.
Further Reading:
When documentation is code
One of the things I think we did right with Maatkit (and now with Percona Toolkit) is making the documentation part of the code itself. So much redundancy and wrong documentation has been eliminated by making the tool actually read its own documentation when it starts up. As an example, the default value of the –shorten option is defined in the documentation (it’s Perldoc) like this:
=item --shorten type: int; default: 1024Not only is the documentation part of the code, but the tool’s –help output is generated from it too. The existence, type, defaults, and even the behavior of the command-line options is defined in the documentation. If I execute the tool with the –help option, you can see that default value:
[baron@ginger bin]$ ./pt-query-digest --help | grep -- --shorten --shorten=i Shorten long statements in reports (default --shorten 1024If I change the tool’s documentation to say the default is 2048, you’ll see it in the output:
[baron@ginger bin]$ ./pt-query-digest --help | grep -- --shorten --shorten=i Shorten long statements in reports (default --shorten 2048We even have tests for the documentation. If the documentation is code, and code should be tested, then the documentation should be tested too. I updated the documentation for the new version of pt-table-checksum the other day without testing it, and pushed the code back to Daniel, who merged it and ran the tests — and found that I’d changed a bit of the documentation that said one option disables another option. A statement like that needs to be tested formally.
We have many thousands of unit tests for Percona Toolkit last time I checked. One of them guarantees that this little bit of documentation is correct. What a great thing. I continue to try to find ways to make the tools’ documentation formally verifiable as much as possible. It’s not possible to do 100% of it, but a surprising amount can be tested.
Further Reading:
Free DevOpsDC meetup on Tuesday
Next Tuesday I’ll be speaking at the DevOpsDC meetup at CustomInk’s offices. I’ll talk about why MySQL downtime happens and what you can do to avoid or prevent it. This is a research-based talk (but it’s not scientific… they’re different) that draws on hundreds of downtime issues I’ve studied. The related white papers are available on Percona’s website. Hope to see you there!
Further Reading:
Speaking at the Southern Computer Measurement Group meeting on Thursday
I’ll be presenting at the Southern Computer Measurement Group’s meeting on Thursday. I’ll discuss how to extract scalability and performance metrics from TCP/IP packet headers. Registration is inexpensive, but it’s even less if you register by Monday. There is a full schedule of other good talks — it is an all-day meeting.
Further Reading:
Blackhole tables and auto-increment keys
Blackhole tables are often used on a so-called “relay slave” where some operation needs to happen but no data needs to exist. This used to have a bug that prevented AUTO_INCREMENT columns from propagating the right values through replication, but that was fixed. It turns out there’s another bug, though, that has the same effect. This one is caused when there is an INSERT into a Blackhole table, where the source data is SELECT-ed from another Blackhole table.
I think it’s wise to keep it simple. MySQL has tons of cool little features that theoretically suit edge-case uses and make ninja tricks possible, but I really trust the core plain-Jane functionality so much more than these edge-case features. That’s precisely because they often have some edge-case bugs, especially with replication.
Something that’s new to MySQL recently is Galera replication. The more I think about it, the more I think it’s fundamentally the right way to replicate. Statement-based replication was brittle; row-based is less so, but still has all kinds of gotchas. The real problem with both is that they are built into the server, not the storage engine. Engine-level replication is the way to go. PBXT has had engine-level replication for a while, although I’ve never used PBXT in production (and kudos to PostgreSQL for adding built-in replication, too). I used to want InnoDB to do replication via streaming the redo logs and applying them, but that actually has a lot of limitations. Galera is InnoDB’s answer to engine-level replication. I think Galera holds a lot of promise for the future.
Further Reading:
What I want to see at Percona Live
For me, next week’s Percona Live is a lot like other events: I know I will be busy meeting and greeting and helping unpack boxes and so forth, but I’ll also be talking and attending other talks. With five concurrent tracks, it’s tough to decide. This is roughly the equivalent of the MySQL conference every year, which has more tracks, but historically it’s been easy to cross off some talks as sales pitches. Not so with this event! Here’s what I want to see the most:
- Clustrix’s keynote. I’ve watched the progress of our tests with Clustrix very closely. If you’re coming to the conference, don’t oversleep.
- Henrik’s session on high availability technologies.
- Johan’s session on NDB performance.
- Fred’s talk on Spider.
- Shlomi’s talk about SQL coding. Shlomi’s talks kind of remind me of the things Beat Vontobel does (solving a sudoku with a single query, anyone?)
- And of course, Domas’s closing keynote.
Perceptive readers will note that I left some slots empty. I haven’t decided yet. It’s too hard to choose.
I will be presenting two things next week: a 3-hour tutorial on how to diagnose problems (part one: method; part two: tools), and a session on black-box performance analysis and scalability modeling with TCP packet headers.
See you there — I hope!
Further Reading:
