Last year I asked this question (http://news.ycombinator.com/item?id=1411937) and I think it was useful to a bunch of people. Figured it's worth asking again and the diffs will be interesting.
There's nothing quite like sending a DB as an email attachment.
Also, it's awesome.
https://docs.djangoproject.com/en/dev/ref/django-admin/?from...
It's like public storage where you have to sift through everyone else's crap to get to yours, every item is stored in a bulk cargo box and only one customer gets to store their stuff at a time.
If you're interested in db internals, here's a few algorithms that MySQL uses. Note this doesn't cover InnoDB which performs far better under high concurrency loads and offers row level locking, clustered indexes, an excellent caching algorithm, foreign key constraints with cascade, etc..etc...
Something like
user_id=int(request.GET['id'])
data_type=int(request.GET['id'])
json_file="{0}-{1}-chart_data.json".format(user_id,data_type)
#get the file, decode, etc.
Thanks, I'll check out the MySQL thing, but I'm not actually intending to build my own database.
Sarcasm doesn't aid in making points online because people who don't know anything about the issue at hand make sarcastic 'points' as easily as an expert.
So I meant that instead of using SQLLite on mobile devices, pc based local fat clients or in the browser... I now use JSON now whereas I might have used SQLLite before [ I also use JSON where I might have used XML or windows config files etc ]
I did not intend to say JSON is replacing MySQL / PostgreSQL.
But yeah, JSON is great for small amounts of data.
This means I can reuse the same REST style data 'provider' from within a Javascript web UI and within an iOS or Android client app.
So theres quite a few reasons why using JSON simplifies things for me - particularly I notice that there is a whole layer or ORM style boilerplate code that I dont need now [ whether its SQLLite <-> Object or XML <--> Object ].
I actually like the SQL Lite implementation!
** The author disclaims copyright to this source code. In place of
** a legal notice, here is a blessing:
**
** May you do good and not evil.
** May you find forgiveness for yourself and forgive others.
** May you share freely, never taking more than you give.
I've used mysql a lot, and pgsql a little. I can't tell the difference, other than pgsql being slower and having less support. Some people swear by it, so I'm curious what I am missing.
The Percona team said that Oracle has a lot of customers who are using Oracle RDBMS for big stuff and MySQL for smaller stuff, and they like being able to sell support for both, but they don't see MySQL as competing with their flagship DB. He also said that Oracle has so far kept their promise to keep developing MySQL and that Percona has pulled their upstream changes.
Besides that, if Oracle decides to stop supporting MySQL, Percona is a drop-in replacement, as is MariaDB, developed by some of the original MySQL developers. So you won't get stuck.
All of that said, however, I'm interested in PostgreSQL just because I've heard that it's a well-made database. There's nothing wrong with switching for other reasons; I just don't think that uncertainty about MySQL's future is a good reason right now.
Historically, PostgreSQL has been considerably more SQL-feature-rich than MySQL. It still is, yet to a lower extent (see previous paragraph) due to MySQL caching up. For example, MySQL only has views, triggers and stored procedures since version 5, and Postgre had them since at least 7 or 8 which were already mature at the time MySQL 5 was still development. I guess a lot of people became adepts at that time.
Disclaimer: I don't think of myself as a database guy, and I couldn't give you a definitive list of the differences. I remember some times in the past when things like transactions, triggers, and stored procedures were on the list of differences the pgsql fans quoted to me.
Subqueries, but we don't require this in production.
Wariness of Oracle's conflict of interest.
Worse still, subqueries in the FROM clause are (documented to be) implemented as an unindexed temp table.
Google apparently compiles subquery support out of their mysql instances so people don't mistakenly think they're usable.
In performance terms, granted, pg was a bit of a dog in those days, but it outperformed MySQL in every benchmark I could throw at it, with minimal tuning. So, if you're experiencing it as less performant, you're most likely running with a default configuration — which is deliberately tuned for something like a 486-class box with 128MB RAM or so — and/or haven't ANALYZEd your data. (Those are complete WAGs, knowing nothing more than what you've said about your scenario, but they tend to be among the more common reasons for lackluster performance.)
As for support, as a complete noob, I had a weird performance problem I couldn't make sense of, so I went to the mailing lists. Within a few hours, I was exchanging stack traces and other sundry debugging/profiling dumps with Tom Lane. (He's in the Wikipedia. Even if you've never touched PostgreSQL, you use his code every day of your life.) I don't think you can get much better support than that. Since, I've never encountered a problem that I haven't been able to have addressed, or at least get pointed in the right direction, by asking on — or searching the archives of — the relevant mailing list.
From there, it was largely a matter of, "This is the one I already know how to use...", along with the better feature-set (not mentioned by any of the sibling posts thus far: transactional DDL); the lack of a known-evil corporate overlord who could pull the plug at any time; the consistent tens-of-percent performance improvements in every major release; a development community that will punt a feature to the next release if it's not 100% ready and provably correct; and, let's be honest, the fact that, as someone who's been doing pg work for as long as I have, I can command a very comfortable hourly rate on the basis of that depth of experience — particularly when it's been focused in high availability and replication.
(EDIT: proofreading.)
It also didn't support transactions where postgres did. Essentially mysql was a dumb datastore with sql interface, whereas postgres was a database.
That's changed now, but postgres is still a head in reliability(shit just doesn't break) and feature-set. and I hear in speed these days. But I no longer care about speed these days, as an SSD backed postgres handles anything I can possibly throw at it.
At present, it looks like the best choice is the forthcoming Intel 710 series drives, but if you need an SSD now, their 320 series, the Sandforce controller based drives with supercaps (like the OCZ Vertex Pro models, though I wouldn't touch those with a competitor's database), or a FusionIO card are the only remotely safe options.
Thanks.
The benefit that has in terms of increased concurrency is worth far, far more than having to implement a workaround for quick-and-dirty row counts for simple things like pagination, IMO.
(Aside: InnoDB is also MVCC-based, which is why its COUNT(*) is an estimate. The MySQL folks apparently decided that it was better to provide an estimate than an exact count, while the PostgreSQL folks decided the other way. There's a part of me that wants to call that symbolic of the way the two projects operate on a much broader level...)
That said, I'm generally more frustrated when using MySQL than when using PG. Here's a sample of the problems I've encountered from using both MySQL and PG. I haven't updated my list in a while now - please feel free to correct me on things - but hopefully it's a little more illustrative than that Wikipedia feature matrix, and a little more specific to MySQL vs. PG. (This list is a cleaned-up selection from my notes wiki at http://yz.mit.edu/notes/Hackery.)
No referential integrity.
No constraints (CHECK).
No sort merge join, let alone hash-join. http://www.dbms2.com/2008/07/10/how-is-mysqls-join-performan..., http://www.mysqlperformanceblog.com/2006/06/09/why-mysql-cou...
Generally poor at analytical workloads, since it's designed for transactional workloads.
Can't reopen TEMP table - WTF? (Still not fixed!) http://bugs.mysql.com/bug.php?id=10327
Multiple storage engines has always restricted progress: http://www.mysqlperformanceblog.com/2010/05/08/the-doom-of-m... (PG also supported multiple storage engines in 80s, then concentrated on one)
No WITH clause: http://stackoverflow.com/questions/324935/mysql-with-clause
Crappy errors: “Incorrect key file for table ‘stock’; try to repair it” on “alter table stock add constraint pk_stock primary key (s_w_id, s_i_id);” where stock is in InnoDB (which has no “repair table”) means I have no /tmp space (no Google answers)
Crappy EXPLAIN output - somewhat better when using the visual-explain tool from Percona.
InnoDB auto-extends ibdata1 file; only way to trim (garbage collect) is dumping and loading.
Scoping is broken:
mysql> create table t(a int, b int); Query OK, 0 rows affected (3.30 sec)
mysql> select a, (select count(*) from (select b from t where a = u.a group by b) v) from t u;
ERROR 1054 (42S22): Unknown column ‘u.a’ in ‘where clause’
“InnoDB is still broken…Just last week we had to drop/re-create an InnoDB-table in one project because it would not allow to add an index anymore, no matter what we tried…Mysql::Error: Incorrect key file for table 'foo'; try to repair it: CREATE INDEX [...]” http://news.ycombinator.com/item?id=2176062
MySQL only recently got such things as per-statement triggers and procedural language support.
MySQL has only its own internal auth system, whereas PG supports a wide array of auth providers.
PG has more supple ALTER TABLE implementation.
MySQL doesn’t support ASC/DESC clauses for indexes http://explainextended.com/2010/11/02/mixed-ascdesc-sorting-...
Optimizer only recently started working properly with certain subqueries
OK documentation, but still considerably unpolished compared to PG's. Random omission: auto_increment jumps up to next power of 2 but inconsistently across versions (platforms?).
(Older issue, not sure if it's still relevant) Crappy concurrency, >3 cores sucks vs PG: http://spyced.blogspot.com/2006/12/benchmark-postgresql-beat...
• Subqueries are optimised as well as JOINs. I can go all-Inception in my queries and they perform well (I find subquery style often easier to understand than equivalent JOIN).
• You can do UPDATE … SELECT on the same table.
• Postgres has query rewrite (RULE) that can be used to implement writeable VIEWs (which is awesome for migrating legacy applications to new schema)
The only thing I really miss from MySQL is ON DUPLICATE KEY UPDATE. Postgres has only weak 1-row hacks emulating this, and "standard" MERGE syntax for this is horribly ugly.
Some features that make Postgres awesome:
* Transactional DDL. You can do "create table" in a transaction. _Everything_ is transactional, it's not a tacked-on feature, it's the basis of everything.
* No legacy cruft. Compared to MySQL, which is filled to the brim with historical warts. The Postgres people have been careful to weed out obsolete functionality. There are essentially no sneaky border cases that a developer needs to be aware of, no weird special cases like "0000-00-00 00:00" having special meaning.
* No need for a "strict" mode, since Postgres is always strict. Postgres doesn't allow invalid dates, doesn't allow byte sequences that violate character encodings, etc. It diligently enforces contraints and generally doesn't allow you to screw up. To Postgres, data integrity is paramount.
* PostGIS. Simply awesome. (MySQL's geospatial stuff also tries to implement the OGC API, but last I looked, it was a half-hearted attempt that negelcted to provide the fast R-tree-based (actually GiST-based) indexing that makes PostGIS so super fast.)
* Replication. It's late to the party, but I rather prefer how Postgres has implemented its replication, even though it has some downsides where it will abort a long-running query if some data has changed under its feet (but if you're using transactions it's easy to simply restart the query). 9.1 will be getting synchronous replication, which is pretty cool.
* Extensions. Postgres can integrate languages like R and Ruby as first-class languages that can be called from SQL. It also has a module system that can extend the type system (a bit of trivia: This was originally the main reason why Michael Stonebraker invented Postgres) with new types, eg. multidimensional matrix columns, or new features, like remote tables.
* The "text" type. Seriously, why should do people keep writing things like varchar(255)? Postgres' text type is an unlimited string. Unlike MySQL's text type, it can be efficiently indexed without limitations. (Varchar is internally implemented as a bounded text type.)
* Cost-based planner backed by row-level statistics. This is the stuff that allows Postgres to do complex nested queries and still perform incredibly well.
* Partial indexes. You can do something like "create index ... on themes (name) where color = 'blue'". Whenever you do a query that falls within the expressions's range, Postgres will use that index, potentially vastly reducing the search space.
* Functional indexes. You can do something like "create index ... on (lower(name))". If you then do a query such as "select ... where lower(name) = 'xyz'", then Postgres will recognize that it's the same expression, and it will be able to use the index.
* Windowing functions and recursive queries, both from ANSI SQL99 iirc. Look this up, they're great.
There are some bad points, none of them significant and all of them a matter of taste:
- I have never really liked Postgres' text indexing, which feels a bit creaky and antique. At least with 8.x, GIN index updating was slow as hell.
- Partitioned tables are a great feature, but I will never use it because of the requirement that one does the plumbing yourself (creating partitioning rules and so on); I keep waiting for something like Oracles's automatic partitioning.
- Stored procedures -- ie., running logic inside the database -- feels wrong to me, and always has. For some people this is a requirement, so I'm not really complaining. In some cases, writing a stored procedure can be essential to speed up queries/operations by saving on database roundtrips.
- Still no "upsert" SQL command (aka "insert or replace", "insert or update") for asserting the existence of a row atomically.
Graphs are a much more modern and elegant way of storing relational data. I've used Postgres for over 10 years, but it's not a graph database. With graph databases you don't have to mess with tables or joins -- everything is implicitly joined.
And Neo4j is ridiculously sweet -- store 32 billion nodes (http://blog.neo4j.org/2011/03/neo4j-13-abisko-lampa-m04-size...) with 2 million traversals per second (http://www.infoq.com/news/2010/02/neo4j-10), and you can use Gremlin with it (the graph traversal language), which let's you calculate PageRank in 2 lines.
Neo4j is open source, and the Community Edition is now free (https://github.com/neo4j/community).
I recommend pairing it with the TinkerPop stack (http://www.tinkerpop.com/).
There is also a Python open-source Web development framework for graph databases called Bulbflow that is based on Bulbs and Flask.
Both frameworks should be released in the next few weeks.
Actually, storing data as graphs is older than relational approaches. It used to be called "network databases". They were not supplanted for the hell of it, relational databases have certain advantages.
For select applications, object databases are absolutely the way to go. But for most purposes relational is hard to beat.
The graph-database model simplifies much of this and makes working with the modern-day social graph so much cleaner.
Graphs allow you to do powerful things like find inferences inside the data in ways that would be hard to do with relational databases.
How would you calculate PageRank using a relational database? As I said, with a graph database and Gremlin, you can do it in 2 lines.
To see the types of things you can do with graphs, check out Marko's short screencast on Gremlin (http://www.youtube.com/watch?v=5wpTtEBK4-E).
And also check out Peter Neubauer's introduction to graph databases and how they compare to RDBMS' and where they stand in the NOSQL-movement (http://www.infoq.com/articles/graph-nosql-neo4j).
The mismatch comes about for a number of reasons:
* OOP has no formal basis so it can't be reliably transformed into relational terms.
* OOP is identity-bound -- each object is essentially
an address in memory, not a relation in a set.
* The biggie: OOP mixes data with behaviour.
Relational does not.
For something on PageRank's scale, a custom datastore based on matrices and their multiplication makes business sense. Or MapReduce over a distributed key-value store (note that these are both OLAP approaches).
Still. SQL's a bit verbose, but these days we have recursive queries. For Oracle users, I'm talking about CONNECT BY. If I find myself running the social graph every minute, I develop an ETL package that periodically moves data from my write-bound system to my read-bound system with a more query-friendly schema. Depending on how you look on it, relational systems invented "eventual consistency".
More to the point, boring old database greybeards have learnt that OLTP and OLAP are very different use cases. The 3/4/5NF of the OLTP database will be very different from the star schema of the OLAP database.
There's really not much about NoSQL that hasn't already been done, under a different name, by the relational crowd.
I still think there is a place for NoSQL. It's just not as universal a replacement for relational systems as many make it out to be, whether we're talking about document stores, distributed key-value stores, graph stores and so on.
Putting relational databases into their own group is silly. I believe this came about because relational databases have dominated for so long, and this has caused developers to try and fit every problem into the relational database model.
In the early days of the Web, choosing a database meant choosing between relational database management systems -- Oracle was king but expensive, Microsoft SQL Server if you were on a Microsoft stack, and PostgreSQL or MySQL were the primary open-source options (with one being a real RDBMS while the other was basically just an SQL interface to the file system).
Ten years ago the RDBMS was the only good option so that's what everyone used even though many problems and programming languages didn't match up well with it -- it was like trying to fit a square peg in a round hole.
You don't have to do that anymore so relational databases can stop being the one-size-fits-all solution, and instead we can move toward using them for the specialized cases where they're the right fit.
This is somewhat different than a general graph database (http://en.wikipedia.org/wiki/Graph_database) where nodes are not restricted to being a hierarchy.
"The more of an IT flavor the job descriptions had, the less dangerous was the company. The safest kind were the ones that wanted Oracle experience. You never had to worry about those."
I would say all these databases are worthwhile as long as the DBA is competent. Given that, unless you have a reason to choose Oracle (e.g. familiarity, integration with other applications, a specific Oracle only feature, contractual obligation), I see no reason to choose Oracle. Many companies choose Oracle because Oracle built a solid database/brand early on while catering to big businesses. MySQL and Postgres made great progress in the past decade make them valid alternatives.
We use Oracle, MS SQL Server, and Sybase because that's what the vendorware we use requires. We do have some open-source software that uses MySQL for administrative purposes.
Above all, are you helping a customer? solve his problems instead of compounding it - that my friend is more important than choosing Oracle or Mongodb.
Having Oracle as your db doesn't mean that you
are not working on interesting problems
But if your company chooses Oracle, it means that you're bogged down by legacy, stupid company processes and/or clueless managers.
And in most such environments, the harder you try to change it for the better, the harder it fights back, putting yourself in an awkward position in which you are considered the bad apple of the team. So you end up either adapting (not giving a shit), quitting or finding some small project with no perspective for the company (i.e. less controlled) that can bring you pleasure.
Of course, some companies, like Adobe for example, use Oracle when it doesn't impact their core competencies, as it's a safe choice for corporate types. But a company like Adobe doesn't earn money from projects that are relying on Oracle and other projects inside Adobe are also using HBase and MySql and their own distributed file-system that can be queried and so forth.
Either way that quote is correct. I'm not promoting the latest fads (personally I'm not into NoSql unless it makes absolute sense), but you can safely ignore companies that make decisions based on brochures and lap dances.
I don't understand how using Oracle for your RDBMS is suddenly equated with all the things you mention.
http://www.google.com/intl/en/jobs/uslocations/mountain-view...
[If 12 dimensional hierarchical databases with complex logic are your thing then Hyperion is really rather cool in a perverse kind of way].
BTW If anyone here is interested in this kind of space I have had some interesting experiences in building extensions to HFM...
true clustering (Oracle RAC)
multi platform
supportMy point, if all you ever need from a database is solved with key/value store then Oracle is not mildly interesting.
Forget price for a moment, any feelings you might have for Larry Ellison, the good/evil nature of the company, or whatever. Consider the software itself.
Many people aren't all that familiar with the basic things a database is supposed to do. The list is large. I can't possibly give it justice off the top of my head, but here are a few that come to mind: make it possible to see data in a consistent state (as of a single point in time), without being blocked, even as other people are changing it; support "transactions" -- a group of requests that either all succeed or all fail -- so that the database cannot end up in an inconsistent state; guarantee recoverability to a consistent state even when people change their minds, statements fails, power fails, hardware fails, or all of these things happen at the same time; and many, many other things like security, support for the relational model, SQL, joins, etc. Real databases aren’t simple key-value stores.
Historically, Oracle obtained a huge lead in market share because it delivered the most complete mix of these basic things long before anyone else.
Something not widely recognized, but that should be obvious to programmers, is that low-level architectural decisions have a huge impact on how well a database performs these basic duties. Oracle maintained its lead for a long time because it got many of the low-level architectural decisions right. It is really hard to catch Oracle if you are trying to polish up a bad locking model, for example. Oracle still does the basics better than most. That, in and of itself, is a reason to consider Oracle.
Oracle remains viable, even as others continue to catch up, because Oracle builds on its solid foundation by adding additional capability and features relentlessly. I challenge you to read the new features guide for any new release of Oracle and to remember just the names of 20% of the new features. The Oracle documentation, as of 6 or 8 years ago (10g), was 40,000 pages. No telling what it is now. I can tell you this. If you have something you need to do with databases, Oracle probably figured out how to do it a long time ago.
I, personally, am a huge fan of PostgreSQL (and it’s freeness), but I recognize that Postgres is never going to be able to touch Oracle in features. It’s impossible. Working with Postgres is just going to require a lot more manual labor. Some things aren’t going to be possible. Performance may just have to suffer sometimes. Let’s hope Postgres does the basics well (it does). That’s the most important thing. But when it comes to building spacial indexes on hierarchical dimensions, or whatever, Postgress just isn’t going to have a feature for that. I’ll have to figure that one out for myself.
One key point: if you need to build a large, high-performance, data-driven application that provides nearly instantaneous response for thousands of simultaneous users, Oracle is one option that can get the job done. If you need to build something huge, say billions of rows, that provides nearly instantaneous response to dozens of users, Oracle is one option that can get that job done. So no wonder that some companies consider Oracle.
The last thing I’ll mention is that it takes a lot of time to learn something like Oracle. I would be surprised if after a year of using it, for example, that you can really make it hum better than any of its top competitors. If you do use it for a while I think you’ll find that it is really good at the normal things and in a different league when it comes to the unusual things. This comes in handy when you are being paid to get things done (by a company that can afford Oracle) -- hence the reason that most startup-oriented people don't have much appreciation for Oracle.
Are these good things?
We can also reverse this argument: if you're using Oracle, you will never have a use for 99% of its features.
If you need Oracle, you'll know it. If you don't know that you need Oracle, you don't need Oracle.
I run MySQL, Oracle SQL Server, hundreds of databases, a couple in the 'many thousands of queries per second' range.
There are reasons for each database platform.
In this case, yes it is, Postgres probably has the best type system and extensibility of all SQL databases. GiST and GIN are very good technologies that also exist in Informix.
If I had to choose something that is missing in Postgres and very far away for sheer want of implementation effort, it's parallel execution of queries.
Oracle RAC is also quite a crazy and neat feature, except when it has problems, in which case you get even more crazy for that crazy.
On the other hand, Oracle is also not a strict superset of the features in PostgreSQL. For example, 9.1 has the only credibly fast implementation of SERIALIZABLE level isolation that I am aware of. There are also interesting features like "index exclusion constraints" that have no equivalent in Oracle, and solve problems hard to solve otherwise (example constraint: there are no overlapping circles in this table)
1. I build products on Oracle at a startup.
2. Oracle has many compelling features that really don't have first-class open source alternatives: OLAP, encryption (wire and at-rest), VPD, materialized views with query rewriting, object and document storage, monitoring and tracing, among others...
3. Oracle got more compelling since being offered--license included!--on Amazon RDS.
There are a ton of responses for Microsoft; why doesn't anyone question that? Actually, I'm curious now. Is there a new incentive for SQL Server? Maybe something in BizSpark that makes it attractive?
Sybase was better than Oracle, and Postgres was just as good as Sybase at that time. But SQL Server (version 2005 on) has stood taller than all of them in my view. Excellent management tools, stable, reliable and no performance issues for our apps. Most of our apps are write light and read heavy with thousands of users hitting the db - served off one Win 2003 server (we have a warm spare).
So there is some truth to your statement about nobody choosing to use it - for us the primary motivation to not use it internally was cost. At our customer premises they picked up the tab. We have MSDN so sql server development licenses are not an issue. Have not met a Microsoft salesperson yet!
I've never had a bad experience with it, it needs a lot less maintenance than Oracle does (or did, at least), it performs pretty well (we're an insurance company with large datasets), is integrated in a .NET stack (while you might not want to go there, especially as a startup, it is very nice for corporate work), it came with Reporting Services which we used to replace Crystal Reports, it has lots of (admittedly non-standard) extremely useful SQL functions, data types, etc. And I love SQL Server Management Studio and the other Microsoft tools.
MySQL felt like a toy database in comparison (especially the management aspect), and the non-relational stuff is out of the question for now. We might not have done due diligence by not looking at other alternatives (notably Postgre I guess) but they seem like a poor fit given our developer's strong Microsoft-centric backgrounds. Most of us also have at least one Microsoft training course in their SQL server, and they have very strong support in my country (Uruguay) against nonexistant for most other platforms.
We haven't finished our testing/profiling phase yet, so any hints on how to optimize a large number of small writes (on the order of ~dozen bytes each) would be appreciated.
When you hit a certain traffic level, scalability, latency and robustness become far more important than single-node ops/s. I need to be able to add nodes and repair failed nodes while under load--I need the 99.9% latency mark to stay ~100ms while doing so. I don't really care how many bajillions of ops a second your database can do in some concocted scenario, b/c you're not going to do that many in the real world anyway (trust me, we tried). The disk subsystem is going to give you a few hundred, maybe a few thousand if you're lucky, IOPS, then your latency will spike to hell and your phone will wake you up at night.
Maybe in the world where 99% of ops are reads, you will put up impressive numbers, but now you're just showing you are pretty good at using the disk cache. That's a relatively easy problem.
The riak guys seem to get all this better than most: http://blog.basho.com/2011/05/11/Lies-Damn-Lies-And-NoSQL/
So, to give you a short answer to your direct question:
Use SLC SSDs + md + RAID-0. Have at least 5 nodes. Use bitcask, but realize that your keys will need to fit in memory. Also, realize that really small values aren't a great fit for Riak in some ways b/c the overhead per value is at least a few hundred bytes.
Also, it's important to note this is where I'm at right now, but maybe not where you (generally) are at. Riak may not make you happy at server #1, but it will make you pretty happy at server 10 and server 100.
Riak's sweet spot is people with scaling pains. If you only need a server or two to try some stuff, and you don't have any users yet, you might cause yourself more headaches than you need. Sometimes you don't need a locomotive, you need a motorcycle.
(These guys have a pretty great motorcycle: http://rethinkdb.com/ )
We got some very frank advice from some people whose opinions on databases I take very seriously to stay away, including reports from within FB.
Having said that, I cannot claim to have firsthand proven or disproven anything about Cassandra.
At large loads and footprints, imvho, Riak, Cassandra and HBase present viable options. But there are some factors to consider that don't seem to get mentioned in the pop tech press
- What are you able to operate in production?
- What are you able/willing to debug and patch?
- What hardware options do you have?
- What are your workloads?
- Which variable of C.A.P, when you lose it, most damages your business?
- Will your company's choices be evaluated in the press?
- Does your board/investors have capital tied up in business's that are using something else?
- What architecture tradeoffs and styles sit well with you?
- What kind of data access and consumption patterns make you money?
- Can you pay for help?
The right choice is context sensitive, and I'm fairly sure for this class of systems at this point in time, there's no free lunch. That means you have to do the legwork for yourself and make your own choices and commitments; doing what you heard worked for someone else is a cargo cult.
There are a few people (YC companies even, alas) who are very vocally negative about Cassandra, but I also saw some of those same people ignoring direct advice given to them in #cassandra on IRC, and then turning around and bashing it when it didn't work as planned. Simply following the advice could have made for a completely different story.
I suppose the lesson to learn is that you need to develop software in a way that simply won't allow developers to shoot themselves in the foot, because people never want to blame themselves for doing it, they blame the gun.
The test hasn't been a "concocted scenario", it's measuring the performance[1] of a prototype implementations for what will be an essential piece of our infrastructure and process (bulk loads of large numbers of small records, very read heavy after the initial load). Riak's write performance was completely adequate, just nowhere near what we got out-of-the-box with the bulk insert operations available in Cassandra and HBase. I asked on #riak channel on freenode and got told to use protocol buffers (which we already were), I'd really appreciate advice beyond this.
> Also, realize that really small values aren't a great fit for Riak in some ways b/c the overhead per value is at least a few hundred bytes.
This is pretty much what I've chalked it up to. It's unfortunate because that is the use case for which we currently need to provide a solution for right now, and once we've got some of our data in one distributed data store, it's convenient (and considered less risky) to use that same technology for the next project. (This is really a culture thing though, it's taking us months to get the necessary buy-in and approval for a postgres 8.2 -> 9.0 upgrade rolled out for a different product, where we know it would solve a specific issue we have).
[1] We've been running our tests on a 4 node cluster, each node has an 8 core 2.8ghz xeon, 32gb of ram, and a woefully inadequate disk: the machines were repurposed from a system that required them to have redundancy and didn't require write performance, so the drives are RAID1. We also need to make recommendations to IT for their hardware purchase plan after our testing.
Btw, b/c I can totally see why you'd read it that way, that particular barb wasn't directed at you, more directed at some of the public benchmarks touted by (non-distributed) NoSQL database systems.
Re: cassandra, please see my reply to the sibling on this thread.
Also, feel free to email me jamie@bu.mp if I can answer any specific questions for you with things we ran into with various database systems.
The only improvement you could make to it would be adding some of the fancier bits that make Redis really nice, like sets and lists.
Based on merits, architecture, and implementation Riak handily beats several of the DBs listed. It's a glaring omission and not the only one. As many chose Other as did Oracle.
I tried introducing MySQL over a year ago only to have some hilarious emails with a senior programmer about how we would have to pay for MySQL. The GPL is not hard to read, but some people don't consider anything not made by Microsoft worth using. Like Linux. "No one uses Linux in the real world!" "PHP is for small personal websites!" - real quotes, sadly.
Also some people don't consider anything made by Microsoft worth using.
Perhaps your senior programmer did fall into the former category, I don't know. However, you should take care not to fall into the latter.
Like pg? "I never used Microsoft software" http://www.paulgraham.com/microsoft.html
May be it's my personal preference, I tried many clients (TOAD, DBArtisan, SQL Developer, SQL Plus etc.) and found that none was as polished (not that i'm mentioning functional) and integrates well with Windows as the one from Microsoft.
It's off topic, but the same applies for Visual Studio and other IDEs.
First class support for Postgres.
I'll leave it to you whether you call it a database :-)
Access plays well with everyone (including Oracle, the MS Office family, and Sharepoint), can be heavily extended if you know VBA and are willing to hack a bit, and can often be handed off to a non-technical person for support. The trick is to try to use the same coding/design standards that you would use in a real langauge - the internal parts of my Access/VBA work is influenced by Python and C++.
And from a consulting perspective, it has the advantage of being aligned with what 90% of your Fortune 500 end-users are familar with (MS Office)... simpler handoffs.
That being said...not my first choice for after hours work (do a lot of MySQL, looking to step up to Postgres).
Incidently (for the other thread) - one really nice thing about oracle is their statistical analysis functions - very nice set of tools, co-located with your data, and can be accessed through SQL. Only free solution I've seen that is competitive in terms of functionality is Postgres...
Just to name a few:
* SQL Server
* SQL Server Express
* SQL Compact 4
* SQL Azure
Also, go Postgres! Woot!
Fortune 500's will pay you to spit out graphs, but they don't use bleeding edge tools like Redis.
Anyone who uses Redis in any serious fashion already knows that the numbers you show on the homepage are easy to get to. Just write a script to pump them into $graphing_software.
My advice would be to give the current product away for free (yes right now), making the barrier to entry lower than doing it yourself and capturing future customers. Then focus on selling people things that are hard, like real actionable intelligence. Everything you charge for should answer a question like "what", "who", "where", "why". Free stuff should answer questions like "how many".
[Edit: Just realized you are the Hoptoad guys (of which I am a happy user), so I know you have the potential]
Also, while I love Tokyo's speed, it is just a better bdb - which is an impressive feat, but I can see why a lot of people would use something slower but more featureful.
Full disclosure: My daily job is postgres developer/consultant and I love it :)
All sorts of odd little things, SQL Server.
Various legacy data processing and newer data warehousing jobs, SAS.
New projects, in theory Oracle but there seems to be a degree of resistance. It'd be interesting to see how that pans out but I won't be around there much longer :)
MongoDB is typically introduced to optimize part of a stack, although more and more it is used as a sole/primary data store. I think ORMs and use of Mongo by prominent consulting shops helped boost adoption.
The particularities of CouchDB replication are very well suited to a enterprise application with a distributed architecture that I'm working on. I hope it sticks around for a long time, even if it doesn't have the biggest dev user base.
That is what made it stand out for us. CouchDB carved itself a nice niche in that area and currently doesn't have any viable competitors. We have a cluster that runs continuous replications to sync data around and it works great. Had we used Erlang we would have used Mnesia (the dataset and pattern of access fits it well) but we use Python & C so CouchDB works great for us.
Also wondering what future will bring and if they'll ever end up with some hybrid of Membase+Couchbase? Reliable document saving and replication + fast key value store, rolled all in one?
That being said, I do the same thing using Python.
We're using the App Engine master/slave datastore for getcloak.com. We're moving over to the HRD soon; the role of the HRD in App Engine's future wasn't clear when we started building our app.
http://neogregious.blogspot.com/2011/04/migrating-app-to-hig...
Redis has also been incredibly fast for our ad network. We throw around 7000 qps at the thing with a low number of writes and we haven't restarted the daemon for months. [Thanks Salvatore!]
After Oracle stopped distributing the source on their website, an open-source project started continuing its development here: http://www.haildb.com/
They should make the DB some SQL variant and use web technologies as their layout engine and it would be a pleasure to work with. I tried to learn Rails and find it hard to get into, though I'm lacking experience as a developer.
In the bank where I work, the banking system uses DB2 on mainframes.
Our data warehouse uses SAS.
Oh, and PostgreSQL for reporting.
... and one of the 2010 poll: http://koldfront.dk/misc/hn/database2010/data.png
(Updated every ~4 hours currently; I will be fading out the update rate.)
The two sets of data ought to be shown in the same graph, really.
Edit: I have now joined the two years in one graph, and plot the percentages to make it easy to see the change: http://koldfront.dk/misc/hn/database/data.png
On a lighter note, does this support the theory of HNers who complain that HN is not the same any more and it has been generalized?
Much of the reason for the large number is for legacy code that is quickly being replaced. We're settling into Postgres, Redis and Couch.
I'm not trying to be a wise guy - it's more of a comment that elementary schools are often last to implement technologically savvy solutions (i.e. solutions that cost a lot of money), so I wouldn't be surprised if you said "yes" to my question.
Let's say migrating a multi node 10G RAC instance - what would I lose in terms of functionality - Distributed transactions, limitations on # of replicated nodes, RAT etc.
Just curious how far it has come in terms of replacing Oracle.
Currently using memcached (not memcachedb) for caching, thinking of trying out Redis.
edit: Why am I getting downvoted for this question?
Also, we use S3 as database for some of our larger datasets where latency isn't a concern and are looking at either Mongo or HBase for large datasets that need faster performance.
For many use-cases, a full database isn't needed. And 5~10 years ago, "NoSQL" databases didn't exist: You just used a BDB as your key-value store.
It's not new and sexy, but it backs a lot of things.
However, for the first time ever, my current project uses Postgres with Oracle being pushed out of the enterprise at a fast rate.
For my personal projects it's Postgres, Redis and SQLite.
As a large enterprise I am aware of atleast one app using the following: MySQL, PostgreSQL, Microsoft, Oracle.
We also use: DB2, Informix, SQLite, and some others I don't even know about.
(not kidding, its what my work uses...)
* Informix
* MSSQL
* Oracle
* MySQL
Yes, Oracle is big, bad and "ebil" and I cannot stand their sales drones.. but if you ever had an "oh CRAP" moment, chances are very good that the db has got your back. Of course it also has its downsides like any other huge software product...
Simply saying "I use XYZ" isnt really informative unless you know under which circumstance each was chosen.
Legacy requirements? New scrappy startup with no money? High performance need? Have shared DB stores across multiple departments/apps?
etc...
Next question, there's karma here?