Upload
msngod
View
220
Download
0
Embed Size (px)
Citation preview
7/31/2019 VMWare Forum - Orlando (05!22!2008)
1/28
SQL Server on VMware
Jonathan Kehayias (MCTS, MCITP)
SQL Database Administrator
Tampa, FL
7/31/2019 VMWare Forum - Orlando (05!22!2008)
2/28
Agenda
SQL Initiative Overview
Performance Metrics
I/O Metrics
Processor
Performance Counters
High Availability Configuration
Manageability Gains
7/31/2019 VMWare Forum - Orlando (05!22!2008)
3/28
SQL Initiative
Primary ObjectiveDisaster Recovery
Success Criteria
Immediate failover to remote data center with minimal data loss
Performance
7/31/2019 VMWare Forum - Orlando (05!22!2008)
4/28
PerformanceVMware ESX Server, 3.0.2, 63195
GENERAL
Manufacturer: HP
Model: ProLiant BL460c G1
Processors: 4 CPU x 2.666 GHzProcessor Type: Intel(R) Xeon(R) CPU 5150 @ 2.66GHz
Hyperthreading: Inactive
Total Memory: 24.00 GB
Number of NICs: 4
Virtual Machines: 7
VMotion Enabled: Yes
7/31/2019 VMWare Forum - Orlando (05!22!2008)
5/28
PerformanceSQL Virtual Machine Configuration
GENERAL
Guest OS: Microsoft Windows Server 2003 R2 Standard x64 Edition
CPU: 2 vCPU
Memory: 4096 MBMemory Overhead: 263.96 MB
Disk Configuration: RAID 6+2
C/D 50GB XP10K
F 350GB XP10KG 350GB XP10K
7/31/2019 VMWare Forum - Orlando (05!22!2008)
6/28
PerformanceSQLIO Benchmarks
SQLIO is a tool provided by Microsoft which can also beused to determine the I/O capacity of a given configuration.
SQL Server stores data 8K pages allocated in blocks of 8 as64K extents. Typical SQL I/O operations involve RandomReads of extenteRs from disk.
SQLIO benchmarks on this SQL Server for 64K Random
Read I/O with 2 threads simulating the recommendedsetting of one file per processor core for SQL Server wereequivalent or better than common physical hardware.
7/31/2019 VMWare Forum - Orlando (05!22!2008)
7/28
Performance (VMWare)Random Read I/O By The Numbers
Drive Format Test IOs/sec MBs/sec
READ
Default Read 8KB random 2286.33 17.86Default Read 64KB random 1940.86 121.30Default Read 128KB random 1059.25 132.40Default Read 256KB random 500.54 125.13Default Read 8KB sequential 2549.82 19.92Default Read 64KB sequential 1397.24 87.32Default Read 128KB sequential 766.72 95.84Default Read 256KB sequential 466.00 116.50
WRITEDefault Write 8KB Random 1475.78 11.52Default Write 64KB Random 609.97 38.12Default Write 128KB Random 366.03 45.75Default Write 256KB Random 210.17 52.54Default Write 8KB Sequential 1560.25 12.18Default Write 64KB Sequential 634.69 39.66Default Write 128KB sequential 222.10 27.76Default Write 256KB sequential 165.52 41.38
7/31/2019 VMWare Forum - Orlando (05!22!2008)
8/28
Dell PowerEdge 6650 2x 2.7 GHz 2GB RAM4x 36GB RAID 10 array
Drive Format Test IOs/sec MBs/sec
READ
Default Read 8KB random 656 5.1Default Read 64KB random 258 16.1Default Read 128KB random 149 18.6Default Read 256KB random 82 20.4Default Read 8KB sequential 3246 25.4Default Read 64KB sequential 378 23.6Default Read 128KB sequential 188 23.5Default Read 256KB sequential 94 23.5
WRITEDefault Write 8KB Random 1567 12.2Default Write 64KB Random 809 50.5Default Write 128KB Random 486 60.7Default Write 256KB Random 333 83.2Default Write 8KB Sequential 7112 55.6Default Write 64KB Sequential 1889 118.1Default Write 128KB sequential 977 122.1Default Write 256KB sequential 494 123.5
Physical SQLIO outputs were obtained from the SQL Server Performance website Forums:
http://sql-server-performance.com/Community/forums/t/15370.aspx
http://sql-server-performance.com/Community/forums/t/15370.aspxhttp://sql-server-performance.com/Community/forums/t/15370.aspxhttp://sql-server-performance.com/Community/forums/t/15370.aspxhttp://sql-server-performance.com/Community/forums/t/15370.aspxhttp://sql-server-performance.com/Community/forums/t/15370.aspxhttp://sql-server-performance.com/Community/forums/t/15370.aspx7/31/2019 VMWare Forum - Orlando (05!22!2008)
9/28
Dual 3.0Ghz Xeon Dual 2GB FC HBAIBM DS4300 SAN RAID 10 (10 x 74GB 15K)
Drive Format Test IOs/sec MBs/secSQLIO 8k sector
Read - Random 2096.15 16.38Read - Sequential 3651.36 28.53Write - Random 1985.08 15.51Write - Sequential 1914.68 14.96
SQLIO 32k sector
Read - Random 1653.85 51.68Read - Sequential 2736.05 85.50Write - Random 1125.63 35.18Write - Sequential 1337.12 41.79
SQLIO 64k sectorRead - Random 1280.91 80.06Read - Sequential 1889.55 118.10Write - Random 724.24 45.27Write - Sequential 889.01 55.56
Physical SQLIO outputs were obtained from the SQL Server Performance website Forums:
http://sql-server-performance.com/Community/forums/t/15370.aspx
http://sql-server-performance.com/Community/forums/t/15370.aspxhttp://sql-server-performance.com/Community/forums/t/15370.aspxhttp://sql-server-performance.com/Community/forums/t/15370.aspxhttp://sql-server-performance.com/Community/forums/t/15370.aspxhttp://sql-server-performance.com/Community/forums/t/15370.aspxhttp://sql-server-performance.com/Community/forums/t/15370.aspx7/31/2019 VMWare Forum - Orlando (05!22!2008)
10/28
DELL 6850 Quad Xeon 3.4ghz w/4GB RAMEMC Clariion CX-700 RAID 10
Drive Format Test IOs/sec MBs/sec
8k random write: 14884.96 116.2864k random write: 1573.03 98.31128k random write: 1110.79 138.84256k random write: 722.48 180.62
8k seq. write: 16545.36 129.2664k seq. write: 3142.00 196.37128k seq. write: 1573.62 196.70256k seq. write: 787.21 196.80
8k random read: 5776.05 45.1264k random read: 2857.16 178.57128k random read: 1567.23 195.90256k random read: 787.22 196.80
8k seq. read: 9200.54 71.8764k seq. read: 2805.23 175.32128k seq. read: 1506.95 188.36256k seq. read: 783.07 195.76
Physical SQLIO outputs were obtained from the SQL Server Performance website Forums:
http://sql-server-performance.com/Community/forums/t/15370.aspx
http://sql-server-performance.com/Community/forums/t/15370.aspxhttp://sql-server-performance.com/Community/forums/t/15370.aspxhttp://sql-server-performance.com/Community/forums/t/15370.aspxhttp://sql-server-performance.com/Community/forums/t/15370.aspxhttp://sql-server-performance.com/Community/forums/t/15370.aspxhttp://sql-server-performance.com/Community/forums/t/15370.aspx7/31/2019 VMWare Forum - Orlando (05!22!2008)
11/28
PerformancePerformance Counter Monitoring
To understand how well a SQL Server is performing the SQL Server as well asWindows Subsystem Performance Counters need to be Monitored.
Key Counters to monitor include:
Processor/%Processor Time should remain below 80%.
Processor/%Privileged Time should remain below 20%
SQL Server General/User Connections Batches/Sec
Page Life Expectancy
Pages/Sec
Memory Grants Pending
Lazy Writes/secFor further information, take a look at the following Screencast series by Kevin Kline(SQL Server MVP and Professional Association for SQL Server (PASS) President):
http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1302600,00.html
http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1302600,00.htmlhttp://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1302600,00.html7/31/2019 VMWare Forum - Orlando (05!22!2008)
12/28
PerformancePerformance Counters (%Processor Time)
< 80% average
7/31/2019 VMWare Forum - Orlando (05!22!2008)
13/28
PerformancePerformance Counters (Processor\%Privileged Time)
< 20% Average
7/31/2019 VMWare Forum - Orlando (05!22!2008)
14/28
PerformancePerformance Counters (User Connections)
This is just a reference counter to be used in tandem with other counters
to view system activity.
7/31/2019 VMWare Forum - Orlando (05!22!2008)
15/28
PerformancePerformance Counters (Batches/sec)
This is a reference to the amount of activity the Server is performing. It is
used along with other counters like Page Splits/sec to determine if there
are problems.
7/31/2019 VMWare Forum - Orlando (05!22!2008)
16/28
PerformancePerformance Counters (Buffer Cache)
Should remain as close to 100% as possible. Consistent drops below95-90% signals Memory Pressure
7/31/2019 VMWare Forum - Orlando (05!22!2008)
17/28
PerformancePerformance Counters (Page Life Expectance)
Page Life in the cache should ideally remain over 300 seconds.Consistent drops below this should be investigated and can signal
Memory Pressure on the server.
7/31/2019 VMWare Forum - Orlando (05!22!2008)
18/28
PerformancePerformance Counters (Memory pages/sec)
The rate at which pages are read from or written to disk. If > 100 on aslow disk subsystem or > 600 on a fast disk subsystem it should be
investigated.
7/31/2019 VMWare Forum - Orlando (05!22!2008)
19/28
Performance Conclusions
1: Compared to various disk configurations ofphysical implementations with local storage, weexperience 10x performance for disk subsystem I/O
2: Critical performance counters SQL maintainsindustry acceptable performance
3: Ability to consolidate multiple VMs along withSQL server, ~5 to 7:1 to save costs on physicalhardware, rack space, power, cooling and integrateinto DR plan.
7/31/2019 VMWare Forum - Orlando (05!22!2008)
20/28
Single SQL server per ESX host
Multiple SQL Servers never reside on the same ESX host
Logical placement of VMs to eliminate contention of resources
Web and App server never communicate with the SQL server on the same host
Average 5-7 to one consolidation ratio
AppServer
WebServer
AppServer
WebServer
AppServer
WebServer
AppServer
WebServer
Consolidation Architecture
7/31/2019 VMWare Forum - Orlando (05!22!2008)
21/28
High Availability
SAN Storage Mirrored for Disaster Recovery toChicago Datacenter.
Quarterly Failover Tests of key SQL Servers onChicago Network with zero data loss at failover.
7/31/2019 VMWare Forum - Orlando (05!22!2008)
22/28
High Availability
VMotion
Eliminates need for Mirroring
Solutions for Hardware
redundancy. In the event of a
host hardware failure the Virtual
Machines can be hot migrated
to another host
Allows live migration of Servers
during high load operation to
shift load to more powerful hosts
as needed.
7/31/2019 VMWare Forum - Orlando (05!22!2008)
23/28
Manageability
Snapshot Technology
Provides a point in time recovery point for risky operations such as
upgrading Server OS and or SQL Server.
Hot Add Disk Arrays
Allows Zero Down Time additions of new Storage LUNS as database
grow in size
HBA Load Balancing
Allows Disk I/O Load balancing across redundant paths to the SAN
storage.
7/31/2019 VMWare Forum - Orlando (05!22!2008)
24/28
Manageability
Rapid Scale UpAdding additional vCPUs and Memory is only a reboot away,
provided the host has available resources.
Easily upsize a server for end of month processing when it
requires the most power while minimizing its footprint while
under minimal load.
7/31/2019 VMWare Forum - Orlando (05!22!2008)
25/28
Manageability
VMWare InfrastructureClient
Immediate shared console levelaccess to the SQL Serverprovides remote administrationand rapid response for criticalserver outages.
No risk of a BSOD requiringphysical access to the server orof a stuck ILO interface on aserver.
Integrated performance
monitoring of processor,memory, disk and networkingcounters.
7/31/2019 VMWare Forum - Orlando (05!22!2008)
26/28
Manageability
VMWare Infrastructure Client (contd)Running history of Events occurring through the client including
Server Resets, Migrations, and Reconfigurations.
Integrated Alarms are configurable for out of tolerance counter
statuses.
7/31/2019 VMWare Forum - Orlando (05!22!2008)
27/28
Lessons Learned
1: Dont lock pages in
memory if you plan to doVMotion.
2: Be cautious whenimplementing MicrosoftRecommended BestPractices that affect system
configurations and test inDevelopment beforedeploying in Production.Some may not be compatiblewith VMotion.
3: Dont accept a vendors
statement that VMware is theproblem look deeper, andyou can generally disprovethis statement.
7/31/2019 VMWare Forum - Orlando (05!22!2008)
28/28
Conclusions
Performance Metrics
I/O Metrics 10x performance, Processor and Performance Counters
High Availability Configuration
VMotion to load balance during end of month reconciliations keepsahead of the business
Reduced downtime with hot add disk
Manageability Gains
SOX compliance for Disaster Recovery
Virtual Center Console for team collaboration
Cost AvoidanceReduces need for Mirroring hardware and software
Consolidation of hardware both primary and DR