Data Antipatterns - NYC Devops 2014

Are you running a Database in the cloud? Worried that you’re doing it wrong?

Engine Yard supports a broad set of databases, with lots of
flexibility for customers to modify and configure their installations. But the freedom to adapt and extend standard functionality comes at the risk of unexpected negative consequences: seemingly benign modifications can seriously affect durability and performance.

During our years of helping customers, I've had the unique opportunity to observe common problems, patterns, and best practices with big (and not so big) data. In this talk I'll highlight the most common pitfalls and tell you how to avoid them (I will most likely rant about other things too!).

http://www.meetup.com/nycdevops/events/181986002/

Transcript

1. ANTI 
 PATTERNS DATA Updated!
2. ines @ Engine Yard.com @Randommood And I’m a happy dog! I N E S S O M B R A I work with Databases
4. I can get a little ragey sometimes Disclaimer #1
5. I’m sorry.
 ! Ask me to slow down. Disclaimer #2
6. ZOMG, the horror!
7. .BACKUPS yes, we are going there
8. “I know you. You know you. And I know you know that I know you” White Goodman

9. Boring Definition #1 Backups Copy and archiving of data Goal is to restore the state of a DB Many types - blah
10. Anti-Pattern #1 Taking too many backups Not free, they requires resources Full backup every hour, really? 
 What about backup retention?
11. Anti-Pattern #2 Taking too few backups Enough to minimize the risk of data loss due to corrupted backup files yes,
12. The untested backup Anti-Pattern #3
13. Doing backups right Logically test backups Errorless restore is not enough. Test logical data too
14. Doing backups right Know your types & tools Take logical and binary backups Continuous archiving & hot backup utilities
15. Doing backups right Practice restores Backups alone do not constitute DR. Have a plan & practice it Server extensions and configuration matter when restoring
16. “I want a ridiculously good looking Database” Derek Zoolander
 (honestly, Ben Stiller rules)
17. Obvious statement #1 Many DB choices
18. Cargo culting your database Anti-Pattern #4 Failure to understand use case, strengths & weaknesses of a new database
19. RDBMS for Session Data Anti-Pattern #5 Often means at least one write per request Any DB issue/task may cause app to hang Tables have a tendency to bloat
20. Modeling, it’s all the same Anti-Pattern #6
21. Data Model Consistency needs Availability needs Scaling needs Operational story & cost Doing it right Know your needs
22. Doing it right Spike it, forealsies Spike it with your data and traffic. 
 Best way to gain operational experience
23. Doing it right Leverage new features Relational databases are getting quite versatile Evaluate clustered MySQL options
24. Are we doing ok?
25. We have a cloud deployment! Happy team on shipping day, lmfao if you don’t celebrate like this
26. Cloud-based databases, they are real Obvious statement #2 Databases can live in the cloud quite well Many IaaS, PaaS, & DBaaS options Easy to get started & may be economical
27. Where did my instance go? Anti-Pattern #7
28. Anti-Pattern #8 Cloud, it’s just like hardware It’s not. Cloud resources are virtualized Capacity planning and monitoring matter. A lot
29. Anti-Pattern #9 Shit doesn’t happen You are not immune to infrastructure failures. 
 Plan for it
30. Anti-Pattern #10 Storage is the same Instance storage is not persisted (unless you use EBS) Data locality matters Don’t run your cloud DBs too hot!
31. Doing cloud right Know your cloud deployments Replication in the cloud is a must-have Put DB master & replicas in different AZs
32. Doing cloud right Learn high availability & disaster recovery Get good at replica promotions (some work involved) Understand and invest in DR/HA. Know your options
33. Doing cloud right Know your system Invest in monitoring Know your data distribution & querying patterns Know baseline behavior
34. And there’s more!
35. Boring Definition #2 Indexes
 (or indices, I prefer indexes) Improves speed of data retrieval Used in random & ordered lookups Imply additional writes & storage
36. Anti-Pattern #12 Too few Indexes Room for query optimization & increased speed Analyze, slow logs & monitoring tools are your friends
37. Anti-Pattern #13 Too many Indexes They are not free. Your DB maintains them. Too many will impact your write throughput.
38. Doing Indexes right Many index types Many types. Learn how your DB does them. You want the right amount.
39. Doing Indexes right Postgres Indexes summarized B-TR EE S 
 D E FAULT. NU MER IC, 
 TE XT, NU LL HA SH 
 E QU A L I T Y . D O N’ T US E G IN 
 A R R AY V A LU E S & F TS G I ST 
 G EO M E T R I C DATA & F TS They Can Be Created Concurrently!
40. Doing Indexes right Postgres Indexes are more baller P A R TIA L IN D E XE S 
 I NDE X + W H E R E C L AUS E EX PR E SS I O N I ND E XE S 
 M AT C H O N FUNC TI ON/
 M OD I F IC ATI ON U NI QU E IN DE XE S 
 PRE V E N T S DU PE S S OR T ED I ND EXE S 
 A LTE R B-TRE E FR OM A S C TO
 DE SC
41. Prioritize them, take them regularly. For the love of sweet baby jesus routinely test them Backups Know a DBs use case, strengths, & weaknesses How well does it fit your needs? Choices tl;dr; Have the right amount. Properly maintain them. DB Indexes Not the same as real hardware. Plan for failures. The Cloud
42. Questions Thank you! I’m @randommood on the twitters .