Recent searches
Search options
Another #TuskPHP thought. I'm thinking I'll try to build it without the assumption of a SQL database. It seems to me one of the scaling issues that the #mastodonadmin community is having is around the fact that the database has to be a single node, and can only be scaled vertically, which works fine but has limits and becomes expensive.
I'm thinking I would like to design it with compatibility with a database option that's designed to run as a cluster to allow for easier horizontal scaling.
@andrewfeeney I've also been curious about using #DynamoDB to back an ActivityPub server. Writes scale massively, and reads cache with DAX/Redis/etc. Should be easy to index for timelines.
Vendor lock-in is an issue though, unless persistence is modular. Then you're stuck with lowest common denominator database functionality.
ActivityPub does seem better suited for a clustered document database. I'm looking forward to following your progress. 
@jeff Thanks for these comments, these are great points!
I'll admit, I've been pigeonholed in SQL land for so long that I'm not really across the tradeoffs of other ways of approaching persistence. I gather there'll be consistency and sharding considerations, but what that looks like in the actual software architecture is all a mystery to me.
Building read / write splitting into the architecture from the start is something I hadn't thought of yet, but makes total sense.
@jeff I agree that ActivityPub seems well suited to a clustered document database. It seems as though it's basically a massive distributed JSON-LD document.
Maybe I'm being naive about the complexity, but I'm wondering if I can decouple the persistence solution well enough at the application layer (using something like the Repository pattern, or some other approach) that it would be possible to implement drivers for any kind of proprietary store that made sense.
@jeff For instance you could have a DynamoDB driver, and an ill-advised flat file disk access driver.
I'm not going to personally implement drivers for every kind of database, but at least building for the possibility of different kinds of persistence from the start makes sense for me.
I think leaning too hard into vendor lock-in doesn't fit fell with the ethos of the Fediverse, which is why I'd at least want to leave the possibility of options, ideally both SQL and NoSQL if possible.
@andrewfeeney Yeah, we do something very similar for volatile cache (Redis, Memcached, formerly APC), full-text search (MySQL, Elasticsearch, Sphinx), and object storage (disk, S3, database).
There's a high-level interface to implement for actions (e.g. get/set, query/index). Anyone can implement support for a new engine.
The main upside is we can pick simple defaults that 'just work' self-hosted w/o extra apps, and scale bigger clients. Downside is losing specialization.
@andrewfeeney Building in read/write splitting from the beginning also saves a lot of headaches and gives you better control than just detecting `SELECT` queries as reads.
For instance, you can elect to always read certain queries from the primary (e.g. read-modify-write w/ strong consistency). That might happen immediately after someone posts and you rebuild their own timeline. Everyone else can wait a few seconds for eventual consistency.
@jeff @andrewfeeney MySQL-proxy can help read / wrote splitting, but never used it.
@ellisgl @andrewfeeney I tried doing it automatically at first but always ran into issues (read-update-write being the main one). I wanted to avoid rewriting hundreds of query calls.
I refactored everything to explicitly use the `Writer` variation as the default.
Then I worked backwards from our most expensive/frequent read queries and used the `Reader` variation.
We have database interfaces like `GetArrayWriter()` and `GetArrayReader()`. The read-only endpoint is all replicas in AWS RDS.
@ellisgl @andrewfeeney Doing it that way made sure every new query added is now explicitly reader/writer by design. You'll find some read queries need to run against the writer to be strongly consistent (e.g. select-before-delete, max ID, some counts).
For instances with simpler needs, if the reader endpoint isn't set we use the writer for everything.
AWS Aurora gives you an endpoint for all DB read replicas. When scaled to 0 replicas it uses the writer too. Added benefit of read-only handle
@jeff @andrewfeeney I know with Doctrine, you could make multiple instances, so you could have an entity manager for read and one for write.
@ellisgl @andrewfeeney Exactly!
In our case, our project started 21 years ago -- before most PHP frameworks + ORM. So we rolled core services ourselves. It's given us a lot of flexibility.
If I was building a highly scalable ActivityPub server like Mastodon in PHP, I'd still wrap `mysqli` calls w/o ORM. DB will be the main bottleneck.
Writes can scale w/ sharding (split up tables w/o common joins) + queuing.
On posts, union hot/cold tables (recent posts write to smaller table + age out)
@jeff @andrewfeeney why mysqli instead of PDO?
@ellisgl @andrewfeeney In our case, performance and specialization.
We chose to use MySQL specific tricks and optimizations. We allow user-built record types, fields, & queries (in our abstract query language), and fighting one database query planner was more than enough. We end up with infinite possible generated queries that need to be reasonably fast.
The same could be true for straight Postgres, etc.
If scale/speed is a top design goal (Mastodon clone), I'd avoid most abstraction.
@jeff @andrewfeeney I guess self promoting my PDO wrapper wouldn't be welcomed. 
@andrewfeeney @ellisgl hah! Andrew can obviously use whatever he likes.
But when you see Mastodon admins sharing tips, it really helps that they're all uniformly tuning Puma + Postgres + Sidekiq + Redis.
Hacks like PgBouncer w/ a single database server will only go so far.
If queries are ~80% read, 1-click replication in AWS gets you up to 15 DB replicas on one endpoint. Each of those can scale vertically, and you can cache/pool/shard. Scaling past that won't be a concern for most instances.
@ellisgl You just have to weigh by design if allowing people to swap out MySQL, Postgres, Oracle, SQL Server, SQLite, etc is worth it.
For us, it wasn't worth the support headache of trying to be a master of every database. Or telling clients to figure out scaling themselves.
It was somewhat a gamble in 2002 picking specific tech (not fitting into a client environment; including PHP itself). It's really no gamble at all in 2022 with containers + VMs to use what you know best.