Em 11/09/2012 14:34, Kevin Grittner escreveu:
Edson Richter <edsonrich...@hotmail.com> wrote:
For storage, du -h --max-depth 1 on data directory gives me the
amount of data.
Biggest objects are just the tables with files.
I've 2 tables that held all these objects. Structure is
create table MYTABLE (id bigint not null primary key, mimetype
varchar(100) null, bytea datafile null)
Could you show the results of this query?:
SELECT relkind, oid, relfilenode, reltoastrelid,
relpages, reltuples
FROM pg_class
ORDER BY relpages DESC
LIMIT 10;
relkind oid relfilenode reltoastrelid relpages
reltuples
r 312470 1043546 312492 29321 639571
r 312585 1043643 0 22732 1.80617e+06
r 312522 1043578 312527 19769 724210
r 312749 1043773 312753 14307 928538
r 312758 1043763 0 10488 917134
r 312498 1043525 0 7689 640572
r 312802 1043804 312810 7670 172789
r 312964 1044076 0 7586 385833
i 1041923 1043648 0 6958 1.80617e+06
r 312815 1043825 312819 6684 715081
Also, just to be sure that all calculations are based on your actual
build, can you show the results of?:
SHOW block_size;
8192
Have you checked the level of bloat yet? (Perhaps autovacuum needs
to be made more aggressive.)
Besides autocacuum, I usually run Vacuum Full on weekly basis. My
calculations came after Vacuum Full.
Edson.
-Kevin
