Re: For index bloat: VACUUM ANALYZE vs REINDEX/CLUSTER

On 18/09/2007, Sander Steffann <s(dot)steffann(at)computel(dot)nl> wrote:
> Hi,
>
> > Can I add SHM with merely by managing the entry in sysctl.conf? My
> > current values:
> >
> > kernel.shmmax = 536870912
> > kernel.shmall = 536870912
> >
> > My "shared_buffers" in postgresql.conf is "20000". From the website
> > http://www.desknow.com/kb/idx/12/061/article/ I notice that shmmax
> > should be sharedbuffer*8192, so I suppose my shmmax can be much lower
> > than the above, but I raised it for performance. Am I wrong to do so?
>
> You need to configure the kernel so it allows processes to use more shared
> memory. This does not mean that a process automatically uses it. For
> PostgreSQL you will need to increase shared_buffers to make it use the extra
> available shared memory. With your shared memory settings you can probably
> increase shared_buffers to about 65000.
>

Thanks, the IPCS command shows me this:

------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x0052e2c1 6782976 postgres 600 176668672 2

Now, I can merrily increase the shared_buffers, but the manual warns
me against increasing the value too much because it is "per
transaction" value.

So here's the conflict for a novice like me:

1. Do not increase shared_buffer too much because it is per-transaction.
2. Do increase the SHM for performance, but it is only useful if you
also increase shared_buffer.

So which is it?

Would it help to increase the effective_cache_size? It is currently at
"512000".

I have 4GB ram on the machine, but am willing to devote about 2GB to pgsql.

Thanks!