Re: Nooby Q: Should this take five hours? And counting? - Mailing list pgsql-general

From Scott Marlowe
Subject Re: Nooby Q: Should this take five hours? And counting?
Date
Msg-id dcc563d10904182007o692e31a6n213854484a55f4a6@mail.gmail.com
Whole thread Raw
In response to Re: Nooby Q: Should this take five hours? And counting?  (Kenneth Tilton <kentilton@gmail.com>)
List pgsql-general
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

Looking at these numbers, it seems you've got 4 cores, 1 is running
100% user, 2 are sitting idle, and 1 is 100% io wait.  I'm guessing
the 100% iowait is the postgres backend running your query.

pgsql-general by date:

Previous
From: Kenneth Tilton
Date:
Subject: Re: Nooby Q: Should this take five hours? And counting?
Next
From: Magnus Hagander
Date:
Subject: Re: Community account