Pros and Cons of virtualisation technology for database-server and datastorage

Question

Yesterday we had a chat about performance and restorability and I realized how many good things a virtualisation environment can bring to me - but as I'm a little sceptic concerning performance I'm asking here. It could be a little GIS specific, but over at the gis-users they said it's to database specific... ;)

Will a database server suffer from severe performance loss through virtualisation? I don't understand the technology to the last detail, but somehow it's a 'blackbox' more, that needs to be processed through hardware. Also will disk access, with all the tricks PostGIS gives us be granted? (clustering, index etc.) - fragmented clustering is like no clustering!

The big advantage is maintenance and scalability. In case of a severe hardware malfunction I can migration within minutes or even realtime to another physical machine.

Who has experience and can point me to good websites or literature on this topic? I remember a few things from the last fossgis and a few in-house benchmarks on ESXi and native Servers and somehow I can't make up my mind whether it's good or not.

Answer 1

This is one of those "It depends" questions.

Performance depends on resources, contention, configuration, and the VM engine

Uncontended VM host: If you properly resource a VM with uncontended high performance locally-attached or SAN storage, low contention for CPU resources, no memory overcommit or contention, fast dedicated network access, etc, it'll generally perform very well on a properly tuned VM engine. Exact results will depend on the VM system used, on how you provide access to resources, and lots more.

You can get great results on high end VPS plans with guaranteed low contention ratios and good storage.

Contended/under-resourced VM host: If you put it on the same box as three other application servers and a file server, all of which share the same RAID 5 array and are fighting over RAM and CPU, it'll perform terribly.

If you put it on a cheap over-subscribed and overcomitted VPS host somewhere you'll get similarly poor results. If half your RAM is really swap on the host's disk, nothing is going to be fast.

"In the cloud": If you put it on EC2, Azure, or whatever, then performance will depend on the contention ratios for the service, the storage they're using, what other users are doing, how good their QoS is, and lots more.

At least for EC2 the disk subsystem has horrible performance (on standard VMs, at least in 2012) so it only performs OK if you have enough RAM to cache at least your indexes. Amazon has introduced new high I/O instances that might be better but I haven't seen benchmarks yet.

Usually, you'll get something in-between if you choose lightly contended hosting with decent disks, like high quality higher-end virtual private server hosts.

Direct vs VM guarantees

Re specific guarantees about things like file ordering, that depends on your VM setup. Are you using VMs backed by files? By raw block devices? By an iSCSI SAN? It also depends on how your VM engine is configured, and exactly which VM system you're using.

Best case

In the best case - a system with all-paravirt drivers, VT-x, VT-IO, uncontended access to host resources, etc - you'll probably get performance pretty close to the host. If you give the VM direct block devices not host files for storage then you'll get proper file ordering without host-side fragmentation. Exactly how close will depend on your particular hardware, host and guest, configuration, and more; benchmark it with your workload.

fsync() and write durability

One thing to watch out for with VMs is that you must make sure the disk system tells the truth about fsync(). A very easy way to make VMs a lot faster is to ignore fsync() requests. That's fine until the VM host crashes or loses power, at which point your databases are likely to be hopelessly corrupted. The VM host must either honour fsync() requests by respecting the guest OS's disk flush commands, or must offer non-volatile write cache that won't go away if power is lost. Some SANs use SSDs for that, most other systems use battery backed RAID controller cache memory. If your VM can process more than a few hundred transactions per second it's likely to either be ignoring fsync or on write-caching storage, and you should find out which before it eats your data.

But why?

(Updated): As noted by Chris Travers, why should you virtualise DB servers? Why not handle replication, heartbeat and failover at the DB server level, migrate via promotion of replicas, and get the full performance of the bare metal?

I wrote this original response with the mindset that a VM was a given, and the question was how to get the best results. The best virtualisation for a DB server is still, in my mind, no virtualisation. That said, I've only managed fairly small sites.

Answer 2

In general I have doubts about database servers on a VM. I don't doubt it is possible as per the previous answer but it strikes me as a complexity loss particularly for PostgreSQL where you could instead run several clusters on the same system without virtualization as long as you different IP addresses or ports.

The thing people talk about on the pro side is that you can consolidate servers. This is most useful on other RDBMS systems where running several instances on the same server is not reasonably doable. In some cases it may offer some flexibility with performance. For lower-end applications, particularly, these may be compelling benefits.

However these have to be weighted against the fact that a VM is very much a black box and if you are running over the cloud in particular, you have a lack of control over the hardware. This means that disk I/O, to which such servers are particularly sensitive, is often outside your control. It is possible to mitigate this with networked storage.

So the way I look at is this: vitualization, if it solves a specific problem you are having, can be a useful solution if you are already in an environment which gives you control over disk I/O for each cluster. However, more often it is a solution in search of a problem and not a complexity win. If you are asking this question, it's not the right solution for you.