Database Reports slow after 10.b05 conversoin

[PJan8724]

Hey All,

Just looking from some idea as to what is going on. We just completed a conversion of our database from Progress 9.1c to OpenEdge 10.0b05. Now I'm getting complaints from our nightly operators that the nightly reports are taking up to twice as long as they did running under the 9.1c version of the database. I guess during initial testing all indications pointed to faster response times according to the Developers who did the application testing. I wasn't involved with the testing so I have to go with what they say, I just handled the database upgrade/converion of the project. Anyway we do have a brand new server for the database that is really beefy so I've ruled out the server as being the problem and I'm pretty confident that this is not a database issue.

I guess what I'm looking for is any ideas on what I could look into to see why the nightly reports are taking so long to process. Are there any database/client side parameters that might need to be put in place in order to make the reports run faster? Or does anyone have any other ideas on what else I could take a look at to see what is wrong?

Thanks in advance for all the help,

Pete J.

 

[joey.jeremiah]

sql or 4gl reports ? if sql, have statistics been collected ?

how was the conversion done and were there any high volume deletes done recently or in the conversion process (stale index entry locks).


ofcourse proper startup parameters, database and hardware configuration can make or break any performance gains.

parameter files could have been delete in the upgrade, uptodate type 2 storage areas, rpb granularity and database planning in general, disk arrays, maybe remote vs self serving connection etc.

it might be a good idea to have a consultant *not just anyone who calls himself one* take a look at your setup.

 

[PJan8724]

Hey Joey,

The reports are 4gl reports I believe.

The conversion was done by me using the Proutil conv 910 conversion tool. I did a backup of our production 9.1c database, restored it to a server that was running Progress 9.1c and OE 10. Then I ran the conversion, and after the DB was converted I copied the files over to the new production server. There was no data purged from the database during the conversion process and as far as I know all the application developers did was recompile our existing code and included some additional functionality for the users which should have no bearing on the reports. I've looked over the parameter files and everything seems to be there and our server is brand new with a lot of power.

However I should meantion that all of my data exists in Schema Area 6 since this is the way the database was configured when I got here. I had wanted to move the data off to a seperate storage area during the conversion but management axed that idea because of the amount of time it would have taken. They didn't want the users to be down for that long. Although we did have a consultant come in about a year ago and check over our database configuration and while he did say that our current schema is not best practice he wasn't allowed to change it either for the same reason.

The consultant did performance tune the database and I am using the startup parameters that he gave us (which worked very well under 9.1c) to run the current 10.0 database. Like I said, during initial testing everything worked much better according to the developers.

I'm sure the disk arrays are set up correctly, I trust our SAN Admin when it comes to setting up the disk arrays.

Any other ideas on what else I can check would be greatly appreciated.

Pete J.

 

[TomBascom]

I'm sure the disk arrays are set up correctly, I trust our SAN Admin when it comes to setting up the disk arrays.

Trust. But verify. SAN configuration is a persistent source of performance problems. Your SAN Admin may have the best of intentions etc, etc but it is still very possible that it isn't set up to your advantage. Storage administraitors are almost always under heavy pressure to do things that are not compatible with good database performance. Sometimes they get convinced to do things that are supposedly wonderous that turn out to not be so good. Storage vendors lie to both management and the admins a lot. In the last 25 years CPU throughput has improved by a factor of more than 3,000. Storage vendor hype would have you believe that disk performance is keeping up but in reality disk throughput has merely doubled.

I'm sure he's a great guy. But check into the details anyway. You might be surprised.

Parameters that work well on a certain server running 9.1c may not work as well with 10.0b and a "beefier" server. The specifics of the server config, the parameters and the load could all be relevant. It would be helpful to post them.

There may also be issues with the application configuration -- is it possible that something there was missed? (Is it a purchased application? If so which one?)

Is performance normal for non-reports usage?

 

[PJan8724]

Hey Tom,

Thanks for the reply. I will definately check into the details of the SAN configuration.

-- Quote--
There may also be issues with the application configuration -- is it possible that something there was missed? (Is it a purchased application? If so which one?)
-- End Quote --
Yes, the Application that we have connecting to the database is a purchased application and the name of the application is UNIFI which was purchased from Fiserv and is a financial product. We are running version 5.xx

-- Quote --
Is performance normal for non-reports usage?
-- End Quote --
Actually performance during normal business hours or non-report usage has improved since the conversion from 9.1c to OE10. I have heard no complaints from users about access times and the word is that users couldn't be happier with the system. The thing that is perplexing us is that during initial testing on this server (the new server mentioned in the thread above, I used a test copy of our production database that I converted over to OE10 from 9.1c using the same steps for the final production conversion) all the reports ran a lot faster then when they were run on the pre-conversion database/server.

As requested I've listed the startup parameters below so that you can take a look at them, let me know if you see anything wrong with them (I've taken out the database name and replaced it with "BogusDB" in the parameter line just for the sake of security). Bear in mind that I am using the Progress Explorer Tool (PET) to start and stop the database. So the parameters are split up with in different fields in the PET. However I've listed time in one line here just to make things easier for eveyone.

Startup Parameters.
-N TCP -S BogusDB -L 6400 -B 180000 -n 505 -Mn 100 -Ma 5 -Mm 8192 -spin 24000 -bibufs 10 -aibufs 10

I've also checked with our security guy and asked him to remove the database drives from virus scans in case this was causing the problem. I won't know if this helped or not until nightly reports are run tonight.

Thanks for all the help, any other ideas will be greatly appreciated.

Pete J.

 

[TomBascom]

This is a new windows server?

Check for a screen saver. Everyone goes home at night, screen saver comes on, performance goes in the tank... (No, this isn't a joke.)

Also try running some reports manually during the day. Are they as fast as expected? (IOW -- is this a problem with the general config of the machine or is it confined to night-time?)

Are the reports being run client/server? Have they always been run that way? (My thought is that perhaps a stray -S got added to a .pf file or a command line somewhere along the line during the conversion... That could cause reports, in particular, to suddenly become much slower than before.)

 

[gabriel.flores]

Hi!

If the server is a new server, a check to the Server performance values (CPU, Disk, Memory, etc) when reports are running and compare whit values on work hours may be a good idea.

Regards
Gabriel Flores