system indices growing huge - Mailing list pgsql-admin

From daniel alvarez
Subject system indices growing huge
Date
Msg-id 23506.1044821845@www52.gmx.net
Whole thread Raw
Responses Re: system indices growing huge
Re: system indices growing huge
List pgsql-admin
I'm observing a strange effect on a PostgreSQL installation. The system
indices of one of the databases are growing large without obvious reason.

You'll find a summary of version numbers at the end of this mail.


Two databases are in active use: immoma is the production database, while
testdb is for testing only. Every 30 minutes a cron job feeds the production
data into the test database. A dump of the immoma database is created and
the testdb database recreated using the dump. Therefore, the contents of
the testdb database are mostly identical to the contents of the immoma
database. I've been working for some time using both databases and
functionally the approch is ok. But the system indices of the testdb
database
are now several times larger than those of the immoma database, while the
database contents are identical. Moreover, psql-commands like \d takes a
much longer time to execute against testdb than against immoma.

The script used for the update is as follows. The .cstr files do only
contain
username and password, each followed by a linefeed. The lines of the
commands
have been wrapped in order to better fit your screen.

/usr/local/pgsql/bin/pg_dump -R -c -U immoma immoma
> /usr/local/httpd/htdocs/kunden/web41/datenbank/export.sql
< /usr/local/httpd/htdocs/kunden/web41/datenbank/pg_dump.cstr

/usr/local/pgsql/bin/psql -f
/usr/local/httpd/htdocs/kunden/web41/datenbank/export.sql
testdb immoma < /usr/local/httpd/htdocs/kunden/web41/datenbank/psql.cstr

/usr/local/pgsql/bin/psql -f
/usr/local/httpd/htdocs/kunden/web41/datenbank/sitzungen.sql
testdb immoma < /usr/local/httpd/htdocs/kunden/web41/datenbank/psql.cstr


Each of the two databases contains about 2500 rows in total, distributed
among
some 20 tables. The largest table does not exceed 1300 rows.

The size of testdb is continuously increasing, although it is recreated at
regular
intervals using the data dumped from the immoma database, which increases
at most by 100 rows per day.

Here is a summary of the sizes of the datafiles on disk. A totel of 941 MB
is definitely
unrealistic for the numbers mentioned above.

web41@julius64:~/datenbank > du -h db_cluster
1.7M    db_cluster/base/1
1.7M    db_cluster/base/16555
4.0k    db_cluster/base/16559/pgsql_tmp
81M     db_cluster/base/16559
4.0k    db_cluster/base/16560/pgsql_tmp
710M    db_cluster/base/16560
795M    db_cluster/base
120k    db_cluster/global
129M    db_cluster/pg_xlog
18M     db_cluster/pg_clog
941M    db_cluster

Below I list the page count summaries and single page counts for the 40 most
significant
relations in each of the databases, including system tables. As you see, the
increase in
size is not caused by data entered by the user, but due to overhead in
system indices.

immoma=# select sum (relpages) from pg_class;
  sum
-------
 10316
(1 row)


immoma=# select relname, relfilenode, relpages, relowner from pg_class order
by relpages desc limit 40;
             relname             | relfilenode | relpages | relowner
---------------------------------+-------------+----------+----------
 pg_attribute_relid_attnam_index |       16426 |     3613 |        1
 pg_class_relname_index          |       16429 |     1699 |        1
 pg_attribute_relid_attnum_index |       16427 |     1504 |        1
 objekte                         |     8283011 |     1443 |      100
 pg_class_oid_index              |       16428 |      615 |        1
 pg_type_typname_index           |       16458 |      225 |        1
 pg_index_indrelid_index         |       16435 |      105 |        1
 pg_index_indexrelid_index       |       16436 |      105 |        1
 pg_type_oid_index               |       16457 |       81 |        1
 objekte_kriterien_idx           |    25785967 |       56 |      100
 sitzungen                       |     8282989 |       47 |      100
 sitzungen_pkey                  |     8282991 |       37 |      100
 objekte_pkey                    |     8283016 |       35 |      100
 pg_proc                         |        1255 |       31 |        1
 pg_attrdef_adrelid_adnum_index  |       16425 |       29 |        1
 pg_proc_proname_narg_type_index |       16446 |       29 |        1
 medien                          |     8282979 |       23 |      100
 pg_attribute                    |        1249 |       21 |        1
 sitzungen_zeitpunkt_idx         |     8286797 |       21 |      100
 pg_statistic                    |       16408 |       21 |        1
 anwender                        |     8282943 |       20 |      100
 objekte_n_eigene_idx            |    25785957 |       15 |      100
 objekte_kaeuflichb_idx          |    25785955 |       14 |      100
 objekte_n_etagen_idx            |    25785963 |       14 |      100
 objekte_markt_id_idx            |    25785949 |       13 |      100
 objekte_anwender_id_idx         |    25785950 |       13 |      100
 objekte_standort_id_idx         |    25785951 |       13 |      100
 objekte_aktivb_idx              |    25785954 |       13 |      100
 objekte_preis_cent_idx          |    25785958 |       13 |      100
 objekte_nk_cent_idx             |    25785959 |       13 |      100
 objekte_baujahr_idx             |    25785966 |       13 |      100
 pg_description                  |       16416 |       12 |        1
 pg_toast_8283002                |     8283004 |       12 |      100
 objekte_typ_id_idx              |    25785952 |       12 |      100
 objekte_n_global_idx            |    25785956 |       12 |      100
 objekte_grundstueck_idx         |    25785960 |       12 |      100
 objekte_nutzflaeche_idx         |    25785962 |       12 |      100
 objekte_n_zimmer_idx            |    25785964 |       12 |      100
 objekte_zeitpunkt_idx           |    25785953 |       11 |      100
 objekte_wohnflaeche_idx         |    25785961 |       11 |      100
(40 rows)

testdb=# select sum (relpages) from pg_class;
  sum
-------
 50605
(1 row)


testdb=# select relname, relfilenode, relpages, relowner from pg_class order
by relpages desc limit 40;
             relname             | relfilenode | relpages | relowner
---------------------------------+-------------+----------+----------
 pg_attribute_relid_attnam_index |       16426 |    25196 |        1
 pg_attribute_relid_attnum_index |       16427 |    10470 |        1
 pg_class_relname_index          |       16429 |     7400 |        1
 pg_class_oid_index              |       16428 |     3480 |        1
 pg_type_typname_index           |       16458 |      947 |        1
 pg_type_oid_index               |       16457 |      519 |        1
 pg_index_indrelid_index         |       16435 |      505 |        1
 pg_index_indexrelid_index       |       16436 |      505 |        1
 pg_attribute                    |        1249 |      403 |        1
 pg_attrdef_adrelid_adnum_index  |       16425 |      178 |        1
 pg_attrdef                      |       16384 |      124 |        1
 objekte                         |    27150520 |       99 |      100
 pg_index                        |       16390 |       40 |        1
 pg_type                         |        1247 |       32 |        1
 pg_proc                         |        1255 |       31 |        1
 pg_proc_proname_narg_type_index |       16446 |       29 |        1
 medien                          |    27150488 |       16 |      100
 pg_description                  |       16416 |       12 |        1
 objekte_kriterien_idx           |    27155393 |       11 |      100
 pg_operator                     |       16392 |       10 |        1
 pg_toast_25983663               |    25983665 |       10 |      100
 artikel_tmp                     |    25983663 |       10 |      100
 pg_toast_27150432               |    27150434 |       10 |      100
 branchen                        |    27150432 |       10 |      100
 anbieter                        |    27150440 |       10 |      100
 pg_toast_27150444               |    27150446 |       10 |      100
 pg_toast_27150452               |    27150454 |       10 |      100
 pg_toast_27150458               |    27150460 |       10 |      100
 pg_toast_27150465               |    27150467 |       10 |      100
 pg_toast_27150471               |    27150473 |       10 |      100
 pg_toast_27150478               |    27150480 |       10 |      100
 pg_toast_27150488               |    27150490 |       10 |      100
 pg_toast_27150493               |    27150495 |       10 |      100
 bildvorschau                    |    27150493 |       10 |      100
 pg_toast_27150503               |    27150505 |       10 |      100
 pg_toast_27150511               |    27150513 |       10 |      100
 test                            |    27150517 |       10 |      100
 pg_toast_27150520               |    27150522 |       10 |      100
 plz                             |    27150526 |       10 |      100
 pg_toast_27150530               |    27150532 |       10 |      100
(40 rows)



What is the reason for this increase in size? How can I prevent it?

I'm stuck with this. Any help is welcome.


Regards,  Daniel Alvarez <d-alvarez@gmx.de>


web41@julius64:~ > uname -a
Linux julius64 2.4.14 #34 Wed Jan 23 17:41:57 MET 2002 i686 unknown

web41@julius64:~ > psql -V
psql (PostgreSQL) 7.2
Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
Portions Copyright (c) 1996, Regents of the University of California
Read the file COPYRIGHT or use the command \copyright to see the

--
+++ GMX - Mail, Messaging & more  http://www.gmx.net +++
NEU: Mit GMX ins Internet. Rund um die Uhr für 1 ct/ Min. surfen!


pgsql-admin by date:

Previous
From: Carlos Ortiz
Date:
Subject: Re: MD5 Passwords and user administratio
Next
From: Ian Barwick
Date:
Subject: Re: system indices growing huge