On 5/16/06, Sean Davis <sdavis2@mail.nih.gov> wrote:
> I had cranked things up a bit from the standard install.
>
> shared_buffers = 15000 # min 16 or max_connections*2, 8KB
> each
> #temp_buffers = 1000 # min 100, 8KB each
> #max_prepared_transactions = 50 # can be 0 or more
> work_mem = 10000 # min 64, size in KB
> maintenance_work_mem = 128000 # min 1024, size in KB
> max_stack_depth = 4096 # min 100, size in KB
>
> Some of these may not be ideal, but it really improved performance for our
> needs.
>
> Suggested by the lack of a stats collector process or stats buffer process,
> I restarted the server, and it appears to have fixed the issue for now. At
> least I know what to watch for now.
We found the same thing - restarting PostgreSQL made the problem go
away. However, we were able to duplicate the problem. Tuning our
kernel memory options and postgresql.conf options down made the
problem occur less frequently. We had 8GB or RAM in the box and were
running 10.3.9. Here were the kernel options we were using...
kern.sysv.shmmax=1073741824
kern.sysv.shmmin=1
kern.sysv.shmmni=64
kern.sysv.shmseg=16
kern.sysv.shmall=262144 #<-- num of pages (a page is 4096 bytes)
ceil(shmmax/4096)
and here's the lowest settings we tried in the postgresql.conf:
shared_buffers = 25000 # min 16, at least max_connections*2, 8KB each
work_mem = 2048 # min 64, size in KB
maintenance_work_mem = 1024 # min 1024, size in KB
With the above settings, we could still "kill" the stats collector.