0xDEADBEEF

Inserting 100 million rows in sqlite on old garbage hardware so fast it's not even funny

How fast is a decade old 200$ thinkpad X220 compared to a 1800$ macbook? About the same. At least as far one benchmark goes.

There was this little article on the internet dealing with question how fast you can push 100M rows in sqlite. It reported that on a newish¹ best selling brand of horribly closed laptop one can stuff about 100M rows to the database in about 33 seconds. Seems quite fast, right? Right? Well, we cannot say anything definitive until we know what's really possible.

So with this in mind, I tried the same. I took my venerable thinkpad with good old Sandy Bridge μarch CPU of 2011 vintage (i5-2520M), 2 real cores (Who needs more? One is for weekdays, the other one for weekends), 4 GB RAM, equally aged SSD (SATA Kingston 120GB SV300) and on than machine I wrote little program in my favourite compiled scripting language D (-O2 -march=native is your friend.) The results are quite something. With all the techniques discussed in the original article it can stuff 100M rows into sqlite3 in about 36 seconds. Compared to 33 seconds on nicer and much more expensive hardware, it's not that bad at all, is it?

Real time 36.7 s, 31.3 seconds in user space and 1.9 seconds in the kernel. Remaining 3.5 seconds were most likely wasted waiting for SSD.

Further profiling using perf record shown that quite a lot of time is spent waiting and spinning on locks. Quite a lot. Like 25%.

perf stat is not showing anything out of ordinary: 1.89 instruction per cycle, 10% backed cycles idle, nothing catastrophic.

So what's the point of all this? Well, perhaps you can do quite a lot on a garbage machine. Or the original Rust programs have some performace gaps compared to usage of naked C sqlite interface. I don't know. Performace measurement is hard and can be dominated by random effects of compiler and hardware interaction. But one thing is for sure: Single threaded performance is more or less flatlining for over a decade. For single threaded programs, exact CPU generation plays pretty small role overall. If your decade old machine can pull off nearly 2 IPC, the newest miracle μarch most likely cannot do that much better.

Yeah. I'll keep my old machine, good enough for me, good enough for cash-poor folk. I live in garbage, live from it, nourished by it and I like to use the most from what I already have.

1. When you are publishing some performance numbers, please specify exact model of your CPU. 2.4 GHz Quad Core i5 means nothing, i5-8279U is the unambiguous model name. Look for something that like looks like that in /proc/cpuinfo.