optimize mysql workloads with amazon elastic block store - february 2017 aws online tech talks
Post on 03-Mar-2017
226 Views
Preview:
TRANSCRIPT
© 2017, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
Vivek R – AWS Solutions ArchitectRicky Chang – AWS Professional Services
February 23, 2017
Optimize MySQL Workloads with Amazon Elastic Block Store
Agenda
• MySQL Deployment Options on AWS• Why use Elastic Block Store (EBS)?• Optimizing MySQL with EBS• Testing for Performance• Best Practices
MySQL Deployment Options
RDS and MySQL on EC2
MySQL on EC2 instance
• Flexible• Deployment Options
- Infrastructure- Storage
• Access to the OS• MySQL plugins support
RDS for MySQL
• No Infrastructure Management• Instant provisioning• Application Compatibility• HA and Read Replica• Managed Patching, Maintenance, Backup and Restore
Why Storage?
Durability and Monitoring
MonitoringCloudWatch
• 5 min or 1 min polling interval• Setup alarms and actions• Integrate with MySQL Monitoring
Tools
Durability• Data Persistence vs. Instance
Store• Decouple Storage from Compute• Redundancy within the AZ
Provisioned IOPS SSDHighest Performance
Volume Size: 4 GiB to 16 TiBMax IOPS/Volume: 20,000Max Throughput/Volume: 320 MiB/s
General Purpose SSDPrice and Performance
Volume Size: 1 GiB to 16 TiBMax IOPS/Volume: 10,000Max Throughput/Volume: 160 MiB/s
Primer - Storage Options
Snapshots and Security
Security• IAM Resource Level Permissions• Data at Rest Encryption
Snapshots• Entire MySQL Database Backup• Scheduled snapshots using
CloudWatch Events• Use for DR, Storage Resize,
Sharing Environment
Elastic Volumesaws ec2 modify-volume --region us-east-1 --volume-id vol-11111111111111111 --size 200 --volume-type io1 --iops 10000{ "VolumeModification": { "TargetSize": 200, "TargetVolumeType": "io1", "ModificationState": "modifying", "VolumeId": "vol-11111111111111111", "TargetIops": 10000, "StartTime": "2017-01-19T22:21:02.959Z", "Progress": 0, "OriginalVolumeType": "gp2", "OriginalIops": 300, "OriginalSize": 100 }}
Elastic Volumes• Ease of Administration• Change while online
• Change Volume Type• Change Provisioned
IOPS• Increase volume size
Optimization
© 2016, Amazon Web Services, Inc. or its Affiliates. | Amazon Confidential
Making the most out of (GP2)
Max I/O credit per bucket is 5.4M
You can spend up to 3000 IOPS per second
Baseline performance = 3 IOPS per GiB or 100 IOPS
Always accumulating3 IOPS per GiB per second
gp2
© 2016, Amazon Web Services, Inc. or its Affiliates. | Amazon Confidential
Burst & Baseline: General Purpose SSD (GP2)IO
PS
0 1 16
1,000
2,000
3,000
8,000
10,000
BASELINE IOPS(Baseline of 3 IOPS/GiB)
Burstable to 3,000 IOPS
3 90.5
Volume Size (TiB)
~ 3334 GB
© 2016, Amazon Web Services, Inc. or its Affiliates. | Amazon Confidential
Time to Deplete a Full GP2 Credit Bucket
1 8 30 100 150 200 250 300 350 400 450 500 550 600 650 700 750 800 850 900 9500
100
200
300
400
500
600
700
Volume Size in GiB
Min
utes
of B
urst
43 Min 1 Hour
10 Hours
Monitor your Burst usage
Typically, Burst IOPS are consumed by: Performing MySQL Backups Restarting MySQL or EC2 Large import and export of MySQL data Additional traffic to the MySQL database
coming from the application
Making the most out of io1
EBS Optimized Instances• Dedicated Bandwidth to EBS 500 Mbps to 12,000 Mbps• Increase Throughput and IOPS
Making the most out of Replication
Need more IOPS and Throughput?
RAID 0 - Considerations• Use EBS optimized instance• Fault tolerance, MySQL Slave• Use the same EBS volume typeand size• Ensure all volumes are quiescedwhen performing EBS snapshots
Caching and Writes
MySQL ConfigurationParameters Default Tuned* Description
innodb_buffer_pool_size 128MB 70-80% % of Memory or large enough for “Hot” data set
innodb_flush_neighbors 1 0 Spreads write operations over your EBS volumes
innodb_log_file_size 50MB * Large enough to spread-out the checkpoint flush activity
* Based on your write workloads
MySQL Master/Slave
Switching from a Physical Server to EC2
sync_binlog
sync_master_info
sync_relay_log
sync_relay_log_info
Improve IO and Throughput
• Separate the logs and data files into different EBS volumes
• Use io1 for bin and relay logs
• RAID 0 – Stripe EBS Volumes
• Extreme cases – use local SSD for Slave or read replica
Backup Server as target
EFS and S3 as Target
EBS Snapshots – best practices
1. Quiesce data before taking snapshotsflush tables with read locksync and fsfreeze
2. Warm up data during the restoreParallel dd, fio, robocopyScan largest table
3. Automate and Manage Snapshotshttp://docs.aws.amazon.com/AmazonCloudWatch/latest/events/TakeScheduledSnapshot.htmlhttps://github.com/awslabs/aws-ebs-custodian
Performance Testing
Latency
• Check for available IOPS
• Verify burst credits for gp2
• Watch out for high disk queue length
ThroughputIssues:• Database replication• Backup and Restore• Import/Export• Index Creation
Increase Throughput by:• RAID 0• Amazon Provisioned IOPS SSD
320 MiB/s• EBS Optimized Instance
Benchmarking Tools• Tools from MySQL such as
MySQL Enterprise Monitor MySQL Workbench Performance MySQL Query Analyzer
• MySQL Monitoring tools at the AWS Marketplace
• Amazon CloudWatch
• MySQL performance Schema
Demo
Best Practices
Best Practices
• Evaluate use case for Amazon RDS or MySQL on EC2• Monitor burst metrics for gp2• Use EBS optimized EC2 instances for io1• Use EC2 placement groups for replication• Test your MySQL workloads• Verify MySQL parameters• Monitor service limits
Thank you!
top related