Hi Pavel,
Yes I did vacuum. In fact the only way to "fix" this problem is
executing a "full" vacuum. The plain vacuum did not help.
Regards,
Jozsef
-----Original Message-----
From: Pavel Stehule [mailto:pavel.stehule@gmail.com]
Sent: Sunday, July 22, 2007 10:53 AM
To: Jozsef Szalay
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Simple select hangs while CPU close to 100%
Hello
did you vacuum?
It's good technique do vacuum table after remove bigger number of rows.
Regards
Pavel Stehule
2007/7/22, Jozsef Szalay <jszalay@storediq.com>:
>
>
>
>
> 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
