abfragebeschleunigung mit paralleltabellen?

Ich habe ein Zeitproblem beim Zugriff auf viele Einträge einer grossen Tabelle.

Ausgangsbasis:

Ich möchte alle Hostnamen suchen, die auf einer IP liegen - das ganze in einer Tabelle mit ca. 25 Millionen Hostname-Einträgen, die (etwa) so aussieht

table names
 id
 hostname
 ... (einiges mehr)

dazu habe ich eine zweite tabelle

table ip_to_hostname
 ip_id
 names_id



jetzt habe ich z.B. für eine IP 8.000 Hostnamen in der Tabelle, der query sieht dann (aufgelöst) so aus:

select hostname from names where id in (a,b,c,d,… (8.000 ids, nicht fortlaufend, sondern bunt gemischt!))

Der Zugriff auf die indizierten IP- und ip_to_hostname-tabellen ist eine Sache von Millisekunden (zumindest sagt das der Analyzer, wenn ich mir das EXPLAINen lasse).
Weil aber die Tabelle Names sehr gross ist und die Hostnamen überall verteilt sind, dauert die Abfrage der Hostnamen aus NAMES sehr lang, je nach Zahl der aufzurufenden Hostnamen bis zu 30 Sekunden. Logisch eigentlich, die Namen sind über die ganze Tabelle verteilt, die alleine schon fast ein Gigabyte belegt, Postgresql kommt bei Tausenden von Hostnamen nur mit viel Glück drum herum, grosse Teile der Tabelle von der Festplatte einzulesen, um an die Namen heranzukommen.

Ein direkter, geclusterter Index ist aus diversen Gründen in der Tabelle “Names” nicht möglich.

Meine Idee wäre jetzt schlicht, eine “Hilfstabelle” (als read only) zu erstellen, die einer Kopie der Names-Tabelle ist, die über Trigger zusammen mit der Tabelle Tables aktualisiert wird und jede Nacht mit einem geclusterten Index ausgestattet wird.
Abfragen nach “hostname” würden dann auf die Hilfstabelle gehen und aufgrund des Clusterings von wenigen Blocks der Festplatte gelesen - damit würden die Zugriffe auf die (dann fortlaufend sortierten) IDs beschleunigt und die Festplatte entlastet.

Ist das ein gangbarer Weg??
Gibt es Alternativen, den Zugriff auf viele Records einer sehr grossen Tabelle zu beschleunigen?
Wie gehen andere mit einem solchem “Lastproblem” um?

Als “Einfachalternative” (ohne Trigger und Funktionen - damit steh ich (noch) ziemlich hilflos da) schwebt mir derzeit auch ein nächtlicher Export in eine Textdatei vor, die dann mit Glimpse indiziert und abgefragt wird - aber das widerspricht irgendwie zumindest teilweise dem Sinn einer Datenbank.

Für Hints (und Lesehinweise) dankbar,

Thomas

Da dein Projekt anscheinend etwas größer ist, kann dir wohl niemand aus dem Stehgreif sagen, was gut und was schlecht ist in einem konkreten Fall.

Hast du denn die zweite Tabelle schon mal getestet, ob und wie performant die von dir gedachte Lösung wird? Du kannst unter Umständen (genaueres müsste ich jetzt erst in der Doku nachschlagen) sogar mit Hilfe einer Rule die Anfragen nach deiner Originaltabelle im Hintergrund auf deine Hilfstabelle umlenken. Ob das praktikabel und im konkreten Fall brauchbar ist, sei aber dahingestellt.

Ich hab heute (für eine spezifische Abfrage) eine solche Hilfstabelle mal über ein “select into” erstellt - das dauert erstaunlicherweise samt geclusterten Index nur ein paar Minuten und wird sich auch noch einige Zeit im Speicher abwickeln lassen (frisst derzeit etwa 1,3 GB RAM), läßt sich also ohne Probleme einmal pro Nacht durchführen.

Ich bin dann doch immer wieder fasziniert, wie schnell PGSql komplexe Queries abarbeiten. Die Abfrage ist dann eine Sache von Millisekunden, d.h. die Geschwindigkeit ist um den Faktor 100 schneller.

Jetz muss ich nur noch denn allerersten Trigger meiner SQL-Laufbahn erstellen, um diese Tabelle in Echtzeit upzudaten, wenn sich die Quellen ändern.

Manchmal hab ich das Gefühl, ich werd zu alt für diesen Kram. :slight_smile:
Ich such immer noch einen SQL-Künstler, der mir das alles for fame only abnimmt.

tom

For fame only, so so … letztendlich kommt aber nicht viel fame bei herum, oder? :wink:

Ist es denn sinnvoll, das du bei jeder Änderung die Tabelle neu schreibst? Nicht das sich zwei “Neuschreiben” Prozesse überschneiden, wenn die Änderungen zu schnell aufeinander folgen.
Auf der anderen Seite kannst du den Trigger auch pro Datensatz ansetzen und den Wert einfach zusätzlich in die zweite Tabelle kopieren.

So etwas ähnliches erledigt btw. mein tablelog Projekt auch, nur mit etwas mehr Infos dazu.

Kennst du einen Datenbankentwickler oder SQL-Wizard, der famous wurde?

Auch mit einer “Datenbank des Internet”, an bzw. mit der ich arbeite, wirst du vermutlich nicht wirklich berühmt werden :slight_smile:

Aber hey - ich habe das Projekt mit diesem Ansatz gestartet:
We started a project called “Map the Net” recently: we created a crawlerscript thats mangling it’s way through the global network and tries to get as many domains, ips and hostnames as it can get.

The results get stored - you guessed it - in a huge database, and will make us very, very famous and rich somewhere in time.

Das Problem ist: Wenn jemand infos über ein IP abruft, startet das im Hintergrund eine Crawlersession und es fliessen eine Menge neuer Datensätze in die Datenbank - die dann natürlich auch sofort zur verfügung stehen sollten - d.h. es sollte schon ein Trigger da sein, der zumindest neue Datensätze in der Haupttabelle sofort per insert auch in die Hilfstabelle schaufelt.
Wenn man dann noch einmal pro Nacht den Index neu clustert, sollte eigentlich alles gut werden und auch die Abfrage von IPs mit >100.000 hostnamen ohne php-timeout funktionieren…

Die dynamische Umleitung der Abfrage brauchts gar nicht - der Vorteil einer Hilfstabelle ist, dass ich darin auf die Normalisierung pfeiffen kann und alle vom Query benötigten Daten in eine Row schreiben kann - damit spare ich mir den Join im Query und die Abfrage wird noch schneller (und weniger rechenzeitintensiv).

Solange der Plattenplatz noch reicht, ist das eine prima Alternative, sofern der Prozessor mitmacht - ich kämpfe schon jetzt damit, dass Inserts eine hohe Prozessorlast verursachen und damit die Erweiterung der Datenbasis ein wenig gebremst wird.

Jetz bräucht ich nur noch einen Text, der mir die Umsetzung von Triggern vernünftig erklärt… :slight_smile: [/i]