rätzelhafte Performance-Abhängikeiten...

Allgemeines zu PostgreSQL.

rätzelhafte Performance-Abhängikeiten...

Beitragvon strippenzieher » Mo 25. Jun 2012, 14:16

Hallo,

die Performanceprobleme welche ich hier ab #10 geschildert hatte waren nach einem postgres pg_dump -Fc datenbank > datenbank.dump aus Version 8.4 und anschließendem pg_restore -d datenbank datenbank.dump in Version 9.1 zunächst verschwunden.
Aber noch während ich gestern fast mit ausschließlich selects die Datenbank bearbeitet hatte war sie des Nachts wieder genauso langsam wie in Version 8.4.

Nun habe ich die Datenbank heute nochmals unter anderem Namen mittels pg_restore eingespielt und kann zwischen den Datenbanknamen hin- und herwechseln. Und siehe da - die ganz frisch wiederhergestellte Version ist wirklich sehr viel schneller - die gestern eingespielte sehr langsam (66 ms zu 66000 ms). Und das bei der selben Serverversion 9.1 und natürlich auch den selben Einstellungen. Ein vacuum analyse oder Neustart des Servers haben weder einen positiven Effekt auf die langsame Version, noch einen negativen auf die frisch wiederhergestellte.

Hat jemand eine Idee was hier im Argen liegt?

Gruß,
Strippenzieher
strippenzieher
Benutzer
 
Beiträge: 74
Registriert: Sa 22. Jan 2011, 12:39

Beitragvon strippenzieher » Do 28. Jun 2012, 16:07

Falls das irgendwie hilft, hier nochmal die Query-Analysen mit der db vor dem dump und anschließendem restore (aber nach vacuum analyse)
Code: Alles auswählen
                                                                       QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
 WindowAgg  (cost=2231.56..2232.17 rows=22 width=59) (actual time=16748.382..16749.203 rows=340 loops=1)
   ->  Sort  (cost=2231.56..2231.62 rows=22 width=59) (actual time=16748.360..16748.575 rows=340 loops=1)
         Sort Key: ba.bookid, (CASE WHEN (e.languageid = 123) THEN 1 WHEN (e.languageid = 401) THEN 2 WHEN (e.languageid = 150) THEN 3 ELSE 4 END)
         Sort Method: quicksort  Memory: 60kB
         ->  Nested Loop  (cost=0.00..2231.07 rows=22 width=59) (actual time=0.125..16747.395 rows=340 loops=1)
               ->  Index Scan using authorid1 on book_author ba  (cost=0.00..73.94 rows=20 width=8) (actual time=0.034..11.453 rows=99 loops=1)
                     Index Cond: (authorid = 543)
               ->  Index Scan using foreign_key_bookid on editions e  (cost=0.00..107.76 rows=8 width=51) (actual time=90.741..169.031 rows=3 loops=99)
                     Index Cond: (bookid = ba.bookid)
                     Filter: mainname
 Total runtime: 16752.146 ms
(11 Zeilen)

und danach:
Code: Alles auswählen
                                                                     QUERY PLAN                                                                     
-----------------------------------------------------------------------------------------------------------------------------------------------------
 WindowAgg  (cost=2325.78..2326.41 rows=23 width=58) (actual time=18.583..19.387 rows=340 loops=1)
   ->  Sort  (cost=2325.78..2325.84 rows=23 width=58) (actual time=18.562..18.823 rows=340 loops=1)
         Sort Key: ba.bookid, (CASE WHEN (e.languageid = 123) THEN 1 WHEN (e.languageid = 401) THEN 2 WHEN (e.languageid = 150) THEN 3 ELSE 4 END)
         Sort Method: quicksort  Memory: 60kB
         ->  Nested Loop  (cost=0.00..2325.26 rows=23 width=58) (actual time=0.385..18.060 rows=340 loops=1)
               ->  Index Scan using authorid1 on book_author ba  (cost=0.00..73.29 rows=20 width=8) (actual time=0.045..0.541 rows=99 loops=1)
                     Index Cond: (authorid = 543)
               ->  Index Scan using foreign_key_bookid on editions e  (cost=0.00..112.49 rows=9 width=50) (actual time=0.056..0.168 rows=3 loops=99)
                     Index Cond: (bookid = ba.bookid)
                     Filter: mainname
 Total runtime: 19.787 ms
(11 Zeilen)


Das ganze läuft auf einem Ubuntu-10.04-shard-host samt Apache mit PostgreSQL 9.1, 3GB Ram,

shared_buffers = 680MB
work_mem = 10MB
maintenance_work_mem = 64MB
checkpoint_segments = 32
checkpoint_completion_target = 0.9
effective_cache_size = 1500MB

Könnte ich noch irgendwelche anderen Informationen beitragen um der Problemlösung näher zu kommen?

Gruß,
strippenzieher
strippenzieher
Benutzer
 
Beiträge: 74
Registriert: Sa 22. Jan 2011, 12:39

Beitragvon akretschmer » Do 28. Jun 2012, 16:51

strippenzieher hat geschrieben:Falls das irgendwie hilft, hier nochmal die Query-Analysen mit der db vor dem dump und anschließendem restore (aber nach vacuum analyse)
Code: Alles auswählen
                                                                       QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
 WindowAgg  (cost=2231.56..2232.17 rows=22 width=59) (actual time=16748.382..16749.203 rows=340 loops=1)
   ->  Sort  (cost=2231.56..2231.62 rows=22 width=59) (actual time=16748.360..16748.575 rows=340 loops=1)
         Sort Key: ba.bookid, (CASE WHEN (e.languageid = 123) THEN 1 WHEN (e.languageid = 401) THEN 2 WHEN (e.languageid = 150) THEN 3 ELSE 4 END)
         Sort Method: quicksort  Memory: 60kB
         ->  Nested Loop  (cost=0.00..2231.07 rows=22 width=59) (actual time=0.125..16747.395 rows=340 loops=1)
               ->  Index Scan using authorid1 on book_author ba  (cost=0.00..73.94 rows=20 width=8) (actual time=0.034..11.453 rows=99 loops=1)
                     Index Cond: (authorid = 543)
               ->  Index Scan using foreign_key_bookid on editions e  (cost=0.00..107.76 rows=8 width=51) (actual time=90.741..169.031 rows=3 loops=99)
                     Index Cond: (bookid = ba.bookid)
                     Filter: mainname
 Total runtime: 16752.146 ms
(11 Zeilen)

und danach:
Code: Alles auswählen
                                                                     QUERY PLAN                                                                     
-----------------------------------------------------------------------------------------------------------------------------------------------------
 WindowAgg  (cost=2325.78..2326.41 rows=23 width=58) (actual time=18.583..19.387 rows=340 loops=1)
   ->  Sort  (cost=2325.78..2325.84 rows=23 width=58) (actual time=18.562..18.823 rows=340 loops=1)
         Sort Key: ba.bookid, (CASE WHEN (e.languageid = 123) THEN 1 WHEN (e.languageid = 401) THEN 2 WHEN (e.languageid = 150) THEN 3 ELSE 4 END)
         Sort Method: quicksort  Memory: 60kB
         ->  Nested Loop  (cost=0.00..2325.26 rows=23 width=58) (actual time=0.385..18.060 rows=340 loops=1)
               ->  Index Scan using authorid1 on book_author ba  (cost=0.00..73.29 rows=20 width=8) (actual time=0.045..0.541 rows=99 loops=1)
                     Index Cond: (authorid = 543)
               ->  Index Scan using foreign_key_bookid on editions e  (cost=0.00..112.49 rows=9 width=50) (actual time=0.056..0.168 rows=3 loops=99)
                     Index Cond: (bookid = ba.bookid)
                     Filter: mainname
 Total runtime: 19.787 ms
(11 Zeilen)


Das ganze läuft auf einem Ubuntu-10.04-shard-host samt Apache mit PostgreSQL 9.1, 3GB Ram,

shared_buffers = 680MB
work_mem = 10MB
maintenance_work_mem = 64MB
checkpoint_segments = 32
checkpoint_completion_target = 0.9
effective_cache_size = 1500MB

Könnte ich noch irgendwelche anderen Informationen beitragen um der Problemlösung näher zu kommen?

Gruß,
strippenzieher


Schuß ins Blaue: relativ viele tote Tupel. Prüf das mal, in pg_class reltuples und relpages.


Andreas
--
ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)

https://www.xing.com/profile/Andreas_Kretschmer7
Hosting-Provider mit PostgreSQL-Support gesucht? Mich fragen!
akretschmer
Erfahrener Benutzer
 
Beiträge: 5937
Registriert: So 29. Okt 2006, 14:35
Wohnort: Kaufbach, Sachsen, Germany, Germany

Beitragvon strippenzieher » Do 28. Jun 2012, 17:30

Ich hoffe, dass ist was du meinst:
Code: Alles auswählen
datenbank=# CREATE EXTENSION pgstattuple;
CREATE EXTENSION
datenbank=# SELECT * FROM pgstattuple('pg_catalog.pg_proc');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
    475136 |        2361 |    449526 |         94.61 |                0 |              0 |                  0 |       6388 |         1.34
(1 Zeile)

Das war die langsame db. Ich habe sie aber auch nach dem vacuum analyze; nicht mehr benutzt. Soweit ich das gelesen habe, werden ja die toten Tupel dabei gelöscht.
Der selbe Befehl in der schnellen db-Variante:
Code: Alles auswählen
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
    475136 |        2361 |    449526 |         94.61 |                1 |            375 |               0.08 |       6012 |         1.27
(1 Zeile)
Also eigentlich kein Unterschied.
strippenzieher
Benutzer
 
Beiträge: 74
Registriert: Sa 22. Jan 2011, 12:39

Beitragvon akretschmer » Do 28. Jun 2012, 17:47

strippenzieher hat geschrieben:Ich hoffe, dass ist was du meinst:


Nein. Es gibt pg_class (das schrieb ich auch), da kann man je Tabelle abfragen, wie viele Tuple und wie viele Pages diese hat. pgstattuples erleichtert Dir den Zugriff, aber pg_proc enthält lediglich Daten über Stored Procedures. Einfach aus der Doku Copy&Paste ist nicht immer hilfreich ...

Andreas
--
ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)

https://www.xing.com/profile/Andreas_Kretschmer7
Hosting-Provider mit PostgreSQL-Support gesucht? Mich fragen!
akretschmer
Erfahrener Benutzer
 
Beiträge: 5937
Registriert: So 29. Okt 2006, 14:35
Wohnort: Kaufbach, Sachsen, Germany, Germany

Beitragvon strippenzieher » Do 28. Jun 2012, 20:00

Ich hoffe das ist es - auch wenn ich die toten tuples nicht herauslesen kann. Limitiert auf die je ersten 10 Zeilen von 528. select relname, reltuples, relpages from pg_class order by reltuples desc limit 10; ergibt in der langsamen Variante:
Code: Alles auswählen
          relname           |  reltuples  | relpages
----------------------------+-------------+----------
 editions                   | 1.06963e+07 |   168977
 idx_editions_languageid    | 1.06962e+07 |    29330
 idx_editions_mainname      | 1.06962e+07 |    29330
 index_isbn10_editions      | 1.06962e+07 |    37654
 index_title_editions       | 1.06962e+07 |    86574
 foreign_key_bookid         | 1.06962e+07 |    29330
 fki_editions_booktype_fkey | 1.06962e+07 |    29330
 editions_pkey2             | 1.06962e+07 |    29330
 orig_title                 | 1.06958e+07 |   102800
 fki_editionid              | 1.06754e+07 |    29273
und in der schnellen:
Code: Alles auswählen
          relname           |  reltuples  | relpages
----------------------------+-------------+----------
 idx_gin1                   | 8.88863e+07 |   118107
 gin_idx                    | 2.76517e+07 |    35845
 orig_title                 |  1.0698e+07 |   102800
 editions                   | 1.06962e+07 |   168977
 index_title_editions       | 1.06962e+07 |    86574
 index_isbn10_editions      | 1.06962e+07 |    37654
 foreign_key_bookid         | 1.06957e+07 |    29330
 idx_editions_languageid    | 1.06957e+07 |    29330
 editions_pkey2             | 1.06957e+07 |    29330
 fki_editions_booktype_fkey | 1.06957e+07 |    29330


Gruß,
strippenzieher
strippenzieher
Benutzer
 
Beiträge: 74
Registriert: Sa 22. Jan 2011, 12:39

Beitragvon akretschmer » Do 28. Jun 2012, 20:22

strippenzieher hat geschrieben:Ich hoffe das ist es - auch wenn ich die toten tuples nicht herauslesen kann. Limitiert auf die je ersten 10 Zeilen von 528. select relname, reltuples, relpages from pg_class order by reltuples desc limit 10; ergibt in der langsamen Variante:

Gruß,
strippenzieher


Hihi. Sorry. relname ist der Tabellenname.


Andreas
--
ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)

https://www.xing.com/profile/Andreas_Kretschmer7
Hosting-Provider mit PostgreSQL-Support gesucht? Mich fragen!
akretschmer
Erfahrener Benutzer
 
Beiträge: 5937
Registriert: So 29. Okt 2006, 14:35
Wohnort: Kaufbach, Sachsen, Germany, Germany

Beitragvon ads » Do 28. Jun 2012, 23:30

Schau mal bitte, was du in "pg_stat_user_tables" für die beteiligten Tabellen findest.
Der Sort frisst dir die Zeit auf, die Anfragen sind eigentlich recht schnell erledigt. Irgendeine Idee, was parallel dazu noch auf deinem System vor sich geht?
Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören Bild

Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler

PostgreSQL Service & Support
ads
Erfahrener Benutzer
 
Beiträge: 4147
Registriert: Mi 8. Feb 2006, 11:58
Wohnort: MD

Beitragvon strippenzieher » Fr 29. Jun 2012, 10:07

akretschmer hat geschrieben:Hihi. Sorry. relname ist der Tabellenname.


Ja, steht ja auch so in der Doku. Aber wo finde ich jetzt die toten Tupel? Verstehe deinen letzten Post nicht :confused:

ads hat geschrieben:Schau mal bitte, was du in "pg_stat_user_tables" für die beteiligten Tabellen findest.
Der Sort frisst dir die Zeit auf, die Anfragen sind eigentlich recht schnell erledigt. Irgendeine Idee, was parallel dazu noch auf deinem System vor sich geht?


Code: Alles auswählen
SELECT * FROM pg_stat_user_tables WHERE relname = 'editions' or relname = 'book_author';

langsame db:
 relid | schemaname |   relname   | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup |          last_vacuum          | last_autovacuum |         last_analyze          |       last_autoanalyze        | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count
-------+------------+-------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+-------------------------------+-----------------+-------------------------------+-------------------------------+--------------+------------------+---------------+-------------------
 25711 | public     | editions    |       12 |    128354760 |    12084 |        366502 |  10696230 |         0 |         0 |             0 |   10696252 |          0 | 2012-06-28 13:00:50.566456+02 |                 | 2012-06-28 13:01:23.448548+02 | 2012-06-28 01:06:16.045851+02 |           10 |                0 |             1 |                 1
 25641 | public     | book_author |        6 |     14811156 |     1762 |          9693 |   2468526 |         0 |         0 |             0 |    2468526 |          0 | 2012-06-28 12:59:08.813571+02 |                 | 2012-06-28 12:59:10.671817+02 | 2012-06-28 01:03:00.483614+02 |           10 |                0 |             1 |                 1
(2 Zeilen)

schnelle db:
 relid | schemaname |   relname   | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | last_vacuum | last_autovacuum | last_analyze |       last_autoanalyze        | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count
-------+------------+-------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+-------------+-----------------+--------------+-------------------------------+--------------+------------------+---------------+-------------------
 27794 | public     | editions    |       11 |    117658530 |     1977 |         72895 |  10696230 |         0 |         0 |             0 |   10695717 |          0 |             |                 |              | 2012-06-28 15:39:37.1816+02   |            0 |                0 |             0 |                 1
 27724 | public     | book_author |        5 |     12342630 |     1081 |          2402 |   2468526 |         0 |         0 |             0 |    2468526 |          0 |             |                 |              | 2012-06-28 15:29:12.420316+02 |            0 |                0 |             0 |                 1
(2 Zeilen)


Der Server ist ein shared-Server - da können zwar sicher mal Lastschwankunen auftreten, aber da ich beide DBs simultan abfragen kann und immer wieder zum gleichen Performanceunterschied komme dürfte es daran nicht liegen. Von mir läuft da noch Apache, aber wenn nicht zufällig ein Crawler vorbeigekommen ist, macht der keine Last.

Gruß,
strippenzieher
strippenzieher
Benutzer
 
Beiträge: 74
Registriert: Sa 22. Jan 2011, 12:39

Beitragvon akretschmer » Fr 29. Jun 2012, 10:53

strippenzieher hat geschrieben:Ja, steht ja auch so in der Doku. Aber wo finde ich jetzt die toten Tupel? Verstehe deinen letzten Post nicht :confused:

Einfach selectieren mit Tabellenname = relname und dann das Verhältniss von reltuples und relpages vergleichen.

Der Server ist ein shared-Server - da können zwar sicher mal Lastschwankunen auftreten, aber da ich beide DBs simultan abfragen kann und immer wieder zum gleichen Performanceunterschied komme dürfte es daran nicht liegen. Von mir läuft da noch Apache, aber wenn nicht zufällig ein Crawler vorbeigekommen ist, macht der keine Last.

Gruß,
strippenzieher


Steht das bei einem Hoster?


Andreas
--
ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)

https://www.xing.com/profile/Andreas_Kretschmer7
Hosting-Provider mit PostgreSQL-Support gesucht? Mich fragen!
akretschmer
Erfahrener Benutzer
 
Beiträge: 5937
Registriert: So 29. Okt 2006, 14:35
Wohnort: Kaufbach, Sachsen, Germany, Germany

Nächste

Zurück zu Allgemeines

Wer ist online?

Mitglieder in diesem Forum: 0 Mitglieder und 0 Gäste

cron