| |||||||||||||
Cool new Free softwareCool new Free softwarePosted Dec 20, 2012 9:51 UTC (Thu) by paulj (subscriber, #341)In reply to: Cool new Free software by Wol Parent article: Status.net service to phase out, replaced by pump.io
I'm curious. You describe the problem with FNF as being the complexity of ensuring consistency between multiple writes, with NFNF having the advantage of generally doing single writes. However, you then describe the problem of cases where you may still have to update multiple entities in one transaction even in NFNF (the invoice example), and say it's tricky. How do you deal with this?
It seems to me that the fundamental performance/integrity trade-off and the attendant complexity is there in both cases, but in the FNF, ACID DB the DB handles this for me while with the NFNF DB I have to handle that myself. So the ACID DB way may result in all transactions suffering some performance overhead, from ensuring integrity, but it will stay consistent (if I've done the schema right) and handle those issues for me. While the NFNF DB gives me fast, no-overhead writes, but I have to be aware of and handle the complex, tricky multi-update integrity issues myself. Is that a fair view?
Would it be fair to say that the NFNF way would be more to suited to DB gurus, while anyone who hasn't spent a good bit of time working with and thinking about multi-update integrity issues might be better off using the ACID DB approach (at least, until update performance is actually proven to be an issue)?
(Log in to post comments)
Cool new Free software Posted Dec 20, 2012 10:02 UTC (Thu) by man_ls (subscriber, #15091) [Link] The first part looks good to me. Allow me to answer the last part even if the question was not for me specifically.Would it be fair to say that the NFNF way would be more to suited to DB gurus, while anyone who hasn't spent a good bit of time working with and thinking about multi-update integrity issues might be better off using the ACID DB approach (at least, until update performance is actually proven to be an issue)?Transactions are tricky anyway you look at them. Developers not thinking about integrity will end messing up their transactions and leaving things in mid-air. Automatic transactions in a middleware layer (e.g. web services) help, but then those web services have to be designed to encompass atomic transactions. I have seen this situation far too often even in large banks, where system failures resulted in thousands of unfinished transactions that had to be repaired by hand. Also, modern databases force everything to be in a transaction, which is horrible to performance. Why force developers to lock tables when sometimes there is no need to? I almost prefer that transactions have to be done explicitly so that developers don't have the lazy option of letting the framework do them. On the other hand, NoSQL databases make transactions too hard to do properly. I miss some kind of system where transactions are not mandatory, have to be done explicitly, and can be activated easily.
Cool new Free software Posted Dec 20, 2012 12:20 UTC (Thu) by andresfreund (subscriber, #69562) [Link]
> Transactions are tricky anyway you look at them. Developers not thinking about integrity will end messing up their transactions and leaving things in mid-air. Automatic transactions in a middleware layer (e.g. web services) help, but then those web services have to be designed to encompass atomic transactions. I have seen this situation far too often even in large banks, where system failures resulted in thousands of unfinished transactions that had to be repaired by hand.
Transactions in the usual database system only persist across session end if they are started as two phase commit transactions. Which you only use if *have* to because you need to coordinate critical changes between distributed systems where divergence is not an option (NB: 2PC != 2PL).
> Also, modern databases force everything to be in a transaction, which is horrible to performance. Why force developers to lock tables when sometimes there is no need to?
Uhm. a) Most RDBMS's allow you to have transactions that automatically only span a single statement b) Why do transactions, unless used together with synchronous commits, imply being horribly slow. c) What does that have to do with locking tables? No sensible database forces you to exlusively lock a table to make modifications to the contents of said table. Most require some lock that prevents it from being ALTERed or DROPed, but thats something different.
Just some trivial number towards b), on my workstation from 2009, with other stuff going on, in an absolutely unoptimized configuration, PG does 320k readonly transactions per sec and 140k trivial writing ones. With a data-loss window of 1/3s. Now, I'll freely admit that the latter number only is that high because I didn't create an index but even after that its still 90k.
Now, I know from experience that there are scenarios where RDBMS's don't work very well, but - sorry to be harsh here - I don't see too many people making those 'RDBMS/ACID/whatever sucks' arguments here have actual experience with that kind of workloads. Also note that most shops using fancy KV stores and such *also* use relational databases for parts of their data. Its simply a question of the right tool for the right job.
Disclaimer: I do work with/on/arround PG, so I am certainly biased. But some other RDBMS's are certainly able to produce similar numbers.
Disclaimer-2: Yes, those numbers are not realistic for any real-world workload.
Cool new Free software Posted Dec 20, 2012 15:15 UTC (Thu) by nix (subscriber, #2304) [Link] on my workstation from 2009, with other stuff going on, in an absolutely unoptimized configuration, PG does 320k readonly transactions per sec and 140k trivial writing ones.How? With an absolutely unoptimized default configuration, each commit involves a sync, which involves waiting for the disk. There's no way you can do more than a few hundred such transactions per second unless you have a battery-backed RAID array so you don't need to wait for on-the-average half a disk rotation to bring the data under the head (and then possibly another avg half rotation for associated metadata). Doing 320,000 transactions per second with PostgreSQL is hard to imagine. I am obviously missing something. (Again. It's not my week...)
Cool new Free software Posted Dec 20, 2012 15:52 UTC (Thu) by andresfreund (subscriber, #69562) [Link]
> How? With an absolutely unoptimized default configuration, each commit involves a sync, which involves waiting for the disk.
unoptimized != default. It was just the system wide installation I had lying around, it sure has some adjustments, just not optimized for anything. I don't know from the top of my head which ones exactly, I can look it up though if you want.
> There's no way you can do more than a few hundred such transactions per second unless you have a battery-backed RAID array so you don't need to wait for on-the-average half a disk rotation to bring the data under the head
Yes, I had synchronous_commit turned off (I do that on all my development systems) thats why I wrote that I had a 1/3 second window of data-loss. Thats ok enough in many scenarios.
You btw. can get into the lower thousands on a single spindle these days (since 9.2) with synchronous commit as well, due to the way WAL flushes are batched/grouped across different sessions. Requires quite a bit of concurrency though.
> (and then possibly another avg half rotation for associated metadata)
PG writes the WAL sequentially into pre-allocated files to avoid that...
> Doing 320,000 transactions per second with PostgreSQL is hard to imagine.
Those were the read only ones. 320k writing transactions to logged (i.e crash-safe) tables is probably impossible independent from the hardware right now due to the locking around the WAL.
Sorry, should have given a bit more context, but it really just was 5 minutes of benchmarking without any seriousness to it.
Cool new Free software Posted Dec 20, 2012 23:23 UTC (Thu) by man_ls (subscriber, #15091) [Link] So there is your "completely unoptimized" database: a heavily customized installation by an expert. Oh, and don't use INSERTs like you were told, just COPYs. And a schemaless specific datatype. And hope that performance is still good... At that point you might as well use a data store which at least has been designed with that scenario in mind, and which any idiot (e.g. me) can use to do thousands of writes per second -- out of the box.
Cool new Free software Posted Dec 21, 2012 0:15 UTC (Fri) by andresfreund (subscriber, #69562) [Link]
Wait what? "Heavily customized"? I now checked and I changed 3 performance critical parameters (and loads of logging/debugging ones, but those don't increase performance).
1) synchronous_commit = off. Is it ok to loose the newest (0.2 - 0.6s) transactions in a crash. Older transactions are still guaranteed to be safe. Thats a choice *YOU* have to make, it really depends the data youre storing (and can be toggled per session & transaction). Obviously things are going to be slower if you require synchronous commits.
2) shared_buffers = 512MB. How much memory can be used to buffer reads/writes. Would I have optimized I would have probably set it to 4-6GB.
3) checkpoint_segments = 40. How much disk can we use for the transaction log. Would I have optimized for benchmarking/write heavy it would have been set up to 300.
I don't know how those could be determined automatically. They depend on what the machine is used for.
And I used plain INSERT & SELECT, no COPY.
Cool new Free software Posted Dec 20, 2012 19:21 UTC (Thu) by raven667 (subscriber, #5198) [Link]
> Now, I know from experience that there are scenarios where RDBMS's don't work very well, but - sorry to be harsh here - I don't see too many people making those 'RDBMS/ACID/whatever sucks' arguments here have actual experience with that kind of workloads. Also note that most shops using fancy KV stores and such *also* use relational databases for parts of their data. Its simply a question of the right tool for the right job.
One thing I see from people who do a lot of work with Postgres and understand it very well is that there is a lot of flexibility on the data structures used and methods to access those data structures that have been optimized by developers competent in the math over the course of decades. So whatever algorithms are used to drive these NoSQL databases and make them fast are probably already implemented as good or better in Postgres, but it does take a lot of familiarity with how the data is stored to make use of this fact. You also have the ability to make plugins to add new data types and storage methods for whatever use case you need.
I guess to distill the point is that many of the techniques which make NoSQL databases fast can also be used with SQL databases if you know how, and you get the benefit of the decades of research and development that has gone into the traditional SQL engines.
a lot of familiarity with how the data is stored Posted Dec 20, 2012 22:23 UTC (Thu) by Wol (guest, #4433) [Link]
:-)
Which is EXPLICITLY FORBIDDEN by the relational definition!
Exactly my point about Postgres taking a lot of ideas from the NoSQL crowd.
In Pick, I know *exactly* how my data is stored, because I told the database how to do it. Which is why Pick databases can be a mess because it's not properly normalised :-( The programmer needs to normalise his FILEs properly or you can lose the benefits. I've seen too many badly normalised FILEs that are a pain to work around.
Cheers,
Cool new Free software Posted Dec 20, 2012 13:43 UTC (Thu) by paulj (subscriber, #341) [Link]
At least for me, I'm not at all an expert in how to do transactions. If I wanted to store data, and have it remain self-consistent with some schema, wouldn't it be easiest for me to start with using a DB that already provided consistency-features? It could be that it will be fast enough for my purposes, no? Wouldn't it be premature optimisation for me to go with a system that provided more performance by not providing consistency? While a good developer who's been around data-bases and relational data storage a good while may know how to implement consistency themselves, what are the chances of arbitrary developers being able to better implement consistency themselves than the database developers?
Unless of course the argument is that often the performance-impacting consistency guarantees are not required. But then, aren't ACID DBs tweakable? You can make, e.g., PostGres play a lot more loose with data than it does by default, I gather, and gain a lot of performance.
?
Cool new Free software Posted Dec 24, 2012 9:58 UTC (Mon) by man_ls (subscriber, #15091) [Link] There is a subtle difference between premature optimization and sensible design. Premature optimization tends to be low level, while sensible design is more of a big picture thing. You cannot commit all your data and your code to live in PostgreSQL and one day migrate magically to Riak; or go back and forth as you need it. Even migrating between similar databases is an error prone task, and JDBC and similar libraries that try to abstract the underlying database are just an excuse for managers. So changing databases is not an "optimization", and therefore it cannot be premature.NoSQL stores usually allow you to use different schemas on the same table. Isn't it better to enforce a single schema? It depends; after all you will have to make sure that your data have the correct format before writing to the store (or after reading from it), so having the database reject your data is not a substitute for thorough testing. Also, having a single schema goes directly against reversible DevOps, as it entails offline data migration. Not everyone can afford downtime to migrate data between schemas. As to consistency, the advantage of NoSQL stores is that they allow you to choose the degree of consistency that you need. You can either read everything from many different "tables" or you can just store every piece of data multiple times. With relational databases you can also denormalize data, but they are usually less flexible as to how it is stored. (It is harder e.g. to store an array inside a table, you can just store the first n items.) If you need total consistency, then by all means go to a relational store since it will give you better guarantees. But consistency is again not a magic pixie dust you can sprinkle on your data; it has to be there from the start. Same with transactions: if you need them, go to a transactional store. But first think if you need them, and if you do then design them properly. Do not just trust your store to do the right thing because there are 100 ways to mess it up. You can try to use PostgreSQL as a NoSQL store, but you will be swimming upstream for the rest of your career. How do you share the load, or replicate between nodes? How do you deal with consistency if you need it in a non-relational table? How do you optimize a single database for both consistency and lack of it?
Cool new Free software Posted Dec 25, 2012 5:26 UTC (Tue) by raven667 (subscriber, #5198) [Link]
> You can try to use PostgreSQL as a NoSQL store, but you will be swimming upstream for the rest of your career. How do you share the load, or replicate between nodes? How do you deal with consistency if you need it in a non-relational table? How do you optimize a single database for both consistency and lack of it?
There are certainly specific use cases that the various NoSQL databases were designed for and are optimized for, if you have one of those cases then by all means use the right tool for the job, but there seem to be a lot of inexperienced developers who think that traditional SQL databases are slow creaking dinosaurs, unsuitable for any purpose when in fact they contain highly optimized data stores which have been developed over decades by people competent in the relevant math and computer science. Different does not always mean better.
Cool new Free software Posted Dec 25, 2012 17:38 UTC (Tue) by man_ls (subscriber, #15091) [Link] Well, designing a relational database is not trivial: modeling n-m relationships can be challenging for beginners. On the other hand, using a document store like MongoDB is trivial until you get to the serious stuff -- or to transactions. No wonder it is the most popular of the NoSQL bunch.Other NoSQL families, like key-value (Dynamo-like) or graph databases, are more specialized and need more effort to keep going. But I would argue that none are near the level of sophistication of a normalized database. Most of you probably don't feel it because you have worked with SQL for many years, but it is a contorted language that uses a highly unnatural data model. Yes, relational databases are highly optimized, but you pay the price every time you read or write anything to them.
Cool new Free software Posted Dec 25, 2012 17:56 UTC (Tue) by Wol (guest, #4433) [Link]
Which is why it sounds like Pick is a good fit for people who understand both relational and NoSQL.
Your "contorted language" is spot on. Basically, in relational you cannot STORE that most natural of data concepts, the list. You have to MODEL it. At which point your database becomes unnecessarily complex and complicated.
Referring back to the comment in your earlier post about "You can either read everything from many different "tables" or you can just store every piece of data multiple times. With relational databases you can also denormalize data, but they are usually less flexible as to how it is stored."
But that's exactly what Pick does! NFNF is what relational purists would call "denormalised". The fact that it can be mechanically normalised by the DB seems to have passed them by. So in Pick, I don't "read from many tables OR store the data many times", I just store the data ONCE in ONE table. imho it is *relational* that needs to "store the data many times in many tables" - just try to store a list, then ask yourself how many times you have to store the name of the list (or its id, the same thing...) As a system that requires (as part of its definition) that you don't store duplicate data, the relational system is very poor at living up to its own definition!
Cheers,
Cool new Free software Posted Jan 4, 2013 16:56 UTC (Fri) by nix (subscriber, #2304) [Link]
I might note that you can in fact store lists in PostgreSQL. It's had arrays since before it was called PostgreSQL. :)
Cool new Free software Posted Dec 20, 2012 16:20 UTC (Thu) by Wol (guest, #4433) [Link]
Replying to everyone here, but ...
First of all, what's wrong with an ACID NFNF database? ACID has ABSOLUTELY NOTHING to do with relational. Relational (both FNF and NFNF) is mathematical theory. ACID is engineering reality. If I execute my relational database program, my database will reflect that EXACTLY. Any errors are down to a buggy program. If I *run* my database program on a computer, however, reality may intervene and things will go wrogn. ACID is meant to address the vagaries of the real world. One only has to follow what's going on with linux file systems to know that integrity and ACID is very important there!!!
The thing is, because an atomic update in a relational database is a row (indeed, it may even be smaller than that), ACID is extremely important to a relational engine because there are so many points at which failure could corrupt the database. In a Pick database, an atomic update is an object - a complete view - so Pick can *safely* rely on the file system to a far greater extent.
As for NFNF being for database gurus, there's a reason most Pick applications were written by *USERS* - it's actually a lot easier for users than relational FNF, while people who've been indoctrinated in FNF can't get their head round it :-) Plus, for FNF people, it lacks large chunks of what they consider necessary, BECAUSE FNF needs those things to get engineering weaknesses.
Things like integrity, for example. I divide integrity into "strong" and "weak". Attributes that refer to a single instance of an object are strongly related. By storing them in a single RECORD, Pick doesn't need integrity checking. But relational needs to store them across multiple tables and rows, so integrity checking is paramount. Weak integrity (the relationship between two objects) is important to both FNF and NFNF (but, because NFNF doesn't have strong integrity checking, it doesn't tend to have weak checking either).
This is where transactions come in, and MODERN Pick databases have it. But many old applications don't. The nice thing with Pick is I can lock all the *objects* I want to update, then update them in a transaction. So (unless I overflow the lock table) I haven't had to lock an entire table to do a complicated transaction. I've offloaded integrity to the db engine, as you wanted, but because each *object* is an atom, the complexity at the db level is far less, and the chance of a transaction failure is far less, because I'm updating far fewer rows in far fewer tables, for the same change to the db.
Picking up on andresfreund here - Pick does NOT have transactions across a single statement. Why? It doesn't need it! A single statement is always atomic, and ACID will be enforced by the OS. The db engine can ignore the problem. That's my point above, about Pick lacking a lot of "necessities" because the Pick model makes them unnecessary.
As for "no experience of workloads", you did pick up on my comment about a P90? That took 5 mins to run its query (db == UniVerse). The replacement system - the twin Xeon - managed to run the query in 4.5 mins after six months hard work tweaking and optimising (db == Oracle). I guessed (and gather I'm right) that the company in question was William Hill, one of the biggest bookies in the UK.
Cheers,
|
|||||||||||||