Re: postgres 8 settings - Mailing list pgsql-general

"vinita bansal" <sagivini@hotmail.com> writes:
> Can you please provide details on the relationship between work_mem and
> maintenance_work_mem (Postgres doc specifies that the value for maintenance
> work_mem should be larger but I am not clear on why that should be so)

Because the other way doesn't make sense.

Each active backend in a system may allocate up to work_mem for each
sort or hash it's currently doing (thus, possibly several times work_mem
for a complex query).  The operations that use maintenance_work_mem are
guaranteed not to use that more than once per backend, and in most
situations you'd not have many backends concurrently doing such
operations anyway.  So maintenance_work_mem can be a fairly large chunk
of your free RAM without driving the system into swapping, but setting
an equally large value of work_mem as a global default is just foolish.

In some cases you might want to raise work_mem temporarily (via SET) for
specific individual queries, but I wouldn't ever put a large value for
it in postgresql.conf.

> Am I right to say that work_mem is the same as sort_mem and
> maintenance_work_mem as vauum_mem in Postgres 7.4??

Yes, although maintenance_work_mem is used for more things than
vacuum_mem was used for in prior releases.

> Also, since I have 32GB RAM, setting wall_buffers as 1000 should not make a
> difference.

The point is that it's not going to help.  Why would you throw away RAM
to a useless purpose when it could be getting used for something useful?

Furthermore, depending on your platform, making the buffer area much
larger than necessary can actually be counterproductive.  You want all
of Postgres' shared memory to stay "hot" enough in the eyes of the
kernel that it won't get swapped out.  If wal_buffers is too large, then
as Postgres circularly re-uses the buffers, it will advance into memory
areas that it has not touched in a good while.  If the kernel decided to
swap that area out, it will have to swap it back in.  That's two I/O
operations utterly wasted (since in fact the old contents of the buffers
are no longer interesting, but the kernel has no way to know that).

I don't recommend tuning Postgres by randomly twiddling values you don't
understand the effects of.  Bigger is not always better.  Change one
thing at a time and measure the results.

> I am bothered about settings that might effect time taken by query execution
> (updates in particular)

Increasing checkpoint_segments (and possibly checkpoint_timeout) would
be more likely to help than increasing wal_buffers.

> Why do you think we need to create regular logs, only to handle database
> crashes??

Postgres is not designed on the assumption that that's an insignificant
consideration ;-)

Meanwhile, you didn't answer my question: what problems are you seeing?

            regards, tom lane