Re: slow building index and reference after Sybase to Pg
От | Gary Fu |
---|---|
Тема | Re: slow building index and reference after Sybase to Pg |
Дата | |
Msg-id | ikh3l2$dsc$1@news.hub.org обсуждение исходный текст |
Ответ на | Re: slow building index and reference after Sybase to Pg (Gary Fu <gfu@sigmaspace.com>) |
Ответы |
Re: slow building index and reference after Sybase to Pg
(Andy Colson <andy@squeakycode.net>)
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: