Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2003
    Posts
    17

    Angry Unanswered: We need to reboot ASE nightly.......

    Hello all,

    We migrated from ASE 11.9.2.3 ebf 9220 to ASE 12.5.0.1 ebf 10241. Immediately following the upgrade we began experiencing a lot of tempdb contention. We found that if we rebooted the ASE server nightly that performance would be better. At the time we were on aging Compaq GS140's. We have now upgrading to a IBM REGATTA server that has more than enough horsepower for sybase. We are still finding that we need to reboot sybase on a nightly basis to acheive optimal performance. Currently we have the tempdb system seg in a RAM fs and the data and log are on a jfs file system (dsync off). Does anyone have any insights or have seen this before.

    Thanks,

    -Carl

  2. #2
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    What sort of contention?

    I've seen problems with locks on system tables in tempdb, but that has always been temporary, and has never required reboots to fix.

    Michael

  3. #3
    Join Date
    Jan 2003
    Posts
    17
    Thanks for the reply Michael.

    We start to see demand locks in the tempdb system tables They will eventually clear (in time) but they will start to re-occur rather frequently. We find that if we reboot this doesn't happen at all. We also see that during the slowdowns procedure removals skyrocket in sysmon. We have a 1.3GB procedure cache and should not be seeing this many removals. (unless update stats/recompiles happen) I have a theory that the File system cache is causing our issues. The cache is always full and the OS then has to clear pages to accept new ones @ write time. We are configuring a test box to test the perf of moving tempdb data and log back to raw. We also added new thresholds to all segment of tempdb to track usage. We want to see if we can possibly fit all of tempdb in ram.

    Any help or assistance with this problem would be appreciated.

    Thanks,

    -Carl

  4. #4
    Join Date
    Aug 2002
    Location
    Omaha, Nebraska
    Posts
    21
    Just curiously, I'd like to ask you what your setting for "number of sort buffers" is. Can you post the output of

    sp_configure "sort buffers"

  5. #5
    Join Date
    Jan 2003
    Posts
    17
    We are set to 1000.... There is a bug is you set it too high... and We ran into another with it set too low.

  6. #6
    Join Date
    Aug 2002
    Location
    Omaha, Nebraska
    Posts
    21
    Originally posted by cdotger
    We are set to 1000.... There is a bug is you set it too high... and We ran into another with it set too low.

    What about leaving at the DEFAULT (500 I believe). Was there a reason you set it to 1000? This is just a hunch on my part.

    The reason I ask is that for some sorting operations, you can suffer both a performance penalty, and cause excessive procedure cache misses due to a high setting of this parameter. If your doing lots of tempdb activity, that performance problem can lead to the symptoms you're seeing.

    If you're feeling up to it, I would say reset this to the DEFAULT (500) value and test.

  7. #7
    Join Date
    Jan 2003
    Posts
    17
    We tried setting back to default but there is a bug in Sybase 12.5.0.1 which is fixed in a later release, that is why we have it set to 1000. The problems is we are on AIX 5.1 64bit and there is only one release for that platform until 12.5.0.3 comes out.

  8. #8
    Join Date
    Aug 2002
    Location
    Omaha, Nebraska
    Posts
    21
    Originally posted by cdotger
    We tried setting back to default but there is a bug in Sybase 12.5.0.1 which is fixed in a later release, that is why we have it set to 1000. The problems is we are on AIX 5.1 64bit and there is only one release for that platform until 12.5.0.3 comes out.
    I don't think the "bug" that you refer to has to do with high, or low settings as much as it has to do with not releasing memory in procedure cache. It might be that you are running into the bug to be fixed in CR289854 where you have growing PROCHDR structures that aren't released. When you reboot, you clear procedure cache and the problem appears to go away, until you accumulate these structures again. Given your platform, and release schedule, I would request an EBF/Release from Tech support which did not contain the fix for CR268273 (The fix which caused the regression addressed in CR289854).

    In any case, mention these CR's to your Tech Support contact and let them respond to my suggestion.

  9. #9
    Join Date
    Jan 2003
    Posts
    17
    Thanks for the info Kevin, I think we already ran a check for that CR. I think it was multiple queryplans staying in proc cache. But, I will pass this info over to sybase.

    Thanks for you assistance.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •