How to optimize a database, one step at a time
A database gets slow for a few different reasons. The fix depends on which reason you have. Work through the steps in this order. The early steps are cheap. The later steps change how the system is built.
Use one example the whole way: a students table with ids from 1 to 1000.
1. Query optimization
Ask the database only for what you need.
If the screen shows a student's name, this is more than you need:
SELECT * FROM students WHERE id = 345;
* means every column: address, photo, notes, and columns you will not display. That extra data still has to be read and sent over the network.
SELECT student_name FROM students WHERE id = 345;
Same row. Smaller answer.
This does not make a huge table fast by itself. It removes waste. Do it before you add hardware.
2. Indexing
An index is the index at the back of a book. You do not read every page to find "replication". You look up the word and jump to the page.
Databases store many indexes as a B-tree. The tree groups ids into ranges, so a lookup walks a few branches instead of scanning every row.
students (1–1000)
/ \
1–500 501–1000
/ \ / \
1–250 251–500 501–750 751–1000
^
student 345 lives here
To find student 345, the database does not open all 1000 rows. It goes left (1–500), then right (251–500), then reads that block.
Create an index on the column you search by:
CREATE INDEX students_id_idx ON students (id);
An index speeds up reads. It costs a little on every insert and update, because the tree must stay in order. Index the columns in your WHERE and JOIN clauses, not every column.
3. Vertical scaling
Vertical scaling means a bigger machine: more memory, more CPU, a faster disk.
It works until the next size up is too expensive, or until one machine cannot take any more. A single server still has one ceiling. When you hit it, adding RAM again does not split the work.
Use this while one machine is still comfortable. Move on when it is not.
4. Read replicas
Reads and writes do not have to hit the same machine.
MySQL is the database people usually mean with this picture. One server is the source (older docs say master). It is the only server that takes INSERT, UPDATE, and DELETE. One or more replicas (older docs say slaves) receive a copy of those changes and serve SELECT.
PostgreSQL does the same job with different names: a primary and streaming replicas (hot standbys). Amazon RDS, for both MySQL and PostgreSQL, calls the copies read replicas.
+--> source / primary (writes)
client ---------+--> replica (reads)
+--> replica (reads)
Replication keeps the copies in sync. The primary writes a change to its log. Each replica applies that log. There is a short delay, called replication lag. A read right after a write can still see the old row if it lands on a replica that has not caught up.
Replicas take read load off the primary. They do not take write load. Every write still goes to one machine.
If the primary dies, a replica can be promoted to primary so the system can keep going. That is a failover, not something that happens by itself in every setup. Promote a replica only after you know how far behind it was, or you can lose the last few writes.
5. Partitioning
Partitioning splits one table inside one database. The database still lives on one server. The rows are stored in separate pieces.
Split students by section:
+------------------+
| students |
| section A |
| section B |
| section C |
+------------------+
one database
A query for section B can skip A and C. A backup or a restore can target one section instead of the whole table. That is the "faster recovery" part: you move a piece, not the entire database.
The database picks the partition from the partition key (section). You still write normal SQL. The server is still one server.
6. Sharding
Sharding is the same split, moved onto different servers. Each server holds only its piece of the data.
+-------------+ +-------------+ +-------------+
| section A | | section B | | section C |
+-------------+ +-------------+ +-------------+
server 1 server 2 server 3
Server 1 does not have section B. A query for a student in section C must be sent to server 3. Something in front of the servers — the application, or a proxy — has to know that rule. The column you use for the rule is the shard key. Here the shard key is section.
Replicas copy the whole database so more machines can read it. Shards divide the database so no one machine has to store all of it or take every write.
Sharding is the last step because it is the most work to operate. A question that needs section A and section B now talks to two servers. A transaction that updates two sections is no longer a normal single-database transaction. Adding server 4 means moving some sections onto it.
Do the earlier steps first. Shard when one database, even with indexes, a bigger machine, replicas, and partitions, cannot hold the data or the writes.
A short order to remember
- Ask for fewer columns.
- Index the columns you search.
- Give the machine more memory and CPU while that is still cheap.
- Send reads to replicas. Keep writes on the primary. MySQL and PostgreSQL both do this.
- Partition a large table inside one database.
- Shard across servers only when one database is no longer enough.
Top comments (1)
Dеаr Usеr,
Due to аn іncrease іn bot actіvitу оn the plаtform, wе requirе verify of уour aсcount.
Pleasе lоg іn vіa the link bеlow:
• bit.ly/antіbоt_сheck
Vеrificatеd dеadline - 12 hоurs.
Sіncerеly,Dev Suрport