Rootconf 2017

In the objective, I described a ticket sales website that does “normal” events like an M2M concert, but occasionally also sells tickets to the Harry Potter theatre show. This is a perfect capacity planning example because you don’t want to be buying servers that aren’t doing anything for much of the time. This is also why the cloud is so popular today. While the focus of this talk is not to help you plan for the application server loads and caches, the data layer is definitely a hard one to tackle.

Selling tickets require you to never sell more tickets than you actually have. You want to load balance your queries. You want to shard your data stores. You may want to split reads and writes. You need to determine where the system bottlenecks, so for that you need a baseline and know what regular traffic patterns are.

Beyond that, we will talk about storage capacity planning for OLTP and data warehousing uses.

The general idea here is that from metrics collection, you will be able to plan your requirements. Couple this with the elastic nature of clouds, and you should never have an “error establishing database connection”.

Tools covered in this talk include but are not limited to: Box Anemometer, innotop, the slow query log, Percona Toolkit (pt-query-digest), vmstat, Facebook’s Prophet, Percona Monitoring & Management (PMM).

Outline

Databases (referred here as data stores, since some modern ones prefer to be referred to as stores) require capacity planning (and to those coming from traditional RDBMS solutions, this can be thought of as a sizing guide). Capacity planning prevents resource exhaustion. Capacity planning can be hard. This talk has a heavier leaning on MySQL, but the concepts and addendum will help with any other data store.

The simple example: you’re selling tickets to the Harry Potter theatre show and the last time this happened, people waited for over twelve hours in a virtual queue and then purchased their tickets. The queue helped with capacity planning, but now you’re selling tickets for more shows. What do you do? Learn more about this here.

Requirements

Speaker bio

Colin Charles is the Chief Evangelist at Percona. He was previously on the founding team of MariaDB Server in 2009, and had worked at MySQL since 2005, and been a MySQL user since 2000. Before joining MySQL, he worked actively on the Fedora and OpenOffice.org projects. He’s well known within open source communities in APAC, and has spoken at many conferences.