The latest from the MySQL community

The traditional way to build Cacti templates is through the Cacti web interface. This is an enormous amount of work, and the result is generally not very consistent or good quality. The process is too error-prone. You can export the templates as XML, but they tend to have problems such as version incompatibilities with other Cacti installations, and it’s hard to adapt them for user preferences such as different graph image sizes and polling intervals.

The way I build Cacti templates is exactly the opposite. I create a data structure in a file, which looks like many configuration file syntaxes you’ve probably worked with. It represents the graphs, templates, scripts, and so on. From this, a tool generates the XML template file, which is a universal template definition, and is a breeze to import into Cacti. It is completely consistent and has zero cruft in it. This process prevents errors, and the results are perfect every time. …

I created a new tool this week:
http://code.google.com/p/shard-query

As the name Shard-Query suggests, the goal of the tool is to run a query over multiple shards, and to return the combined results together as a unified query. It uses Gearman to ask each server for a set of rows and then runs the query over the combined set. This isn't a new idea, however, Shard-Query is different than other Gearman examples I've seen, because it supports aggregation.

It does this by doing some basic query rewriting based on the input query.

Take this query for example:

select c2, 
       sum(s0.c1), 
       max(c1) 
 from t1 as s0 
 join t1 using (c1,c2) 
 where c2 = 98818 
 group by c2;

The tool will split this up into two queries.

This first query will be sent to each shard. Notice that …

Multi-byte characters can cause quite a few problems for the unsuspecting DBA or web master. Most of the times all you need to do to figure out how to fix the problem is detect which database records have UTF-8 data in them. Scanning records manually is not an option. Try the following query to find strings with multi-byte [...]

Web Operations

Web Operations. By John Allspaw and Jesse Robbins, O’Reilly 2010. (Here’s a link to the publisher’s site).

This book is due out in about a month. It is part of O’Reilly’s Beautiful series, which you might know through Beautiful Code. This one’s about web ops, of course. There are a dozen contributors, including some of my favorites such as Theo Schlossnagle, whose …

Daniel changed mk-query-digest to use much less memory. It parsed and aggregated a 2GB MySQL slow query log file in a few dozen megabytes of memory for me yesterday. Thanks to Facebook for sponsoring this work.

Related posts:

1. mk-query-digest now supports Postgres logs
2. Learn about mk-query-digest at PgEast 2010
3. Slides from my session on mk-query-digest at PgEast 2010
4. …

I spent a little time yesterday doing some things with the “summary” tool from Aspersa. I added support for summarizing status and configuration of Adaptec and LSI MegaRAID controllers. I also figured out how to write a test suite for Bash scripts, so most major parts of the tool are fully tested now. I learned a lot more sed and awk this weekend.

There is really only one way to get status of Adaptec controllers (/usr/StorMan/arcconf), but the LSI controllers can be queried through multiple tools. I added support for MegaCli64, as long as it’s located in the usual place at /opt/MegaRAID/MegaCli/MegaCli64. I am looking for feedback and/or help on supporting other methods of getting status from the LSI controllers, such as megarc and omreport. If you can contribute sample output from these tools, please attach them as a file to a new issue report on the project’s issue …

These days I spend more time looking at /proc/diskstats than I do at iostat. The problem with iostat is that it lumps reads and writes together, and I want to see them separately. That’s really important on a database server (e.g. MySQL performance analysis).

It’s not easy to read /proc/diskstats by looking at them, though. So I usually do the following to get a nice readable table:

• Grep out the device I want to examine.
• Push that through “rel” from the Aspersa project.
• Add column headers, then format it with “align” from the same project.

Here’s a recipe. You might want to refer to the kernel iostat documentation too.

wget http://aspersa.googlecode.com/svn/trunk/rel
wget http://aspersa.googlecode.com/svn/trunk/align
chmod +x rel align
while sleep 1; do grep sdb1 …

I like to write tools that make hard things easy, when possible. By and large, MySQL is easy and simple. But some simple things are too hard with MySQL. I want to change that, at least for the things that matter the most to me, and which I think I know how to fix.

I will probably write a lot about this. I have already written a number of rants blog posts about the lack of instrumentation in MySQL, and that is where I’ll probably continue to put most of my energy.

To begin with, imagine this simple scenario. You are a remote DBA. Your client says “New Relic is showing periods of slow response time from the database.” You connect to MySQL at the command line and try to troubleshoot. How do you catch the problem in action, from within the database itself? The following are no good:

• It doesn’t count to see the problem two minutes later by observing the application tier, as New Relic does. That’s too late, and …

I was just reading up on the syntax for index hints in MySQL, and noticed this:

An index_name value need not be a full index name. It can be an unambiguous prefix of an index name. If a prefix is ambiguous, an error occurs.

I actually prefer not to have extra “syntactic sugar” features such as this. It helps avoid bugs and unexpected behavior. Even if I don’t use it intentionally, I can get bitten by it, if someone adds another index whose name has the same prefix as one that I already use:

mysql> create table t(a int primary key);
mysql> select * from t force index(PRIMAR);
Empty set (0.00 sec)

mysql> alter table t add key PRIMARY_2(a);

mysql> select * from t force index(PRIMAR);
ERROR 1176 (HY000): Key 'PRIMAR' doesn't exist in table 't'

I actually considered adding support for prefixes of …

Most database companies would be proud to say that their products perform optimally out of the box. It means they accomplished a Herculean feat of engineering. But most databases have configuration options because this is almost impossible. For example, MySQL has scores of tuning options, and it needs a lot more.

So when someone benchmarks your database and makes you look bad, usually you can say “that benchmark was run by someone who doesn’t know how to properly tune my database software.”

But what if the benchmarker claims that your database didn’t need to be tuned, (via Dave Page), because it’s optimal out of the box? Do you accept the benchmark results, or reject the compliment?

Related posts:

…