slow building index and reference after Sybase to Pg

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