Server Write Performance
Today I was experimenting with write performance for a web server. I wanted to know what kind of performance I should expect to be able to get when a simple server actually does something beyond a simple “hello world” GET request. Having been inspired by Casey Muratori’s “performance-aware programming” I try to get an understanding of what my baseline performance characteristics should be given the basic tools I’m using (in this case Node.js, fastify, and sqlite) and then run some simple performance tests to validate that I’m not sacrificing more performance than necessary.
I chose to use Node.js 24.3.0, Fastify, and SQLite mostly because they are familiar to myself and my team, but the applied learning of the experiment should not be considered specific to these tools. I’m also using the npm tool autocannon to do simple measuring. The requests/second are not important for this experiment but just the difference from the base case. (NOTE: I’m doing these tests with my machine in power saver mode off the battery, so the numbers will likely be more spread apart if in performance mode). I mostly look at the Avg column when reviewing results.
Let’s start with simple “ok” GET request to establish the expected throughput of this application using the tools given.
import fastify from 'fastify'
const app = fastify({ logger: false })
app.get('/base', () => 'ok')
await app.listen({ port: 3000, host: '0.0.0.0' })$ autocannon -c 100 http://localhost:3000/base
Running 10s test @ http://localhost:3000/base
100 connections
┌─────────┬──────┬──────┬───────┬───────┬─────────┬─────────┬────────┐
│ Stat │ 2.5% │ 50% │ 97.5% │ 99% │ Avg │ Stdev │ Max │
├─────────┼──────┼──────┼───────┼───────┼─────────┼─────────┼────────┤
│ Latency │ 3 ms │ 3 ms │ 7 ms │ 11 ms │ 3.62 ms │ 1.74 ms │ 118 ms │
└─────────┴──────┴──────┴───────┴───────┴─────────┴─────────┴────────┘
┌───────────┬─────────┬─────────┬─────────┬─────────┬──────────┬──────────┬─────────┐
│ Stat │ 1% │ 2.5% │ 50% │ 97.5% │ Avg │ Stdev │ Min │
├───────────┼─────────┼─────────┼─────────┼─────────┼──────────┼──────────┼─────────┤
│ Req/Sec │ 13,327 │ 13,327 │ 24,511 │ 25,007 │ 23,505.1 │ 3,235.92 │ 13,325 │
├───────────┼─────────┼─────────┼─────────┼─────────┼──────────┼──────────┼─────────┤
│ Bytes/Sec │ 2.21 MB │ 2.21 MB │ 4.07 MB │ 4.15 MB │ 3.9 MB │ 537 kB │ 2.21 MB │
└───────────┴─────────┴─────────┴─────────┴─────────┴──────────┴──────────┴─────────┘
Req/Bytes counts sampled once per second.
# of samples: 11
259k requests in 11.05s, 42.9 MB readAverage is 23.5k req/sec. So that will be our baseline moving forward.
Let’s now add sqlite and create a table for todos and a route to insert new todos.
import fastify from 'fastify'
+import sqlite from 'node:sqlite'
+const database = new sqlite.DatabaseSync('./db.sqlite')
+database.exec(`CREATE TABLE IF NOT EXISTS todos (id INTEGER PRIMARY KEY AUTOINCREMENT, item TEXT, complete INTEGER)`)
const app = fastify({ logger: false })
app.get('/base', () => 'ok')
+app.post('/todos', (request, reply) => {
+ const insert = database.prepare('INSERT INTO todos (item, complete) VALUES (?, ?)')
+ insert.run(request.body.item, 0)
+ reply.status(201).send('ok')
+})
await app.listen({ port: 3000, host: '0.0.0.0' })And the results are pitiful.
$ autocannon -c 100 --method POST -H 'Content-Type=application/json' --body '{"item":"dishes"}' http://localhost:3000/todos
Running 10s test @ http://localhost:3000/todos
100 connections
┌─────────┬────────┬────────┬────────┬─────────┬───────────┬───────────┬─────────┐
│ Stat │ 2.5% │ 50% │ 97.5% │ 99% │ Avg │ Stdev │ Max │
├─────────┼────────┼────────┼────────┼─────────┼───────────┼───────────┼─────────┤
│ Latency │ 103 ms │ 164 ms │ 247 ms │ 2270 ms │ 225.92 ms │ 596.69 ms │ 9745 ms │
└─────────┴────────┴────────┴────────┴─────────┴───────────┴───────────┴─────────┘
┌───────────┬─────────┬─────────┬─────────┬─────────┬─────────┬─────────┬─────────┐
│ Stat │ 1% │ 2.5% │ 50% │ 97.5% │ Avg │ Stdev │ Min │
├───────────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┤
│ Req/Sec │ 360 │ 360 │ 443 │ 472 │ 437.8 │ 29.64 │ 360 │
├───────────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┤
│ Bytes/Sec │ 61.6 kB │ 61.6 kB │ 75.8 kB │ 80.8 kB │ 74.9 kB │ 5.08 kB │ 61.6 kB │
└───────────┴─────────┴─────────┴─────────┴─────────┴─────────┴─────────┴─────────┘
Req/Bytes counts sampled once per second.
# of samples: 10
4k requests in 10.07s, 749 kB readI remembered that for SQLite you need to enable WAL mode if you want to get any decent performance for writes.
$ sqlite3 ./db.sqlite
sqlite> PRAGMA journal_mode=WAL;
walThat gets me:
$ autocannon -c 100 --method POST -H 'Content-Type=application/json' --body '{"item":"dishes"}' http://localhost:3000/todos
Running 10s test @ http://localhost:3000/todos
100 connections
┌─────────┬───────┬───────┬───────┬────────┬──────────┬─────────┬────────┐
│ Stat │ 2.5% │ 50% │ 97.5% │ 99% │ Avg │ Stdev │ Max │
├─────────┼───────┼───────┼───────┼────────┼──────────┼─────────┼────────┤
│ Latency │ 64 ms │ 73 ms │ 97 ms │ 109 ms │ 75.15 ms │ 9.94 ms │ 205 ms │
└─────────┴───────┴───────┴───────┴────────┴──────────┴─────────┴────────┘
┌───────────┬────────┬────────┬────────┬────────┬─────────┬─────────┬────────┐
│ Stat │ 1% │ 2.5% │ 50% │ 97.5% │ Avg │ Stdev │ Min │
├───────────┼────────┼────────┼────────┼────────┼─────────┼─────────┼────────┤
│ Req/Sec │ 1,057 │ 1,057 │ 1,340 │ 1,440 │ 1,318.8 │ 98.98 │ 1,057 │
├───────────┼────────┼────────┼────────┼────────┼─────────┼─────────┼────────┤
│ Bytes/Sec │ 181 kB │ 181 kB │ 229 kB │ 246 kB │ 226 kB │ 16.9 kB │ 181 kB │
└───────────┴────────┴────────┴────────┴────────┴─────────┴─────────┴────────┘
Req/Bytes counts sampled once per second.
# of samples: 10
13k requests in 10.07s, 2.26 MB readSo that’s definitely better. Went from 437 req/sec average to 1318 req/sec average. About 3x improvement, but that’s still terrible compared to our base case. The next thing I wondered is if I batched the writes by putting everything in a queue and processing in chunks.
import fastify from 'fastify'
import sqlite from 'node:sqlite'
const database = new sqlite.DatabaseSync('./db.sqlite')
database.exec(`CREATE TABLE IF NOT EXISTS todos (id INTEGER PRIMARY KEY AUTOINCREMENT, item TEXT, complete INTEGER)`)
const app = fastify({ logger: false })
app.get('/base', () => 'ok')
+
+let queue = []
+function processQueue () {
+ if (queue.length > 0) {
+ let sql = `INSERT INTO todos (item, complete) VALUES ('${queue.shift()}', 0)`
+ for (let i = 0; i < 1000; i++) {
+ if (queue.length === 0) break
+ sql += `, ('${queue.shift()}', 0)`
+ }
+ const insert = database.prepare(sql)
+ insert.run()
+ }
+ setTimeout(processQueue, 1000)
+}
+processQueue()
+
app.post('/todos', (request, reply) => {
- const insert = database.prepare('INSERT INTO todos (item, complete) VALUES (?, ?)')
- insert.run(request.body.item, 0)
+ queue.push(request.body.item)
reply.status(201).send('ok')
})
await app.listen({ port: 3000, host: '0.0.0.0' })$ autocannon -c 100 --method POST -H 'Content-Type=application/json' --body '{"item":"dishes"}' http://localhost:3000/todos
Running 10s test @ http://localhost:3000/todos
100 connections
┌─────────┬──────┬──────┬───────┬───────┬─────────┬──────────┬────────┐
│ Stat │ 2.5% │ 50% │ 97.5% │ 99% │ Avg │ Stdev │ Max │
├─────────┼──────┼──────┼───────┼───────┼─────────┼──────────┼────────┤
│ Latency │ 5 ms │ 5 ms │ 9 ms │ 14 ms │ 6.52 ms │ 13.31 ms │ 612 ms │
└─────────┴──────┴──────┴───────┴───────┴─────────┴──────────┴────────┘
┌───────────┬─────────┬─────────┬─────────┬─────────┬─────────┬──────────┬─────────┐
│ Stat │ 1% │ 2.5% │ 50% │ 97.5% │ Avg │ Stdev │ Min │
├───────────┼─────────┼─────────┼─────────┼─────────┼─────────┼──────────┼─────────┤
│ Req/Sec │ 8,423 │ 8,423 │ 14,927 │ 15,687 │ 14,108 │ 2,059.49 │ 8,417 │
├───────────┼─────────┼─────────┼─────────┼─────────┼─────────┼──────────┼─────────┤
│ Bytes/Sec │ 1.44 MB │ 1.44 MB │ 2.55 MB │ 2.68 MB │ 2.41 MB │ 352 kB │ 1.44 MB │
└───────────┴─────────┴─────────┴─────────┴─────────┴─────────┴──────────┴─────────┘
Req/Bytes counts sampled once per second.
# of samples: 10
141k requests in 10.06s, 24.1 MB readNow we’re getting somewhere! Still just a little better than half our base case, but I feel like we’ve got something we can start tuning. Now I’m wondering what would happen if we removed the need to parse JSON on every request and instead passed the value in the query params.
import fastify from 'fastify'
import sqlite from 'node:sqlite'
const database = new sqlite.DatabaseSync('./db.sqlite')
database.exec(`CREATE TABLE IF NOT EXISTS todos (id INTEGER PRIMARY KEY AUTOINCREMENT, item TEXT, complete INTEGER)`)
const app = fastify({ logger: false })
app.get('/base', () => 'ok')
let queue = []
function processQueue () {
if (queue.length > 0) {
let sql = `INSERT INTO todos (item, complete) VALUES ('${queue.shift()}', 0)`
for (let i = 0; i < 1000; i++) {
if (queue.length === 0) break
sql += `, ('${queue.shift()}', 0)`
}
const insert = database.prepare(sql)
insert.run()
}
setTimeout(processQueue, 1000)
}
processQueue()
app.post('/todos', (request, reply) => {
- queue.push(request.body.item)
+ queue.push(request.query.item)
reply.status(201).send('ok')
})
await app.listen({ port: 3000, host: '0.0.0.0' })$ autocannon -c 100 --method POST http://localhost:3000/todos?item=dishes
Running 10s test @ http://localhost:3000/todos?item=dishes
100 connections
┌─────────┬──────┬──────┬───────┬──────┬─────────┬──────────┬────────┐
│ Stat │ 2.5% │ 50% │ 97.5% │ 99% │ Avg │ Stdev │ Max │
├─────────┼──────┼──────┼───────┼──────┼─────────┼──────────┼────────┤
│ Latency │ 3 ms │ 4 ms │ 7 ms │ 9 ms │ 4.82 ms │ 12.24 ms │ 305 ms │
└─────────┴──────┴──────┴───────┴──────┴─────────┴──────────┴────────┘
┌───────────┬─────────┬─────────┬─────────┬─────────┬───────────┬──────────┬─────────┐
│ Stat │ 1% │ 2.5% │ 50% │ 97.5% │ Avg │ Stdev │ Min │
├───────────┼─────────┼─────────┼─────────┼─────────┼───────────┼──────────┼─────────┤
│ Req/Sec │ 12,399 │ 12,399 │ 19,471 │ 23,343 │ 19,544.41 │ 3,033.68 │ 12,397 │
├───────────┼─────────┼─────────┼─────────┼─────────┼───────────┼──────────┼─────────┤
│ Bytes/Sec │ 2.12 MB │ 2.12 MB │ 3.33 MB │ 3.99 MB │ 3.34 MB │ 519 kB │ 2.12 MB │
└───────────┴─────────┴─────────┴─────────┴─────────┴───────────┴──────────┴─────────┘
Req/Bytes counts sampled once per second.
# of samples: 10
196k requests in 10.06s, 33.4 MB readHo ho ho! That’s not too bad! But can we dial the batch processing to get closer?
function processQueue () {
if (queue.length > 0) {
let sql = `INSERT INTO todos (item, complete) VALUES ('${queue.shift()}', 0)`
- for (let i = 0; i < 1000; i++) {
+ for (let i = 0; i < 5000; i++) {
if (queue.length === 0) break
sql += `, ('${queue.shift()}', 0)`
}
const insert = database.prepare(sql)
insert.run()
}
- setTimeout(processQueue, 1000)
+ setTimeout(processQueue, 50)
}$ autocannon -c 100 --method POST http://localhost:3000/todos?item=dishes
Running 10s test @ http://localhost:3000/todos?item=dishes
100 connections
┌─────────┬──────┬──────┬───────┬──────┬─────────┬─────────┬────────┐
│ Stat │ 2.5% │ 50% │ 97.5% │ 99% │ Avg │ Stdev │ Max │
├─────────┼──────┼──────┼───────┼──────┼─────────┼─────────┼────────┤
│ Latency │ 3 ms │ 4 ms │ 7 ms │ 8 ms │ 4.35 ms │ 4.36 ms │ 286 ms │
└─────────┴──────┴──────┴───────┴──────┴─────────┴─────────┴────────┘
┌───────────┬─────────┬─────────┬─────────┬─────────┬──────────┬──────────┬─────────┐
│ Stat │ 1% │ 2.5% │ 50% │ 97.5% │ Avg │ Stdev │ Min │
├───────────┼─────────┼─────────┼─────────┼─────────┼──────────┼──────────┼─────────┤
│ Req/Sec │ 11,791 │ 11,791 │ 22,063 │ 22,335 │ 21,003.6 │ 3,084.98 │ 11,790 │
├───────────┼─────────┼─────────┼─────────┼─────────┼──────────┼──────────┼─────────┤
│ Bytes/Sec │ 2.02 MB │ 2.02 MB │ 3.77 MB │ 3.82 MB │ 3.59 MB │ 528 kB │ 2.02 MB │
└───────────┴─────────┴─────────┴─────────┴─────────┴──────────┴──────────┴─────────┘
Req/Bytes counts sampled once per second.
# of samples: 10
210k requests in 10.06s, 35.9 MB readAnd man … that is within spittin’ distance of our base case! I’m going to leave it at that and be satisfied for today. There is obviously things that are left out, like doing schema validation/data on the inputs. Better ways to batch the inserts, like considering using a more async approach. But this little experiment showed that if you are not careful you can lose 100x performance real quick, but if you put in just a small bit of effort that you can get most of it back.