TR Forums

There are a lot of bright people on this site. I don't typically ask work-related questions here, but here goes:

We're having a pretty serious performance hit on our image repository server running Laserfiche. It looks like there are a number of contributing elements, but one of them is that we're told it's never a good idea to run SQL on a virtualized server.

And we were just getting into virtual servers, as a way to mitigate the risk of hardware failure.

Do you guys concur it is a bad idea to run SQL on a virtualized server?

Milo Burke wrote:

Do you guys concur it is a bad idea to run SQL on a virtualized server?

No I don't. Back in they day when I helped as a DB Admin, we had most of our servers virtualized included many that did MS SQL. Which type are you using?

We've been running virtualized MS SQL servers for some years now in an ESX/ESXi environment. We've not had any major issues that I can say, "Yes, that issue was a direct result of running SQL on a VM". Now, if you have the .vmdk files for the DB server on a LUN with a ton of other VMs, all competing for disk IOPS, then you might notice some issues. We just keep our SQL .vmdks files on their own LUN, usually.

Milo Burke wrote:

Do you guys concur it is a bad idea to run SQL on a virtualized server?

Was there further explanation, exceptions, or context for that statement? Because otherwise I call bovine feces. The only instance where I could see that being true was if said database was write-heavy and the VMs were hosted on regular HDDs.

If the database is read-heavy and slow then you need one or more of:
- RAM
- Tuning your RDMS to use bigger buffers (see above)
- Better indexing

If the database is very write-heavy, then you need SSDs, regardless of whether it's a VM or not.

This very site was moved to a VM last week by yours truly. It's been awesome so far.

Agree with morphine. An I/O bottleneck is an I/O bottleneck. Doesn't matter if the server is physical or virtual, the capacity (and RAM to support sufficient disk/database cache) needs to be there.

morphine wrote:

If the database is very write-heavy, then you need SSDs, regardless of whether it's a VM or not.

This very site was moved to a VM last week by yours truly. It's been awesome so far.

Indeed! The site really does feel smoother and more responsive now.

Also, as a Linode customer for the past couple of years, I can say that their recent migration to 100% SSDs for all of their servers has turned their VPSes into greased lightning. And they somehow managed to do this without raising prices; in fact they've effectively done the opposite -- they recently raised the storage and bandwidth caps on their existing plans!

[quote="just brew it!"]Agree with morphine. An I/O bottleneck is an I/O bottleneck. Doesn't matter if the server is physical or virtual, the capacity (and RAM to support sufficient disk/database cache) needs to be there.

[quote]
I'm a DBA here, Oracle and SQLserver.
I think JBI is nailing it.
The issue in most VM environments is the over provisioning. How over provisioned are you?
I/O is something that in a VM environment that the bandwidth is not always well taken into account on the VM allocation.
A DB that is I/O intensive needs a good pipe to the storage.
You need to analyze to see what your bottlenecks are... it is memory paging? Is it I/o waits? For your VMs are you ballooning?
Also you did not mention what VM software you are running... that would help on advice.
We run ALL our SQL Servers on VMware and are moving all our Oracle servers to VMware (about 50% there) it is the future, especially in the age of Cloud.

I should have further explained in my original post that we are running on EMC2 Clariion AX4 storage arrays connected to Cisco Fibre Channel Fabric switches with fiber HBAs on the ESX hosts. The drives in the arrays are 15K RPM drives. Again, no issues with SQL that I can blame specifically on being virtualized.

Pagey wrote:

The drives in the arrays are 15K RPM drives.

Quickest path to success = SSDs.

SSDs are practically built for handling databases with massive IOps.

There are probably some structural problems in your software/hardware, but in my opinion, I wouldn't waste time or money with anything else before moving away from mechanical drives.

morphine wrote:

Pagey wrote:

The drives in the arrays are 15K RPM drives.

Quickest path to success = SSDs.

SSDs are practically built for handling databases with massive IOps.

There are probably some structural problems in your software/hardware, but in my opinion, I wouldn't waste time or money with anything else before moving away from mechanical drives.

We're probably due to upgrade storage arrays next year. I was just lending a little anecdotal evidence to demonstrate to the OP that contrary to what he's heard, SQL can run fine in a VM environment.

Guys, thanks for lending your expertise. I'll try to answer as many questions as I can, but my knowledge is limited. I'm a help desk employee being groomed for project management, and my meager understandings of databases has been self-taught.

Laserfiche is where we put all our scanned images. We use it as a scanning tool, and as a folder tree to navigate through to the documents we want to see. I just recently learned it runs on SQL, which appears to be SQL 2008. We're getting major slowdowns when a thumbnail-gathering process runs. Apparently, it can't be scheduled but can only run whenever it chooses, and the software manufacturer recommends leaving it enabled.

The company we pay to support Laserfiche said that the performance implications of the thumbnail processing are exacerbated by our lack of database optimization, our lack of database maintenance, improper selection of database options including things like "auto-close" (whether these things should be on or off I'm not sure), and finally because SQL is on a VM and not on an OS that is on the bare hardware.

The server has a single Xeon 2620, a Sandy Bridge server processor with six cores at 2 GHz and HyperThreading. It has 16 GB of RAM, an internal RAID-5 spread across three business class 7200 rpm drives, and a DAS with RAID 5 spread across consumer class 7200 rpm drives.

The machine has Windows Server 2012 with HyperV as its host OS with Server 2012 and Server 2008 as the two guest OS's. The 2012 guest is for Laserfiche and the company file server, and the 2008 guest is for Exchange. The resources are not being pushed that hard.

The image repository takes up 400-500 GB, and it is on the DAS. We previously had it connected via USB 2. We since upgraded that connection to eSATA with no signs of performance improvement.

The company we pay for support says they're looking into whether there is a backdoor for scheduling the thumbnail generation. They're also going to see if there's any low-hanging fruit for database optimization (which seems like a black art to me). But they are recommending we move SQL to a non-virtualized OS.

Until about six months ago, we hosted Laserfiche from a server circa 2003 with dual Intel Nocona procs and 2 GB total RAM. Clearly we're doing something wrong if we're having performance issues on the new hardware.

- - - - -

So there's the hardware and the context. Is it truly a drag to run SQL on a VM? Or just the expected 10% overhead for which we should have suitable hardware overkill?

just brew it! wrote:

And they somehow managed to do this without raising prices; in fact they've effectively done the opposite -- they recently raised the storage and bandwidth caps on their existing plans!

And yet they are still overpriced....

Milo, I don't understand why thumbnail generation is hard on the database.

It makes me wonder if they're storing thumbnails in the database itself, which is a Terrible, Terrible Idea.

And if that's the case, you're getting bogged down on writes. I'd suggest opening Resource Monitor and seeing exactly what happens in the server when those thumbnail generations run. I bet it's a I/O write spike.

Milo Burke wrote:

There are a lot of bright people on this site. I don't typically ask work-related questions here, but here goes:

We're having a pretty serious performance hit on our image repository server running Laserfiche. It looks like there are a number of contributing elements, but one of them is that we're told it's never a good idea to run SQL on a virtualized server.

And we were just getting into virtual servers, as a way to mitigate the risk of hardware failure.

Do you guys concur it is a bad idea to run SQL on a virtualized server?

Nope.

We've been running SQL Server on virtual servers for a few years now and our performance is pretty awesome. The key is to make sure that your drive space is setup properly... as someone else mentioned, if you have your .vmdk files on a LUN that's shared with a bunch of other stuff, then you're going to see some performance hits. If you keep them on their own LUN it's much better.

Also, it's not a bad idea to split up your data files from your log files; and if you have databases that are hit particularly hard, to split them off from the other databases.

Finally, lots and lots of RAM... Modern SQL engines are pretty good at keeping stuff cached. We have nearly 1.5TB of SQL data on our main server, split up over about a dozen or so databases, and we have a 99.7% cache hit ratio

Wither Hyper-V in Server 2012, Microsoft claims they made virtualization possible for something like 99% of SQL Server deployments. A big part of that, if I remember, is the increased size capabilities of VHDX, storage offloading with ODX, and because a single virtual server can now have 64 virtual processors and 1TB of memory. But I'm guessing your biggest problem is plain old disk, having two databases on a single RAID-5 with just three 7.2k drives. If you had a proper entry level SAN I'm sure that server could handle them both, but maybe with a RAM upgrade.

Milo Burke wrote:

Guys, thanks for lending your expertise. I'll try to answer as many questions as I can, but my knowledge is limited. I'm a help desk employee being groomed for project management, and my meager understandings of databases has been self-taught.

Laserfiche is where we put all our scanned images. We use it as a scanning tool, and as a folder tree to navigate through to the documents we want to see. I just recently learned it runs on SQL, which appears to be SQL 2008. We're getting major slowdowns when a thumbnail-gathering process runs. Apparently, it can't be scheduled but can only run whenever it chooses, and the software manufacturer recommends leaving it enabled.

The company we pay to support Laserfiche said that the performance implications of the thumbnail processing are exacerbated by our lack of database optimization, our lack of database maintenance, improper selection of database options including things like "auto-close" (whether these things should be on or off I'm not sure), and finally because SQL is on a VM and not on an OS that is on the bare hardware.

The server has a single Xeon 2620, a Sandy Bridge server processor with six cores at 2 GHz and HyperThreading. It has 16 GB of RAM, an internal RAID-5 spread across three business class 7200 rpm drives, and a DAS with RAID 5 spread across consumer class 7200 rpm drives.

The machine has Windows Server 2012 with HyperV as its host OS with Server 2012 and Server 2008 as the two guest OS's. The 2012 guest is for Laserfiche and the company file server, and the 2008 guest is for Exchange. The resources are not being pushed that hard.

The image repository takes up 400-500 GB, and it is on the DAS. We previously had it connected via USB 2. We since upgraded that connection to eSATA with no signs of performance improvement.

The company we pay for support says they're looking into whether there is a backdoor for scheduling the thumbnail generation. They're also going to see if there's any low-hanging fruit for database optimization (which seems like a black art to me). But they are recommending we move SQL to a non-virtualized OS.

Until about six months ago, we hosted Laserfiche from a server circa 2003 with dual Intel Nocona procs and 2 GB total RAM. Clearly we're doing something wrong if we're having performance issues on the new hardware.

- - - - -

So there's the hardware and the context. Is it truly a drag to run SQL on a VM? Or just the expected 10% overhead for which we should have suitable hardware overkill?

The esata connection may be one of you issues here. USB 2.0 would have been a nightmare.

The excuse blaming virtual servers is likely since they don't have experience with them.
SQL server without doing tuning and optimization jobs can be a nightmare. but they are quite easy to set up with SQL server.

with just that server you should be fine for the 2 VMs.
RAM to me seems a little low for Exchange plus and application.
do the usual checking in Windows Resource manager on both Windows instances watching for any high I/O and which process.
in SQL server Management Studio check the Activity Monitor for the SQL instance. Check for Resource waits and locks. also watch to see if any data file in particular is experiencing heavy I/O.

dextrous wrote:

just brew it! wrote:

And they somehow managed to do this without raising prices; in fact they've effectively done the opposite -- they recently raised the storage and bandwidth caps on their existing plans!

And yet they are still overpriced....

They're overpriced compared to shared hosting. For a dedicated VPS with decent bandwidth that you have complete control over they seem pretty reasonable to me.

I don't know the details of how the thumbnail gathering process works, but their guy told me it hits the SQL database pretty hard. And when it is running, processor utilization jumps from ~10% to ~80%.

We didn't see any improvement moving from USB 2 to eSATA. It didn't look like that was the bottleneck when we upgraded the pipe, but we did it anyway just in case.

We tried shutting down the Exchange VM, no performance improvement there either. During that time, it was simply host OS, guest OS, Laserfiche and required SQL beneath it.

I'm baffled, because we shouldn't have a processing, memory, or storage bottleneck Just bad ... something.

Again, get Resource Monitor running during one of those moments and you'll be able to get close to the cause.

morphine wrote:

Again, get Resource Monitor running during one of those moments and you'll be able to get close to the cause.

Walk me through it, please?

Just throwing this out there: the company that does our core processing just switched us from ESX to Hyper-V in their data center. We have 4 Terminal Services servers and a DB server (it runs a proprietary DB app, not MS SQL) running. Apparently, if you take the default config options in Hyper-V, it only creates a single virtual storage controller. So when they ran end-of-day on the DB server, it brought ever other VM to a screeching halt. Once Hyper-V was configured with multiple virtual storage controllers, our end-of-day processing time was cut by 67%, and the other VMs were not impacted.

EDIT: I also can't help but cringe a little at all of this running on 7200 RPM drives. I may be barking up the wrong tree, but I just don't see that helping at all. Even if the DB use is not that IOPS heavy, 7,200 RPM disks are, relatively speaking, very IOPS limted.

You think configuring multiple virtual storage controllers would help?

First, we only have 12 employees, and only 8 of them use this image database with any regularity.

Second, slow as the drives might be, they're way faster than the storage array we had on the old server I mentioned before. Modern 7200 rpm hard drives have come a long way.

For our other server, we have 3x Intel DC s3700 SSDs in RAID. That's for SQL and Microsoft Dynamics CRM. I thought the SSDs would make it fly, but it's not faster than when we ran them on a much slower server with 10k HDDs. (It boots in record time, but navigating the database is the same speed.) The SSDs were very expensive. I doubt my boss is going to spring for getting SSDs for the other server as well, particularly one with as large storage needs: Laserfiche, Exchange, and our file server add up.

Yes, performance of consumer drives has improved a lot. But they still tend to suck pretty badly at server-like workloads where there's a lot of random I/O going on from multiple threads. If you've got some sort of indexing/thumbnailing job running in the background that could easily drag the performance into the toilet.

Edit: Other than maybe Caviar Blacks, I would not recommend consumer drives for an enterprise server environment, unless they are only being used for near-line or off-line archiving/backup.

No, no. I'm not saying you should or should not do anything. I am sorry if I gave you that impression. I too am a novice in the VM and SAN world. I was simply putting the idea out there, as I had never realized it could be an issue until our core processing data center ran into the problem. I agree that running Resource Monitor and the Activity Monitor inside the SQL Server Management Studio are the first, best places to start. I was just throwing out "in general" ideas as a basis for discussion from other, more learned folks. In your case, virtual storage controllers is probably not an issue. But, I would feel bad if I didn't at least point it out and say, "hey, we ran into this, you might want to consider it."

This is my go-to query when there are performance issues on any instance of SQL Server... you can do similar things on Oracle and others, but the names of the tables and columns will change. Anywho, run this when things seem bogged down:


The first part is stuff SQL has asked the OS to do; if you see a ton of crap here, especially a lot of CXPACKET waits, it might indicate a lot of memory pressure.

Second second part is tasks at the SQL engine level; these are current execution requests. Generally speaking, if something is being blocked or is waiting on something else, it'll be in this list.

The third part is the OS scheduler. Basically, this is a listing of the available schedulers and what they're doing. If you see 0-1 in the runnable_tasks_count column, you're probably good. If you see 2-4, the server is being pressed. 5 and above is bad in most cases; and if you ever get into the double digits, ouch.

The fourth part is the actual scripts from part two.

My advice is that when things are getting bogged down, run this script (it's basically a snapshot so you have to run it multiple times) and see what wait types are being reported in the second section. Google those wait types and you can get a good idea of what exactly is killing your performance. Could be hardware, bad schema, etc.

Additionally, if you are running SQL Server, consider downloading a copy of SQL Monitor from Red Gate http://www.red-gate.com/ - even if you can't justify the price, it's still worth checking out the 30 day demo. SQL Monitor is awesome for keeping track of SQL Server performance over time. Run that for 30 days and it'll give you a solid picture of what's happening on your server over that time. It even lays out the biggest wait reasons and identifies the processes that cause them.

just brew it! wrote:

Edit: Other than maybe Caviar Blacks, I would not recommend consumer drives for an enterprise server environment, unless they are only being used for near-line or off-line archiving/backup.

I wasn't going to comment, but then I saw your comment and thought I'd post a couple of links and drop some of my own experience here as well:

https://www.backblaze.com/blog/how-long ... ives-last/
https://www.backblaze.com/blog/enterpri ... liability/
https://www.backblaze.com/blog/what-har ... uld-i-buy/

My own experience is that we have used both enterprise and consumer-grade in our enterprise, and found:

1. Performance is identical. Ultimately it depends on software / configuration. If you put two consumer drives in RAID, you get same results as enterprise drives in RAID. Mechanical disks are mechanical disks when it comes to standard web server stuff, e.g. database transactions and serving web pages. Even with big data (MR, elastic search, storm etc.), everybody knows when they're stuck on a mechanical drive server vs one of the newer SSD's.

2. Life is about the same for both. Actually I'd say the enterprise drives (we have used several SCSI and SAS models), life seemed to be less and they get REALLY hot.

Drive model matters across the board. WD Blacks are great, but EXPECT them to fail after a certain period of time. We stood up a cluster of several machines over a period of about 12 months all using WD Blacks, this being about 3-4 years ago. They consistently have started to fail in the last year, probably about 20% have gone down since the first one started.

WD Greens we thought were a great idea, but have turned out to be hit-or-miss. The -EARS models all seemed to fail almost out-of-the-box, while -EADS models and others are still going.

Others we've had success with many models from Hitachi, Samsung, and Toshiba. We have several older Toshiba drives - circa 2005 - that are STILL churning. Golly.

Seagates have been a mixed bag. Some models have been excellent, while others have been REALLY bad.

JdL wrote:

1. Performance is identical. Ultimately it depends on software / configuration. If you put two consumer drives in RAID, you get same results as enterprise drives in RAID. Mechanical disks are mechanical disks when it comes to standard web server stuff, e.g. database transactions and serving web pages. Even with big data (MR, elastic search, storm etc.), everybody knows when they're stuck on a mechanical drive server vs one of the newer SSD's.

2. Life is about the same for both. Actually I'd say the enterprise drives (we have used several SCSI and SAS models), life seemed to be less and they get REALLY hot.

Where you see the difference is a couple of places in the grades of the disks. Consumer caps out at 7200 RPM with most "green" drives now being 5400 again. Enterprise 10K and 15K RPM are the norm. This causes sustained reads (especially sequential reads) to be MUCH faster.
The build quality as you say is not that different, but the warranty and the attitude from the supplier are vastly different.
In an enterprise style array the thermal envelope is different too that is usually in the design of the drives. Though there is a trend to more consumer drives in the arrays now.

There is another advantage of enterprise drives: multipath options. If the controller dies, the data on the drive is still accessible and that can be critical in an enterprise environment. Of course this applies to both HDD and SSD.

I really appreciate all the feedback.

What metrics should I track in Performance Monitor?

Resource Manager is easier, otherwise you will need to add a bunch of perfmon counters which can be a bit of a pain. But Perfmon you can even look at I/O queue length which can be useful.

BTW, you said you are being groomed for project management, does it involve supporting this thing? Because if you will be tasked for this long term, you will do yourself a big favour just to read up on the product a little. I gleaned a bit for like half hour and I think I got a pretty good idea already. I could have started billing you already.

Read up on Laserfiche, you mean?

Project management will be a step away from technical issues. As far as I can tell, it will be stuff like organizing marketing campaigns, doing a cost-benefit analysis for hiring a ghost writer for the boss to "write" a book, sourcing companies that are more effective at portfolio management than we are and learning from them, selecting cloud-based software to give us a smoother user experience, etc.

I'm realizing I've got some enthusiasm for PC hardware. I think my all-time favorite podcast was Wasson going over the details for the mythical Haswell. But I'm really ready to be done working at a help desk, and I have almost no desire to learn about network administration and database maintenance.