Read Locks? Z.B. Drop Constraint (FK) geht nciht...

Mahlzeit!

Ich bin ein wenig wie die “Jungfrau zum Kind” zu PostgreSQL gekommen. Habe nun die Aufgabe mit “NULL” zusätzlichen Ressourcen eine fette DB am Leben zu erhalten.

Aktuell haben wir das Problem, das sich ständig fachlich parallelisierte Prozesse gegenseitig beim Laden der Fakttabellen die gemeinsamen Dimensionstabellen (Snowflake-Schema) mit einer Art Read-Lock sperren. Das macht überhaupt keinen Sinn (zumindest inhaltlich)!

Als Bsp.: Gestern haben wir eine Art MERGE Statement/Schritt nachgebaut. Vor dem Löschen der alten Tabelle und umbenennen der neuen Tabelle werden die Constraints auf der alten Tabelle z.B. mit

ALTER TABLE “schema”.“alte_kindtabellenname” DROP CONSTRAINT “kindtabelle_id_mastertabelle_id_fk” "

gelöscht. Die Anweisung kommt nicht zurück! Die Vermutung ist, andere Pozesse greifen lesend auf die Elterntabelle zu, während sie selbst in eigenen Untertabellen Änderungen durchführen…

Falls mich jemand verstanden hat: Woran liegt das (link auf PostgreSQL RTFM DMBMS-Struktur reicht)? Und/oder ggf. ein paar Hinweise welche Parameter man verdrehen oder welche Schritte man unternehmen kann um das Problem zu umgehen?

Gruß

antonYM

SELECT … FOR UPDATE

PostgreSQL: Documentation: Manuals: PostgreSQL 8.1: SELECT

Hi,

PostgreSQL: Documentation: Manuals: PostgreSQL 8.3: The Statistics Collector

weiter unten ist eine Liste nützlicher Views, z.B. pg_stat_activity

oder
PostgreSQL: Documentation: Manuals: PostgreSQL 8.3: pg_locks

sollte dir anzeigen welches Query dich blockiert.

Freundliche Grüße
hkocam

Hi! Danke für die Hinweise, aber ich sehe welche Statements mich blockieren und ich mache KEIN select for update - das ist meine Frage weil es keinen Sinn macht DAS blockiert wird.!?

antonYM

Ach ja … bei DDL (z.B. Constraints löschen etc.) ist sowieso ein Table-Lock notwendig - d.h. das DDL-Kommando wartet bis keine andere Transaktion da drauf operiert.

ausserdem ist das diskrete verhalten abhaengig vom gewählten transaktions - isolation level (da gibts bei PG effektiv nur 2)

PostgreSQL: Documentation: Manuals: PostgreSQL 8.3: Transaction Isolation

dann wird vor dem select sicher eine explizite transaktion “gestartet” …

=> SHOW TRANSACTION ISOLATION LEVEL;
transaction_isolation

read committed
(1 row)

Hm… bin ich jetzt auf dem Holzweg? SERIALIZABLE würde doch noch mehr Overhead produzieren und macht im Data Warehouse Kontext (noch) wenig(er) Sinn.!?

Das PostgreSQL nur zwei Transaktionslevel kennt, ist das hier zumindest das Optimum.!?

Nunja … das es nur 2 kennt ist zumindest kein Beinbruch … es kennt die wesentlichen … aber das spielt bei dir gerade keine rolle …

dein Problem ist, dass du DDL Statements (constraints loeschen etc) absetzt - die warten natuerlich auf den tablelock - den sie erst bekommen wenn keine anderen transaktionen auf die betroffene tabelle offen sind …

(für DDL ist logischer weise sowieso die komplette tabelle betroffen - egal um was es sich für ein DDL statement handelt)

und die transaktion (so schaetze ich) wird bei dir bereits vor den selects EXPLIZIT gestartet …

bei read committed dürften sich DDL und offene datenmengen einfacher selekts (ohne vorheriges BEGIN TX) nicht in die quere kommen …

Hier gibt es die Erklärungen dazu:
PostgreSQL: Documentation: Manuals: PostgreSQL 8.3: Transaction Isolation

Dass Repeatable Read fehlt ist wohl nicht wirklich tragisch, aber diverse Leute vermissen Read Uncommitted. Nun ja …


Das PostgreSQL nur zwei Transaktionslevel kennt, ist das hier zumindest das Optimum.!?

Dein Problem liegt anders gelagert, das hat dir symbiont aber schon erklärt. Du hast keine Datenänderungen sondern Strukturänderungen an der Datenbank. Da hilft dir kein Isolationslevel weil dabei immer die kompletten Objekte blockiert werden.

Näheres dazu steht hier:

PostgreSQL: Documentation: Manuals: PostgreSQL 8.3: Explicit Locking

Juuti, habe jetzt verstanden.

Nochmal: Ich hatte das Gefühl, das die Locks sowohl bei DDL als auch bei DMLs auftauchen. Das beim DDL, also dem entfernen einer Kindtabelle ein Lock existiert obwohl nachweislich keiner auf der Kindtabelle drauf ist, lediglich die Elterntabelle benutzt wird, zu der ich den Foreign Key auch grillen will, will mir aber dennoch nicht so recht in den Kopf…

Die Beschreibung Sperren und Indexe finde ich auch sehr interessant.

Nach weiteren Beobachtungen und “studieren” der Anleitung komme ich zu dem Schluss, das weniger die Locks das Problem sind sondern eher, das die Transaktionen entweder keine Prozessorzeit (ideln ewig rum) bzw. keine I/O vom Storage bekommen…

Außerdem habe jetzt die letzten Tage den Prozess weiter umgebaut um Locks, also aufwendige Joins, zu vermeiden. Aktuell hänge ich noch an einem Teilschritt der einfach mal knapp über 3h braucht (auf z.B. 270760806 Datensätzen zwei Sorts machen muss). Ich prüfe jetzt ob man besser indizieren kann - bitmap Indizees wären eine Maßnahme. “Echte” hat die PostgreSQL nach meinem Verständnis noch nicht.!? Ich werde die Suche bemühen und ggf. im entsprechenden Forumteil einen weiteren Thread zum Thema “Indizierung” eröffnen… Danke

antonYM

PostgreSQL kennt auch lesende LOCKs. Damit werden alle Daten/Tabellen belegt, die innerhalb einer Transaktion gebraucht werden - sonst könntest du ja eine Tabelle löschen die später von einer anderen Transaktion noch benötigt wird.



Außerdem habe jetzt die letzten Tage den Prozess weiter umgebaut um Locks, also aufwendige Joins, zu vermeiden.

Locks und Joins sind zwei völlig unterschiedliche Sachen …




Ich prüfe jetzt ob man besser indizieren kann - bitmap Indizees wären eine Maßnahme. “Echte” hat die PostgreSQL nach meinem Verständnis noch nicht.!?

Was bringt dich zu dieser (falschen) Annahme?
Ich emofehle das Schweiter Offiziersmesser, nennt sich EXPLAIN. Näheres dazu in der Dokumentation.

Zitat von > antonym > >

Nochmal: Ich hatte das Gefühl, das die Locks sowohl bei DDL als auch bei DMLs auftauchen. Das beim DDL, also dem entfernen einer Kindtabelle ein Lock existiert obwohl nachweislich keiner auf der Kindtabelle drauf ist, lediglich die Elterntabelle benutzt wird, zu der ich den Foreign Key auch grillen will, will mir aber dennoch nicht so recht in den Kopf…

  1. in einen Foreignkey sind immer 2 Tabellen involviert …
  2. Hast Du uns immernoch nicht verraten, ob explizite Transaktionen offen sind - unabhaengig davon, ob da eine Datenmenge offen ist oder nicht

Hallo Andreas!

Ich glaube wir reden die ganze Zeit aneinander vorbei:

Zum Thema Bitmap Index: Kann PostgreSQL “CREATE BITMAP INDEX name_index_idx ON tabellenname (feld1, feld2);” ???

Wenn ja, bitte um Link zur Doku! Ich habe bisher nur gefunden Bitmap Indexes - und btrees anzulegen in der Hoffnung der Optimizer wird immer magischerweise einen Bitmap HeapScan machen, von dem mehr an Speicherverbrauch durch Btrees ganz zu schweigen, finde ich das etwas spröde…

Davon mal abgesehen das das im Moment nicht das dringendste Problem ist (komme ich am Ende nochmal drauf).



Ja Nein Jein, ich habe hier kein OLTP System, sondern lang laufende Querys auf fetten Fakttabellen, die sich aus den kleinen Dimensionstabellen (die sie sich leider mit anderen Aggregat Fakten teilen) Informationen herauslesen. Was ich jetzt mache ist, die Dimensionsjoins vor den langlaufenden Fakttabellen Querys zu erledigen um sie in Sekunden “woanders” materialisieren zu lassen. Somit sind Locks und joins natürlich unterschiedliche Sachen, die ich aber im Zusammenhang sehen muss… das musste ich erstmal lernen… wie auch das es nicht wirklich die Locks sind, sondern die DB einfach mal dicht ist (siehe Post vorher)…
Aus fachlicher Sicht ist halt bei einem reinen select Statement eine Read Lock völlig überflüssig, da ich beim täglichen Load ODER bei der Abfrage drauf achte, das keine Daten heraus gelesen werden zu denen noch Änderungen passieren - h.h eine (sehr sinnvolle) Funktionlität des DBMS behindert mich…

Und dass hier ein hoch entwickeltes (O)RDBMS durch ein einwenig verkorkstes DB Design/Architektur, im Grunde genommen die Staging Area, die dafür nicht ausgelegt/vorgesehen ist, vergniedelt werden muss ist mir auch klar - nur kann ich es erstmal nicht ändern…

Rate mal was ich die ganze Zeit mache, aus einem 8h query ist nun noch ein Problem übrig geblieben (braucht zw. 2h und 4h):

explain select
bla_id,
user_id,
group_last(result_id) as result_id,
max(date_id) as date_id
from (
select
bla_id as bla_id
, user_id as user_id
, result_id as result_id
, date_id as date_id
from
partitions.irgendwas_2008_11_08
order by user_id, bla_id, result_id, timestamp
) tmp
group by bla_id, user_id, variable_result_id;


GroupAggregate (cost=87615745.28…91171486.64 rows=8000000 width=16)
-> Sort (cost=87615745.28…88188368.84 rows=229049424 width=16)
Sort Key: irgendwas_2008_11_08.bla_id, irgendwas_2008_11_08.user_id, irgendwas_2008_11_08.result_id
-> Sort (cost=45119031.86…45691655.42 rows=229049424 width=24)
Sort Key: irgendwas_2008_11_08.user_id, irgendwas_2008_11_08.bla_id, irgendwas_2008_11_08.result_id, irgendwas_2008_11_08.“timestamp”
-> Seq Scan on irgendwas_2008_11_08 (cost=0.00…3919669.24 rows=229049424 width=24)
(6 rows)

In der Detailtabelle gibt es zu jeder bla_id und user_id und Datum mehrere Einträge (1-n untersiedliche result_ids). Ich möchte daraus pro Datum, bla_id und user_id eine (die letzte) result_id haben.

Ich lasse mir gerne attestieren das das SQL grottig ist - irgendwelche Vorschläge?

Was macht das group_last()? Das ORDER im Subselect erscheint mir hyperfluid. Möglicherweise hast Du in der Doku DISTINCT ON bisher übersehen.


Andreas

Um das DISTINCT ON mal zu erklären:

Du hast:

test=*# select * from detail ;
 bla_id | user_id |   datum    | result_id
--------+---------+------------+-----------
      1 |       1 | 2008-12-01 |         2
      1 |       1 | 2008-12-01 |         3
      1 |       1 | 2008-12-01 |         4
      2 |       2 | 2008-12-01 |         6
(4 rows)

und suchst:

test=*# select distinct on (bla_id,user_id,datum) bla_id, user_id,datum,result_id from detail order by bla_id,user_id,datum,result_id desc;
 bla_id | user_id |   datum    | result_id
--------+---------+------------+-----------
      1 |       1 | 2008-12-01 |         4
      2 |       2 | 2008-12-01 |         6
(2 rows)

Andreas

Jepp, habsch gelernt… und habe ich wie beschrieben ja schon versucht zu umgehen…

Ich bin leider noch nicht so vertraut mit dem Data Dictonary… äh… Systemkatalog - wenn es irgendwo ein paar Hilfsmittel gibt - z.b. ein fertiges SQL Statement über pg_locks, pg_class etc.

Mal um den aktuellen Zustand der Kiste zu zeigen:

select count(*) from pg_locks;
count

1760

Tendenz gleichbleibend… Ist das überhaupt der richtige Weg?

Ich habe noch die pg_stat_activity gefunden wo das attribut “waiting” mit drin ist (dort sind aber alles auf “f” und keiner auf “t” verwirrtsei? Holzweg?

Woher soll ich wissen, was du unter “echten” Indexes verstehst kopfschüttel Da implementiert jede Datenbank ihr eigenes Spielzeug, Index steht nicht im SQL-Standard.

Für die Probleme mit deinem verkorksten Design können wir nichts und deswegen pampig zu werden bringt dich auch nicht weiter. Das sorgt nur dafür dass ich keine Lust mehr habe.

Du hast den timestamp übersehen

Ich habe (timestamp nicht im richtigen Format nur zur Veranschaulichung! z.B. HH24:MM)

test=*# select * from detail ;
 bla_id | user_id |   datum    |   timestamp   | result_id|
--------+---------+------------+------------+-----------
      1 |       1 | 2008-12-01 |       10:24   |        2
      1 |       1 | 2008-12-01 |       10:30   |        2
      1 |       1 | 2008-12-01 |       10:30   |        3
      1 |       1 | 2008-12-01 |       10:35   |        4
      1 |       1 | 2008-12-01 |       10:30   |        4
      2 |       2 | 2008-12-01 |       10:30   |        6
(4 rows)

und brauche:

--------+---------+------------+-----------
      1 |       1 | 2008-12-01 |         2
      1 |       1 | 2008-12-01 |         3
      1 |       1 | 2008-12-01 |         4
      2 |       2 | 2008-12-01 |         6
(2 rows)

Immernoch “hyperfluid”? Anm.: Da die Detailtabelle von mehreren Quellen befüttert wird kenne ich NICHT die Reihenfolge… habe ich einen Knoten im Hirn?

Ich werfe 2 mal Ja in den Raum.

Andreas

Korrektur: 3 mal. Das dritte mal auf die nicht gestellte Frage, ob das Tabellendesign mit der Verwendung von Datum und Timestamp Blödsinn ist.

Andreas