Improve WAN replication performance?

[michaelgg]

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]

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.

[Moderator]

We will be working on the provider optimization, and we'll update the thread.