Re: Simple select hangs while CPU close to 100% - Mailing list pgsql-performance

In response to "Jozsef Szalay" <jszalay@storediq.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.

Based on the information, I would expect that this problem is the result
of improper PG tuning, or inadequate server sizing (RAM, etc).

A table changing 100,000 rows shouldn't cause enough bloat to hurt
count(*)'s performance significantly, unless something else is wrong.

Some quick piddling around shows that tables with over a million rows
can count(*) the whole table in about 1/2 second on a system 2G of RAM.
A table with 13 mil takes a min and a half.  We haven't specifically
tuned this server for count(*) performance, as it's not a priority
for this database, so I expect the performance drop for the 13 mil
database is a result of exhausting shared_buffers and hitting the
disks.

> -----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
>
>
> ---------------------------(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@collaborativefusion.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.
****************************************************************