On Mon, Jun 22, 2009 at 9:48 PM, Saurabh Dave
<saurabhdave@gmail.com> wrote:
Thanks Albe,
"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?
Yes, but you needed to analyze what is slow to determine this. Simply changing things and without knowing why doesn't help us determine the problem.
But back to encoding. There are situations where the default setup will not use an index if the encoding is not correct.
So please analyze the slow queries with explain analyze
Also be aware that the driver uses prepared statements so
explain analyze select * from foo where blah=1 is different from
prepare t1 as select * from foo where blah = ?
explain analyze execute foo(1)
Dave
Thanks,
SaurabhOn Mon, Jun 22, 2009 at 1:02 PM, Albe Laurenz
<laurenz.albe@wien.gv.at> wrote:
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.
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