Hallo,
ich brauche IP-accounting und mit fast jedem netfilter/iptables betreffenden Kernelupdate mus ich das veraltete/ungewartete ipac-ng irgendwie anpassen. Außerdem läßt es IPv6 Support vermissen.
Ich denke in pmacct einen würdigen Nachfolger gefunden zu haben.
Es werden ein paar Daten gesammelt und in eine Datenbank geschrieben:
[...] aggregate: src_host,dst_host,proto,src_port,dst_port interface: eth0 ports_file: /etc/pmacct/ports.list [...]
Wenn ich nun verschiedene Stellen nach dem Trafficaufkommen befrage, erhalte ich leider zu unterschiedliche Angaben:
[...] ns1:/tmp# sudo -u postgres psql -d pmacct -c "SELECT SUM(bytes) FROM acct WHERE stamp_updated >= '2014-12-23 00:00:00' AND stamp_updated <= '2014-12-23 01:00:00'" sum ------------ 1995191440 (1 Zeile) [...] ns1:/tmp# ipacsum -t "the hour 2 hours ago" IP accounting summary Host: ns1 / Time created: 2014/12/23 02:11:48 CET Data from 2014/12/23 00:00:00 CET to 2014/12/23 00:59:59 CET all_in : 37M all_out : 46M [...] 2a01:*:*:*::/64 0,007 0,043 0,050 78.*.*.* ns1 0,010 0,026 0,036 Gesamt: 0,017 0,069 0,086 [...]
In der Datenbank sind knapp 2gb aufkumuliert, ipac-ng hat auf IPv4 nur 83mb gesehen und beim Serverhoster sind es auf beiden IP-Versionen keine ca. 86mb. Jetzt stellt sich mir die Frage, warum weicht der von pmacct gezählte Datenverkehr so gewaltig von der übereinstimmenden Meinung der beiden anderen Zähler ab?
Mit freundlichen Grüßen / Kind regards Ronny Seffner -- Ronny Seffner | Alter Viehweg 1 | 01665 Klipphausen www.seffner.de | ronny@seffner.de | +49 35245 72950 7EA62E22D9CC4F0B74DCBCEA864623A568694DB8
Ronny Seffner ronny@seffner.de wrote:
Wenn ich nun verschiedene Stellen nach dem Trafficaufkommen befrage, erhalte ich leider zu unterschiedliche Angaben:
[...] ns1:/tmp# sudo -u postgres psql -d pmacct -c "SELECT SUM(bytes) FROM acct WHERE stamp_updated >= '2014-12-23 00:00:00' AND stamp_updated <= '2014-12-23 01:00:00'"
Gemäß http://www.nwlab.net/tutorials/pmacctd/pmacct.html hat die Tabelle neben stamp_updated auch noch stamp_inserted. Ich weiß nicht, wann stamp_updated gesetzt wird und auch nicht auf was, vermute aber, Du solltest eher auf stamp_inserted aggregieren.
Andreas
Hallo Gruppe, Hallo Andreas,
wiedermal saß der Fehler vorm Bildschirm. 'pmacct' zählt richtig, alle anderen nicht. Die Auflösung: 'ipac-ng' schaut sich "nur" IPv4 an und da läuft nachts ja noch ein Backup ins RZ über - oh staune IPv6. Der Hoster ist so gütig mir traffic in seinen Netzen nicht zu berechnen und zeigt ihn mir deshalb auch nicht an. Nun habe ich gelernt, dass 'obnam' nicht mehr in einer Stunde fertig ist und über 20gb vom Backupziel pro Sicherung einliest.
Nun sammeln sich die Daten und machen vermutlich absehbar die Datenbank unbedienbar. 'ipac-ng' hatte da eine super Funktion "-r : replace", die Daten zusammengefasst hat. So habe ich Daten alle Minute gesammelt, aller Stunden, Tage, Wochen und Monate aber zusammengefasst. Sowas hätte ich jetzt gern wieder um der Millionen Zeilen Herr zu werden. 'pmacct' selbst kann das offenbar nicht, nur in täglich neue Tabellen schreiben was die Datenmenge ja nicht minimiert.
Jetzt braucht es wohl intelligente und performante SQL-Syntax das für mich zu erledigen - ein Wunder, dass es sowas offenbar noch nicht fertig gibt. Ich habe die Spalten ip_src, ip_dst, ports_src, port_dst, bytes sowie inserted (vereinfacht). Nun sollen alle Zeilen, deren 4 erste Spalten identisch sind und deren 'inserted' innerhalb einer Stunde liegen zu einer Zeile mit summierten 'bytes' zusammengefasst werden. Nur muss man dazu immer wieder iterativ über die gesamte DB rennen, was mir Sorgen bereitet.
Hier die aktuelle Tabelle:
CREATE TABLE acct ( mac_src macaddr NOT NULL DEFAULT '0:0:0:0:0:0', mac_dst macaddr NOT NULL DEFAULT '0:0:0:0:0:0', ip_src inet NOT NULL DEFAULT '0.0.0.0', ip_dst inet NOT NULL DEFAULT '0.0.0.0', port_src INT NOT NULL DEFAULT 0, port_dst INT NOT NULL DEFAULT 0, ip_proto SMALLINT NOT NULL DEFAULT 0, packets BIGINT NOT NULL, bytes BIGINT NOT NULL, stamp_inserted timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP(0), stamp_updated timestamp without time zone, CONSTRAINT acct_pk PRIMARY KEY (mac_src, mac_dst, ip_src, ip_dst, port_src, port_dst, ip_proto, stamp_inserted) );
Ich möchte da so wenig wie möglich keys drin haben, da die die INSERTS und UPDATES durch 'pmacct' sicher nur verlangsamen - kann man da welche entfernen?
Und wie komme ich jetzt am Besten zu meiner Datenzusammenführung?
neben stamp_updated auch noch stamp_inserted. Ich weiß nicht, wann stamp_updated gesetzt wird und auch nicht auf was, vermute aber, Du solltest eher auf stamp_inserted aggregieren.
Ja, wenn die Konfiguration - und dort konkret sql_refresh_time und sql_history - zusammenpassen, dann ist 'stamp_insterted' wohl das Mittel der Wahl.
Mit freundlichen Grüßen / Kind regards Ronny Seffner -- Ronny Seffner | Alter Viehweg 1 | 01665 Klipphausen www.seffner.de | ronny@seffner.de | +49 35245 72950 7EA62E22D9CC4F0B74DCBCEA864623A568694DB8
Ronny Seffner ronny@seffner.de wrote:
Jetzt braucht es wohl intelligente und performante SQL-Syntax das für mich zu erledigen - ein Wunder, dass es sowas offenbar noch nicht fertig gibt. Ich habe die Spalten ip_src, ip_dst, ports_src, port_dst, bytes sowie inserted (vereinfacht). Nun sollen alle Zeilen, deren 4 erste Spalten identisch sind und deren 'inserted' innerhalb einer Stunde liegen zu einer Zeile mit summierten 'bytes' zusammengefasst werden. Nur muss man dazu immer wieder iterativ über die gesamte DB rennen, was mir Sorgen bereitet.
Warum interaktiv? Das geht doch in einem Rutsch. Und sollen die Daten aus der Tabelle entfernt werden?
Angenommen, Deine Tabelle sieht so aus:
test=*# select * from ronny ; s1 | s2 | ts | val ----+----+---------------------+----- 1 | 2 | 2014-12-23 10:00:01 | 10 1 | 2 | 2014-12-23 12:00:01 | 12 1 | 2 | 2014-12-23 13:00:03 | 22 1 | 2 | 2014-12-23 13:00:04 | 23 (4 rows)
Du kannst nun das rauslöschen und aggregieren und auch noch das Einfügen in eine aggregierte Tabelle in einem Rutsch machen:
test=*# with move as (delete from ronny returning *) select s1, s2, date_trunc('hour',ts), sum(val) from ronny group by s1, s2, date_trunc('hour',ts); s1 | s2 | date_trunc | sum ----+----+---------------------+----- 1 | 2 | 2014-12-23 10:00:00 | 10 1 | 2 | 2014-12-23 12:00:00 | 12 1 | 2 | 2014-12-23 13:00:00 | 45 (3 rows)
Hier ist jetzt noch nicht das Einfügen mit drin - das Insert kannst Du aber vor ads Select machen. Deine Ursprungstabelle ist dann um die aggregierten Zeilen gekürzt - hier alle, da keine Where-Condition da war.
Das geht so ab 9.2, glaube ich. (nennt sich writeable Common Table Expression, also daß man mit WITH (...) so arbeiten kann, das kann man noch beliebig verschachteln. Am Ende ist das ein atomarer Befehl, eine einzige Transaktion.
Ich möchte da so wenig wie möglich keys drin haben, da die die INSERTS und UPDATES durch 'pmacct' sicher nur verlangsamen - kann man da welche entfernen?
Depends. Wenn der PK woanders als Foreign Key dienen sollte dann eher nicht. Hinreichend aktuelle Versionen zeigen dies an:
test=*# create table master (id int primary key); CREATE TABLE Time: 51,789 ms test=*# \d master Table "public.master" Column | Type | Modifiers --------+---------+----------- id | integer | not null Indexes: "master_pkey" PRIMARY KEY, btree (id)
test=*# create table slave( m_id int references master); CREATE TABLE Time: 41,206 ms test=*# \d master Table "public.master" Column | Type | Modifiers --------+---------+----------- id | integer | not null Indexes: "master_pkey" PRIMARY KEY, btree (id) Referenced by: TABLE "slave" CONSTRAINT "slave_m_id_fkey" FOREIGN KEY (m_id) REFERENCES master(id)
Wie man sieht, vor Erstellung der slave-Tabelle ist kein Referenced by, erst nach Erstellung der slave-Tabelle.
Das sind alles so Dinge, die bei PG halt cool sind ...
Fragen? Fragen!
Grüße ins Nachbardorf ;-)
Andreas
Hi Andreas,
irgendwann musste mir Dein pgsql-Wissen mal nützlich werden. Die Fähigkeiten hier (im RDBMS) sind ja der Wahnsinn ;-)
test=*# with move as (delete from ronny returning *) select s1, s2, date_trunc('hour',ts), sum(val) from ronny group by s1, s2, date_trunc('hour',ts);
Habe ich mal überführt zu:
mac_src | mac_dst | ip_src | ip_dst | port_src | port_dst | ip_proto | packets | bytes | stamp_inserted | stamp_updated -------------------+-------------------+---------+---------+----------+----- -----+----------+-----+-----+---------------------+--------------------- 00:00:00:00:00:00 | 00:00:00:00:00:00 | 1.1.1.1 | 2.2.2.2 | 1 | 2 | 0 | 1 | 100 | 2014-12-23 17:01:00 | 2014-12-23 17:07:00 00:00:00:00:00:00 | 00:00:00:00:00:00 | 1.1.1.1 | 2.2.2.2 | 1 | 3 | 0 | 2 | 200 | 2014-12-23 16:02:00 | 2014-12-23 16:12:00 00:00:00:00:00:00 | 00:00:00:00:00:00 | 1.1.1.1 | 3.3.3.3 | 1 | 2 | 0 | 3 | 300 | 2014-12-23 16:03:00 | 2014-12-23 16:22:00 00:00:00:00:00:00 | 00:00:00:00:00:00 | 1.1.1.1 | 2.2.2.2 | 1 | 2 | 0 | 7 | 700 | 2014-12-23 17:04:00 | 2014-12-23 17:13:00 00:00:00:00:00:00 | 00:00:00:00:00:00 | 1.1.1.1 | 3.3.3.3 | 1 | 2 | 0 | 3 | 300 | 2014-12-23 16:05:00 | 2014-12-23 16:05:08
with move as (delete from acct returning *) select mac_src, mac_dst, ip_src, ip_dst, port_src, port_dst, ip_proto, sum(packets), sum(bytes), date_trunc('hour',stamp_inserted) from acct group by mac_src, mac_dst, ip_src, ip_dst, port_src, port_dst, ip_proto, date_trunc('hour',stamp_inserted);
Das führt zu:
mac_src | mac_dst | ip_src | ip_dst | port_src | port_dst | ip_proto | sum | sum | date_trunc -------------------+-------------------+---------+---------+----------+----- -----+----------+-----+-----+--------------------- 00:00:00:00:00:00 | 00:00:00:00:00:00 | 1.1.1.1 | 3.3.3.3 | 1 | 2 | 0 | 6 | 600 | 2014-12-23 16:00:00 00:00:00:00:00:00 | 00:00:00:00:00:00 | 1.1.1.1 | 2.2.2.2 | 1 | 3 | 0 | 2 | 200 | 2014-12-23 16:00:00 00:00:00:00:00:00 | 00:00:00:00:00:00 | 1.1.1.1 | 2.2.2.2 | 1 | 2 | 0 | 8 | 800 | 2014-12-23 17:00:00
Hier ist jetzt noch nicht das Einfügen mit drin - das Insert kannst Du aber vor ads Select machen. Deine Ursprungstabelle ist dann um die aggregierten Zeilen gekürzt - hier alle, da keine Where-Condition da war.
Zuerst das WHERE. Wenn mich nur die 16 Uhr Einträge interessieren:
with move as (delete from acct where date_trunc('hour',stamp_inserted) = '2014-12-23 16:00:00' returning *) select mac_src, mac_dst, ip_src, ip_dst, port_src, port_dst, ip_proto, sum(packets), sum(bytes), date_trunc('hour',stamp_inserted) from acct where date_trunc('hour',stamp_inserted) = '2014-12-23 16:00:00' group by mac_src, mac_dst, ip_src, ip_dst, port_src, port_dst, ip_proto, date_trunc('hour',stamp_inserted);
Und nun das INSERT dazu, weil ich die Daten ja zusammengeführt weiter nutzen will. Bis hier her hab ich die vorgekaute Syntax sogar verstanden, aber jetzt ist mir die Platzierung des INSERT nicht so ganz klar? Und die Spalte stamp_updated kann leer bleiben.? Das Ergebnis hat in den manipulierten Spalten (siehe oben) nun andere Bezeichner, das macht dem korrekten INSERT ja aber sicher nichts. Auf jeden Fall gibts einen Syntaxfehler bei "moved" ;-(
with move as (delete from acct where date_trunc('hour',stamp_inserted) = '2014-12-23 16:00:00' returning *) moved as (insert into acct (select * from move)) select mac_src, mac_dst, ip_src, ip_dst, port_src, port_dst, ip_proto, sum(packets), sum(bytes), date_trunc('hour',stamp_inserted) from acct where date_trunc('hour',stamp_inserted) = '2014-12-23 16:00:00' group by mac_src, mac_dst, ip_src, ip_dst, port_src, port_dst, ip_proto, date_trunc('hour',stamp_inserted);
Die wiederholten date_trunc() und WHERE kostet nicht unnötig Ressourcen?
Depends. Wenn der PK woanders als Foreign Key dienen sollte dann eher nicht. Hinreichend aktuelle Versionen zeigen dies an:
v9.1.14 und kein "Referenced by". Was meinst Du zu den Indizes? Geschrieben in die DB wird alle 5min, den Job zum Zusammenführen lass ich 1x stündlich laufen, wirklich reingucken in die Daten wird man zwei drei Mal im Monat.
Das sind alles so Dinge, die bei PG halt cool sind ...
Offenbar.
Mit freundlichen Grüßen / Kind regards Ronny Seffner -- Ronny Seffner | Alter Viehweg 1 | 01665 Klipphausen www.seffner.de | ronny@seffner.de | +49 35245 72950 7EA62E22D9CC4F0B74DCBCEA864623A568694DB8
Ronny Seffner ronny@seffner.de wrote:
Hier ist jetzt noch nicht das Einfügen mit drin - das Insert kannst Du aber vor ads Select machen. Deine Ursprungstabelle ist dann um die aggregierten Zeilen gekürzt - hier alle, da keine Where-Condition da war.
Zuerst das WHERE. Wenn mich nur die 16 Uhr Einträge interessieren:
with move as (delete from acct where date_trunc('hour',stamp_inserted) = '2014-12-23 16:00:00' returning *) select mac_src, mac_dst, ip_src, ip_dst, port_src, port_dst, ip_proto, sum(packets), sum(bytes), date_trunc('hour',stamp_inserted) from acct where date_trunc('hour',stamp_inserted) = '2014-12-23 16:00:00' group by mac_src, mac_dst, ip_src, ip_dst, port_src, port_dst, ip_proto, date_trunc('hour',stamp_inserted);
Und nun das INSERT dazu, weil ich die Daten ja zusammengeführt weiter nutzen will. Bis hier her hab ich die vorgekaute Syntax sogar verstanden, aber
test=# select * from ronny; s1 | s2 | ts | val ----+----+---------------------+----- 1 | 2 | 2014-12-23 10:00:01 | 10 1 | 2 | 2014-12-23 12:00:01 | 12 1 | 2 | 2014-12-23 13:00:03 | 22 (3 rows)
Time: 0,189 ms test=*# create table ronny_ziel (s1 int, s2 int, ts timestamp, val int); CREATE TABLE Time: 15,266 ms test=*# with move as (delete from ronny returning *) insert into ronny_ziel select s1, s2, date_trunc('hour',ts), sum(val) from ronny group by s1, s2, date_trunc('hour',ts); INSERT 0 3 Time: 0,621 ms test=*# select * from ronny; s1 | s2 | ts | val ----+----+----+----- (0 rows)
Time: 0,144 ms test=*# select * from ronny_ziel ; s1 | s2 | ts | val ----+----+---------------------+----- 1 | 2 | 2014-12-23 10:00:00 | 10 1 | 2 | 2014-12-23 12:00:00 | 12 1 | 2 | 2014-12-23 13:00:00 | 22 (3 rows)
Da hab ich das Insert in die ronny_ziel mit eingebaut.
Die wiederholten date_trunc() und WHERE kostet nicht unnötig Ressourcen?
Nö.
Depends. Wenn der PK woanders als Foreign Key dienen sollte dann eher nicht. Hinreichend aktuelle Versionen zeigen dies an:
v9.1.14 und kein "Referenced by". Was meinst Du zu den Indizes? Geschrieben
Ich hab grad noch mal geschaut, die Syntax mit dem WITH und der Weiterverarbeitung von dem, was da rauskommt (nennt sich writeable Common Table Expressions) wurde in 9.1 eingeführt, das sollte also gehen.
Die Ausgabe in psql, welche Tabellen auf diese referenzieren, kam aber glaube ich erst später, dabin ich mir nicht sicher.
in die DB wird alle 5min, den Job zum Zusammenführen lass ich 1x stündlich laufen, wirklich reingucken in die Daten wird man zwei drei Mal im Monat.
Ach gott, aller 5 Minuten schreiben ist ja keine Last. Laß den Index halt, wenn Du Dir nicht sicher bist.
Das sind alles so Dinge, die bei PG halt cool sind ...
Offenbar.
PG kann Dinge wo auch Oracle alt aussieht. DDL in Transaktionen kann Oracle erst seit kurzem, PG schon immer. Exclusion Constraints kann keine andere DB.
Andreas
Andreas Kretschmer akretschmer@spamfence.net wrote:
Zuerst das WHERE. Wenn mich nur die 16 Uhr Einträge interessieren:
with move as (delete from acct where date_trunc('hour',stamp_inserted) = '2014-12-23 16:00:00' returning *) select mac_src, mac_dst, ip_src, ip_dst, port_src, port_dst, ip_proto, sum(packets), sum(bytes), date_trunc('hour',stamp_inserted) from acct where date_trunc('hour',stamp_inserted) = '2014-12-23 16:00:00' group by mac_src, mac_dst, ip_src, ip_dst, port_src, port_dst, ip_proto, date_trunc('hour',stamp_inserted);
sorry, das war mein Fehler. Das mit der WITH - Syntax geht so:
with xxx as (... returning *) select ... from xxx
Ich hatte, das war mein Fehler, den Du brav übernommen hast, im select wieder die originale Tabelle, nicht das, was via WITH xxx in der nun temp. Tabelle xxx steht. Siehst noch durch?
Passiert halt, wenn es schnell gehen soll...
Du kannst auch solche Dinge damit machen:
with x1 as (select ....), x2 as (delete from ... returning *), x3 as (update ... ) select * from x1 left join x2 on ... left join x3 on ...
Oder, was auch sehr nützlich ist, bei Inserts in Tabellen, wo ein Key generiert wird, den Du im zweiten Insert dann brauchst. Läßt sich so alles in einem Befehl schreiben, damit nur ein mal Applikation -> DB, nur ein mal für die DB einen Plan erstellen etc. kann durchaus Performance bringen.
Andreas
Hallo Andreas,
ich habs jetzt verstanden und aggregiere meine Tabelle stündlich und täglich per cronjob. Danke.
Nun interessieren mich Auswertungen und ich fange an postgress eingebaute Funktionen zu lieben: so stehen in der Tabelle mit Typ 'inet' Adressen wie 1.2.3.5 und ich kann jetzt im SELECT einen Vergleich mit einem Netz fahren (... WHERE ip_src <<= 1.2.3.4/27 ...). Das ist der Hammer.
Zur Vollständigkeit, ich hab mich wohl noch nie damit befasst, was die RDBMS so bieten: unter MySQL gibt es wohl inet_aton und inet_nota um platzsparend mit IP-Adressen umzugehen, da dabei aus den Adressen ein nummerischer Wert wird könnte man auch hier relativ bequem in Bereichen suchen. MySQL unterscheidet aber IPv4 und IPv6 soweit, dass unterschiedliche Funktionen nötig sind -> unnötig kompliziert, wie postgresql zeigt.
Mit freundlichen Grüßen / Kind regards Ronny Seffner -- Ronny Seffner | Alter Viehweg 1 | 01665 Klipphausen www.seffner.de | ronny@seffner.de | +49 35245 72950 7EA62E22D9CC4F0B74DCBCEA864623A568694DB8
Ronny Seffner ronny@seffner.de wrote:
Hallo Andreas,
ich habs jetzt verstanden und aggregiere meine Tabelle stündlich und täglich per cronjob. Danke.
Nun interessieren mich Auswertungen und ich fange an postgress eingebaute Funktionen zu lieben: so stehen in der Tabelle mit Typ 'inet' Adressen wie 1.2.3.5 und ich kann jetzt im SELECT einen Vergleich mit einem Netz fahren (... WHERE ip_src <<= 1.2.3.4/27 ...). Das ist der Hammer.
Nein, PostgreSQL ;-)
Ich bin ja prinzipiell der Meinung, daß es keinen Sinn ergibt, bei Datenbanken plattformunabhängig zu programmieren. Also daß eine Anwendung z.B. mit MySQL UND(!) PostgreSQL funktioniert. Sobald man dies versucht ist man gezwungen, den 'kleinsten gemeinsammen Nenner' zu finden und wirft dabei ALLE Vorteile der jeweiligen DB übern Haufen. Gut, bei MySQL bleibt das überschaubar...
Andreas
lug-dd@mailman.schlittermann.de
-
Andreas Kretschmer -
Ronny Seffner