Notices by Kris (Backup Account) (isotopp@chaos.social), page 3

So they sat out to move from Postgres to MySQL and completed that just the yesr before I was asked to look into performance and scaling.

They were running on 32-bit kernels on 64-bit machines, using MySQL 4 with MyISAM, using 1 or 2 HDD per machine.

There were a few easy wins – move to 5.0, move to 64-bit kernel, move to disk arrays, use thin wide stripes.

Use memory tables for GeoIP and other busy, static tables.

Then build a replication architecture and a plan for downtime-free upgrades, and a plan for smooth high availability primary pairs using shared disks (using DRBD with great success).

Get in some nice MSA arrays from HP, spread load over spindles.

Get more memory, keep working set in memory, get rid of disk reads.

Learn that MySQL has a global InnoDB Buffer Pool lock, engage with my MySQL colleagues and ask for a version of MySQL with per-page locks.

Great success, and with 5.0.36 it is GA.

Look at the data.

Find tables with a very high change rate, and talking to the Perl devs, learn that these tables are part of a subsystem called "availability".

The tables store hotel room availability, and each time a hotel puts up a room for sale, or a room is sold, they change.

We take out the availability tables, change the data format to make it all smaller, and numeric and put things into a special database hierarchy, "av".

Great success: We isolate a lot of change-rate from the main database, and we also shrink the data size in av through optimization.

We can keep all the working set of av in memory all of the time, only writes reach the disk.

By adding av replicas, we can scale out reads at will, unlimited.

From a management PoV:

We create an agile DBA org.

We enable developers to break the databases, faster, and then help them to collect the pieces. Our task is to enable development.

We make changes to the database, including upgrades, part of operations – we have process for that, not projects.

We do this by writing code for operations, we do database devops.

We develop strategies to test new versions SAFELY and IN PRODUCTION, giving FEEDBACK to the vendor.

Not just av, we also isolate tables with a time dimension in the primary key or table name into a second chain, dw, "data warehouse" (it's not, but close enough).

These tables have archive-nature, they are only ever appended to, or they contain aggregates of time intervals.

They are also less critical for the "take customers money" codepath.

Soon other sets of tables are identified and also split off.

Because if something works, you do it more.

Somewhere along this timeline we celebrate 86400 bookings a day, an average of 1 booking per second over the day.

A booking is typically a small number of roomnights, and the cost of a roomnight varies a lot from country to country, between 40 Euro in India to 250 Euro in UAE.

Today, during peak hours, it's probably 100s of bookings per second.

Working at Booking in 2013 meant working in an environment that grew 85% YoY.

We had event processing pipelines with sliding window aggregators, called "monitors", because the aggregates were displayed on monitors in all rooms.

This is what you see before you break the site with your next rollout.

Back then it was Apache with mod_perl in the Apache workers, later changed to a more fcgi-like architecture, and then moving to nginx.

Back then a set of 4 front-end apache servers were talking to a fixed set of database replicas for reads.

Some backend databases were too lightly loaded so they were shared between cells.

4 frontends spoke to 1 bp, one app, 1/2 av and 1/4 res instance.

And because you will fuck this up, too, the goal is to build for survivability.

I wasn't fired.

But we found errors in project management, found two broken cluster failover scripts, an abandoned migration, and an error in MySQL (that was already fixed in the most current version, which we could not migrate to, because of the cluster failover scripts, because of wrong project managements).

Five Whys.

They work better than five Nein!s

In the end, many years later, a solution would be established with DIRECT connections from the clients to a read-pool of replicas,

using Zookeeper as a place where databases registered themselves as ready-to-serve,

and clients subscribed to read-pools in Zk,

materialized them as files in /etc/bookings/db on their harddisk

and lb'ed using a random file from these endpoint lists.

This would show up properly in SHOW PROCESSLIST, would survive Zk downtime (the lists freeze) and would be …

very fast: subscribing to changes to a Zk directory gives you a Pubsub notification on the client on change, and then you wait a small random interval and slurp the entire endpoint list and materialize them in your local cache as a file.

This also turned out to be very robust, if you know a thing about Unix and code it right.

We have 2, then 3 data centers, and we are running a few hundred instances of MySQL in a about a dozen of replication chains in 2011.

It begins to hurt, quite a bit.

EDIT: There is also the move to InnoDB, which was met with a lot of scepticism:

- Uses more disk space
- Couldn't easily detach and attache tablespaces
- Broke merge tables, which we used a LOT in dw.

but

20.000 writes/s instead of 7000 writes/s.

Parts of database provisioning are already automated.

There is a git that is checked out into every database, collecting all the scripting and tooling that DBAs use.

A lot of things are half-automated.

The DBA team begins to also move to Python, and starts to work on B.admin, automated database operations.

2013 things exploded, for a numver of reasons.

For example, MySQL can serve a "memcached"-like query "SELECT * FROM t WHERE id = 17" in 125µs if the data is in memory.

It scales better than memcached, though, can automatically prewarm on start, and unliked memcached runs well on machines with many cores and much memory.

The cost of memcached and MySQL on bare metal are the same.

We drop memcached, gain a LOT of replicas.

Automation is really, really necessary.

And we have that really botched change from 5.1 to 5.5 that drags us back.

And we have trouble finding people, because what we do with MySQL is at a level where it is not easy finding people that understand our way of thinking.

The teams objective was "to be able to move 95% of all servers to a new version of MySQL within 20 work days/1 calendar month, no matter the number of servers."

We did not meet that goal at all at that time, and it took a long time to get there.

Other goals were "downtime free primary switches" (with GTID), which enables and also gets you "downtime free ugrades"

We move away from DRBD and to filers, but only until GTID is invented.

Replication between data centers becomes a thing, which means replication hierarchies are generally at least 3 deep, introducing intermediate primaries.

All replication delay measurement breaks and we move to heartbeat tables for end-to-end replication delay metering.

The Perl libraries pick up heartbeat information and moderate bulk writes by measured repliction delay automatically.

Availability data now changes so fast and is so large, that we have to implement "CQRS" (which is java architect speak for materialized views)

The AV Queue workers overwhelm MySQL 5.5 with their writes, we need larger redo logs (limited to 4 GB) and the first SSD machines.

In May 2012, we push fastav (the read-view of av) to MySQL 5.6-alpha in production, on SSD hardware. MySQL implements a number of changes for us, based on our experience.

Boom! That works, we do not die in summer 2012.

The Nemo (network automation) and ServerDB (server automation) teams have had very good experience with Python, so we move our shared git repo with scripts to Python, as well.