Transactional Replication Performance

[NickDech]

I've just setup transactional replication using a licensed copy of v1.4.0.3132. It works, but it's really slow with transactions involving large row counts. I'm getting a synch rate of about 2500 rows / minute...

Is there something I can to increase my performance?

I'm going from SQL Server 2008 R2 to Postgres 9.2.1.

I'm just doing straight inserts into fairly wide table. Something like this:

insert ReplicatedTable [...]
selet top 2500 [...]
from Source

The insert statement itself only takes a second. According to replication monitor, my time to DistributionDB is only a second. The lag is from distribution to subscriber.

I followed the replication directions in the developer manual at first. I eventually tried to change the extended properties of PGNP via
exec sp_addpushsubscription_agent @publication = N'PostgresPublication',
@subscriber_provider = N'PGNP', @subscriber_provider_string = N'PORT=5432;BULK_METHOD=Copy;BULK_INSERT=15000;',

to no avail.

I also tried to increase the -CommitBatchSize and -CommitBatchSizeThreshold of my distribution agent, also with no success.

I have an SSIS package running on the same SQL/Postgres servers that is getting a much, much higher throughput... Will I be able to get the same kind of performance out of transactional replication?


Thanks,
Nick

[Moderator]

Yes, there is an extended property BULK_METHOD=PIPECOPY that improves performance of the transactional replication. Please change the parameter from "COPY" to "PIPECOPY".

According to the Developer's Manual: "When [BULK_METHOD] set to ‘PIPECOPY’, then multiple INSERT statements are combined into a COPY command. This is a special mode designed for fast transactional replication."

[NickDech]

Hmm... Not sure how I missed that. Thanks for the prompt reply, I will try it tomorrow AM and see how it goes.

Nick

[NickDech]

I changed the extended properties, to this: @subscriber_provider_string = N'PORT=5432;BULK_METHOD=PIPECOPY;BULK_INSERT=15000;' and dropped and recreated both my publisher and subscription.

I'm getting this error now in my distributor agent:

ERROR: invalid input syntax for integer: "615-2073640308spProcessSupplementalListingspUpdateListingImagesBitmap210Kenmore 26.5 cu. ft. Side-by-Side Refrigerator (5103) ENERGY STAR$1503.99151555|04651033000P|04/15/2012NN\Retailers\Sears\SearsSupplement001_04651033000P.jpgKenmore-2099999750 1879.9990SearsSupplement00104651033000P06"
CONTEXT: COPY listing2, line 1, column merchantid: "615-2073640308spProcessSupplementalListingspUpdateListingImagesBitmap210Kenmore 26.5 cu. ft. Side-by..." (Source: MSSQLServer, Error number: 20049)
Get help: http://help/20049
ERROR: invalid input syntax for integer: "615-2073640308spProcessSupplementalListingspUpdateListingImagesBitmap210Kenmore 26.5 cu. ft. Side-by-Side Refrigerator (5103) ENERGY STAR$1503.99151555|04651033000P|04/15/2012NN\Retailers\Sears\SearsSupplement001_04651033000P.jpgKenmore-2099999750 1879.9990SearsSupplement00104651033000P06"
CONTEXT: COPY listing2, line 1, column merchantid: "615-2073640308spProcessSupplementalListingspUpdateListingImagesBitmap210Kenmore 26.5 cu. ft. Side-by..."
Commit failed: ERROR: current transaction is aborted, commands ignored until end of transaction block
Commit failed: ERROR: current transaction is aborted, commands ignored until end of transaction block

I'm using the developer manual that's here: http://www.pgoledb.com/downloads/Developer%20Manual.pdf
there's no mention of PIPECOPY in in. Am I looking at the wrong version?

[NickDech]

I was referencing the wrong version of the developer manual. I found the one that references PIPECOPY. I dropped and recreated the publication and subscription using the updated developer manual, however I was not able to use the @schema_option of sp_addarticle as directed in the documentation because it was throwing an error about a timestamp field. ( The source table has a datetime field , but no timestamp). So I just left @Schema_option blank for now. According to BOL the default for non-SQL Server publications is 0x050D3.

However, whenever I set the extendedproperties of the subscription to PIPECOPY, I now get this different error message:

Error messages:
ERROR: column "merchantid" is of type integer but expression is of type character varying
LINE 1: ...ription","imageid","savingsprogramvalue") VALUES ($1,$2,$3,$...
^
HINT: You will need to rewrite or cast the expression. (Source: MSSQLServer, Error number: 20203)

If I change it back to COPY or VALUES, it works fine.

Any ideas?

[Moderator]

I updated the Developer's Manual to the latest version. Thank you!

It looks like there is a bug in the PIPECOPY code branch. Would it be possible to obtain the table DDL that has the problem? Also, having sample rows that cause the "invalid input syntax for integer" error would be very helpful. Thank you!