Recently I've had a chance to build a 3-server AlwaysOn environment distributed between the primary and secondary data centers.
The configuration looks like this:
Primary Data Center Secondary Data Center
SQLDEV1 SQLDEV3
SQLDEV2
The availability group was crated with synchronous commit replicas on SQLDEV1 and SQLDEV2 and the replica on SQLDEV3 was configured for asynchronous commit.
The link between the data centers was not great and when I pinged SQLDEV3 from SQLDEV1 I got these results
I also created a very simple application that:
- generates a random INT
- if the table has a record with such key its updates the record otherwise it adds a new record to the table.
- every 5 seconds the app also records to a different (result) table StartTime, EndTime, and total number of inserts/updates
And I query the result table and sum up totals updates occurred in past 10 seconds.
I executed this application with 4 concurrent users from SQLDEV1 and the test database was also hosted on the same server.
Then the test database was not a part of the availability group the average number of transaction per second was 5,500-6,000.
When the test database was a part of the availability group with synchronous commit to SQLDEV2 and asynchronous commit to SQLDEV3 the average number of the transactions was 2,800 - 3,300
And in the final test I changed the replica on SQLDEV3 to by in the synchronous-commit mode. And the average number of the transactions dropped to 40-60 per second (yes, decrease of two orders of magnitudes).
Certainly, this is not a valid performance tests but nevertheless it gives some feeling of the performace impact ones can expect when dealing with AlwaysOn.
The configuration looks like this:
Primary Data Center Secondary Data Center
SQLDEV1 SQLDEV3
SQLDEV2
The availability group was crated with synchronous commit replicas on SQLDEV1 and SQLDEV2 and the replica on SQLDEV3 was configured for asynchronous commit.
The link between the data centers was not great and when I pinged SQLDEV3 from SQLDEV1 I got these results
Approximate round trip times in milli-seconds:
Minimum = 39ms, Maximum = 63ms, Average =
42msI also created a very simple application that:
- generates a random INT
- if the table has a record with such key its updates the record otherwise it adds a new record to the table.
- every 5 seconds the app also records to a different (result) table StartTime, EndTime, and total number of inserts/updates
And I query the result table and sum up totals updates occurred in past 10 seconds.
I executed this application with 4 concurrent users from SQLDEV1 and the test database was also hosted on the same server.
Then the test database was not a part of the availability group the average number of transaction per second was 5,500-6,000.
When the test database was a part of the availability group with synchronous commit to SQLDEV2 and asynchronous commit to SQLDEV3 the average number of the transactions was 2,800 - 3,300
And in the final test I changed the replica on SQLDEV3 to by in the synchronous-commit mode. And the average number of the transactions dropped to 40-60 per second (yes, decrease of two orders of magnitudes).
Certainly, this is not a valid performance tests but nevertheless it gives some feeling of the performace impact ones can expect when dealing with AlwaysOn.
would you be able share the tool you created for your test? Also, how did you measure transactions per second? I am working on a similar test myself.
ReplyDeleteHi Dustin,
DeleteI've posted the script here
http://smizrohi.blogspot.ca/2013/04/a-simple-script-to-test-server.html
Let me know if it works for you
Regards,
Serguei
Your new valuable key points imply much a person like me and extremely more to my office workers. With thanks; from every one of us.
ReplyDeleteAmazon Web Services Online Training
Thank you for your post. This is excellent information. It is amazing and wonderful to visit your site. This article is very much helpful and i hope this will be an useful information for the needed one.Keep on updating these kinds of informative things.
ReplyDeleteoracle training in chennai
oracle training institute in chennai
oracle training in bangalore
oracle training in hyderabad
oracle training
oracle online training
hadoop training in chennai
hadoop training in bangalore