Become a MySQL Guru

Presented in SoCal Linux Expo #scalex16 in 2018

Transcript

1. @dbsmasher How to become a MySQL Guru AKA Growing pains and how to learn from them
2. @dbsmasher Who am I? • Silvia Botros/dbsmasher • Principal DBA @ SendGrid • > 6 years • Accidentally a DBA • Initially a software engineer
3. @dbsmasher SendGrid • 63,000+ paying customers in 100+ countries • > 40 Billion emails processed per month
4. @dbsmasher Who is this talk for • You work at a rapidly growing company • The ops team is comprised of a single digit number of people • There is no dedicated DBA on staff….yet
5. @dbsmasher
6. @dbsmasher What are we gonna talk about? • Basics • Architecture best practices • When/Why do you need a DBRE
7. @dbsmasher Schema design tips • Primary keys are your friend • If possible, avoid an auto increment key • Always make your auto increment keys unsigned
8. @dbsmasher Schema design tips • Set sane default values. Null is *the worst* • Timestamps are your friend • Do not sweat the indexes • Iterate later using logs • Know the MySQL types. AKA: The 6-way boolean trap • Column names matter
9. @dbsmasher
10. @dbsmasher Metrics • Metrics you should always track ◦ Threads running/Threads connected
11. @dbsmasher Metrics • Disk IOPS (yes, even SSDs)
12. @dbsmasher A big SSD array in tears
13. @dbsmasher Metrics • Disk space growth rate/disk space alerting threshold • Instate data removal/age out where you can • Your business critical database is not a data warehouse
14. @dbsmasher Data rotation management • Date based partitions • pdb-parted (Credit: Palomino db team) • Using sensu for DBA tasks
15. @dbsmasher Architecture • Beware of ORMs • ProxySQL • Caching • Configuration changes with 0 downtime • Load shedding • Connection mirroring
16. @dbsmasher Architecture • Writes can fail. Design with that in mind • Degraded mode, read only mode…are all things • Shard your abstraction layer just as you do your clusters • Lag will happen. Design with that in mind • …no that doesn’t mean send all your read to the primary
17. @dbsmasher Shard and shard again If your product succeeds, you will have more than one DB cluster
18. @dbsmasher Shortcuts you can take • Agonizing over every column type • Tuning every config possible • Indexing all the things • Audit logging
19. @dbsmasher Shortcuts you should *not* take • The database timezone • Not tracking your auto increment key space • Backup testing
20. @dbsmasher Shortcuts you should *not* take • Not tuning certain configs • Turn off the query cache….really • Set your buffer pool size. Beware of connection overhead • Set your mysql process file limits • Set your max_connection
21. @dbsmasher
22. @dbsmasher “We need a DBA” • There are good reasons and bad reasons
23. @dbsmasher First, the bad reasons • To run schema changes • Get familiar with pt-online-schema-change, gh-ost and shift • Be ‘on call’ for databases • ‘No one else has time to take care of this’
24. @dbsmasher Good reasons • Help set standards/best practices on how to use datastore for your engineers • Be an escalation point for your engineers when needed • Your engineers are new to scaling datastores at the growth rate you are seeing • You are solving really interesting, new, problems
25. @dbsmasher Who to look for • An enabler • A teacher • NOT a gardener for your walled garden of artisanal databases
26. @dbsmasher Reading material • The DBRE book - Charity Majors and Laine Campbell • High Performance MySQL - Baron Schwartz • “The accidental DBA” blog post by Charity Majors
27. @dbsmasher You got this