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: (view raw, whole thread or download thread mbox)
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


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



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!



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-2017 The PostgreSQL Global Development Group