Why doesn't my Windows database application run faster? How to improve Windows system cache performance?
Hi , fellow experts,
I have a Windows 2008 R2 server system (i7-3930K hexa-core CPU, 16 GB RAM, 2x 300GB 10k SATA drives in Windows-RAID1) that runs a software named TurboMed. TurboMed uses a FastObjects database server, that runs on the same machine. (Both are 32bit apps.) I tell TurboMed to process a rather long textfile and import it into the database. That import process takes a long time (hours).
I would like to understand WHY this takes so long. I checked task manager and resource monitor and don't see any subsystem of the server that is fully loaded and busy. CPU is idling around at ~3%, only about 2.5 GB of RAM is used, the database itself should be fully in the system cache, the harddisk is chirping away at 0.5-1 MByte/s, LAN is idle (actually unplugged). I just don't see any bottlenecks... (well, I do see that one of the 12 virtual CPUs is somewhat busy and loaded about 50% on average, +/- 20%.)
In theory, shouldn't the system just run as fast as it can until at least one of the subsystems is fully saturated, e.g. a cpu core (or Hyper-Thread) is constantly at 100% (on the assumption that the program, e.g. the FastObject server is single-threaded)? However, I do see that FastObject server runs 29 threads, and TurboMed runs 31 threads...
Could someone please enlighten me and explain why the app isn't working any faster? (Please reply only with a qualified answer. If you just guess and don't know than don't reply.)
I'm attaching a couple of screen dumps from the resource monitor - it's in German, but I assume that you can identify the important columns easily.
Thanks a lot for your support,
Thomas
EDIT: As a test I have created a 8GB RAMdisk and have moved the entire TurboMed directory and the file to be imported to the RAMdisk. The process is now much faster, I would guess at least 5 times. CPU 0 is now used quite a bit more (around 80-95% average). The harddisk is not accessed anymore - which is expected.
Now the question must be rephrased somewhat: I would assume that with plenty of RAM Windows should keep the entire TurboMed database and the file to be imported in the system cache and should consequently perform the same as with a RAMdisk. Why doesn't it do that? Why is it still accessing the harddisk and how could I have seen that the harddisk is apparently the bottleneck?
I have a Windows 2008 R2 server system (i7-3930K hexa-core CPU, 16 GB RAM, 2x 300GB 10k SATA drives in Windows-RAID1) that runs a software named TurboMed. TurboMed uses a FastObjects database server, that runs on the same machine. (Both are 32bit apps.) I tell TurboMed to process a rather long textfile and import it into the database. That import process takes a long time (hours).
I would like to understand WHY this takes so long. I checked task manager and resource monitor and don't see any subsystem of the server that is fully loaded and busy. CPU is idling around at ~3%, only about 2.5 GB of RAM is used, the database itself should be fully in the system cache, the harddisk is chirping away at 0.5-1 MByte/s, LAN is idle (actually unplugged). I just don't see any bottlenecks... (well, I do see that one of the 12 virtual CPUs is somewhat busy and loaded about 50% on average, +/- 20%.)
In theory, shouldn't the system just run as fast as it can until at least one of the subsystems is fully saturated, e.g. a cpu core (or Hyper-Thread) is constantly at 100% (on the assumption that the program, e.g. the FastObject server is single-threaded)? However, I do see that FastObject server runs 29 threads, and TurboMed runs 31 threads...
Could someone please enlighten me and explain why the app isn't working any faster? (Please reply only with a qualified answer. If you just guess and don't know than don't reply.)
I'm attaching a couple of screen dumps from the resource monitor - it's in German, but I assume that you can identify the important columns easily.
Thanks a lot for your support,
Thomas
EDIT: As a test I have created a 8GB RAMdisk and have moved the entire TurboMed directory and the file to be imported to the RAMdisk. The process is now much faster, I would guess at least 5 times. CPU 0 is now used quite a bit more (around 80-95% average). The harddisk is not accessed anymore - which is expected.
Now the question must be rephrased somewhat: I would assume that with plenty of RAM Windows should keep the entire TurboMed database and the file to be imported in the system cache and should consequently perform the same as with a RAMdisk. Why doesn't it do that? Why is it still accessing the harddisk and how could I have seen that the harddisk is apparently the bottleneck?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have in the meantime tracked down the issue somewhat and it is clearly primarily a disk performance/caching issue, as suggested by jackieman. I was able to speed things up quite a bit by checking the disks "Turn off Windows write-cache buffer flushing on the device" option. Also, resource monitor's disk tab showed "70% time with max. activity", so this is where I could have seen a pointer to the disk being the bottleneck. There's still a lot I need to explore, but this thread would be going into the wrong direction, so I'm closing it here. Thanks a lot for your thoughts and advice.
Thanks for your feedback.
ASKER
However, the more I play with this the more I come to the conclusion that this is not a database problem, but rather an issue with how Windows handles its system cache. As this is a testbed environment with a single running process and plenty of free RAM, Windows should - in theory - cache the entire database in RAM and only occasionally write back to physical disk (when it has time to spare, and which shouldn't affect the overall system/TurboMed performance). It's apparently not doing that. The question is, why, and how can I make Windows behave as I expect it.