pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
DB Performance Tuning
3+ messages / 3 participants
[nested] [flat]

* DB Performance Tuning
@ 2006-09-05 15:08 nilesh khode <nilesh_khode@yahoo.com>
  2006-09-05 15:24 ` Re: DB Performance Tuning romje@javaxpert.com
  2006-09-05 15:28 ` Re: DB Performance Tuning Guido Neitzer <guido.neitzer@gmail.com>
  0 siblings, 2 replies; 3+ messages in thread

From: nilesh khode @ 2006-09-05 15:08 UTC (permalink / raw)
  To: pgsql-admin

  Hello,
 
 I am working on database performace tuning on Postgresql 8.1 database build on Suse 10 Linux dedicated server. I am having 32 GB of RAM and 8 CPUs.  
    

I have made minimum recommended settings under postgresql.conf (i.e shared_buffer, work_mem, and effective cache_size also kernel.shmmax)
   
  I have run SQL queries using pg-admin, the query execution + retrieval time is 80% reduced over existing server.
  But same I tested with JDBC, the query requires more time as compared with existing server. 
   
  Please help me out in this performance tuning. 
  
Regards,
Nilesh

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: DB Performance Tuning
  2006-09-05 15:08 DB Performance Tuning nilesh khode <nilesh_khode@yahoo.com>
@ 2006-09-05 15:24 ` romje@javaxpert.com
  1 sibling, 0 replies; 3+ messages in thread

From: romje@javaxpert.com @ 2006-09-05 15:24 UTC (permalink / raw)
  To: nilesh khode <nilesh_khode@yahoo.com>; +Cc: pgsql-admin


On Tue, September 5, 2006 5:08 pm, nilesh khode wrote:
>   Hello,
>
>  I am working on database performace tuning on Postgresql 8.1 database
> build on Suse 10 Linux dedicated server. I am having 32 GB of RAM and 8
> CPUs.
>

nice machine :)
>
> I have made minimum recommended settings under postgresql.conf (i.e
> shared_buffer, work_mem, and effective cache_size also kernel.shmmax)
>
>   I have run SQL queries using pg-admin, the query execution + retrieval
> time is 80% reduced over existing server.
>   But same I tested with JDBC, the query requires more time as compared
> with existing server.
>



what 's the driver used ? do you use statement or preparedstatement ?
the time given is made after 1 query or the same query repeated x times
then dividing the whole time by x ?
have you tried to use jdbc proxy (p6spy for instance) to watch the query ?
to know where the time is spent (don't use it in production !!!)
>   Please help me out in this performance tuning.
>


HTH
Jerome
> Regards,
> Nilesh
>
>


-- 
Auteur cahiers du programmeur J2EE - Eyrolles 2003




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: DB Performance Tuning
  2006-09-05 15:08 DB Performance Tuning nilesh khode <nilesh_khode@yahoo.com>
@ 2006-09-05 15:28 ` Guido Neitzer <guido.neitzer@gmail.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Guido Neitzer @ 2006-09-05 15:28 UTC (permalink / raw)
  To: nilesh khode <nilesh_khode@yahoo.com>; +Cc: pgsql-admin

HI.

Can you try (just for testing) using a jdbc2 driver and a connection
string with "protocolVersion=2" included in the jdbc connection?

I had the same issue with this and the problem is, that the query
optimizer sometimes decides NOT to use indexes on prepared statements
(the newer JDBC drivers create use them) and bound variables.

I have a solution now for WebObjects but this might not help you.

cug

2006/9/5, nilesh khode <nilesh_khode@yahoo.com>:

> But same I tested with JDBC, the query requires more time as compared with
> existing server.

-- 
PostgreSQL Bootcamp, Big Nerd Ranch Europe, Nov 2006
http://www.bignerdranch.com/news/2006-08-21.shtml



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2006-09-05 15:28 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-05 15:08 DB Performance Tuning nilesh khode <nilesh_khode@yahoo.com>
2006-09-05 15:24 ` romje@javaxpert.com
2006-09-05 15:28 ` Guido Neitzer <guido.neitzer@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox