Navision 3.7 C/Side performance

Huysmans

I'm experiencing a very poor performance while using Navision 3.7.

The hardware that's being used:

• Compaq Proliant ML370 1.4GHz 1*P-III CPU (average usage 10%, peaking to 60%)
• 2 GB internal memory (usage steady at 1.64 GB)
• Smart array 5i controller, 16GB read cache
• 6 internal WU3 SCSI disks 10K Rpm (2 in raid 1 for OS, 4 in raid 0+1 for DB)
• network is 100MB switched
• wild variety of clients (w98-2003) all using 100MB switched connection

Navision C/Side DB settings:

• Size is 8 GB (4 segments of 2 GB each, 23% of free space)
• Commit cache enabled
• 1 GB Cache, 8 MB ObjectCache
• Application is version 3.01.B, client/server 3.70.A
• Average of 60 concurrent users
• The table optimizer is scheduled to run on a daily basis

Besides the usual client data-entry, reporting etc. we have one typical client: interface into our APS environment using ODBC. The interface runs with a frequency of twice per minute.

Any suggestions as to how to measure and improve performance (as perceived by the end-user)?

I'm wondering if there is any improvement to be gained by changing database storage:

• now: 1 logical drive (single partition) based on raid 0+1 of 4 physical disks, containing 4 database segments.
• option1: keep logical drive as is, store DB in a single segment of 8GB
• option2: rebuild to 2 logical drives (each single partions) based on raid 1, store DB in two segments (4GB on each drive)

Final, rather costly option; order new HW based on SAN storage.

kine

basicaly, if you split DB into more files, it is best to have each part on separate physical disks... if you have placed them on same physical disc (or volume) there is no performance boost...

HW for your DB and users is something about:

8x18GB HDD with RAID 1 for DB only + some backup HDD, HDD for OS etc etc... Network 1GB minimal...

Huysmans

kine wrote:

basicaly, if you split DB into more files, it is best to have each part on separate physical disks... if you have placed them on same physical disc (or volume) there is no performance boost...

HW for your DB and users is something about:

8x18GB HDD with RAID 1 for DB only + some backup HDD, HDD for OS etc etc... Network 1GB minimal...

OK. If I understand you right, you're saying don't try to optimize using different file layout. Current config is optimum. First substantial performance gain is to be expected by adding additional disks.

When ordering new HW. I would use these rules of thumb:

• store DB segments on dedicated disks (put OS, temp files, backup etc on other disks).
• get the fastest disks you can get (IO is always the first bottleneck)
• disable write cache on controller, use all memory for read cache
• DB segments should be of identical size and never exceed 80% usage
• when using raid1; put the two disks on different controllers

Any suggestions for:

• optimum size for a segment? I have some fague recollection of "never exceed 2GB size". If you need more space, add disks. If you can't add disks, increase size. There is no benefit gained in having multiple smaller segments on the same disk.
• use raid 10 versus using raid 1 and doubling the number of segments? I.e. one segment of 8GB on raid10 or two segments of 4GB on raid 1? My thoughts: HW is better capable of spreading load than SW. So I would prefer to use the raid10 sollution in combination with excellent controller.

Finally, I'm surprised by your suggestion to use 1GB network? When monitoring network characteristics on the server (using perfmon), the max network traffic I've seen is 1225666 bytes/s which would be eating up just a bit more than 1 MB/s roughly 10Mb/s. So I get the impression using fullduplex 100Mb/s network controller should easily satisfy that load. (BTW. network internal is 1Gb switched, 100Mb switch to each server/station).

kine

Ok, it seems that network is OK... it is only recommendation... :-)

use raid 10 versus using raid 1 and doubling the number of segments? I.e. one segment of 8GB on raid10 or two segments of 4GB on raid 1? My thoughts: HW is better capable of spreading load than SW. So I would prefer to use the raid10 sollution in combination with excellent controller.

There is one thing: By my opinion it is better to have 2x2 HDD with raid1 and 2 files than have 1x4 HDD with raid10 and one File. Because if you have 2x2HDD, the access time is equal to access time of one HDD and SW can split the load. If you have 1x4 HDD, there is higher access time (but I am not sure of this... :-)) and DB server is not able to split the load... don't forget that in case 2x2 there are 2 cache... in case 1x4 is one cache...

Huysmans

I appreciate all the pointers I can get. Sometimes most obvious things will be overlooked.

My preference for raid10 is based on following reasoning. Raid1 is optimized for read but can slow down write transactions. Read is optimized because fastest disk will satisfy the request. Write is slowed down because transaction is written as soon as it is stored on BOTH disks. Raid10 will solve this problem as striping means the controller has multiple disks to write information to which will effectively reduce write delays. So if I only have 4 HDD 18GB, I could use it for 2 raid1 volumes (2*18GB) or 1 raid10 volume (1*36GB). The raid1 sollution would require Navision to do optimization, the raid 10 sollution would do that hardware wise.

Perhaps on DBMS level caching multiple disks is better than one single. But I doubt. Navision is single processor. This means only one processor can deal with one cache. In that case using one big cache for one big file should not differ from two smaller caches for two smaller files. In both cases there is only that much information you can store as cache is limited to 1GB. Situation would be very different if you use the multi-threaded SQL DB.

kine

Yes, I think you are right. I am not proffesional for HW issues in connection to RAID controllers. With the cache I mean the HDD cache or the controller cache... no the CPU cache or DB cache. It means, if each RAID controller have some cache, there is for case 2x2 two caches, in case 1x4 one cache... but it is only my idea :-k

facade

Amount of object cache seems awfully small.

Dakkon

For best performance on Navision you should not use raid 10. Instead use raid 1. If you can split the database across multiple 10k rpm scsi drives running raid, you should notice a nice boost in performance.