Hallo,
Ronny Seffner wrote:
Hallo,
ich habe regelmäßig (täglich) ein paar Daten aus einer proprietären Software anfallenden Textdateien in eine MySQL Datenbank zu packen. Als Admin kann ich mir mit BASH und MySQL ein wenig helfen, Programmierer bin ich damit nicht. Nun fällt erschreckend auf, wie langsam das Ganze ist und ich hoffe man kann mir hier helfen (im Zweifel dann auch bezahlt). Aber erstmal zu den Fakten:
So sieht die Datei aus (es sind dann Dutzende dieser Dateien, mit je Tausenden Zeilen):
03.05.2012 00:00:25 0,03296 03.05.2012 00:00:25 0,03296 03.05.2012 00:00:25 0,03296 03.05.2012 00:00:25 0,03296 03.05.2012 00:00:25 0,03296 03.05.2012 00:00:25 0,03296 03.05.2012 00:00:26 0,03296 03.05.2012 00:00:26 0,03296 03.05.2012 00:00:26 0,03296 03.05.2012 00:00:26 0,03296 03.05.2012 00:00:26 0,03296 03.05.2012 00:01:44 0,04413 03.05.2012 00:01:45 0,42134 03.05.2012 00:01:46 0,39369 03.05.2012 00:01:47 0,44423 03.05.2012 00:01:48 0,54073 03.05.2012 00:01:49 0,56819 03.05.2012 00:01:50 0,61855
OK, also der Timestamp mit einem Leerzeichen getrennt, und dann mit einem Tab getrennt davon der eigentliche Datensatz.
Sind mehrere Zeitstempel identisch, soll der _LETZTE_ Wert genommen werden.
Das ruft ein wenig nach einem Hash und damit eher nach perl als bash...
Also zum Beispiel:
#!/usr/bin/perl use strict; # das DBI ist für später nötig use DBI; my $data; # das Datenfile wird per Kommandozeile an das Skript übergeben my $datafile = $ARGV[0]; die "Usage: $0 <datafile>" unless -f $datafile; # jetzt das Dateinfile einlesen open(IN,"<",$datafile) or die "Can't open $datafile for reading: $!"; while (my $line = <IN>) { chomp($line); my ($timestamp, $value) = split /\t/,$line; $data->{$timestamp} = $value; } close(IN);
# und ab jetzt sind alle Daten in einem Hash, auf den die # Referenz $data existiert, und es ist auch immer nur der letzte # Eintrag pro Zeitstempel pro Datei im Hash, da ein doppelter # Eintrag immer vom nächsten überschrieben wird
Das ist meine Tabelle:
CREATE TABLE IF NOT EXISTS `test` ( `timestamp` datetime NOT NULL, `value` decimal(10,5) NOT NULL, PRIMARY KEY (`timestamp`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Ich vermute schon hier unüberlegt strukturiert zu haben.
Kommt darauf an, erstmal sieht es naheliegend aus.
Und hier nun mein primitives Skript, welches auf einer Core i3 3GHz Maschine mit 8gb RAM so 20 bis 30 Zeilen in der Sekunde ins MySQL schickt:
cat "$FILE" | while read ENTRY; do YEAR=`echo "$ENTRY" | awk '{print $1}' | awk -F. '{print $3}'` MONTH=`echo "$ENTRY" | awk '{print $1}' | awk -F. '{print $2}'` DAY=`echo "$ENTRY" | awk '{print $1}' | awk -F. '{print $1}'` TIMESTAMP=`echo "$ENTRY" | awk '{print $2}'` NEWDATE="$YEAR-$MONTH-$DAY $TIMESTAMP" # VALUE=`echo "$ENTRY" | awk '{ print $3 }' | sed -e 's/,/./g'` VALUE=`echo "$ENTRY" | awk '{ print $3 }'` SQL="INSERT INTO `test` (`timestamp`, `value`) VALUES ('$NEWDATE', '$VALUE') ON DUPLICATE KEY UPDATE `timestamp`= '$NEWDATE', value = '$VALUE';" mysql machines -uroot -p$SQLPWD -e "$SQL" done
Der Fehler hier ist, dass du pro SQL-Statement mysql neu aufrufst, also jedesmal den Overhead von Verbindungsaufbau, Userauthentifizierung und Verbindungsabbau in Kauf nimmst. Den solltest du minimieren. Die paar tausend sed und awk würden sich in perl auch erübrigen. Bei obigem perl-Schnippsel am Anfang würde das also zum Beispiel so weitergehen:
my @sqlstatements; foreach my $timestamp (keys %$data) { my ($date, $time) = split / /, $timestamp; my ($day, $month, $year) = split /./, $date; my $newtimestamp = "${year}-${month}-$day $time"; my $value = $data->{$timestamp}; $value =~ s/,/./; push @sqlstatements, "INSERT INTO `test` (`timestamp`, `value`) VALUES ('$newtimestamp', '$value') ON DUPLICATE KEY UPDATE `timestamp`= '$newtimestamp', value = '$value';"; }
# die SQL-Statements werden also hier erstmal in ein Array # gepackt. Das kann dann nachher am Stück abgearbeitet werden. # Alles nur der übersichtlichkeit halber für diese Mail, # normalerweise könnte man in der Schleife schon das Statement # ausführen
Ich denke hier jedes Mal mehrere awk und sed zu starten nur um das unglückliche Datumsformat zu verändern muss nicht sein. Leider kann ich kein perl, das soll ja Wunder wirken ...
Siehe oben, alles in ein paar Variablen packen und gut, der Overhead für neue Prozesse (fork() und exec()) entfällt also erstmal.
Aber auch ohne die Datumskonvertierung ist das alles nicht wirklich schnell. Die my.cnf ist noch UBUNTU Standard und vielleicht sind so viele kleine INSERTS auch gar nicht wirklich gut?
Das Problem sind die vielen einzelen "Verbindung aufbauen, User authentifizieren, am Ende Verbindung abbauen" für jedes Statement.
Daher jetzt also der letzte Schritt im perl-Skript, die Kommunikation mit der Datenbank:
my $database = "machines"; my $host = "localhost"; my $port = "3128"; my $user = "root"; my $password = "wasauchimmer"; my $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port"; my $dbh = DBI->connect($dsn, $user, $password); foreach my $sql (@sqlstatements) { $dbh->do($sql); } $dbh->disconnect();
# Fertig. :-)
Wenn du bei bash statt perl bleiben willst, dann gib in deiner Schleife alle SQL-Statements nur aus (mit echo) und pipe das Ganze in ein einzelnes mysql - das beschleunigt die Sache deutlich.
Wenn du wirklich viele Statements hast, hilft es noch, prepared Statements zu verwenden. Aber das ist ein Thema für jemdanden, der mehr mit mysql macht als ich :-)
Ciao, Thomas