Amazon AWS and RDS, moving towards it

1 .Amazon AWS and RDS, moving towards it Dimitri Vanoverbeke Solution Engineer @ Percona 

2 .Who am I? • Solution engineer in Percona for almost 4 years • Prior to Percona an open source system integrator • MySQL, puppet, General Linux, … • I’m a Belgian, located in the city of Ghent! • Aka Dim0 2 

3 .What is AWS? • Acronym for Amazon Web Services • A division of amazon.com (the book retailer) • Operates as a set of connected services § EC2, LB, Beanstalk, containers, application services, messaging, identity management, … • EVERYTHING IS A SERVICE! • Fun fact: AWS came to be first to support MySQL as cloud solution • amazon.com until they finished this concept 3 

4 .Why could you use AWS? • Easy to configure and deploy! § Often used in startup mode but also in mature companies. • Easy solution to have infrastructure. § Often used to move to a public cloud offering (ask your manager why) • No high step in cost! You pay for what you use. § EC2 services are sometimes within a free tier or with limited cost § Something your manager will appreciate § Costs are incremental • You can limit the amount of systems based on usage § Auto scaling application servers, … • No infrastructure management § It will give you less worries, if all works well :-D 4 

5 .Why shouldn’t you use AWS? • You prefer to get your hands dirty yourself. § You feel satisfaction when configuring switches, replacing raid controllers, enclosures and other pleasureful hardware actions. • You don’t trust external partners maintaining your infrastructure. § You wake up at night in cold sweat thinking about the lack of visibility you have in the cloud providers Datacenter § You are essentially running your infrastructure on ‘other people’s computers’ • You have extremely stringent availability concerns however, typically people get a better uptime in the cloud vs. own equipment. § You require 99.99999999999 % uptime for your infrastructure. 5 

6 .What will we discuss? • Amazon EC2 § What is it? § How to migrate into EC2. • Amazon RDS § What is it? § How to migrate into RDS. § Operational optimizations • Amazon Aurora § What is it? § How to migrate into Aurora. 6 

7 .Amazon EC2 • Elastic Compute capacity § Virtual server with attached storage § You can choose which service you run on it: • MySQL • MariaDB • Percona Server • Cluster options like Galera/Percona XtraDB cluster/MariaDB Cluster • Mix databases with application servers (not a best practice ;-)) § Operational management is still performed by your team or external § Monitoring using cloudwatch or external services (PMM, vividcortex, clustercontrol, …) § It’s Xen based 7 

8 .When should you use Amazon EC2? • If you have DBA’s/sysadmins in house § Flexible solution for DBA’s § Control over choices, less of a black box § Technologies like Galera or XtraBackup are available § Ability to deep dive troubleshoot you issues § Lower cost than Amazon RDS • If you want to use Percona Server, or have access to all configuration options. § You can even install very old versions of MySQL (not advised) 8 

9 .Example architecture of Amazon EC2 9 

10 .Migrating to Amazon EC2 • Use Percona Xtrabackup § Non locking backup for InnoDB storage engine § Supports MyISAM storage engine • Create a EC2 slave for your current MySQL environment • Switch over will demand only a very limited amount of downtime • You could even use a Percona XtraDB cluster to add in nodes (not recommended if write heavy workload) 10 

11 .Migrating to Amazon EC2 11 

12 .Amazon RDS what is it? • Amazon Relation database service • DBAAS solution offer covering the following technologies § MySQL since 2009 § Oracle Database § Microsoft SQL server § PostgresSQL § Amazon Aurora § MariaDB • Complete GUI interface for management of the instance’ • Maintenance and updates will be performed during a predefined moment. • Competitors: Oracle cloud, Rackspace cloud databases, Google cloud SQL, … 12 

13 .Amazon RDS what is it? • Fully managed MySQL database environment § Support for multi AZ synchronous replication (comparable to DRBD) § Support for read replica’s § Can failover to read replica § Backups daily and Point in time recovery using binlogs internally. § Easy scalable (maintenance will be performed in your maintenance window or immediately.) • Monitoring § Percona monitoring and management (PMM), Vividcortex, … 13 

14 .When should you use Amazon RDS • When you want to outsource the engineering around creating a MySQL infrastructure. § Your system engineers and DBA’s can be working on other jobs/tasks. § Scalability concerns, at this point you cannot predict the amount of growth you will experience. § HA concerns, building a high availability solution can be a complex thing to maintain and engineer. • To Save time (easy spin up) § And essentially save money, as time is apparently related to money… • To move to the cloud because your manager told you so. 14 

15 .When to avoid using Amazon RDS • You want specific Tuning for specific workloads (difficult in RDS) • Limited diagnostics and Capabilities § You have no direct access to the infrastructure § You cannot use GDB or strace to troubleshoot the infrastructure. § You cannot verify the logs easily – or actually verify the iops • A lot is mitigated using the API • If you need active active synchronous replication (if Galera, then use EC2 instances.) • You want a hybrid solution. • You cannot handle the additional cost 15 

16 .Some limitations in RDS (or recommended limitations) • Limited to 10000 tables if you are using provisioned iops • Limited to 1000 tables if your database is smaller than 200 GB using general purpose or have standard storage. • The moment you go over these numbers, Amazon recommends you to move to 1 InnoDB table space (innodb_file_per_table=0) • 6 TB of data 16 

17 .Current generation of Amazon RDS db’s available • Standard generation § Db.m4.large (2vcpu, 8GB) – db.m4.10xlarge (40vcpu’s, 160GB RAM, storage EBS) • Memory optimized generation § Db.r3.large (2vcpu’s, 15,25 GB RAM, SSD 32GB) – db.r3.8xlarge (32 vcpu’s, 244 GB RAM, 2x 320GB SSD) • Burst capable generation § Db.t2.micro(1 vcpu, 1GB memory) – db.t2.large (2 vcpu’s , 8GB RAM) § Baseline performance 17 

18 .Some caveats • You want consistent performance. § Choose provisioned IOPS § Automated updates (optin or opt out) • Make sure you have read performance § Create replication slaves (Read replica’s) § Separate your reads and move them to your read replica’s • Use VPC’s § This means you have separate networks 18 

19 .Usage optimisations • Make sure your database is set up in UTC prior to moving, some people however choose to go a local time zone like CET. § It’s going to save you issues • Max allowed packet is default pretty low, in MySQL 5.6 it’s 16MB, when using RDS it’s 4MB § You can work around it with ‘skip-extended-insert’ in your dump. • You don’t have the extra user access (extra super connection) when something is problematic. • You have to call out amazon designed functions if you want to kill queries or other administrative actions. • You can download the logs and use pt-query-digest for example to verify the query performance. 19 

20 .You don’t have root nor super privilege • Kill a query: § CALL mysql.rds_kill_query(queryID) • Define an external master: § CALL Mysql.rds_set_external_master • InnoDB cache warming § CALL mysql.rds_innodb_buffer_pool_dump_now § CALL mysql.rds_innodb_buffer_pool_load_now • Modify the binlog retention configuration § CALL mysql.rds_set_configuration 20 

21 .If you need to use triggers • Due to the absence of the super privilege you cannot simply create triggers as in normal MySQL. ERROR 1419 (HY000): You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) • You can solve this by following adding a parameter group and activating it: aws rds modify-db-parameter-group \ --db-parameter-group-name allow-triggers \ --parameters "name=log_bin_trust_function_creators,value=true, method=pending-reboot” https://www.percona.com/blog/2014/07/02/using-mysql-triggers-and-views-in- amazon-rds/ • Keep in mind that triggers inside a dump might be problematic. 21 

22 .Backups with Amazon RDS § Everything using a MySQL client works easily: • MySQL dump • MyDumper § Amazon Snapshots • Only works with InnoDB • If you use myisam make sure you stop all activity prior to the snapshot. • Really try to limit yourself to InnoDB § Default retention period is only 7 day (change this if necessary :-D) 22 

23 .How to Migrate to Amazon RDS? • Standard dump and import procedure § Keep in mind that you cannot import triggers and stored procedure by importing due to the lack of a SUPER user § --routines=0 --triggers=0 --events=0 § Maybe use force for the MySQL import if time is of the essence. • Replicate to Amazon RDS system to import information § Migrate from Physical hardware to Amazon RDS § Fundamentally the same to MySQL replication § Limiting downtime to your MySQL database infrastructure. § Replicating to RDS but not with MySQL versions earlier than 5.1 23 

24 .Some pointers • Use mysqldump or mydumper § Supports InnoDB and other storage engines • But really try to use InnoDB. § Use myloader or mysqlimport to import the information. • Use an EC2 machine to import the information to limit network bottlenecks. • VERIFY the consistency of the database environment! • Perform your own verification, check your character sets, verify if your load will work. 24 

25 .Moving to RDS (example) 25 

26 .Moving to RDS (example) 26 

27 .Moving to RDS (example) 27 

28 .Moving to RDS (example) 28 

29 .Replicating back out of RDS • You can replicate out of Amazon RDS towards for example an EC2 machine. • You will have to dump and restore the data though for the initial MySQL dump. • It’s probably going to be a more painful process so make sure if you choose to go the RDS product, you are sure about it’s capabilities and function. • Several Nines have an interesting blog post series about this situation just released this week: https://severalnines.com/blog/mysql-cloud-online-migration-amazon-rds-your-own- server-part-2 29