Thread: slow building index and reference after Sybase to Pg

slow building index and reference after Sybase to Pg

From
Gary Fu
Date:
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

Re: slow building index and reference after Sybase to Pg

From
Tom Lane
Date:
Gary Fu <gfu@sigmaspace.com> writes:
> 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).

Have you read
http://www.postgresql.org/docs/9.0/static/populate.html
?  In particular I'm wondering what you have maintenance_work_mem
set to.

            regards, tom lane

Re: slow building index and reference after Sybase to Pg

From
Andy Colson
Date:
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

Re: slow building index and reference after Sybase to Pg

From
Gary Fu
Date:
  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



Re: slow building index and reference after Sybase to Pg

From
Andy Colson
Date:
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@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

Re: slow building index and reference after Sybase to Pg

From
Gary Fu
Date:
On 02/25/11 18:11, 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).
>>>
>>> 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
>
>
>
Here are the more test results on the 3rd ref and parallel on 2nd and
3rd references:

  325 mins on FK_FM_ALL_REF_FILE   (334 mins previous)

parallel results on 2nd and 3rd references: (much worse on 2nd ref)

    610 mins on FK_EXPORT_FILES_REF_FILE   (??? almost double the time)
    340 mins on FK_FM_ALL_REF_FILE

There are more than 250 millions records in table FileMeta_All and 80
millions records in table Export_Files that have references on the 280
millions records of File table on index FileId.

Here are some information on my system:

1:07pm 20 gfu@moddblads:/dump/gfu> free -m   (48MB of memory)
              total       used       free     shared    buffers     cached
Mem:         48036      47867        168          0        294      46960

1:08pm 21 gfu@moddblads:/dump/gfu> hinv
Total CPU's: 24
  Intel(R) Xeon(R) CPU           X5660  @ 2.80GHz  2793 MHZ
  Cache Size:  12288 KB

4:19pm 23 gfu@moddblads:/dump/gfu> df
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/sda1              8123168   5682344   2021532  74% /
/dev/sdb1            1134323348 576046660 499726996  54% /db

4:19pm 24 gfu@moddblads:/dump/gfu> cat /etc/fstab
LABEL=/                 /                       ext3    defaults        1 1
LABEL=/db               /db                     ext3    defaults        1 2

Any comments and suggestions ?

Thanks,
Gary


Re: slow building index and reference after Sybase to Pg

From
Andy Colson
Date:
>>
>>
> Here are the more test results on the 3rd ref and parallel on 2nd and
> 3rd references:
>
> 325 mins on FK_FM_ALL_REF_FILE (334 mins previous)
>
> parallel results on 2nd and 3rd references: (much worse on 2nd ref)
>
>

> Here are some information on my system:
>
> 1:07pm 20 gfu@moddblads:/dump/gfu> free -m (48MB of memory)
> total used free shared buffers cached
> Mem: 48036 47867 168 0 294 46960
>
> 1:08pm 21 gfu@moddblads:/dump/gfu> hinv
> Total CPU's: 24
> Intel(R) Xeon(R) CPU X5660 @ 2.80GHz 2793 MHZ
> Cache Size: 12288 KB
>

Huh.  Dunno.  Ah, how about locks?

while its building a reference, look at pg_locks (select * from pg_locks).

Looking at the build times:
 > 610 mins on FK_EXPORT_FILES_REF_FILE (??? almost double the time)
 > 340 mins on FK_FM_ALL_REF_FILE

looks like the first one ran and the second waited for it to complete
before it ran.

Are those two touching the same tables?

-Andy


Re: slow building index and reference after Sybase to Pg

From
Gary Fu
Date:
  On 02/28/11 17:32, Andy Colson wrote:
>>>
>>>
>> Here are the more test results on the 3rd ref and parallel on 2nd and
>> 3rd references:
>>
>> 325 mins on FK_FM_ALL_REF_FILE (334 mins previous)
>>
>> parallel results on 2nd and 3rd references: (much worse on 2nd ref)
>>
>>
>
>> Here are some information on my system:
>>
>> 1:07pm 20 gfu@moddblads:/dump/gfu> free -m (48MB of memory)
>> total used free shared buffers cached
>> Mem: 48036 47867 168 0 294 46960
>>
>> 1:08pm 21 gfu@moddblads:/dump/gfu> hinv
>> Total CPU's: 24
>> Intel(R) Xeon(R) CPU X5660 @ 2.80GHz 2793 MHZ
>> Cache Size: 12288 KB
>>
>
> Huh.  Dunno.  Ah, how about locks?
>
> while its building a reference, look at pg_locks (select * from
> pg_locks).
>
> Looking at the build times:
> > 610 mins on FK_EXPORT_FILES_REF_FILE (??? almost double the time)
> > 340 mins on FK_FM_ALL_REF_FILE
>
> looks like the first one ran and the second waited for it to complete
> before it ran.
>
> Are those two touching the same tables?
>
> -Andy
>
There are no other processes running on the db when the reference is
rebuilt.

Yes, they are reference to the same table and the 1st one must lock the
table before the 2nd one can run.

Gary

Re: slow building index and reference after Sybase to Pg

From
Andres Freund
Date:
Hi,

On Saturday, February 26, 2011 12:11:19 AM Gary Fu wrote:
> wal_buffers = 16MB
sensible
> checkpoint_segments = 256
A setting that high seems unlikely to be beneficial... I suggest you configure
log_checkpoints to monitor this.

> effective_cache_size = 30GB
Not likely to matter in this case.

> maintenance_work_mem = 2GB
1GB is the max value taking effect.

I would also suggest setting
wal_sync_method=fdatasync

You haven't configured shared_buffers at all? I would suggest setting it to 2GB
or such.


Andres

Re: slow building index and reference after Sybase to Pg

From
Gary Fu
Date:
  On 02/28/11 17:56, Andres Freund wrote:
> Hi,
>
> On Saturday, February 26, 2011 12:11:19 AM Gary Fu wrote:
>> wal_buffers = 16MB
> sensible
>> checkpoint_segments = 256
> A setting that high seems unlikely to be beneficial... I suggest you configure
> log_checkpoints to monitor this.
>
>> effective_cache_size = 30GB
> Not likely to matter in this case.
>
>> maintenance_work_mem = 2GB
> 1GB is the max value taking effect.
>
> I would also suggest setting
> wal_sync_method=fdatasync
Already set this.
> You haven't configured shared_buffers at all? I would suggest setting it to 2GB
> or such.
>
It is set to 12GB.

Do you recommend to set fsync to off ?  It needs to restart the server.

Thanks,
Gary

Re: slow building index and reference after Sybase to Pg

From
"Joshua D. Drake"
Date:
On Mon, 2011-02-28 at 18:16 -0500, Gary Fu wrote:

> >
> It is set to 12GB.
>
> Do you recommend to set fsync to off ?  It needs to restart the server.

Not in production.

JD

>
> Thanks,
> Gary
>

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt


Re: slow building index and reference after Sybase to Pg

From
Andres Freund
Date:
Hi,

On Wednesday 23 February 2011 19:31:58 Gary Fu wrote:
> 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).
I think some additional information would be useful:
* pg version
* kernel version
* distribution


Andres

Re: slow building index and reference after Sybase to Pg

From
Gary Fu
Date:
  On 02/28/11 19:30, Andres Freund wrote:
> Hi,
>
> On Wednesday 23 February 2011 19:31:58 Gary Fu wrote:
>> 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).
> I think some additional information would be useful:
> * pg version
> * kernel version
> * distribution
>
>
> Andres
>
Here are the information :

modaps_lads=> show server_version;
  server_version
----------------
  9.0.1

9:58am 32 gfu@moddblads:/dump/gfu> uname -a
Linux moddblads 2.6.18-194.17.1.el5 #1 SMP Wed Sep 29 12:50:31 EDT 2010
x86_64 x86_64 x86_64 GNU/Linux

9:58am 34 gfu@moddblads:/dump/gfu> cat /proc/version
Linux version 2.6.18-194.17.1.el5 (mockbuild@builder10.centos.org) (gcc
version 4.1.2 20080704 (Red Hat 4.1.2-48)) #1 SMP Wed Sep 29 12:50:31
EDT 2010

Also, we have RAID10 with 600GB SAS drives 15000RPM

Another question here is that why building the reference will lock the
table for reading ?  I mean why I cannot build two references at the
same time on the same reference table.  Does the reference build just read ?

Thanks,
Gary