Navision Speed

vijay4398

Hi Guys ,

I am facing speed issues with Navision . We are using native Navision database .

Used Database size is 33.7 GB
Dataase Size is 50GB
Licensed Size is 65 GB

working with Heavy tables like ledgers takes long time .
What should be the size of the DBMS cache and Object cache .

What other measures could i take to have better speed .

We have 25 users .

Much appreciated .

Vijay

bbrown

What is your drive configuration?

ashbhardwaj

Hi..Vijay
Have you ever done optimization of tables. (File ->Database->Information->Tables->Optimize)
Please do & then check ...
it will reduce the Database usage & also increase the speed.

Also check the hardware/RAm etc of server...

stryk

Well, the C/SIDE Server is sort of a "technological fossil" and requires actually just few resources, which it should get:

DBMS Cache: 1GB (maximum)
Commit Cache: Yes (requires relyable disk-subsystem!)
Object Cache: 32.000 KB (32 MB)

To speed a native server up, it is crucial to have an optimized disk-subsystem, using multiple db files stored on multiple physical volumes! The more spindles used, the faster!

Be careful with the "Table Optimization": this will defragment the database, probably shrinking the used space. But wih certain tables it is not that bad to have a minor fragmentation; as the "version principle" management sometimes benefits from empty "nodes" between the data.

devu_13

Hi
I think you try Optimization of Tables.

kriki

devu_13 wrote:

Hi
I think you try Optimization of Tables.

I agree with stryk. My experience with the native server is that it is best NOT to optimize the tables for performance (Except the ones that almost never change like the post code-table).

bbrown

I find the best remedy with native is to throw a good disk system at it.

canadian_bacon

Regarding optimization, optimization will speed up searches but MAY make inserts slower. So, large tables will benefit from optimization to speed up searches but because it is a large table there are probably a lot of inserts as well which may slow down. So, I wouldn't optimize and instead do as others have suggested and spread the database file over multiple drives. In your case I would span it over at least 5 drives (10GB each).

themave

canadian_bacon wrote:

Regarding optimization, optimization will speed up searches but MAY make inserts slower. So, large tables will benefit from optimization to speed up searches but because it is a large table there are probably a lot of inserts as well which may slow down. So, I wouldn't optimize and instead do as others have suggested and spread the database file over multiple drives. In your case I would span it over at least 5 drives (10GB each).

With the Native database you can't know if a particlular file like the item ledger entry is spanned over multiple disk. you only can put database pieces on separate drives, but you have no way of knowing where the files are located within the database peice. It is not like on SQL where you can place tables where you want them. My experience as an end user with a very similar size native database is to change the keys your different task use. this will be money well spent, as nothing increases proformance on the native database better then using the proper key. and be defualt Navision doesn't, you need to change the forms and codeunits yourself to get it to use the best key. Because the best key for you can be something completely different then the best key for someone else depending on which features you use.

slmaluwa

Hi

This is my last week experience. A Proliant DL 380 with 5GB RAM and lot of hard disks (RAID5) didn't help to keep the performance. 40 GB (single file) Native server was scrawling and i saw lot SLAVE.EXE opened in task manager. around 18 users were invoicing internal and external. Table Locks were terrible and sometime it took more than 30 minutes to complete a 100 line orders. Everyone was locking each other.

I realized that it is something to do with DISK SYSTEM and did the following:

1. Backed up the data
2. Converted the DISK SYSTEM (RAID5) to RAID 0+1 (online using HP tools)
3. Created a new empty database which spanned over 3 disks.(each file 10GB)
4. Restored the DB (it took around 7 hours to complete).
5. All users started working without any issues. Now, the server is very fast and users are happy

note: earlier the DB size was 40GB and now 30GB is enough to have all the data (may be the admin might have expanded unnecessarily).

Finally: we are in the process of migrating to SQL 2005

So, my advice here is to first check your hardware and disk system and make sure it is acceptable config.

Hope this helps

DenSter

For native multiple RAID1 drives are even faster than a RAID10 configuration

slmaluwa

Yes, Denster
I agree with you.

RAID5 parity is worse for NATIVE where a lot of writes are necessary.
RAID 0 may be necessary in case of small capacity hard drives to stripe.
RAID1 has less fault tolerance than RAID5 but performance is very good.

bbrown

slmaluwa wrote:

...
RAID1 has less fault tolerance than RAID5 but performance is very good.

I would have to disagree with this.

I've never tried this, but a NAV senior engineer (back in the pre MS days) told me that multiple RAID 10 arrays was a very good option for larger databases. If you had the budget.

kriki

slmaluwa wrote:

RAID1 has less fault tolerance than RAID5 but performance is very good.

Less fault tolerance?

bbrown wrote:

I've never tried this, but a NAV senior engineer (back in the pre MS days) told me that multiple RAID 10 arrays was a very good option for larger databases. If you had the budget.

Most of the time we talk about less than 10 disks. In this case it is best to have multiple RAID1.
Of course if you can change those 5 RAID1 into 5 RAID10 with each 8 disks.

BTW:
vijay4398: do you have Commit-cache enabled. stryk mentioned to enable it, but you didn't say if it was enabled or not. This can change a lot the performance for writing. And neither did you mention the DB cache size of the server. You should go for at least 800MB of DB-cache. This serves for reading and also for writing (if you have the commit-cache enabled).
If you don't have commit-cache enabled, you will have a big write-performance hit on RAID5. With the commit-cache enabled the write-performance is less of a problem for peaks in writes on RAID5. It gets a LOT worse after a sustained writing.

vijay4398

Thanks a lot guys for your inputs and suggestions ...

I have the scope for increasing the DBMS cache which currently is 500MB. Do i need to reinstall the server for this or this can be done from the options in the Navision itself.

I have Commit cache already activated .

From all the discussions and inputs i think next thing that i should do is spanning the more database files .

Is migrating to SQL server also the option.

Much appreciated

Vijay

Malajlo

You can increase cache size in registry: HKLM/System/Services/CurrentControlSet/<servername>/CACHE

Restart service.

ta5

or you can type cache=<theSize> in the windows service control manager and start the service immeadetly afterwards. Note: Clicking the apply button does not work here.
Thomas

DenSter

slmaluwa wrote:

RAID1 has less fault tolerance than RAID5 but performance is very good.

I don't know if "fault" is the right word for this. With multiple RAID1 arrays, you can lose up to 50% of your disks, and still be running. With RAID 5 you're toast when you lose more than one.

slmaluwa

DenSter wrote:

slmaluwa wrote:

RAID1 has less fault tolerance than RAID5 but performance is very good.

I don't know if "fault" is the right word for this. With multiple RAID1 arrays, you can lose up to 50% of your disks, and still be running. With RAID 5 you're toast when you lose more than one.

Ok, DensTer.
After little thinking I understand your point. "fault" may not be the right word.
We now say RAID0+1 will be a better choice for NATIVE DB with more write activities. This will definitely help when the processes are heavily customized to capture more data (fields) to all ledger entries.

bbrown

Multiple RAID 1 arrays. Not RAID 10 or RAID 0+1. The idea is to get each file on its own array set.

DenSter

slmaluwa wrote:

We now say RAID0+1 will be a better choice for NATIVE DB with more write activities.

The reason for multiple RAID1 pairs is to let NAV stripe the data across the multiple files, and let the storage system take care of the mirroring. There have been instances on RAID10 (0+1 or 1+0) where NAV and the storage system striping at the same time caused conflicts. For native the best practice has always been to put it on multiple RAID 1 pairs, so I don't agree with your advice. There are no added benefits for native to use RAID10.

slmaluwa wrote:

This will definitely help when the processes are heavily customized to capture more data (fields) to all ledger entries.

ehm... that has nothing to do with the RAID setup :-k

slmaluwa

DenSter wrote:

ehm... that has nothing to do with the RAID setup :-k

Right, but, that was based on my experience only. Our config earlier was RAID5 and lot of extra fields were copied from masters to ledgers (may be due to bad custmisation to satisfy the customer.) and all the time I was referring to hardware mirroring/striping only (RAID Controller).
Again, with my very recent experiment and success only I was giving that advice.

One more thing I would like to note here for anyone face performance issue is first refer the a document like Hardware Guide for Dynamics - NAV to see whether your hardware meet suggested config.

Savatage

Raid 5 Thread
viewtopic.php?f=23&t=14029
Raid 6 Thread
viewtopic.php?f=23&t=26743
Raid5 to 10 Thread
viewtopic.php?f=23&t=16736