Navision 4.03 Native database performance issue

rajdeepsamanta · 2008-05-14

Hi,
I recently upgrade a database from Nav3.70 to Nav4.0 SP-3 and their Hardware configuration in Server is as fllowes:

O/S: Windows Server 2003 R2
Processor: xeon 3.0
Ram: 4GB
Data Base on RAID 5.
HDD Size:135 GB
Current Database Size:90GB
Usage of DataBase:60 GB from 90 GB.
Data Base On Native.
Number of Licence:40

The problem is that when they are posting a Purchase order of 50 items it's takes almost 5 minutes time and that time all user get a massage locked by another user(It's include voucher entry). If any one making any posting all other user get locked by another user.
As per client in Nav-3.70 it was not happenining earlier. Multiple users were able to pass same type of entries at the same time. The new version 4.0 SP3 is not allowing them and becomes very very slow.
When access the database it's taking time. First we put user id and password then coming the main screen it's take almost 30 to 40 seconds. When we acess any menu that will be taken almost that amount of time.

Please get back to me ASAP.

Slawek_Guzek · 2008-05-14

You didn't specify if you did only technical update (executables only) or merge to 4.0SP3.

First simple steps you may do are:

1. check cache settings on native server, and set it to 950MB or something close - as big as possible
2. rebuild your array and go for RAID10
3. backup your database and recreate it using multiple files config. Use at least as many separate files as your CPUs, then restore your backup to the new database
4. Turn off hypher-threading in machine BIOS
5. Set processor affinity of database server process to exclude processor 0 and the last processor
6. Check you LAN config, especially for the workstation which is the locking cause. LAN should be as fast as possible, as the system will be as fast as the slowest workstation connected to the database.

Locking problems can to be resolved on native only by reviewing and rewriting C/AL code, however if you speed up your hardware you will minimize locking time and decrease number of locks.

Regards,
Slawek

BTW Native is not SQL

kriki · 2008-05-14

[Topic moved from SQL Performance forum to Navision forum]

rajdeepsamanta · 2008-05-14

Hi Slawek,
We done the data base marged and also technical update. Can u share me one thing. If the databse in RAID5 then accessing of the databse is slower? The table locking is happend when the post is made. My concern is our posting is slow. so that loking time is taken. Accessing of the data base is slow.

Please get back to me.

rajdeepsamanta · 2008-05-14

Why hypher-threading need to be disabled???????

Slawek_Guzek · 2008-05-14

rajdeepsamanta wrote:

...If the databse in RAID5 then accessing of the databse is slower? ...

RAID5 has quite good read performance, and very poor write performance when it comes to random write small blocks of data (like Native database 4kb page).

RAID10 has excellent read and write performance especially with random read/write patterns and small blocks of the data.

If your writes are long, then your locks are long, so it is more likely that one write will block another.

rajdeepsamanta wrote:

Why hypher-threading need to be disabled???????

Hyper-threading is a feature which utilizes iddle states of real cores to simulate extra 'virtual processor'. If your CPU is very busy, then there is almost no iddle states, so second 'virtual processor' perform very very porly. However neither application, neither OS, are not aware which core is real and which is virtual, so OS might actually assign quite heavy loaded thread to the 'virtual processor'.

Native database server uses only one processor, most likely processor number 0 (default), but disk writer proceses (slaves) are run on more than one thread, so they may be assingned to porly performing virtual processor

David_Singleton · 2008-05-14

rajdeepsamanta wrote:

Hi,
I recently upgrade a database from Nav3.70 to Nav4.0 SP-3 and their Hardware configuration in Server is as fllowes:

O/S: Windows Server 2003 R2
Processor: xeon 3.0
Ram: 4GB
Data Base on RAID 5.
HDD Size:135 GB
Current Database Size:90GB
Usage of DataBase:60 GB from 90 GB.
Data Base On Native.
Number of Licence:40

The problem is that when they are posting a Purchase order of 50 items it's takes almost 5 minutes time and that time all user get a massage locked by another user(It's include voucher entry). If any one making any posting all other user get locked by another user.
As per client in Nav-3.70 it was not happenining earlier. Multiple users were able to pass same type of entries at the same time. The new version 4.0 SP3 is not allowing them and becomes very very slow.
When access the database it's taking time. First we put user id and password then coming the main screen it's take almost 30 to 40 seconds. When we acess any menu that will be taken almost that amount of time.

Please get back to me ASAP.

DO NOT USE RAID 5

The ONLY option for Native database is RAID 1. For a 60 Gig database, I would recommend that you have 12 separate physical drives configured as 6 RAID 1 arrays, for the database and create 6 files of about 15 gig each one on each Array. (You also need another RAID 1 for the OS and NAV Server program.)

@Slawek, some of your comments seem aimed at SQL, but this is a Native configuration.

David_Singleton · 2008-05-14

Slawek Guzek wrote:

...Native database server uses only one processor, most likely processor number 0 (default), but disk writer proceses (slaves) are run on more than one thread, so they may be assingned to porly performing virtual processor

server.exe barely even touches the CPU. You should find that you could run a Navision server on 486 without much problem. Yes its true that multiple processors will be slower, but thats only theoretical. The real reason for not using multi processors, is because that money should really be spent on more hard drives. Of course every little bit helps, and yes it is best to turn off Hyper Threading, but the big gain here is moving to RAID 1 so that the Commit cache can work correctly (which obviously is not the case with RAID 5 or RAID 10).

Slawek_Guzek · 2008-05-14

David Singleton wrote:

@Slawek, some of your comments seem aimed at SQL, but this is a Native configuration.

Which one ? my RAID 10 recommendation ? why do you think that two RAID1 will perform better than one RAID10 under Native and worse under SQL ?

Why in your opinion commit cache will work better on RAID1 than on RAID10 ?

Slawek

garak · 2008-05-14

note: a native server can only adress 1GB Ram an for a better perfomance it's a good "idea" to have more databasefile with the same size, and the disk have the same perfomance.

kriki · 2008-05-14

Slawek Guzek wrote:

David Singleton wrote:

@Slawek, some of your comments seem aimed at SQL, but this is a Native configuration.

Which one ? my RAID 10 recommendation ? why do you think that two RAID1 will perform better than one RAID10 under Native and worse under SQL ?

Why in your opinion commit cache will work better on RAID1 than on RAID10 ?

Slawek

The native DB is best kept in multiple DB-files for performance. The Navision DB-server does already "striping" between the DB-files and is very good at it. So it is not needed to have a RAID10 that does extra striping.

Slawek_Guzek · 2008-05-14

kriki wrote:

The native DB is best kept in multiple DB-files for performance

True, but block write to the single file may also be optimized, may be slower or faster.

kriki wrote:

The Navision DB-server does already "striping" between the DB-files and is very good at it. So it is not needed to have a RAID10 that does extra striping.

The discussion probably will be 'academic' but..

I don't agree. The best at stripping is the device closest to the physical disk - the controller. My point is that informatin of physical disks characteristics and data location are available in the realtime only to the disc controller. So only the disk controller is able to make opimum decision in what order and to which physical disk the data stripes should be written. Assuming that you have good hardware RAID controller of course.

kriki · 2008-05-14

Slawek Guzek wrote:

kriki wrote:

The Navision DB-server does already "striping" between the DB-files and is very good at it. So it is not needed to have a RAID10 that does extra striping.

The discussion probably will be 'academic' but..

I don't agree. The best at stripping is the device closest to the physical disk - the controller. My point is that informatin of physical disks characteristics and data location are available in the realtime only to the disc controller. So only the disk controller is able to make opimum decision in what order and to which physical disk the data stripes should be written. Assuming that you have good hardware RAID controller of course.

I agree that hardware striping is better than software striping, but Navision does already software striping and there is no way to turn it of (remember that multiple DB-files is better than 1 big DB-file).

Slawek_Guzek · 2008-05-14

kriki wrote:

I agree that hardware striping is better than software striping, but Navision does already software striping and there is no way to turn it of (remember that multiple DB-files is better than 1 big DB-file).

Actually it is not 'pure' stripping algorithm optimized for speed but some kind of fair-load algorithm, which generally writes blocks to the file with the most free space. This technique is used for both performance and file size balance purposes, but when files are the same size the difference is not big. Indeed you cannot turn off this feature, but if you have only one RAID controller in the system all 'stripes' are again put into one queue in controller cache.

Database 'stripes' are written to the controller memory faster if you have multiple files on multiple logical disks and server uses separate thread for every file/drive pair, so the writes are performed in paralell.

In my opinion config with multiple RAID1 arrays would be faster if there is more than one RAID controller in the system.

bbrown · 2008-05-14

Multiple files vs. RAID 10 really address two different performance bottlenecks. When breaking the native database up into several files it allows the NAV server to initiate a separate disk process for each one. If you look at Task Manager you will find several copies of Slave.exe loaded in memory. Each of these is handling a portion of the disk I/O. If you had only 1 data file on a RAID 10 then there would be 1 disk process charged with handling all the I/O.

Now take 1 of the data files. Regardless of what type of drive it is on the NAV server sees it as a file and has assigned on disk process to manage its I/O. Now the question is where is the bottleneck? Does Slave.exe process data faster then the disk system? In this case would not more drives (RAID 10) help?

Back in the old Pre-MS days one of the NAV engineers recommended to combine these methods on a large database. That was to create several data files and place each one on its own RAID 10 array.

kriki · 2008-05-14

bbrown wrote:

Back in the old Pre-MS days one of the NAV engineers recommended to combine these methods on a large database. That was to create several data files and place each one on its own RAID 10 array.

How about 16 DB-files, each on a 6-disk RAID10?

bbrown · 2008-05-14

kriki wrote:

bbrown wrote:

Back in the old Pre-MS days one of the NAV engineers recommended to combine these methods on a large database. That was to create several data files and place each one on its own RAID 10 array.

How about 16 DB-files, each on a 6-disk RAID10?

Only 6 disk? =P~

kriki · 2008-05-14

bbrown wrote:

kriki wrote:

bbrown wrote:

Back in the old Pre-MS days one of the NAV engineers recommended to combine these methods on a large database. That was to create several data files and place each one on its own RAID 10 array.

How about 16 DB-files, each on a 6-disk RAID10?

Only 6 disk? =P~

Don't exaggerate, please. 6*16=96 disks.

rajdeepsamanta · 2008-05-16

Hi all,
The problem on raid 5, I change the server HDD array with raid10 its effected. Now no issue of locking and posting and datafetching.

Savatage · 2008-05-17

Let this be a good reminder to all those using raid 5 & native database.

I've never seen the setup guide ever mention Raid 5 as a good idea for Native Nav.

The Raid 5 Thread..
http://www.mibuso.com/forum/viewtopic.php?t=14029

Howdy, Stranger!

Quick Links

Categories

Navision 4.03 Native database performance issue

Comments