Improve WAN replication performance?

  • michaelgg
  • Topic Author
  • Visitor
  • Visitor
11 years 10 months ago #6448 by michaelgg
Improve WAN replication performance? was created by 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.

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

  • michaelgg
  • Topic Author
  • Visitor
  • Visitor
11 years 10 months ago #6449 by michaelgg
Replied by michaelgg on topic Re: Improve WAN replication performance?
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.

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

More
11 years 10 months ago #6450 by Moderator
Replied by Moderator on topic Re: Improve WAN replication performance?
We will be working on the provider optimization, and we'll update the thread.

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

Time to create page: 0.129 seconds
Powered by Kunena Forum