how microsoft built mysql, postgresql and mariadb for the ... · how microsoft built mysql,...
TRANSCRIPT
![Page 1: How Microsoft Built MySQL, PostgreSQL and MariaDB for the ... · How Microsoft Built MySQL, PostgreSQL and MariaDB for the Cloud. 2 Azure Infrastructure Services Azure Service Fabric](https://reader034.vdocuments.us/reader034/viewer/2022042223/5ec98aaab83f5f77ec2d47f0/html5/thumbnails/1.jpg)
Santa Clara, California | April 23th – 25th, 2018
How Microsoft Built MySQL, PostgreSQL and MariaDB
for the Cloud
![Page 2: How Microsoft Built MySQL, PostgreSQL and MariaDB for the ... · How Microsoft Built MySQL, PostgreSQL and MariaDB for the Cloud. 2 Azure Infrastructure Services Azure Service Fabric](https://reader034.vdocuments.us/reader034/viewer/2022042223/5ec98aaab83f5f77ec2d47f0/html5/thumbnails/2.jpg)
2
Azure Infrastructure ServicesAzure Service Fabric
Azure Data Service Architecture
• Share Cluster with SQL DB
• Cluster is decomposed into Azure Service Fabric applications (10+ applications)
• All applications and all tenants are individually deployable
• DB engine Instances are “services” managed by Azure Service Fabric
Provisioning services
Control Plane
TelemetryConnection
Proxy
Data Plane
Control data store
Port Sharing Service
MySQL/PG/SQL tenants
Resource Governance
Node health
Azure Storage
![Page 3: How Microsoft Built MySQL, PostgreSQL and MariaDB for the ... · How Microsoft Built MySQL, PostgreSQL and MariaDB for the Cloud. 2 Azure Infrastructure Services Azure Service Fabric](https://reader034.vdocuments.us/reader034/viewer/2022042223/5ec98aaab83f5f77ec2d47f0/html5/thumbnails/3.jpg)
Azure Storage Azure ComputeAzure Networking
Global Azure with 38 Regions
SQL Database SQL DW
Database Services Platform
Database Jobs
Orchestration of
Management
Workflows
Backup Manager &
Backups retention,
PITR and Geo-
Restore
Azure Resource Manager APIs,
Client Tools, Portal integration
Active Geo-ReplicationLocal Replication for HA
Service Fabric
High
Availability
Hyper-ScaleHigh
DensityRolling
Upgrades
Stateful
services
Low Latency Cluster
messaging
Fast startup &
shutdown Container Orchestration &
lifecycle management
Failure Detection &
FailoverLoad balancing
Self-healing
Automated
Rollback
Health
Monitoring
Placement Services
Location Services & Routing
Resizing Service
Continuous Delivery
through Deployment
Automation
SMART
Monitoring Proactive Analytics
and Alerting
Auto-mitigation of
LiveSite incidents
Workload Insights
Connection Proxy
and Connection
Redirection Cross-region
and in-region
data migration
Data Security & Compliance
Resource Governance and Resource
Isolation per Server/Database
MySQL/MariaDB
SQL Server 2017 SQL Server 2017 + PDW MySQL/MariaDB Server
A/B Testing
Azure Monitoring
Azure Relational Database Services PlatformPostgreSQL
PostgreSQL Server
![Page 4: How Microsoft Built MySQL, PostgreSQL and MariaDB for the ... · How Microsoft Built MySQL, PostgreSQL and MariaDB for the Cloud. 2 Azure Infrastructure Services Azure Service Fabric](https://reader034.vdocuments.us/reader034/viewer/2022042223/5ec98aaab83f5f77ec2d47f0/html5/thumbnails/4.jpg)
Microsoft Azure Service FabricA platform for reliable, hyperscale, microservice-based applications
Microservices
Service Fabric
High
Availability
Hyper-
Scale
High
DensityRolling
Upgrades
Stateful
services
Low Latency
Cluster
messaging
Fast startup
& shutdown
Container Orchestration
& lifecycle management
Failure
Detection &
Failover
Load
balancing
Self-healing
Automated
Rollback
Health
Monitoring
Placement
Services
Location Services & Routing
Resizing Service
![Page 5: How Microsoft Built MySQL, PostgreSQL and MariaDB for the ... · How Microsoft Built MySQL, PostgreSQL and MariaDB for the Cloud. 2 Azure Infrastructure Services Azure Service Fabric](https://reader034.vdocuments.us/reader034/viewer/2022042223/5ec98aaab83f5f77ec2d47f0/html5/thumbnails/5.jpg)
A set of machines that Service Fabric stitches together to form a cluster
One cluster can scale to 1000+ machines
Cluster: A federation of machines
Node
Node
Node
Node
Node
Node
![Page 6: How Microsoft Built MySQL, PostgreSQL and MariaDB for the ... · How Microsoft Built MySQL, PostgreSQL and MariaDB for the Cloud. 2 Azure Infrastructure Services Azure Service Fabric](https://reader034.vdocuments.us/reader034/viewer/2022042223/5ec98aaab83f5f77ec2d47f0/html5/thumbnails/6.jpg)
Relational data services – Control Plane
Node
Node
Node
Node
Node
Node
Database Connection Redirector/Proxy
Management Service
Front-end Services (GW)
Cluster Control Services (MN)
Cluster Metadata(CMS)
One cluster per region managed by service fabric
Provides front-end and cluster control services.
![Page 7: How Microsoft Built MySQL, PostgreSQL and MariaDB for the ... · How Microsoft Built MySQL, PostgreSQL and MariaDB for the Cloud. 2 Azure Infrastructure Services Azure Service Fabric](https://reader034.vdocuments.us/reader034/viewer/2022042223/5ec98aaab83f5f77ec2d47f0/html5/thumbnails/7.jpg)
Db tenant2 Files
Db tenant1 Files
Relational data services – Data Plane
Node
Node
Node
Node
Node
Node
Platform Services
Node 1
Azure Storage
Db log Files
Db Files
Application ServicesOne to many clusters per region managed by service fabric
Each node has application services (MySQL server) and platform services
![Page 8: How Microsoft Built MySQL, PostgreSQL and MariaDB for the ... · How Microsoft Built MySQL, PostgreSQL and MariaDB for the Cloud. 2 Azure Infrastructure Services Azure Service Fabric](https://reader034.vdocuments.us/reader034/viewer/2022042223/5ec98aaab83f5f77ec2d47f0/html5/thumbnails/8.jpg)
8
Multi-tenancy
• Multi-tenancy is really hard• Noisy neighbors; accidental or intentional abuse
• Different levels of multi-tenancy have different tradeoffs in cost, capacity and density
• More sharing leads to greater efficiencies but adds more points of contention
• Expectations on performance predictability need to be managed via min guarantee and max caps across different hardware SKUs
On prem/stamp
VM/IaaS
OS sharing
Process sharing
![Page 9: How Microsoft Built MySQL, PostgreSQL and MariaDB for the ... · How Microsoft Built MySQL, PostgreSQL and MariaDB for the Cloud. 2 Azure Infrastructure Services Azure Service Fabric](https://reader034.vdocuments.us/reader034/viewer/2022042223/5ec98aaab83f5f77ec2d47f0/html5/thumbnails/9.jpg)
9
Our Solution
• Running a strip down version of latest Windows in a security container (SQLPAL)• Strong Security Isolation
• Strong Resource Isolation
• Less memory footprint (compare to a Full OS)
• Less attack surface (Lock down to bare minimum for engine)
• Leverage Microsoft SQL Server schedulers and memory management
• Resource Governance combined with Native Windows and SQLPAL• CPU
• Memory
• Disk
• Network
![Page 10: How Microsoft Built MySQL, PostgreSQL and MariaDB for the ... · How Microsoft Built MySQL, PostgreSQL and MariaDB for the Cloud. 2 Azure Infrastructure Services Azure Service Fabric](https://reader034.vdocuments.us/reader034/viewer/2022042223/5ec98aaab83f5f77ec2d47f0/html5/thumbnails/10.jpg)
10
SQL Platform Abstraction Layer (SQLPAL)
• Windows Host Extension has a driver for creating the Pico process and a monitor process (user mode) that implements non-perf related ABIs.
• ABI calls are handled by the driver and are either handled directly (Like File IO) or are marshalled to the monitor process for handling (Like File Open)
Ring 3
DBMS
Win32
SOSv2LibOS
Ring 0Host Extension(HE)
Windows Kernel
SQLPAL
Non-WindowsWindows
![Page 11: How Microsoft Built MySQL, PostgreSQL and MariaDB for the ... · How Microsoft Built MySQL, PostgreSQL and MariaDB for the Cloud. 2 Azure Infrastructure Services Azure Service Fabric](https://reader034.vdocuments.us/reader034/viewer/2022042223/5ec98aaab83f5f77ec2d47f0/html5/thumbnails/11.jpg)
11
Decoupled Compute and Storage
• Remote Storage built on top of commodity hardware
• Different optimizations for I/O Path of Log and Data files• Log files require low latency write, sequence read when crash recovery
• Data files require high throughput, random read/write
• Snapshot based backup• Never possible for huge amount of data through other ways (TB+)
• Snapshot Support
• PITR support
![Page 12: How Microsoft Built MySQL, PostgreSQL and MariaDB for the ... · How Microsoft Built MySQL, PostgreSQL and MariaDB for the Cloud. 2 Azure Infrastructure Services Azure Service Fabric](https://reader034.vdocuments.us/reader034/viewer/2022042223/5ec98aaab83f5f77ec2d47f0/html5/thumbnails/12.jpg)
12
Security Enhancement
• Network Security• VNET
• Firewall Support
• Both Inbound and outbound lock down
• Port Sharing Service (One per Node)• One port listen for each server
• Duplicate the socket and SSL security context to the real instance
• Encrypt-At-Rest
• Threat Detection
![Page 13: How Microsoft Built MySQL, PostgreSQL and MariaDB for the ... · How Microsoft Built MySQL, PostgreSQL and MariaDB for the Cloud. 2 Azure Infrastructure Services Azure Service Fabric](https://reader034.vdocuments.us/reader034/viewer/2022042223/5ec98aaab83f5f77ec2d47f0/html5/thumbnails/13.jpg)
13
Rate My Session
![Page 14: How Microsoft Built MySQL, PostgreSQL and MariaDB for the ... · How Microsoft Built MySQL, PostgreSQL and MariaDB for the Cloud. 2 Azure Infrastructure Services Azure Service Fabric](https://reader034.vdocuments.us/reader034/viewer/2022042223/5ec98aaab83f5f77ec2d47f0/html5/thumbnails/14.jpg)
Thank You!