5 ms·
Marmot – A distributed SQLite server with MySQL wire compatible interface
- geenat 10mo agoOh man, tons of updates including DDL replication! V2 looks very impressive. Now I'm curious how sharding/routing is handled- which seems like the final piece of the puzzle for scaling writes.
- maxpert 10mo agoRight now I've started off with full replication of every database in cluster. On my roadmap I have: - Ability to launch a replica on selected databases from main cluster. - Ability for replica to only download and replicate changes of select databases (right now all or nothing). - Ability for replica to proxy DML & DDL into write cluster transparently. - Custom set of commands for replicas to download and attach/detach databases on the fly. This will instantly solve 80% of the problems for most of consumption today. I will probably go after on demand page stream and other stuff once core features are done. Not to mention this solves majority use-cases of lambdas. One can have a persistent main cluster, and then lambda's coming up or going down on demand transparently.
- maxpert 10mo agoAuthor here! Every time I post my own stuff here it seems to sink, so hopefully this actually reaches some of you. Marmot started as a sidecar project using triggers and polling to replicate changes over NATS. It worked, but I hit a wall pretty fast. Most people really want full ACID compliance and DDL replication across the cluster. I realized the only clean way to do that was to expose SQLite over a standard protocol. While projects like rqlite use REST and others go the page-capture route, I decided to implement the MySQL protocol instead. It just makes the most sense for compatibility. I’ve reached a point where it works with WordPress, which theoretically covers a huge chunk of the web. There are scripts in the repo to deploy a WP cluster running on top of Marmot. Any DB change replicates across the whole cluster, so you can finally scale WordPress out properly. On the performance side, I’m seeing about 6K-7K inserts per second on my local machine with a 3-node quorum. It supports unix-sockets, and you can even have your processes read the SQLite DB file directly while routing writes through the MySQL interface. This gives you a lot of flexibility for read-heavy apps. I know the "AI slop" label gets thrown around a lot lately, but I’ve been running this in production consistently. It’s taken a massive amount of manual hours to get the behavior exactly where it needs to be.
- hardwaresofton 10mo agoJust want to note that every time I see it I’m impressed with the project, great job so far. The fact that you’ve been running this with WP is also a really huge use case/demonstration of trust in your different software — IMO this should be on the README prominently. These days I personally just ignore projects that insist on MySQL — Postgres has won in my mind and is the better choice. The only way I’d run something like a WP hosting service is with a tool like Marmot. One thing you might find interesting is trying marmot with something like Litestream v2 — marmot of course has its own replication system but I like the idea of having a backup system writing to s3. It seems trivial (as you’ve noted that you can still work directly on the s3 file) but would be a nice blog post/experiment to see “worked out” so to speak.(and probably wouldn't sink to the bottom of hn!)
- maxpert 10mo agoMarmot already supports debezium, so you can do way more than just basic S3 backups. I've noted your suggestions, it's definitely helpful.
- hardwaresofton 10mo agoThanks for the consideration! The reason something like litestream is interesting to me is that it’s (now[0]) an off the shelf way to do PITR backups for SQLite. Sure, I could piece together or write something myself to catch the CDC stream or run another replica, but simply running one more process on one of the boxes and having peace of mind that there’s an S3 backup continuously written is quite nice. I thought debezium was mostly for moving around CDC records, not a backup tool per say. I.e. if I were to write debezium records to object storage with their connectors it’s my job to get a recent dump and replay? [0]: https://fly.io/blog/litestream-v050-is-here/ https://fly.io/blog/litestream-v050-is-here/
- maxpert 10mo agoI see your point. Yes the Debezium path requires more configuration and ochestration, litestream makes it very simple. Would be more than happy to provide this out of box in Marmot if enough users request it (Feel free to open ticket).
- oblio 10mo agoFunny, I was just reading about this: https://github.com/synopse/mORMot2 https://github.com/synopse/mORMot2 FreePascal ORM, so in an adjacent space (ORM, can work with both SQLite and MySQL). I guess DB devs really love marmots? :-))
- _a9 10mo agoI used this a while back while running a waybackmachine style site for a large social media platform. I wanted to keep it simple with sqlite but when it got popular it started to become a problem. Marmot was the only thing that I was able to get to work with the amount of data I was pulling in. It would sync the master db from the main archiver server to all the ha servers so the user would be able to access it immediately no matter what ha server they got. The dev team was nice to talk to when I had some issues in setting it up. It was definitively a weird backend setup I had made but it just worked once set up so I didnt have to touch any of the frontend code.
- naveed125 10mo agoThat looks pretty cool
- iliesaya 10mo agois this an alternative to SymmetricDS to replicate database on multiple node without master/slave ?
- wg0 10mo agoIs there something similar that exists for Postgres?
- bawolff 10mo ago> Marmot uses gossip protocol (no leader) So umm, does that mean it sacrifices consistency?
- foldr 10mo agohttps://github.com/maxpert/marmot?tab=readme-ov-file#limitations https://github.com/maxpert/marmot?tab=readme-ov-file#limitat...
- savolai 10mo agoSo would it make sense to use to use this on a nas to keep wordpress sites’ content backed up?
- maxpert 10mo agoYes you can. I know wordpress has been trying to get SQLite as first class citizen too. But at this point I am tired of waiting for that pipe-dream. But this will let you do it today. Plus Marmot supports debezium so you can have continuous stream to your NAS.
- ChocolateGod 10mo agoI wonder if you could tie this with Litestream to get streamed backups.
- maxpert 10mo agoWe already support debezium so you can do more than backups :D
- PunchyHamster 10mo agoweird choice considering SQLite is more similar to PostgreSQL
- phplovesong 10mo ago[flagged]
- itslennysfault 10mo agoOP mentioned using this to scale WordPress instances in one of the comments. So I assume that had something to do with the choice. It probably wouldn't be TOO hard to support multiple dialects of SQL in the future though.
- PunchyHamster 10mo agoThat disease of a codebase shittifies another project
- ckok 9mo agoThe MySQL protocol is a lot saner and easier to implement than the pgsql one. I can imagine thats a factor? Once connected the sql dialect matters really little
- joelthelion 10mo agoNaïve question : why would you want to use this over, say postgre?
- jockm 10mo agoYou might not, it depends on your use case. However SQLite is very small and lightweight, and amazing for read heavy databases. Using SQLite lets you bypass a lot of setup and configuration; then adding something like marmot lets you add being distributed after the fact.
- phplovesong 10mo ago[flagged]
- nighttime 10mo ago[dead]
- schulm 10mo agoIs this suitable for LocalFirst apps?
- eduction 10mo agoLet’s not forget that keeping wildlife, uh, an amphibious rodent, for uh, domestic, within the city-- that ain't legal either.
- revengeduck 10mo agoThis looks cool! Curious, how does this compare to https://github.com/superfly/corrosion https://github.com/superfly/corrosion?
- maxpert 10mo agoYes explored that path too with CRDTs. - DDL gets really tricky in these cases, that's why you see Corrosion has this weird file based system. - cr-sqlite ain't maintained anymore but I did some benchmarks and if I remember correctly it was as slow as 4x-8x depending upon type of your data & load. Storage bloats by 2x-3x, tombstones accumulate pretty fast as well. I mean each mutation on every column looks something like: table, pk, cid, val, col_version, db_version, site_id, cl, seq Overall I dropped the idea after spending month or two on it.
- wgjordan 10mo agoVery helpful hearing about your own similar experiments with CRDTs. As a followup I'd be interested in more direct comparison between Marmot and Corrosion in terms of features/performance, since they both serve a similar use case and Corrosion seems to have worked through some of the CRDT issues you mentioned.
- maxpert 10mo agoOk it's a very long discussion but I will try to keep it brief here (more than happy to chat on Marmot Discord if you wanna go deeper). Honestly I've not done head to head comparison, but if you are asking for guestimated comparison: - Marmot can give you better easy DDL and better replication guarantees. - You can control the guarantees around transactions. So if you're doing a quorum based transaction, you are guaranteed that quorum has written those set of rows before returning success. This takes care of those conflicting ID based rows getting overwritten that people would usually ignore. And you should be able to do transactions with proper begin and commit statements. - Disk write amplification is way lower than what you would see in CRDT. This should usually mean that on a commodity hardware you should see better write throughput. As I mentioned on my local benchmarks I'm getting close to 6K insert ops. This was with a cluster of three nodes. So you can effectively multiply it by three and that is like 18k operations per second. I did not set up a full cluster to actually benchmark these. That requires investing more money and time. And I would be honestly frugal over here since I am spending all my $$$ on my AI bill. - Reads as you can see, you can read directly from the SQLite database. So you are only bottlenecked by your disk speed. There are no fancy mergers that happen on CRDT level in the middle. It's written once and you're ready to read. - The hardest part in my opinion that I faced was the auto increment IDs. It is a sad reality but turns out 99% of small to mid-size companies, are using the auto increment for IDs. In all CRDTs, in case of conflict, the LWW (based on one ID or another) happens, and I can guarantee you at some point in time without coordination, if nodes are just emitting those regular incrementing IDs, THEY WILL OVERWRITE each other. That was the exact problem in the first version of Marmot. - SQLite is single writer database. cr-sqlite writes these delta CRDT rows in a table as well, under high write load you are putting too much pressure on WAL, how do I know? I did this in Marmot v0.x and even v2 started with that and eventually I decided to write logs in a SQLite database as well. Turns out at a high throughput even writing or dumping those logs that change logs that I'm gonna discard away is a bad idea. I eventually move to PebbleDB, with mimalloc based unmanaged memory allocator for serialization/deserialization (yes even that caused slowdowns due to GC). It doesn't stop here each row in CRDT entry is for one every column of table (changed column) + it has index for faster lookup. So there that will bog it down further on many many rows. For context I have tested Marmot on gigs of data not megs. I do have couple of ideas on how I can really exploit the CRDT stuff, but I don't think I need it right now. I think most of stuff can be taken care of if I can build and MVCC layer on top.