Re: PostgreSQL performance issues

Gregory Stark a écrit :
> "Tiago J. Adami" <adamitj@gmail.com> 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.
>
>