Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004
    Posts
    27

    Unanswered: Sybase database issue

    Greetings all,

    I have a database that was built in the following the way:

    CREATE DATABASE Navigator ON
    data_01=2700 ,data_02=300
    LOG ON data_02=1500
    go
    ALTER DATABASE Navigator ON
    data_02=900, log_01=990
    LOG ON log_01=660
    go

    We have just recently migrated from ASE 12.0 to ASE 12.5, and we are having a slowdown in performance in some of stored procedures and backend processing. The configuration environment is either greater or the same as the previous production database. I ran sp_sysmon to see what I can find anything related to performance degraduation. I noticed the following from Task Management.


    Task Context Switches Due To:
    Voluntary Yields 2.1 1.5 3744 5.6 %
    Cache Search Misses 3.6 2.5 6432 9.6 %
    System Disk Writes 0.4 0.3 695 1.0 %
    I/O Pacing 0.8 0.5 1363 2.0 %
    Logical Lock Contention 0.1 0.0 102 0.2 %
    Address Lock Contention 0.0 0.0 0 0.0 %
    Latch Contention 0.0 0.0 0 0.0 %
    Log Semaphore Contention 0.0 0.0 4 0.0 %
    PLC Lock Contention 0.0 0.0 0 0.0 %
    Group Commit Sleeps 0.0 0.0 84 0.1 %
    Last Log Page Writes 1.5 1.1 2665 4.0 %
    Modify Conflicts 0.0 0.0 29 0.0 %
    I/O Device Contention 0.0 0.0 0 0.0 %
    Network Packet Received 14.3 10.2 25821 38.7 %
    Network Packet Sent 34.6 24.6 62314 93.4 %
    Other Causes -20.3 -14.4 -36567 -54.8 %

    Other sysmon sections that might be important are Disk I/O Mgmt and data cache mgmt which are below:

    I/O Mgmt
    ---------
    Disk I/O Management
    -------------------

    Max Outstanding I/Os per sec per xact count % of total
    ------------------------- ------------ ------------ ---------- ----------
    Server n/a n/a 248 n/a
    Engine 0 n/a n/a 248 n/a


    I/Os Delayed by
    Disk I/O Structures n/a n/a 0 n/a
    Server Config Limit n/a n/a 0 n/a
    Engine Config Limit n/a n/a 0 n/a
    Operating System Limit n/a n/a 0 n/a


    Total Requested Disk I/Os 8.1 5.8 14619

    Completed Disk I/O's
    Engine 0 8.1 5.8 14619 100.0 %
    ------------------------- ------------ ------------ ----------
    Total Completed I/Os 8.1 5.8 14619

    Data Cache Mgmt
    -----------------
    Cache Statistics Summary (All Caches)
    -------------------------------------
    per sec per xact count % of total
    ------------ ------------ ---------- ----------

    Cache Search Summary
    Total Cache Hits 12241.2 8692.0 22034122 99.4 %
    Total Cache Misses 77.4 55.0 139344 0.6 %
    ------------------------- ------------ ------------ ----------
    Total Cache Searches 12318.6 8746.9 22173466

    Cache Turnover
    Buffers Grabbed 4.9 3.4 8733 n/a
    Buffers Grabbed Dirty 0.0 0.0 0 0.0 %

    Cache Strategy Summary
    Cached (LRU) Buffers 12236.5 8688.6 22025627 100.0 %
    Discarded (MRU) Buffers 1.6 1.1 2836 0.0 %

    Large I/O Usage
    0.0 0.0 0 n/a

    Large I/O Effectiveness
    Pages by Lrg I/O Cached 0.0 0.0 0 n/a

    Asynchronous Prefetch Activity
    APFs Issued 1.2 0.9 2200 53.2 %
    APFs Denied Due To
    APF I/O Overloads 0.0 0.0 0 0.0 %
    APF Limit Overloads 0.0 0.0 0 0.0 %
    APF Reused Overloads 0.0 0.0 0 0.0 %
    APF Buffers Found in Cache
    With Spinlock Held 0.0 0.0 0 0.0 %
    W/o Spinlock Held 1.1 0.8 1937 46.8 %
    ------------------------- ------------ ------------ ----------
    Total APFs Requested 2.3 1.6 4137

    Other Asynchronous Prefetch Statistics
    APFs Used 0.8 0.5 1367 n/a
    APF Waits for I/O 0.2 0.2 443 n/a
    APF Discards 0.0 0.0 0 n/a

    Dirty Read Behavior
    Page Requests 0.0 0.0 0 n/a

    -------------------------------------------------------------------------------
    Cache: default data cache
    per sec per xact count % of total
    ------------------------- ------------ ------------ ---------- ----------
    Spinlock Contention n/a n/a n/a 0.0 %

    Utilization n/a n/a n/a 100.0 %

    Cache Searches
    Cache Hits 12241.2 8692.0 22034122 99.4 %
    Found in Wash 0.4 0.3 799 0.0 %
    Cache Misses 77.4 55.0 139344 0.6 %
    ------------------------- ------------ ------------ ----------
    Total Cache Searches 12318.6 8746.9 22173466

    Pool Turnover
    2 Kb Pool
    LRU Buffer Grab 4.9 3.4 8733 100.0 %
    Grabbed Dirty 0.0 0.0 0 0.0 %
    ------------------------- ------------ ------------ ----------
    Total Cache Turnover 4.9 3.4 8733

    Buffer Wash Behavior
    Buffers Passed Clean 6.3 4.5 11393 99.0 %
    Buffers Already in I/O 0.0 0.0 0 0.0 %
    Buffers Washed Dirty 0.1 0.0 108 0.9 %

    Cache Strategy
    Cached (LRU) Buffers 12236.5 8688.6 22025627 100.0 %
    Discarded (MRU) Buffers 1.6 1.1 2836 0.0 %

    Large I/O Usage
    Total Large I/O Requests 0.0 0.0 0 n/a

    Large I/O Detail
    No Large Pool(s) In This Cache

    Dirty Read Behavior
    Page Requests 0.0 0.0 0 n/a


    My question is this, from the way the database was created with data and log being written to the same device, could that have been the main reason for performance degraduation because the size of the throughput for 'reads' and 'writes' went down? Could this have also caused the issue with tasks switching, waiting dor some to fully complete?

    Patrick Quinn
    Navigant Database Admin

  2. #2
    Join Date
    Sep 2005
    Location
    France, Paris
    Posts
    34
    Hello,

    Data & log on the same device is not really idealistic.
    It can leads to performance issue and recovery trouble in some case.
    It could be better to rebuild this database on the right devices, if it is production database you will have to plan a downtime.

    You migrated from 12 to 12.5. In general, after migration it is not rare to see some jobs running slower than usual and others running faster than usual because optimizer can be slightly modified or improved.
    Try to see at query level, make sure statistics are updated.
    Regards.

Posting Permissions

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