A question regarding streaming replication

2019-07-31 Thread Mariel Cherkassky 
Hey all,
I have a  questions regarding streaming replication that I would like to
ask in order to understand the feature better :
I have 2 nodes configured with replication (primary + secondary).
In my primary I configured streaming replcation + archiving. My archive
command :
gzip < %p > /var/lib/pgsql/archive/%f ; echo "archiving wal %f"

Correct me if I'm wrong but my archive_command will be run when the
archive_timeout is reached or when the wal is full (16MB). The wal file is
created with default size of 16MB and it will be archived only after 16MB
of wal records will be created.

In my secondary I have the following settings :
restore_command = 'rsync -avzhe ssh postgres@my_primary
:/var/lib/pgsql/archive/%f /var/lib/pgsql/archive/%f ; gunzip <
/var/lib/pgsql/archive/%f > %p; echo "restore command was launched"'
archive_cleanup_command = '/usr/pgsql-9.6/bin/pg_archivecleanup
/var/lib/pgsql/archive %r; "archive_cleanupup for %r was launched"'

Which means, that the restore command on the secondary connects to the
primary, copies the wal file from the archive dir , unzip it and saves it
in the pg_xlog dir of the database.

my question :
When exactly the restore_command will be used ? I use streaming replication
therefore wal records are passed through wal_sender and wal receiver. I
dont see in my logs in the secondary that the restore_command is used but I
see that the same wal files as in the primary are exists on the pg_xlog in
the secondary. Does the streaming replication generates wals on the
secondary from the wals records that it receives from the primary ?

Re: A question regarding streaming replication

2019-07-31 Thread Holger Jakobs 
Beware, the echo command always returns the same exit status, whether the 
archiving had succeeded or not. You might lose WAL files!

Regarding the restore command: It will be executed only if the slave is not 
capable of receiving all WALs via streaming replication, e. g. in case the 
slave was of for a while.

Regards,
Holger

Am 31. Juli 2019 11:28:44 MESZ schrieb Mariel Cherkassky 
:
>Hey all,
>I have a  questions regarding streaming replication that I would like
>to
>ask in order to understand the feature better :
>I have 2 nodes configured with replication (primary + secondary).
>In my primary I configured streaming replcation + archiving. My archive
>command :
>gzip < %p > /var/lib/pgsql/archive/%f ; echo "archiving wal %f"
>
>Correct me if I'm wrong but my archive_command will be run when the
>archive_timeout is reached or when the wal is full (16MB). The wal file
>is
>created with default size of 16MB and it will be archived only after
>16MB
>of wal records will be created.
>
>In my secondary I have the following settings :
>restore_command = 'rsync -avzhe ssh postgres@my_primary
>:/var/lib/pgsql/archive/%f /var/lib/pgsql/archive/%f ; gunzip <
>/var/lib/pgsql/archive/%f > %p; echo "restore command was launched"'
>archive_cleanup_command = '/usr/pgsql-9.6/bin/pg_archivecleanup
>/var/lib/pgsql/archive %r; "archive_cleanupup for %r was launched"'
>
>Which means, that the restore command on the secondary connects to the
>primary, copies the wal file from the archive dir , unzip it and saves
>it
>in the pg_xlog dir of the database.
>
>my question :
>When exactly the restore_command will be used ? I use streaming
>replication
>therefore wal records are passed through wal_sender and wal receiver. I
>dont see in my logs in the secondary that the restore_command is used
>but I
>see that the same wal files as in the primary are exists on the pg_xlog
>in
>the secondary. Does the streaming replication generates wals on the
>secondary from the wals records that it receives from the primary ?

-- 
Holger Jakobs, Bergisch Gladbach
+49 178 9759012
- sent from mobile, therefore short -

SV: Oracle to postgres migration via ora2pg (blob data)

2019-07-31 Thread Niels Jespersen 
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_LOB095961C8$$);
-- 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 
Sendt: 31. juli 2019 13:32
Til: [email protected]; [email protected]
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_LOB095961C8$$  80.26
LOBSEGMENT   SYS_LOB095961C7$$  79.96
LOBSEGMENT   SYS_LOB094338C8$$   8.84
LOBSEGMENT   SYS_LOB084338C7$$   8.71
LOBSEGMENT   SYS_LOB085961C9$$   5.32

VM Details are:

RAM  8GB
VCPUs 2 VCPU
Disk 40GB

Thanks,

Re: Oracle to postgres migration via ora2pg (blob data)

2019-07-31 Thread Amol Tarte 
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.
>
>
>
> Alter table CLIENT_DB_AUDIT_LOG move;
>
> Alter table CLIENT_DB_AUDIT_LOG  move lob (SYS_LOB095961C8$$);
>
> -- 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 
> *Sendt:* 31. juli 2019 13:32
> *Til:* [email protected];
> [email protected]
> *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_LOB095961C8$$  80.26
>
> LOBSEGMENT   SYS_LOB095961C7$$  79.96
>
> LOBSEGMENT   SYS_LOB094338C8$$   8.84
>
> LOBSEGMENT   SYS_LOB084338C7$$   8.71
>
> LOBSEGMENT   SYS_LOB085961C9$$   5.32
>
>
>
> VM Details are:
>
>
>
> RAM  8GB
>
> VCPUs 2 VCPU
>
> Disk 40GB
>
>
>
> Thanks,
>
>
>

Oracle to postgres migration via ora2pg (blob data)

2019-07-31 Thread Daulat Ram 
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_LOB095961C8$$  80.26
LOBSEGMENT   SYS_LOB095961C7$$  79.96
LOBSEGMENT   SYS_LOB094338C8$$   8.84
LOBSEGMENT   SYS_LOB084338C7$$   8.71
LOBSEGMENT   SYS_LOB085961C9$$   5.32

VM Details are:

RAM  8GB
VCPUs 2 VCPU
Disk 40GB

Thanks,

Re: Oracle to postgres migration via ora2pg (blob data)

2019-07-31 Thread Gilles Darold 
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,  > 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_LOB095961C8$$);
>
> -- 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  >
> *Sendt:* 31. juli 2019 13:32
> *Til:* [email protected]
> ;
> [email protected]
> 
> *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_LOB095961C8$$  80.26
>
> LOBSEGMENT   SYS_LOB095961C7$$  79.96
>
> LOBSEGMENT   SYS_LOB094338C8$$   8.84
>
> LOBSEGMENT   SYS_LOB084338C7$$   8.71
>
> LOBSEGMENT   SYS_LOB085961C9$$   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        1


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 1
up to 25. 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

PSQL performance - TPS

2019-07-31 Thread Shital A 
Hello,

We are working on development of an application with postgresql 9.6 as
backend. Application as a whole is expected to give an throughput of 100k
transactions per sec. The transactions are received by DB from component
firing DMLs in ad-hoc fashion i.e. the commits are fired after random
numbers of transaction like 2,3,4. There is no bulk loading of records. DB
should have HA setup in active passive streaming replication. We are doing
a test setup on a 8-core machine having 16 GB RAM. Actual HW will be
better.

Need help in:
1. On this env(8core cpu, 16GB) what is the TPS that we can expect? We have
tested with a simple Java code firing insert and commit in a loop on a
simple table with one column. We get 1200 rows per sec. If we increase
threads RPS decrease.

2. We have tuned some DB params like shared_buffers, sync_commit off, are
there any other pointers to tune DB params?


Thanks.

Re: PSQL performance - TPS

2019-07-31 Thread Gavin Flower 

On 01/08/2019 15:10, Shital A wrote:

Hello,

We are working on development of an application with postgresql 9.6 as 
backend. Application as a whole is expected to give an throughput of 
100k transactions per sec. The transactions are received by DB from 
component firing DMLs in ad-hoc fashion i.e. the commits are fired 
after random numbers of transaction like 2,3,4. There is no bulk 
loading of records. DB should have HA setup in active passive 
streaming replication. We are doing a test setup on a 8-core machine 
having 16 GB RAM. Actual HW will be better.


Need help in:
1. On this env(8core cpu, 16GB) what is the TPS that we can expect? We 
have tested with a simple Java code firing insert and commit in a loop 
on a simple table with one column. We get 1200 rows per sec. If we 
increase threads RPS decrease.


2. We have tuned some DB params like shared_buffers, sync_commit off, 
are there any other pointers to tune DB params?



Thanks.


Curious, why not use a more up-to-date version of Postgres, such 11.4?   
As more recent versions tend to run faster and to be better optimised!


You also need to specify the operating system!  Hopefully you are 
running a Linux or Unix O/S!



Cheers,
Gavin

Re: PSQL performance - TPS

2019-07-31 Thread Shital A 
Hello,

Version 9.6 is used because the components interacting with DB support this
version. OS is RHEL 7.6.

Thanks!

On Thu, 1 Aug 2019, 10:45 Gavin Flower, 
wrote:

> On 01/08/2019 15:10, Shital A wrote:
> > Hello,
> >
> > We are working on development of an application with postgresql 9.6 as
> > backend. Application as a whole is expected to give an throughput of
> > 100k transactions per sec. The transactions are received by DB from
> > component firing DMLs in ad-hoc fashion i.e. the commits are fired
> > after random numbers of transaction like 2,3,4. There is no bulk
> > loading of records. DB should have HA setup in active passive
> > streaming replication. We are doing a test setup on a 8-core machine
> > having 16 GB RAM. Actual HW will be better.
> >
> > Need help in:
> > 1. On this env(8core cpu, 16GB) what is the TPS that we can expect? We
> > have tested with a simple Java code firing insert and commit in a loop
> > on a simple table with one column. We get 1200 rows per sec. If we
> > increase threads RPS decrease.
> >
> > 2. We have tuned some DB params like shared_buffers, sync_commit off,
> > are there any other pointers to tune DB params?
> >
> >
> > Thanks.
>
> Curious, why not use a more up-to-date version of Postgres, such 11.4?
> As more recent versions tend to run faster and to be better optimised!
>
> You also need to specify the operating system!  Hopefully you are
> running a Linux or Unix O/S!
>
>
> Cheers,
> Gavin
>
>
>