# Napkin Problem 2: Expected Database Query Latency

Nov 2019

This is an edition of the Napkin Math newsletter, a newsletter about using napkin math and first-principle thinking to estimate the performance of systems. You can subscribe through email.

Fellow computer-napkin-mathers, it’s time for napkin problem #2. The last problem’s solution you’ll find at the end! I’ve updated sirupsen/napkin-math with last week’s tips and tricks–consult that repo if you need a refresher. My goal for that repo is to become a great resource for napkin calculations in the domain of computers. My talk from SRECON’s video was published this week, you can see it here.

Problem #2: Your SSD-backed database has a usage-pattern that rewards you with a 80% page-cache hit-rate (i.e. 80% of disk reads are served directly out of memory instead of going to the SSD). The median is 50 distinct disk pages for a query to gather its query results (e.g. InnoDB pages in MySQL). What is the expected average query time from your database?

Answer: First I jotted down the basics and convert them to scientific notation for easy calculation `~1 *10^3 bytes/request (1 KB)`, `9 * 10^4 seconds/day`, and `10^5 requests/second`. Then multiplied these numbers into storage per day: `10^3 bytes/request * 9 * 10^4 seconds/day * 10^5 requests/second = 9 * 10^12 bytes/day = 9 Tb/day`. Then we need to use the monthly cost for disk storage from sirupsen/napkin-math (or your cloud’s pricing calculator) – `\$0.01 GB/month`. So we have `9 Tb/day * \$0.01 GB/month`. We do some unit conversions (you could do this by hand to practise, or on Wolframalpha) and get to `\$3 * 10^3 per month`, or \$3,000 per month. Most of those who replied got somewhere between \$1,000 and \$10,000 – well within an order of magnitude!