Transaktionen werden laufend langsamer

Das war ein VACUUM FULL oder ein VACUUM?

Auf alle Fälle hat die Datenbank 700k Zeilen entfernt und knapp eine Million Zeilen könnten entfernt werden, wenn sie nicht noch irgendwo gebracht werden - z. B. durch offene Transaktionen.

Wenn deine Tabelle in dem Fall nur 300 Zeilen hat, bedeutet das, dass du 5.000 mal so viel Platz verbraucht hast wie eigentlich notwendig.



Danach steht in pg_stat_user_table für diese Tabelle:

n_live_tup: 942172

select count(*) ergibt aber nur 308 Datensätze.

Was hat das zu bedeuten?

Das bedeutet, dass irgendwas gar nicht in Ordnung ist. Normalerweise räumt Autovacuum so etwas auf, wenn das ausgeschaltet ist, hast du schon mal ein Problem.


Was sagt denn:

SELECT pg_size_pretty(pg_relation_size('hier Tabellenname einsetzen'));

und:

SELECT pg_size_pretty(pg_total_relation_size('hier Tabellenname einsetzen'));

Nein. Vacuum würde die alle wegräumen, wenn es könnte. Die 1540 sind aber entweder neu hinzugekommen seit dem Vacuum oder die waren noch durch derzeit laufende Transaktionen blockiert.

Klär das bitte zuerst, woran genau das liegt.

Das ist ein Missverständnis. Es geht nicht um die 1540 sondern um den Unterschied zwischen Zustand nach erstem vacuum (942172) und zweitem vacuum (1540), daß also bei der zweiten Ausführung auf einmal noch über 900000 Datensätze wenige in n_dead_tup standen. Die Logs der vacuums habe ich hier vollständig in die Postings eingefügt, vielleicht geht für euch ja daraus hervor, woran das liegt.

Bis dann, Helmut.

Hoi,

Kann man sich die Zahl der offenen Transaktionen ausgeben lassen? Eigentlich kann das aber nicht das Problem sein, denn ein Neustart der Anwendungs-Prozesse hilft auch nicht. Das sollte ja eigentlich alle eventuell noch offenen Transaktionen schließen. Ich habe jetzt auch mal die Anwendungen gestoppt, trotzdem meint vacuum noch

INFO: »dalvekurzsammel«: 0 entfernbare, 986832 nicht entfernbare Zeilenversionen in 7288 von 12406 Seiten gefunden

Warum kann er die nicht entfernen?

Ach ja, das war ein vacuum ohne full. Eine Ausführung mit full schien nicht in endlicher Zeit fertigzuwerden, ich hab sie deshalb abgebrochen.


Die sagen 83 MB und 127 MB.

Bis dann, Helmut.

Nein, geht es nicht. Das erste Log hat, wie von mir beschrieben, die Meldung, dass die Zeilen derzeit noch nicht gelöscht werden können. Erst beim zweiten Vacuum Lauf konnten sie dann wirklich gelöscht werden.

Daraufhin habe ich dir aufgezählt, woran das vermutlich liegen wird: entweder sind die in der Zeit neu hinzugekommen oder da liegen noch andere Transaktionen, so dass diese Daten noch benötigt werden.

Darum auch mein Hinweis, dass du das bitte zuerst klären sollst. Das ist aus den Logs nicht ersichtlich, dafür müsste man zu dem Zeitpunkt live in die DB hineinschauen.

Zumindest alle derzeit laufenden Anfragen stehen in der Tabelle pg_stat_activity. Alle derzeit aktiven und angefragten Locks stehen in der Tabelle pg_locks. Noch eine Frage: verwendest du prepared transactions?


Ach ja, das war ein vacuum ohne full. Eine Ausführung mit full schien nicht in endlicher Zeit fertigzuwerden, ich hab sie deshalb abgebrochen.

Ein VACUUM FULL schreibt die Tabelle neu und löscht dabei alle nicht mehr benötigten Inhalte. Jetzt gibt es zwei Möglichkeiten: entweder, das VACUUM FULL konnte noch gar nicht anfangen, das braucht nämlich einen exklusiven Lock auf diese Tabelle und den bekommt es nur, wenn alle bereits laufenden Anfragen erledigt sind. Oder es sind so viele Daten, dass das Neuschreiben einfach so lange dauert.
Die Sache mit den Locks könntest du in der Systemtabelle pg_locks herausfinden.


Die sagen 83 MB und 127 MB.

83 MB ist die reine Größe deiner Tabelle auf der Festplatte. Also nur die Daten, allerdings inklusive der gelöschten und durch Updates ungültig gewordenen Zeilen.
127 MB ist die Größe der Tabelle samt aller Indicies.

83 MB entsprechen bei ca. 1000 Einträgen einem durchschnittlichen Datensatz von 87 kB. Jetzt sag du mir: Ist jeder Datensatz wirklich so groß bei dir? Wenn nicht, wie groß sollte ein Datensatz im Schnitt sein?

Hoi,

ich habe jetzt die Anwendungen angehalten, damit ist es dann schließlich gelungen auch die nicht entfernbaren Zeilenversionen gelöscht zu bekommen. Das ist wahrscheinlich mit den regelmäßigen Transaktionen kollidiert und ist dann abgebrochen. Wenn das so sein sollte, haben wir ein praktisches Problem, denn die Anwendungen sollen ständig laufen.

Was mir noch auffällt ist, daß der Index immer noch sehr groß ist (44 MB). Muss man zusätzlich zum vacuum noch regelmässig den Index auf den Tabellen neu erstellen, in denen doch nur in place updates durchgeführt werden? Das kann ich gar nicht glauben.

Bis dann, Helmut.

Radio Eriwan: Eigentlich nicht.
Theoretisch - und in den meisten Fällen auch praktisch - funktioniert das. Was das Entfernen wirklich behindert sind langlaufende Transaktionen. Also über Minuten, Stunden, Tage hinweg …



Was mir noch auffällt ist, daß der Index immer noch sehr groß ist (44 MB). Muss man zusätzlich zum vacuum noch regelmässig den Index auf den Tabellen neu erstellen, in denen doch nur in place updates durchgeführt werden? Das kann ich gar nicht glauben.

Auch das sollte nicht passieren, aber dafür gibt es REINDEX. Das blockiert jedoch die gesamte Tabelle.


Wenn ich mir das alles so durchlese, weiß ich nicht so recht, was ich davon halten soll. Gibt es eine Möglichkeit, direkt mal im laufenden Betrieb in die DB zu schauen? Irgendwas spielt da vielleicht nur nicht sauber zusammen, aber die Fragestunde kann sich jetzt hier noch länger hinziehen.

Hallo zusammen,

Die Transaktionen werden minütlich gestartet, zur Ausführungsdauer siehe die Postings zu Beginn des Threads. Das autovacuum sollte also eigentlich genug Gelegenheit haben, die toten Datensätze zu entfernen. Das komplette vacuum über das Wochenende ohne laufendes System hat übrigens die Transaktionszeiten ohne Neustart des Postgres-Servers wieder in den Sekundenbereich heruntergebracht.

Mir scheint, dass das Problem eigentlich am besten dadurch gelöst würde, die toten Tupel erst gar nicht erst entstehen zu lassen. Ich habe das System jetzt mal ca. eine halbe Stunde laufen lassen und es sind gleich wieder über 8000 tote Tote Tupel pro Tabelle entstanden. Bei “HOT aktualisierte Tupel” steht 464, und bei “Aktive Tupel” steht 308. Auf den fraglichen Tabellen wird aber garantiert nur aktualisiert, es werden keine Datensätze gelöscht und es kommen keine neuen hinzu. Was könnte der Grund dafür sein, daß postgres hier nicht wie erwartet funktioniert?

Bis dann, Helmut.

PostgreSQL funktioniert wie erwartet, es tut, was du willst - jedenfalls mit deinen Daten.

Du hast früher in einem Posting gesagt, dass du HOT beachtest. Das ist aber anscheinend nicht der Fall sondern du hast massenweise Updates, bei denen HOT nicht greift. Kannst du mal bitte die Definition der Tabelle(n) hier posten sowie ein paar der typischen Updates, die du durchführst?

Zur Frage der Aktualisierungen: gerade dabei entstehen die totel Tuples, ohne dass man das erwartet. Ist die MVCC ein Begriff? Der alte Datensatz muss aufgehoben werden, weil andere Transaktionen den ev. noch benötigen. Also wird ein neuer Datensatz geschrieben und der alte bloß als veraltet markiert. Irgendwann räumt VACUUM den alten Datensatz dann weg.

Du hast die letzte Frage in meinem letzten Posting aber noch nicht beantwortet. Wir rätseln hier nämlich immer noch herum, ohne konkret weiter zu kommen.

nur so eine Idee …

demnach würden offene Resultsets auf diese Daten - z.B. von einem anderen Client - auch vacuum solange blockieren, bis diese geschlossen würden? ich meine - denkbar scheint es mir … wenigstens bei serialized transaktionslevel - wenn nicht gar bei beiden!!!

Vacuum wird nicht blockiert sondern arbeitet diese noch benötigten Daten einfach nicht ab.

Hallo zusammen,

Leider nein. Ich denke aber, daß ich ein Repro liefern kann. Siehe dazu die meinen nächsten Beitrag.

Bis dann, Helmut.

das meine ich mit blockieren - sicher funktioniert vacuum auf anderen daten …

ich wollte nur eine anregung liefern, warum diese daten um die es hier geht evtl nicht aufgeraeumt werden -> andere clients die nur “gucken” … auch wenn sie nicht schreiben

Nachtrag:
oder auch der selbe client - nehmen wir mal an er macht sehr früh ein selekt auf die table … und der client haelt das resultset - möglicherweise hat ers einfach nur vergessen zu schließen -> leak … oder er haelt das resultset sehr lange / unnötig lange offen

alle neuen / nachfolgenden versionen der selektierten tupel würden dann nicht vacuumisiert werden?!

ist das so korrekt?

Das Ende der Transaktion beendet alle gehaltenen Locks.


ist das so korrekt?

Nein :wink:

ja “welcher” Transaktion - “welche” (Art von) Locks … darum ging es mir ja gerade … wir kennen ja seinen client nicht - vielleicht schaut er ueber eine 2 connection (read only) auf die gleichen daten (so mein gedanke)

vielleicht liege ich ja wirklich schief - interessant ist es allemal, ob ein offenes Resultset die “früheren/alten” tupel referenziert - oder nicht daran gebunden ist … v.a. bei der Verwendung von Cursor … und hier - ob vacuum neuere versionen aufräumen kann, wenn das resultset ältere versionen betrifft

Hmm

Hallo zusammen,

Was wir hier tun sind Updates von Datensätzen, ohne Primärschlüsselspalten zu verändern. Ob das den Bedingungen für HOT genügt kann ich nicht sicher sagen. So wie ich die Erläuterungen dazu interpretiert habe sollte das aber so sein. Einzig die Einschränkung “allerdings nur innerhalb der aktuellen Speicherseite” ist für mich als Anwender nicht transparent.

Hier ein praktisches Beispiel (zum Nachvollziehen die Tabelle mit 308 Datensätzen mit eaid von 2000000 bis 2000307 füllen):

CREATE TABLE dalvekurzsammel
(
status_ smallint NOT NULL,
“time” integer,
eaid integer NOT NULL,
qkfz smallint,
qlkw smallint,
vpkw smallint,
vlkw smallint,
tnetto smallint,
beleg smallint,
s smallint,
vkfzmittel smallint,
CONSTRAINT dalvekurzsammel_pkey PRIMARY KEY (eaid)
)
WITH (
OIDS=FALSE
);
ALTER TABLE dalvekurzsammel OWNER TO postgres;

Vor Ausführung des folgenden Statements steht in der Statistik “HOT aktualisierte Tupel” 465, Aktive Tupel 308 und Inaktive Tupel 8281:

update dalvekurzsammel set time = 124741282, qkfz=-1, qlkw=-1, vpkw=255, vlkw=255, tnetto=255, beleg=255, s=255, vkfzmittel=255 where eaid=2000001;

Nach Ausführung des Statements steht in der Statistik “HOT aktualisierte Tupel” 466, Aktive Tupel 308 und Inaktive Tupel 8282.

Soweit ich das nun verstanden habe ist die Erzeugung toter Tuples durch Updates, so wie wir sie durchführen, unvermeidlich. Ich muss mich also auf das Tuning der Autovacuum-Einstellungen konzentrieren. Als Zusatzinformation sei vielleicht noch erwähnt, dass die Datenbank aktuell 144441 Tabellen hat. Vielleicht dauert ein Durchlauf des Vacuum dadurch einfach zu lang und wird bereits vor dem Abschluss wieder abgebrochen.

Bis dann, Helmut.

also soweit ich das verstehe verhindert HOT grundsätzlich nicht, dass tote Tupel entstehen - dabei geht es “nur” um index-inserts - d.h. das spart das vacuumisieren der indizes - mehr nicht (wenn man darauf verzichtet index-spalten zu updaten). grundsätzlich gilt: jedes update erzeugt IMMER eine neue tupel-version -> beim commit ist die alte version tot

Re: [GENERAL] Postgres 8.3 HOT and non-persistent xids

Holla!
Eure Datenbank braucht ein paar Anpassungen, speziell auch was Indicies auf Systemtabellen betrifft …


Vielleicht dauert ein Durchlauf des Vacuum dadurch einfach zu lang und wird bereits vor dem Abschluss wieder abgebrochen.

Autovacuum kommt nicht hinterher, würde ich vermuten.
So etwas solltest du zu Anfang sagen :wink:

Ich würde Autovacuum über die pg_autovacuum Tabelle für die meisten eurer Tabellen ausschalten, aber insgesamt aktiviert lassen. Dann kümmert sich Autovacuum um alle anderen Datenbanken (template1, postgres) und um alle Tabellen, die du vergessen hast in deiner Auflistung.

Für eure Datentabellen würde ich mir was eigenes schreiben, aber hier musst du mit mehr als den drei standardmäßig konfigurierten Workern herangehen. Du kannst ja mal im Log schauen, Autovacuum müsste normal vermelden, welche Tabellen es bearbeitet.

Außerdem steht in pg_stat_user_tables der Zeitpunkt des letzten Autovacuum. Sortier doch mal die Tabelle anhand der Zeitstempel und schaue, wie lange ein Durchlauf durch alle Tabellen braucht.

Je länger ich darüber nachdenke: wozu braucht ihr so viele Tabellen? Wie verwaltet ihr die? Und wie ist die Datenbank so groß geworden - habt ihr dabei noch nie Probleme gehabt?

Wie generiert ihr ein Backup des ganzen?