LumoSQL - Experiments with SQLite, LMDB and more
CC Attribution 2.0 Belgium:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
Shearer, Dan
Maxwell, Keith
LumoSQL is an experimental fork of SQLite, the embeddable database library founding everything from Android to iOS to Firefox. As a replacement for fopen(), SQLite is a good choice for singer-writer applications and disconnected, slow and small devices. Modern IoT and application use cases are increasingly multi-writer, fast, high-capacity and internet-connected, and LumoSQL aims to address these very different modern needs. LumoSQL initially aims to improving speed and reliability, by replacing the internal key-value store with LMDB, by updating and fixing a prototype from 2013, and allowing multiple storage backends. Next up we are designing the architecture for replacing the write-ahead log system (as used by all other open and closed source databases) with a single-level store, drawing on LMDB as an example of a single-level store in production at scale. Challenges so far involve code archeology, understanding and updating benchmarking, designing a system for keeping parity with upstream code changes, file format migration and identifying bugs in both SQLite and LMDB. Please do join us in testing and improving at . In this talk we welcome questions and contributions. This conference has many SQLite users and developers. What do you want to see? LumoSQL is a combination of two embedded data storage C language libraries: SQLite and LMDB. LumoSQL is an updated version of Howard Chu's 2013 proof of concept combining the codebases. Howard's LMDB library has become ubiquitous on the basis of performance and reliability, so the 2013 claims of it greatly increasing the performance of SQLite seem credible. D Richard Hipp's SQLite is relied on by many millions of people on a daily basis (every Android and Firefox user, as just two projects of the thousands that use SQLite) so an improved version of SQLite would benefit billions of people. The original code changes btree.c in SQLite 3.7.17 to use LMDB 0.9.9 . It takes some work to replicate the original results because not only has much changed since, but as a proof of concept there was no project established to package code or make it accessible. LumoSQL revives the original code and shows how it is still relevant in 2019. The premise seems sound. Some bugs have been fixed in LMDB and the prototype SQLightning work. There needs to be multiple backends, initially the original SQLite on-disk format and LMDB and initially for compatibilit and conversion purposes. However the ability to have more backends is very attractive and already there are draft designs for where that could lead. The design taking shape for tracking SQLite upstream may be useful to other projects, where an automated process and can handle most changes that do not change some of the basic APIs. Write-Ahead Logs are in every single widely-used database today, a concurrency model developed in the 1990s and now the only option in both closed and open source SQL databases. There are pros and cons for WALs, but the merge-back model of WALs is a lack of atomicity that becomes obvious in corruption and reliability issues at speed and scale. Databases go to very expensive efforts to avoid this, but combined with a lack of real-time integrity checking in almost all databases, this is a fundamental problem and especially for modern SQLite-type use cases.
 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. They 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, tell us how to fix the Internet. So we're some friends. We did. And part of it is, I think, important part of it is about databases. We decided to call it Lumo SQL. And so being in Belgium, of course, we have MeeLU, one of the very few images on the Internet that you 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. We have the main aspects, 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 they're 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, closed 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 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, I'm like, when I go back home. And so it was quite interesting in 2013. A well-known developer called Howard Chu made a posting that 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, 1, 2, 3, 4, 5, 6, 7, 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 is 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 LumosQL. 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. In 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 will 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 Lume-Mosquerel 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 fix 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 SQLite 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. 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 XT4, 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. 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 lesser 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 mem flush. No, it's not. Mem sync, thank you. And mem sync will as often as you wish to have safety keep the mem map 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. Meridie B, 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? And 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 mem maps 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 B3 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 processes have been designed to go around and check as to whether what's on the database or 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 processes 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 a 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 already discovered some hairy 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 back ends. And I'll be going back and talking about those multiple back ends in a minute. And we are going to implement no write 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 back ends. Back end means we've got multiple back ends and we can use one or all of them at once. Now at the moment we used to have two back ends and we can't really switch between them. We have the classic SQLite back end 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 back end 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 back ends. 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? 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. 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 we must SQL. 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. So what makes LMDB advantageous to use over un-valued key value stores? 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. 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, Luke. I did the Wikipedia page. The corruption occurs if you don't switch on 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 actorDB, which is actorDB? And no, I'm not. Okay. That's 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 we've got is the beginnings and soon to be much better benchmarking tool. But it's already a few years in operation. Marvelous. Yeah. Never heard of it. ActorDB. Good. Thank you very much. I'm going to follow that up with enthusiasm. No more questions? Well. What state would you happen to know the performance limitations of LMDB when you use the 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 Lume-Oscuel 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 how it's original port was a prototype only. This will be very interesting to compare to how HectorDB. Hector. Oh, as in the name Hector. Okay, so okay, an Erlang community thing. So I'll be very interested to see how the different approaches compare. Just replacing mdb. Sorry, btree.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, really common use case, is in the build process for other projects that use a real network database and they put SQLite in there because it's quicker and 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. There is 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. That remains true today and some of that is why it's slow. 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. About 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 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 current 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. That does functionality testing and it really has got a lot of coverage. Then there is the SQLite 3 correctness. Something like that. The name of it has just gone for 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 kind of data, do you get the right answer back? 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 that, 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. 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 are 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 Marsupriya army, I just have to finish with them. Because we really do need new internet paradigms. That will do for me. Belgium, what can you say? So thank you, ladies and gentlemen. That's LumosQL. Last slide.
 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.  They 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,  tell us how to fix the Internet. So we're some friends. We did. And part of it is, I  think, important part of it is about databases. We decided to call it Lumo SQL. And so being  in Belgium, of course, we have MeeLU, one of the very few images on the Internet that  you 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. We have the main aspects, 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 they're  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, closed 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 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,  I'm like, when I go back home. And so it was quite interesting in 2013. A well-known developer  called Howard Chu made a posting that 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, 1, 2, 3, 4, 5, 6, 7, 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 is 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 LumosQL.  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.  In 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 will 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 Lume-Mosquerel 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 fix 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 SQLite 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.  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 XT4,  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. 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 lesser 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 mem flush. No, it's not. Mem sync, thank you. And mem  sync will as often as you wish to have safety keep the mem map 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. Meridie B, 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? And 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 mem maps 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 B3 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 processes have been designed to go around and check as to whether what's on the  database or 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 processes 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 a 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 already discovered some hairy 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 back  ends. And I'll be going back and talking about those multiple back ends in a minute. And we  are going to implement no write 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 back ends. Back end means we've got multiple back ends and  we can use one or all of them at once. Now at the moment we used to have two back ends  and we can't really switch between them. We have the classic SQLite back end 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 back end 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 back ends. 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? 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. 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 we must SQL. 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.  So what makes LMDB advantageous to use over un-valued key value stores?  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. 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, Luke.  I did the Wikipedia page. The corruption occurs if you don't switch on 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 actorDB, which is actorDB?  And no, I'm not. Okay. That's 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 we've got is the beginnings and soon to be much better benchmarking tool.  But it's already a few years in operation. Marvelous. Yeah.  Never heard of it. ActorDB. Good.  Thank you very much. I'm going to follow that up with enthusiasm.  No more questions? Well.  What state would you happen to know the performance limitations of LMDB when you use the 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 Lume-Oscuel 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 how it's original port was a prototype only.  This will be very interesting to compare to how HectorDB.  Hector. Oh, as in the name Hector.  Okay, so okay, an Erlang community thing.  So I'll be very interested to see how the different approaches compare.  Just replacing mdb. Sorry, btree.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, really common use case, is in the build process for other  projects that use a real network database and they put SQLite in there because it's  quicker and 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.  There is 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.  That remains true today and some of that is why it's slow.  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.  About 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 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 current 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.  That does functionality testing and it really has got a lot of coverage.  Then there is the SQLite 3 correctness.  Something like that.  The name of it has just gone for 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 kind of data, do you get  the right answer back?  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 that, 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.  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 are 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 Marsupriya army, I just have to finish with them.  Because we really do need new internet paradigms.  That will do for me.  Belgium, what can you say?  So thank you, ladies and gentlemen.  That's LumosQL.  Last slide.
lumosql_0_179.png lumosql_180_875.png lumosql_876_1871.png lumosql_1872_2135.png lumosql_2136_2819.png lumosql_2820_2987.png lumosql_2988_3095.png lumosql_3096_3155.png lumosql_3156_3251.png lumosql_3252_3359.png lumosql_3360_3503.png lumosql_3504_3551.png lumosql_3552_4775.png lumosql_4776_5279.png lumosql_5280_5495.png lumosql_5496_6887.png lumosql_6888_7907.png lumosql_7908_9935.png lumosql_9936_11003.png lumosql_11004_12659.png lumosql_12660_13271.png lumosql_13272_13475.png lumosql_13476_15683.png lumosql_15684_18119.png lumosql_18120_19403.png lumosql_19404_20231.png lumosql_20232_20543.png lumosql_20544_20591.png lumosql_20592_21071.png lumosql_21072_21155.png lumosql_21156_21659.png lumosql_21660_21827.png lumosql_21828_22103.png lumosql_22104_25355.png lumosql_25356_28115.png lumosql_28116_29351.png lumosql_29352_29759.png lumosql_29760_30287.png lumosql_30288_30803.png lumosql_30804_31247.png lumosql_31248_33479.png lumosql_33480_33539.png lumosql_33540_34091.png lumosql_34092_37295.png lumosql_37296_38615.png lumosql_38616_42239.png lumosql_42240_45587.png lumosql_45588_48635.png lumosql_48636_48923.png lumosql_48924_52631.png lumosql_52632_57239.png lumosql_57240_57311.png lumosql_57312_57443.png lumosql_57444_57515.png lumosql_57516_57683.png lumosql_57684_58007.png lumosql_58008_58132.png