Em 11/09/2012 14:34, Kevin Grittner escreveu:
Edson Richter <edsonrichter@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;
|
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