Machine unresponsive with cache_lock: blocked on... message

[elekktretterr at exemail.com.au]

> Still a bit fuzzy on some the related issues that might be at play here,
> but as I understand it, the postgres data cache uses as much shared
> memory as it 'wants' (e.g. up to max configured) to hold the database
> data - so if this machine was up for a long time, potentially the entire
> database data set is in memory -
>
> and if the data set isn't used very often, it might get swapped out by
> something else running or just from inactivity ..
>
> If you want to keep this from happening, you can set:
>
> kern.ipc.shm_use_phys=1
>
> in /boot/loader.conf


Here is the situation: Machine 1: FBSD 7.2. Machine 2: DragonFly. Both
boxes have 4gb memory. Both run postgresql 8.4 and their configuration is
same: 800MB shared memory(actually the FBSD box has 1GB shared mem), 2GB
effective cache size, 128MB work mem.

The database data set is about 2-3GB on both boxes(actually they replicate
exactly the same database). The FBSD box also runs MySQL and Apache(and
known memory hogs like Ruby on Rails). Yet this box always has at least
has few hundred MB free memory. Ive *never* seen it swap, and if it ever
did, then only briefly.

top from the fbsd box:
Mem: 777M Active, 2365M Inact, 383M Wired, 26M Cache, 399M Buf, 395M Free
Swap: 4096M Total, 4096M Free

top from the DF box:
Memory: 878M Active, 1962M Inact, 516M Wired, 146M Cache, 188M Buf, 6132K
Free
Swap: 4096M Total, 10M Used, 4086M Free

ps axl from the fbsd box:

   70   740   737   0  44  0 1052056 210136 select Ss    ??    6:06.89
postgres: writer process    (postgres)
   70   741   737   0  44  0 1052056  6004 select Ss    ??    3:45.14
postgres: wal writer process    (postgres)
   70   742   737   0  44  0 1053080  5268 select Ss    ??    2:06.09
postgres: autovacuum launcher process    (postgres)
   70   743   737   0  44  0 22576  4796 select Ss    ??    2:56.13
postgres: stats collector process    (postgres)
   70 96486   737   0   4  0 1055128 17012 sbwait Is    ??    0:00.05
postgres: aubill aubill_production 203.xx.xxx.xxx(55645)  (postgres)
   70 96781   737   0   4  0 1054104 12152 sbwait Is    ??    0:00.01
postgres: aubill aubill_production 203.xx.xxx.xxx(61963)  (postgres)
   70 96784   737   0   4  0 1054104 13064 sbwait Is    ??    0:00.02
postgres: aubill aubill_production 203.xx.xxx.xxx(63374)  (postgres)


ps axl from the DF box:
 1002 22890 22888   0 152   0 853324 15996 select SLs   ??    0:09.07
postgres: writer process    (postgres)
 1002 22891 22888   0 152   0 853132 4940 select SLs   ??    0:09.15
postgres: wal writer process    (postgres)
 1002 22892 22888   0 152   0 853420 2064 select SLs   ??    0:02.81
postgres: autovacuum launcher process    (postgres)
 1002 22893 22888   0 152   0  9108 1440 poll   SLMs  ??    0:10.35
postgres: stats collector process    (postgres)
 1002 27196 22888   0 152   0 854628 10444 sbwait ILs   ??    0:00.06
postgres: aubill aubill_production xxx.xxx.xxx.xx(56668) idle (postgres)
 1002 27260 22888   0 152   0 855156 507492 sbwait ILs   ??    0:24.36
postgres: aubill aubill_production 203.xxx.xxx.xxx(58165) idle (postgres)
 1002 27274 22888   0 152   0 854084 4740 sbwait ILs   ??    0:00.00
postgres: aubill aubill_production 203.xxx.xxx.xxx(64789) idle (postgres)
 1002 27275 22888   1 152   0 854020 4748 sbwait ILs   ??    0:00.00
postgres: aubill aubill_production 203.xxx.xxx.xxx(60636) idle (postgres)