Transcript Dell DVD Store - Part 1x

Scaling the DVD Store
From 100 to a million TPM
Bob Duffy
Database Architect
MTUGd
 http://www.meetup.com/MTUGD-IE/
 http://www.meetup.com/AzureDublin/
SSIUG
 www.sql.ie/join
 Blog: https://ssiug.wordpress.com/
 @SQLIreland
Speaker - Bob Duffy







20 years in database sector, 250+ projects
Senior Consultant with Microsoft 2005-2008
One of about 25 MCA for SQL Server globally (aka SQL Ranger)
SQL MCM on SQL 2005 and 2008
SQL Server MVP 2009+
SSAS Maestro
Database Architect at Prodata SQL Centre of Excellence
 http://blogs.prodata.ie/author/bob.aspx
 [email protected]
Agenda
 Part I – Tuning the DVD Store
 Remove bottlenecks
 More resources
 Part II – Scaling the DVD Store
 Reduce reliance on database
 Move to the cloud
 Scale out Solution
Scaling the DVD Store
Part 1
Tuning and Scale Up
A Typical.net web Application?






Dell DVD Store Database Test Suite
Sample ecommerce application
Available for SQL, Oracle, MySQL
Includes Test Driver and Web Application
Used by both dell and VMWare for benchmarks
http://linux.dell.com/dvdstore/
Great for quick comparative Benchmarks
Version 2.0 is great example has lots of room for
improvements in its data access layer.
Where/What is slowing my Solution
Client
Application
Client
Client
Presentation
Logic
Data
Tier
Interactions
Database/
External
Platform
Connections
Plan Cache
Queries
Resource
CPU
RAM
Disk
Network
Presentation
Logic
Data
Connections
Plan Cache
Queries
Resource
CPU
RAM
Disk
Network
DMV
Trace
Perfmon Counters
Client
Visual Studio Profiling Tools
Client
Visual Studio Web
Test
What Tools to Monitor ?
Load Test Baseline
Demo
1. The baseline Results
 2 Cores, 10 Threads, Ad Hoc Sql. 100 OPM
Tools Used





Visual Studio Performance Profiling
SqlDiag – Generate Trace
RML or SQL Nexus
Perfmon
SQL DMV
 Wait Statistics
 Exec_requests
 Plan Cache
2. With Index + location of code bug
 1,400 OPM
3. Connection Pooling
 2,600 OPM
5. With Parameterisation
 7,000 OPM
6. Stored Procs
 11,000 OPM. What’s Next ?
Summary Throughout so far…
Scenario
baseline
without verification
Add Missing indexes
With Pooling
Paramterised
Stored Procs
OPM
Max RT x Change
100
3,100
900
1,200
9
1,400
900
14
2,900
464
29
8,700
200
87
11,000
54
110
The Dell DVD Store
Part II – Scaling your workload
Initial Scaling 2-16 cores
Scenario
2 Core
4 Core
8 core
16 Core
OPM
Threads Max RT % Improvement
11,000
10
54
22,000
20
52
2.0
44,000
40
54
2.0
77,000
48
38
1.8
Analysis of Bottlenecks?
Scaling Out Web Tier
Co-located
Cache
Web Role
Web Role
SQL
Database
Scale out Web/test Server


81,000 TPM
What Happens if we optimise the SQL Workload and cache data ?
Web Server
Added Data Caching
 112,000 TPM
 Try “Hekaton” – In memory OLTP ?
Upgrade to Hekaton- In Memory OLTP
 Back to 2K TPM ;-(
After Fixing Hekaton Schema
 Still only about 112k. Web Server at 100%
Delayed Durability
 New to SQL 2014
 Up to 160k TPM ;-)
 Obviously a risky proposition
Conclusion on Scale up database
 1 Million TPM not possible on cloud 16 core
 “possible” on a 32 cloud core
 If we use SSD/premium storage
 If we offload login
 If we “fix” latch contention
 Use heaps or random keys
 Cost: 8k per month for 32 core for EE
 Maybe 3k per month for STD
 Under 1k euro a month if you have free licenses ;-)
Scaling Out to “Web Scale”
Co-located
Cache
Web Role
Web Role
Worker
Role
SQL
SQL
SQL
Database
Database
Database
Dedicated
Cache
Capacity Planning – SQL Azure
 Premium P3 – 800 DTU
 About 60k TPM per Azure Database
 16 PREMIUM databases needed for 1 million TPM
 55k euro per month!
Capacity Planning – SQL Azure
 Premium P1 – 125 DTU
 About 8k TPM per Azure Database
 125 databases needed for 1 million TPM
 43k euro per month
Capacity Planning – SQL Azure
 Standard S3– 125 DTU
 About 7k TPM per Azure Database
 142 databases needed for 1 million TPM
 16k euro per month
Capacity Planning – SQL Azure
 Basic – 5 DTU




800 TPM
1,250 databases needed
Only 4 euro per month per DB ;-)
5k euro per month total
Comparing Cost for 1 Million TPM
SKU
DTU
DBs
Cost 000/m
5
1,250
5
60
S2
100
142
16
50
P1
125
125
45
P3
800
16
55
G5 (32 Core)
???
1
8
Basic
Cost for million TPM
40
30
20
10
0
Cost 000/m
Basic
S3
P1
P3
G5 (32 Core)
Introducing Elastic DB
 Client DLLs to use in App Tier
 Microsoft.Azure.SqlDatabase.ElasticScale.Client.dll
 Supports required functions




In Memory ShardMap
Add/Split/drop Shard
Multi shard Query
Data Dependant Routing
Capacity Planning – Application Tier
 One 16 Core Server can do 125K OPM
 Need 20 x 8 core servers if synchronous
 Less if Worker Role
Web Scale(12x8 core)
 105 MILLION TPM. All Web Servers at 80%
 10 Database Servers with Queueing
Conclusion – Scaling the DVD Store
10000
9000
8000






Baseline
Application Changes
Indexing
Connection Pooling
Parameterisation
Stored Procs / Chattiness



4 Core
8 Core
16 Core


Data Caching
10 Web Server,
MQ and sharded DB
1000
100
7000
6000
5000
4000
3000
2000
1000
0
10
1
Thank you!
Next Event – Half Day Seminar (free)




Common TSQL Mistakes
http://tsqlmistakes.eventbrite.ie
19th June 13:30-17:30
With kevin Boles
 US SQL performance guru