Two cons against NoSQL data stores read like this: 1. It’s very hard to move data out from one NoSQL to some other system, even other NoSQL. There is a very hard lock in when it comes to NoSQL. If you ever have to move to another database, you have basically to re-implement a lot [...]

MySQL’s SHOW STATUS and SHOW VARIABLES commands (or queries against the corresponding INFORMATION_SCHEMA tables) don’t always show what they say. In particular, SHOW STATUS contains several rows that aren’t status-related, but are really configuration variables in my opinion (and it is an opinion — sometimes the difference isn’t black and white).

Here’s a short list of some status counters that I think are really better off as configuration variables:

• Innodb_page_size
• Slave_heartbeat_period
• Ssl_cipher
• Ssl_cipher_list
• Ssl_ctx_verify_depth
• Ssl_ctx_verify_mode
• Ssl_default_timeout
• Ssl_session_cache_mode
• Ssl_verify_depth
• Ssl_verify_mode
• Ssl_version

Most of those are legacy, but Slave_heartbeat_period is a recent addition.

Can you think of others? What are your …

Last night I wrote about trending data with a moving average, and then after I went to bed, I realized I made a mistake on the chart I showed. I calculated α for the exponentially weighted moving average so that the average age of metrics approaches 60 samples as time approaches infinity, and I plotted that on the same chart with a 60-sample simple moving average.

I’ve made this mistake several times before. The mistake is that the average age of the metrics in the 60-sample simple moving average is 30, not 60.

Here’s what the chart looks like if I change the exponential moving average to a 30-second average age:

If you compare this with yesterday’s chart, you’ll see that today’s red line …

In my recent talk at Surge and Percona Live about adaptive fault detection (slides), I claimed that hardcoded thresholds for alerting about error conditions are usually best to avoid in favor of dynamic or adaptive thresholds. (I actually went much further than that and said that it’s possible to detect faults with great confidence in many systems like MySQL, without setting any thresholds at all.)

In this post I want to explain a little more about the moving averages I used for determining “normal” behavior in the examples I gave. There are two obvious candidates for moving averages: straightforward moving averages and exponentially weighted moving averages.

A straightforward moving average just computes the average (mean) over the last N samples of data. In my case, I used 60 samples. This requires keeping an array …

I’ve been browsing some mailing lists and so on (it doesn’t matter which ones) and came across the Great 32-Bit Debate afresh. The debate always starts with someone saying “I need 32-bit support” or “this doesn’t work on 32-bit systems,” and someone else raising an objection to that, saying that nobody uses 32-bit systems anymore and anyone who does is doing something wrong.

Why would anyone use a 32-bit OS in the year 2012? I can think of a few very good reasons.

1. The universe isn’t wholly x86_64 yet; there’s still ARM and other non-server systems.
2. Developer laptops often run 32-bit operating systems, especially when Linux is the developer’s preference, because it works better with a lot of proprietary software such as Flash and audio drivers, and generally causes a lot less user headache. This is especially relevant because Ubuntu, Fedora, and other popular distributions are targeted towards …

How do you connect to and converse with your database of choice (MySQL, in my case)? Chances are it’s largely formed by the programming language you choose. I’ve worked with a variety of programming languages. Anytime I begin learning a new one, I am reminded again that there’s more than one way to skin a cat.

Over the last few years, I’ve mostly programmed in Perl and shell. The shell interface to MySQL is simple: the mysql command-line tool. There is not much to think about. In Perl, however, the standard is to use DBI. I have grown accustomed to DBI over the years, but that doesn’t mean I like it. I think it’s one of the worst database APIs I’ve seen. The abstractions it uses (there are only two object abstractions: connections and “statement handles”) are awkward to the extreme.

It does get worse, though. PHP historically used libmysql’s C library for connecting to MySQL, and similarly used native …

Do you ever find yourself wanting to open several terminal windows and send the same commands to all of them? I’ve had this need many times, and I’ve never found a completely satisfactory solution. I’ve also known a lot of people who’ve written various sets of scripts to help them accomplish such tasks.

In no particular order, here are a few ways I’ve done this in the past:

1. Facebook’s pmysql client
2. The dsh tool
3. Several screen windows named remoteXXX, followed by a bash for-loop: while read cmd; do screen -X at remote# stuff "$cmd"; done
4. Using many PuTTY windows and the puttycs tool
5. Opening many tabs in KDE’s Kterm tool and selecting the options to send input to all tabs

Here …

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
• …