The course is very clear, accessible and ready for the beginner, advanced or the future sqlite user. Get ready for a serious mix of in depth technical knowledge and an approachable way of learning from an experienced teacher.Martijn De Boer
Build with and integrate SQLite into your production applications.
I've made my test data available for you to use and follow along.
Alright. Enough blabbering away. Let's look at some actual, performance benchmarks. But before we do, my standard benchmark disclaimer, all benchmarks are a lie, but some of them are useful. I hope that this 1 is useful.
But no benchmark really, simulates production as much as being in production does, but we can't really do that here. So what we're gonna do is we're gonna run the exact same workload against 2 different databases, 1 in rollback mode and 1 in wall mode, and we're gonna see the relative difference. That's what we're after, the relative difference. Not necessarily the absolute performance. Although the absolute performance is directionally correct, we're looking at the relative difference.
Let me show you some of this code and then we'll run the benchmarks. A lot of this doesn't matter. This is just a Laravel command that I have written. It's called bench, and we can pass through a bunch of processes and a bunch of queries. So we're, by default, 25 processes, 15, 000 queries each, which 25 times 15, 000 is a lot of 1, 000.
And we're gonna use this flag to set the different databases. We've got rollback.sequelite and wall.sequelite. Then coming down here, it does a bunch of stuff. It spawns all the new processes. But what I really want to show you is what we're actually doing down here.
5 percent of the time, we're gonna write to a key value store table. 95 percent of the time, we're just going to read. The key is a random string and this random number generator has been seeded and so the random numbers are or the random strings will be the same across both tests. The lottery has been seeded as well and so we'll get the same number of reads and writes per test. The value is just 64 character string.
And here's what we're gonna do. Insert into kv, the values k and v, On conflict, we're just gonna update the value. So this is just basically an upsert which we'll cover later. It's very cool that SQLite has this by the way. And then on if it is not a write, if it's a read, we're just gonna select star from kv where k where key equals key.
That's it. And then we're gonna dump out some timings at the end. Okay. Hopefully, you're satisfied with my methodology there. Let's run the bench command on db equals rollback.
And this is gonna be so slow that in fact we're gonna run, 2, 000 queries per process and we have 25 processes. So let's run this. It's gonna spin up 25 processes and there we go. What's interesting here is the relative disparity. Some get out way ahead and some stay super far behind.
Alright, so here we go. We've got the rollback database, ran 25 processes, ran 2, 000 queries each. It completed in 8 and a half seconds, with 56 100 reads per second and 291 writes per second. Let us open let's do split vertically and let us open a new 1, and we will run this again. But instead of rollback mode, we're gonna do wall mode and we'll still do queries at 2, 000 even though this is going to blow your mind how fast this goes.
Alright. So here we go. It is going to Alright. It's about to start. There we go.
It's done. That's it. Seriously. That is the exact same benchmark run on a wall database. And we're talking 70, 000 reads per second compared to 50 600 and 3.6000 writes compared to 291.
So we're in order of magnitude difference. And here you can see p 99 is 0.5 milliseconds versus 20 milliseconds. So if we were to run this again just for giggles, let's run this again. Yeah. 2000.
We can run them both because they're they're running on different databases so it shouldn't be a problem. Boom. Done. So 83, 000 reads per second, 84 and 43 100 writes per second. 60 6 103144.
So you can see what a massive, massive difference write ahead log makes compared to the traditional rollback journal and this is why you would want to set right ahead log on your database. The way that you do that, by the way, the way that you do that is, if we were to clear this out and we were to say pragma, we can probably just arrow up. So journal mode will tell you what mode you're in. So we're already in journal mode here but you can just set it to wall and it will set to wall. Now it is a good idea to set the journal mode before you put anything in the database.
That is the easiest way to go about it. And do remember that this is a persistent setting so you can just set it once and then everybody else that connects to the database will be using wall mode and you don't have to re set it like you do with some of the other pragmas, maybe every other pragma, I don't really know. But I do know that journal mode is persistent and you want to be using the write ahead log.