Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hsvn5-0005uN-Bd for pgsql-performance@arkaria.postgresql.org; Wed, 31 Jul 2019 21:05:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hsvn3-0000K4-UZ for pgsql-performance@arkaria.postgresql.org; Wed, 31 Jul 2019 21:05:41 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hsvn3-0000Ju-KE for pgsql-performance@lists.postgresql.org; Wed, 31 Jul 2019 21:05:41 +0000 Received: from 4.mo68.mail-out.ovh.net ([46.105.59.63]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hsvn0-0007ds-4U for pgsql-performance@lists.postgresql.org; Wed, 31 Jul 2019 21:05:41 +0000 Received: from player791.ha.ovh.net (unknown [10.109.146.1]) by mo68.mail-out.ovh.net (Postfix) with ESMTP id 76B7E13C4C0 for ; Wed, 31 Jul 2019 23:05:36 +0200 (CEST) Received: from darold.net (7.165.73.86.rev.sfr.net [86.73.165.7]) (Authenticated sender: gilles@darold.net) by player791.ha.ovh.net (Postfix) with ESMTPSA id 037C28631EC6; Wed, 31 Jul 2019 21:05:29 +0000 (UTC) Subject: Re: Oracle to postgres migration via ora2pg (blob data) To: Amol Tarte , Niels Jespersen Cc: Daulat Ram , "pgsql-general@lists.postgresql.org" , "pgsql-performance@lists.postgresql.org" References: <3967181ED595B445B1E39718204FD4BD0122BA2638@SRVEXC5.dst.local> From: Gilles Darold Openpgp: preference=signencrypt Autocrypt: addr=gilles@darold.net; prefer-encrypt=mutual; keydata= xsBNBEyscTYBCADOHjxbECHbFaVEA8fKAh8gtdRMKCXPhYwgrhIq+435c824l0NkxxT6xAq/ U2embiCGSBy13yVT016bzcxOnOqxgw4dJgXPE1P6RYLgjd17c5Xt7uzkbn4ChO1LOVtx+7y/ uf8GV1fiPTtnilzGQvWdXMDDTNaFbyfIsebE6rB2p47Ns0m+cj46hS22pmEWELM4RilmQ2MX X7vNZPxQ/C6SKQDgeGZSHrvw9OLzFty8yY7HnExfUmCzfN4asAH3q/lCu1YQgud84bCC6hXz Cbw/DvR7pW8QK/ej/UkNzeGUWsWX6GYDF26P3wO9Zqk5T8JixrveiCaWA/e4DR5yr2bTABEB AAHNKEdpbGxlcyBEYXJvbGQgPGdpbGxlcy5kYXJvbGRAZGFsaWJvLmNvbT7CwHgEEwECACIF AkyscTYCGyMGCwkIBwMCBhUIAgkKCwQWAgMBAh4BAheAAAoJEHAqzJ89qW5hIlgH/0cWzoKx 6Ir1K8L2uA5qDYbpxoryeMoCMSHHvK7TGPNrb1C1exHuGOQhb7bD3rkerohGy4ZR4EytjRA8 oj6MttW96w9hMzQKQI7k5v17R53zU/1f7BTWLRVgt7lrTMSxryx/Z92PFfjw4yjcSoNy1iNG OPdTo2wGgJ1U8TcF7ny3f92O3BoNMwOkH1cYJnLhTrTLZVMAPpxjcNYEyR47b/2eIidBngMA cn87UnSpa0n8Z/5CirzP0B+ofopRz4PMYWKRPdvelTKB1iiJtvBWLcQkG2H3NZJxpvso91rY 97Rwt/Nez2rsA8pZzGoL13Sf4LqscKXK0qVnhIGORW0qBMvOwE0ETKxxNgEIALVvukwz2p2V w37pnMk2CGWCgoj76Lnts/3sG42Jw6Ewjvjg7u9SGg2fJJ4dzywcoBW5ufJlZalghO3RpbZq DwT2432CUdGG500GLLzYrHIhGBw8h9SkoJqnfel3MOjGy/HZMymKmCy/uqx3ti3Tp0l0Is38 C7GK/lG3NKnYUwQuFaM2pDkIoIx3hyogGXEMwV+IHtYpvFroCBViCgkYubsOEfqJti40BMXJ fKv74ecDk43I7x9V6Pzt0j0rfmj4vFbSKHF6ptJNeZBrXrNSeTmfAJXaPD/olDLEStWu6cr5 XyMmYxYE9D2eMOQHhFb7UOZdh++HhWcPxMMyKNNB2YsAEQEAAcLAXwQYAQIACQUCTKxxNgIb DAAKCRBwKsyfPaluYZETB/91iP031oHNFMU62RC8x+bERW5x6Dpu7Kk9rOSKf7ChcSWElBQ2 A7OnNAACgPNbJgYyuEquYHTdXLP43phNDJ93EhL+af5z2BLx1gkdV+1rotlzbzIaUv9uofRW U6UJ/emgL3kn1TRmf3PKWWgJkHRlePqKDeNVc53uApbXHoNrS4uluiwD88mx3+xgZqteCQO2 DhEpAckzki82S4n4Ww6fRFB3KIGyafO7fzHFK8cFuT4za4YUFQN9Ix6WTABleoRLz3Cn+Jde 049Zhg1222l0N07KWRS3ZZxAhwr3rv+pg3cJdbNf/rDszym92rig79SbGEEuL3nFCUXmA/QR IE+v Message-ID: Date: Wed, 31 Jul 2019 23:05:28 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.8.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------A75BA9ECE14E4C51595959DA" Content-Language: en-US X-Ovh-Tracer-Id: 189151185261300557 X-VR-SPAMSTATE: OK X-VR-SPAMSCORE: -100 X-VR-SPAMCAUSE: gggruggvucftvghtrhhoucdtuddrgeduvddrleehgdduheeiucetufdoteggodetrfdotffvucfrrhhofhhilhgvmecuqfggjfdpvefjgfevmfevgfenuceurghilhhouhhtmecuhedttdenucesvcftvggtihhpihgvnhhtshculddquddttddm List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------A75BA9ECE14E4C51595959DA Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Le 31/07/2019 =C3=A0 18:02, Amol Tarte a =C3=A9crit=C2=A0: > FullConvert does this job much faster than ora2pg > > > With Warm Regards, > Amol Tarte, > Project Lead, > Rajdeep InfoTechno Pvt. Ltd. > Visit us at http://it.rajdeepgroup.com > > On Wed 31 Jul, 2019, 5:16 PM Niels Jespersen, > wrote: > > I would look at the source table in Oracle first. It looks a lot > like audit data. Perhaps all content is not needed in Postgres. If > it is, then the table and lobs may benefit from being reorganised > in oracle. > > =C2=A0 > > Alter table CLIENT_DB_AUDIT_LOG move; > > Alter table CLIENT_DB_AUDIT_LOG =C2=A0move lob > (SYS_LOB0000095961C00008$$); > > -- Three more of these. > > =C2=A0 > > The syntax is from my the back of my head. You may need to look > the details up. > > =C2=A0 > > Niels > > =C2=A0 > > =C2=A0 > > *Fra:* Daulat Ram > > *Sendt:* 31. juli 2019 13:32 > *Til:* pgsql-general@lists.postgresql.org > ; > pgsql-performance@lists.postgresql.org > > *Emne:* Oracle to postgres migration via ora2pg (blob data) > > =C2=A0 > > Hello team, > > =C2=A0 > > We have to migrate a schema from oracle to postgres but there is > one table that is having following large lob segments.=C2=A0 This t= able > is taking time to export. What parameters we have to set in > ora2pg.conf to speed up the data export by ora2pg. > > =C2=A0 > > Table: =C2=A0=C2=A0 CLIENT_DB_AUDIT_LOG > > =C2=A0 > > LOBSEGMENT=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 SYS_LOB0000095961C00008$$=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 80.26 > > LOBSEGMENT=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 SYS_LOB0000095961C00007$$=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 79.96 > > LOBSEGMENT=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 SYS_LOB0000094338C00008$$=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 8.84 > > LOBSEGMENT=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 SYS_LOB0000084338C00007$$=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 8.71 > > LOBSEGMENT=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 SYS_LOB0000085961C00009$$=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 5.32 > > =C2=A0 > > VM Details are: > > =C2=A0 > > RAM=C2=A0 8GB > > VCPUs 2 VCPU > > Disk 40GB > > =C2=A0 > > Thanks, > Hi, Before using impressive commercial products you can try some additional configuration with Ora2pg. The only solution to improve data migration performances is to use parallelism. The problem with BLOB is that they have to be converted into hex to be inserted into a bytea. The internal function in Ora2Pg that responsible of this job is _escape_lob(). The other problem is that Oracle is very slow to send the BLOB to Ora2Pg, I don't know if commercial products are better at this point but I have challenged Ora2Pg with Kettle some years ago without do much differences. So what you should do first is to set the following in your ora2pg.conf: NO_LOB_LOCATOR=C2=A0=C2=A0=C2=A0 1 LONGREADLEN=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 100000000 This will force Oracle to send the full content of the BLOB in a single pass otherwise it will use small chunks. The only drawback is that you have to set LONGREADLEN to the highest BLOB size in your table to not throw and LONGTRUNC error. That also mean that for each ROW returned DBD::Oracle (Perl driver for Oracle) will allocate at least LONGREADLEN in memory and we want to extract as much rows as possible. You have understood that your 8GB of memory will limit the quantity of rows that can be exported at the same time. The other point is that Oracle is slow so you have to parallelize data export. Use -J 4 at command line to create 4 simultaneous process to data export. Parallelization on Oracle side is only possible if you have a numeric column that can be used to split the data using modulo 4 in this case. This is a basic implementation but it is enough in most cases.= Converting BLOB to Bytea consume lot of cpu cycle too so it is a good practice to parallelize this work too. Use -j 2 or -j 3 for this work. The number of parallelisation process should be tested because there is a limit where you will not win anything. If you have, let's say 32GB of memory and 12 cpu you could try a command like : =C2=A0=C2=A0=C2=A0 ora2pg -c ora2pg.conf -J 4 -j 3 -t CLIENT_DB_AUDIT_LOG= -L 500 If you have less resources don't forget that -J and -j must be multiplied to have the number of process that Ora2Pg will parallelize. The -L option (DATA_LIMIT) is used to reduce the number of row extracted at a time. Here with a value of 500 it will process 50 (DATA_LIMIT/10) rows with BLOB at a time. If the table do not have any BLOB it will use 500 row at a time. For most tables this parameter should be set to 10000 up to 250000. If you have lot of memory the value can be higher. If you think it is too low you can set BLOB_LIMIT in ora2pg.conf to set it at a higher value. However Ora2Pg will show you the data migration speed so you can adjust all these parameters to see if you have some performances gains. If you want to know exactly at which speed Oracle is able to send the data add --oracle_speed to the ora2pg command. Ora2Pg will only extract data from Oracle, there will be no bytea transformation or data writing, just the full Oracle speed. You can do some test with the value of the -J option to see what is the best value. On the other side you can use --ora2pg_speed option to see at which speed Ora2Pg is able to convert the data, nothing will be written too. Use it to know if you have some win with the value of the -j option. Don't forget to do some additional test with the BLOB_LIMIT value to see if there some more improvement. If someone can prove me that they have better performances at Oracle data extraction side I will be pleased to look at this code. I hope this will help. =C2=A0 Regards, --=20 Gilles Darold http://www.darold.net/ --------------A75BA9ECE14E4C51595959DA Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit Le 31/07/2019 à 18:02, Amol Tarte a écrit :
FullConvert does this job much faster than ora2pg


With Warm Regards,
Amol Tarte,
Project Lead,
Rajdeep InfoTechno Pvt. Ltd.
Visit us at http://it.rajdeepgroup.com

On Wed 31 Jul, 2019, 5:16 PM Niels Jespersen, <NJN@dst.dk> wrote:

I would look at the source table in Oracle first. It looks a lot like audit data. Perhaps all content is not needed in Postgres. If it is, then the table and lobs may benefit from being reorganised in oracle.

 

Alter table CLIENT_DB_AUDIT_LOG move;

Alter table CLIENT_DB_AUDIT_LOG  move lob (SYS_LOB0000095961C00008$$);

-- Three more of these.

 

The syntax is from my the back of my head. You may need to look the details up.

 

Niels

 

 

Fra: Daulat Ram <Daulat.Ram@exponential.com>
Sendt: 31. juli 2019 13:32
Til: pgsql-general@lists.postgresql.org; pgsql-performance@lists.postgresql.org
Emne: Oracle to postgres migration via ora2pg (blob data)

 

Hello team,

 

We have to migrate a schema from oracle to postgres but there is one table that is having following large lob segments.  This table is taking time to export. What parameters we have to set in ora2pg.conf to speed up the data export by ora2pg.

 

Table:    CLIENT_DB_AUDIT_LOG

 

LOBSEGMENT           SYS_LOB0000095961C00008$$          80.26

LOBSEGMENT           SYS_LOB0000095961C00007$$          79.96

LOBSEGMENT           SYS_LOB0000094338C00008$$           8.84

LOBSEGMENT           SYS_LOB0000084338C00007$$           8.71

LOBSEGMENT           SYS_LOB0000085961C00009$$           5.32

 

VM Details are:

 

RAM  8GB

VCPUs 2 VCPU

Disk 40GB

 

Thanks,


Hi,

Before using impressive commercial products you can try some additional configuration with Ora2pg.

The only solution to improve data migration performances is to use parallelism. The problem with BLOB is that they have to be converted into hex to be inserted into a bytea. The internal function in Ora2Pg that responsible of this job is _escape_lob(). The other problem is that Oracle is very slow to send the BLOB to Ora2Pg, I don't know if commercial products are better at this point but I have challenged Ora2Pg with Kettle some years ago without do much differences. So what you should do first is to set the following in your ora2pg.conf:

NO_LOB_LOCATOR    1
LONGREADLEN        100000000

This will force Oracle to send the full content of the BLOB in a single pass otherwise it will use small chunks. The only drawback is that you have to set LONGREADLEN to the highest BLOB size in your table to not throw and LONGTRUNC error.

That also mean that for each ROW returned DBD::Oracle (Perl driver for Oracle) will allocate at least LONGREADLEN in memory and we want to extract as much rows as possible. You have understood that your 8GB of memory will limit the quantity of rows that can be exported at the same time.

The other point is that Oracle is slow so you have to parallelize data export. Use -J 4 at command line to create 4 simultaneous process to data export. Parallelization on Oracle side is only possible if you have a numeric column that can be used to split the data using modulo 4 in this case. This is a basic implementation but it is enough in most cases.

Converting BLOB to Bytea consume lot of cpu cycle too so it is a good practice to parallelize this work too. Use -j 2 or -j 3 for this work. The number of parallelisation process should be tested because there is a limit where you will not win anything.

If you have, let's say 32GB of memory and 12 cpu you could try a command like :

    ora2pg -c ora2pg.conf -J 4 -j 3 -t CLIENT_DB_AUDIT_LOG -L 500

If you have less resources don't forget that -J and -j must be multiplied to have the number of process that Ora2Pg will parallelize. The -L option (DATA_LIMIT) is used to reduce the number of row extracted at a time. Here with a value of 500 it will process 50 (DATA_LIMIT/10) rows with BLOB at a time. If the table do not have any BLOB it will use 500 row at a time. For most tables this parameter should be set to 10000 up to 250000. If you have lot of memory the value can be higher. If you think it is too low you can set BLOB_LIMIT in ora2pg.conf to set it at a higher value.

However Ora2Pg will show you the data migration speed so you can adjust all these parameters to see if you have some performances gains. If you want to know exactly at which speed Oracle is able to send the data add --oracle_speed to the ora2pg command. Ora2Pg will only extract data from Oracle, there will be no bytea transformation or data writing, just the full Oracle speed. You can do some test with the value of the -J option to see what is the best value. On the other side you can use --ora2pg_speed option to see at which speed Ora2Pg is able to convert the data, nothing will be written too. Use it to know if you have some win with the value of the -j option. Don't forget to do some additional test with the BLOB_LIMIT value to see if there some more improvement. If someone can prove me that they have better performances at Oracle data extraction side I will be pleased to look at this code.

I hope this will help.
 

Regards,
-- 
Gilles Darold
http://www.darold.net/
--------------A75BA9ECE14E4C51595959DA--