Improve WAN replication performance?

Using PGNP with MS SQL Server, Data Transformation Services (DTS), SQL Server Integration Services (SSIS), Linked Servers, Snapshot and Transactional Replication and more.

Improve WAN replication performance?

Postby michaelgg » Tue May 01, 2012 3:38 pm

Any hints on how to improve WAN replication performance? It seems that replicating to Postgres involves waiting on a lot of round-trips. A nearby server replicates fairly quick (few thousand row/sec), whereas on 30ms away goes very, very slowly. (5KB/sec throughput, only a few rows/sec.) Same hardware, so it seems to indicate network related problems. SQL Server Activity Monitor shows a lot of waits on Network I/O.

I tried creating the subscription with "SQLSERVER=2008;BULK_METHOD=COPY;BULK_INSERT=1000;" in the provider string, but it didn't make any difference. I thought this was supposed to absorb consecutive INSERTs and somehow make it more efficient, automagically?

Is this a limitation from SQL Server that's impossible to workaround, or what are my options? I don't mind if individual transactions take a long time (that is, if I do 1000 separate inserts, I don't care too much if it's slow), but bulk loads (either initial, or when I do a bulk copy into a table), should go reasonably quick... right?

I'm using SQL Server 2012, Postgres 9.1.3, and PGNP 1.3.
michaelgg
 
Posts: 4
Joined: Mon Jan 23, 2012 6:07 pm

Re: Improve WAN replication performance?

Postby michaelgg » Tue May 01, 2012 3:53 pm

More details:

It's not the transactions. SQL Server is delivering 1000 commands per transaction. The problem is that each row sends:
Bind/Describe/Execute/Sync and waits until it gets Bind completion/No data/Command completion("INSERT 0 1")/Ready for Query until it sends the next row.

So in one case, 31ms round-trip time means 1000 rows = 31 seconds. After 1000 rows, there's a COMMIT, and a new transaction starts. What I don't understand is why PGNP is waiting for sync/ready after each command, and how I can get it to not do that.
michaelgg
 
Posts: 4
Joined: Mon Jan 23, 2012 6:07 pm

Re: Improve WAN replication performance?

Postby Moderator » Tue May 01, 2012 9:07 pm

We will be working on the provider optimization, and we'll update the thread.
User avatar
Moderator
Site Admin
 
Posts: 306
Joined: Wed Oct 29, 2008 11:27 pm


Return to SQL Server/DTS/SSIS/Linked servers/Replication

Who is online

Users browsing this forum: No registered users and 1 guest