News for hackers. Stuff that matters.
Re:Scaling Databases at Activision [pdf] by Nathanba on Friday April 21, 2023 @08:29PM (#35662058)
I really wonder about the justification for using MySQL/Postgres sometimes. How is it not simply superior in almost every way to start with ScyllaDB which is faster in every way and the only downside I can see is that you have a very limited way to query things. But so what, at least it's fast. I mean isn't that the nr.1 thing you want from a database, it's not about comfort.. it's about storing data. It is genuinely annoying though for example that you can't even alter a column type after the column was created in ScyllaDB.
Re:Scaling Databases at Activision [pdf] by christophilus on Friday April 21, 2023 @08:37PM (#35662108)
> the only downside I can see is that you have a very limited way to query things

That’s a pretty big one. If you know you need the scale, the trade off might be worth it. But I’m 99% of projects, Postgres is more than adequate and has great flexibility.

Re:Scaling Databases at Activision [pdf] by Nathanba on Friday April 21, 2023 @09:04PM (#35662263)
yeah but I don't think that we can just trust that when people say this. They are invested into these single node databases for 10+ years probably. They are not going to tell you: Hey yeah, if you architect your app differently you could have x10 the performance.. but don't because it's a bit annoying when you query data. I mean is that being objective? Lots of people are not okay with the performance of mysql/postgres, that's the whole point of solutions like crdb/tidb/singlestore/vitess/yugabyte/citus and so many others. To tell someone that they should start with mysql or postgres is the equivalent of telling them that they'll have to migrate in the future and relearn a whole bunch of stuff about the operations, like backup etc. I don't really like the feeling of choosing a technology with the mindset that I'll have a lot of problems when there is a performance problem and at some point the pain will be so great that I'll have to switch to vitess.
Re:Scaling Databases at Activision [pdf] by richieartoul on Friday April 21, 2023 @09:14PM (#35662335)
It’s really not just about annoyance it’s about correctness and speed of iteration. Many apps that can be built trivially in a couple SQL queries cannot be implemented correctly in Scylla without reinventing half of tidb/vitess/etc. On top of that, it’s very hard to hire and pay the engineers who are capable of doing that in the first place. Finally, even if you manage to do it, your competitors will have shipped 100x more functionality than you in that time.

That’s why people use these systems and why so many very smart people spend so much time trying to make them better

Re:Scaling Databases at Activision [pdf] by Nathanba on Friday April 21, 2023 @10:03PM (#35662643)
Well I do everything myself, I think what you said is mostly true only from the perspective of people who use orms and then everything works initially sure. But then are you really in control of your data and do you really know what's going on? I can only speak from a .net perspective now but the popular ORMs are all extremely slow, so with some research you end up using dapper. But then you end up basically writing SQL manually because that's just how it is. It wouldn't be any different for many languages, like Rust. Essentially I think unless you use java+hibernate or some other extremely common combination, you'll end up writing SQL yourself and then it's pretty painful anyway because you have a second textual DSL embedded into your other code with its own datatypes that you constantly have to map to the language's datatypes and vice versa. So iteration wise it doesnt even seem to be about mysql vs scylladb, it is about orm vs. no orm.

And regarding correctness and iteration speed.. yes sure but it's easy to say that you are correct when you only run on a single node. The reason why those distributed solutions are less correct is because it's harder to be correct. For example in vitess you end up having to do a bunch of manual work with their sequences for every table. In scylladb afaik you just trust that the timeuuids are unique or you do the same and pregenerate bigints into a table.

My point is: To say that mysql/postgres are safer because they are correct and you can iterate faster is only true if you go down a very narrow path with your orm and you stay on a single node. You are essentially postponing all the pain. At some point you'll stop using the orm and write your own, at some point you'll stop using the single node.

Re:Scaling Databases at Activision [pdf] by richieartoul on Friday April 21, 2023 @10:48PM (#35662913)
I think it’s the last paragraph I’m really disagreeing with. Many many application will do just fine with a single Postgres. If that is not enough even on a big box, they can scale out to a large master system like aurora. If that is not enough, it’s much easier to try and scale with something like Vitess or Cockroach that offers a lot more consistency and correctness out of the box.

Scylla is great for specific workloads, but it’s a niche tool that makes sense for specific high volume workloads but I wouldn’t recommend it as a general purpose application development database. It will slow you down a lot and a lot of really simple features will be hard to implement.

Also I’m not talking about ORMs. Writing SQL by hand in your application is 10-100x more productive for feature development than using something like Scyllas APIs directly IMO.

Re:Scaling Databases at Activision [pdf] by Nathanba on Friday April 21, 2023 @11:25PM (#35663082)
CQL to me is just another SQL dialect, databases all have different, incompatible dialects. And on top of that, it's not like SQL is a highly productive programming language anyway, it's a pretty hard to use language for anything that's even slightly more complicated. I don't really see where this x10-x100 more productiveness would come from compared to scylla APIs which are just another form of SQL dialect so it isn't that different. Sure I can't write e.g (recursive)CTEs or repeating events in Scylladb but then: do I really want them anyway? wouldn't it be better anyway if I write everything as a set of simple queries? Wouldn't it be better anyway if instead of an event on an interval in SQL, I'd just run a simple SQL query in a background thread from my application on an interval?
Re:Scaling Databases at Activision [pdf] by Mike_12345 on Saturday April 22, 2023 @12:14AM (#35663271)
> CQL to me is just another SQL dialect

SQL is for relational databases. That's a different paradigm to CQL. The similarity is superficial. CQL is not a dialect of SQL. Not even close.

Re:Scaling Databases at Activision [pdf] by Aeolun on Saturday April 22, 2023 @12:01AM (#35663235)
People saying the performance of Postgres is not adequate is my red flag for them not knowing what they’re doing.

Of course, sometimes they do have actual performance issues, but 99 out of 100 times they just don’t know what an index is.

Re:Scaling Databases at Activision [pdf] by geenat on Friday April 21, 2023 @09:17PM (#35662347)
It's a great point- I've been looking into this path myself: ScyllaDB + logic inside app.

The main issue I've run into is deployment. It's just very rough compared to CockroachDB or Postgres, which are single binary or very close to it.

If ScyllaDB became as easy to set up as CockroachDB (or close-ish), they'd have a lot more users, I think.

You can make Postgres setup this easy: https://gist.github.com/gnat/cfe3754c3dc817c7fb8b2225ef4db62...

Re:Scaling Databases at Activision [pdf] by Nathanba on Friday April 21, 2023 @09:56PM (#35662586)
yes the easy setup with crdb is really attractive but then you realize that they hide a few very important features behind the paid option. No incremental backups and no partitions (I think of any kind...). Those are some big features to lose, obviously I'm not complaining, just comparing. That was the main reason why I started developing my project in MySQL because at least MySQL has a lot of different free providers. YugabyteDB exists for Postgres too but initially I was more impressed with Vitess and TiDB because TiDB is used by some really huge chinese companies and if they don't have a problem then it must be okay. Unfortunately TiDB essentially doesn't want you to run a single node, ever, and their smallest recommended deployment is 5 nodes. That's a lot. Citus also exists for Postgres but their docs basically tell you that it's only recommended for analytics so I don't know what to think of it. And it sounds like all it's doing is a basic master-slave postgres setup with quite a few manual things you have to do to even benefit (manually altering tables to make them sharded/partitioned)
Re:Scaling Databases at Activision [pdf] by geenat on Friday April 21, 2023 @10:26PM (#35662793)
I totally hear you, been down a similar path.

As for the "huge chinese" databases, not to sound like a conspiracy, but CockroachDB has outperformed them in my server load testing (stress test with very basic "SELECT FROM users" and "INSERT INTO users" on KC3000 + Ryzen 7950x)- so I'm beginning to seriously question all of the self-published benchmarks from mainland china.

That, and when one considers they have a similarly rough deployment story compared to ScyllaDB, is it maybe worth just going directly to ScyllaDB in the first place?

One other damming thing: I've also observed that all Spanner (Big Table) inspired databases have lower throughput compared to Postgres when under 5-ish servers: Aka you only begin to see throughput benefits after you have a sizeable cluster.

ScyllaDB / Cassandra is a different architecture, though.

Heck even Vitess, pretty rough deploy story as well (they really push k8s on you), but that's why PlanetScale exists, IMO.

Re:Scaling Databases at Activision [pdf] by Nathanba on Friday April 21, 2023 @11:15PM (#35663041)
One nice thing I found today is that ScyllaDB has a pretty nice JSON feature, they let you insert and select directly via json strings. I can't do that in MySQL which is annoying, I could do something equivalent in Postgres. One of my biggest complaints about SQL in general is the datatype mapping / serialization and if I can simply use a json library in my language of choice then I save on a lot of work. https://www.scylladb.com/2019/01/10/json-support-in-scylla/

But you are right about how important it is to have a simple deployment, it's true for all of the databases. Sure there is docker but that usually has its own problems, like with scylla where it wouldn't properly forward ports for whatever reason so I could never connect to it. Then I had to do all kinds of manual fixes and install it on a VM to edit configs to make it run. This is really the stuff that makes you want to use crdb and be done with it all, even though it's slower and eventually requires payment.

Re:Scaling Databases at Activision [pdf] by heipei on Saturday April 22, 2023 @03:26AM (#35664190)
We're running ScyllaDB on Docker via Nomad and it's been an absolute breeze. One simple container with a handful of open ports. I don't get what's complicated about ScyllaDB deployment.
Re:Scaling Databases at Activision [pdf] by geenat on Saturday April 22, 2023 @02:12PM (#35669406)
One strategy I've considered:

Write Postgres in a way that's fully compatible with CockroachDB, with the intention of transitioning to CockroachDB if needed.

Basically: 1. BIGINT or UUID or TEXT or composite Primary Keys only. 2. Limited use of sequential indexes. (Ex: timestamp. Not the end if the world but creates hotspot, will require a hash sharded index) 3. Avoid advanced features such as stored procedures. 4. Avoid or very light use of foreign keys. (Performance)

Going straight to CockroachDB seems logical as well, but you'll hit the performance wall much sooner on 1-2 servers, and will require a cluster to match it.

Re:Scaling Databases at Activision [pdf] by Nathanba on Sunday April 23, 2023 @12:18AM (#35673245)
if crdb had some kind of perpetual fallback license like Jetbrains then I would be far happier with the prospect of eventually switching to crdb as an option. But "contact us" pricing is not exactly unscary pricing. If you think about it, even the infamously opaque Microsoft SQLServer has a pricing table nowadays. Therefore I tried to calculate some kind of pricing comparison, I'm looking at the cpu alone now. I'm seeing that they ask for $0.46/h for m5.large and $6.34/h for their m5.8xlarge (managed dedicated offering) Using the aws price calculator and selecting these instance types directly it would cost x4 less to rent the aws instances directly. Paying 4 times your raw server cost for licensing is not exactly a dream come true.
Re:Scaling Databases at Activision [pdf] by geenat on Sunday April 23, 2023 @03:39AM (#35674186)
Can confirm the inability to select null values. Have not yet found reasonable pattern / workaround.. The reasoning seems to be that null values are not stored-- it's displayed as null in csql, but in reality, that data just does not exist.

For the record, CockroachDB does not store the value either, but it allows querying null.

A workaround may be stepping through the full table manually checking for null "in app" and cleaning them that way.

Noticed INSERTS are treated as UPSERT unless you use IF NOT EXISTS... ScyllaDB doesn't give a dump if it's a unique primary key lol. I can live with the UPSERT footgun, though.

Re:Scaling Databases at Activision [pdf] by geenat on Monday April 24, 2023 @06:09PM (#35693937)
Found a solution to address the lack of WHERE x = NULL

SELECT * FROM users;

Then you can plug your PK into.

UPDATE users SET address='' WHERE user_id IN (9affeac3-5d92-111d-779c-55eb6d78a806, ...) IF address=null;

Then you can do your SELECT to do your repair.

SELECT * FROM users WHERE address='';

_______________________

Another footgun: No default values for columns in CREATE TABLE. Very annoying.

Another footgun: Only "=" and "IN (...)" is supported in WHERE for partition keys. https://docs.scylladb.com/stable/cql/dml.html#the-where-clau...

This means, you cannot do UPDATE WHERE user_id > 0 ....

I'm starting to really appreciate the effort CockroachDB went to, to make their version of "CQL" postgres compatible, even though architecturally they are both key-value store databases under the hood... it's just a shame CockroachDB just has far lower performance because of enforced consistency and no way to turn it off.

To be fair, ScyllaDB is removing footguns with every new version, just not fast enough for my taste.

Re:Scaling Databases at Activision [pdf] by geenat on Saturday April 22, 2023 @10:02PM (#35672672)
So ScyllaDB allows you to choose how much Consistency in CAP you desire, in order to gain performance. Pretty interesting.

In contrast to CockroachDB, Postgres, etc, where Consistency in CAP is always enforced and there's no way to turn it off.

You turn it on/off by simply specifying IF NOT EXISTS which is insanely simple: https://www.scylladb.com/2020/07/15/getting-the-most-out-of-... (called "lightweight transactions" or LWT)

I wish CockroachDB had a way to trade consistency for speed when desired.

Another sidenote, in my limited investigation so far- when using LWT in ScyllaDB: performance of CockroachDB INSERT and ScyllaDB INSERT both line up fairly evenly. Still investigating, but this makes sense- Only when you avoid IF NOT EXISTS, ScyllaDB pulls away massively in performance.

Re:Scaling Databases at Activision [pdf] by mulander on Saturday April 22, 2023 @06:37AM (#35665115)
Hi Nathanba, I am a technical program manager for the Citus extension at Microsoft.

> Citus also exists for Postgres but their docs basically tell you that it's only recommended for analytics

Could you point me at the docs that made you think that? We find Citus very good at multi-tenant SaaS apps (OLTP), IoT (HTAP) workloads and analytics (OLAP).

> And it sounds like all it's doing is a basic master-slave postgres setup with quite a few manual things you have to do to even benefit (manually altering tables to make them sharded/partitioned)

This is true for now. We are looking into ways to make onboarding easier. That said, the time spent on defining a good sharding model for your data often leads to very good perf characteristics. Regarding the architecture, I personally find Citus closer in spirit/design to what Vitess is doing. Additionally, every node in the cluster is able to take both writes and reads, so I don't see the parallel to a basic primary/secondary setup.

Re:Scaling Databases at Activision [pdf] by Nathanba on Sunday April 23, 2023 @12:29AM (#35673275)
I'm looking at this page: https://www.citusdata.com/use-cases "Multi-Tenant SaaS" to me very clearly sounds like there are features specific towards isolating the database into different customer domains ("tenants"). Which to me implies that this is sort of a scaling trick that isn't going to provide performance for a regular application where I can't partition my data like that.
Re:Scaling Databases at Activision [pdf] by doh on Friday April 21, 2023 @09:33PM (#35662456)
The main difference is eventual vs strong consistency. Scylla scales great, but then the developer has to guard the consistencies. And that is quite challenging job, that usually trips even the most experienced developers.

There is a middle ground, storage systems/databases that both scale and provide strong consistency. FoundationDB, Citus, etc.

SQL databases are not only easier to query, but also enforce data consistency. While querying is their core job, it's hardly the only one.

Re:Scaling Databases at Activision [pdf] by tomnipotent on Friday April 21, 2023 @11:43PM (#35663156)
> the only downside I can see is that you have a very limited way to query things

Which is a huge downside.

> But so what, at least it's fast

So is an RDBMS for most read/write workloads. Few will see an overall benefit from Cassandra and the labor needed to replicate the conveniences offered by an RDBMS.

> it's about storing data

It's not about storing data, it's about using data to accomplish business objectives with the promises that entails. You opt for something like Cassandra/Scylla because it solves a technical challenge otherwise untenable or more expensive, but it's a poor choice if your problem is reasonably solved with what Postgres can offer.

Re:Scaling Databases at Activision [pdf] by stickfigure on Saturday April 22, 2023 @12:46AM (#35663409)
Looking back over my ~30 yr career I could pretty neatly divide my projects into two categories:

* Low volume, complicated queries

* High volume, simple queries

In the rare case that I've seen high volume, complicated queries it generally involves a lot of pre-indexing, denormalization, and exotic data structures.

Postgres really shines in the first case, which is probably the dominant case for "data important enough to pay people to work on".