Does anyone know what they mean by `Instead, use applicative transactions.`?
Scaling to 100M: MySQL Is a Better NoSQL
171–180 of 183 posts
Re: Scaling to 100M: MySQL Is a Better NoSQL
#172Mongo comes with geospatial indexing baked right in. Never mind map / reduce. It comes down to the data structures of our times, which are increasingly not relational.
Re: Scaling to 100M: MySQL Is a Better NoSQL
#173Earlier 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…
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 b…
they would have scaling issues with traditional sql systems but don't really have billions of transactions to process.
If you have scaling issues with traditional SQL systems, the first thing you should do is understand your problem, not cargo-cult to a different paradigm.
More specifically:
* You do not get to complain about SQL transaction speed if you use denormalized tables.
* You do not get to complain about SQL transaction speed if your primary keys are varchar(50).
* You do not get to complain about SQL transaction speed if you store all non-identifying data in a single json blob.
Re: Scaling to 100M: MySQL Is a Better NoSQL
#174Earlier quoted context omitted.
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…
Do you think it's more likely that they invented a unique id scheme that requires 50 bytes, or that they're using an overly wide datatype for ordinary UUIDs?
And I have to pick one of those two options?
Like I mentioned, there are different UUID/GUID schemes using more than 128 bits publicly available already. You don't have to invent one. They may simply be using one of those with additional bytes reserved for future algorithmic changes (using first few bytes as a header to specify the algorithm).
Re: Scaling to 100M: MySQL Is a Better NoSQL
#175Earlier 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…
> 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" This one folk wisdom that is untrue. There are significant speed disadvantages relating to large blobs of data the database doesn't understand. Serialisation time makes returning large JSON/XML objects expensive when you only need a small part. Overwriting a wh…
the reason for putting everything in json in one column is because alter table on a large database can take days. the only sql database i'm familiar with that doesn't have this problem is tokudb.
> Serialisation time makes returning large JSON/XML objects expensive when you only need a small part.
the expensive part of reads is finding the row on disk. once you've found the row the cost of reading part of the row vs the whole row is negligible. amount of data sent over the network doesn't matter either in 99% of cases. these days network bandwidth is orders of magnitude greater than random IO bandwidth on a spinning disk and still greater than random IO bandwidth on a SSD assuming you're using 10GbE.
> Overwriting a whole object to increment a counter is an unnecessary source of IO.
there is no way to write 4 bytes to a hard disk. disk io is performed in multiples of 4096 bytes, so it doesn't matter whether you just update the counter or update the whole blob. only incrementing the counter may allow you to write less data to the write ahead log, so you may save some IO there, but most databases put the whole row in the log anyway so it doesn't matter.
> Duplicating JSON keys in every record bloats the size of your working set, making it more difficult to fit into memory (or the fast part of your SAN).
this is definitely true, it's better to use an extensible but compact format like protobuf or thrift if you have a significant amount of data. or you could use a better database and not have to worry about the cost of adding columns.
Re: Scaling to 100M: MySQL Is a Better NoSQL
#176Anybody else thinks Postgres would have been a better comparison here?
Re: Scaling to 100M: MySQL Is a Better NoSQL
#177Earlier quoted context omitted.
Do you think it's more likely that they invented a unique id scheme that requires 50 bytes, or that they're using an overly wide datatype for ordinary UUIDs?
And I have to pick one of those two options? Like I mentioned, there are different UUID/GUID schemes using more than 128 bits publicly available already. You don't have to invent one. They may simply be using one of those with additional bytes reserved for future algorithmic changes (using first few bytes as a header to specify the algorithm).
But is it likely that they are? I would imagine no.
Re: Scaling to 100M: MySQL Is a Better NoSQL
#178Earlier quoted context omitted.
> 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" This one folk wisdom that is untrue. There are significant speed disadvantages relating to large blobs of data the database doesn't understand. Serialisation time makes returning large JSON/XML objects expensive when you only need a small part. Overwriting a wh…
the reason for putting everything in json in one column is because alter table on a large database can take days. the only sql database i'm familiar with that doesn't have this problem is tokudb. > Serialisation time makes returning large JSON/XML objects expensive when you only need a small part. the expensive part of reads is finding the row on disk. once you've found the row the cost of reading part of the row vs…
> alter table on a large database can take days
There's a lot to migrations in these various implementations but in short: every variant of alter table is an "online" operation (meaning it doesn't hold an exclusive lock) in InnoDB as of MySQL 5.6 and it's possible with various well-supported third party tools before that. For Postgres: most migrations can be done online and those that do hold a lock are typically constant time.
Admittedly migration has been a big problem in the past but that hasn't been true for years now.
> the expensive part of reads is finding the row on disk
Hopefully most SQL queries in an optimised setup are not finding a row on disk! The difference between reading the whole row from on-disk heap storage and reading the interesting part of it from an index in memory is in fact considerable: 1000x or more - and obviously far worse if you have to scan through any of the heap storage.
> amount of data sent over the network doesn't matter either in 99% of cases
It actually matters hugely in the case of JSON blob storage because it all has to be doubly deserialised on the other end - first from the db's wire protocol and then from JSON. There are many apps out there for which JSON serialisation is a bottleneck (for the CPU, not the network) - that's why there are so many "fast JSON" libraries.
Good point - you could mitigate this by using something quicker. I haven't seen anywhere do that - the ability to read the database with other tools is normally useful
> most databases put the whole row in the log anyway so it doesn't matter
re: this whole topic - I'm not proposing switching a JSON blob with a table for which every JSON field was reified into a column. I'm comparing to a normalised design with narrow tables (most tables have fewer than 5 columns). The other stuff about serialisation applies.
Re: Scaling to 100M: MySQL Is a Better NoSQL
#179Stupid question here: what are serial keys, and how do they impose locks?
Re: Scaling to 100M: MySQL Is a Better NoSQL
#180Stupid question here: what are serial keys, and how do they impose locks?
Ah, did he mean "SERIAL" as in "BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE" ... and the reason why this locks a table is because the database, on insert, needs to figure out a valid key?