1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Scatter Factor - Should I be afraid - VERY afraid...

Discussion in 'Database Admin' started by taberj, Mar 5, 2011.

  1. taberj

    taberj New Member

    Hello all.

    I'm a consultant that knows very little about progress. Infact - What I know I've learned from Google - I Google - there for I am....I am in a bad place.... ;-)

    I have a medical customer which paid alot of money for an application (record system) that runs on Progress.

    The vendor that supports it tells me how great Progress and cannot find anything wrong with our system.

    I have no doubt (Based on all the positive info I've turned up in Google :) that Progress is a Great Database...

    However - this SAME vendor setup our environment. Purchased the Physical Hardware long before I came on the scene.

    They purchased an IBM server - with 6 drives. These 6 drives were used to create 3 mirrors. One for the OS and Progress. (Cdrive). One for the Progress backup and logs (D-Drive) and one for what appears to be only the images for their application - no Intergy DB.

    That being said - 3 mirrors supporting Intergy (one of which the database sits on C:\Intergy\DB\Medman.db)

    This - atleast to me - is wrong on SOOOO many levels - having two "spindles" mirrored together with the production database AND the operating system....

    The Database is 20GB.

    I've tried to attach the output of the following command:
    C:\Intergy\DB>proutil medman -C tabanalys

    But it failed.

    Background information:

    I'm dealing with a HUGE disk (filesystem) fragmentation issue. I'm trying to get it done pass after pass with the DB offline.

    I can't do a dump/load myself because I don't have the expertise - and really the vendor should be doing that for all they pay them....

    I'll take care of the filesystem fragmentation.

    The tabanalys mentioned above shows what "google has taught me" to be pretty good for this database. However - the Scatter factor is what makes me nervous...I've got scatter greater than 4 and even 7 (Yes SEVEN) on some tables....

    Is this ok....

    Can someone please give me their professional opinion....

    Should I "be afraid, VERY afraid...."

    :)

    Thanks!!

    Respectfully,

    John Taber
  2. Casper

    Casper ProgressTalk.com Moderator Staff Member

    Well before the real experts start writing (I am pretty sure they will), I am very intersted in what OS is running on that server (I suppose its windows, but what flavor is it?). Progress version would be nice too. And while you're at it, maybe give more details on the hardware too.

    You say the vendor says the system runs fine, but you forgot to tell us why you think it isnt running fine. I read between the lines you have a serious performance issue, which is understandable if the disk is fragmented.

    Ok, answer to your direct question: Should you be afraid? Well, no you should not be afraid :)

    Scatter factors that high is an issue if there is a significant amount of data in that table. If all the table have scatter factor > 4 then the database is a very good candidate for a reload. If we would know your progress version we can give more specific detail on what would be the best aproach. Maybe you should even be happy cuz its an easy win to make such a database perform much and much better :) If you cant do it by yourself, I know there are people here who would love to offer there expertise to you and/or your customer.

    HTH,

    Casper.
  3. TomBascom

    TomBascom Curmudgeon

    As Casper said, scatter factor by itself may or may not be an actual problem. Large scatter in a large table -- yeah, that's /probably/ an indicator of a problem.

    But if the only thing wrong is that you see a high scatter factor while the users are happily doing their thing then maybe it's not such a big deal.

    And if there /are/ performance problems, a high scatter factor may not be the most important or beneficial thing to address. (In fact it's quite a ways down on my usual list -- it usually goes away before I get to it specifically...)

    The specific OS and version of Progress are always very helpful. And a description of what the actual problems are is helpful too. Of course chipping away at it in the forums isn't going to be nearly as effective as bringing in someone to help ;)
  4. taberj

    taberj New Member

    You guys are absolutely right - sorry for not shedding enough light on the issue.
    OS = Windows 2003 R2 Enterprise Edition

    DB= OpenEdge Release 10.2A02 as of Tue Aug 11 23:11:18 EDT 2009
    I think this is the Progress edition - the software integrates it enough that it's not easily "looked up" as it's been rebranded somewhat....
    ProUtil returns: OpenEdge Release 10.2A02 as of Tue Aug 11 23:11:18 EDT 2009
    Is there another command I can run to collect the information you need.

    Problem = Intermittently slow response times or people get kicked out of the system. They all connect to the client using terminal services (on a terminal server cluster). From there, the client makes the database connection to the intergyserver.

    The servers = cleaverly named TSINTERGY1, TSINTERGY2 and INTERGYSERVER

    :) I didn't set it up.... It's not bad - but - geezz....

    So - back to the problem - The screens often are waiting for data after the staff clicks on a button. It hangs for a long period of time, some times not so long... I know some of the clients have a bandwidth issue which can hurt the "experience" - but we're very aware of the difference between network "lag" which presents it self much in the same way it would when you or I remote control someone for support over a slow connection.

    What they (the clients) are seeing when that is not a factor is more a delay in the time it takes to return data or respond to a action such as switching tabs or clicking on a button in the interface.

    Some times the whole program will "freeze" while the rest of the terminal server session stays responsive.

    This also presents itself on the server - I've seen the server behave VERY erratically - at one point in time the vendor had been running diagnostics and it would kick everyone out of the system when that ran or other rudimentary processes or actions were run - simply loggin into the console would render the INTERGYSERVER nonresponsive.

    We've since managed the fragmenation (filesystem not database) a little more effectively - but it's still ugly. I've been arguing with the vendor for over a year about the poor design/layout of the physical storage.

    I am going to post the output of the file I couldn't upload in the first post I made. I'm going to post it in the post right after this one so it's not such a "dump" in this post which is more just to answer some of your basic questions.

    BTW - THANKS!! For taking the time to get a little direction on a SATURDAY = You guys are great!

    John
  5. taberj

    taberj New Member

    I got 2011-0305-tabanalys-medman.txt attached.

    Hopefully that helps!

    Attached Files:

  6. TomBascom

    TomBascom Curmudgeon

    You have many largish tables that are fairly scattered so, yes, you probably have a problem. But, again, it may not be the biggest problem that you have.

    There should also be a .st file in the same directory as the db. That would be interesting to look at.

    Another thing that you could dig up is to extract the startup paranmeters from the .lg file. They are shown in the first 50 lines or so whenever the database is started. If, for instance, the db was started on March 1st you could search for "Tue Mar 1" (note two spaces between "Mar" and "1") and then cut & past the entries from "Multi user session begin" through the first few logins (include stuff about BIW, AIW, WDOG, APW and such -- if in doubt just grab 100 lines).

    That will tell us how it is configured but it won't tell us anything about activity levels and contention while a problem is actually occurring. For that you need PROMON or ProTop.
  7. tamhas

    tamhas ProgressTalk.com Sponsor

    I'm going to reinforce an idea referred to earlier ... consider bringing in help. There are some things we can help you do through the forums, but it isn't going to be a rapid or easy process. You will learn some valuable skills, perhaps, but slowly. Whereas, if you and the customer recognize that your expertise really lies somewhere other than tuning the Progress part of what they are doing, then a short engagement with a knowledgeable pro could move you along *much* faster ... as well as educating you much more rapidly in the care and feeding of a Progress DB. It is probably a mistake to expect that kind of support from the vendor ... not because I don't think they should be providing it, but simply because a large number of the vendors in this realm aren't very good at providing it.
  8. taberj

    taberj New Member

    Thanks All for your replys.

    Yes - I agree - getting Tom or Casper involved for some pro services - is the best route to go.

    We do however have to work with the vendor early this week to establish our intentions - We really don't have a choice in the matter because if they don't support our agenda, we'll be in deep trouble with this application.

    We're supporting about 9 doctor's offices and they are provided those services as part of a contractual agreement with my client.

    So we have to work with Sage to get approval.

    Reaching out to ProgressTalk is initially to get professional opinion. Once I "call the vendor to the carpet" this week - showing the "facts". My customer will want to get someone involved from this team to work with the vendor as they've lost all faith in the vendor to effectively manage and troubleshoot their own environment.

    My intention is to start with what I learn hear - putting a high-level "action plan" together - for starters - get Tom or Casper on this system for 2 - 4 hours of "fact finding" and "rock turning".

    After which we hopefully can start with a basic, slightly more detailed, action plan such as Dump and Load, different physical disk configuration suggestions, index rebuilding... I don't know... That's where you experts come in. And yes - I would like very much to glean as much knowledge on how to identify the "tell tale signs" of looming or mounting issues so that I can work with Tom or Casper in the future.

    Additionally, before resting my reputation on a stranger - albeit - a quite time tested - guru - as can be seen just by sifting through the forums here, I'd like to collect the information requested to see what road I'm being led down.

    Indeed - we all know, no good deed goes unpunished, and if I bring in someone - "cosigning" them as a guru - and likely the answer to our mission.... And I fail - I'm in a very lonely place. Where I'm at now - is a very desirable vantage point. I have the luxury of sitting back and saying - this server config is academic and was obviously built by some not so experiences hardware admins. Additionally, I'm seeing some really poor storage file layout on the file system, really bad "Scatter Factors" and some other information that leads me to believe that little to no effort has been made on behalf of the vendor. "THEY" need to step up and handle business.

    At that point I would not be putting my neck out.

    I fully intend to continue to support this customer for years to come. I've been helping them for 1.5 years now. I would much rather establish a business relationship with a 3rd party Progress Pro/Guru and contract to them for professional service on going.

    Please see the requested LG and ST files - I've zipped them to get their size down - the LG file was pretty large so I've only included a portion from just after the startup of the database today. I wonder if the logging level is set a little high - Again - I'm no expert so I'm just throwing that out there to the experts.

    Thanks again for EVERYthing done and all the effort provided up til now!

    Respectfully,

    John Taber

    View attachment Medman-lg-st-files.zip
  9. tamhas

    tamhas ProgressTalk.com Sponsor

    So, OK, sounds like there is time pressure and that is contributing to panic. Step 1, breath deeply, exhale slowly, and relax. Ask yourself if
    1) You need more time before the meeting; or
    2) The meeting is just a starting point, so don't stress too much about what happens in the next couple of days.

    Step 2, examine your expectations versus the actual contractual obligations of the vendor. Business practices among APs vary from throwing the software over the transom to cradle to grave doing everything for you. Generally, the actual *obligation* is clear if you do a little looking, regardless of what you think it should be. *Most* of the time, I believe, DBA management and tuning are the responsibility of the customer, although the AP may provide "guidance" ... which is frequently questionable. Get this clear before the meeting because getting outraged at them for not doing something they are not obligated to do ... and, moreover, may not be very good at ... is non-productive.

    I think you are also maybe stressing a bit about the risk of bringing in a consultant. You are not promising that this consultant will be a miracle worker ... and no competent consultant is going to promise to be a miracle worker. At best, they are expert in their field and will share that expertise with you. Frankly, if you check websites, all Progress forums (PT is not the only one, but any means!), etc. you are going to discover that there maybe three names outside of PSC itself which have stood out for years and years as performance experts ... frankly, even those at PSC worth mentioning in the same breath are not available to you. Pick one of those three and is a bit like it used to be buying IBM. Whether it works or not, you can't really be blamed.
  10. Casper

    Casper ProgressTalk.com Moderator Staff Member

    Even though there so far hasn't been a system which I couldnt tune properly, I must say that most of the things I learned came from the mind of Tom. So if you would be hiring one (which is the best to do in this case), I strongly recommand Tom Bascom, who is in my opininion one of the best DBA-ers in the Progressfield.

    Just needed to say that :)

    Hope you succeed in bringing the system in good shape again.

    One more things, in most cases the worst performance issues are due to programming issues. Although a badly configured system togetehr with poorly written programs can be a real pain.

    Regards,

    Casper.
  11. TomBascom

    TomBascom Curmudgeon

    Aw shucks :blush:
  12. TomBascom

    TomBascom Curmudgeon

    Observations regarding the .st file:

    1) These are "type 1" storage areas. (Type 2 storage areas were introduced with Progress version 10 and are a major improvement.)
    2) The storage areas use a uniform (and poorly poorly choosen) rows per block setting. ("Poorly chosen" is my opinion, some high profile Progress people might disagree.)
    3) Data, Indexes and LOBs (if any) /appear/ to be mixed in the same areas -- best practice is to separate them.
    4) The sizes of the extents show that growth of the system has not been well managed -- there have multiple incidents where new extents have been added with haphazard sizes.
    5) Extent sizes are needlessly small resulting in many more extents needing to be managed.
    6) The bi file is in 10 pieces -- this is a very old school approach (version 8 or earlier) with no value and is excessively complex.
    7) On the bright side after imaging is enabled.

    Regarding the .lg file:

    1) -B 200,000 suggests that this is a 32 bit executable. That is unfortunate. With 4k db blocks you are using just 800MB for the db buffer cache. As a result you're probably doing a lot of IO. And because these are type 1 areas it probably isn't very efficient.
    2) -spin 200,000 says that it is an "Enterprise" license, which is good, but 200,000 is probably an inappropriate value for -spin.
    3) -L 400,000 suggests that the application has poor lock table discipline.
    4) BI cluster size of 16MB is likely to be small but I cannot tell for sure without monitoring the live system.
    5) -n 769 -- are there really around 700 users? If so then trying to run that large of a system on a poorly configured Windows server without an experienced DBA is going to be a disaster.
    6) They are starting a SQL-92 broker. So SQL-92 performance management needs to be considered. How often do they run DBTOOL? How about UPDATE STATISTICS?
    7) BIW, AIW, WDOG and APWs are starting. That's good.
    8) There are app servers in this picture. That is another possible source of tuning issues.
    9) User's schema cache files are out of date. There should be some mechanism for refreshing those.
    10) The application appears to be a combination of fat client and app server. Client side parameters and networking issues may be significant contributors to performance problems.
    11) Tablerangesize and indexrangesize do not appear to be set -- thus analysis of table and index activity will not be possible. These parameters should be added at the next db restart. (Knowing what to set them to requires access to the db.)
  13. TomBascom

    TomBascom Curmudgeon

    As TMH said the vendors responsibilities may be misunderstood. There is frequently a lot of miss-communication between the customer, the partner, 3rd parties, the sales person, the support people etc, etc about who is responsible for what.

    Frequently someone will say "we won't support it" when the customer wants to engage a 3rd party to tune their system.

    This is often not actually true. But once it gets said it can be difficult to work around.

    A good counter is "you aren't supporting us now, how would we know the difference?"

    Your vendor may be very, very good at understanding their application space and at writing software. That is their expertise.

    Most of them have no clue how to tune a system. Unfortunately most of them also don't have any idea how clueless they are. Worse are the ones who think they know but really don't. Worst of all are the clueless ones who also want to charge you for "professional services" to make your system run badly.

    If the vendor is saying that they won't support you if you tune the system yourself then you need to escalate the issue. A lot of times it is a generic knee jerk response given by a low level support person who thinks that you are just another mom & pop shop when you are actually one of their biggest and most important customers. Usually having your CEO have a pointed little chat with their CEO will resolve that sort of thing. (It is a very good idea to have done your homework first.)

    The other option is to just not tell them. It's none of their business and they will never know. They will probably think that *they* solved the problem!
  14. taberj

    taberj New Member

    Excellent!

    Thanks Tom.

    I'll take your bullet items here and approach them to see what we can get arranged.

    I'm not sure about "statistics" so I'm don't know how often or not so often they may be run.

    When time permits today and the client has time, I'll talk with there operations manager. I know it's nearly impossible to give any kind of scope of work at this point. Can I give you a call later today? The scopes pretty big so I'd like to break it down into prioritized, managable pieces so that we could provide them a rough estimate of how much time they could expect to be billed for each "phase". Its a medical billing company - they are full of anaytical bean counters.....ha!

    SO...Can I give you a call later today? If so, at which number?


    Thanks!

    John

    john@raidcomputing.com
  15. cj_brandt

    cj_brandt Member

    I believe the main issue with the Intergy database layout is mixing tables with blob fields into Type 1 storage areas, which leads to a lot of physical IO. If you look at your table analysis, you'll see Area 20 has a TMSCatalogBlob table using 7.5gb of the 8.7gb total of the storage area. I am surprised to see your GenFileBlob table isn't larger - in area 22, that used to be the big problem. This database isn't very large for an Intergy client - so once the physical IO issue is addressed, Intergy should run fine.

    MedicalMgr / WebMD / Emdeon / Sage used to perform dump and loads for clients (pre 2007 anyway), if this area concerns you they should be able to help. On small systems with tight timelines, you can run the dump and load for a couple areas each weekend for a month. I would also consider type II storage, unless you would like to pay me to do this every year :)

    I haven't looked through the db log, but if these sites also use Practice Analytics, you will see a large improvement in performance there as well.

    These physical IO issues started popping up in late 2005 or 2006, Sage should know exactly what the issue is and how to address it.
  16. cj_brandt

    cj_brandt Member

    One other item to add. The Intergy database by default doesn't rotate AI extents on a set interval and from looking at the small db log you provided, it doesn't appear this site has anything setup. So at 2am when the db backup kicks off, the entire day's transactions are all sitting in 1 AI extent.

    I would setup the AI Archive Utility in Progress to switch AI extents and send the AI logs to the other 2 Intergy servers as a precaution. You can map a drive to the other machines and move them over pretty easy.
  17. TomBascom

    TomBascom Curmudgeon

    Sure, you can call. I sent you an e-mail with some details.

Share This Page