Re: Simple select hangs while CPU close to 100%

In response to "Jozsef Szalay" <jszalay(at)storediq(dot)com>:

> 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.

I read over my previous reply and picked up on something else ...

What is your vacuum _policy_? i.e. how often do you vacuum/analyze?
The fact that you had to do a vacuum full to get things back under
control tends to suggest that your current vacuum schedule is not
aggressive enough.

An explicit vacuum of this table after the large delete/insert may
be helpful.

> -----Original Message-----
> From: Pavel Stehule [mailto:pavel(dot)stehule(at)gmail(dot)com]
> Sent: Sunday, July 22, 2007 10:53 AM
> To: Jozsef Szalay
> Cc: pgsql-performance(at)postgresql(dot)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(at)storediq(dot)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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
>
>
>
>

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran(at)collaborativefusion(dot)com
Phone: 412-422-3463x4023

****************************************************************
IMPORTANT: This message contains confidential information and is
intended only for the individual named. If the reader of this
message is not an intended recipient (or the individual
responsible for the delivery of this message to an intended
recipient), please be advised that any re-use, dissemination,
distribution or copying of this message is prohibited. Please
notify the sender immediately by e-mail if you have received
this e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The
sender therefore does not accept liability for any errors or
omissions in the contents of this message, which arise as a
result of e-mail transmission.
****************************************************************