#Scaling up vs scaling out
AddedWhen one database server can't keep up, you have two directions. Scaling up (vertical) means a bigger machine: more RAM so the working set fits in memory, faster disks, more cores. Scaling out (horizontal) means more machines: read replicas that copy the data and share the reads, and eventually sharding, where each server holds only part of the data and takes part of the writes.
The mental model is a busy restaurant kitchen. Scaling up is a bigger kitchen with a faster stove — nothing about how you cook changes, but there's a biggest kitchen money can buy. Scaling out is opening more kitchens: copies of the menu (replicas) can serve more diners, but if every order must also update one shared stock book (writes), the copies don't help; you'd have to split the stock book by region (sharding), and then "how much flour do we have in total?" means phoning every kitchen.
For a relational database, scaling up is the first move and goes further than people expect — a single modern server with hundreds of gigabytes of RAM handles a lot of traffic. The cheapest "scale-up" of all is often making sure the hot data fits in the InnoDB buffer pool, MySQL's in-memory page cache:
#!/bin/bash
# Scan bp_test (1,000,000 rows, 234 MB) three times, reporting time and InnoDB disk reads for each scan.
cd "$(dirname "$0")/docker"
for i in 1 2 3; do
docker compose exec -T sec sh -c 'MYSQL_PWD=study mysql -uroot ptdemo -N 2>/dev/null' <<'SQL'
SELECT VARIABLE_VALUE INTO @r0 FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_pages_read';
SET @t = NOW(6);
SELECT COUNT(*) INTO @n FROM bp_test WHERE payload LIKE 'y%';
SELECT VARIABLE_VALUE INTO @r1 FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_pages_read';
SELECT CONCAT('buffer pool ', @@innodb_buffer_pool_size DIV 1048576, ' MB: scan ', ROUND(TIMESTAMPDIFF(MICROSECOND, @t, NOW(6)) / 1000), ' ms, pages read from disk: ', @r1 - @r0);
SQL
done
# Run it with the default 128 MB buffer pool, then grow the pool online and run it again:
# SET GLOBAL innodb_buffer_pool_size = 1024 * 1024 * 1024;buffer pool 128 MB: scan 157 ms, pages read from disk: 10389
buffer pool 128 MB: scan 135 ms, pages read from disk: 9781
buffer pool 128 MB: scan 139 ms, pages read from disk: 9781
buffer pool 1024 MB: scan 141 ms, pages read from disk: 6738
buffer pool 1024 MB: scan 74 ms, pages read from disk: 0
buffer pool 1024 MB: scan 71 ms, pages read from disk: 0bp_testhas 1,000,000 rows of about 230 bytes: 234 MB, roughly 15,000 InnoDB pages of 16 KB. It runs on a separate MySQL 9.7 server started for this chapter, so changing global settings doesn't disturb anyone else.- With the Docker image's default 128 MB buffer pool, the table doesn't fit. Every scan has to read about 9,800 pages from disk again: the pool keeps only part of the table, and a scan pushes out what the previous scan loaded. Each scan takes about 140 ms.
SET GLOBAL innodb_buffer_pool_sizeresizes the pool online (MySQL 5.7.5+), no restart. The first scan after growing it still reads 6,738 pages, because those pages weren't in memory yet.- From then on the whole table is in RAM: 0 pages read from disk and the scan time halves to about 72 ms. Here the "disk" is a fast laptop SSD with the operating system's file cache behind it; on a cloud volume each page read costs far more, so the gap is usually much bigger.
- That's scaling up in miniature: same query, same schema, more memory. The usual guidance is to size
innodb_buffer_pool_sizeat 50–75% of a dedicated server's RAM (or let MySQL do it withinnodb_dedicated_server = ON).
| Scale up (bigger server) | Read replicas | Sharding | |
|---|---|---|---|
| Helps reads | yes | yes, add more replicas | yes |
| Helps writes | yes, up to the biggest machine | no: every replica applies every write | yes: each shard takes its share |
| Application changes | none | route reads, cope with replication lag | route every query by shard key; no cross-shard joins or transactions |
| Operational cost | low | moderate | high: resharding, backups and schema changes per shard |
| Limit | the largest instance you can buy | write throughput of one server | coordination and complexity |
The order most teams follow, cheapest first: fix the queries and indexes (see Finding slow queries), make sure the working set fits in the buffer pool, cache what's read often and changes rarely, scale the server up, add read replicas for read-heavy traffic, move analytics to a separate system, partition very large tables for easier maintenance, and only then shard. Each step adds less benefit per unit of complexity than the one before.