File utilization or Navision striping

ptech5443

I am looking for help with an issue I am having.

The detail:
I am running native NAV database (db) on windows server 2003. The db is version 2.0 running 4.0 sp3 exe. The objects are highly customized. The db is 72 GB. The db is spread out over 7 files. Database used is 74%. Each file is 10.3 GB. Each file is on a separate Serial Attached SCSI (SAS) 15,000 RPM physical drive. The database files were created at the same time and not expanded after the db grew. We are using raid 1. I am running a process that copies 70 fields from the job ledger entry table which has 3,554,673 records and writes them to a new table. the records are deleted after the analysis is complete. the entries are re-written to the new table each time the analysis is run. I understand I am writing a lot of data from a lot of records. Which brings me to…

The Issue:
When I am running the process I see, using the Database Monitor Console Form, the file usage on the first two files max out while the other files have little or no usage. The two files have high reads and writes in the queue while the mean read and write time is 5 ms or less. The memory and processor have low utilization. Does anyone have any insight that might help me see what I am not seeing? Or could anyone explain to me what my problem is that the usage is on the first two files and not across all files, Navision striping?

kriki

First CPU : Navision-server is very hungry on CPU-time because he never eats CPU time
So a low CPU is very normal for a Navision DB.

Memory : this depends only on the DB-cache given to Navision (at most +- 800MB). In theory to 1 GB but I never heard of an installation were someone has been able to do it.
IMPORTANT : REMEMBER TO USE THE COMMITCACHE!!!!! This is necessary for writing. PS Check also http://www.mibuso.com/forum/viewtopic.php?t=6945.

Disks: These are VERY important for Navision. The faster the better. Navision uses it's own striping. So in general no need for a BIG RAID10, multiple RAID1 are ok. Navision tries to divide to writing load over the different disks, so they are filled evenly. Probably you have the problem that the records you are deleting are on 1 disk and others are written on 1 other disk.
Remember also that MODIFY for Navision are INSERTS (new version of an old record while other sessions use the old version) and when a COMMIT is done, the old records are deleted and it's space in the DB freed.

ptech5443

Kriki,

Thanks for the reply.

I entered as much information as possible to avoid questions that I could answer with detail in the first place. Memory and cpu being low was my attempt to let readers know that the cpu and memory were not the bottle neck.

CPU:

So a low CPU is very normal for a Navision DB.

Ok So you have verified the problem is not CPU related.

Memory: 1 GB of memory for cache, commit cache is turned on, and object cache is 32,000.
Since Memory has low usage would agree that is not the problem?

Disk: 15,000 rpm with 3.0 gbs bandwidth.
Do you feel these are fast enough drives?

Navision tries to divide to writing load over the different disks, so they are filled evenly.

This was my understanding.

Probably you have the problem that the records you are deleting are on 1 disk and others are written on 1 other disk.

I am copying records from one table and writing them to another. I am not deleting the records I am copying from.

So I am not sure what to conclude from your post, CPU and memory does not seem to be the problem.

Since as you say

Navision tries to divide to writing load over the different disks, so they are filled evenly.

why would you then say

Probably you have the problem that the records you are deleting are on 1 disk and others are written on 1 other disk.

So after kriki's post confirming what I already thought, why are two files doing all the work and the other 5 files doing little or nothing? Anyone else have any insights?

kriki

You should check these fields in virtual table "File Database". Each database file has a record.
-"Writes in Queue": these are the writes in the commit-cache waiting to be written on that disk. When you are writing a lot, this can get very high (several 1000).
-"Reads in Queue": This in general remains below 2 or 3 and normally is 0. If it gets higher, it probably means that the server is very busy writing data to the disks slowing down on the reads.
-"Disk Load (%)":this should be 100% when something heavy is going on.

I think the problem is this: you are reading a lot of data that is in those 2 files. Navision is dividing the writing over all disks, but for if each of the files has to do 1000 writes, the first 2 files also need to do 1000 reads because you are reading. So this creates a bottleneck on those 2 files.

Also : if you have a high "Writes in Queue", it creates extra problems because the data remains in the DBcache (maximum 2/3 of the total) occupying place that otherwise can be used as readcache resulting in even more reads on disk resulting in a higher "Reads in Queue".
And if a record to be written is longer then 1 minute in the cache, Navision gives more priority to writing than to reading this to avoid too much dataloss in case of a black-out, resulting in extra performance drop.

If you have performance problems in that moment for other users, I advice to implement COMMITCACHE-trick in the link in my previous post.
This keeps the "Writes in Queue" low (guaranteeing better performance) but using the serverresources (=Disks) at 100%.