Speed degradation issue

Hi, I’m the managing director of an end-user of Financials (cutting over to Attain in 2 weeks) in Brisbane Australia consisting of 40 users, which has grown from 8 users in a relatively short space of time. We’re suffering from speed degradation as the database and the number of users grow and no one seems willing or able to recommend a hardware configuration to take us into the future. When I speak to our local Navision office they say that it’s a matter for our NSC and our NSC doesn’t want to make a recommendation for reasons that I’m unable to fathom. So I’m appealing to anyone who knows out there to help or at least point me in the right direction. Our hardware is as follows (database is 12GB and growing at approx 1GB per month at the moment) 1) Server – HP Netserver Dual PII 450Mhz Xeons with 2GB of ECC RAM. It has a 100Mhz Bus, which may be a bottleneck. 2) Disks – 3 banks of 8 drives in mirrored pairs (RAID 1) giving us 12 physical drives with the database spread evenly across the 12 drives. Drives are 10,000 rpm 18GB SCSI’s. RAID controller is an Adaptec 4 channel device 3) Network – gigabit cards running into a 3COM eight port managed gigabit switch. Power users are running gigabit cards straight into the 8 port managed switch with the other users running off 2 managed 24 port unmanaged hubs. We’ve tested these hubs and are suffering minimal collisions so we can rule this out. We’ve been told that we need a faster server but if we put Navision under full load we can’t get the CPU utilisation above 20% so we doubt a faster server would help but I’d be glad to invest the money in one if it would help. We were told to get as much memory as possible but then we couldn’t get more than 1GB allocated to the Navision service (it wouldn’t start if we tried to give it more than 1GB and it’s wonderful to have bought an extra gig of HP ECC RAM never to have been able to use it. Again, I’d be happy to invest in 10GB of RAM if that would help but we can’t Navision to use more than 1GB. We’ve measured the disk loads using an report I downloaded from this website and in normal usage with all 40 users hammering away disk loads average between 3% and 7% never exceeding 10%. The only activity that causes disk loads to increase is the Navision backup, which gets them up to around 40%. Now in all of this we possibly have got a bottleneck somewhere and the easy way out is to eliminate the bottleneck but I don’t think this will serve us longer term. There have to be much bigger Navision users out there with much much larger databases and many more users who have a hardware infrastructure in place that allows for acceptable speeds and future growth. If anyone is able to assist either with advice or where I should be looking I’d very much appreciate it. My e-mail is alex@dynamicsupplies.com.au and I will be checking the forum regularly too

Can we assume that you are running on SQL server?

There have been many discussions over time on the forum about hardware configurations. For starters, try doing a search for “hardware” and similar key words and you’ll find a wealth of former postings. I’m no hardware expert but 40 users and 12GB database shouldn’t pose any real problem to Navision.

No, I had originally ordered SQL server as part of the upgrade to Attain but cancelled that when the NSC mumbled that SQL was likely to involve an increased overhead and to expect that we may well get further speed degradation after moving to SQL and Attain (which we can ill afford). Thankyou for the advice Adam, off to search now. Just need to find a hardware config that will give us speed through to maybe 150 users and 100GB database over the next few years.

Again, I’m not the expert on this, but in summary I think you’ll find the following from the other posts: 1. The native database can not take advantage of dual processors. SQL can. 2. You are correct that SQL, on like for like hardware, will perform more slowly than native. But SQL will give you other advantages, particularly when moving to higher user numbers. 3. With Native database, disk speed is king. With SQL, memory is more important. 4. Therefore, the database type will hugely effect the specification of server that you go for. I guess it depends on your longer term strategy.

To me it does not sound like the hardware is the problem here. It looks to me like the hardware is adequate for 40 users on a native database. Have you had any custumizations made? It could be a customization that is causing the speed degradation.

When I was working at another company, we had about 20 users and a 12 Gig database. We were running a singe PII 400 with only 384Mb of RAM and were also using 10k drives. We manually entered and posted about 15,000 invoice lines per day so something was always posting. We had no performance problems - believe it or not. [:D] I agree with Lars - look at the points in time where you’re having the problem and see who’s doing what. Might be a mod or other processing. Also, even though you have lots of RAM, check how much physical memory you have available. If you’re on NT instead of 2000 you may get memory leaks from other applications running on the server. Have you noticed that when you reboot the server the problem goes away for a while? Lastly, are you optimizing regularly?

One more thing. When I read the first post, it is not clear if the server mentioned is Navision Attain server only, or if it also is the network server. If it is also the network server, this might be the problem, because it has to handle a lot of other tasks as well as handling Attain.

Okay, in answer to both Lars and Scott, we have had many modifications made. If we just look at invoice posting we have added many new fields and the invoice posting itself is fairly highly customised. We’re posting around 7,500 sales invoice lines daily. We’ve also had a lot of objects made to allow us to view historical sell through numbers at a glance on customer cards, item cards, vendor cards, sales orders, purchase orders, etc, etc. Rebooting the Navision server assists in no way, we’re running W2K Pro and Navision is the only application the server has to run (with the exception of the Ultrium drive attached - but that only runs after hours). I optimise about every two weeks. 384Mb of RAM and 15K invoice lines a day – I’m jealous [:p] We’re still running Financials and about to cut over to Attain 3.10b and our NSC has warned us we may lose more speed again [:(!] I’m currently playing with commit cache and RAM settings at the suggestion of someone else but I have to get user feedback on these results. I’m sorely tempted to just build keys for everything we’re running slow on but then the database size would more than likely blow out and backups and restores are taking long enough as it is. A Navision backup takes around 20 mins and a restore and building keys close to 7 hours. And with strange timing one of the disk arrays (a Nexsan) has had three HBA bus resets in 3 days so I’ve taken that down and am restoring the database across 8 physical drives instead of the usual 12. Ahh, the sweet joys of another all-nighter [^] Have done some searching and suspect that the RAID controller might have been having timing issues with the last array causing these bus resets and this timing issue might have been degrading performance. Will report back on this

One of our customers running 40 users on 45 Gb NA 3.01 SQL database. There is my expirience: RAID. You must choose speed or reliability. If you decide to have speed use stripping. Striping (RAID 0) on SQL imrove perfomance more than 2 times. On Native too. TABLE LOCKING. This is bigest problem, then posting 7500 invoices. SQL DON’T SOLVE THIS PROBLEM. In native server is useless to fight with that. In SQL server you can’t try share entry No’s of posting tables (tables: 17,21,32,45,46 …). For user or department: A: posting 0…99999999 b: 100000000…199999999 … This can you save from lock’s and deadlock’s. NA 3.10: Running NA 3.10 native server with 20 Gb database is madness (if you upgrade NF to NA you get about 50 % database grow - 12 * 1.5 = 18 Gb). If you decide upgrade UPGRADE ONLY to NA 3.XX SQL option. In yours situation, try use RAID 0 on 6 or more HDD, and use as many database files as HDD’s you have stripped. What kind of speed problems you have? Posting, reporting or browsing?

Dalius has a point in the sense that we need some more information about where the problems actually are. However, before going into that, it is important for me to stress that there is definitely no reason to update to SQL Server just because of database size. On the contrary, the database size limit for Navision Server was recently changed from 64 GB to 128 GB (you get 64 GB by standard, but ask for it, and you get an extra 64 GB) for the sole reason of avoiding wasteful conversions, like the one Dalius is suggesting, from Navision Server to SQL Server. Dalius is also suggesting striping the database. Do not follow Dalius’ advice on Navision Server! Navision Server will inherently stripe it’s disk access and adding an extra layer of striping will only cause performance to degrade. As Lars and others have suggested, you are probably doing very well on the hardware part. Of course you may be suffering from weird problems in your disk controllers or related to specific client machines, but if I were you, I’d not spend too much time looking at the hardware unless I had some specific indication that a particular hardware component was causing the trouble. The one exception would be the network hubs. Hubs should be replaced by switches! Test by comparing the performance of machines connected to the switch with the performance of machines connected to the hub. If there is significant difference (in times of high activity), replace the hubs with (full duplex) switches before going further. Regarding Optimization and cache/commitcache: The Optimize feature optimizes for disk space. Optimizing the database will cause performance of update activity to deteriorate, performance of read tasks could improve. Cache: Navision Server can use only 1000 MB. Set the cache to 1000 (or slightly less, there can be problems starting up Navision Server with the max value) and set the commitcache to Yes. For the troubleshooting, I’d suggest the following approach: 0. Upgrade to latest version of C/SIDE (currently 3.60). You can choose to upgrade your application when it suits your business. Upgrading to latest C/SIDE gives you benefit of recent performance optimizations as well as the server-based backup feature: Hotcopy. It also gives your NSC access to the performance troubleshooting tool mentioned below. 1. If general performance of form navigation, browsing, updating, … is poor even in single-user scenarios: You should be looking at network and client computer speed (hardware issue). 2. If general performance is OK but some tasks are slow in single-user scenarios: You have bad modifications. Investigate the C/AL code and table (key) design of those tasks. Your NSC has access to the toolkit: “Performance Troubleshooting Toolkit” to help him. 3. In multi-user scenarios: If performance of read/browse/report tasks is OK, but performance of (some) update tasks is bad: You have blocking (and perhaps even deadlock) problems, most likely related to bad (or over-ambitious!) modifications or too many keys to be updated. Investigate the C/AL code and table (key) design of those tasks/tables. Your NSC has access to the toolkit: “Performance Troubleshooting Toolkit” to help him. 4. In multi-user scenarios: If general performance of even read/browse/report tasks is poor: You probably have some hardware problems. As you have already investigated the most obvious HW components (net and disk), I can’t suggest you what to do in that situation. Enjoy - Jens

I’d like to thank everyone for the things they’re pointing me to as I’ve learnt more about Navision in these past few days through the postings on the forum and searching through past posts than I’ve learnt in the past two years. There’s been a further update from what happened a few days ago which is when I disconnected an array of 4 paired drives from the server and rebuilt the database across 8 drives. The array had started getting bus resets from the controller possibly because of timing differences. Going down from 12 drives to 8 has increased speed quite significantly (mainly reading speed) by easily 50% (we didn’t have benchmarks but 50% is conservative). So one issue appears to have been possible conflicts between different arrays (the problems of bolting on different technology at different times). I have had solved another large problem at the suggestion of another user which was to use dataports instead of the Windows copy function from a table (sales invoice header as an example) and then paste into Excel (this was the only way I was ever shown). What was taking 10 minutes on a fast workstation and would occasionaly lock the client now takes 10 seconds - I’m absolutely flabbergasted and very very happy indeed [:D] [:D] [:D] [:D] Thankyou thankyou thankyou We have had the network tested after installing the managed switch and the network flies so we know that isn’t the problem. I optimise regularly (every 2 weeks). General read tasks have improved out of sight after taking out the bad array. One speed issue remains when we’re posting invoices - between 20 to 30 seconds (major mods were made in this area) and I’ll take this up with our NSC. I didn’t know that badly written mods or that keys with these mods would be such an issue but obviously they are. The only issue that remains is that Navision backups take about 20 mins which I feel is a long time for 12GB but restores take over 8 hours (2 hours to restore and 6 plus hours to build keys) Does anyone know if this is normal ? I can’t imagine what we would do when we reach 30 GB and taking 24 hours to restore - we’d be down for a day just restoring data.

We do both a Navision backup and a more “traditional” backup which is nothing more than a tape backup of the database but we run a script to shut down the navision server service first. This of course requires a bit of down time at night or whenever. Better safe than sorry and the full tape backup won’t require a rebuild of the keys.

Hi Alex We had here the same performance problem with navision, we have a database with 30 Gb and 50 remote users in Wan. We solved the problem recently by Using Raid1, 8200Mb of cache memory and splitting the database over 4 Hard Drives, it´s working better than ever. I hope this helps. Br Jose

I can see there has been some talk about restore times in this topic. Remember to use HOTCOPY (avaliable since 3.01). Then You’ll have no problem with time for rebuilding keys since HOTCOPY takes copies of the database files. A restore is just a matter of coping the files back to it’s original location. And also remember: Since it has been Navisions C/SIDE DBMS that has been discussed here. Allways use RAID1! //Lars

quote:


Originally posted by AlexPicc
Hi, I’m the managing director of an end-user of Financials (cutting over to Attain in 2 weeks) in Brisbane Australia consisting of 40 users, which has grown from 8 users in a relatively short space of time. We’re suffering from speed degradation as the database and the number of users grow and no one seems willing or able to recommend a hardware configuration to take us into the future. When I speak to our local Navision office they say that it’s a matter for our NSC and our NSC doesn’t want to make a recommendation for reasons that I’m unable to fathom. So I’m appealing to anyone who knows out there to help or at least point me in the right direction. Our hardware is as follows (database is 12GB and growing at approx 1GB per month at the moment) 1) Server – HP Netserver Dual PII 450Mhz Xeons with 2GB of ECC RAM. It has a 100Mhz Bus, which may be a bottleneck. 2) Disks – 3 banks of 8 drives in mirrored pairs (RAID 1) giving us 12 physical drives with the database spread evenly across the 12 drives. Drives are 10,000 rpm 18GB SCSI’s. RAID controller is an Adaptec 4 channel device 3) Network – gigabit cards running into a 3COM eight port managed gigabit switch. Power users are running gigabit cards straight into the 8 port managed switch with the other users running off 2 managed 24 port unmanaged hubs. We’ve tested these hubs and are suffering minimal collisions so we can rule this out. We’ve been told that we need a faster server but if we put Navision under full load we can’t get the CPU utilisation above 20% so we doubt a faster server would help but I’d be glad to invest the money in one if it would help. We were told to get as much memory as possible but then we couldn’t get more than 1GB allocated to the Navision service (it wouldn’t start if we tried to give it more than 1GB and it’s wonderful to have bought an extra gig of HP ECC RAM never to have been able to use it. Again, I’d be happy to invest in 10GB of RAM if that would help but we can’t Navision to use more than 1GB. We’ve measured the disk loads using an report I downloaded from this website and in normal usage with all 40 users hammering away disk loads average between 3% and 7% never exceeding 10%. The only activity that causes disk loads to increase is the Navision backup, which gets them up to around 40%. Now in all of this we possibly have got a bottleneck somewhere and the easy way out is to eliminate the bottleneck but I don’t think this will serve us longer term. There have to be much bigger Navision users out there with much much larger databases and many more users who have a hardware infrastructure in place that allows for acceptable speeds and future growth. If anyone is able to assist either with advice or where I should be looking I’d very much appreciate it. My e-mail is alex@dynamicsupplies.com.au and I will be checking the forum regularly too


Alex, did you ever consider waiting a bit and moving to 3.70? This will be (as it seems now) the most stable version ever, with bugs fixed which have been reported for years now… Besides, you told your solution center advised you not to go to SQL: did you ask them if they have the license to deliver SQL to you? Here in the Netherlands you need special SQL certification to deliver the navision sql option, and therefore many NSC’s do not advise to go to sql, not risking their customers to run to other NSC’s who can. If you are growing towards 150 CU and 100 GB you will have to go to SQL some time, because navision doesn’t support more than 128 GB. having to remain some empty databasespace, you better move to SQL when the impact is (relatively) small… But that is just my personal opinion…

Alex, get your users to keep an eye on the left hand side of the status bar at the bottom of the screen. If you get messages like "Searching table blah blah blah - press Ctrl+Break - " then that is a sure sign that the system is searching for records with a sub-optimal key. The longer the message stays and the bigger the numbers get on it will indicate just how much of a problem it is. Get your NSC to analyse the process with the performance tool (if they don’t know how then it comes with a very good guide about what to do). This should allow you to pinpoint any bottlenecks in the code and address them either by redesigning the code or adjusting the key usage. I recently found a couple of howlers in the standard 3.01 reservation management codeunit where a couple of lines of code reduced the timed performance by about 40%. Of course, Sod’s Law operated and the customer said he couldn’t see any difference … Cheers, John

Alex, The company I now currently work for also has huge volumes of invoices daily and I wonder if you’ve ever considered having this process running outside normal working hours as a batch job handled by Business Alert Server (ExpandIT product)? On a personal note, where in Brisbane are you based?