From: Bill Moran <wmoran@collaborativefusion.com>
To: Jozsef Szalay <jszalay@storediq.com>
Cc: Pavel Stehule <pavel.stehule@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Simple select hangs while CPU close to 100%
Date: Wed, 25 Jul 2007 16:28:34 -0400
Message-ID: <20070725162834.c18bd204.wmoran@collaborativefusion.com> (raw)
In-Reply-To: <E387E2E9622FDD408359F98BF183879EE028F5@dc1.storediq.com>
References: <E387E2E9622FDD408359F98BF183879EE02634@dc1.storediq.com>
<162867790707220852q2369eb3fi2c268925d1bd198@mail.gmail.com>
<E387E2E9622FDD408359F98BF183879EE028E3@dc1.storediq.com>
<20070725141220.c16473f2.wmoran@collaborativefusion.com>
<E387E2E9622FDD408359F98BF183879EE028F5@dc1.storediq.com>
In response to "Jozsef Szalay" <jszalay@storediq.com>:
> Our application is such that any update to the database is done by a
> single session in a batch process using bulk load. The frequency of
> these usually larger scale updates is variable but an update runs every
> 2-3 days on average.
>
> Originally a plain VACUUM ANALYZE was executed on every affected table
> after every load.
Any other insert/update activity outside of the bulk loads? What's
the vacuum policy outside the bulk loads? You say originally, does
it still do so?
I agree with Pavel that the output of vacuum verbose when the problem
is occurring would be helpful.
> VACUUM FULL ANALYZE is scheduled to run on a weekly basis.
If you need to do this, then other settings are incorrect.
> I do understand the need for vacuuming. Nevertheless I expect Postgres
> to return data eventually even if I do not vacuum. In my case, the
> simple SELECT COUNT(*) FROM table; statement on a table that had around
> 100K "live" rows has not returned the result for more than 6 hours after
> which I manually killed it.
It should, 6 hours is too long for that process, unless you're running
a 486dx2. You didn't mention your hardware or your postgresql.conf
settings. What other activity is occurring during this long count()?
Can you give us a shot of the iostat output and/or top during this
phenomenon?
>
> Jozsef
>
>
> -----Original Message-----
> From: Bill Moran [mailto:wmoran@collaborativefusion.com]
> Sent: Wednesday, July 25, 2007 1:12 PM
> To: Jozsef Szalay
> Cc: Pavel Stehule; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Simple select hangs while CPU close to 100%
>
> 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.
>
> 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.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
--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/
wmoran@collaborativefusion.com
Phone: 412-422-3463x4023
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: wmoran@collaborativefusion.com, jszalay@storediq.com, pavel.stehule@gmail.com
Subject: Re: Simple select hangs while CPU close to 100%
In-Reply-To: <20070725162834.c18bd204.wmoran@collaborativefusion.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox