Identifying bloated tables

От: Michal Taborsky - Internet Mall
Тема: Identifying bloated tables
Дата: ,
Msg-id: 44F30022.1000009@mall.cz
(см: обсуждение, исходный текст)
Ответы: Re: Identifying bloated tables  (Brad Nicholson)
Re: Identifying bloated tables  (Markus Schaber)
Список: pgsql-performance

Скрыть дерево обсуждения

Identifying bloated tables  (Michal Taborsky - Internet Mall, )
 Re: Identifying bloated tables  (Brad Nicholson, )
  Re: Identifying bloated tables  (Michal Taborsky - Internet Mall, )
  Re: Identifying bloated tables  (Alvaro Herrera, )
 Re: Identifying bloated tables  (Markus Schaber, )
  Re: Identifying bloated tables  (Michal Taborsky - Internet Mall, )
   Re: Identifying bloated tables  ("Peter Childs", )

I just put together a view, which helps us in indentifying which
database tables are suffering from space bloat, ie. they take up much
more space than they actually should. I though this might be useful for
some folk here, because the questions about bloat-related performance
degradation are quite common.

When using this view, you are interested in tables, which have the
"bloat" column higher that say 2.0 (in freshly dump/restored/analyzed
database they should all be around 1.0).

The bloat problem can be one-time fixed either by VACUUM FULL or
CLUSTER, but if the problem is coming back after while, you should
consider doing VACUUM more often or increasing you FSM settings in
postgresql.conf.

I hope I did the view right, it is more or less accurate, for our
purposes (for tables of just few pages the numbers may be off, but then
again, you are usually not much concerned about these tiny 5-page tables
performance-wise).

Hope this helps someone.

Here comes the view.


CREATE OR REPLACE VIEW "public"."relbloat" (
     nspname,
     relname,
     reltuples,
     relpages,
     avgwidth,
     expectedpages,
     bloat,
     wastedspace)
AS
SELECT pg_namespace.nspname, pg_class.relname, pg_class.reltuples,
     pg_class.relpages, rowwidths.avgwidth, ceil(((pg_class.reltuples *
     (rowwidths.avgwidth)::double precision) /
     (current_setting('block_size'::text))::double precision)) AS
expectedpages,
     ((pg_class.relpages)::double precision / ceil(((pg_class.reltuples *
     (rowwidths.avgwidth)::double precision) /
     (current_setting('block_size'::text))::double precision))) AS bloat,
     ceil(((((pg_class.relpages)::double precision *
     (current_setting('block_size'::text))::double precision) -
     ceil((pg_class.reltuples * (rowwidths.avgwidth)::double precision))) /
     (1024)::double precision)) AS wastedspace
FROM (((
     SELECT pg_statistic.starelid, sum(pg_statistic.stawidth) AS avgwidth
     FROM pg_statistic
     GROUP BY pg_statistic.starelid
     ) rowwidths JOIN pg_class ON ((rowwidths.starelid = pg_class.oid)))
JOIN
         pg_namespace ON ((pg_namespace.oid = pg_class.relnamespace)))
WHERE (pg_class.relpages > 1);


Bye.

--
Michal Táborský
IT operations chief
Internet Mall, a.s.
<http://www.MALL.cz>


В списке pgsql-performance по дате сообщения:

От: "Merlin Moncure"
Дата:
Сообщение: Re: Postgre SQL 7.1 cygwin performance issue.
От: "Junaili Lie"
Дата:
Сообщение: slow i/o