Re: PostgreSQL performance issues

Tiago J. Adami wrote:
> 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.

That sounds like a classic case of "you're not running vacuum often
enough". VACUUM FULL shouldn't be necessary in normal operation. Have
you investigated which queries are causing the I/O?

What version of PostgreSQL are you running? In recent versions, just
enabling autovacuum does a reasonable job in most scenarios.

Check that you don't have any connections forgotten in "idle in
transaction" state. That would prevent VACUUM from recovering dead space.

> 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.

PostgreSQL has a multi-process, single-thread architecture, which means
that only a single CPU can be executing a single query at at time. That
makes it hard to believe that canceling a query uses 100% of *all*
processors.

Have you tried EXPLAINing that query to see why it take so long? Can you
post the query and the EXPLAIN output?

> 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).

Do you think the fragmentation causes you problems? Do you do large
sequential scans regularly? I suppose you could run a defragmenter if
you suspect that's behind the increase in I/O.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com