1. Page2Pub -- harvest wiki content and turn it into EPub and PDF. See also Sony dropping its proprietary format and moving to EPub. Open standards rock. (via oreillylabs on Twitter)
2. SQL Pie Chart -- an ASCII pie chart, drawn by SQL code. Horrifying and yet inspiring. Compare to PostgreSQL code to produce ASCII Mandelbrot set. (via jdub on Twitter and Simon Willison)
3. …

Every once in a while, I find myself needing to round a number up to the nearest even power of 10, or round a time interval to the nearest quarter of an hour, or something like that. This is actually quite simple, but for some reason I always find myself confused about how to do it. I have to reason it out all over again, instead of just remembering how to do it. Perhaps writing this blog post will help me remember next time.

The basic idea for rounding to whole multiples is to divide the number, losing precision. Then round, floor, or ceiling the resulting number, and multiply to get back to the original magnitude. For rounding to fractions, reverse the process: multiply, round and divide again.

This actually works for any programming language, not just SQL. But I find myself doing it in SQL most often.

Here’s an example of how to turn a year into a decade:

mysql> SELECT FLOOR(YEAR(NOW()) / 10) * 10 AS …

Do you test your application systematically when you upgrade or reconfigure your database server? You should! Here’s a real (anonymized) story of what happens if you don’t.

When we upgraded to 5.0.62 (from 5.0.27 and 5.0.45), our code broke for queries like this:

SELECT SUM(amt) FROM daily_amt WHERE day = FROM_UNIXTIME(1222889772);

The problem here was a wrong DATE/DATETIME comparison and other bug fixes in MySQL 5.0.62; it was stricter in enforcing the comparison.

This resulted in an outage and revenue loss to the company.

Daniel and I (mostly Daniel) continue to improve mk-upgrade to make it easy and inexpensive to find these kinds of scenarios before they bite you. Don’t get caught with your pants down — next time you make …

A while ago I wrote about a tool to help make upgrades safer. Since then, we have gotten several people to help sponsor development on this tool, and a few of our customers are using it to help find problems before they upgrade their systems.

I can’t think of a single one of the Maatkit tools that didn’t grow out of the need for deeper insight into some part of the system. This tool is no exception. And as always, these tools are like flashlights. When you crouch down near the floor, and shine your flashlight under the refrigerator, you should expect to find a few things that make you cringe.

The other day, one of our customers was using this tool and we started getting an error. The error was caused by the part of the tool that verifies that result sets are the same. Our thought on how to do this was to checksum the results of a query. You can …

Ryan posted an article on the MySQL Performance Blog about how to use mk-query-digest to analyze and understand your memcached usage with the same techniques you use for MySQL query analysis. This is an idea that came to me during the 2009 MySQL Conference, while talking to our friends from Schooner, who sell a memcached appliance.

It suddenly struck me that the science of memcached performance is basically nonexistent, from the standpoint of developers and architects. Everyone treats it as a magical tool that just performs well and doesn’t need to be analyzed, which is demonstrably and self-evidently false. memcached itself is very fast, true, so it doesn’t usually become a performance bottleneck the way a database server does. But that’s not the point. There is a …

The OpenSQLCamp 2009 European edition, is under scrutiny. There are 27 session proposals, from which we will need to get 12 in the schedule.
The open voting is done via Twitter or the mailing list.
I have a good feeling about it. Since I am proposing a public …

MySQL Administrator's Bible

MySQL Administrator’s Bible by Sheeri K. Cabral and Keith Murphy, 2009. Page count: 800+ pages. (Here’s a link to the publisher’s site.)

This book is a comprehensive reference guide to MySQL that’s accessible to beginning DBAs or DBAs familiar with another database. It has enough detail to be a useful companion throughout a DBA’s career. It also covers many related technologies, such as memcached, at a moderate-but-useful level of detail. This isn’t exactly a how-to book, and it isn’t exactly a reference manual; it’s more of a blend of the two.

The …

Suppose that you’re writing a new Maatkit tool (just a random example, really) and its job is to measure the difference in execution of queries. The simplest metric is execution time.

Now suppose that you’re trying to figure out a metric of badness. The query executes in a second on machine 1 and 1000 seconds on machine 2.  That’s a pretty bad change.  How do you quantify this?

Now you’ve got a query that executes in 1ms on machine 1, and 10ms on machine 2.  It’s a tenfold change.  Is it a bad change?  Maybe it’s just the difference in which files were cached in memory, or network latency because someone flooded the TCP pipe and the packets had to be backed off and retried, or something like that.  Is this significant?  How should it contribute to the badness score?

Let’s think of another …

Last weekend, my brother and I attended SELF 2009. A few thoughts on it:

The mixture of sessions was interesting. There were some really good ones. I think the best session I attended was an OpenSolaris/NetBeans/Glassfish/Virtualbox/ZFS session, given by a Sun employee. He was an excellent presenter, and really showed off the strengths of the technologies in a nice way. He started up enough VMs to make his OpenSolaris laptop chew into swap, and I thought it was fun to see how it dealt with that. I’ve heard Solaris and OpenSolaris do a lot better at avoiding and managing swapping than GNU/Linux, but I couldn’t make any opinion from watching. I did think it was odd to have this session at a “Linux” (yes, they left off the GNU) conference. But I thought the session was a good addition to the conference. In other sessions, and in the hallways and expo, there was a lot more …