Mongodb – Unable to add collumn in mongo

Some thoughts....

Typically one does not want to store pieces of tightly interrelated information in different systems. The chances of things getting out of sync is significant and now instead of one problem on your hands you have two. One thing you can do with Mongo though is use it to pipeline your data in or data out. My preference is to keep everything in PostgreSQL to the extent this is possible. However, I would note that doing so really requires expert knowledge of PostgreSQL programming and is not for shops unwilling to dedicate to using advanced features. I see a somewhat different set of options than you do. Since my preference is not something I see listed I will give it to you.

You can probably separate your metadata into common data, data required for classes, and document data. In this regard you would have a general catalog table with the basic common information plus one table per class. In this table you would have an hstore, json, or xml field which would store the rest of the data along with columns where you are storing data that must be constrained significantly. This would reduce what you need to put in these tables per class, but would allow you to leverage constraints however you like. The three options have different issues and are worth considering separately:

hstore is relatively limited but also used by a lot of people. It isn't extremely new but it only is a key/value store, and is incapable of nested data structures, unlike json and xml.

json is quite new and doesn't really do a lot right now. This doesn't mean you can't do a lot with it, but you aren't going to do a lot out of the box. If you do you can expect to do a significant amount of programming, probably in plv8js or, if you want to stick with older environments, plperlu or plpython. json is better supported in 9.3 though at least in current development snapshots, so when that version is released things will get better.

xml is the best supported of the three, with the most features, and the longest support history. Then again, it is XML.....

However if you do decide to go with Mongo and PostgreSQL together, note that PostgreSQL supports 2 phase commit meaning you can run the write operations, then issue PREPARE TRANSACTION and if this succeeds do your atomic writes in Mongo. If that succeeds you can then COMMIT in PostgreSQL.

You have answered some of your own questions here, specifically you have a decent idea about the write lock aspect of the equation - 12,000 insert/sec gets you to ~60% write lock. That's a reasonable level to get consistent performance - you will be getting some contention, and some ops will be a little slower, but you really want to start worrying at about 80% - like a lot of things, when you start exceeding 80% available capacity you will start hitting issues a lot more often.

In terms of other bottlenecks, and specifically how quickly you can write to disk - this can cause problems, but to look at the relevant stats over time I would recommend getting MMS installed with the munin-node plugin to give you hardware and IO stats in addition to the MongoDB stats.

When you have that, the metrics you will want to keep an eye on are:

• The Average Flush time (this is how long MongoDB's periodic sync to disk is taking)
• The IOStats in the hardware tab (IOWait in particular)
• Page Faults (if your disk is busy with writes and you need to read data, they are going to be competing for a scarce resource)

It's a bit complicated then, but here's a basic idea:

• When average flush time starts to increase, be worried
• If it gets into the multiple second range, you are probably on the limit (though this depends on the volume of data written and the disk speed)
• If it approaches 60 seconds you will see performance degrade severely (the flush happens every 60 seconds, so they would essentially be queuing up)
• High IOWait is going to hinder performance too, especially if you have to read from disk at any point
• Hence looking at page fault levels will also be important

The other piece of this puzzle, which we have not mentioned yet, is the journal. That will be persisting data to disk as well (by default every 100ms) and so it will be adding to the disk's load if it is on the same volume. Hence if you are seeing high disk utilization, then moving the journal off to another disk would be a good idea.

There are no real "magic numbers" to stay under, in most cases it's all relative, so get a good baseline for your normal traffic, check to see if things are trending up and maybe load test to see what your limits are and when things start to degrade and you will be in good shape.

After all that pre-amble, on to some of your questions:

What happens if there are more inserts per second than mongod is able to save to the hard disk? Will there be any warning or will it simply fail silently?

If you start to stress the disk to the levels described above, eventually everything is going to slow down and at some point (and this will depend on time outs, how beefy your hardware is, how you handle exceptions) your writes will fail - if you are using a recent version of pymongo then you will be using safe writes by default and those will then fail. If you wish to be a little more paranoid, you can occasionally do a write concern of j:true which will wait to return OK until the write has made it to the journal (i.e. on disk). This will, of course, be slower than a normal safe write, but it will be an immediate indication of disk capacity related issues, and you could use it to block/queue other operations and essentially act as a throttle to prevent your database from being overwhelmed.

I am thinking about a simple replication setup using one master and one slave. Does the initial sync or a resync process lock the databases?

I think I covered locking overall at the start, but to answer this piece specifically: First, make sure you are using a replica set, not master/slave. The master/slave implementation is deprecated and not recommended for use in general. As for the initial sync will add some load to the primary in terms of reads, but not in terms of writes, so you should be fine in terms of locking.

What happens to my data if the write queue increases on long term?

As you can probably tell from the explanation above, the answer is very much dependent on how you write your application, how you choose to have your writes acknowledged and how much capacity you have available. You can, essentially, be as safe as you wish when it comes to writing to disk on MongoDB, but there is a performance trade off, as mentioned with the j:true discussion above.

Generally, you want to figure out your limiting factor - be it locking, disk speed etc. and then track the levels over time and scale out (sharding) or up (better hardware) before you hit a hard limit and see performance problems.

One last thing, db.serverStatus().writeBacksQueued is actually a metric that will only ever be non-zero in a sharded environment, and it has to do with making sure that writes to a chunk during a migration are dealt with appropriately (handled by the writeback listener). Hence it essentially is a red herring here - nothing to do with general write volume.