If you don’t wear your phone on your belt you can probably stop reading right here…
We’re trying to give our students at MIT (and anyone else who wants to join, Jan 26-28) a little real-world talk about hosting an RDBMS in our modern virtualized, cloud-based, containerized world. Here’s an outline so far and comments/real-world experience would be appreciated. Thanks in advance.
High-performance Hosting of RDBMS in a virtualized and containerized world
Traditional hosting: One computer, enough RAM to hold all data regularly accessed, lots of separate physical disk drives, e.g., one for the table, a different one for each index on the table, one for the logs, etc. Thus an update that involves multiple disk writes can be done in the time consumed by single disk seek and write. Downside: Moving to bigger computer means exporting data, importing (could take a weekend or longer) or using more DBMS admin skills.
Virtual hosting: This is what you’re doing right now. The virtual machine (VM) has one “disk”, which on my desktop computer right now is C:\cygwin\home\Philip\VirtualBox VMs\three-day-rdbms_default_1421369255990_87357.vmdk (4.4 GB file). Downside: slow; Upside: easy to move to bigger computer. (How slow? VMware did a study with, presumably, the best-tuned virtual machine ever to run Oracle, and found that Oracle RDBMS throughput dropped about 20 percent compared to native hardware. More typical is a friend’s ecommerce site where the VM was configured to use a maximum of 16 GB of RAM and installed by itself on a computer with 32 GB of RAM.)
Virtual hosting, real-world server: in a high-load installation the VM could have multiple files assigned, each of which was on a separate physical hard drive, would then look like a separate disk to the VM. Could even give the VM actual physical disks, but then moving to a new computer isn’t so straightforward (what if the new machine has a different number of disks?)
Idiot-proof yet modern and hip: Amazon RDS! Pick a size, a type of storage (disk or flash memory (SSD)), and an RDBMS, then let Amazon keep it all running, upgraded, backed up, etc. Amazon can also set up the system for automatic failover to a hot standby system that has been reading the transaction logs of the production server. Amazon currently supports MySQL, Oracle, Microsoft SQL Server, Postgres, and … Aurora (November 2014 innovation from Amazon, a “drop-in replacement for MySQL”). Note that Google has a competitive service, though limited to MySQL.
Modern, hip, and like an Asian martial arts weapon (i.e., you are more likely to hurt yourself than an opponent): Containerization (see docker.com). Instead of running MySQL as an operating system process (“Traditional hosting”) or in a virtual machine that is larded up with a full copy of Unix you run MySQL in a “container” that is easy to move from one computer to another but smaller and faster to start up than a VM. What about storage? If you’re a true containerization believer then MySQL can use a separate “storage container” when it wants to write to a hard drive. How does this work out when people try it? We did a quick Google search and found “When I run the benchmark on the native machine, I get 779.5 transactions per second. When I run the benchmark in a mysql container, I get 336 transactions/second.”
Phil: I suspect the docker user was Doing It Wrong. When configured properly, docker should have essentially zero performance penalty. Docker uses Linux Containers, which is essentially just chroot + recently added syscalls to allow different processes to see different PIDs. i.e. for two processes running on the same system, process A’s pid 1 != process B’s pid 1. Aside from process isolation and (optional) CPU and disk quotas, there’s no virtualization that would slow this down.
My first guess as to what they were doing wrong involves running the container on top of AUFS (I believe the default in docker), rather than Ext4 or btrfs (defaults on recent linux distros). Of course, in all DB configurations, filesystem selection, configuration and sharing all matter.
I don’t know what that ‘storage container’ is doing, but it sounds like a bad idea. One container is enough.
I can’t provide much real world experience, but I do have two questions.
1) what happens to your traditional solution when you switch from spinning disks to SSDs? (or to take it even further, I see Intel has these new PCIe cards that emulate a disk, 4X SSD performance for $1200 with 400GB)
2) can you provide any cost figures for a year of Amazon RDS?
thanks
Allen: Of course the VM or container priesthoods will always say someone was “doing it wrong”. But I think it is better to be realistic about one’s capabilities one’s organization’s capabilities. If others do something wrong, why I am likely to do it right? So I always like real-world results rather than benchmarks conducted by experts.
(Example: A friend’s ecommerce site was slow. He paid Rackspace for a server with 32 GB of physical RAM. His tech experts configured a VM in which MySQL ran with 16 GB of RAM as a hard limit. Nothing else was running on that physical server. I think that is a good example of what happens when the American IT workforce uses a technology than a benchmark that VMware might develop (or what Amazon and Google employees are able to achieve).
John: Thanks for the SSD reminder. I went over to Newegg.com and found that a 1 TB SSD from Samsung is now just $437. So if you have a database size of less than about 700 GB it would easily all fit on one server with one of these SSDs (plus maybe a second one or a larger magnetic drive for backups to do point-in-time recovery).
Probably in 10 years people with CS degrees will interpret “disk seek” as “Golden Retriever in backyard looking for Frisbee”
Please forgive my obtuse question: but is there no possibility to lock the VMs in transit to machines that lock volumes to SSDs or AFAs that have a service level IOPs sufficient to run the RDBMS? Is SLA for IOPs strict, or can you overprovision? I mean, is this about being optimally efficient, from a IO perspective, primarily?
This sounds like a question that Nate, who blogs at Techopsguys dot com, may have encountered,
I am unaware how one provides QOS for IOPs in a _generic environment.
If everything is filed on a single filer or filer array, then it’s different.
On 3par arrays, you can set priority optimization, e.g.
Nate’s real world views can be found, here: http://www.techopsguys.com/tag/3par/
This post, from 2010, may be relevant, to consider:
“On Solaris, and I expect the same with other operating systems, whenever and I/O is requested some process-level lock is set. This means that if you try to run very high IOPS, this lock can become 100% busy, causing all threads that need this lock to start spinning. End result is two-fold: high CPU utilization and/or lower than expected IOPS”
https://blogs.oracle.com/henk/entry/running_high_iops_against_a
Which would interest me, in context of migrating a node in a cluster, and reflects on the RDBMS interaction with IO, interestingly.
The way this question is posited, is a open to a variety of responses. Comparing a traditional monolithic install, to a possibly open ended (?) number of solutions, including forklifting the database to another engine or architecture, invites more interesting debate, than it may cause absolute solutions.
Definitely a good debate to be had, though!
Phil,
The new “disk seek”, might turn out to be HAL/bus/driver and especially API latency, which is certainly creeping into calculations for performance, with about every array, indeed any storage, I’ve looked at lately!