Обсуждение: pg_class has 3615 rows and 1010Mb in table size
The pgAdmin performance on one of our database servers has been dismal
for a while now. I captured one of the queries that was taking forever
to return, therefore making pgAdmin unresponsive for up to 10 minutes.
The query is as follows:
SELECT rel.oid, relname, rel.reltablespace AS spcoid, spcname,
pg_get_userbyid(relowner) AS relowner, relacl, relhasoids,
relhassubclass, reltuples, description, conname, conkey,
EXISTS(select 1 FROM pg_trigger
JOIN pg_proc pt ON pt.oid=tgfoid AND
pt.proname='logtrigger'
JOIN pg_proc pc ON
pc.pronamespace=pt.pronamespace AND pc.proname='slonyversion'
WHERE tgrelid=rel.oid) AS isrepl
, substring(array_to_string(rel.reloptions, ',') from
'fillfactor=([0-9]*)') AS fillfactor
FROM pg_class rel
LEFT OUTER JOIN pg_tablespace ta on ta.oid=rel.reltablespace
LEFT OUTER JOIN pg_description des ON (des.objoid=rel.oid AND
des.objsubid=0)
LEFT OUTER JOIN pg_constraint c ON c.conrelid=rel.oid AND
c.contype='p'
WHERE relkind IN ('r','s','t') AND relnamespace = 16686::oid
ORDER BY relname
I looked at the pg_class table and noticed that its size is 1010Mb and
index size is 1137Mb, while the table itself has only 3615 rows in it.
I tried vacuuming it, but that did not change anything. Is there
anything I can do to get this table back to the size it is supposed to
be at?
PostgreSQL 8.2.6 on Windows 2003 Server.
On Wed, 2010-05-05 at 09:40 -0700, Nikola wrote: > I looked at the pg_class table and noticed that its size is 1010Mb and > index size is 1137Mb, while the table itself has only 3615 rows in it. > I tried vacuuming it, but that did not change anything. Is there > anything I can do to get this table back to the size it is supposed to > be at? > > PostgreSQL 8.2.6 on Windows 2003 Server. vacuum full, its an exclusive lock though. I don't recall if 8.2 autovacuum would vacuum system catalogs or not. Either way, you really should upgrade, especially since you are on Windows. Joshua D. Drake > -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering
On May 5, 3:37 pm, j...@commandprompt.com ("Joshua D. Drake") wrote:
> On Wed, 2010-05-05 at 09:40 -0700, Nikola wrote:
> > I looked at the pg_class table and noticed that its size is 1010Mb and
> > index size is 1137Mb, while the table itself has only 3615 rows in it.
> > I tried vacuuming it, but that did not change anything. Is there
> > anything I can do to get this table back to the size it is supposed to
> > be at?
>
> > PostgreSQL 8.2.6 on Windows 2003 Server.
>
> vacuum full, its an exclusive lock though.
>
> I don't recall if 8.2 autovacuum would vacuum system catalogs or not.
> Either way, you really should upgrade, especially since you are on
> Windows.
>
> Joshua D. Drake
>
>
>
> --
> PostgreSQL.org Major Contributor
> Command Prompt, Inc:http://www.commandprompt.com/- 503.667.4564
> Consulting, Training, Support, Custom Development, Engineering
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general
Yes, I was hoping not to have to do a vacuum full due to the exclusive
lock. However, if that's the only solution (other than upgrading in
general) then that's what needs to happen. Hopefully, vacuum full does
not end up taking an extremely long time to run.
Excerpts from Joshua D. Drake's message of mié may 05 15:37:05 -0400 2010: > On Wed, 2010-05-05 at 09:40 -0700, Nikola wrote: > > > I looked at the pg_class table and noticed that its size is 1010Mb and > > index size is 1137Mb, while the table itself has only 3615 rows in it. > > I tried vacuuming it, but that did not change anything. Is there > > anything I can do to get this table back to the size it is supposed to > > be at? > > > > PostgreSQL 8.2.6 on Windows 2003 Server. > > vacuum full, its an exclusive lock though. Note though that there being such few tuples, it should be rather quick. > I don't recall if 8.2 autovacuum would vacuum system catalogs or not. Eh, of course it would. --
On Wed, 2010-05-05 at 09:40 -0700, Nikola wrote: > I looked at the pg_class table and noticed that its size is 1010Mb and > index size is 1137Mb, while the table itself has only 3615 rows in it. > I tried vacuuming it, but that did not change anything. Is there > anything I can do to get this table back to the size it is supposed to > be at? > > PostgreSQL 8.2.6 on Windows 2003 Server. vacuum full, its an exclusive lock though. I don't recall if 8.2 autovacuum would vacuum system catalogs or not. Either way, you really should upgrade, especially since you are on Windows. Joshua D. Drake > -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering
On May 5, 6:16 pm, alvhe...@alvh.no-ip.org (Alvaro Herrera) wrote: > Excerpts from Joshua D. Drake's message of mié may 05 15:37:05 -0400 2010: > > > On Wed, 2010-05-05 at 09:40 -0700, Nikola wrote: > > > > I looked at the pg_class table and noticed that its size is 1010Mb and > > > index size is 1137Mb, while the table itself has only 3615 rows in it. > > > I tried vacuuming it, but that did not change anything. Is there > > > anything I can do to get this table back to the size it is supposed to > > > be at? > > > > PostgreSQL 8.2.6 on Windows 2003 Server. > > > vacuum full, its an exclusive lock though. > > Note though that there being such few tuples, it should be rather quick. > > > I don't recall if 8.2 autovacuum would vacuum system catalogs or not. > > Eh, of course it would. > -- > > -- > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general Vacuum full worked. It blocked all queries for about 15 minutes, but it was not nearly as long as I thought it would be. pg_class (and other catalog tables that had similar issues on a smaller scale) significantly reduced in size resulting in much quicker (almost instantaneous) response from pgAdmin. Thanks for the help.