Improve WAN replication performance?

More
9 years 8 months ago #6448 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.

More
9 years 8 months ago #6449 by 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.

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

More
9 years 8 months ago #6450 by Moderator
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.048 seconds
Powered by Kunena Forum