I wanted to set a field on a MySQL table to one of 4 values for testing purposes. Let's say I want to set the "pet" field to one of {cat,dog,rabbit,hamster}.

First, add a new field to the table:

alter table test add column `id` int(10) unsigned unique key autoincrement;

Now insert each of the four values:

update test set pet = 'cat' where MOD(id, 4) = 1;
update test set pet = 'dog' where MOD(id+3, 4) = 1;
update test set pet = 'rabbit' where MOD(id+2, 4) = 1;
update test set pet = 'hamster' where MOD(id+1, 4) = 1;

Finally, drop the additional field:

alter table test drop column `id`;

I'm always interesting hearing better/alternative ways to do this sort of thing.

Most of the servers I manage are 64-bit. I have one linode box that is 32-bit. I chose 32-bit because it has better memory usage than 64-bit, which is possibly important with a 512MB instance. This was probably a mistake as the management overhead involved with maintaining a 32-bit infrastructure for just one 32-bit machine is silly. No matter – we are where we are…!

I use the fnv_64 user-defined function from maatkit with MySQL. So, I need to build a 32-bit version for use on the 32-bit server.

Here's how to use mock to create a 32-bit build environment (in this case, for CentOS 5) on a 64-bit machine (the host is actually a Fedora 15 server).

Continue reading

I'm sure we've all seen this message from time to time when using puppet with exported resources:

Error 400 on SERVER: Exported resource Sshkey[foo] cannot override local resource on node bar.example.com

It's actually pretty easy to fix. Simply delete the exported resource for node foo.

Assuming you are using MySQL for your DB, something like this will do the trick:

mysql -e "delete from resources where restype like 'sshkey' and exported=1 and host_id = (select id from hosts where name 'foo')" puppet

We’ve had a bunch of new servers in place for around 3 months now. They seem to be working well and are performing just fine.

Then, out of the blue, our monitoring started throwing alerts on seemingly random servers. Our queues were building up – basically, database performance had dropped dramatically and our processing scripts couldn’t stuff data into the DBs fast enough.

What could be causing it?

Continue reading

As I mentioned in a previous post, the MySQL RPMs provided for RHEL/CentOS by percona are not actually compatible with RHEL/CentOS. They use the same package layout as the MySQL-provided RPMs.

Here's how I create my own RPMs having the same package layout as the RHEL/CentOS packages but with the percona highperf patchset applied.

Continue reading

ourdelta provide MySQL packages for various platforms, built with assorted performance/feature patchsets.

Sadly, like the percona builds, the RPM packages for RHEL/CentOS are not upstream-compatible, ie. they package MySQL differently.

I was planning to re-build the ourdelta packages to use the upstream RPM package layout but I've decided to stick with re-building the percona packages as I've already done the work for that.

Anyway, in case it helps someone, here's how to rebuild the ourdelta packages from the SRPM:

rpmbuild --rebuild \
  MySQL-OurDelta-5.0.87.d10-65.el5.src.rpm \
  --define 'ourdelta 1' \
  --define 'mysqlversion 5.0.87' \
  --define 'elversion 5' \
  --define 'patchset d10'