Archive for October, 2008

Point-in-time recovery of MySQL with binlog and blobs: Not possible?

Monday, October 27th, 2008

How can I make point in time recovery of a mysql database? Easy! Do regulary well flushed snapshot copies of the mysql file system, and keep the binary transaction log. Then all you have to do is to recover from tape, and then spool from the snapshot to the point in time that you want, using the mysqlbinlog tool. mysqlbinlog takes the binary log and turns it into nice ascii SQL code

Now, what if you have objects in your database that is not clear text? Binary objects like for example images or, hashed or encrypted data. And yes, that is more common than you would like to think.

Some years ago, we found out the hard way that mysqlbinlog can not handle this at all. The binary data is dumped as exactly that, which renders the produced SQL code useless. The fix is of course to add –hex-blob to the tool, as mysqldump has had for ages. I found later that some had already reported this, but the MySQL developers does not seem to respond to this idea.

Just a thought: The replication code in mysql can already spool the binary log from one point to another through a master server. Would it be possible to reuse parts of this code for point in time recovery too?

New Logo

Monday, October 27th, 2008

System3^wLinpro gets a “new” logo, after its merge with Swedish Redpill. Remember “The Last Ninja”? Not much more to say, actually.

Redpill Linpro Logo System 3 logo

Today’s munin tip: Spooling a year of backlog to munin

Tuesday, October 21st, 2008

A customer asked for a graph that we didn’t have. But we had the logs, so we said “sure, no worries”, and wrapped up some perl.

The problem with spooling a backlog to munin is missing values, and what to do with them. What was the value at the actual time? This case was about members on a mailing list, and the log looked like this:

1046900665 +manual handadded@somewhere.tld
1134930282 + wantstobemember@someotherplace.tld
1135008014 -probe deadalias@somewhere.tld
1135194225 - leavesthislist@someotherplace.tld
1105605482 +manual invalid@nope.tld?

In this case, the state is static when nothing happens in the log, so we just had to do something like this: 1) Get the state right now (just count the members in the list), 2) spool the log backwards, (using /usr/bin/tac) and fill the points in time with a counter running up or down, when a user has been added or removed from the list. If there is more than 300s between each entry, fill in the last value again.

Add a progress meter, and the code looks something like this. Optimization can of course be added, like in, not running rrdtool update several hundrer thousand times. This script finishes in some 5 minutes on my workstation.

#!/usr/bin/perl -w
use strict;
my $rrd="foo.rrd";
my $log="Log";
# Remove old rrd file and create a new one
unlink ($rrd); 
system (
	"rrdtool", "create",
	"-b", "1046900655", $rrd,
	"DS:42:GAUGE:600:0:U",
	"RRA:AVERAGE:0.5:1:576",
	"RRA:MIN:0.5:1:576",
	"RRA:MAX:0.5:1:576",
	"RRA:AVERAGE:0.5:6:432",
	"RRA:MIN:0.5:6:432",
	"RRA:MAX:0.5:6:432",
	"RRA:AVERAGE:0.5:24:540",
	"RRA:MIN:0.5:24:540",
	"RRA:MAX:0.5:24:540",
	"RRA:AVERAGE:0.5:288:450",
	"RRA:MIN:0.5:288:450",
	"RRA:MAX:0.5:288:450"
	);
# Take a snapshot of the state right now
my $t=time;
my $prevtime=$t;
my $r=`munin-run something | cut -d ' ' -f 2`; chomp $r;
my $then=$r;
my $prevval=$r;
# Some helper variables
my $this;
my $what;
my %dates;
my $i=0;
# Parse the log. Using /usr/bin/tac to read it from the bottom up, to
# get latest entries first
open (TAC,"/usr/bin/tac $log|") or
	die "Unable to open a pipe from tac, $!";
while () {
	next if /\?$/;
	if ( /^(\d+) ([\\+\\-])/ ) {
	    $this=$1;
	    $what=$2;
	    # Skip values older than some 13 months
	    if ( ($t-$this) > 34214400 ) { 
		last; 
	    }
	    else {
		$what eq "+" and $r--;
		$what eq "-" and $r++;
	    }
	}
	else { next; }
	# Fill out missing 5 minutes intervals
	if ( ($prevtime-$this) > 300 ) { 
	    for ( $i=$prevtime; $i>$this; $i=$i-300 ) {
			$dates{$i}=$prevval;
		}
	}
	$dates{$this}=$r;
	$prevval=$r;
	$prevtime=$this;
}
close TAC;
# Get a nice progress meter
my $all=$t-$this; my $tmp;
print "    Filling $rrd...";
# Fill the munin rrd graph
$t++; # one-off
for my $d (sort keys %dates) {
	system ("rrdtool", "update", $rrd, "-t", "42", "$d:$dates{$d}");
	$tmp=100*($d-$this)/$all; 
	print "\\r"; printf "%*d%%",2,$tmp;
}
system ("rrdtool", "update", $rrd, "-t", "42", "$t:$then");
print "Done\\n";
#EOF

iPhone OS v2.1

Monday, October 20th, 2008

Yeah, yeah, Apple haters, I know, didn’t you just tell me.

I just upgraded my iPhone to firmware/os/whatever version 2.1. Everybody I knew with an iPhone asked why I haden’t upgraded yet. So I did. What a dissapointment! Apple has encrypted the media library hashes, and the encryption is not broken yet, so I can no longer sync my music from Linux. This means all other Linux users I know with an iPhone syncs via Windows or OSX. Loosers!

The correct thing to do is of course not to play cat and mouse with Apple by deassembling iTunes, as somebody has started doing, but write something that works instead. Luckily somebody did. dTunes and PwnPlayer does the job. Especially the latter has great potential. I know, I’ve tested it. It seems it will even be released as Free Software in the end. There is still hope.

Tom Egeland: Paktens voktere

Monday, October 20th, 2008

Tom Egeland liker visst godt å skrive bøker i stil med Dan Brown og Indiana Jones. En alibino arkeolog undersøker gamle manuskripter etter et funn fra Snorres tid på Island. Disse inneholder spor som leder til et rebusløp av en skattejakt på tvers av Islands oldsteder, Norges stavkirker, Egypts kongegraver, Vatikanets arkiver og Vinlands strender. Hakk i hæl følger noen morderiske galninger på desperat jakt etter de samme skattene. Avslutningen er Hollywood verdt, og med en ikke så rent lite svulstig epilog.

Her spares det verken på action eller vakre kvinner underveis. Det kunne blitt en rimelig bra film. Men konseptet ellers er litt opptråkket etter såpass mange bøker i samme stil. Det rakes raskt over noen hundreårs kristningshistorie, noe som er litt artig med Arn-bøkene friskt i mente. De beste delene er historiene i historien, der vi får blant annet får høre en gammel viking fortelle om livet sitt med Olav den Hellige.

Anbefales? Tja, helt greit.

Akilles’ død

Monday, October 20th, 2008

Fandorin er tilbake fra Japan. Han har med seg ninja-utdannelse(!) og en personlig livvakt av samurai-type. Rett etter hjemkomsten blir han trukket inn i etterforskningen etter den sagnomsuste general Sobolevs død, og her er det intriger og konspirasjoner til langt opp på regjeringsnivå.

Det er selvsagt litt artig med en russisk diplomat med ninja-arsenal og tilhørende akrobatikk, men det blir nesten for mye av det gode. Langt mer interessant er vrien boka tar halvveis. Da får vi nemlig møte skurken, fra oppvekst til han selvsagt møter Fandorin i finalen. Dette fungerer ganske så bra, og redder boka langt opp på godkjent nivå. Anbefales som artig tidtrøye, om ikke genial litteratur.

Varnish in EPEL – packages for Red Hat Enterprise Linux and clones

Monday, October 20th, 2008

I requested EPEL EL-4 and EL-5 tag and build for varnish on friday night. The packages will be included in the main EPEL repos at the next sync from testing, that is primo November. These packages are the same as we use in production, and should be quite ready for general usage. Packages are available at http://download.fedora.redhat.com/pub/epel/testing/ . There is a list of known bugs that will be fixed eventually in the 2.0 series at http://varnish.projects.linpro.no/wiki/TroubleLog

Please report any problems as tickets in Trac at http://varnish.projects.linpro.no/ (you’ll to register and login to file tickets). Bugs reported by RedHat’s Bugzilla will end up the same place over time, but we would prefer our the local ticket system.

Who is using Varnish in Scandinavia?

Wednesday, October 15th, 2008

Varnish is a high performance http accelerator.

As varnish-2.0 hopefully will be released in a few hours, it is time to check out who is using it again. I fixed up my small web usage toplist parsing scripts, and ran them looking for varnish. The sources are, for Norway, TNS Gallup’s top 100, for Denmark, FDIM’s top 250, and KIAIndex for Sweden. For comparison, I also used Alexa’s country lists.

Now, the results for this search are of course heavily coloured by the quality of the lists in use. TNS Gallup, FDIM, and KIAIndex’s lists are result of reported hits from customers/members. Sites that for various reasons, like, they do not want to pay for the service, do not report in, are of course not included in the lists. We know, for example that yr.no, an extremely popular weather site, uses varnish for some of their services, but is not listed. The same goes for the Pirate Bay. They are not included here. Alexa’s lists are based on the usage of their web browser tool bar. Who uses their tool bar. Most people do not, I would guess. Also, some of the numbers are old. FDIM’s last update is from August. And my parsing of KIAIndex is ugly and faulty at the best. Still, the results may give a rough hint of the usage of Varnish in Scandinavia.

Topplisten TNS Gallup (Norway)

Place   1 Varnish running on vg.no (interaktiv.vg.no) (www.vg.no) (sport.vg.no)  (atvs.vg.no) (www1.vg.no)
Place   4 Varnish running on startsiden.no (www.startsiden.no) (img.startsiden.no)
Place  10 Varnish running on aftenposten.no (www.aftenposten.no)
Place  14 Varnish running on e24.no (www.e24.no)
Place  17 Varnish running on abcnyheter.no (www.abcnyheter.no)
Place  22 Varnish running on klikk.no (www.klikk.no) (skatt.klikk.no)
Place  23 Varnish running on dn.no (multimedia.dn.no)
Place  25 Varnish running on bt.no (fotball.bt.no)
Place  27 Varnish running on adressa.no (kundeservice.adressa.no) (www.adressa.no) (fotball.adressa.no) (berlin.adressa.no)
Place  28 Varnish running on dinepenger.no (www.dinepenger.no)
Place  31 Varnish running on aftenbladet.no (fotball.aftenbladet.no)
Place  34 Varnish running on speaker.no (live.speaker.no)
Place  45 Varnish running on kvinneguiden.no (bilder.kvinneguiden.no)
Place  46 Varnish running on rb.no (tjenester.rb.no)
Place  48 Varnish running on amobil.no (www.amobil.no)
Place  54 Varnish running on idg.no (www.idg.no)
Place  62 Varnish running on tvnorge.no (limefiles.tvnorge.no) (www.tvnorge.no)
Place  63 Varnish running on budstikka.no (livefotball.budstikka.no)
Place  76 Varnish running on tu.no (www1.tu.no) (www.tu.no) (web.tu.no)
Place  79 Varnish running on rbk.no (www.rbk.no) (forum.rbk.no)
Place 101 Varnish running on dagligvarehandelen.no (www.dagligvarehandelen.no)

Comparison: .no-domains in Alexa's top list for Norwegian users

Place   2 Varnish running on http://vg.no
Place   6 Varnish running on http://startsiden.no
Place   8 Varnish running on http://aftenposten.no
Place  12 Varnish running on http://e24.no
Place  21 Varnish running on http://abcnyheter.no
Place  36 Varnish running on http://adressa.no


Foreningen af danske interaktive medier (Denmark) top 250, August numbers:

Place   4 Varnish running on dmi.dk (www.dmi.dk) (dmi.dk)
Place   7 Varnish running on ekstrabladet.dk (multimedia.ekstrabladet.dk)
Place  15 Varnish running on jp.dk (jp.dk) (multimedia.jp.dk)
Place  24 Varnish running on epn.dk (epn.dk)
Place  34 Varnish running on fpn.dk (fpn.dk)
Place  56 Varnish running on newz.dk (newz.dk)
Place 105 Varnish running on filmz.dk (filmz.dk)

Comparison: .dk-domains in Alexa's top list for Danish users

Place  13 Varnish running on http://dmi.dk
Place  17 Varnish running on http://jp.dk
Place  21 Varnish running on http://komogvind.dk
Place  28 Varnish running on http://epn.dk


KIAindex (Sweden) top 300 or something

Place  12 Varnish running on e24.se (e24.se)
Place  15 Varnish running on svd.se (svd.se)
Place  23 Varnish running on Sydsvenskan.se (Sydsvenskan.se)
Place  37 Varnish running on hd.se (websvcc.hd.se) (blogg.hd.se) (misc.hd.se) (media.hd.se) (hd.se)
Place  61 Varnish running on affarsvarlden.se (affarsvarlden.se) (www.affarsvarlden.se)
Place  68 Varnish running on nyteknik.se (www.nyteknik.se) (nyteknik.se)
Place  73 Varnish running on nwt.se (nwt.se)
Place  79 Varnish running on bt.se (www.bt.se) (bt.se)
Place  80 Varnish running on smp.se (www.smp.se) (smp.se)
Place  83 Varnish running on barometern.se (www.barometern.se) (barometern.se)
Place  85 Varnish running on norran.se (vader.norran.se) (norran.se) (m.norran.se)
Place  92 Varnish running on blt.se (www.blt.se) (blt.se)
Place 165 Varnish running on pchemma.se (pchemma.se) (www.pchemma.se)
Place 170 Varnish running on ut.se (ut.se) (www.ut.se)

.se-domains in Alexa's top list for Swedish users

Place   2 Varnish running on http://aftonbladet.se
Place  18 Varnish running on http://affarsvarlden.se
Place  20 Varnish running on http://svd.se
Place  44 Varnish running on http://sydsvenskan.se