Re: slow building index and reference after Sybase to Pg

On 02/25/2011 05:11 PM, Gary Fu wrote:
> On 02/25/11 17:22, Andy Colson wrote:
>> On 2/23/2011 12:31 PM, Gary Fu wrote:
>>> Hi,
>>>
>>> I'm testing on converting a big Sybase db to Pg. It took about 45 hours
>>> to convert all sybase tables (bcp) to Pg (copy) without index and
>>> reference. After that I built the index (one by one, sequentially) and
>>> it took about 25 hours and then I started to add the references (one by
>>> one), however, it has been more than 30 hours and still has no sign of
>>> finishing. I wonder, is there any suggestion that may speed up the index
>>> and reference building (on Pg).
>>>
>
> By the way, I just did vmstat -n 1 with the following results (building the reference
> FK_FM_ALL_REF_FILE). However, I don't know how to interpret it.
>
> 6:02pm 116 gfu(at)moddblads:/dump/gfu> vmstat -n 1
> procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
> r b swpd free buff cache si so bi bo in cs us sy id wa st
> 0 1 556 137340 70280 48446004 0 0 10 24 0 0 0 0 100 0 0
> 0 1 556 132896 70296 48449828 0 0 4212 328 1655 1115 0 0 96 4 0
> 0 1 556 140768 70296 48442580 0 0 4240 28 1585 956 0 0 96 4 0
> 0 1 556 132368 70296 48451308 0 0 8424 0 1573 820 0 0 96 4 0
> 0 1 556 130800 70272 48452784 0 0 13536 0 1589 755 1 0 96 3 0
> 0 1 556 136148 70264 48447920 0 0 6344 0 1611 1082 0 0 96 4 0
> 0 1 556 132368 70280 48451416 0 0 3960 376 1492 829 0 0 96 4 0
> 0 1 556 135784 70284 48448180 0 0 8240 0 1856 957 0 0 96 4 0
> 0 1 556 139092 70288 48444668 0 0 8700 0 1828 917 1 0 96 4 0
> 0 1 556 134052 70292 48449608 0 0 5076 0 1566 880 0 0 96 4 0
> 0 1 556 140460 70276 48443636 0 0 5536 0 1509 724 0 0 96 4 0
> 0 1 556 131648 70300 48452340 0 0 8616 336 1598 826 0 0 96 4 0
> 1 0 556 135524 70284 48448112 0 0 8004 0 1588 836 0 0 96 4 0
>
>
>
> Thanks,
> Gary
>
>
>

wa is the time waiting for disk. Looks like 4%. id is idle time, looks like 96% idle.

I'm not sure what the system was doing, but doesnt look like it was using cpu time, or waiting for disk IO.

How many cores?

This box really have 30 gig of ram?

What sort of IO subsystem do you have? (raid? sata? scsi? nfs?)

I'll bet its network attached storage.

Looking at this, I'd say run all of them in parallel.

-Andy