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

* PG slowdown
@ 2006-07-11 17:14 Abu Mushayeed <amushayeed@hotmail.com>
  2006-07-13 03:31 ` Re: PG slowdown adey <adey11@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Abu Mushayeed @ 2006-07-11 17:14 UTC (permalink / raw)
  To: pgsql-admin

Hello,

My hardware

CPU: Intel 3 GHZ X 4
Memory: 9 GB
LINUX

My conf file are as follows:

shared_buffers=10000
wal_buffers=128
vacuum_mem = 256000
sort_mem=32768
max_fsm_pages = 3000000
max_fsm_relations = 32768
checkpoint_segments = 2048
checkpoint_timeout = 1800
checkpoint_warning = 300
effective_cache_size =  262144
random_page_cost = 2
default_statistics_target = 250

Some of my queries that used to run within a minute, before I changed the 
"default_statistics_target" from 10 to 250, now runs for 20-30 minutes. Some 
queries did see improvement in their run time.

I have two questions:

1. Why did the other queries slowdown? I tried to tune them, as their is a 
join between two tables and it reads all the rows, I forced it to do a seq 
scan rather than index scan but it quadrupled it run time. Why?
2. How can I disable the logging of updates? What are the pitfalls? When an 
update or insert happening there are lot of logging going on and the system 
writes messages like this "2006-07-11 10:08:54 [2330] LOG:  checkpoints are 
occurring too frequently (182 seconds apart)
HINT:  Consider increasing the configuration parameter 
"checkpoint_segments"."

Thank you for the help.

Abu





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

* Re: PG slowdown
  2006-07-11 17:14 PG slowdown Abu Mushayeed <amushayeed@hotmail.com>
@ 2006-07-13 03:31 ` adey <adey11@gmail.com>
  2006-07-13 03:50   ` Re: PG slowdown Joshua D. Drake <jd@commandprompt.com>
  0 siblings, 1 reply; 3+ messages in thread

From: adey @ 2006-07-13 03:31 UTC (permalink / raw)
  To: Abu Mushayeed <amushayeed@hotmail.com>; +Cc: pgsql-admin

If you turn WAL logging off (via fsynch parameter in .conf), it is my
understanding that you will no longer log database updates, and will
therefore not be able to recover any uncommitted transactions in the event
of failure, (which seems contradictory to database practice).

Gut feel to me says your chckpoint_segments and timeout are too high.


On 7/12/06, Abu Mushayeed <amushayeed@hotmail.com> wrote:
>
> Hello,
>
> My hardware
>
> CPU: Intel 3 GHZ X 4
> Memory: 9 GB
> LINUX
>
> My conf file are as follows:
>
> shared_buffers=10000
> wal_buffers=128
> vacuum_mem = 256000
> sort_mem=32768
> max_fsm_pages = 3000000
> max_fsm_relations = 32768
> checkpoint_segments = 2048
> checkpoint_timeout = 1800
> checkpoint_warning = 300
> effective_cache_size =  262144
> random_page_cost = 2
> default_statistics_target = 250
>
> Some of my queries that used to run within a minute, before I changed the
> "default_statistics_target" from 10 to 250, now runs for 20-30 minutes.
> Some
> queries did see improvement in their run time.
>
> I have two questions:
>
> 1. Why did the other queries slowdown? I tried to tune them, as their is a
> join between two tables and it reads all the rows, I forced it to do a seq
> scan rather than index scan but it quadrupled it run time. Why?
> 2. How can I disable the logging of updates? What are the pitfalls? When
> an
> update or insert happening there are lot of logging going on and the
> system
> writes messages like this "2006-07-11 10:08:54 [2330] LOG:  checkpoints
> are
> occurring too frequently (182 seconds apart)
> HINT:  Consider increasing the configuration parameter
> "checkpoint_segments"."
>
> Thank you for the help.
>
> Abu
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org
>

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

* Re: PG slowdown
  2006-07-11 17:14 PG slowdown Abu Mushayeed <amushayeed@hotmail.com>
  2006-07-13 03:31 ` Re: PG slowdown adey <adey11@gmail.com>
@ 2006-07-13 03:50   ` Joshua D. Drake <jd@commandprompt.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Joshua D. Drake @ 2006-07-13 03:50 UTC (permalink / raw)
  To: adey <adey11@gmail.com>; +Cc: Abu Mushayeed <amushayeed@hotmail.com>; pgsql-admin

adey wrote:
> If you turn WAL logging off (via fsynch parameter in .conf), it is my 
> understanding that you will no longer log database updates, and will 
> therefore not be able to recover any uncommitted transactions in the 
> event of failure, (which seems contradictory to database practice).
>  

You can not turn of WAL logging. You can only delay how the data is 
written to disk.

Sincerely,

Joshua D. Drake



-- 

    === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
    Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/





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


end of thread, other threads:[~2006-07-13 03:50 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-11 17:14 PG slowdown Abu Mushayeed <amushayeed@hotmail.com>
2006-07-13 03:31 ` adey <adey11@gmail.com>
2006-07-13 03:50   ` Joshua D. Drake <jd@commandprompt.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