Relational Databases in the Cloud - Great Wide Open 2014

In this session we'll go over the two most common open source relational databases. We'll start with a quick introduction to relational theory and an overview of why this technology is still powerful and relevant. We will address the challenges and operational specifics of developing against relational databases on Cloud environments.

Note: This talk is aimed to developers

Transcript

1. Relational Databases 
 in the Cloud
2. Inés 
 Sombra I NE S @ E NG I NE Y A R D.C O M @R A N D O M M O O D
4. Today’s Agenda DE VELOPM E NT 
 IN THE C LOUD 
 (A N D A D A SH O F O PS ) M YS QL REL ATI O NA L
 DATABASE S P O ST GR E SQ L
5. Http://Www.Flickr.Com/Photos/Nathaninsandiego/5819143579/ Let’s Get Started!
6. Relational Intro & Tenets
7. Relational Origin
8. Relational Databases DATA PERC EIVED BY USER S A S T ABL E S & ONLY T A BLE S OPERATO R S G E N E RAT E 
 N E W TA BL E S FROM OL D * Trivia Q1: Why ‘Relation’?
9. E NTI TI E S RE LAT I O N SH I PS C ON STR AINTS ATTR IB U T E S Relational Concepts
10. E N TI T I E S RE LAT I O N SH I PS C ON STR AINTS AT TR IB U T E S Relational Concepts
11. E N TI T I E S RE LAT I O N SH I PS C ON STR AINTS ATTR IB U T E S Relational Concepts
12. E N TI T I E S RE LAT I O N SH I PS C O N STRAINTS ATTR IB U T E S Relational Concepts
13. Relational Data Model
14. Relational Data Model
15. On SQL D M L : D AT A M A NIPU L ATI ON 
 (SE L EC T , I N S E R T, U PDATE … ) D DL : D AT A D EF INI TI ON (C R EATE, DR OP , A LTE R ) D C L : D AT A C O NTROL
 (GR A N T, RE V O K E)
16. Relational Properties ATOM IC I T Y C ON SI STE NCY I SOLAT I ON DURABILI T Y
17. Http://Www.Flickr.Com/Photos/Mukumbura/12828737425/ Our Databases
19. A Little Context INTRODUC ED 1 994(I SH)
 V5.6. 17 C URRE NT STABLE * Trivia Q2: Why ‘My’? V ER Y P E R VA S I V E D U E TO
 LAM P ST AC K & D E F AU LTS
20. Multi Masters 10/ 2 00 5 
 ORAC LE BUYS INN OBASE 02 / 20 0 6 
 OR AC LE B UY S S L E E PYCAT 01 / 20 0 8 
 SU N B UY S M YS Q L 04 - 08 /2 0 0 9 
 ORAC L E B U Y S S U N
21. Pro: Storage Engines * Trivia Q3: Current Default? INN OD B MYI S AM ME M OR Y C SV A R C H I VE
22. Pro: Distros & Clustering XT R A D B MA R I A D B WE B S C A L EDB T O KUD B GA L ER A
23. Pro: Extras C RA ZY MI GRATI ON S 
 ON REPL IC AS C OMMER C I A L EC O SYSTEM 
 R E PL ICAT OR S & C O NNE C T O R S
24. Cons EXP L AIN & Q UE RY PL A NNE R KIN D A M E H ORAC LE RELATI ON S H IP I S P RO B LE M ATI C F OR I TS C OM M UN I T Y
25. Common Mistakes SI M P L E T UR N S YOU L A Z Y N OT US I N G I N N ODB WE AK/ M I S S I NG VA L IDATI ON S
26. Best Use Case When you need a clustered RDBMS
 
 * that works off the shelf and the option of commercial support
28. A Little Context DEE P ACA D EMIC R O OTS
 B E RK EL E Y’S PO STGR E S( 9 5 ) * Trivia Q: Why An Elephant? O B J EC T D ATA BA SE
 C OM M UNI T Y OWNED
29. Pro: Versatile MVCC F TS FDW S E XT E N S I O N S L A N G U AG E S FL E XIB L E DATA
30. Pro: Versatile LI STE N/ N OT I F Y FL EXI BL E I ND E X E S PE R TR A N SAC T I O N 
 SYN C H R O N O U S RE P L I C AT I O N
31. B - T R EE S 
 DEF AULT. N UME RIC, TEXT,NULL H A S H 
 EQUA LI T Y. D ON ’T USE GIN 
 A RRAY VA LUE S & F TS GI ST 
 GE OM E TRI C DATA & F TS They Can Be Created Concurrently! Many Indexes
32. PA R T IA L I NDEX E S 
 IND EX + WHERE C LAUSE EX P RE S S I O N I NDE X E S 
 MAT C H ON FUN C TI ON/ MOD IF ICATI ON U N IQU E I ND EX E S 
 PR E VE N TS DUPE S SOR TE D I NDEX E S 
 A LT ER B-TREE FROM ASC TO DE SC Many Indexes
33. Cons C LU STE R I N G S TORY I S N OT Q UI T E T HE RE YE T MO RE ST RI C T IN TE RM S OF T Y P E S. N O MORE SHE N A NI G A N S!
34. Common Mistakes UN N EC E S S A RY INDE X E S (MU LTI -C O LUMN) OVE RLO O K EX T E N S I ON S GIV IN G UP O N IN S TA L L
35. When you need an amazingly versatile & powerful RDBMS
 
 * and replica promotion is enough Best Use Case
36. Developing & Running * DB S IN TH E C LO UD
37. Know Your Replication DB RE P LIC ATI ON I S A MUST H AVE USE D IFFE R E N T F A ILURE D O MAIN S U N DER STA ND DR /H A O P T I O N S P RAC T IC E REPLICA PR OMOTI ON S
38. BAC KU P S≠D I S AS TER REC OVERY HAV E A P LA N & P R AC T IC E I T ! Expect Failures DB Master
 us-east-1a Replica
 us-east-1b App App X
39. Let’s Wrap Up (+ R E S O U R C E S & Q & A )
40. RDBMSs CO M PLIC AT E D B Y D ESIG N TO M A KE YOUR L IF E SIMP LE R LEV ER A G E T H E M APP R OP R I AT E LY
41. Questions? TH A NK YO U ! GI T H UB .C O M/ RA ND O M MO O D / GRE AT W I DE O P E N 2 0 1 4