Here is the typical “Big” data architecture, that covers most components involved in the data pipeline. More or less, we have the same architecture in production in number of places[...]

If you’ve ever used non-opensource code, or applications that you don’t have complete control over, then you may have run into situations you need to alter data on a per-row basis but been unable to do so for lack of application SQL access. The solution to this type of problem is to use a MySQL TRIGGER, which allows us to execute arbitrary SQL commands when defined events occur. Why is this useful and how does it work? Well…

For example, I have a freeRADIUS server that uses MySQL as a backend for the user authentication, and one of my server applications (HostBill) provides a freeRADIUS plugin that allows my users to manage their RADIUS accounts; however the default freeRADIUS schema lacks a DATETIME column on the user table. When a user is created (INSERT) or has their password changed (UPDATE) I have no row data that tells me the dates when these operations were issued. Typically this would be a trivial change: issue an ALTER TABLE …

With the Big Data craze that’s sweeping the world of technology right now, I often ask myself whether we’re deficit-spending, so to speak, with our data consumption habits. I’ve seen repeated examples of being unwilling to get rid of data, even though it’s unused and nobody can think of a future use for it. At the same time, much Big Data processing I’ve seen is brute-force and costly: hitting a not-very-valuable nut with a giant, expensive sledgehammer. I think the combination of these two problems represents a giant opportunity, and I’m going to call the solution Smart Data for lack of a better word.

What’s the problem, in 25 words or less? I think it’s that we’re collecting a lot of data simply because we can. Not because we know of any good use for it, but just because it’s there.

What is the real cost of all of this data? I think we all know we’re well behind the curve in making use of it. A huge …

I’ll speak at the Central Virginia Ruby Enthusiasts group on Nov 13th. The topic is adaptive fault detection in MySQL servers. The techniques apply to any system for which you can measure throughput and concurrency, so come on out and learn how you can get rid of threshold-based monitoring!

Further Reading:

• Central Virginia MySQL Meetup has reached cruising altitude
• New Central Virginia MySQL Meetup
• Speaking at the Shenandoah Ruby Users …

I was just skimming through the latest “Meet the Experts” podcast from Oracle, whose topic is debugging stored routines in MySQL. The tl;dr (tl;dl?) version is if you’re working with Windows and .NET, you can install a Visual Studio plugin that lets you debug stored routines in the server via the .NET connection libraries. That’s pretty nice, for those who are using that platform. The podcast is only a few minutes, so if you’re interested, by all means listen to it, or take a look at the documentation.

The ideal way to debug stored routines would be an API in the server, but that doesn’t exist. Nevertheless, I remembered having seen some sort-of implementations of debugging at times in the past. My memory was …

I’ve been doing a little bit of programming in Go recently, and really enjoying it. It’s an awesome language with really solid libraries. It reminds me a lot of the .NET framework in that it’s well-thought-out, but the Go language itself is a lot smaller, less formal, and a lot more expressive than I ever found C# to be. And that’s saying a lot — C# was my previous favorite language, along with the occasional Java, C++, Python, JavaScript, and — gasp — Visual Basic 6. Go beats them all.

But I digress. I’m writing Go applications that talk to MySQL. Some of them talk to the database in read-only ways, and I just wanted to share this neat little nugget about the MyMySQL client libraries (pure-Go implementation; not a wrapper around a C library). One of its features, which you can enable optionally, is autorc. This stands for auto-reconnect to the server. It’s …

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 …