New-age Transactional Systems - Not Your Grandpa's OLTP
John Hugg discusses high volume transaction processing applications with high and low frequency profiles, and how VoltDB can be used for that purpose.
The content has been bookmarked!
There was an error bookmarking this content! Please retry.
Posted by Jonathan Allen on Feb 12, 2008
Hosting server applications inside VM images is all the rage today. The ability to quickly move a virtual server from one machine to another as needs change is a big win for IT departments. But can this be applied to heavyweight systems like SQL Server? Conor Cunningham says no.
According to Conor, SQL Server makes several assumptions about its environment. These include:
The first issue comes into play with high-end SKUs that support parallel queries. When a query is executed, the work is evenly spilt among threads. But with both hyperthreading and virtualization, those threads are not running at a consistent speed.
So now I have some threads that finish earlier than others. So they block until the slowest threads finish. Even worse, I don't think that the query re-allocates those threads for other queries until the whole query finishes. So, now you have some background as to why hyperthreading was not recommended for at least some SQL Server deployments.
Later Conor discusses memory and I/O,
SQL Server assumes, at least in the main server SKUs, that it is the only significant memory consumer on the machine. It's a *server*. (SQLExpress has different assumptions, but it's no memory slouch either). Now, SQL Server will work in a memory constrained environment, but you often don't want to do that. You take that away from a lot of different things - the buffer pool, the compiled plan cache, memory to execute queries (for example, hash join grants). All of these things can add up if you aren't careful.
I/O is the area where I have the least experience in virtualization. This is one of the reasons I asked people about production SQL Servers. Usually they did get some storage array, and this makes sense - it ramps the I/O bandwidth and usually isolates it from any other operations on the machine (your OS, your application you are developing on top of SQL Server, etc). I'm going to spend some more time on this, but I think the core idea is sound - as you start sharing your I/O bandwidth over several VMs, you are going to hit limits earlier with big IO consumers like SQL Server. The same basic logic applies - isolate your database traffic onto different storage paths, especially when building a system to scale. In a VM world, this can let you avoid the sharing penalties vs. the default config of everyone sharing the same hard drive.
All this isn't to say that SQL Server cannot run in a virtual image, merely that if performance is critical than it probably isn't work the cost.
Free Gartner Cloud Service Brokerage Report
Agile Practices to Improve Project Management Organization (PMO) Effectiveness
18 agile and lean practices for effective software development governance
Using Drools? See what you're missing! Get the Power of Drools with the Assurance of Red Hat
I recently virtualized SQL Server on Xen HVM and it worked well. Right now the biggest issue is the maturity of the open source paravirtualized IO drivers in Windows (they are making rapid improvements). Enterprise Xen has some proprietary ones.
Nobody said it would be free; of course when you have more scheduling contention (or gosh, less RAM allocated) there is some performance degradation.
It's always a question of quality of services. The first question is : what do I want? the fastest database engine, the most secure database engine, or something that could be maintained easily? Never forget to tell you what do you want to do with your database and what should be its size. Of course, for a large production database, I can suppose that a VM will not be as optimized as a native system. But, tn many cases, a VM could be considered as a good solution. So, I'm not sure that there is a debate around "databases inside VM or not".
Any service that is both using a lot CPU and a lot of memory a lot of the time isn't a terribly good candidate for virtualization. If your DB is used by a lightly used app, then its as much a candidate for virtualization than anything.
But, I would also ask "why do this?". Most first rate RDBMS can happily run multiple databases under one server instance, so burdening the hardware with needing to run mulitple OS instances doesn't seem needed most of the time. You then get that memory/cpu time for the RDBMS. I suppose if you were really worried about things stepping on one another, this is ok, but I think that's rare. I suppose there could be more to worry about regarding ACID properties of your transactions in a virtualization scenario if you can't be sure that a disk write in a VM really is written to disk (I have no idea about that though).
Of course, in all of this I'm thinking of production. For development, there's many good reasons to virtualize a DB server.
John Hugg discusses high volume transaction processing applications with high and low frequency profiles, and how VoltDB can be used for that purpose.
Kevlin Henney examines code samples to see what can be learned from them starting from the premise that one won’t write great code unless he knows how to read it.
Jason Ayers share the observations he made watching a team of developers collaborating in real time on the same code base, pushing XP, pair programming and continuous integration to their extremes.
Michael Snoyman presents Yesod, a web framework written in Haskell and containing a web server, templating, ORM, libraries (templating, gravatar, etc.).
Richard Kreuter and Kyle Banker on how to avoid classical RDBMS transactional systems by using compensation mechanisms, transactional messaging or transactional procedures.
Attila Szegedi talks about performance tuning Java and Scala programs at Twitter: how to approach GC problems, the importance of asynchronous I/O, when to use MySQL/Cassandra/Redis, and much more.
One category of risk that project teams need to ensure they address is business value failure – delivering a product that fails to provide value for the business investor.
InfoQ spoke to the authors of Software Systems Architecture on a couple of new topics, the System Context viewpoint and Agile, which have been added to the second edition.
3 comments
Watch Thread Reply