On Thu, Apr 15, 2010 at 6:31 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Chris <lists@xxxxxxxxxx> 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 (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
