Curious run-away index build on upgrade to 8.1.3

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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/


[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux