Slow mean write time using native Navision DB

Alex_Chow

Hi, we have a client that has a very slow mean write time (between 50ms to 80ms). This only happens to one of our implementation sites.

Their server information is:
IBM xSeries x360
CPU = Pentium Xeon MP 1.9
RAM = 2 GB
HD = C:\ the OS directory is RAID 5 with 3 harddrives
\ the database directory is IBM's proprietary RAID called 5EE. It has 7 harddrives
Speed = 15,000 RPM
Size = every harddrive is 73GB
Users = 45 concurrent users
Navision DB Size = 28 GB
Navision DBMS cache = 1,000,000kb
Commit Cache = On
Navision version = 3.7b

They only have Symantec Antivirus Corporate Edition installed. It's set to Autoprotect scan.

Everytime someone posts something, every computer running Navision slows down.

Our other installations are running the same Navision version with larger database size, but only has 0 - 5ms mean write time.

Any possible solutions?? ](*,) ](*,) ](*,)

Savatage

I remember a post way back when that someone finally noticed that the virus program kept scanning the database everytime it changed.

Just throwing it out there, that you might want to try it with the virus program turned off.

The other thing I noticed was the Raid configuration. 5 is not recommended.

& the cache size is quite large. I remember another post about a slow down and the lowered the cache size to improve performance

Is it always slow or just when posting or a report is run?

ara3n

Lower the dbms cache to 800. I think Navision behaves wierd when you assign more than that. Do you have a lot of keys, Modification in this installation?
How much free space do you have on the database? What percentage of the db is free?

Savatage

ara3n wrote:

Lower the dbms cache to 800. I think Navision behaves wierd when you assign more than that.

This post also mentions this:
http://www.mibuso.com/forum/viewtopic.php?t=7117

Alex_Chow

We've turned off the Antivirus but the performance is still slow.

Yes, our installation site has a lot of modifications, but so does other sites that has 0-5ms average write time. I don't think modifications in Navision has any impact to writing the data into the harddrive?

The mean write time is consistantly slow. It goes even slower when someone posts a large batch of orders since a bunch of data gets stuck in the Write queue.

We will try to lower the DBMS cache to 800,000 and see how it goes. I will keep you guys updated.

Alex_Chow

Oh, one other piece of information is that all of our client sites are using RAID 5, but their performance is blazing.

Also, the site that has the slow mean write time is split into 9 database files.

Savatage

since ther is no mirroring - I assume they are not worried if one of the drives crashes & burns?

Are the backups updated daily?

Captain_DX4

deadlizard wrote:

Yes, our installation site has a lot of modifications, but so does other sites that has 0-5ms average write time. I don't think modifications in Navision has any impact to writing the data into the harddrive?

I'm going to throw the standard caveats out there about modifications writing to tables with excessive amounts of flowfields, lots of keys, etc.

Also, try going into Database Information (Tables) and make sure you are optimized. We're running a 98GB database and notice tremendous performance boosts when we are fully optimized.

We're on a terribly modified database as well (v2.60 + v3.60 + v3.70 objects all together !! YIKES !!), and we have too many keys and flowfields in my opinion. Mind you, I wasn't around when the original installation was done... *wink*

DenSter

RAID5 is just not recommended for write-intensive database apps, I don't know why people just ignore this advice. If you have to have mirroring and striping, use RAID1+0. Especially with an app like Navision, at some point the parity calculations slow down Navision posting and you have problems. There have been cases where RAID5 has been linked to corrupted databases.

Most of the time performance issues are caused by the way people use the system. Check the number of global dimensions they use, check if they automatically update analysis views, check if they have automatic cost posting on. Then you can do a lot of key optimization. It's not going to make as big of a difference as the SQL Server option, but still a lot of the recommendations on SQL Server also apply to Navision.

You say there are many modifications. Check the number of additional flowfields for reports that run only once a month. Check the number of flowfields on list forms and consider creating an F9 type form instead. I had a customer that added 30 flowfields to the Item table and put all of them on the Item List. They had 25000 Items, and the list came to a screeching halt, even on C/SIDE database server.

Alex_Chow

Just an update. Changing the cache to 800,000 didn't work. It's still slow.

I'm not understanding how modification, even bad ones, can affect the write time of data into the hard drive. If the bad performance is caused by modification in Navision, it should have no affect on how the hardware works.

We ran a test process only report that modifies a field on a brand new test table, and it had mean write time of 50ms.

Most of our customers use RAID 5 because it offers the most protection against crashes, that's according to the book anyway. However, I'm only experiencing slow write time for this one installation.

I'm thinking the culprit is the IBM proprietary Raid 5EE. Does anyone have experience with IBM xSeries server that might shed some light on this? ](*,) #-o

DenSter

All I have to say about RAID 5 is that it works fine until it corrupts your database. Microsoft specifically advises against it, and anyone has the right to ignore that advice. It is better NOT to have parity for Navision databases.

If both your customers have exactly the same database and use it the same way that they should both have the same write time. However, if one ILE table has 10 sumindexfields as opposed to another one that has 1, the first one has 10 times the data to write as the second, and should therefore need 10 times the effort (and presumably time) to write the one transaction, because it has to write an entry for each key.

Suppose you have just one additional compounded sumindex key, with 20 different fields, and 10 different sumindexfields. The DBMS has to maintain the sumindex values for all levels of the key. I have had a case in which I cut the database size in half just (8GB to 4GB) by disabling all secondary keys of the ILE table. You might not realize it, but there's a whole lot of writing going on behind the scenes.

Again, it depends on the number of dimensions, if automatic cost posting is turned on, if the analysis views are automatically updated, things like that. Posting one sales order does not result in one database write.

Then another issue might even be the machine brand, or the disk space, maybe the one customer has a badly defragmented disk, maybe the database usage is slightly different. There are a number of variables involved, not just disk write speed.

Alex_Chow

DenSter wrote:

If both your customers have exactly the same database and use it the same way that they should both have the same write time. However, if one ILE table has 10 sumindexfields as opposed to another one that has 1, the first one has 10 times the data to write as the second, and should therefore need 10 times the effort (and presumably time) to write the one transaction, because it has to write an entry for each key.

Suppose you have just one additional compounded sumindex key, with 20 different fields, and 10 different sumindexfields. The DBMS has to maintain the sumindex values for all levels of the key. I have had a case in which I cut the database size in half just (8GB to 4GB) by disabling all secondary keys of the ILE table. You might not realize it, but there's a whole lot of writing going on behind the scenes.

Again, it depends on the number of dimensions, if automatic cost posting is turned on, if the analysis views are automatically updated, things like that. Posting one sales order does not result in one database write.

We're looking at the mean write time using the Database Monitor Console (a neat little tool in Navision in case you don't have it already) to see the mean write time. If I have a bunch of data that needs to be written into the database, it should put it into the write queue. But putting data in the write queue shouldn't slow down the actual write speed. Is this correct? :?:

DenSter wrote:

Then another issue might even be the machine brand, or the disk space, maybe the one customer has a badly defragmented disk, maybe the database usage is slightly different. There are a number of variables involved, not just disk write speed.

Can you defragment a RAID 5?

[/quote]

DenSter

If you write a record into a table that has many sumindex fields as opposed to a table that has few sumindexfields, the write time will be longer, because it has to maintain all those key values. These individual key value maintenance write operations don't show up in any Navision tool that I know of. I don't think you are comparing apples to apples. This does not HAVE to be because of hardware. I know many people like to only focus on hardware performance, but it is just not that simple.

I don't know if you can defragment RAID5. I would assume you can, but I can't say that I know for sure, and I have no idea how.

If you want to make this a fair comparison, you need to create a blank Cronus database on both systems and do exactly the same transaction with the same values and then measure the difference.

Alex_Chow

DenSter wrote:

If you write a record into a table that has many sumindex fields as opposed to a table that has few sumindexfields, the write time will be longer, because it has to maintain all those key values. These individual key value maintenance write operations don't show up in any Navision tool that I know of. I don't think you are comparing apples to apples. This does not HAVE to be because of hardware. I know many people like to only focus on hardware performance, but it is just not that simple.

This would put more transactions in the write queue, but how does this affect the actual mean write time?

:-k

Alex_Chow

Just an update.

We created a test database server using RAID 0, the mean write time is within norm (0-5 ms) . However, the mean read time shot up to 15-20 ms #-o .

We're researching on how we can get the best of both worlds.... ](*,)

Savatage

Here's a quote from a website describing Raid Arrays

Raid 0
Random Read Performance: Very good; better if using larger stripe sizes if the controller supports independent reads to different disks in the array.
Random Write Performance: Very good; again, best if using a larger stripe size and a controller supporting independent writes.
Sequential Read Performance: Very good to excellent.
Sequential Write Performance: Very good.
Cost: Lowest of all RAID levels.

Special Considerations: Using a RAID 0 array without backing up any changes made to its data at least daily is a loud statement that the data is not important to you.

Recommended Uses: Non-critical data (or data that changes infrequently and is backed up regularly) requiring high speed, particularly write speed, and low cost of implementation. Audio and video streaming and editing; web servers; graphic design; high-end gaming or hobbyist systems; temporary or "scratch" disks on larger machines.

Here's an old post - but it still probably can hold it's own.
http://www.mibuso.com/forum/viewtopic.php?t=2102

DenSter

Best of both worlds: RAID 1+0, which is stiped mirrored pairs. FYI, RAID 0+1 is mirroring a striped array.

Alex_Chow

Here's an update.

We were able to maintain the data security of RAID 5 while speeding up our mean read and write time.

Instead of breaking out the FDB files into multiple files, we just created one large 28GB database file. This dramatically lowered our mean write and read time.

Savatage

We currently Run 1 large file 17GB Raid 1 w/os on sep drive. No problems

..I'm still not confortable with your Raid 5 :whistle: but If you're happy then I'm happy for you! \:D/

eddiewood

deadlizard wrote:

Just an update.

We created a test database server using RAID 0, the mean write time is within norm (0-5 ms) . However, the mean read time shot up to 15-20 ms #-o .

We're researching on how we can get the best of both worlds.... ](*,)

Hi, as you have discovered RAID 5 is awful for write operations and I am amazed at how many installations I hear of with performance problems as a result of using RAID 5.

On an IBM note, I inherited a Navision box that used IBM RAID 1E and it was awful. 1E was used because the RAID controller could only do 8 x RAID 1 arrays and they wanted 9 (8 for data and 1 OS).

I dumped the 1E set-up by moving the OS onto a separate RAID controller, which gave me the 8 x RAID 1's for data and increased the write speed.

Reading from a RAID 5 array should be pretty good, but I would stick to RAID 1 on Native and not worry about read performance, it'll be good enough.

Our database is 92Gb, 100+ users and a high number of transactions. No problems.

frede

How have you set up the Raid 0 also with Raid 1 for striping the data?

And have created the array as one big disk, and just created one big Navision database - so that it is the RAID controller which is taking care of the striping? This is in my opinion the best way to setup the system...

This way Navision is just doing the writing to the database - and it is the RAID hardware taking care of distributing the data correctly...

eddiewood

frede wrote:

How have you set up the Raid 0 also with Raid 1 for striping the data?

And have created the array as one big disk, and just created one big Navision database - so that it is the RAID controller which is taking care of the striping? This is in my opinion the best way to setup the system...

This way Navision is just doing the writing to the database - and it is the RAID hardware taking care of distributing the data correctly...

Best performance was achieved by splitting the database across 8 x RAID 1 arrays. More than 8 arrays and the price/performance ratio wasn't worth it.

Basically leave native Navision to do its job, it appears to be pretty good at it.

Ed.

pciccone

(removed)

DenSter

Raid 0+1 is mirroring a striped array. If you lose one disk, the whole striped array goes down, cutting available disks with 50%. Raid 1+0 is striping of mirrored pairs. if one disk goes out, you can still use all the other ones. I used to have a link with a picture, but I lost it.