Gregory Stark a écrit :
"Tiago J. Adami" <[EMAIL PROTECTED]> writes:
The issue topics:
1) As the database grows on our customers, lower performance occurs. After
one week of use, the I/O on database is extremely high. It appears that
VACUUM FULL and/or VACUUM ANALYZE doesn't work on this databases.
VACUUM FULL is a last-resort utility for recovering from a bad situation. It
shouldn't be necessary under normal operation. The intended mode is to run
VACUUM (or VACUUM ANALYZE) frequently -- possibly several times a day -- to
maintain the data density.
How frequently are you running VACUUM (or VACUUM ANALYZE)? How many updates
and deletes are you executing in that interval?
If you run VACUUM (or VACUUM ANALYZE) interactively what does it print at the
end of the operation?
2) We have a very complex view mount on other views. When we cancel a simple
SELECT on this top-level view (expecting return a max. of 100 rows for
example) the PostgreSQL process starts a infinite loop (we left more than 4
days and the loop doesn't stops), using 100% of all processors on the
server.
That does sound bad. Would it be possible to attach to the process when it's
spinning and get a back trace? Also, what version is this precisely? Have you
taken all the bug-fix updates for the major version you're using?
3) On these servers, the disk usage grows very small than the records loaded
into database. For example, after restoring a backup, the database DIR have
about 40 Gb (with all indexes created). After one week of use, and about
500,000 new records on tables, the database size grows to about 42 Gb, but
on Windows 2003 Server we can see the high fragmentation of disk (maybe on
linux this occurs too).
Postgres does extend files as needed and some filesystems may deal better with
this than others. I think this is something we don't know much about on
Windows.
humm, kernel 2.6.23 introduce fallocate ...
(I am perhaps about re-lauching a flamewar)
Does postgresql use posix_fallocate ?
You might find running a CLUSTER on the fragmented tables improves matters.
CLUSTER effectively does a full vacuum too so it would leave you in a good
situation to monitor the growth and vacuum frequency necessary from that point
forward too. The downsides are that CLUSTER locks the table while it runs and
it requires enough space to store a whole second copy of the table and its
indexes.
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
