Skip site navigation (1) Skip section navigation (2)

Simple select hangs while CPU close to 100%

From: "Jozsef Szalay" <jszalay(at)storediq(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Simple select hangs while CPU close to 100%
Date: 2007-07-22 15:29:04
Message-ID: E387E2E9622FDD408359F98BF183879EE02634@dc1.storediq.com (view raw or flat)
Thread:
Lists: pgsql-performance
I'm having this very disturbing problem. I got a table with about
100,000 rows in it. Our software deletes the majority of these rows and
then bulk loads another 100,000 rows into the same table. All this is
happening within a single transaction. I then perform a simple "select
count(*) from ..." statement that never returns. In the mean time, the
backend Postgres process is taking close to 100% of the CPU. The hang-up
does not always happen on the same statement but eventually it happens 2
out of 3 times. If I dump and then restore the schema where this table
resides the problem is gone until the next time we run through the whole
process of deleting, loading and querying the table.

 

There is no other activity in the database. All requested locks are
granted. 

 

Has anyone seen similar behavior? 

 

Some details:

 

Postgres v 8.1.2

Linux Fedora 3

 

shared_buffers = 65536

temp_buffers = 32768

work_mem = 131072 

maintenance_work_mem = 131072

max_stack_depth = 8192

max_fsm_pages = 40000

wal_buffers = 16

checkpoint_segments = 16

 

 

top reports

 

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND

19478 postgres  25   0  740m 721m 536m R 99.7  4.4 609:41.16 postmaster

 

ps -ef | grep postgres reports 

 

postgres 19478  8061 99 00:11 ?        10:13:03 postgres: user dbase
[local] SELECT

 

strace -p 19478 

no system calls reported

 

 

Thanks for the help!

Jozsef

Responses

pgsql-performance by date

Next:From: Pavel StehuleDate: 2007-07-22 15:52:34
Subject: Re: Simple select hangs while CPU close to 100%
Previous:From: Tom LaneDate: 2007-07-21 04:26:10
Subject: Re: User concurrency thresholding: where do I look?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group