AC190 Performance problem

Couple of easy things to check:
- Make sure AC190 is not compiled in debug -- i.e. is it AC190.gnt ?
- recompile invoked programs (lstinvk -q prodline ac190)
- verifymsf2000 to verify that the indexes are not missing
- compare the MICROSOFT files between the servers, and make sure you don't have a bad setting

Is your Lawson Application on a SAN share?

John - Thanks for the suggestions.  I went ahead and recompiled the AC190 along with all the other programs it calls (using lstinvk -q).  I verified all tables and indexes are in place using verifymsf2000 which reported no errors.  I also decided to enable TIMESTATS for the AC190 program and kicked it off again to see what is captured.

Brad - As a matter of fact we are using SANs for our LSF9 environment.  Our LSF9 environment actually consists of two servers:

• Application Server running Windows 2003 Enterprise Edition R2 (32-bit) - Utilizes two SAN drives that have the Lawson applications and IBM Websphere, Java, Perl, MS-ADAM, etc. installed.
• Database Server running Windows 2003 Enterprise Edition R2 (64-bit) - Utilizes multiple SAN drives that have SQL-Server 2005 64-bit edition installed.  We have the various databases and related log files installed on specific SAN drives with the Lawson application database and log on it's own drive.

Would the SAN configuration have a bearing on the performance of the AC190 program?

John,

I am an applications guy and know AC, the technical aspects of this issue I am discussing with one of my technical co-workers (so I apologize if what I am about to say is in anyway confusing). We have run across a similar issue with programs that write temp files to the $LAWDIR/product line/work directory.

He suggests checking the Application Server throughput to the SANS, specifically for programs that write to the work directory.

How many records do you have on ACTRANS?

Brad

Some time back, we had a performance problem when we had different kinds of storage in use for different filesystems.  Some temporary files being written to a slow disk, while almost everything was being written to much faster devices.  Higher throughput capacity disks that is, due to being split accross multiple physicial disks.  The "slow disk" was not striped accross multiple devices, so when the I/O rate was high the device response time could become very slow.   Is ALL lawson data on the SAN, or are there a mixture of SAN & non-SAN disks?  Are all SAN disks using the same striping & RAID settings?

John, Did you have the same dual-server setup before LSF?  At one point we went from having the database on the same server as Lawson applications to having it on a separate server.  When that change was made, certain jobs were noticiably slower.  A number of jobs were affected, but to varying degrees, some substantially and others not noticeably.

Brad - Thanks for the suggestion.  I'll have our server folks take a look at the throughput on the SAN.  Based on my limited understanding, the SAN we are using for the LSF9 environment was just purchased last year so the hardware is relatively new and should be providing some high performance.

Our ACTRANS table is quite large, consisting of over 53 million records.  However, the record count is the same as compared with our current environment so I don't believe that to be the driving factor for the difference in performance though I agree some purging of old data would help out (that's another subject that our system users don't like to talk about...)

Ben - The application server contains six local physical disks configured as three Raid-1 disk drives, each 136gb in size.  The C: drive contains the operating system, Java, MKS-Toolkit, NetExpress, MS-ADAM, and Perl.  Drive E: contains all of the WebSphere products.  Drive F: contains all of the Lawson products including the applications, web products, ProcessFlow, EDI, Vertex Quantum (ISAM), and that's about it.

The database server quite different.  It consists of eight local physical disks configured as four Raid-1 disk drives each 136 gb in size.  The first disk, configured as Drive C:, contains the operating system and SQL-Server 2005 (both 64-bit).  The second disk, configured as Drive H:, contains the log file for the LSF9 production database.  The third disk, configured as Drive I:, contains the log files for all secondary SQL-Server databases.  The fourth disk, configured as drive Drive J:, contains the log file for the TempDB database.  In addition to the Raid-1 drives above, this server also is using three Raid-10 SAN drives.  Drive F: is hosting secondary database data files, Drive E: is hosting the LSF9 production database data file, and Drive G: is hosting the TempDB data file.

This configuration for our LSF9 system is far different from our current production system about to be shut off.  In it, all Lawson applications, SQL-Server databases, etc. are on one server consisting one physical drive hosting the operating system and one SAN drive (Raid 5) hosting the applications and database.  All web services are handled by a second server, again containing one physical disk hosting the OS and one SAN drive hosting the web services (TomCat, IIS, and WEBDIR).

Yesterdays AC190 job took  [b]14 hours and 12 minutes[/b] to complete!  That is just completely unacceptable.  However, I have some TIMESTATS to look at now and hopefully we can determine where the slow-down is occurring.  I'd be more than happy to post the stats here if anyone wants to take a look.

How many activities do you have, and when you run AC190, which activity / activity group options are you using?

John - We have a total of 2,732,431 activities in the ACACTIVITY table, only 21,000 of which are in a posting status.

For the AC190 parameters, we are selecting All Activity Groups and both Transaction Level types (Posting and Contract).  Burden GL and Offsets are set to 'Y' as is the 'Update' option.  All other options are set to their defaults.

Are you running BR and/or do you have contract activities?

When AC190 runs for that large of a population of activities, it creates a HUGE workfile, and requires locks on every activity, etc.
In other words, if even only ONE transaction is postable in ACTRANS, and you run AC190 for all activity groups, etc. it will take a ton of resources to post that ONE transaction.

I have a utility that I wrote that will alleviate this problem, if you're interested I can send you more info...

John - Funny you should mention this.  Looking at my time stats, I find that the AC190 program is spending about 60% of it's total time trying to pull records from the ACCNTRACT and ACBILL tables.  Well, we don't do contract and billing thorough Lawson so these tables are actually empty.  So what I did today was kick off another AC190 but this time configuring it for "Posting Activities" only and not contracts.  I'm hoping it finishes a lot sooner (it's been running for 2 hours 15 minutes now).  I'm still capturing statistics on the AC190 so we'll see if there are any differences in what tables are being hit and where all the time is being spent.

By all means, let me know more about your utility.  Thanks.

Well, so far no luck.  We've gone as far as moving the ACCNTRACT and ACBILL tables to an empty database space in hopes that would speed up performance.  We've also implemented an AC190.cfg file that caches 2000 records for the ACACTIVITY and ACTRANS tables while also fully loading the IFMONITOR, ACCNTRACT, and ACBILL tables into memory (-1 parameter).  I've also increased the priority of the job queue used by the AC190 job to the highest setting (20).  It's been over five hours since I kicked the job off and it's still running. 

At this point, we have to assume the problem is related to running the database on one server and the applications on another.  Our current production system has the apps and database located on the same server and we're guessing that there is just no way a data pipe between two separate servers is going to perform as well or as fast as a system bus where everything is on one server.

I'd appreciate any suggestions because at this point it appears we will not be going live with our LSF9 system until this performance issue is resolved.

If that is the cause though, I would expect more than just this one job to be affected. We did see a slowdown when we moved the DB to another server, although it was not as much, and the impact varied considerably from job to job. Many were slower, and some were faster. (Our new database server was a larger, faster machine so some SQL ran faster. Only the network communication was slower)

I would take whatever steps are practical to get the fastest possible link between the app server and database. You may have plenty of bandwidth already, but the issue is more likely latency rather than bandwidth. If these are regular physical servers I would try to make sure they are on the same subnet, without any routers or firewalls between them. And of course gigabit network cards. If these are virtual servers, you may have additional options to use some kind of virtual network.

This is a interesting problem...... Other batch jobs runs faster / or same while only the AC190 takes double (or longer than before). I think the focus needs to be on AC190, what it "touches" and "uses".

1. Although the Verifymsf2000 reported all is ok, I would try forcing a rebuild of the 13 index on ACTRANS, and the 8 on ACACTIVITY. After you do this (which will take a while), run the AC190 and see if its better

2. Shut Down the Apps and SQL server and start from point zero. Once the server starts up,
a. clear out all OLD AC190 work files
b. run ONLY the AC190 and nothing else
c. Look at how TEMP DB and TEMP DB's Logs are growing on the DB server. Is it growing at small increments, or in large chuncks.
d. Also look at the %LAWDIR%\PL\WORK and see how AC190's work file is being generated.

Good Luck.