Обсуждение: 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

Saurabh Dave, 22.06.2009 07:02:

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

It's not clear to me *where* the memory is taken. Is that on the machine running your application (i.e. Hibernate and
theconnection pool) or is that memory taken on the Postgres server? 

If it's on the application server, could this be caused by not properly closed result sets? The Postgres JDBC driver by
defaultbuffers all ResultSets in memory before returning to the calling class. If you do not close your result sets
properly,maybe this memory is not released by the driver. 

As an alternative you might try to configure the connection to use "cursor based fetching" to avoid the buffering in
thedriver code: 

http://jdbc.postgresql.org/documentation/83/query.html#query-with-cursor

Regards
Thomas

Saurabh Dave wrote:
> 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'll have to analyze *what* is slow.

Is it the database server or some other component?

It helps to have the various components installed on different machines.

Once you have found out what is slow, drill down there.

If it turns out to be the database and need help, you'll have to provide
EXPLAIN ANALYZE output of a sample query that runs fast in the
beginning and slow later.

Yours,
Laurenz Albe

Saurabh Dave wrote:
> "You'll have to analyze *what* is slow."
>
> That is an excellent question, which I am struggling to answer.
> As per your recommendation I moved DB Server on a remote
> machine and made following changes:
> 1. Postgres version 8.3.7.1 instead of 8.2
> 2. DB encoding changed from UTF-8 to SQL_ANSCII
>
> Now I see the performance of my application to be quite fast,
> I need to observe it for some more time though.
>
> But over the top, do you think that changing the encoding
> would make difference?

I don't think that this will have a big impact, and I wouldn't
do it if I were you. You want your database to be encoding-aware,
don't you? So I would change as little as possible in the setup,
in the hope that you can reproduce the slowdown.

Moving to 8.3.7 (what is 8.3.7.1?), on the other hand, is certainly
a good thing.

If your problem is something that develops over time, one thing
you could watch out for are resource leaks, most prominently
memory leaks.

Yours,
Laurenz Albe

On Tue, 2009-06-23 at 09:38 +0200, Albe Laurenz wrote:

> Moving to 8.3.7 (what is 8.3.7.1?), on the other hand, is certainly
> a good thing.

Probably a Linux distro packaging thing, or a Windows installer
revision. On my Ubuntu 9.04 box for example, `dpkg -l postgresql-8.3'
reports Pg's version as 8.3.7-1 . The -1 is the Debian package revision,
but people may not know that.

--
Craig Ringer

Saurabh Dave wrote:
>>>If your problem is something that develops over time, one thing
>>>you could watch out for are resource leaks, most prominently
>>>memory leaks.
>
> What is the recommended way to do that? It seems postgres
> uses shared memory, and we have 15 connections all taking up
> around 35MB as per task manager OR top. We are not able to
> figure out definitatively if there is a memory leak.
> Help/Clues??? Thanks all for your responses so far.

I'm just ranting, a memory leak frequently does not impact
performance (before the machine starts thrashing...).

When watching for memory leaks, you can (as a crude measure)
daily list the memory that each process consumes and
track notable increases for long running processes.

PostgreSQL shared memory should be excluded here: it does
not increase and it will show up in each backend, thus
confusing the issue.

Once things get slow, find out if the CPU or the disks
are busy and watch out for unusual "vmstat" output.

Yours,
Laurenz Albe

Dave Cramer wrote:
> This also suggests that you have not tuned pg. 35M shared
> memory is not very much. You should look at tuning the installation.

Maybe, but first the problem should be identified, else this falls into
the category of "changing things without knowing why".
Maybe the whole database is only 30MB big?

Yours,
Laurenz Albe