quinthar

The Paxos is only used to elect the master (or re-elect if the master fails). Once a master is established, it coordinates all distributed transactions centrally, which is very fast.

Hm... thanks for the head's up. I thought that SQLITE_CONSTRAINT would detect both read and write conflicts, but I'll dig deeper there.

As for how much contention there will be, I'm not sure: that's one reason we haven't taken the plunge yet as we sorta want to determine this first. However, given that we have a huge number of users modifying unshared data (eg, our largest group still makes up a tiny fraction of the userbase), I suspect conflicts will be relatively few.

And to be clear: even a little write concurrency goes a long way. If we can even do two writes simultaneously, that doubles our write performance. But I personally suspect the average batch size of non-conflicting transactions will be in the dozens or even hundreds -- meaning I suspect we will achieve so much replication throughput that something else becomes the bottleneck before replication is saturated.

Only one way to find out however!

Incidentally, if anybody has experience with this I'd love to know:

1) What happens if "mysqlfailover" itself dies? For example, if I have 6 servers split equally between 2 datacenters, and the datacenter running mysqlfailover loses power -- how does the other datacenter get reconfigured?

2) If you run two copies of mysqlfailover (one in each datacenter), how does it solve the "split brain" problem? If you have 6 servers split equally between two datacenters, and a clean network severance down the middle, what prevents both sides from configuring and operating a master? (This is the nightmare scenario.)

3) If mysqlfailover dies, it sounds like it will prevent any other copy from running without manual intervention: "At startup, the console will attempt to register itself with the master. If another console is already registered, and the failover mode is auto or elect, the console will be blocked from running failover. When a console quits, it unregisters itself from the master. If this process is broken, the user may override the registration check by using the --force option."

Overall, this sounds like a great improvement over what existed before (eg, nothing), but still a pretty brittle, manual, and (in the case of split-brain) very dangerous approach. Have you had good experience with it in practice?

Oh, neat! That one seems to replace SQLite's storage engine with a custom one. But yes, also similar. Cool! Again, what is the largest real-world user? We've found a lot of the theories tend to break down when they are subjected to reality -- Bedrock's advantage is it's been hardened in the crucible of live production traffic for years.

Here's a quick overview of how to configure in multiple zones: http://bedrockdb.com/multizone.html

Security isn't provided by Bedrock (or really, any database, at least not well). Rather, with Bedrock security is the responsibility of the application layer, but built into the database via stored procedures.

Thanks for all these links, I have some reading to do! Also, to clarify one point, we're not doing multi-master writes/replication -- just multi-threaded writes/replication.

Incidentally, the latest plan is to use http://sqlite.org/sessionintro.html to do the following:

1) Spin up multiple write threads

2) Every write thread opens its own database handle

3) Every write thread creates a new "session" object before each write command, and then creates a "changeset" afterwards

4) The first write thread to process a write command calls sqlite3changebatch_new() to create a new batch (initially empty)

5) It then calls sqlite3changebatch_add() to add the changeset to that batch, which returns SQLITE_OK to indicate that it does not conflict with anything in the (currently empty) batch

6) The next write thread calls sqlite3changebatch_add() on the existing batch, providing the write command's changeset

7) If sqlite3changebatch_add() returns SQLITE_OK then it creates a patchset from the changeset, and sends it to the slave.

8) Slaves apply and commit patchsets within the same batch in any order

9) On the other hand, if sqlite3changebatch_add() returns SQLITE_CONSTRAINT, then that means the new changeset conflicts with one or more existing changesets in the batch. In this scenario, it increments the batchID, calls sqlite3changebatch_zero() and then sqlite3changebatch_add() again (to initialize the new batch). It then creates and sends a patchset to the slave for committing in any order with changes in the new batch.

Re: SQLite's performance, I find the database is consistently underestimated in every regard -- performance, stability, functionality, etc. And the database isn't half as amazing as the team behind it. Those are some of the most solid engineers -- in a true sense of engineering (of which programming very rarely is) -- that I've met.

I should get better stats. 4.5M users, 6 nodes split between 3 datacenters. Each node has 16 CPUs. 2158341079 total write transactions (over 8 years); not sure how many read (10-100x more?). I'll try to get better stats on peak read/write transactions per second. Not sure the total number of rows of the largest table (that actually takes a long time to count).

Counting "failures" is difficult -- if it breaks, it's because of some bug in our application logic (eg, our stored procedure). Most of our restarts are due to normal maintenance and upgrades. In the history of the company there were a handful of core problems to the logic (generally as we encountered some weird edge case for the first time), but I can't remember the most recent.

Regardless, I'll try to get better data on this. Thanks for asking!

Yes, SQLite has transactions, and Bedrock runs all stored procedures in transactions. However, the Bedrock::DB plugin (which is just a single stored procedure: "query") doesn't expose transactions to the caller. So you couldn't, for example, call "BEGIN TRANSACTION" from the webserver, issue a few queries, and then call "COMMIT". The proper way in Bedrock (and I'd argue, for any modern database) is to package your transactions into stored procedures that execute inside the database itself.

Re: SQLite -- Yes, Bedrock is just the replication layer, SQLite does all the SQL and storage.

Re: Paxos -- It's our own implementation. Split brain is prevented by the master refusing to stand up unless a majority of configured (not just active) slaves approve its standup request. So in a 6 node deployment, 1 master and 5 slaves, 3 of the slaves would need to approve. This means one "half" would have 4 nodes, and the other would have 2 -- and the half with 2 would recognize it doesn't have quorum and thus stay idle. In a scenario where there is a split down the middle, nobody would do anything because nobody has quorum. (This is precisely why you shouldn't deploy in just 2 datacenters -- three is the magic number.)

Re: Sticky sessions -- True multi-machine consistency is impossible to guarantee and has no real practical application. The only consistency that matters is from the perspective of an observer, ensuring that it always sees a world whose time arrow progresses linearly forward. If that is what "linearizable" means, then perhaps. Sorry, my terminology could be off. Thanks for the correction!

Re: SQLite on SSD -- Sorry, I didn't mean to claim Bedrock was somehow faster than SQLite. Indeed, Bedrock adds overhead to SQLite (to do all the networking and such). My main claim is that Bedrock is faster than other databases, in particular when using C++ stored procedures for complex operations.

Thanks for the questions!

Cool, thanks! It looks like that came out in 2012? Neat, I'll take a look!

Haha, Bedrock has been in continuous development and production use (remember: this powers all of Expensify's and has since day one) for about 8 years. However, the public repo is very new.

I'm not actually familiar with Rqlite, but wow, it does look very similar. Thanks for the tip! Does anybody use it at scale?

Our PHP library has the concept of a primary and failover host. We recommend configuring a local Bedrock node as the "primary host", and then setting up a load-balanced pool of all nodes to be used as the "secondary host". This means in normal operation, every webserver talks directly its nearest node (bypassing the load balancer), but "fails over" to the load balancer if the primary is down.

Granted, a simpler configuration is to just have two load balancers -- both of which have all nodes -- and then use each as the primary/secondary. We did this for years and it was fine, but for just a tiny bit more configuration overhead you can bypass the load balancer for the primary and thus shave off those precious milliseconds from each request.

Thanks, strong comments welcome!

Yes, I think it does speak for itself. Thanks!

The Bedrock::Status plugin returns full status on its internal state, as well as the state of the other nodes it sees. We have Icinga monitor this to make sure the serve is not merely up, but in the correct state.

Re: SQLite's performance -- Can you point to anything that shows SQLite isn't the fastest for some class of query? In my experience (though admittedly I haven't formally benchmarked) it's at least as fast, and generally faster (due to the dramatically reduced overhead to query it over a traditional database server).

Re: SQLite reliability. I'm confused how SQLite isn't more reliable than every other contender, merely given that there is so much less to fail. In 8 years, 2158341079 writes, and probably 100x that many reads, I don't think I've ever had a single failure or bug with SQLite itself.

Re: SSDs -- That's a fair point. Bedrock is not the ideal choice if you are using small volumes and spinning disks.

Re: Security -- I think all databases have crap security that nobody really relies upon. Bedrock doesn't try to pretend. Rather, it gives you a very complete plugin framework, such that you can write C++ "stored procedures" that enforce your application's security model. In practice, we run with the Bedrock::DB plugin disabled, such that the only way to access the database is via our stored procedures -- and those do complete authentication and authorization in an application-specific way that no database could in a generalized fashion.

Thanks for asking!

Thanks for asking!

Re: "SQLite can be beat really easily by the client/server systems since each SQLite instance only supports one concurrent writer" -- That's not actually true, sqlite supports concurrent writers via their page-locking branch (and changesets allow for effectively row-level locks). But none of that matters, because single-threaded replication means any multi-threaded write capability is irrelevant. Regardless, when we add multi-threaded replication (on the way), we'll take advantage of SQLite's multi-thread write capabilities.

Re: MySQL's replication. What specifically are you referring to? It's distributed transaction features are actually quite new -- and I think were developed after Bedrock was already in production for years. Furthermore, MySQL doesn't support automatic master failover (or recovery), and struggles over high-latency, low-reliability WAN connections. Anyway, if I'm wrong on any of that, I'm eager to be corrected. Thanks!

Re: Read throughput. Agreed it doesn't have "parallel reads" (eg, it doesn't split a large read up into multiple smaller reads that are joined automatically). However, I don't think MySQL does either. Regardless, Bedrock does quite well for normal read queries, and uses all of the node's CPUs efficiently. (To be clear, I love this idea of adding "super threaded reads", and have some ideas. But at the moment we don't have any application that requires it, so haven't built it.)

Re: Jetpants, I haven't heard of that before. Thanks for the tip! But as for why SQLite over MySQL, I'd ask quite the opposite: why add a networked layer on top of MySQL, thereby ignoring all of MySQL's networking? That just adds all the overhead of MySQL, without any of the benefit. If you're going to build a networking layer atop a database, SQLite seems the clear choice because it is explicitly designed to be a database library -- and not a database server.

Thanks for all these comments!

Out of curiosity, what specifically do you find idiotic about the document you linked to?

Well, with respect to stored procedures, I think C++ is a far more powerful language than MySQL's SQL-based approach -- especially since Bedrock plugins can also encapsulate schema changes.

I think Bedrock's replication is definitely more more powerful than MySQL's as well.

So in general I think it's more powerful than MySQL. I'd love to hear your thoughts about where MySQL has the edge, however. Thanks!

Incidentally, sqlite doesn't provide stored procedures, but Bedrock does (using a C++ plugin capability). Otherwise, SQLite is actually very feature rich; a subset of features are here: http://sqlite.org/fullsql.html

Yes, we've made a lot of good progress on this front, though nothing ready to demo. Multi-threaded replication is really exciting stuff. As it stands, our "selective sync" capability already gives a lot of headroom for write capacity, but multi-threaded replication will raise that ceiling even higher.

Agreed, benchmarks would be helpful to back up these claims. Good suggestion!

I think the argument in favor of it being more reliable is an easier one, however, as Bedrock is specifically designed (and used, at scale, for 8 years) to provide highly available redundancy in the form of WAN-replication and automatic failover.

Regardless, I think you'll agree that "rock-solid" is not a precise technical term, and I hope you'll allow a bit of play on words. Whether or not it's literally as solid as a rock is unclear, but it is definitely very reliable -- and more reliable than most.

Heh, there's a lot of detail to be captured I agree. But in short:

- All nodes connect to all other nodes

- Each node has a priority; a Paxos algorithm is used to identify the highest priority node, which "stands up" to be the master

- All nodes respond to read queries using the local database.

- So long as you always talk to the same node, there are no consistency issues: you are guaranteed that each request will answered with a database at least as fresh as the last.

- However, if you switch databases (eg, a node goes down and you are forced to go to a different one that might not be as fresh), it will wait until it is as fresh as the node you were using

- Write requests are escalated to the master, which processes them with a two-phase commit distributed transaction

- By default, the master waits for a "quorum" of the cluster to approve the transaction before committing

- However, this limits write capacity to 1/median(rtt) of the slaves

- Accordingly, we have a "selective synchronization" feature where you can optionally designate some transactions as requiring "less consistency". For example, you can specify that the master should commit when at least one other node approves.

- For the highest performance, you can designate a transaction as "asynchronous" and the master will commit immediately

- For example, when someone is reimbursed, we absolutely want to get quorum approval of the transaction: we can't risk losing that if the master crashes. But if someone just adds a report comment, we can risk losing it upon master crash.

- When the master does go down (either gracefully or not), the next highest priority node steps up seamlessly

- Any command escalated to the master but not processed will be re-escalated to the new master

- When a higher priority node comes up, it synchronizes to get up to date with everything it missed, and then becomes master

Anyway, lots of details here and I agree they haven't been well captured in the site yet. Coming soon!

Consistency is only an issue if you switch database servers mid session. Just "stick" to a node by issuing all subsequent requests to the same server and there are no consistency issues.

Discuss in real-time here: https://gitter.im/Expensify-Bedrock/Lobby

There's a bit of that here: http://bedrockdb.com/multizone.html

But yes, lots more details to add over time. Thanks for asking!

Thanks for all that Latin! Yes, it's been open sourced for under 24 hours. Those are all good suggestions.

Heh, yep, we definitely had that sent to us. The point of Bedrock is to resolve those points and make SQLite usable in the enterprise.

As for the CAP theorem, we compromise on "C". Each slave responds based on its current state, which might be a different state than other slaves or the master. However, every response returns the "commitCount" of the database at the time the response was generated. Then when you submit a new request, you can specify that commitCount and the slave will wait until it is at least as recent as that.

This way you can safely act upon the response of one request, and then make a second request to another.

Actually, 3 of us will be fathers in the next 60 days.

Heh, as mentioned, the trip is optional. And to clarify, we do cover airfare and some lodging -- but people have wildly different preferences when it comes to food and lodging overall (eg, some people prefer to splurge on one and skimp on the other) so we just try to pay well and allow people to make their own decisions as to how to spend it. More information is here:

http://blog.expensify.com/2013/10/25/ceo-friday-how-to-take-...