wake-up-neo.com

Hohe Anzahl von Live/Dead-Tupeln in Postgresql/Vacuum funktioniert nicht

Es gibt eine Tabelle mit 200 Zeilen. Aber die Anzahl der Live-Tupel, die dort gezeigt werden, ist größer als etwa 60K.

select count(*) from subscriber_offset_manager;
 count 
-------
   200
(1 row)


 SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables  where relname='subscriber_offset_manager' ORDER BY n_dead_tup
;
 schemaname |          relname          | n_live_tup | n_dead_tup 
------------+---------------------------+------------+------------
 public     | subscriber_offset_manager |      61453 |          5
(1 row)

Wie aus pg_stat_activity und pg_locks hervorgeht, können wir jedoch keine offenen Verbindungen verfolgen.

SELECT query, state,locktype,mode
FROM pg_locks
JOIN pg_stat_activity
  USING (pid)
WHERE relation::regclass = 'subscriber_offset_manager'::regclass
  ;
 query | state | locktype | mode 
-------+-------+----------+------
(0 rows)

Ich habe auch volles Vakuum an dieser Tabelle versucht. Unten sind Ergebnisse: 

  • Zu allen Zeiten werden keine Zeilen entfernt 
  • manchmal werden alle Live-Tupel zu toten Tupeln. 

Hier wird ausgegeben.

vacuum FULL VERBOSE ANALYZE subscriber_offset_manager;
INFO:  vacuuming "public.subscriber_offset_manager"
INFO:  "subscriber_offset_manager": found 0 removable, 67920 nonremovable row versions in 714 pages
DETAIL:  67720 dead row versions cannot be removed yet.
CPU 0.01s/0.06u sec elapsed 0.13 sec.
INFO:  analyzing "public.subscriber_offset_manager"
INFO:  "subscriber_offset_manager": scanned 710 of 710 pages, containing 200 live rows and 67720 dead rows; 200 rows in sample, 200 estimated total rows
VACUUM

 SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables  where relname='subscriber_offset_manager' ORDER BY n_dead_tup
;
 schemaname |          relname          | n_live_tup | n_dead_tup 
------------+---------------------------+------------+------------
 public     | subscriber_offset_manager |        200 |      67749

und nach 10 sek 

SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables  where relname='subscriber_offset_manager' ORDER BY n_dead_tup
;
 schemaname |          relname          | n_live_tup | n_dead_tup 
------------+---------------------------+------------+------------
 public     | subscriber_offset_manager |      68325 |        132

Wie unsere App diese Tabelle abfragt.  

  • Unsere Anwendung wählt im Allgemeinen einige Zeilen aus, und aktualisieren Sie die Zeile basierend auf einer betriebswirtschaftlichen Berechnung. 

    select Abfrage - select basierend auf einer ID 

    select * from subscriber_offset_manager wobei shard_id = 1 ist;

    Abfrage aktualisieren - eine andere Spalte für diese ausgewählte Shard-ID aktualisieren

  • rund 20 Threads tun dies parallel und ein Thread arbeitet nur in einer Zeile.

  • app ist in Java geschrieben, und wir verwenden den Ruhezustand, um Datenbankoperationen durchzuführen. 
  • Postgresql-Version ist 9.3.24

Noch eine interessante Bemerkung: - Wenn ich meine Java-App stoppe und dann volles Vakuum mache, funktioniert es gut (Anzahl der Zeilen und Live-Tupel werden gleich). Es ist also etwas nicht in Ordnung, wenn wir die Java-App kontinuierlich auswählen und aktualisieren. - 

Problem/Problem  

Diese Live-Tupel gehen einige Male in tote Tupel und werden nach einiger Zeit wieder lebendig. 

Aufgrund des oben genannten Verhaltens wählen Sie aus der Tabelle Zeit und erhöhen die Serverlast, da viele Live/Deadtuples vorhanden sind.

javapostgresqlperformancehibernatepostgresql-9.3
12
Sahil Aggarwal

Ich weiß drei Dinge, die VACUUM von seiner Arbeit abhalten:

  • Lange laufende Transaktionen.

  • Vorbereitete Transaktionen, die nicht festgeschrieben wurden.

  • Veraltete Replikationsslots.

Siehe mein Blogbeitrag für Details.

2
Laurenz Albe

Ich habe das Problem ☺. 

Beachten Sie für das Verständnis des Problems den folgenden Ablauf: 

Thread 1 - 

  • Öffnet eine Hibernatsitzung
  • Fragen Sie nach Table-A 
  • Wählen Sie aus subscriber_offset_manager
  • Aktualisieren Sie subscriber_offset_manager. 
  • Schließt die Sitzung. 

Viele Threads des Typs Thread-1 laufen parallel. 

Thread 2 - 

  • Diese Art von Threads laufen parallel. 
  • Öffnet eine Hibernatsitzung
  • Stellen Sie einige Auswahlabfragen auf Table-A 
  • Sitzung wird nicht geschlossen. (Sitzungsleck.) 

Temporäre Lösung - Wenn ich alle Verbindungen, die Thread-2 mit pg_cancel_backend herstellt, schließe, funktioniert das Staubsaugen. 

Auch haben wir das Problem viele Male neu erstellt und diese Lösung ausprobiert und es hat funktioniert. 

Nun gibt es folgende Zweifel, die immer noch nicht beantwortet werden. 

  1. Warum zeigt postgres keine Daten an, die sich auf die Tabelle "subscriber_offset_manager" beziehen. 
  2. Dieses Problem wird nicht erneut erstellt, wenn Thread-2 ausgeführt wird, wenn Sie Table-A mit psql auswählen. 
  3. warum arbeitet postgres mit jdbc so? 

Noch einige überwältigende Beobachtung: 

  1. event, wenn wir Abfragen zu "subscriber_offset_manager" in verschiedenen Sitzungen ausführen, dann wird auch das Kommando ausgegeben; 
  2. wir haben hier viele Beispiele gefunden, in denen Thread 2 an einer dritten Tabelle "Table-C" arbeitet, und es wird ein Problem kommen 
  3. alle diese Transaktionen vom Typ od in pg_stat_activity lauten "idle_in_transaction". 

@Erwin Brandstetter und @Laurenz Albe, wenn Sie wissen, dass es einen Fehler in Bezug auf Postgres/Jdbc gibt. 

4
Sahil Aggarwal

Es kann immerhin Sperren geben, Ihre Abfrage könnte irreführend sein:

SELECT query, state,locktype,mode
FROM pg_locks
JOIN pg_stat_activity USING (pid)
WHERE relation = 'subscriber_offset_manager'::regclass

pg_locks.pid kann NULL sein, dann würde der Join die Zeilen beseitigen. Das Handbuch zu Postgres 9.3:

Prozess-ID des Serverprozesses, der diese Sperre hält oder erwartet, oder null, wenn die Sperre von einer vorbereiteten Transaktion gehalten wird

Mutige Hervorhebung meiner. (Immer noch dasselbe auf S. 10).

Bekommen Sie etwas für die einfache Abfrage?

SELECT * FROM pg_locks
WHERE relation = 'subscriber_offset_manager'::regclass;

Dies könnte erklären, warum VACUUM sich beschwert:

DETAIL:  67720 dead row versions cannot be removed yet.

Dies würde wiederum auf Probleme in Ihrer Anwendungslogik/Abfragen hinweisen, da mehr Zeilen als nötig gesperrt werden.

Meine erste Idee wäre long running transaction, bei der sogar eine einfache SELECT (die eine niedrige ACCESS SHARE-Sperre erhält) VACUUM die Ausführung ihrer Arbeit blockieren kann. 20 parallele Threads können VACUUM unbegrenzt verkettet und gesperrt werden. Halten Sie Ihre Transaktionen (und ihre Sperren) so kurz wie möglich. Stellen Sie außerdem sicher, dass Ihre Abfragen optimiert sind, und sperren Sie nicht mehr Zeilen als nötig.

Noch etwas zu beachten: Transaktionsisolation Stufen SERIALIZABLE oder REPEATABLE READ machen es VACUUM viel schwieriger, diese aufzuräumen. Der voreingestellte READ COMMITTED-Modus ist weniger einschränkend, VACUUM kann jedoch wie beschrieben gesperrt werden.

Verbunden:

2
Erwin Brandstetter