Scaling to 100M: MySQL Is a Better NoSQL • Hacker News Board

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

#141

Post by combatentropy » Wed, May 25, 2016, 4:53 AM UTC

chucky_z wrote: post #136

Everyone should try PostgreSQL with hstore (and JSONB now, too!). This is a key/value store inside an RDBMS that just works, and it works great! I converted a crappy sloppy super messy 1000+ column main table in a ~800GB database to use hstore, it was, in real world benchmarks, between 7x and 10,000x (yes, really, ten thousand times) faster. The CEO of the company who had a technical say in everything, and was very p…

> SQL itself is a little bit goofy, right?

The proper pronunciation of SQL is SQuirreL.

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

#142

Post by eblanshey » Wed, May 25, 2016, 5:09 AM UTC

> Do not perform table alter commands. Table alter commands introduce locks and downtimes. Instead, use live migrations.

Care to elaborate more on this? What do you mean by live migrations?

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

#143

Post by partiallypro » Wed, May 25, 2016, 5:16 AM UTC

ph33t wrote: post #46

I hate these stupid "my db is better than whatever db" articles. 1) What db to be used depends on the situation AND MORE IMPORTANTLY 2) what experience your staff has I can say that 10 years go, I would have chosen M$SQL over MySQL and it would have been the correct choice. At the time I had almost 10 years experience with M$SQL and almost none with MySQL. Now I have more than 10 years of MySQL under my belt. AND the…

First off, I can't take you seriously after using a dollar sign in Microsoft. That's just a conversation killer for anyone talking seriously in tech. This isn't an IRC channel for 13 year olds in 2004.

Secondly, the article isn't about what is "better" overall. It's about scaling SQL, and how noSQL isn't always necessary. The "in" thing to do right now is to have noSQL in your stack, blindly, without looking at your project. Or doing expensive migrations to noSQL solutions when you already have an expansive infrastructure built on SQL but need to scale. Wix is just giving insight into their techniques with MySQL and how in the end it made more sense for them than going with something like Mongo.

TL;DR: You didn't read the article.

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

#144

Post by tagrun » Wed, May 25, 2016, 5:31 AM UTC

wtetzner wrote: post #140

tagrun wrote: post #126

Earlier quoted context omitted.

>> `site_id` varchar(50) NOT NULL, > Why varchar(50)? UUIDs are 16-bytes. Why do you think it's a UUID? > The best way to store them would be the binary bytes (which is how postgres stores them). Is it actually better than a pair of BIGINTs?

>Why do you think it's a UUID? Because of this: >Also notice that we are not using serial keys; instead, we are using varchar(50), which stores client-generated GUID values

GUID and UUID refer to different things in general. Also, depending on the context, they can be longer than 128-bits. Oracle Coherence API defines 256-bit UUIDs for example (which is clearly not RFC 4122 or Microsoft GUID, but it still is an identifying number which can statistically be called unique, which a UUID is).

As long as it meets the statistical properties for collision, I don't see any problems with making a 50-bytes GUID. The essential thing is the statistical property, not the number of bits.

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

#145

Post by jamesblonde » Wed, May 25, 2016, 5:35 AM UTC

I thought this article would be about the true MySQL NoSQL system: MySQL Cluster (or NDB). It scales to 200m transactional reads per second - per second! http://highscalability.com/blog/2015/5/18/how-mysql-is-able-... We have got 16m read/sec on our commodity rack with MySQl Cluster, so it's not a fantasy result.

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

#146

Post by EGreg » Wed, May 25, 2016, 5:58 AM UTC

Here is my (albeit limited experience) advice:

1. Use PostgreSQL, or MySQL with InnodDB for row level locking

2. Huge tables should be sharded with the shard key being a prefix of the primary key.

If you need to access the same data via different indexes then denormalize and duplicate the index data in one or more "index" tables.

3. Do not use global locks. Generate random strings for unique ids (attempt INSERT and regenerate until it succeeds) instead of autoincrement.

4. Avoid JOINs across shards. If you use these, you won't be able to shard your app layer anymore.

5. For reads, feel free to put caches in front of the database, with the keys same as the PK. Invalidate the caches for rows being written to.

It's actually pretty easy to model. You have the fields for the data. Then you think by which index will it be requested? Shard by that.

Note that this will still lead you to a huge centralized datacenter!! Because your authentication happens at the webserver level and then you just have all the servers trust each other. While it is a nice horizontal architecture, it leads to crazy power imbalances like we have today. Consider instead making it a distributed architecture, where the shards turn into domains, and each user on each domain has to auth with every other domain. But your network can then be distributed without a single point of failure. What's more, local area networks will be able to host your app and be quick without the signal bouncing halfway around the world.

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

#147

Post by Jweb_Guru » Wed, May 25, 2016, 6:23 AM UTC

tracker1 wrote: post #137

viraptor wrote: post #115

Earlier quoted context omitted.

> 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.…

Querying a single table with an indexed key will be faster as a single lookup than doing a JOIN, let alone several. That said, it really depends on your load, and if you're not dealing with hundreds of thousands of simultaneous users, and haven't over-normalized your data, you can get by with a lot of options. And a good caching layer for mostly-read scenarios will likely get you further anyway. That said, use a syst…

> Querying a single table with an indexed key will be faster as a single lookup than doing a JOIN, let alone several.

Not always, particularly when you're returning multiple keys at once. Some comparisons with column stores would be instructive.

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

#148

Post by johanneskanybal » Wed, May 25, 2016, 7:16 AM UTC

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…

Not sure how you manage to miss the point of the article. I'll break it down for you:

* A lot of traditional sql solutions have scaling issues.

* A lot of nosql solutions for these issues have become popular in recent years. Their main use case is web scale (simplification)

* A ton of actual use cases fall between those two chairs i.e they would have scaling issues with traditional sql systems but don't really have billions of transactions to process.

The article presents some valid real world approaches for this third case, some of it might be case dependant but overall pretty good advice.

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

#149

Post by bechampion » Wed, May 25, 2016, 7:25 AM UTC

I love mysql , saved my ass many times , but this article doesn't mean anything .. it just says that you can use subqueries and joins to do "nosql"... we all know that .. you can also use a text file. I'd like if mysql copies what postgres has done with hstore.

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

#150

Post by jbergens » Wed, May 25, 2016, 7:39 AM UTC

jdiscar wrote: post #121

koolba wrote: post #114

Earlier quoted context omitted.

> 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.

Are you saying a NoSQL solution is better for this use case? Because that's what this article is asking. Sure, they could do things better (can't we always?), but that's not the point they're trying to make. Like you said, 10GB of data isn't very much, it really doesn't matter if you go with NoSQL or SQL. But SQL will probably give you more flexibility and will be easier to manage until you get really, really big.

I think you loose flexibility with sql. If the usage changes slightly and you need to index one or two more fields you would have to do an alter table and read through 200 GB of data deserializing all blobs to put the values into the new field. A good nosql would just add a secondary index. You might also have a hard time doing map-reduce on sql. It is often built into nosql systems.