Database application runs faster on external USB drive?

[Pluto]

OK, we have this accounting application which we just started using which runs in a client/server setup. The database is very small at the moment (100mb) and there are only 2 users total. We have the database sitting on the data partition on the our 1yr old 2.8ghz Xeon file server's RAID5 volume (Intel 6 channel SATA RAID card) and this is where the server app is running as well.

Even when just one user is connected, they complain of an annoying level of lag when entering data in the fields. I observed this myself. We tried moving the server app and database over to the hard disk of another workstation in the office and it behaved similarily.

We contacted the vendor and get this, they said according to their testing the program runs much better when you store the database on a USB2.0 external hard drive. They even put the following blurb in the product documentation...

Why don't other software products have similar performance problems?
Most often you will not experience performance problems with other software applications such as word
processors, spreadsheets, graphics programs, and so on. These products typically access the disk solely to load
and save files and do not access the disk while carrying out general operations. Even when they do access the
disk, the disk is accessed in a sequential fashion, not with so-called random access.
The software applications that are most likely to expose disk performance problems are database applications
that work on files intensively, using random access.

xxxxxxx is such a disk-intensive database-oriented application. It accesses the disk in a random fashion as you
perform your accounting activities. This high volume of random access is what encounters and exposes potential
performance deficiencies of the disk.

How can external drives perform better than internal drives?
The short answer is that they shouldn't.
But we have discovered that about 80 percent of current computers have a very slow hard drive interface (with
standard Windows drivers for IDE ATA controller). The hard drive may be very fast according to published
specifications, but in reality, the actual data transfer between the drive and Windows is very inefficient, especially
when accessed randomly. When an external drive is plugged into the same machine, xxxxxxx performs up to
10 times faster than with the internal drive. Windows appears to have better drive communication through USB
than through ATA or SATA!

The usual underlying cause is a mis-match of software drivers with drive hardware. Often the computer
manufacturer is unaware of the problem and does not install more up to date or specifically tuned software drivers
for the computer hardware being configured. Often a computer with poor disk performance can be improved by
installing the "right" driver software. However, because most users never run disk-intensive software the problem
usually doesn't surface and the manufacturer never becomes aware of it.

In any case, such a fix is technically complicated, hard to do, and fraught with uncertainty. That is why we
recommend using an external drive. This is sure to work and is very simple to implement.

I read this and reacted extremly doubtfully. A high end Intel SATA RAID card is a 'very slow disk interface' ? come on! we're using the same hardware on other servers to run far more utilized SQL server instances.

Well, I tried doing as they suggested and it actually WORKED

What I can't understand is why... any ideas?


Running the database off an external USB drive is far from ideal for redundancy purposes.

 

[kamper]

Sounds like a fancy excuse for some piss-poor programming. I'd be looking for an alternative

 

[Mark R]

Could it be that moving the database leaves the log file on the other drive?

Now you don't have 2 files competing for writes.

 

[kamper]

What database does it use, btw?

 

[Pluto]

I think its proprietary... not sure about the log file question, will have to check on Thursday.

 

[Smilin]

Originally posted by: Mark R
Could it be that moving the database leaves the log file on the other drive?

Now you don't have 2 files competing for writes.

that's a thought.


Overall though the technicians response was a crock of sh1t.

Be sure the drivers are up to date on your server and see if any other apps (or benchmarks) are having problems. Check your system logs for 7,9,11,5x events indicating any potential driver or controller problems. Once these steps are complete call the company back and tell them the solution is unacceptable to you and to fix their software.

I'm from MS enterprise tech support. One option for you (man, the setup/perf/core team guys are gonna kill me if they know I said this hehe) is to open a paid incident with Microsoft regarding the problem. I can almost guarantee they are going to find it's a problem with the application and not your OS. The difference is they will PROVE it rather than take some wild assed guess like that tech did. As a service to their customer the MS tech will be willing to join a 3-way call with you and the app vendor and explain it.

We get people all the time calling MS with a problem that they know ahead of time is not our fault. It's just that we don't guess and won't point the finger at the other guy unless it can be proven. Customers seem to use us to gain leverage with the lesser tech support the other vendor offers. This typically drives us nuts as proving a 3rd party app is the root cause requires a whole lot of work when you don't write the app yourself! People do it to us all the time though... and it gets the problem fixed for them.

If by some miracle it turns out to be a true bug with your OS, MS will refund your call....dont' get your hopes up though

 

[Nothinman]

What I can't understand is why... any ideas?

Have you tried putting it on a seperate volume on the same RAID card? That would at least rule out contention with Windows since everything it needs should be on the primary volume.

Running the database off an external USB drive is far from ideal for redundancy purposes.

Depends on how you look at it. If you setup 2 servers for redundancy and the primary dies you can 'bring up' the secondary by just pluggin the data drive into it. =)

Sounds like a fancy excuse for some piss-poor programming. I'd be looking for an alternative

Generally I'd agree, but moving the database to an external drive without changing the app itself kinda rules that out. There's nothing special the app is doing in either case.

We get people all the time calling MS with a problem that they know ahead of time is not our fault. It's just that we don't guess and won't point the finger at the other guy unless it can be proven. Customers seem to use us to gain leverage with the lesser tech support the other vendor offers. This typically drives us nuts as proving a 3rd party app is the root cause requires a whole lot of work when you don't write the app yourself! People do it to us all the time though... and it gets the problem fixed for them.

One thing I will say is that MS tech support is thorough. One of our BUs was blaming our firewall (not ISA) for performance and timeout issues with their app that's built around MS Reporting Services, the MS guys didn't believe it was the firewall from the start which was a nice change of pace. =) And after the first day they proved that it was the way our developers were using Reporting Services and tried to work with them on a way to fix the issues. Of course MS couldn't do a whole lot because the app was totally mis-designed, but it was nice to have someone else tell the BU that for once. =)

 

[kamper]

Originally posted by: Pluto
I think its proprietary... not sure about the log file question, will have to check on Thursday.

I can't imagine anybody would really attempt to write their own database these days. There's a million good ones you can pay for, lots of open source ones that may fit their needs and, as a result, limited-but-still-decent versions of the big ones for free-as-in-beer.

 

[Nothinman]

I can't imagine anybody would really attempt to write their own database these days. There's a million good ones you can pay for, lots of open source ones that may fit their needs and, as a result, limited-but-still-decent versions of the big ones for free-as-in-beer.

I doubt they wrote their own, chances are that it's one that they licensed and they just don't want to say which one.

 

[Czar]

try defrag

 

[Pluto]

Originally posted by: Nothinman
Have you tried putting it on a seperate volume on the same RAID card? That would at least rule out contention with Windows since everything it needs should be on the primary volume.

Do you mean a seperate array or seperate partition/logical volume? It is running from a seperate partition from the OS on a single array. I was thinking of adding a couple additional internal drives and either setting them up as a seperate RAID-1 array (which the Intel card should support) or using the onboard SATA ports and see how the performance is then.

My other idea was to purchase two external USB drives and software-mirror them in Windows but then I realized today you can't upgrade a USB disk to dynamic so no go there.

Defrag has not been done recently but with a 350GB partition and only 30GB used I can't see that helping, but I'm running one now just in case. Analyze reported 3 fragmented files and the database file wasn't one of them.


I wouldn't be surprised if the database was something proprietary as this is the 'new' Windows version of a really old DOS application.

BTW, you can open the database directly from the client app without the 'server' app in single-user mode, doing this across a network share or off the local disk of a PC or the FileServer results in decent performance. The problem only seems to be when you are using the multi-user 'server' program.

 

[Nothinman]

Do you mean a seperate array or seperate partition/logical volume?

Either, the latter would probably be easier to setup and test though.

It is running from a seperate partition from the OS on a single array.

Which isn't really good because any I/O the OS does is competing with the database.