Last time I said that the USL has a forgotten third coefficient, the coefficient of performance. This is the same thing as the system’s throughput at concurrency=1, or C(1). How do you determine this coefficient? There are at least three ways.

Neil Gunther’s writings, or at least those that I’ve read and remember, say that you should set it equal to your measurement of C(1). Most of his writing discusses a handful of measurements of the system: one at concurrency 1, and at least 4 to 6 at higher concurrencies. I can’t remember a time when he’s discussed taking more than one measurement of throughput at each level of concurrency, so I think the assumption is that you’re going to take a single

  [Read more...]

If you’re familiar with Neil Gunther’s Universal Scalability Law, you may have heard it said that there are two coefficients, variously called alpha and beta or sigma and kappa. There are actually three coefficients, though. See?

No, you don’t see it — but it’s actually there, as a hidden “1″ multiplied by N in the numerator on the right-hand side. When you’re using the USL to model a system’s scalability, you need to use the C(1), the “capacity at one,” as a multiplier. I call this the coefficient of performance. It’s rarely 1; it’s usually thousands.

To illustrate why this matters, consider two systems’ throughput as load increases:

  [Read more...]

Moral of the upcoming story: when your systems rely on someone else’s software, make sure you test it thoroughly to understand how it works.

I found a couple of interesting things about the go-mysql-driver driver for Go. One is that it prepares every query before executing it, and then closes it afterwards, if you’re just using the db.Query() or db.QueryRow() functionality. There is zero benefit to this; Bill Karwin has probably listed it as an antipattern somewhere. I asked for one query, but I got three.

The other is that it doesn’t open the database connection when you call db.Open(). You can call that function and get no error. The first query on the resulting “db” object will actually connect to MySQL. Thus it’s actually kind of

  [Read more...]

Those who’ve been around the MySQL world are probably aware of the much-discussed topics of GPL licensing, dual licensing, and in particular, licensing of the client libraries (also called connectors or drivers) and the FOSS exception (http://www.mysql.com/about/legal/licensing/foss-exception/) to that licensing. This is newly relevant with the announcement of a permissively-licensed MySQL-compatible client library for MariaDB.

The difference is that this time there’s been some question about the provenance and history of the source code. Some people asked me about this. Some of them were aware of a relatively obscure detail: there’ve been permissively licensed MySQL client libraries for years, in the form of libdrizzle, a BSD-licensed library for the Drizzle fork of MySQL.

Here are some of the thoughts that seemed to be going through

  [Read more...]

I was just bitten by failing to catch a MySQL warning. It’s the old familiar tune: I inserted 100 characters into a VARCHAR(50) and it didn’t throw an error*. Of course, then subsequent SELECT statements didn’t find the value I inserted.

What’s different this time is that I was using Go as the client. There is no single official MySQL driver for Go, although there are several good-quality community-maintained ones. I was using one of those through the official Go database interface, which is a simple and lightweight way to interact with relational databases. This interface will generate errors, but I didn’t think about warnings. This is funny, because usually I’m paranoid about capturing warnings from MySQL and treating them as errors.

After I discovered my mistake, I

  [Read more...]

I spoke last Friday at DevIgnition about what’s coming in the MySQL 5.6 release. My slides are embedded below.

Further Reading:

• Presentation uploaded for Maatkit talk at MySQL Conference
• Videos and slides for MySQL Conference 2010
• Surge 2011 slides, recap
• Slides for my MySQL Conference talks are online
• How to create stepping slides in OpenOffice.org Impress

I just realized that I didn’t hear a tremendous amount of feedback about Oracle’s MySQL Connect conference. Were you there? If so, how did you like it?

Further Reading:

• Schedule for MySQL-and-beyond conference is live
• I’m going to the upcoming MySQL Camp
• Why is MySQL more popular than PostgreSQL?
• Like it or not, it is the MySQL Conference and Expo
• I’m speaking at the MySQL conference in April

“Some people even think that “Hadoop” and “Big Data” are synonymous (though this is an over-characterization). Unfortunately, Hadoop was designed based on a paper by Google in 2004 which was focused on use cases involving unstructured data (e.g. extracting words and phrases from Webpages in order to create Google’s Web index). Since it was not [...]

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)

  [Read more...]

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

  [Read more...]

The following questions are of little importance, yet I find them entertaining. I stumbled upon these while developing QueryScript.

Can you guess the results of the following statements?

Pop quiz 1

SET @x := 7;
SELECT ++@x;

What is the computation result? What will be the value of @x?

Pop quiz 2

SET @ := 4;
SELECT @ + @'' + @``

What is the computation result?

Pop quiz 3

SET @a := 2;
SELECT @a = @'a' = @`a`

Do we get TRUE or FALSE? When?

Hints

Consider the following queries as hints to the above questions:

SELECT +++++-@x;
SELECT @ = @'', @ = @``
SELECT (@a = @'a') = @`a`

Answers

• Pop quiz

  [Read more...]

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 Group

  [Read more...]

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

  [Read more...]

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

  [Read more...]

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

  [Read more...]

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:

  [Read more...]

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

  [Read more...]

It has been a while since I wrote on this blog. Basically I had too much on my mind (expanding my department, holidays, etc) to actually write here and I’ll promise to post more regularly from now onwards.

Anyway, as the title already suggests: I found out how you can use CURDATE() in a wrong way. One of the developers in my company asked me to help him out as his code all of a sudden did not work properly anymore. Or even better: it used to process several thousands of rows and all of a sudden it processed none.

I looked at his code snippet and it was quite a long query with a lot of brackets:

  [Read more...]

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.

The universe isn’t wholly x86_64 yet; there’s still ARM and other non-server systems.

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

  [Read more...]

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.

  [Read more...]

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:

Facebook’s pmysql client

The dsh tool

Several screen windows named remoteXXX, followed by a bash for-loop: while read cmd; do screen -X at remote# stuff "$cmd"; done

Using many PuTTY windows and the puttycs tool

Opening many tabs in KDE’s Kterm tool and selecting the

  [Read more...]

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:

SELECT col1, col2… can be implemented with several variants of Unix utilities: cut and awk are the two most obvious. I tend to

  [Read more...]

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

  [Read more...]

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
• What I want

  [Read more...]

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:

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

  [Read more...]

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

  [Read more...]

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
• Archive strategies for

  [Read more...]