Next up is Using Amazon Web Services for MySQL at Scale. I missed the first bit, on RDS vs EC2, because I tried to get into Choose Your Weapon: A Survey For Different Visualizations Of Performance Data but it was packed.
AWS Scaling Options
Aside: use boto
vertical scaling – tune, add hw.
table level partitioning – smaller indexes, etc. and can drop partitions instead of deleting
functional partitioning (move apps out)
need more reads? add replicas, cache tier, tune ORM
replication lag? see above, plus multiple schemas for parallel rep (5.6/tungsten). take some stuff out of db (timestamp updates, queues, nintrans reads), pre-warm caches, relax durability
writes? above plus sharding
sharding by row range requires frequent rebalancing
hash/modulus based- better distro but harder to rebalance; prebuilt shards
lookup table based
Availability
In EC2 you have regions and AZs. AZs are supposed to be “separate” but have some history of going down with each other.
A given region is about 99.2% up historically.
RDS has multi-AZ replica failover
Pure EC2 options:
- master/replicas – async replication. but, data drift, fragile (need rapid rebuild). MySQL MHA for failover. haproxy (see palomino blog)
- tungsten – replaced replication and cluster manager. good stuff.
- galera – galera/xtradb/mariadb synchronous replication
Performance
io/storage: provisioned IOPS. Also, SSD for ephemeral power replicas
rds has better net perf, the block replication affects speed
instance types – gp, cpu op, memory op, storage op. Tend to use memory op, EBS op. cluster and dedicated also available.
EC2 storage – ephemeral, epehemeral SSD (superfast!), EBS slightly slower, EBS PIOPS faster/consistent/expensive/lower fail
Mitigating Failures
Local failures should not be a problem. AZs, run books, game days, monitoring.
Regional failures – if you have good replication and fast DNS flipping…
You may do master/master but active/active is a myth.
Backups – snap frequently, put some to S3/glacier for long term. Maybe copy them out of Amazon time to time to make your auditors happy.
Cost
Remember, you spend money every minute. There’s some tools out there to help with this (and Netflix released Ice today to this end).
the agile admin 