Transcript Scaling SP2007 - Storage Architecture

Scaling SharePoint 2007:
Storage Architecture
Russ Houberg
Senior Technical Architect, MCSD, MCTS
7/6/2015
© 2008 KnowledgeLake, Inc.
1
Introduction
• KnowledgeLake
 High speed document imaging for SharePoint
 Scaling SharePoint since 2003
• Engaged with Microsoft for high scale POCs
 Large auto manufacturer
 Internal MPSC scalability study partially funded by
SharePoint product team
 Large pharmaceutical
 EMC
Overview
• Wider is Better
• Optimizing Tempdb performance
• Optimizing Content Database Performance
• Optimizing Search Database Performance
• Other SharePoint Databases
• Other I/O Performance Considerations
• The 100GB Database “Recommendation”
• Monitoring SharePoint Database I/O
7/6/2015
© 2008 KnowledgeLake, Inc.
3
Wider is Better
• “Wider is Better”
• Three factors in poor application performance
 Poor application design
 Poor database architecture
 Poor database implementation
7/6/2015
© 2008 KnowledgeLake, Inc.
4
Optimizing Tempdb
• Tempdb is used heavily during query and sort
operations
• Limited I/O can severely impact table queries
• Tempdb optimizations are mentioned first in
Microsoft documentation
7/6/2015
© 2008 KnowledgeLake, Inc.
5
Optimizing Tempdb
• Recommendations
 Pre-size the tempdb
•
•
•
•
•
7/6/2015
Free Option
Tempdb is reset every SQL restart
Set to 25% size of largest database
Leave autogrow enabled
Monitor for future growth
© 2008 KnowledgeLake, Inc.
6
Optimizing Tempdb
 Add data files
• Total data files should be equal to CPU cores
• All data files equal in size
• Size calculation formula:
[MAX DB SIZE (KB)] X [.25] / [# CORES] = DATA FILE SIZE (KB)




7/6/2015
Place Tempdb data files on RAID 10 LUN
Store each data file on its own LUN
Store Tempdb log file on unique LUN
Each data file should be the only file on LUN
© 2008 KnowledgeLake, Inc.
7
Optimizing Content Databases
• Document binary data is stored in SharePoint
• Content can be externalized but is not
recommended in WSS v3.0
• Content databases implemented in Imaging or
File Share replacement solutions grow rapidly
• Content Database Overhead formula:
Low: 1.2 * [Raw Storage Size] = ContentDB Size
High: 1.5 * [Raw Storage Size] = ContentDB Size
7/6/2015
© 2008 KnowledgeLake, Inc.
8
Optimizing Content Databases
• Recommendations
 Pre-construct (pre-position) and pre-size content
databases
• Leave autogrow enabled and monitor size to ensure
predicted size is not exceeded
• SharePoint UI and STSADM can use an empty database
 Place content database data files on RAID 10 volume
• RAID 5 is acceptable, lower cost alternative
 Add data files to match the number of CPU cores
 Avoid sharing LUN space with multiple data files
• Alternatively, stripe data files from multiple content
databases across multiple LUNs
7/6/2015
© 2008 KnowledgeLake, Inc.
9
Optimizing the Search Database
• Search performance is the key to SharePoint
usability.
• Search database can be considerably larger than
the individual content databases
• Search database storage calculation:
GB of disk space required = Total_Content_Size (in GB) x File_Size_Modifier x 4
where File_Size_Modifier is a number in the following range, based on the average
size of the files in your corpus:
1.0 if your content consists of very small files (average file size = 1KB).
0.12 if your content consists of moderate files (average file size = 10KB).
0.05 if your content consists of large files (average file size 100KB or larger)
7/6/2015
© 2008 KnowledgeLake, Inc.
10
Optimizing the Search Database
• Recommendations
 Pre-construct (pre-position) and pre-size the search
database
• Leave autogrow enabled and monitor size to ensure
predicted size is not exceeded
• SharePoint UI and STSADM can use an empty database
 Place content database data files on RAID 10 volume
• Search DB is VERY read/write intensive!
 Add data files to match the number of CPU cores
 Avoid sharing SPINDLES with other data files
 Place search database log file on its own LUN
7/6/2015
© 2008 KnowledgeLake, Inc.
11
Other SharePoint Databases
• Databases include
 SharePoint Configuration Database
 Shared Services Provider (SSP) Database
 SSP Administration Database
• Minimal file I/O requirements
• Store all on the same LUN
• Log files can be co-located or on shared LUN
7/6/2015
© 2008 KnowledgeLake, Inc.
12
Sample Storage Structure
7/6/2015
© 2008 KnowledgeLake, Inc.
13
Other I/O Performance Considerations
• Physical volume fragmentation
 Data space plus 25% - 50% for defragmentation
 Execute scheduled defrag jobs if possible
• Database Index/Statistics Fragmentation
 Out of the box: Database Statistics (Timer Job)
 Standard SQL Maintenance Plan may be better
 Watch out for bug! KB 930887
• WSS Indexes vs Large Content Database
 Indexes facilitate library views and Site Data Queries
 Index changes can cause table (SharePoint) locking
7/6/2015
© 2008 KnowledgeLake, Inc.
14
The 100GB Database “Recommendation”
• Microsoft recommends
 50GB as responsive DB size
 100GB max db size
• Based on SLA requirements and performance impact
• SLA can be mitigated with disk-to-disk backups,
database mirroring, or database log shipping
• Proper storage architecture can mitigate performance
impact
• MS Internally tested up to 350GB
• KL deployments have been successful over 400GB
7/6/2015
© 2008 KnowledgeLake, Inc.
15
Monitoring SharePoint Database I/O
• Use Perfmon counters to track database I/O
• Key counter is Average Disk Queue Length
 Decimal values are excellent
 Single digit values are acceptable
 Steady low double digit values represent degrading
performance
 Steady 3 or 4 digit values represent significant
degradation with existing or imminent user impact
• If values indicate issues, view ADQ on individual
LUNs to isolate offending database
7/6/2015
© 2008 KnowledgeLake, Inc.
16
Monitoring SharePoint Database I/O
• Other helpful I/O counters









Logical
Logical
Logical
Logical
Logical
Logical
Logical
Logical
Logical
Disk:
Disk:
Disk:
Disk:
Disk:
Disk:
Disk:
Disk:
Disk:
Disk Transfers/sec
Disk Read Bytes/sec & Disk Write Bytes/sec
Average Disk sec/Read (Read Latency)
Average Disk sec/Write (Write Latency)
Average Disk Byte/Read
Average Disk Byte/Write
Current Disk Queue Length
Average Disk Reads/Sec
Average Disk Write/Sec
• Explained in “Performance Recommendations for
Storage Planning and Monitoring”
7/6/2015
© 2008 KnowledgeLake, Inc.
17
Whitepaper
Scaling SharePoint 2007: Storage Architecture
http://www.knowledgelake.com/whitepaper/
7/6/2015
© 2008 KnowledgeLake, Inc.
18
Summary
• SharePoint scalability is an exercise in resource
balancing
• Capacity Planning tool is good but does not
consider the storage subsystem
• The storage subsystem is the key a responsive
SharePoint implementation
• Storage isn’t the place to cut $$$
• ”E Aho Laula”…Wider is better!
7/6/2015
© 2008 KnowledgeLake, Inc.
19
Questions?
7/6/2015
© 2008 KnowledgeLake, Inc.
20
References
•
Russ Houberg’s SharePoint Blog
http://www.sharepointblogs.com/rhouberg
•
Capacity Planning for tempdb
http://msdn2.microsoft.com/en-us/library/ms345368.aspx
•
Estimate Performance and Capacity Requirements for Search Environments
http://technet.microsoft.com/en-us/library/cc262574.aspx
•
Performance Recommendations for Storage Planning and Monitoring
http://go.microsoft.com/fwlink/?LinkID=105623&clcid=0x409
•
“Office SharePoint Server 2007 – Administrator’s Companion”
Bill English with the Microsoft SharePoint Community Experts, Microsoft Press
•
How to Defragment Windows SharePoint Services 3.0 and MOSS Databases
http://support.microsoft.com/default.aspx/kb/943345/
•
Physical Storage Recommendations (Office SharePoint Server)
http://technet.microsoft.com/en-us/library/cc298801.aspx
7/6/2015
© 2008 KnowledgeLake, Inc.
21