Thread: slow building index and reference after Sybase to Pg
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
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
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
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
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
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
>> >> > 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
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
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
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
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
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
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