From: Jerry Sievers <jerry@jerrysievers.com>
To: pgsql-admin@postgresql.org
Subject: Curious run-away index build on upgrade to 8.1.3
Date: 16 Mar 2006 09:40:37 -0500
Message-ID: <m3pskmmp0a.fsf@prod01.jerrysievers.com> (raw)
Hello list; We stumbled on a maintenance_work_mem related problem with
index builds.
Recently imported a good sized DB into a new 8.1.3 install on platform
SunOS 5.9. The DB consists of some 400 tables and about twice as many
indexes.
The Upgrade went very smoothly until we hit an index build over a
table with approx 1.5 million records. The index field was type
bigint. We have a great many other tables of similar composition
which had no problem on index creation.
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.
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.
maintenance_work_mem parameter was traced down to be the problem.
It's initial setting was the same that we've been using on 8.0 with
apparent success.
And it would seem as if the maintenance_work_mem value could be set a
lot higher.
Nonetheless, I found the tipping point to be somewhere between 32 and
45k of the parameter setting.
A bit of info on the table;
1.5 million records, 1650 distinct values in index column,
Here's a mod 50 walk through the distribution of how many records
match each of the index field values;
select b from foo where a % 50 = 0 order by b;
b
------
1
2
2
4
6
10
15
21
30
37
44
56
68
84
99
117
149
190
228
261
298
340
423
486
593
717
853
1077
1302
1596
2470
3385
5739
(33 rows)
Any comments on this and/or request for additional diagnostics
welcome.
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.
Thank you!
--
-------------------------------------------------------------------------------
Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant
305 321-1144 (mobile http://www.JerrySievers.com/
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: jerry@jerrysievers.com
Subject: Re: Curious run-away index build on upgrade to 8.1.3
In-Reply-To: <m3pskmmp0a.fsf@prod01.jerrysievers.com>
References: <m3pskmmp0a.fsf@prod01.jerrysievers.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