From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jerry Sievers <jerry@jerrysievers.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Curious run-away index build on upgrade to 8.1.3
Date: Thu, 16 Mar 2006 10:32:35 -0500
Message-ID: <16725.1142523155@sss.pgh.pa.us> (raw)
In-Reply-To: <m3pskmmp0a.fsf@prod01.jerrysievers.com>
References: <m3pskmmp0a.fsf@prod01.jerrysievers.com>
Jerry Sievers <jerry@jerrysievers.com> writes:
> What happened was that for a couple minutes the CPU load would
> steadily increase and disk activity decrease at the same time. Before
> long, one CPU is 100% busy and we let this continue for 2 hours, a
> 100x longer than this index usually takes to build. Disk IO dropped
> to nothing and remained so.
Hm, we were just doing some work in this area a week or two ago, and
8.2 should be materially faster than current releases ... but offhand
I don't know why 8.1 would be any worse than earlier versions. Looking
in the CVS history shows that the sort logic didn't change at all
between 8.0 and 8.1. Are you sure index build on this index really
performs differently than it did in 8.0? What platform is this on?
> Worse is that the backend that was spinning would not respond to a
> cancel nor SIGTERM. Stopping this activity required a -m immediate
> shutdown of Postgres.
Yeah, we also noticed last week that some of the major loops in btree
index build were free of any CHECK_FOR_INTERRUPTS calls :-(. This is
fixed for 8.1.4.
> If it would be of interest to someone that I truss one of the spinning
> processes, I can redo this in an R&D setting and submit the results.
truss probably wouldn't show anything interesting; a gprof or oprofile
profile could be useful though.
regards, tom lane
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, jerry@jerrysievers.com
Subject: Re: Curious run-away index build on upgrade to 8.1.3
In-Reply-To: <16725.1142523155@sss.pgh.pa.us>
References: <m3pskmmp0a.fsf@prod01.jerrysievers.com> <16725.1142523155@sss.pgh.pa.us>
* 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