Transactional Replication Performance

  • NickDech
  • Topic Author
  • Visitor
  • Visitor
9 years 3 months ago #9899 by NickDech
Transactional Replication Performance was created by 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

Please Log in or Create an account to join the conversation.

More
9 years 3 months ago #9900 by 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."

Please Log in or Create an account to join the conversation.

  • NickDech
  • Topic Author
  • Visitor
  • Visitor
9 years 3 months ago #9901 by NickDech
Replied by NickDech on topic Re: Transactional Replication Performance
Hmm... Not sure how I missed that. Thanks for the prompt reply, I will try it tomorrow AM and see how it goes.

Nick

Please Log in or Create an account to join the conversation.

  • NickDech
  • Topic Author
  • Visitor
  • Visitor
9 years 3 months ago #9902 by NickDech
Replied by NickDech on topic Re: Transactional Replication Performance
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: www.pgoledb.com/downloads/Developer%20Manual.pdf
there's no mention of PIPECOPY in in. Am I looking at the wrong version?

Please Log in or Create an account to join the conversation.

  • NickDech
  • Topic Author
  • Visitor
  • Visitor
9 years 3 months ago #9903 by NickDech
Replied by NickDech on topic Re: Transactional Replication Performance
I [i:27bdtwlo]was [/i:27bdtwlo]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?

Please Log in or Create an account to join the conversation.

More
9 years 3 months ago #9905 by 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!

Please Log in or Create an account to join the conversation.

Time to create page: 0.078 seconds
Powered by Kunena Forum