Scott Marlowe wrote:
On Sat, Apr 18, 2009 at 6:32 PM, Kenneth Tilton <kentilton@xxxxxxxxx> wrote:
Scott Marlowe wrote:
On Sat, Apr 18, 2009 at 4:11 PM, Kenneth Tilton <kentilton@xxxxxxxxx>
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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
