On 5/16/06, Sean Davis <[EMAIL PROTECTED]> 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.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
