Hi. I have a problem on one of our production servers. A fairly
complicated query is running, and the backend process is using 30 GB of
RAM. The machine only has 32GB, and is understandably swapping like crazy.
My colleague is creating swap files as quickly as it can use them up.
The work_mem setting on this machine is 1000MB, running Postgres 8.3.0.
Here is an excerpt from top:
top - 15:54:17 up 57 days, 6:49, 3 users, load average: 20.17, 21.29, 16.31
Tasks: 250 total, 2 running, 248 sleeping, 0 stopped, 0 zombie
Cpu(s): 3.1%us, 2.5%sy, 0.0%ni, 15.2%id, 78.7%wa, 0.0%hi, 0.5%si, 0.0%st
Mem: 32961364k total, 32898588k used, 62776k free, 22440k buffers
Swap: 8096344k total, 8096344k used, 0k free, 6056472k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
27192 postgres 18 0 30.6g 22g 1984 R 31 71.7 32:20.09 postgres: flymine production-release-15.0
192.168.128.84(33736)INSERT
650 root 10 -5 0 0 0 S 5 0.0 13:56.10 [kswapd2]
5513 postgres 15 0 130m 19m 364 S 4 0.1 1067:04 postgres: stats collector process
957 root 10 -5 0 0 0 D 1 0.0 1:39.13 [md2_raid1]
649 root 10 -5 0 0 0 D 1 0.0 14:14.95 [kswapd1]
28599 root 15 0 0 0 0 D 1 0.0 0:01.25 [pdflush]
648 root 10 -5 0 0 0 S 0 0.0 15:10.68 [kswapd0]
2585 root 10 -5 0 0 0 D 0 0.0 67:15.89 [kjournald]
The query that is being run is an INSERT INTO table SELECT a fairly
complex query.
Any ideas why this is going so badly, and what I can do to solve it?
Matthew
--
First law of computing: Anything can go wro
sig: Segmentation fault. core dumped.
