On Thu, Apr 15, 2010 at 6:31 PM, Tom Lane <[email protected]> wrote:
> Chris <[email protected]> writes:
>> I have a lot of centos servers which are running postgres. Postgres isn't
>> used
>> that heavily on any of them, but lately, the stats collector process keeps
>> causing tons of IO load. It seems to happen only on servers with centos 5.
>
> Say, I just realized that both of you are complaining about stats
> collector overhead on centos 5 servers. I hadn't been thinking in terms
> of OS-specific causes, but maybe that is what we need to consider.
> Can you tell me the exact kernel versions you are seeing these problems
> with?
uname -a says "... 2.6.18-92.1.13.el5 #1 SMP ... x86_64", and it's CentOS 5.2.
I'm not sure whether this is related to the stats collector problems
on this machine, but I noticed alarming table bloat in the catalog
tables pg_attribute, pg_attrdef, pg_depend, and pg_type. Perhaps this
has happened slowly over the past few months, but I discovered the
bloat when I ran the query from:
http://pgsql.tapoueh.org/site/html/news/20080131.bloat.html
on the most-active database on this server (OID 16389 from the
pgstat.stat I sent in). See attached table_bloat.txt. The autovacuum
settings for this server haven't been tweaked from the default; they
probably should have been, given the heavy bulk updates/inserts done.
Maybe there's another cause for this extreme catalog bloat, besides
the weak autovacuum settings, though.
Table sizes, according to pg_size_pretty(pg_total_relation_size(...)):
* pg_attribute: 145 GB
* pg_attrdef: 85 GB
* pg_depend: 38 GB
* pg_type: 3465 MB
I'll try to send in strace outputs later today.
Josh
schemaname | tablename | reltuples | relpages | otta |
tbloat | wastedpages | wastedbytes | wastedsize | iname
| ituples | ipages | iotta | ibloat | wastedipages |
wastedibytes | wastedisize
---------------+-----------------------+-----------+----------+--------+----------+-------------+-------------+------------+------------------------------------------------+----------+---------+--------+---------+--------------+--------------+-------------
pg_catalog | pg_attribute | 0 | 12178069 | 0 |
0.0 | 12178069 | 99762741248 | 93 GB | pg_attribute_relid_attnam_index
| 0 | 4525199 | 0 | 0.0 | 4525199 |
37070430208 | 35 GB
pg_catalog | pg_attribute | 0 | 12178069 | 0 |
0.0 | 12178069 | 99762741248 | 93 GB | pg_attribute_relid_attnum_index
| 0 | 848842 | 0 | 0.0 | 848842 |
6953713664 | 6632 MB
pg_catalog | pg_attrdef | 767 | 8789472 | 30 |
292982.4 | 8789442 | 72003108864 | 67 GB | pg_attrdef_oid_index
| 767 | 361513 | 28 | 12911.2 | 361485 |
2961285120 | 2824 MB
pg_catalog | pg_attrdef | 767 | 8789472 | 30 |
292982.4 | 8789442 | 72003108864 | 67 GB |
pg_attrdef_adrelid_adnum_index | 767 | 359805 | 28 |
12850.2 | 359777 | 2947293184 | 2811 MB
<snip one bloated user table>
pg_catalog | pg_depend | 14270 | 1890691 | 98 |
19292.8 | 1890593 | 15487737856 | 14 GB | pg_depend_depender_index
| 14270 | 1649557 | 65 | 25377.8 | 1649492 |
13512638464 | 13 GB
pg_catalog | pg_depend | 14270 | 1890691 | 98 |
19292.8 | 1890593 | 15487737856 | 14 GB | pg_depend_reference_index
| 14270 | 1371522 | 65 | 21100.3 | 1371457 |
11234975744 | 10 GB
<snip some more user tables>
pg_catalog | pg_type | 1333 | 385445 | 31 |
12433.7 | 385414 | 3157311488 | 3011 MB | pg_type_oid_index
| 1333 | 54965 | 24 | 2290.2 | 54941 |
450076672 | 429 MB
pg_catalog | pg_type | 1333 | 385445 | 31 |
12433.7 | 385414 | 3157311488 | 3011 MB | pg_type_typname_nsp_index
| 1333 | 3129 | 24 | 130.4 | 3105 |
25436160 | 24 MB
<snip>
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
