Re: Queries very slow and memory consumption too high

On Mon, 2009-06-22 at 10:32 +0530, Saurabh Dave wrote:
> Hi,
>
> I am using Postgres 8.2.10 + Hibernate + c3p0 connection pooling.
> For our application we support MySQL + Oracle + MSSQL + Postgres.
>
> With all the other databases our application is working quite fine,
> but with Postgres after a day queries are becoming extremely slow and
> it seems to be taking up all the available memory.
>
> Please let me know what could be the issue. I am new to Postgres, so
> any help would be greatly appreciated.

You might want to provide some basic information, like:

- What platform / OS / version your PostgreSQL server runs on. Eg
"Ubuntu 9.04" or "Windows 2003 server".

- The hardware specs of your server, including CPU, memory, and most
importantly disks. "2Ghz" isn't good enough; a good description would be
something like "SomeOem 2200GX - two 2.4GHz Xeon E4420s, 16GB RAM, 3Ware
9550/16 SATA disk controller with battery backup, write cache enabled,
attached to 8 250GB 10,000rpm SATA HDDs in RAID 5 and 4 1TB HDDs in RAID
10."

- Where on the server's disks your PostgreSQL data is. For example, you
might say "WAL on 4x1TB RAID 10, main DB on 8x250GB RAID 5, pg_temp and
sort tempfiles on single 8GB SATA RAM drive". If all your postgresql
data is in one place, just mention how it's stored - the file system
type, the RAID level, etc.

- How much memory does your server have free when it slows down? On
UNIX/Linux use "top" and "vmstat" to find this out. On Windows, this
depends on OS version, but the process manager / task manager /
performance monitor should tell you.

- Is your server doing lots of disk I/O when it's slow? (unix/linux:
seem vmstat. Windows Vista, see performance monitor. Others: nfi)

- Is your server paging to disk when it's busy? (unix/linux: see vmstat.
Windows: see task manager/process manager/performance monitor)

--
Craig Ringer