 Hi. Good evening. Our next speaker is Dan Shearer. He is from Scotland. Now he is going to introduce us to Lumo SQL. It's a fork of popular database library. SQLite. Let's welcome our speaker. Hello. Thank you. So yes, I'm here talking about the Lumo SQL project, a very new project. It's not just me. And yes, I live in Scotland. Keith Maxwell, who is not here tonight. He lives in Ireland. And there's one or two other contributors here in this very room. So thank you to all those contributors. What I'm actually here for is the European Union's Next Generation Internet Initiative, which is very grand. In fact, what it says is reimagining and re-engineering the Internet for the third millennium and beyond. And I can't really help with that. That's far too grand. But what's a lot more interesting and perhaps a lot more practical is that the EU's NGI initiative has gone and given funding to a variety of bodies around Europe. One of them being NLNet based in the Netherlands. And they put it this way that the Internet is broken and we need to fix it. In fact, they say, tell us how to fix the Internet. So we're some friends. We did. And part of it is, I think, an important part of it is about databases. We decided to call it Loom SQL. And so being in Belgium, of course, we have Milo, one of the very few images on the Internet that you are actually allowed to reuse. Mostly because it's in Belgium, but also because it hasn't changed in years, which would be the nature of much of our broken Internet today. Including databases, rows and columns and RDBMSs being behind most things that are happening on the Internet now. Even now with fancy new column stores. And so what do we have in our famously broken Internet? Please interrupt me if you think it's not broken. The main aspects are applications which are very centralized, which don't cope very well with scaling, which are very expensive to scale. We have the networking as well as the apps, which is centralized and therefore broken. Because as soon as we have centralization, then we have security issues. I'm assuming most people in the room would be familiar with that. And then we have devices which are a very large part of the Internet now, the things in the Internet of Things. And they clearly work because everybody loves them. And they clearly don't work because they're insecure and they break and unreliable. And there are various other things. And so the Internet needs fixed. Thank you, NLET. They decided to support LumosQL. And so is there something about databases as used on the Internet that is particularly broken? So what do we have in the most popular databases? RDBMSs. We have the famous names, the Postgres, the MariaDB. Close source ones, Oracle and so on. But probably the most deployed would be SQLite. And I managed not to have my phone on me, but I could right here be waving my phone, which has Android on it and therefore has SQLite on it. Several copies of it, almost certainly. iOS, the same thing. Anyone who uses Mozilla has got at least one copy of SQLite. It's an embedded database that gives you an SQL type interface. Embedded, meaning it doesn't do networking. So what we're looking for in, so I claim in the next generation Internet databases is these things. And SQLite, people love it. Good reason to love it. It's been around for a long time. 91, if I remember rightly, and it does what it says on the tin. It's small compared to many of the other databases. It's moderately reliable except that any database developer who uses SQLite that I've ever met has many stories about corruption. I've done various informal tests. Lots and lots of people report SQLite corruptions, but the good outweighs the bad. And privacy, of course, SQLite doesn't do encryption. More on that later. And so it's not really a good fit for where we are at in the 21st century with very high-performance devices. They have a lot of concurrency on them, where corruption is increasingly a problem. Some electric cars are basically Android on wheels and where privacy is increasingly mandated. I stand right here in the European Union, unlike when I go back home. And so it was quite interesting in 2013. A well-known developer called Howard Chu made a posting. It was noticed around the world where he said he had taken SQLite and put a new key value store underneath. So the key value store is the very simple database compared to SQL where you have a list of items, one, two, three, four, five, six, seven, and then values against that. So number one might be table or chair. And that's all it does. And this is the key value store at the bottom of just about every database. What he said was, I'm the author of LMDB, one of the faster and more reliable key value stores around the place, and I have replaced SQLite's key value store. And so he posted some figures that looked very impressive. He's a very experienced database developer. And so lots of people said, oh, wow. And for a variety of reasons, it stayed at, oh, wow, and where is that thing until about a month and a half ago. And so at that point, the LumosQL project started and said, let's do some code archaeology. So we had some components to that. Mr. D. Richard Hips, well-known again since 1991. He released under the GPL the first versions of SQLite. It's no longer under the GPL, but that is its origins. These are database developers with decades and decades of experience. I don't know very much about databases at all, but still, someone had to do this. Howard, very well-known again, developed LMDB. And this is a key value store that is remarkably small and still behaves quite like a database in terms of its guarantees of consistency and concurrency and multiple threads accessing at once. It's got quite a small footprint, certainly smaller than the key value store under native SQLite even today. So he mashed these together. He called a thing called SQLightening. It was just a prototype. He knew it was a prototype. In fact, even the name was already taken, but that's great. He thought a thing needed done. He had a go and all the well-benefited just a bit later, like two months ago. And so Keith and myself and one or two others who may wish to identify themselves later on in this talk got together and created Lume SQL. Not a good idea to fix something that isn't broken. And so in general, we can imagine that databases are broken on the Internet because the Internet's broken. But there are specific things that SQLite doesn't address and that really matter to a lot of people. Given that, there is perhaps, depending how one counts, two or three billion people using SQLite right now today around the world. That is a matter of some import if it's not really delivering on what these people need. And so you can go to SQLite.org and you can see what their supported use cases are and they make some exceptions. They say very clearly, we don't do high concurrency because you might get corruptions. And it says very clearly that there are various other use cases that will result in corrupted databases, most of which are quite common these days. A mobile phone is a very powerful computer, compared to not very long ago, and yet SQLite, with its crashy corrupt ways, doesn't quite say what I meant it to say, but you understand, is deployed at scale. And so these are the things that are broken. Encryption is an issue, because we're standing here in the European Union, which mandates that personal data shall be encrypted at some point soon. We expect a new regulation to arrive called ePrivacy, and that will require end-to-end encryption, including on the terminal device or mobile phone. SQLite doesn't support encryption in its open source form. You go and pay the people at SQLite.org and they will give you a closed source encryption plug-in. And so there are other ways around that, but still, that's not ideal. SQLite is famous for having a really quite comprehensive test suite, and that is true. It tests the code. It doesn't necessarily test use cases that are relevant to the users, and there's quite a difference there. And so it'll do things like 25,000 inserts and see how long that takes, and that's great. But it won't necessarily do 25,000 inserts from three different writers at once, and so on. And so there's a lot of work to be done to take the SQLite that we have today, used at scale and loved, and make it more relevant to the 21st century. So we've had some fun, and we've done some things, and there is an absolutely really, really cool announcement-type thing to make. We did some code archaeology. It wasn't easy to find what the antecedents of LUMOSQL are. That's the boring bit, but it did take a lot of time and effort. We've written a benchmarking tool so that we have some idea of whether we're actually improving things or not, or how bad they were in the first place. Benchmarking is not as easy as it may seem, as I have been learning working with Keith, who just loves it. And we fixed some bugs. We'll talk about that again in a minute, but there are a couple of blocking bugs that made it impossible to see actually how good the idea of SQL Lightning was in the first place, and we've got some features. This is SQLite. By and large, if it's on this side, then people who are SQLite users here will be used to the SQLite 3 underscore prepare API call. Those are the bits that service prepare, and then this is the SQLite 3 underscore execute or step API calls. And so btree.c implements the btree under which we have a pager, under which we have some operating system specifics. But the pager is the thing that the btree says, I need to store a page, and the pager decides where it should go and whether it should be journal. That is the way that SQLite works at the moment. Very crucially, the pager handles right ahead logs. And so again, those who are familiar with programming in SQLite, those are the two bits we care about. These are the two bits, the one bit, SQLite 3 step, is where you're going to see the differences in what we've done with LumosQL so far. I'm going to come back to some of the details of what's been done. I just want to cover a very important thing. Right ahead logs. So the pager gets to do the concurrency bit and the security bit. Sorry, I should say the integrity bit for SQLite. So the idea is that there is a transaction. The transaction doesn't complete straight away because there's many things going on. And so we write out the interim state to a little file called a right ahead log file. If you're familiar with Postgres, you can see a whole lot of files called WAL files. And in fact, this is a tried and true 1990s database technique. And every single major database used on the Internet today uses right ahead logs. Now there are two features of modern operating systems that mean that's probably not necessary anymore and actually not as good as we can do. And those features are journaling file systems, which if you look at it is like a special case of right ahead logs. So if you're running this on, say, X4, then SQLite does its right ahead logging or Postgres or MariaDB does all its right ahead logging. And then underneath the file system just does the same thing all over again. And that doesn't sound very efficient, does it? And then the other thing is that we have a virtual memory system. It's really very good. A lot of time has been put into modern operating systems, virtual memory systems, especially Linux, but there are others. We do acknowledge the less of people in the universe. And this is where we get the idea of memory mapping an entire database or a level zero store, zero level store. I'm not sure what the technique is. I just forgotten where basically we allow the operating system to worry about all the details of whether a page is on disk or in memory and to keep it as safe as we possibly can. There is a command called MemFlush. No, it's not. MemSync, thank you. And MemSync will, as often as you wish to have safety, keep the MemMap image up to date on disk. So with these two advances in operating systems that really are the core of what makes a robust operating system, we don't really need write ahead logs anymore. But that's a lot of technical debt. Postgres has 2.5 million lines of code, I think. MariaDB, I think, has more. SQLite, bless it, only has 350,000 lines of code. And it's still doing write ahead logs. Wouldn't it be great if we could eliminate write ahead logs? Which we have. The way we've done that is by making sure that LMDB works underneath SQLite correctly, which it now does, as a very recent date. LMDB doesn't use write ahead logs. That's one of its very strong features. The coolest thing is it MemMaps everything. It's as safe as it can possibly be using just one file. It doesn't have to have consistency with some journal. You have to replay after a crash. This is amazing. And so once we fixed a few bugs in Lumo SQL, all of a sudden we got this benefit for free. Because LMDB is sitting at the B-tree level, completely replacing your pager and some of the operating system specific things. And we now have the world's first database API, I have to put it that way, used at scale, which does not have a write ahead log. That's absolutely amazing. I like it. I'm quite enthusiastic about it. So we have some other things with Lumo SQL, where we are, what we've done, what we'd like to do. It's a really baby project. That's the first thing. I mean, we're talking like two months old. NLNet has made this possible. We're starting to get a good idea of where it's going next. We need to talk to NLNet about just where it goes and how fast. There's some very, very important, and I think cool new features to come. But just before we go on to that, are there any comments from some of the expert database users and implementers in the audience, of which I know there's at least four? Okay. Very, very conscious that the amount of database expertise and all the millions of lines of code that I've referred to is huge. There are people who have been, who spent their professional lives making reliable row and column stores that the internet runs on today. And I guess what do you say? On the shoulders of giants we stand. So one of the things, the very important things that we are really required to have in a database these days is reliability and detecting whether what we wrote, consistency, so the acid things, right? Atomicity, consistency, integrity. The middle two are integrity and consistency. What we have with all of the major databases, and including SQLite, is that external processors have been designed to go around and check as to whether what's on the disk looks consistent. Does it look like it hasn't been corrupted? If you look at a running Oracle database, you can see there are these processors that are fantastically going through the database and trying to do its integrity checking completely separate from whatever the applications might be trying to do. That's quite an interesting thought. You've got concurrent access by an integrity checker looking over the shoulder of the thing that's supposed to be writing reliably. So basically what that says is that we don't have any good way now of getting a mainstream column and row database and knowing pretty sure that what I just read from disk now is what was written last week. And that doesn't seem like a big ask for the 21st century, but it does seem to be a problem. And so we have an idea. We know how we're going to implement this idea. We hope it's going to work. It sounds easy enough. Although we've already discovered some hairy use cases, corner cases, and the idea is this. What if each row had a checksum? How about that? So every time we update a row, we keep a little checksum on the far end, and then when we read it in off disk, we had a pretty good idea whether what we read just five minutes ago was what was written last week. I haven't found any major database that does this. I don't know why, but if I'm required to have consistency, it seems like a good way of doing it. Anyway, that's what we're implementing. I'll let you know if it works. So that's quite exciting. So what we have to do is make a first release. We have to have a way of having multiple backends, and I'll be going back and talking about those multiple backends in a minute. And we are going to implement no right-ahead logs and be sure that that is right, because you don't do this lightly, and we're going to implement per row checksumming. That's what's in the very short-term roadmap. The backends. Backend MUXER means we've got multiple backends, and we can use one or all of them at once. Now, at the moment, we used to have two backends, and we can't really switch between them. We have the classic SQLite backend with the B-tree code, which is well-used, and at least if it doesn't work, sometimes we can't know how it doesn't work. We have the LMDB backend, which has really only been passing all of the SQLite tests for a very short time now. So I'm not going to say that's production ready. But already, we have pretty clear designs for other backends. So you can think of other key-value stores. There are reasons why you might want other ones in there. Going higher up the architecture diagram, you can imagine that it's going to be an interesting place to put in some networking facilities. That isn't within the next month or two. The other things I've discussed are within the next month or two. But these are the things we're trying to do. One of the big, big elephants, non-postgres elephants, in the room is how do you track upstream SQLite? And we've got some ideas about that. We haven't got a definitive answer about that. But one thing we want to make absolutely clear is that right now we are not reinventing SQLite. There is a lot of code there. If you look at the left-hand side of that architecture diagram that I showed you earlier on, there is a good deal in getting a statement, parsing a statement, preparing it for the virtual machine, and so on. And we don't in any way want to re-implement any of that. And we want to have all their bug fixes. We believe we can solve this problem by judicious use of good APIs. I'm pretty confident about that. But in a nutshell, that's lean-muscular. That's where we're up to. That's where we're going. And next up, 3.5 billion people's pockets. There we are. Do we have any comments at this point? I mean, there's a lot more. I've been skipping over the highlights. We have a question. I think that was absolutely a planted question. What makes LMDB much better than any other key-value stores? Okay, I'll take that one. So firstly, as we've already talked about, it's a single-level store. So it only has one file. It maps everything in memory. And if everything goes well, as we would hope, then there's a much lower risk of corruption and loss of integrity. It is also actually very efficient. There's quite a few others. There's level DB. And there are other design architectures out there that aren't based on B-trees at all. There's the LSM log-structured merge-back systems. And there are quite a lot of other key-value store libraries. LMDB is extremely widely used. And that is because it's almost a drop-in replacement for Barkley DB. And Barkley DB can't really be used anymore because Oracle changed the license on it. So the key word is sleepy cat if you care about the history there. So LMDB is extremely well-tested. It's the bottom of OpenELDA, which is the project out of which it came. But now you'll find it under all kinds of other projects. Well-known ones include Samba and bits of Mozilla and so on and so forth. LMDB, therefore, is very performant. It is known to be quite reliable. You can get corrupted databases out of LMDB, but I am going to go out on a limb and say not nearly as often as SQLite. And it has a very small footprint, much smaller than many other B-tree code bases, including SQLites. So the design goal for LMDB was to fit into L1 cache on a typical reasonable CPU, and it manages that. That's really cool because if you can keep the cache hot, then your performance overall increases, and that really matters on funky modern architectures. That's a start. Will that do, sir? Thank you, Lou. The Wikipedia page. The corruption occurs if you don't switch on F-sync. The F-sync mode. Yeah, corruptions with LMDB. Corruptions and all kinds of dreadful things happen with LMDB if you're unaware of its sharp edges. So there was another question there. Yes. Are you aware of HectorDB, which is HectorDB? No, I'm not. Okay, that actually did this combination of SQLite and LMDB underneath of it several years back. And they manage that, or they call that a database node, and then on top of that they have an Erlang layer, and that manages a number of these kinds of nodes. So does this still exist? Is it an active project? Yes, it's an active project on GitHub. Marvelous. I can't wait to meet the team. I would have a look. Okay, excellent. What is the beginnings and soon-to-be much better benchmarking tool? Yeah, but it's already for a few years in operation. Marvelous? Yeah, so... Never heard of it? HectorDB. Good. Thank you very much. I'm going to follow that up with enthusiasm. No more questions? Well... Do you happen to know the performance limitations of LMDB when used on SQLite? I don't, and I'm skeptical that anybody actually does. I don't want to take that one any further because I want to go on to some other questions. First of all, without naming any names, is there anyone in the audience who would like to talk about what we know so far about LUMOSQL internals? Because we fix some bugs, and it's quite interesting as to the nature of some of those bugs and what it might say about where we go to look further. For performance enhancements, we can perhaps do that as a prepared talk then, another time. We certainly have a skilled bug fixer in the audience tonight. So, what I'd like to just quickly run over some of the things that we've discovered. So, Howard's original port was a prototype only. This will be very interesting to compare to how Hector DB? Hector. Hector. Oh, as in the name, Hector. Hector, yes. Okay, so... Okay, an Erlang community thing. Okay, so I'll be very interested to see how the different approaches compare. Just replacing MDB, sorry, B3.C is a fairly limited and short-term approach. That's our conclusion so far. It's great. We need to do this. We need to have multiple key-value stores down there. But if you've got a general purpose and highly effective and widely used SQL interface at the top end, it seems a bit limiting just to have a key-value store as the ultimate destination in the back end. And that is something that we're very keen to introduce an API for, so that we can not only switch between key-value stores on disk but also between different network models and other ways of storing and retrieving data. So where we fit in the API level, in the architecture diagrams of this, is active consideration right now. So is there anything further? Do we have SQLite users here? Yeah, so is it doing exactly what you want? Not really. Why not? What's the problem? SQLite is too slow. Now, here's an interesting thing. A common use case for SQLite, a really common use case, is in the build process for other projects that use a real network database. And they put SQLite in there to run a lighter to start up and faster. I also think that SQLite is quite slow, and this is the beginnings of what we can see with the benchmarking we're doing. It has particular hotspots. But it's really interesting that if you're finding SQLite slow, that probably means, depending on your use case, you'd find a real database unbearably slow. So this isn't what we want in this century. I feel your pain, and that is something that I'm trying to look at quite hard, as to how can we make this thing faster from top to bottom. Even though there is only 350,000 lines of code as compared to millions, there is still quite a bit of technical debt in SQLite. So much so they tried to, a few years ago, make a SQLite 4, which didn't work, but they identified quite a lot of historical craft that they would like to do without in a new version of SQLite, and that remains true today, and some of that is why it's slow. So I look forward to finding out whether we can actually make this thing faster, and I don't know the answer. I think it's very likely we can. I certainly think that there is a lot of benefit to be had from talking to the really good experts who've been working on this code base for years and years and years, and they know that there are some bits that haven't changed, and why, and maybe how it could change. So any other good or bad things about SQLite in your current users? So speed, yes. Corruption we've discussed. Is there anything else that could be maybe better? The API, that's the one thing that mostly I don't want to see change. Now, noting I did say encryption, but there is encryption already in the API, so we wouldn't be introducing anything new there. The point is it's at scale billions in its usage worldwide, and that is the API that people are using, so I'd be very reluctant to change that even a bit. The only thing I have thought of is that there's older API interfaces associated with quite a lot of code that we could perhaps, after great care and consultation, drop from the code base. But SQLite 3 underscore API, I would feel very funny changing those in an incompatible fashion. Did you have something in particular you hate? Okay, so, but SQLite has all kinds of APIs, all kinds of wrappers, all kinds of languages. The main point of LumosQL is to be able to seamlessly go wherever SQLite is today, and hopefully with better effect, or if not, at least we'll produce benchmarking and stability results that will be of use to the entire SQLite-using community. Anything else? Did you add any unit tests or benchmarks for concurrent reads and writes yet? At the moment, we're really pleased that we've got the existing ones working, and what we're doing is created a benchmarking tool that compares consecutive runs with each other, which is not something that's SQLite. Now, that's the thing. There are three test environments for SQLite. The oldest one is the TCL, which is very extensive, lots of code there. And that does functionality testing, and it really has got a lot of coverage. Then there is the SQLite correct, SQLite 3 correctness, no, something like that. The name of it has just gone from me at the moment, but it is an SQL correctness testing engine that can run against pretty much any database. The only thing that cares about is if you put in a certain amount of data, a certain kind of data, do you get the right answer back? But the third one is what we are told is very excellent, fast, and even much more comprehensive test system for SQLite, which you can get access to if you go to sqlite.org people and pay them money. So a lot of people do seem to be thinking that that might be a bug. How it's addressed, I don't know, but that is certainly a question that keeps popping up. So there we are. That is the introduction to LumosQL, which is a brand new project, and we keep finding exciting new things every day. We keep finding that benchmarking would appear to be the answer at the moment, and we keep finding people who say, oh, I can imagine contributing, and we're trying to make sure that they do. There we are. I don't have anything more to say except that we are in Belgium, right? And I did say that Nilo hasn't changed for years and years and years. I spent so much time with the Marsupriyami, I just have to finish with them, because we really do need new internet paradigms. That'll do for me. Belgium, what can you say? So, thank you, ladies and gentlemen. That's LumosQL.