pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedmax_fsm_pages, shared_buffers and checkpoint_segments
8+ messages / 6 participants
[nested] [flat]
* max_fsm_pages, shared_buffers and checkpoint_segments
@ 2007-05-23 16:22 Y Sidhu <ysidhu@gmail.com>
0 siblings, 3 replies; 8+ messages in thread
From: Y Sidhu @ 2007-05-23 16:22 UTC (permalink / raw)
To: pgsql-performance
I am a newbie, as you all know, but I am still embarassed asking this
question. I started my tuning career by changing shared_buffers. Soon I
discovered that I was hitting up against the available RAM on the system.
So, I brought the number down. Then I discovered max_fsm_pages. I could take
that up quite high and found out that it is a 'disk' thing. Then I started
increasing checkpoint_segments,which is also a disk thing. However, setting
it to 25, and then increasing any of the other 2 variables, the postgresql
daemon stops working. meaning it does not start upon reboot. When I bring
shared_buffers or max_fsm_pages back down, the daemon starts and all is
normal. This happens on a 1 GB RAM machine and a 4 GB RAM machine.
Anyone know what I am doing wrong?
System: FreeBSD 6.1, Postgresql 8.09, 2 GB RAM
--
Yudhvir Singh Sidhu
408 375 3134 cell
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: max_fsm_pages, shared_buffers and checkpoint_segments
@ 2007-05-23 16:31 Ben <bench@silentmedia.com>
parent: Y Sidhu <ysidhu@gmail.com>
2 siblings, 1 reply; 8+ messages in thread
From: Ben @ 2007-05-23 16:31 UTC (permalink / raw)
To: Y Sidhu <ysidhu@gmail.com>; +Cc: pgsql-performance
Do you have an overall plan (besides "make it go faster!") or are you
just trying out the knobs as you find them?
This may be helpful:
http://www.powerpostgresql.com/Downloads/annotated_conf_80.html
On May 23, 2007, at 9:22 AM, Y Sidhu wrote:
> I am a newbie, as you all know, but I am still embarassed asking
> this question. I started my tuning career by changing
> shared_buffers. Soon I discovered that I was hitting up against the
> available RAM on the system. So, I brought the number down. Then I
> discovered max_fsm_pages. I could take that up quite high and found
> out that it is a 'disk' thing. Then I started increasing
> checkpoint_segments,which is also a disk thing. However, setting it
> to 25, and then increasing any of the other 2 variables, the
> postgresql daemon stops working. meaning it does not start upon
> reboot. When I bring shared_buffers or max_fsm_pages back down, the
> daemon starts and all is normal. This happens on a 1 GB RAM machine
> and a 4 GB RAM machine.
>
> Anyone know what I am doing wrong?
>
> System: FreeBSD 6.1, Postgresql 8.09, 2 GB RAM
>
> --
> Yudhvir Singh Sidhu
> 408 375 3134 cell
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: max_fsm_pages, shared_buffers and checkpoint_segments
@ 2007-05-23 16:43 Y Sidhu <ysidhu@gmail.com>
parent: Ben <bench@silentmedia.com>
0 siblings, 2 replies; 8+ messages in thread
From: Y Sidhu @ 2007-05-23 16:43 UTC (permalink / raw)
To: Ben <bench@silentmedia.com>; +Cc: pgsql-performance
I cannot answer that question on the grounds that it may incriminate me.
Hehe. I am really trying to get our vacuum times down. The cause of the
problem, I believe, are daily mass deletes. Yes, I am working on performing
vacuums more than once a day. No, I am not considering partitioning the
offending table because a few scripts have to be changed. I am also turning
the knobs as I find them.
Any help is appreciated b'cause "I can't hold er t'gether much longer
kap'n." Sorry, that's the best 'Scotty' I can do this morning.
Yudhvir
=============
On 5/23/07, Ben <bench@silentmedia.com> wrote:
>
> Do you have an overall plan (besides "make it go faster!") or are you
> just trying out the knobs as you find them?
>
> This may be helpful:
> http://www.powerpostgresql.com/Downloads/annotated_conf_80.html
>
> On May 23, 2007, at 9:22 AM, Y Sidhu wrote:
>
> > I am a newbie, as you all know, but I am still embarassed asking
> > this question. I started my tuning career by changing
> > shared_buffers. Soon I discovered that I was hitting up against the
> > available RAM on the system. So, I brought the number down. Then I
> > discovered max_fsm_pages. I could take that up quite high and found
> > out that it is a 'disk' thing. Then I started increasing
> > checkpoint_segments,which is also a disk thing. However, setting it
> > to 25, and then increasing any of the other 2 variables, the
> > postgresql daemon stops working. meaning it does not start upon
> > reboot. When I bring shared_buffers or max_fsm_pages back down, the
> > daemon starts and all is normal. This happens on a 1 GB RAM machine
> > and a 4 GB RAM machine.
> >
> > Anyone know what I am doing wrong?
> >
> > System: FreeBSD 6.1, Postgresql 8.09, 2 GB RAM
> >
> > --
> > Yudhvir Singh Sidhu
> > 408 375 3134 cell
>
>
--
Yudhvir Singh Sidhu
408 375 3134 cell
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: max_fsm_pages, shared_buffers and checkpoint_segments
@ 2007-05-23 17:00 Ben <bench@silentmedia.com>
parent: Y Sidhu <ysidhu@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Ben @ 2007-05-23 17:00 UTC (permalink / raw)
To: Y Sidhu <ysidhu@gmail.com>; +Cc: pgsql-performance
Mass deletes are expensive to clean up after. Truncates are better if
you can, but, as it sounds like you can't, you might look into
vacuum_cost_delay and its many variables. It will make your vacuums
run longer, not shorter, but it will also make them have less of an
impact, if you configure it properly for your workload.
As you've found out, it's probably better not to poke things randomly
in the hope of making it faster.
On May 23, 2007, at 9:43 AM, Y Sidhu wrote:
> I cannot answer that question on the grounds that it may
> incriminate me. Hehe. I am really trying to get our vacuum times
> down. The cause of the problem, I believe, are daily mass deletes.
> Yes, I am working on performing vacuums more than once a day. No, I
> am not considering partitioning the offending table because a few
> scripts have to be changed. I am also turning the knobs as I find
> them.
>
> Any help is appreciated b'cause "I can't hold er t'gether much
> longer kap'n." Sorry, that's the best 'Scotty' I can do this morning.
>
> Yudhvir
> =============
>
> On 5/23/07, Ben <bench@silentmedia.com> wrote:
> Do you have an overall plan (besides "make it go faster!") or are you
> just trying out the knobs as you find them?
>
> This may be helpful:
> http://www.powerpostgresql.com/Downloads/annotated_conf_80.html
>
> On May 23, 2007, at 9:22 AM, Y Sidhu wrote:
>
> > I am a newbie, as you all know, but I am still embarassed asking
> > this question. I started my tuning career by changing
> > shared_buffers. Soon I discovered that I was hitting up against the
> > available RAM on the system. So, I brought the number down. Then I
> > discovered max_fsm_pages. I could take that up quite high and found
> > out that it is a 'disk' thing. Then I started increasing
> > checkpoint_segments,which is also a disk thing. However, setting it
> > to 25, and then increasing any of the other 2 variables, the
> > postgresql daemon stops working. meaning it does not start upon
> > reboot. When I bring shared_buffers or max_fsm_pages back down, the
> > daemon starts and all is normal. This happens on a 1 GB RAM machine
> > and a 4 GB RAM machine.
> >
> > Anyone know what I am doing wrong?
> >
> > System: FreeBSD 6.1, Postgresql 8.09, 2 GB RAM
> >
> > --
> > Yudhvir Singh Sidhu
> > 408 375 3134 cell
>
>
>
>
> --
> Yudhvir Singh Sidhu
> 408 375 3134 cell
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: max_fsm_pages, shared_buffers and checkpoint_segments
@ 2007-05-23 17:56 PFC <lists@peufeu.com>
parent: Y Sidhu <ysidhu@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: PFC @ 2007-05-23 17:56 UTC (permalink / raw)
To: Y Sidhu <ysidhu@gmail.com>; pgsql-performance
> When I bring shared_buffers or max_fsm_pages back down, the daemon
> starts and all is
> normal.
Linux has a system setting for the maximum number of shared memory that a
process can allocate. When Postgres wants more, Linux says "No."
Look in the docs for the setting (sysctl whatsisname).
VACUUM VERBOSE will tell you if you need to put more max_fsm_pages or not.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: max_fsm_pages, shared_buffers and checkpoint_segments
@ 2007-05-23 20:40 Peter Schuller <peter.schuller@infidyne.com>
parent: Y Sidhu <ysidhu@gmail.com>
2 siblings, 1 reply; 8+ messages in thread
From: Peter Schuller @ 2007-05-23 20:40 UTC (permalink / raw)
To: Y Sidhu <ysidhu@gmail.com>; +Cc: pgsql-performance
> increasing checkpoint_segments,which is also a disk thing. However, setting
> it to 25, and then increasing any of the other 2 variables, the postgresql
> daemon stops working. meaning it does not start upon reboot. When I bring
Sounds like you need to increase your shared memory limits.
Unfortunately this will require a reboot on FreeBSD :(
See:
http://www.postgresql.org/docs/8.2/static/kernel-resources.html
Last time I checked PostgreSQL should be complaining about the shared
memory on startup rather than silently fail though. Check your logs
perhaps. Though I believe the RC script will cause the message to be
printed interactively at the console too, if you run it. (Assuming you
are using it installed from ports).
--
/ Peter Schuller
PGP userID: 0xE9758B7D or 'Peter Schuller <peter.schuller@infidyne.com>'
Key retrieval: Send an E-Mail to getpgpkey@scode.org
E-Mail: peter.schuller@infidyne.com Web: http://www.scode.org
Attachments:
signature.asc (application/pgp-signature, 249B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: max_fsm_pages, shared_buffers and checkpoint_segments
@ 2007-05-24 04:27 Heikki Linnakangas <heikki@enterprisedb.com>
parent: Y Sidhu <ysidhu@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Heikki Linnakangas @ 2007-05-24 04:27 UTC (permalink / raw)
To: Y Sidhu <ysidhu@gmail.com>; +Cc: Ben <bench@silentmedia.com>; pgsql-performance
Y Sidhu wrote:
> I cannot answer that question on the grounds that it may incriminate me.
> Hehe. I am really trying to get our vacuum times down. The cause of the
> problem, I believe, are daily mass deletes. Yes, I am working on performing
> vacuums more than once a day. No, I am not considering partitioning the
> offending table because a few scripts have to be changed. I am also turning
> the knobs as I find them.
Yudhvir, I don't think the tuning options are going to make any
difference to your vacuum times.
I don't know if this been brought up already, but the way vacuum works
in 8.1 and 8.2 is that when it scans the table for the second time, it
does a WAL flush for every block that had deleted tuples on it. That's
really expensive, in particular if you don't have a separate drive for
the WAL, and/or you don't have a battery backed up cache in your controller.
You could try turning fsync=off to see if it helps, but be warned that
that's dangerous. If you have a power failure etc. while the database is
busy, you can get data corruption. So do that to see if it helps on a
test matchine, and if it does, put WAL on another drive or get a
controller with battery backed up cache. Or wait until release 8.3,
which should fix that issue.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: max_fsm_pages, shared_buffers and checkpoint_segments
@ 2007-05-31 21:23 Vivek Khera <vivek@khera.org>
parent: Peter Schuller <peter.schuller@infidyne.com>
0 siblings, 0 replies; 8+ messages in thread
From: Vivek Khera @ 2007-05-31 21:23 UTC (permalink / raw)
To: pgsql-performance
On May 23, 2007, at 4:40 PM, Peter Schuller wrote:
> Sounds like you need to increase your shared memory limits.
> Unfortunately this will require a reboot on FreeBSD :(
No, it does not. You can tune some of the sysv IPC parameters at
runtime. the shmmax and shmall are such parameters.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2007-05-31 21:23 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-23 16:22 max_fsm_pages, shared_buffers and checkpoint_segments Y Sidhu <ysidhu@gmail.com>
2007-05-23 16:31 ` Ben <bench@silentmedia.com>
2007-05-23 16:43 ` Y Sidhu <ysidhu@gmail.com>
2007-05-23 17:00 ` Ben <bench@silentmedia.com>
2007-05-24 04:27 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-05-23 17:56 ` PFC <lists@peufeu.com>
2007-05-23 20:40 ` Peter Schuller <peter.schuller@infidyne.com>
2007-05-31 21:23 ` Vivek Khera <vivek@khera.org>
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