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/