Re: slow building index and reference after Sybase to Pg - Mailing list pgsql-general

  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).
>>
>> Thanks,
>> Gary
>>
>
> In addition to Toms answer, disable fsync for a bit.
>
> ( http://www.postgresql.org/docs/9.0/static/non-durability.html )
>
> Also, why one at a time?  Are you IO bound?  If you are IO bound then
> ok, but otherwise try a few at a time.  (I mean COPY, create index,
> and add constraint)
>
> While this was going on, did you view vmstat?  Did you look at PG's log?
>
>
> -Andy

Thanks for your information.  Here are more information about my situation:

Below is the listing of the time for creating the references after we
ported the tables and built the indexes with the following configuration
info:

wal_buffers = 8MB
checkpoint_segments = 30
effective_cache_size = 21GB
maintenance_work_mem = 1GB
fsync = on

          5.301638 min    FK_FILE_REF_FILETYPE
          7.250384 min    FK_PGE_REF_PGE_DEF
         15.024702 min    FK_FILESONDISKLOST_REF_FILE
         21.143256 min    FK_FILEEXPORTED_REF_FILE
         22.404361 min    FK_PGE_INPUTFILE_REF_PGE
         23.439486 min    FK_FMC_METFILEID_REF_FILE
         24.942795 min    FK_FM_ARCHIVESET_REF_FMC
         33.286959 min    FK_PGE_LOGFILE_PCF_REF_FILE
         46.875006 min    FK_FILEMETA_NV_REF_FMC
         51.223537 min    FK_FM_BJ_REF_FMC
         52.603217 min    FK_FM_L1L2_REF_FMC
         73.314357 min    FK_FM_L3L4T_REF_FMC
         76.118838 min    FK_FMC_REF_PGE
         89.317196 min    FK_FMC_REF_FM_ALL
        248.595640 min    FK_EMS_FILES_REF_FILE
        258.633713 min    FK_EXPORT_FILES_REF_FILE
        269.605100 min    FK_FILESONDISK_REF_FILE
        299.187822 min    FK_FILEREQHF_REF_FILE
        331.076144 min    FK_FILESNOTON_REF_FILE
        334.494474 min    FK_FM_ALL_REF_FILE
        608.402847 min    FK_PGE_INPUTFILE_REF_FILE


We changed with the following configuration and tried to rebuild some of the references with worse results:

wal_buffers = 16MB
checkpoint_segments = 256
effective_cache_size = 30GB
maintenance_work_mem = 2GB
fsync = on


          75  min    FK_FM_L1L2_REF_FMC          (52 min previous)
          311 min    FK_EXPORT_FILES_REF_FILE    (258 min previous)
         still running    FK_FM_ALL_REF_FILE


We are also going to run parallel (2 refs) at at a time to see what happen.

Also, after that we are going to try Andy's suggestion to set fsync = off.

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@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


Any other suggestions that I should try ?

By the way, as far as I know that Sybase does not check the reference for
each records when creating the reference.  Is there a way for pg to do the
same ?

Thanks,
Gary