PFunc

I've had a project over on google code for a while now called PFunc.  It was inspired by FUNC a project I used to use, but due to it's linear processing I figured I could improve by threading.  Func is written in python, and I'm far more comfortable in perl.  So I wrote Pfunc.  It's purpose, like Func's is to execute commands on arbitrary groups of servers immediately.  I figured Pfunc would be a threaded tied-me-over until the Func project progressed and added asynchronous processing of commands.  Instead the Func project has gone in a different direction, trying to be pluggable.  My tied-me-over has lasted much longer than I ever imagined.  I have yet to find something to replace it with.

Pfunc's focus is narrow.  It's about running a command on arbitrary groups of servers instantly all at the same time from the command line and providing easily readable output.  Pfunc also logs the output and exit status of all commands run through it to a mysql database.  Pfunc will copy files, but it is not a configuration manager.  Pfunc should only be used on a closed private network or over an stunnel, because while it does verify commands  to execute and files via DSA signatures, it passes traffic in plain-text. 

Setup is simple, download the code on the central machine you want to control other machines from, and put a copy on all the machines you want to control.  Create a database and import the provided .sql file.  One daemon called dsacertd runs on your central server to provide certs to clients for signing commands and small files.  Configure it to point to your database server and start this up, then create some clients and groups using the phostadd.pl and pgroupadd.pl commands.  Now you can configure the client machines to connect to the same database server and dsacertd server, then start up the pfuncd daemon.  That's it, your done.  Now you can execute arbitrary commands on any group of servers you defined with the pfunc command on your central server.  Add the -o option and get nicely formatted output, or you only receive exit status for each machine in the group.  While pfunc does not suffer from the interpolation problems of Func, command-line interpolation is still an issue.  You've gotta escape what your shell requires escaping.

Pfunc has become an integral part of my workplace and the application I support.  We use it for many things, including our RPM based release process, using it to execute synchronized yum update commands across groups of web servers.

Add AUTO_INCREMENT to MyISAM without Recreating the Table

Here's a Filthy hack.

Using ALTER table to add AUTO_INCREMENT to a column in MySQL takes significantly longer than you might think.  It's because behind the scenes it has to recreate the whole table.  If you've been using sequence tables and now you'd like to do away with them, this may take much longer than you'd think.  I recently ran into this problem.  I had around 450 databases with tens of thousands of tables in each that I needed to convert.  I found this article on mysql performance blog from a while back.  This only works for MyISAM.  It suggests that at least at that time one could simply create a new table defined the way you wanted it ( in their example case removing AUTO_INCREMENT) and switch out the .frm files from your new table to the one you want to change.  They go on to specifically say it doesn't work to add AUTO_INCREMENT.  While that might have been the case at the time, it's not true now.  You can add AUTO_INCREMENT to a column this way, with the addition of switching out the .MYI file as well.  Here's the process:

Do a 'SHOW CREATE TABLE `tablename`' on the table you'd like to add AUTO_INCREMENT.  Take the output and create a new table.  Alter the new empty table adding AUTO_INCREMENT with  MAX+1 from the PRIMARY KEY of the original table as the AUTO_INCREMENT value.  Make sure your column type matches the existing type of the table you're converting.  (I.E. int(10) unsigned)

ALTER TABLE `convert_table` MODIFY COLUMN `id` INT(10) UNSIGNED AUTO_INCREMENT, AUTO_INCREMENT = 1000;

Now switch out the .frm and .MYI files in /var/lib/mysql/databasename/ from the new table to the old one.  Then it's likely a good idea to repair the table.  Flushing and Locking the tables is also likely a good idea.

Here's the Perl code I wrote to do it to all of the tables I needed to do this for.  Backup first and use at your own extreme risk, it really is a filthy hack.

#!/usr/bin/perl
use strict;

use warnings;
use DBI;
use Carp;
use File::Copy;
use Getopt::Long;

my $dbuser = q{root}; # Database user
my $dbpass = q{}; # Database password
my $db;
my $safe = undef; # Lock and Flush Tables
my $remove = undef; # Drop the sequence tables
my $type = undef; # Don't convert the table if the Primary key isn't an int(10) unsigned
my $ext = '_seq'; # Pattern appened to your sequence tables
 
GetOptions(
'database|d=s' => \$db,
'safe|s' => \$safe,
'remove|r' => \$remove,
'type|t' => \$type,
'extension|x=s' => \$ext,
);
unless ( $db ) {
croak qq{ You must provide a database (-d/--database)\n };
}
my $dbh = DBI->connect(
qq{dbi:mysql:host=localhost;database=$db},
$dbuser,
$dbpass,
{ RaiseError => 1 }
) or croak qq{Can't connect to the db, I haven't done anything yet, no cleanup needed.\n}.DBI->errstr;

# Get a list of all the sequence tables
my $sthm = $dbh->prepare(q{
SELECT `TABLE_NAME`
FROM `INFORMATION_SCHEMA`.`TABLE_CONSTRAINTS`
WHERE `TABLE_SCHEMA` = ?
AND `TABLE_NAME` LIKE ?
});
$sthm->execute($db,qq{\%_$ext});

print "Commencing filthy hack\n";

$| = 1; # So we get output during the while
MAIN:
while ( my $row = $sthm->fetchrow_hashref ) {
# Dump the $ext to get the table name
$row->{'TABLE_NAME'} =~ s/$ext$//;
my $table = $row->{'TABLE_NAME'};
# Make sure the table exists
my $sth = $dbh->prepare(q{
SELECT `TABLE_NAME`
FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE `TABLE_SCHEMA` = ?
AND `TABLE_NAME` LIKE ?
});
$sth->execute($db,$table);
unless( $sth->fetchall_arrayref->[0]->[0]) {
print qq{\nTable $table missing, mabye you should drop $table$ext\n};
next MAIN;
}
# Get the table definition
$sth = $dbh->prepare(qq{SHOW CREATE TABLE $table});
$sth->execute;
my ($primary,$create_statement,$type);
while ( my $create = $sth->fetchrow_arrayref ) {
if ( $create->[1] =~ /AUTO_INCREMENT/ ) {
print "\nTable $table already has auto_increment, you might want to check this table out.\n";
next MAIN;
}

my $desc = $dbh->selectall_hashref(qq{DESC $table}, q{Key});
unless ( $desc->{'PRI'} ) {

print qq{\nWTF? No primary col found for table $table, this is going to need manual intervention.\n};
next MAIN;
}
# If type option set, don't convert if the type for the primary col isn't an int(10) unsigned
if ( $type ) {
unless ( $desc->{'PRI'}->{'Type'} eq 'int(10) unsigned' ) {
print qq{\nWTF? Your primary key isn't an 'int(10) unsigned' on table $table, it's a }. $desc->{'PRI'}->{'Type'} .qq{, I'm not touching it.\n};
next MAIN;
} 
}
$type = $desc->{'PRI'}->{'Type'};
# change the table name in the create statement to convert_table
$create->[1] =~ s/CREATE TABLE `[^`]+`(.+)/CREATE TABLE `convert_table`$1/;
# find the primary key
$primary = $desc->{'PRI'}->{'Field'};
$create_statement = $create->[1];
}

# Get the max val from the table to use for AUTO_INCREMENT
$sth = $dbh->prepare(qq{SELECT MAX(`$primary`) FROM `$table`});
$sth->execute;
my ($id) = @{ $sth->fetchall_arrayref} ;
$id = $id->[0] ? $id->[0]+1 : 1;

# Make sure we have an int cause the orginal DB might really be honked up
unless ( int($id) ) {
print "$id for table $table is jacked up. Manual intervention needed.\n";
next MAIN;
}

# Create the convert table
$sth = $dbh->prepare($create_statement);
$sth->execute or croak qq{Can't create the convert_table, you might need to do some cleanup\n}.DBI->errstr;

# For safty sake
if ( $safe ) {
$dbh->do(qq{FLUSH TABLES `$table`, `$table$ext`});
$dbh->do(qq{LOCK TABLES `$table` WRITE, `$table$ext` WRITE, `convert_table` WRITE});
}
# Alter the empty convert_table
my $alterquery = qq{
ALTER TABLE `convert_table`
MODIFY COLUMN `$primary` $type AUTO_INCREMENT,
AUTO_INCREMENT = $id
};
$sth = $dbh->prepare($alterquery);
$sth->execute or croak qq{WTF? Can't alter the convert table, cleanup needed for sure, you've got locked tables\n}.DBI->errstr;

# Switch out the .frm and .MYI files from the convert table the table we're fixing
copy(qq{/var/lib/mysql/$db/convert_table.frm},qq{/var/lib/mysql/$db/$table.frm}) or croak "Very bad, cleanup needed, you have locked tables. Copy failed: $!";
copy(qq{/var/lib/mysql/$db/convert_table.MYI},qq{/var/lib/mysql/$db/$table.MYI}) or croak "Very bad, cleanup needed, you have locked tables. Copy failed: $!";

# Just in case, Cause this is a flithy hack
$dbh->do(qq{REPAIR TABLE `$table`}) if $safe;

# Shouldn't need to do with but it's a good non-invasive test of the table
$alterquery = qq{ALTER TABLE `$table` AUTO_INCREMENT = $id };
my $sth7 = $dbh->prepare($alterquery);
$sth7->execute or croak qq{Hope you backed up, filthy hack failed. Cleanup needed for sure, you have locked tables and possible corruption, try repair with USE_FRM\n}.DBI->errstr;

# Clean up
$dbh->do(qq{DROP TABLE `convert_table`}) or croak qq{Clean up needed, you have locked tables\n}.DBI->errstr;
if ( $remove ) {
$dbh->do(qq{DROP TABLE `$table$ext`}) or croak qq{Clean up needed, you have locked tables\n}.DBI->errstr;
}
if ( $safe ) {
$dbh->do(q{UNLOCK TABLES}) or croak qq{Clean up needed, you have locked tables\n}.DBI->errstr;
}
print ".";
}

print "\nFilthy hack complete! Hope I didn't just corrupt everything\n"

Efficient Rule Based Scheduling

Recently at work we had a project to convert a number of myisam latin 1 databases to utf8. The process we settled on was to lvm snapshot the databases before we started the conversions so that if something went wrong we could mount the snapshots and restore quickly. The problem that arose from this was limited space for the snapshots. We needed to break the conversions up so that we were never converting more data than the size of the snapshots we were creating. I needed to come up with a schedule for doing the conversion, to maximize the number of conversions that could be undertaken at a time, while maintaining the rule of not allowing more data to change than the size of the snapshot.

The answer was simple enough, collect the sizes of all the databases, sort them largest to smallest and then start scheduling each round by taking the largest database off the top of the list and start adding the smallest databases from the bottom of the list until we hit our max size limit.

As per my usual, I solved this with perl. Here is what it looked like;

First lets get a list of the databases

my $dbh = DBI->connect( $dsn, $user, $password, { RaiseError => 1, AutoCommit => 0 } );
my $dbs = $dbh->selectcol_arrayref(q{SHOW DATABASES});
$dbh->disconnect;


Now lets get the sizes for each


my @dbsizes;
foreach my $db ( @$dbs ) {
next if grep /^$db$/, @skip;
opendir my $fh, "$dbpath/$db" or die $!;
my @files = readdir($fh);
# Add up the file sizes for all files in the directory
# there are no subdirectories or this would be to be recursive
my $t;
map { my @s = stat "$dbpath/$db/$_" or die $!; $t += $s[7]; } @files;
push(@dbsizes, [$t,$db]) if defined($t);
}


And now lets sort the list by database size


my @sites = sort { $a->[0] <=> $b->[0]; } @dbsizes;


Lets get to the actual scheduling. Here I'm using a recursive function that will build a reference to a hash keyed by round with an array of databases to convert.


# Recursive function, builds a schedule for database conversion so that no
# Round of conversions exceeds $max
sub build_schedule {
my ($sites,$top,$round,$total,$schedule) = @_;
$top = 1 unless defined($top);
$round = 1 unless defined($round);
$total = 0 unless defined($total);
my $next_site = $top ? shift @$sites : pop @$sites;
$top = 0;
return $schedule unless $next_site;
my ($size, $site) = @$next_site;
if (($total + $size) > $max_size_per_round || $#{$schedule->{round}} > $max_sites_per_round) {
if ($schedule->{$round}[0]) {
unshift @$sites, $next_site;
}
else {
push(@{$schedule->{$round}}, $site);
}
$total = 0;
$top = 1;
$round++;
}
else {
push(@{$schedule->{$round}}, $site);
$total += $size;
}
build_schedule($sites,$top,$round,$total,$schedule);
}


Finally we'll print the schedule

my $schedule = build_schedule(\@sites);
# Print schedule
foreach my $round ( sort { $b <=> $a } keys %$schedule ) {
print "Round $round\n\t";
print join("\n\t", @{$schedule->{$round}} );
print "\n";
}


Here is the complete script

#!/usr/bin/perl -w
use DBI;
use strict;
my $dsn = q{dbi:mysql:mysql:localhost:3306};
my $user = q{root};
my $password = q{password};
my $max_size_per_round = 4_000_000_000; # Max size for each round
my $max_sites_per_round = 5; # Max number of sites for each round
my $dbpath = q{/var/lib/mysql};
my @skip = qw{ information_schema mysql };

# Get a list of all databases on the server
my $dbh = DBI->connect( $dsn, $user, $password, { RaiseError => 1, AutoCommit => 0 } );
my $dbs = $dbh->selectcol_arrayref(q{SHOW DATABASES});
$dbh->disconnect;

# Get the size on disk for each database;
my @dbsizes;
foreach my $db ( @$dbs ) {
next if grep /^$db$/, @skip;
opendir my $fh, "$dbpath/$db" or die $!;
my @files = readdir($fh);
# Add up the file sizes for all files in the directory
# there are no subdirectories or this would be to be recursive
my $t;
map { my @s = stat "$dbpath/$db/$_" or die $!; $t += $s[7]; } @files;
push(@dbsizes, [$t,$db]) if defined($t);
}

# Sort dbs in order of largest to smallest
my @sites = sort { $a->[0] <=> $b->[0]; } @dbsizes;
my $schedule = build_schedule(\@sites);

# Print schedule
foreach my $round ( sort { $b <=> $a } keys %$schedule ) {
print "Round $round\n\t";
print join("\n\t", @{$schedule->{$round}} );
print "\n";
}


# Recursive function, builds a schedule for database conversion so that no
# Round of conversions exceeds $max
sub build_schedule {
my ($sites,$top,$round,$total,$schedule) = @_;
$top = 1 unless defined($top);
$round = 1 unless defined($round);
$total = 0 unless defined($total);
my $next_site = $top ? shift @$sites : pop @$sites;
$top = 0;
return $schedule unless $next_site;
my ($size, $site) = @$next_site;
if (($total + $size) > $max_size_per_round || $#{$schedule->{$round}} > $max_site_per_round ) {
if ($schedule->{$round}[0]) {
unshift @$sites, $next_site;
}
else {
push(@{$schedule->{$round}}, $site);
}
$total = 0;
$top = 1;
$round++;
}
else {
push(@{$schedule->{$round}}, $site);
$total += $size;
}
build_schedule($sites,$top,$round,$total,$schedule);
}