~kanarip

Some organisations tend to use VMWare ESX(i) to virtualize their server environment and regardless of whether that is the best choice for any given environment, what you should most certainly not do, in my humble opinion, is run any type of database server on any kind of virtualization technology that;

1. does not support virtio,
2. does not natively support x86_64 guests,
3. does not permit you to tweak the hell out of it's virtualization stack,
4. does not permit you to fix their code (if at all the code is available)

To clarify statement #2 a little; You should know that if you choose the guest to be x86_64, you are going to see a relatively large performance hit if your virtualization technology only supports a x86 host node operating system and thus needs to apply binary translation (in series, increasing the number of levels to decend in the virtualization stack), to strip the (faster) x86_64 "shortcut" CPU instructions and have it all be supported by the x86 host operating system.

I'm not the absolute expert on the subject of virtualization myself, admittedly, regrettably, and whenever I talk about the benefits of later generations of Free Software virtualization in comparison to other virtualization technologies I tend to simplify and abstract my understanding of how it is more efficient and less error-prone, by saying that most of the throughput from the guest to the actual hardware needs to decend as few levels in the virtualization stack as possible, and some stuff can be done in parallel (memory page sharing, virtio, anything else?). It also helps to simply not support layer-on-top-of-another-layer foo such as binary translaction which results in building a giant stack to be descended (in series). I think in the past I've drawn a few very high-level diagrams illustrating this for the often not-so-technically-savvy audience, but I'm unable to find them right now.

So, I like to think that I have some understanding of how this stuff works even though in this blog post I'm obviously wrong when I say stuff like some of the aforementioned because it is technically not entirely accurate but regardless, it draws a nice picture for those that would not understand what I'd be saying if it were technically accurate.

Anyway, that's where I'm coming from, you be the judge of whether I understand the foo or not ;-)

A MySQL server on VMWare ESX(i) 3.X or 4.X virtualization

If you virtualize a database server, you are going to find that any given form of a performance hit by using that virtualization technology is greatly impacting the performance of your database server. It is commonly known that one should very, very carefully consider NOT to virtualize the database server on a virtualization technology that meets the aforementioned 4 symptoms. Taking one more step back, I'd say even virtualizing a database server on any given type of virtualization technology should be very, very carefully considered even if the virtualization technology of choice is the latest and greatest and offers 98% performance of bare metal (Guess which virt. tech. I'm talking about here and get a free beer at FUDCon -all you need to do is leave a comment and attend and pick up your beer).

Let me start by saying this database server is the backend store for one of those services that just needs to be snappy and fast and shiny all-over; end-user facing, it's the most signifcant and most important service a business can run internally; E-mail and calendering. It's what tends users to go nuts if unavailable or even not as responsive ans snappy as they would like. If you've ever done Helpdesk Support I bet you know what I mean.

Re: the performance hit; For one, the I/O performance on the hard disk for this one MySQL server that I manage, virtualized using VMWare ESX(i) is around 12 MB/s. My laptop hard disk does ~57 MB/s of buffered write I/O (if I've not used the wrong benchmark by mistake, I'm sure one of you readers can recommend me a util I do get correct numbers with if this seems a little off).

Since it's doing 33k of InnoDB reads per second (on average, so including weekends and outside office hours), you can imagine the peak is over 250k InnoDB reads -which I then imagine cannot be in memory at any given point the database server needs it, causing direct (yet buffered?) I/O to disk.

It's running on x86_64 guest operating system because of the memory footprint of MySQL being over 2GB so that it can cache a lot, but as it seems the VMWare ESX(i) node it runs on only natively supports the i686 CPU instruction set. Enter the binary translation from x86_64 to i686 (that is, if it is not i586), in series, accounting for yet another layer in the stack that increases the performance hit as well as makes the environment more error-prone. Note that the hardware itself is x86_64, and so the virtualization technology does not use all of the hardware capabilities.

Then of course there is the PAE CPU feature needed for x86 to be able to address over 4GB (which shows as 3290MB) of memory. No matter how small the performance hit actually is, it is a performance hit regardless.

I'm done throwing everything I know at the problem of performance in this situation and so here it goes;

Dear lazyweb,

What would you recommend I do (except raise my hands and stop supporting the environment which I've already seriously considered), given this awkward situation? What's left to tweak that you think I might not have tried yet? This MySQL box is going to production soon and I want it's dependent services to be snappier then ever before -something I don't think I'm going to get if the MySQL box remains to be virtualized using the current virtualization technology in use -but I secretly hope you can prove me wrong.