6 ms·
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
by koolba 10y ago
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 to store them would be the binary bytes (which is how postgres stores them). If it's hex without dashes, it'll be varchar(32). If it's hex with dashes, it'll be varchar(36). Why did they pick 50? Future growth? Smart keys? Schema designer doesn't know what a UUID actually is?
> Do not normalize.
Bullshit. Normalize as much as is practical and denormalize as necessary. It's much easier to denormalize and it greatly simplifies any transaction logic to deal with a normalized model.
> Fields only exist to be indexed. If a field is not needed for an index, store it in one blob/text field (such as JSON or XML).
This is terrible advice. Fields (in a table) exist to be read, filtered, and returned. If everything is in a BLOB then you have to deserialize that BLOB to do any of those. That doesn't mean you can't have JSON "meta" fields but if your entire schema id (id uuid, data json) you're probably doing it wrong. It's next to impossible to enforce proper data constraints and all your application logic becomes if/then/else/if/then/else... to deal with the N+1 possibilities of data. Oh and when you finally add a new one, you have to update the code in M+1 places.
- wvenable 10y agoTo be fair, this article is about using MySQL as a NoSQL storage and so all of this advice is geared towards that use-case. I'd kill for so much traffic that any of this would be necessary as opposed to any RDBMS best-practices. I do agree that UUIDs should be stored differently -- the use of varchar rather than a fixed length type for a primary key will hurt performance.
- agentgt 10y agoWe use varchar for UUID (on postgres) which surprisingly hasn't been that terribly performance wise. And yes we do use varchar(36) although on postgres it doesn't really matter because I think almost all varchars are text. I would love to switch to native UUID someday though.
- narfz 10y agowe did the same and then switched to the native UUID type. it eliminates the need for a unique index and we saw a drop in storage space by 1/2. it's totally worth converting UUID to the uuid field. ALTER TABLE my_table ALTER COLUMN my_uuid TYPE uuid USING uuid::uuid;
- olavgg 10y agoAnd you want as much of the index as possible in RAM. When the index is 10 times larger than necessary and do not fit in your RAM, you get a very expensive performance penalty!
- StevePerkins 10y agoSame, but we use raw binary columns (i.e. "BINARY(16)" for MySQL and "BYTEA(16)" for PostgreSQL). Conversion between the database-native UUID type and the application-layer UUID type is often so flaky (particularly with Java and JPA).
- jdiscar 10y agoYeah, VARCHAR and TEXT are backed by the same datatype, but VARCHAR(N) gives you a limit check (that can be a pain to change if you ever need to.) There shouldn't be any noticable performance difference. For UUIDs, the native type is way better.
- elmigranto 10y agoAll the `CHAR`s in Postgres are `TEXT`, from the docs: Tip: There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs and slower sorting. In most situations text or character varying should be used instead.
- calpaterson 10y agoThis article is pretty terrible but just in point of fact: it looks like they are using MySQL's InnoDB backend - which does support transactions and MVCC. If they're even talking about avoiding transactions for speed purposes (no matter how stupidly) they must be talking about Inno because in MyISAM BEGIN and COMMIT are no-ops.
- nerfhammer 10y agoThe article says "Note that a transaction is using a DB-level lock that prevents concurrent writes—and sometimes reads—from the affected tables." In innodb locks are row-level; myisam supports table-level locks though that's not and shouldn't be confused with a transaction; I don't know what a "database-level" lock is supposed to mean, are they really saying they're locking all tables to do a write? It doesn't sound like this author understands what a transaction is.
- justizin 10y ago> If they're even talking about avoiding transactions for speed purposes (no matter how stupidly) As many have pointed out, so much criticism of this article ignores that it is comparing to other key-value stores which are not transactional. Many of what this would compete with are AP, with Consistency not guaranteed. It really sounds like they should be talking about MySQL cluster, which is protocol compatible but a completely separate implementation and essentially a key-value store with RDBMS attributes atop it. It supports many-master mode like mongo and other distributed systems, which is fairly mandatory for replacing them. It's hard to argue you can replace HDFS with anything that's not distributed, and if you didn't, why wouldn't you just use .. the actual FS? The author may not really understand that HDFS is optimized for storing large-ish files.
- justinhj 10y agoThere is nothing terrible or stupid about avoiding joins/transactions for speed. The technique of using blobs of data in MySQL rows in this article is perfectly valid and widespread at this point. As long as you understand the trade offs.
- 10y ago
- spotman 10y ago>> 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 a lot of writes and reads concurrently to a hot dataset, it's really quite hard to beat this architecture. This is why its such a popular and battle tested solution for many extremely high scale applications with workloads like this. Not to mention extremely well understood. >> Do not normalize. > Bullshit. Normalize as much as is practical and denormalize as necessary. It's much easier to denormalize and it greatly simplifies any transaction logic to deal with a normalized model. 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" >> Fields only exist to be indexed. If a field is not needed for an index, store it in one blob/text field (such as JSON or XML). > This is terrible advice. So facebook/friendfeed, uber, dropbox, and many more or wrong then. Ok. This is really all best practice for running something like this. 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. You end up having more logic in your application and coordination layers, but this is all pretty good advice for people at this scale, and certainly not bad at all.
- koolba 10y ago> 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/ https://eng.uber.com/schemaless-part-one/
- viraptor 10y ago> 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.
- jdiscar 10y agoThe 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.
- tagrun 10y ago>> `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?
- wtetzner 10y ago>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
- tagrun 10y agoGUID 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.
- mdellabitta 10y agoDo 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?
- tagrun 10y agoAnd 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).
- pg_is_a_butt 10y agoyou totally missed the point, and i have so so so much more that you did to diagree with what you responded to here.
- return0 10y agoThis is not terrible advice in general. It is also not good advice in general. To make things scale, you obviously have to "break the norms". They give an insight how their specific case works. It's "watch, learn and pick what fits you" material.