Reena Gupta - Reference Architect, Microsoft Business Unit
Many times in the SharePoint 2010 deployments, there is a struggle going between the Infrastructure teams and the end users. Users might complain about errors, timeouts, and slow performance while reading or writing their large files. Infrastructure teams may not realize that their SharePoint deployment which is completely using the SQL databases for storing all the documents and files is actually being the bottleneck. There had been guidance on using the Remote BLOB Storage with SharePoint to improve the performance, but the Infrastructure team needs the proof points.
Recently we did the performance testing for RBS (Remote Blob Storage) on SharePoint 2010 SP1. The objective for this testing was to compare SharePoint throughput and the file I/O performance while all the documents are in SQL content databases vs. all BLOBs are stored in the Remote BLOB Storage and only metadata in the SQL content databases. The test results clearly show the value of using RBS with the SharePoint deployments and support Microsoft recommendations on the file size.
Microsoft recommends to use RBS, when the documents stored in SharePoint are >1MB and any documents <256KB are best stored in SQL databases only. To prove the RBS value, I created the test environment for SharePoint 2010 with 2 WebApps - one as ‘SQLNative’ to keep all the documents completely in the content DBs and other webapp as ‘SQLRBS’ to keep all the BLOBs for any document >1KB in the Remote BLOB Storage in a SMB share, while the metadata was stored in content DBs. I built the SharePoint corpus of 1TB data for each webapp consisting of different size of files as 100KB, 1MB, 10MB and 100MB. Each webapp had 5 site collections with one content DB each.
1. 4-5 Web Servers (Web Front End servers) running SharePoint 2010SP1,
2. 1 Visual Studio 2010 Load Test Controller,
3. 6 Visual Studio 2010 Load Agents,
4. 1 SQL server running SQL 2008R2
5. 2 Media Servers running Windows 2008R2 SP1.
1. NetApp SnapDrive 6.3R1 to manage the database and logs LUNs,
2. NetApp Snap Manager for SQL 5.1 to manage the SQL databases,
3. NetApp Snap Manager for SharePoint 6.0 to manage SharePoint and provide the RBS provider.
Note: SnapManager 6.0 for SharePoint Architecture has been described in my previous blog.
All the servers were virtualized using Hyper-V and distributed evenly on 4 physical servers with SQL server hosted on a dedicated physical server. The backend storage was NetApp FAS3170 Controller with 600GB SAS disk drives used for hosting SQL content DBs and logs for both ‘SQLNative’ and ‘SQLRBS’ webapps and 1TB SATA disk drives used for hosting the SMB shares for Remote BLOB Storage. The network bandwidth between the servers and the storage controllers was 10GBE for all the data communication and the management network bandwidth was 1GBE.
The baseline test cases included a mix of read-write operations with a ratio of 77% reads and 23% writes. Some percentage of the operations was metadata intensive.
RBS Performance Results
- RBS Reduces SQL Server CPU Usage: As RBS removes some I/O operations from SQL Server, the overall CPU load that SQL Server generates with BLOBs going directly to RBS is significantly less than the same workload running against a native SQL Server BLOB store.
2. RBS Relieves SQL Lock Pressure: Since SharePoint writes all documents for a given content database to a single table, we see significant SQL Server Wait time due to locking of the tables in workloads that include concurrent document uploads. Our base case assumes a 77%/23% read/write ratio. Customers with lower write ratios will see less locking than customers with higher write profiles.
3. RBS Reduces the Read-Write Test Time for a Mixed Workload: As Microsoft recommended the use of RBS, mostly for the files above 1MB in size, where RBS is really beneficial for better performance. The observation on read and write test duration measurement for specific file sizes was very much in line with Microsoft’s recommendation. The read test duration for 100k files was very close in both cases, but starting with 1MB file size, the read tests with RBS sites were much faster than the ones on SQL Native. Similarly the write test for 1MB files or higher, showed a signification performance improvement with RBS enabled site. Following charts show the test results for the read/write behavior in the mixed workload use case for 500 and 400 user thread counts. Please note the chart indicates the total read and write test time measurement for the time for reading/writing of all the files in the corpus with a specific file size; not to get confused, it’s not for a single file test.
4. RBS Improves the File Download and Upload Time: We also performed the read only and write only tests as per the test mixes shown in the table earlier in this blog. RBS also proved the average download or upload time to be lower or close to the SQLNative sites for all the smaller sizes, but significantly lower at 10MB or 100MB file operations. Following charts show the read only test for a 500 user thread count and write only tests for a 75 user thread count. Please note that the write only workload is quite intensive with range of such file sizes.
5. RBS Increases SharePoint Scalability: In the baseline tests, the aggregate average throughput (requests per second) from all the SharePoint WFEs was measured thru Perfmon counters. SharePoint throughput with RBS clearly outperformed the throughput from SQL Native databases, which means SharePoint could handle more # of concurrent user requests with RBS than with SQL Native. Following chart shows the throughput with 4 WFEs:
Please note if there are more web servers added to the configuration, you’d see a better throughput, unless the web servers start to saturate or backend storage infrastructure is not able to serve more requests.
In addition to the higher throughput, RBS sites were able to perform the baseline tests with lower latency (avg. test time reported from Visual Studio), see the chart below.
Overall RBS provided higher scalability for SharePoint 2010 by increasing the SharePoint throughput while taking less time for all the tests. There will be more tests performed for RBS in different scenarios and different kinds of use cases; we’ll keep you posted as we get new information.