Re: Nooby Q: Should this take five hours? And counting?

Scott Marlowe wrote:
> On Sat, Apr 18, 2009 at 6:32 PM, Kenneth Tilton <kentilton@gmail.com> wrote:
>>
>> Scott Marlowe wrote:
>>> On Sat, Apr 18, 2009 at 4:11 PM, Kenneth Tilton <kentilton@gmail.com>
>>> wrote:
>>>> ie, 5hrs and counting, no clue how long it intends to run, but methinks
>>>> this
>>>> is insane even if it is 10^7 records, mebbe half a dozen dups per value
>>>> (a
>>>> product-id usually around 8-chars long):
>>>>
>>>> CREATE INDEX web_source_items_by_item_id_strip
>>>>               ON web_source_items
>>>>               USING btree (item_id_strip);
>>>>
>>>> Am I unreasonably impatient?
>>>>
>>>> I see pg getting just a few % CPU on a heavily ram/core-endowed Sun box
>>>> with
>>>> nothing else going on. Mebbe they installed pg on a compact flash?
>>>> DVD-RW?
>>>> /usr/local/something, prolly not.
>>> What does vmstat 1 60 say during the index build?  Specifically the
>>> cpu columns for user, system, wa?
>> uh-oh, Unix noob too, and unfortunately someone has jumped on with a
>> CPU-intensive task pegging one of the cores at 100%, so these numbers prolly
>> do not help, but here goes:
>>
>> procs -----------memory---------- ---swap-- -----io---- --system--
>> -----cpu------
>>  r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id
>> wa st
>>  1  1 2076312 1503204 182152 30669308   49   69   260   299    3    3 28  2
>> 63  7  0
>>  1  1 2076312 1502900 182152 30669656    0    0   192  2260 1198  332 25  1
>> 50 24  0
>>  1  1 2076312 1503024 182152 30669656    0    0     0   704 1181  282 25  1
>> 50 25  0
>>  1  3 2076312 1502904 182156 30669740    0    0   104  2780 1224  422 25  0
>> 48 26  0
>>  1  3 2076312 1502896 182156 30669740    0    0     0  1552 1173  309 25  0
>>
>> I'll sample again if I get a window, but these jobs tend to run for hours.
>
> I'm gonna take a guess about a few things:
> 1: You've got a lot of memory in that machine, try cranking up
> work_mem for this query to see if that helps

A bit. I killed the indexing and jacked work_mem up to 500mb, indexing
then finished in 7 min 25s.

Yer a genius!

> 2: You've got a slow disk subsystem, if you're already seeing 25%
> IOWait with only ~2 to 3 megs a second being written.

This has been passed along to management for consideration.

Thx a ton,

ken