Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Scaling to 100M: MySQL Is a Better NoSQL

blog.wix.engineering

Post reply on HN
Print view View original

111–120 of 183 posts

Re: Scaling to 100M: MySQL Is a Better NoSQL

#111

Post by koolba »

spotman wrote: post #93
koolba wrote: post #56

So much to disagree with here ... > Locks limit access to the table, so on a high throughput use case it may limit our performance. Then use a proper database that implements MVCC. > Do not use transactions, which introduce locks. Instead, use applicative transactions. Or just use a database that handle transactions more efficiently. > `site_id` varchar(50) NOT NULL, Why varchar(50)? UUIDs are 16-bytes. The best way…

>> Locks limit access to the table, so on a high throughput use case it may limit our performance. > Then use a proper database that implements MVCC. InnoDB does implement MVCC. MVCC is not a silver bullet. >> Do not use transactions, which introduce locks. Instead, use applicative transactions. > Or just use a database that handle transactions more efficiently. Easy to say, hard to implement at this scale. If you do…

> Of course it flies in the face of best practice for running a smaller system. Is there tradeoffs? Absolutely! Would it be smart to do this if the need for this scale is not obvious? Probably not

From the article:

> The routes table is of the order of magnitude of 100,000,000 records, 10GB of storage. > The sites table is of the order of magnitude of 100,000,000 records, 200GB of storage

That's tiny. Both of those easily fit in memory on modern hardware. This isn't cough web scale, this is peanuts.

The savings from having a simpler system that operates both transactional and the lack of disparate CASE/IF logic would win over this monstrosity of a design.

For a counterpoint where this type of model makes more sense check out Ubers data model[1]. Similar setup but more applicable use case and (without having any inside intel on it) I'd wager is justified.

[1]: https://eng.uber.com/schemaless-part-one/

Re: Scaling to 100M: MySQL Is a Better NoSQL

#112

Post by spotman »

koolba wrote: post #111
spotman wrote: post #93

Earlier quoted context omitted.

>> Locks limit access to the table, so on a high throughput use case it may limit our performance. > Then use a proper database that implements MVCC. InnoDB does implement MVCC. MVCC is not a silver bullet. >> Do not use transactions, which introduce locks. Instead, use applicative transactions. > Or just use a database that handle transactions more efficiently. Easy to say, hard to implement at this scale. If you do…

> Of course it flies in the face of best practice for running a smaller system. Is there tradeoffs? Absolutely! Would it be smart to do this if the need for this scale is not obvious? Probably not From the article: > The routes table is of the order of magnitude of 100,000,000 records, 10GB of storage. > The sites table is of the order of magnitude of 100,000,000 records, 200GB of storage That's tiny. Both of those e…

So your argument has shape shifted from "This is terrible advice" to "this is terrible advice unless your at uber scale".

Sounds like we are in agreement then - at high enough scale - this is solid advice.

Re: Scaling to 100M: MySQL Is a Better NoSQL

#113

Post by dbenhur »

rspeer wrote: post #104
BinaryIdiot wrote: post #31

> Use client-generated unique keys. We use GUIDs. Minor note but wouldn't UUIDs be better since they're time based? Sure it's really unlikely to hit an already used GUID but an UUID makes it impossible. In fact is there a use case where it's better to use GUIDs over UUIDs? I couldn't think of one but I could be omitting something from my thinking so I'm curious. Edit: apparently GUID and UUID are the same thing and G…

UUIDs of all formats are universally unique, for all practical purposes. Consider UUID4, the one with 122 random bits. The birthday paradox says that you would need about 2^61 UUIDs before you expect even one duplicate. If this concerns you, you might not recognize how big 2^61 is. (edited because I was originally talking about 2^64, but there are 6 non-random bits in UUID4)

What does "expect even one" mean? This is a probability equations, so what p does expect correspond to?

Let's make this real concrete, with 122 random bits, you can issue a million UUIDv4s every second for the next 100 years and still have a less than one in a million chance that you issued a duplicate.

https://lazycackle.com/Probability_of_repeated_event_online_... n = 5316911983139663491615228241121378304 (2122) p = 0.000001 => m = 3260955271619137 3260955271619137/(100000086400365) => 103

Re: Scaling to 100M: MySQL Is a Better NoSQL

#114

Post by koolba »

spotman wrote: post #112
koolba wrote: post #111

Earlier quoted context omitted.

> Of course it flies in the face of best practice for running a smaller system. Is there tradeoffs? Absolutely! Would it be smart to do this if the need for this scale is not obvious? Probably not From the article: > The routes table is of the order of magnitude of 100,000,000 records, 10GB of storage. > The sites table is of the order of magnitude of 100,000,000 records, 200GB of storage That's tiny. Both of those e…

So your argument has shape shifted from "This is terrible advice" to "this is terrible advice unless your at uber scale". Sounds like we are in agreement then - at high enough scale - this is solid advice.

> So your argument has shape shifted from "This is terrible advice" to "this is terrible advice unless your at uber scale".

No my argument is this particular design is both unjustified for the use case and poorly thought out/implemented. The uuid as varchar(50) is a dead giveaway of amateur status.

Re: Scaling to 100M: MySQL Is a Better NoSQL

#115

Post by viraptor »

spotman wrote: post #93
koolba wrote: post #56

So much to disagree with here ... > Locks limit access to the table, so on a high throughput use case it may limit our performance. Then use a proper database that implements MVCC. > Do not use transactions, which introduce locks. Instead, use applicative transactions. Or just use a database that handle transactions more efficiently. > `site_id` varchar(50) NOT NULL, Why varchar(50)? UUIDs are 16-bytes. The best way…

>> Locks limit access to the table, so on a high throughput use case it may limit our performance. > Then use a proper database that implements MVCC. InnoDB does implement MVCC. MVCC is not a silver bullet. >> Do not use transactions, which introduce locks. Instead, use applicative transactions. > Or just use a database that handle transactions more efficiently. Easy to say, hard to implement at this scale. If you do…

> But we are talking about performance... Having something in a single table that is denormalized is always going to be faster than having an elegant data model with "Everything In It's Right Place"

Unless you specify the workload, that's anywhere between completely true and exactly incorrect. Do you have big values you're always interested in and a couple of tiny ids? That's probably going to be faster in one table.

Are you querying only the metadata most of the time and the big value is multiple KB, almost never accessed? You're just killing your readahead and multiple levels of caches for no reason. "always going to be faster" is always incorrect ;)

Re: Scaling to 100M: MySQL Is a Better NoSQL

#117

Post by viraptor »

koolba wrote: post #56

So much to disagree with here ... > Locks limit access to the table, so on a high throughput use case it may limit our performance. Then use a proper database that implements MVCC. > Do not use transactions, which introduce locks. Instead, use applicative transactions. Or just use a database that handle transactions more efficiently. > `site_id` varchar(50) NOT NULL, Why varchar(50)? UUIDs are 16-bytes. The best way…

> If it's hex without dashes, it'll be varchar(32)

Or just char(32) no need to note the length if it's always the same.

Re: Scaling to 100M: MySQL Is a Better NoSQL

#118

Post by viraptor »

cnlwsu wrote: post #73
zzzcpan wrote: post #25

I thought nosql movement was about distributed systems, cap and all that. What does this "active-active-active" even mean? No consistency and no availability guaranties I presume?

I may be reading this wrong, but I think they are purposing a not C not A not P solution... thats "ok" fast? They explain how to make a single mysql instance run as key-value but I dont understand how it becomes master-master or cross DCs. Wonder if they run Jepsen or do any partition tolerance tests given their mentioning it.

AFAIK the only way to do 3 masters in vanilla mysql is ring replication. That means C is B's slave, B is A's, A is C's. If that's what they do, then yeah, it's a noCAP deployment. No consistency if you insert the same UUID at the same time into 2 masters. No availability unless you implement it yourself by retry to another master. No partition tolerance, because if you break one replication link, half the writes are not replicated between other servers and you can't really both reconfigure the ring and replay the transactions.

(Yes, they say active-active-active, not master-master-master, but then they say across DCs... It could be just M-S-S with config switch on failover, but for me the post suggests it's not that)

Re: Scaling to 100M: MySQL Is a Better NoSQL

#119

Post by stevesun21 »

beachy wrote: post #107
stevesun21 wrote: post #105

System design 101: keep business logic into the layer above database layer rather than relying on specific db system to implement them. In this way to design an system, there shouldn't have any different between using MySQL of using NoSQL, their role is just storage engine. So, you don't need to follow the relational database practice, like for example, foreign key, constrains, normalization anymore.

Trying to avoid using foreign key constraints in a relational database is not "system design 101", its an instant fail. When I cast my eye over a table with foreign key constraints, I am 100% certain that every single row conforms to those constraints, and always will. By contrast, when the same table does not have constraints, but instead relies on some business logic layer to enforce them, then I have to consider w…

Sadly, you remind me the DBA-is-everything system design style. In modern system designs, a system need more than just a database system to store business states, and to encapsulate business logic into higher layer is not just have flexibility also have scalability. Take sometime to think about the following three scenarios:

Scenario 1: what if a system need to migrate to different database system, then the whole business logic are need to totally re-implemented with the destination system DSL.

Scenario 2: if system need more just one storage system to persist business states, for example, I use db to store image metadata and use s3 to store the image? I don't believe the foreign key constrains will still works.

Scenario 3: if we have system need to process business state in asynchronously, for example, use message queue.

Also think about how to do unit tests (this is also how we keep the business logic correct) how to do CI/CD. System design is more than just a ERD design.

Re: Scaling to 100M: MySQL Is a Better NoSQL

#120

Post by jdiscar »

koolba wrote: post #56

So much to disagree with here ... > Locks limit access to the table, so on a high throughput use case it may limit our performance. Then use a proper database that implements MVCC. > Do not use transactions, which introduce locks. Instead, use applicative transactions. Or just use a database that handle transactions more efficiently. > `site_id` varchar(50) NOT NULL, Why varchar(50)? UUIDs are 16-bytes. The best way…

The point of this article is showing how MySQL could be used to get a lot of what a NoSQL solution provides. NoSQL certainly has a place, but a lot of people don't really understand what that is and simply use NoSQL because it's popular, which cuts them off from a lot of useful features a SQL solution could provide them. That said, you're right more care could have been put in the details of the article, but a lot of the points could be correct for their situation.

For example, 'Do not normalize.'

This was in the context of a read heavy table that competes with NoSQL. In that context, I think this is accurate. We noticed a big difference after denormalizing when we went from millions of rows to billions of rows.

The general advice of SQL solutions being as useful as NoSQL to a certain scale is good. I don't think the individual examples are horrible, but they aren't universal advice to achieve NoSQL performance.

Post reply on HN