Taming the Distributed Database Problem: A Case Study Using MySQL
By Giuseppe Maxia, July 08, 2004
There are situations where data belonging to a given context are nonetheless scattered among different servers, possibly in separate locations. In such situations, gathering data together is more difficult than dealing with a unified set of tables, where all of them were under the same engine. The challenge is purely technological. It is easy to create a virtual file system, making the database engine believe that all the files are in the same machine, even though they are just a link over the network, perhaps hundreds of miles apart. However, in such a case the performance would be so unfavorable that the whole purpose of having a DBMS would be mystified. Hence, the distributed database studies, which try to find clever ways of reducing the technological gaps and make normal operations look, if not ordinary business, at least affordable.
Listing 3 find_differences.pl: Implementation of the range search
algorithm
#!/usr/bin/perl -w
# find_differences
# (C) Giuseppe Maxia 2003
#
# Compare two tables in two remote databases, looking for different records.
# Requires a "shadow table", created by make_shadow_table
#
use strict;
use DBI;
# This script is for educational purposes only.
# Parameters are embedded in the script itself.
# For production, it woyld be better to use either
# a config file or any Getopt::XX modules
my $db1 = "xcompany";
my $db2 = "xcompany";
my $host1 = "localhost";
my $host2 ="192.168.59.9";
my ( $user1, $password1 ) = ( 'myuser', 'secret' );
my ( $user2, $password2 ) = ( 'myuser', 'secret' );
my $dbh1 = DBI->connect("DBI:mysql:$db1;host=$host1",
$user1, $password1,
{RaiseError => 1, PrintError =>0})
or die "can't connect to $host1 : $DBI::errstr";
my $dbh2 = DBI->connect("DBI:mysql:$db2;host=$host2",
$user2, $password2,
{RaiseError => 1, PrintError =>0})
or die "can't connect to $host2 : $DBI::errstr";
$dbh1->do(qq{set SQL_LOG_OFF=1 }); # prevents writing on the general log file
$dbh2->do(qq{set SQL_LOG_OFF=1 }); # which can become quite big if you <BR>
# have many records
my @probes =();
my $initialprobe = qq{
SELECT
span, COUNT(*) AS cnt,
CONCAT(SUM(CONV(SUBSTRING(signature,1,8),16,10)),
SUM(CONV(SUBSTRING(signature,9,8),16,10)),
SUM(CONV(SUBSTRING(signature,17,8),16,10)),
SUM(CONV(SUBSTRING(signature,25,8),16,10))) AS sig
FROM
shadow_table
GROUP BY
span };
my $probe1 = $dbh1->selectall_arrayref($initialprobe);
my $probe2 = $dbh2->selectall_arrayref($initialprobe);
my @totest = ();
my $maxdiff = 0;
for (0.. @{$probe1} -1) {
if ($probe1->[$_]->[2] ne $probe2->[$_]->[2]) {
push @totest, $probe1->[$_]->[0];
$maxdiff += $probe1->[$_]->[1];
}
}
my $get_probe_sig = qq{SELECT signature from shadow_table where span = ?};
my $sth1 = $dbh1->prepare($get_probe_sig);
for (@totest) {
$sth1->execute($_);
my $columns = $sth1->fetchall_arrayref();
my $query = qq{SELECT PK FROM shadow_table WHERE span = $_ AND
signature NOT IN (}
. (join(",", map {"'$_->[0]'"} @$columns ))
. ")";
my $sth2 = $dbh2->prepare($query);
$sth2->execute();
while (my $different = $sth2->fetchrow_arrayref()) {
print "$different->[0]\t";
}
}
print "\n";
