Odd Performance Spikes in ASE 11.9.2

Odd Performance Spikes in ASE 11.9.2

Post by David Hu » Sat, 07 Dec 2002 07:49:27



Greetings,

   I am encountering an odd problem with an ASE server and thought I
would poll for any thoughts. :) I'm just starting on this problem, but
I would appreciate any pointers on things to look at or snap
judgements, ;)

   First, let me list my configuration. I am running an 8 processor
Solaris box with 4 GB of RAM and ASE 11.9.2. The database has no more
than 10 tables with the largest ( and primary ) table containing
approximately 10,000 rows. Only one trigger is configured to maintain
a history for updates to the primary table. No variable length data is
used in any column; they are all numeric(x,0) and char(x). Load
overall is very light; no where near the amount ASE could handle. The
only weak link would seem to be the hosting of the data over NFS. The
database is also heavily over-indexed ( index-to-data ratio of about
24 ).

   The problem is this : periodically, response times for simple
updates and queries will go through the roof ( e.g. from .25 seconds
to 9 seconds ). This seems to happen during periods of low to zero
usage, but will then persist, sometimes for hours, through periods of
normal usage.

   My colleague has done a variety of tests including moving the data
onto locally hosted drives and increasing the memory for the
dataserver to a whopping 512 MB. Nothing has worked.

   Here are the things I have observed : ( Thank you sp_sysmon! ;) )

   1) During some of the slow queries, I have noticed a checkpoint
being run. This does not seem to be consistent across all of them,
however, ASE seems to be checkpointing an inordinate amount.

   2) Slow queries seem to be making a lot more ( x10! ) non-blocking
network checks than the fast ones.

   3) Address Lock Contention seems to be the major cause of process
task switches on both cases, but much more so for the bad queries.

   Any ideas? I have suspected HOUSEKEEPER and CHECKPOINT SLEEP, but
this does not seem to be the case. Also, I have tried switching the
tables in the affected database from "allpages" to "datarow", to no
avail.

   Thank you! :)

David Hunt
---
"Sword, hold thy temper; heart, be wrathful still:
 Priests pray for enemies, but princes kill."

 
 
 

Odd Performance Spikes in ASE 11.9.2

Post by Carl Doa » Sat, 07 Dec 2002 12:36:06



> Greetings,

>    I am encountering an odd problem with an ASE server and thought I
> would poll for any thoughts. :) I'm just starting on this problem, but
> I would appreciate any pointers on things to look at or snap
> judgements, ;)

>    First, let me list my configuration. I am running an 8 processor
> Solaris box with 4 GB of RAM and ASE 11.9.2. The database has no more
> than 10 tables with the largest ( and primary ) table containing
> approximately 10,000 rows. Only one trigger is configured to maintain
> a history for updates to the primary table. No variable length data is
> used in any column; they are all numeric(x,0) and char(x). Load
> overall is very light; no where near the amount ASE could handle. The
> only weak link would seem to be the hosting of the data over NFS. The
> database is also heavily over-indexed ( index-to-data ratio of about
> 24 ).

>    The problem is this : periodically, response times for simple
> updates and queries will go through the roof ( e.g. from .25 seconds
> to 9 seconds ). This seems to happen during periods of low to zero
> usage, but will then persist, sometimes for hours, through periods of
> normal usage.

>    My colleague has done a variety of tests including moving the data
> onto locally hosted drives and increasing the memory for the
> dataserver to a whopping 512 MB. Nothing has worked.

Have you verified the network between your host and the NFS shares?
This is not the preferred method for running DBs.  Moving the data to
local disks is a good idea, but if your queries use tempdb and if tempdb
is on an NFS share, that could be another problem.

Quote:

>    Here are the things I have observed : ( Thank you sp_sysmon! ;) )

>    1) During some of the slow queries, I have noticed a checkpoint
> being run. This does not seem to be consistent across all of them,
> however, ASE seems to be checkpointing an inordinate amount.

The checkpoint process is defaulted at 5 mins (recovery time).  When
there is light activity, the checkpoint process can wake up and write
things to disk.  This sounds normal.

Quote:

>    2) Slow queries seem to be making a lot more ( x10! ) non-blocking
> network checks than the fast ones.

Are your these queries all the same, or are the slower ones doing table
scans?  What are the CPU usages when running the slower queries (is one
of the processors running much higher than the others?)

Quote:

>    3) Address Lock Contention seems to be the major cause of process
> task switches on both cases, but much more so for the bad queries.

If you can attach a sysmon output, maybe we can get a better picture of
what is going on here.

Quote:

>    Any ideas? I have suspected HOUSEKEEPER and CHECKPOINT SLEEP, but
> this does not seem to be the case. Also, I have tried switching the
> tables in the affected database from "allpages" to "datarow", to no
> avail.

>    Thank you! :)

> David Hunt
> ---
> "Sword, hold thy temper; heart, be wrathful still:
>  Priests pray for enemies, but princes kill."

Try running sp_object_stats "hh:mm:ss" (put in a good run time to cover
the time of the problem) and see which tables are experiencing locking
problems.  This could narrow down the possibilities.

You can try sp_monitor to see if you are i/o intensive also.  Don't do
sp_sysmon and sp_monitor at the same time.

If you see the locks taking the 9 seconds, you can try:
      sp_showplan <spid>, null, null, null
to see the query plan.

Just some ideas....

CD

 
 
 

Odd Performance Spikes in ASE 11.9.2

Post by Anthony Mandi » Sun, 08 Dec 2002 22:56:32



> The only weak link would seem to be the hosting of the data over NFS.

        NFS. Say no more.

Quote:>    Here are the things I have observed : ( Thank you sp_sysmon! ;) )

>    1) During some of the slow queries, I have noticed a checkpoint
> being run. This does not seem to be consistent across all of them,
> however, ASE seems to be checkpointing an inordinate amount.

>    2) Slow queries seem to be making a lot more ( x10! ) non-blocking
> network checks than the fast ones.

>    3) Address Lock Contention seems to be the major cause of process
> task switches on both cases, but much more so for the bad queries.

        Monitor your network performance. Are you using cache partitions?

-am     ? 2002

 
 
 

Odd Performance Spikes in ASE 11.9.2

Post by David Hu » Wed, 11 Dec 2002 00:07:29


Hello, everyone.

   Anthony, I have tried local drives for the database and it still
seems to exhibit this behavior. :( However, since both you and Carl
have pointed it out, I am going to shift it to a local drive again.
I'll have to check on the cache partitions with the production DBA; I
only have SA on the test box.

   I ran a robot over the weekend to track performance on the server
while I was home. The test query was massaged to run at transaction
level 0 to see if locking was at the heart of the problem.

   No dice. :( The queriy weighed in at about 5.5 sec instead of 9 sec
without locking. Here is a typical sysmon :

===============================================================================
      Sybase Adaptive Server Enterprise System Performance Report
===============================================================================

Server Version:        Adaptive Server Enterprise/11.9.2/1062/P/SWR
8505 ESD 7
Server Name:           peach5008
Run Date:              Dec 07, 2002
Statistics Cleared at: 11:20:04
Statistics Sampled at: 11:20:09
Sample Interval:       00:00:05

===============================================================================

Kernel Utilization
------------------

  Engine Busy Utilization
    Engine 0                        3.6 %
    Engine 1                       10.9 %
  -----------             ---------------          ----------------
  Summary                 Total    14.5 %          Average    7.2 %

  CPU Yields by Engine            per sec      per xact       count  %
of total
  -------------------------  ------------  ------------  ----------
----------
  Total CPU Yields                    0.0           0.0           0  
   n/a

  Network Checks
    Non-Blocking                  76212.8       76212.8      381064  
  99.1 %
    Blocking                        684.0         684.0        3420  
   0.9 %
  -------------------------  ------------  ------------  ----------
  Total Network I/O Checks        76896.8       76896.8      384484
  Avg Net I/Os per Check              n/a           n/a     0.00030  
   n/a

  Disk I/O Checks
    Total Disk I/O Checks         77012.8       77012.8      385064  
   n/a
    Checks Returning I/O              0.0           0.0           0  
   0.0 %

===============================================================================

Worker Process Management
-------------------------
                                  per sec      per xact       count  %
of total
                             ------------  ------------  ----------
----------
 Worker Process Requests
  Requests Granted                    0.2           0.2           1  
 100.0 %
  Requests Denied                     0.0           0.0           0  
   0.0 %
 --------------------------  ------------  ------------  ----------
  Total Requests                      0.2           0.2           1

  Requests Terminated                 0.0           0.0           0  
   0.0 %

 Worker Process Usage
   Total Used                         0.4           0.4           2  
   n/a
   Max Ever Used During Sample        0.4           0.4           2  
   n/a

 Memory Requests for Worker Processes
   Succeeded                          5.0           5.0          25  
 100.0 %
   Failed                             0.0           0.0           0  
   0.0 %
 --------------------------  ------------  ------------  ----------
   Total Requests                     5.0           5.0          25

 Avg Mem Ever Used by a WP (in bytes) n/a           n/a       704.0  
   n/a

===============================================================================

Parallel Query Management
-------------------------

  Parallel Query Usage            per sec      per xact       count  %
of total
  -------------------------  ------------  ------------  ----------
----------
  Total Parallel Queries              0.2           0.2           1  
   n/a
  WP Adjustments Made
    Due to WP Limit                   0.0           0.0           0  
   0.0 %
    Due to No WPs                     0.0           0.0           0  
   0.0 %

  Merge Lock Requests             per sec      per xact       count  %
of total
  -------------------------  ------------  ------------  ----------
----------
    Network Buffer Merge Locks
      Granted with no wait         1429.6        1429.6        7148  
 100.0 %
      Granted after wait              0.0           0.0           0  
   0.0 %

    Result Buffer Merge Locks
      Granted with no wait            0.0           0.0           0  
   0.0 %
      Granted after wait              0.0           0.0           0  
   0.0 %

    Work Table Merge Locks
      Granted with no wait            0.0           0.0           0  
   0.0 %
      Granted after wait              0.0           0.0           0  
   0.0 %
  -------------------------  ------------  ------------  ----------
  Total # of Requests              1429.6        1429.6        7148

  Sort Buffer Waits               per sec      per xact       count  %
of total
  -------------------------  ------------  ------------  ----------
----------
  Total # of Waits                    0.0           0.0           0  
   n/a

===============================================================================

Task Management                   per sec      per xact       count  %
of total
---------------------------  ------------  ------------  ----------
----------

  Connections Opened                  0.4           0.4           2  
   n/a

  Task Context Switches by Engine
    Engine 0                        866.0         866.0        4330  
  96.2 %
    Engine 1                         34.0          34.0         170  
   3.8 %
  -------------------------  ------------  ------------  ----------
    Total Task Switches:            900.0         900.0        4500

  Task Context Switches Due To:
    Voluntary Yields                  3.0           3.0          15  
   0.3 %
    Cache Search Misses               0.0           0.0           0  
   0.0 %
    System Disk Writes                1.4           1.4           7  
   0.2 %
    I/O Pacing                        2.0           2.0          10  
   0.2 %
    Logical Lock Contention           0.0           0.0           0  
   0.0 %
    Address Lock Contention           0.0           0.0           0  
   0.0 %
    Log Semaphore Contention          0.0           0.0           0  
   0.0 %
    Group Commit Sleeps               0.0           0.0           0  
   0.0 %
    Last Log Page Writes              2.2           2.2          11  
   0.2 %
    Modify Conflicts                  0.0           0.0           0  
   0.0 %
    I/O Device Contention             0.0           0.0           0  
   0.0 %
    Network Packet Received           4.6           4.6          23  
   0.5 %
    Network Packet Sent              18.8          18.8          94  
   2.1 %
    SYSINDEXES Lookup                 0.0           0.0           0  
   0.0 %
    Other Causes                    868.0         868.0        4340  
  96.4 %

===============================================================================

Application Management
----------------------

  Application Statistics Summary (All Applications)
  -------------------------------------------------
  Priority Changes                per sec      per xact       count  %
of total
  -------------------------  ------------  ------------  ----------
----------
    To High Priority                  0.0           0.0           0  
   0.0 %
    To Medium Priority                0.6           0.6           3  
  75.0 %
    To Low Priority                   0.2           0.2           1  
  25.0 %
  -------------------------  ------------  ------------  ----------
  Total Priority Changes              0.8           0.8           4

  Allotted Slices Exhausted       per sec      per xact       count  %
of total
  -------------------------  ------------  ------------  ----------
----------
    High Priority                     0.0           0.0           0  
   0.0 %
    Medium Priority                   0.4           0.4           2  
 100.0 %
    Low Priority                      0.0           0.0           0  
   0.0 %
  -------------------------  ------------  ------------  ----------
  Total Slices Exhausted              0.4           0.4           2

  Skipped Tasks By Engine         per sec      per xact       count  %
of total
  -------------------------  ------------  ------------  ----------
----------
  Total Engine Skips                  0.0           0.0           0  
   n/a

  Engine Scope Changes                0.0           0.0           0  
   n/a

===============================================================================

ESP Management                    per sec      per xact       count  %
of total
---------------------------  ------------  ------------  ----------
----------
  ESP Requests                        0.0           0.0           0  
   n/a
===============================================================================

Housekeeper Task Activity
-------------------------
                                  per sec      per xact       count  %
of total
                             ------------  ------------  ----------
Garbage Collections                  0.2           0.2           1    
  n/a
Pages Processed in GC                0.0           0.0           0    
  n/a

Statistics Updates                   0.2           0.2           1    
  n/a

===============================================================================

Monitor Access to Executing SQL
-------------------------------
                                  per sec      per xact       count  %
of total
                             ------------  ------------  ----------
----------
 Waits on Execution Plans            0.0           0.0           0    
  n/a
...

read more »

 
 
 

Odd Performance Spikes in ASE 11.9.2

Post by Carl Doa » Fri, 13 Dec 2002 11:42:52


Sorry for taking so long to answer, I missed this thread the last few days.

What is your default packet size set at and is TCP nodelay set to 1?
This won't make the huge difference, but it can help in the overall
performance throughout the day.

I am concerned about your attachment.  Did you edit the output?  The
cache section is incomplete (or there is something wrong with your
system), and your devices section all show the same read/write amounts.

You had several dirty reads take place.  I would investigate the caches
at this point.  Do you have a better snap shot of cache performance?

Carl

David Hunt wrote:
> Hello, everyone.

>    Anthony, I have tried local drives for the database and it still
> seems to exhibit this behavior. :( However, since both you and Carl
> have pointed it out, I am going to shift it to a local drive again.
> I'll have to check on the cache partitions with the production DBA; I
> only have SA on the test box.

>    I ran a robot over the weekend to track performance on the server
> while I was home. The test query was massaged to run at transaction
> level 0 to see if locking was at the heart of the problem.

>    No dice. :( The queriy weighed in at about 5.5 sec instead of 9 sec
> without locking. Here is a typical sysmon :

> ===============================================================================
>       Sybase Adaptive Server Enterprise System Performance Report
> ===============================================================================

> Server Version:        Adaptive Server Enterprise/11.9.2/1062/P/SWR
> 8505 ESD 7
> Server Name:           peach5008
> Run Date:              Dec 07, 2002
> Statistics Cleared at: 11:20:04
> Statistics Sampled at: 11:20:09
> Sample Interval:       00:00:05

> ===============================================================================

> Kernel Utilization
> ------------------

>   Engine Busy Utilization
>     Engine 0                        3.6 %
>     Engine 1                       10.9 %
>   -----------             ---------------          ----------------
>   Summary                 Total    14.5 %          Average    7.2 %

>   CPU Yields by Engine            per sec      per xact       count  %
> of total
>   -------------------------  ------------  ------------  ----------
> ----------
>   Total CPU Yields                    0.0           0.0           0  
>    n/a

>   Network Checks
>     Non-Blocking                  76212.8       76212.8      381064  
>   99.1 %
>     Blocking                        684.0         684.0        3420  
>    0.9 %
>   -------------------------  ------------  ------------  ----------
>   Total Network I/O Checks        76896.8       76896.8      384484
>   Avg Net I/Os per Check              n/a           n/a     0.00030  
>    n/a

>   Disk I/O Checks
>     Total Disk I/O Checks         77012.8       77012.8      385064  
>    n/a
>     Checks Returning I/O              0.0           0.0           0  
>    0.0 %

> ===============================================================================

> Worker Process Management
> -------------------------
>                                   per sec      per xact       count  %
> of total
>                              ------------  ------------  ----------
> ----------
>  Worker Process Requests
>   Requests Granted                    0.2           0.2           1  
>  100.0 %
>   Requests Denied                     0.0           0.0           0  
>    0.0 %
>  --------------------------  ------------  ------------  ----------
>   Total Requests                      0.2           0.2           1

>   Requests Terminated                 0.0           0.0           0  
>    0.0 %

>  Worker Process Usage
>    Total Used                         0.4           0.4           2  
>    n/a
>    Max Ever Used During Sample        0.4           0.4           2  
>    n/a

>  Memory Requests for Worker Processes
>    Succeeded                          5.0           5.0          25  
>  100.0 %
>    Failed                             0.0           0.0           0  
>    0.0 %
>  --------------------------  ------------  ------------  ----------
>    Total Requests                     5.0           5.0          25

>  Avg Mem Ever Used by a WP (in bytes) n/a           n/a       704.0  
>    n/a

> ===============================================================================

> Parallel Query Management
> -------------------------

>   Parallel Query Usage            per sec      per xact       count  %
> of total
>   -------------------------  ------------  ------------  ----------
> ----------
>   Total Parallel Queries              0.2           0.2           1  
>    n/a
>   WP Adjustments Made
>     Due to WP Limit                   0.0           0.0           0  
>    0.0 %
>     Due to No WPs                     0.0           0.0           0  
>    0.0 %

>   Merge Lock Requests             per sec      per xact       count  %
> of total
>   -------------------------  ------------  ------------  ----------
> ----------
>     Network Buffer Merge Locks
>       Granted with no wait         1429.6        1429.6        7148  
>  100.0 %
>       Granted after wait              0.0           0.0           0  
>    0.0 %

>     Result Buffer Merge Locks
>       Granted with no wait            0.0           0.0           0  
>    0.0 %
>       Granted after wait              0.0           0.0           0  
>    0.0 %

>     Work Table Merge Locks
>       Granted with no wait            0.0           0.0           0  
>    0.0 %
>       Granted after wait              0.0           0.0           0  
>    0.0 %
>   -------------------------  ------------  ------------  ----------
>   Total # of Requests              1429.6        1429.6        7148

>   Sort Buffer Waits               per sec      per xact       count  %
> of total
>   -------------------------  ------------  ------------  ----------
> ----------
>   Total # of Waits                    0.0           0.0           0  
>    n/a

> ===============================================================================

> Task Management                   per sec      per xact       count  %
> of total
> ---------------------------  ------------  ------------  ----------
> ----------

>   Connections Opened                  0.4           0.4           2  
>    n/a

>   Task Context Switches by Engine
>     Engine 0                        866.0         866.0        4330  
>   96.2 %
>     Engine 1                         34.0          34.0         170  
>    3.8 %
>   -------------------------  ------------  ------------  ----------
>     Total Task Switches:            900.0         900.0        4500

>   Task Context Switches Due To:
>     Voluntary Yields                  3.0           3.0          15  
>    0.3 %
>     Cache Search Misses               0.0           0.0           0  
>    0.0 %
>     System Disk Writes                1.4           1.4           7  
>    0.2 %
>     I/O Pacing                        2.0           2.0          10  
>    0.2 %
>     Logical Lock Contention           0.0           0.0           0  
>    0.0 %
>     Address Lock Contention           0.0           0.0           0  
>    0.0 %
>     Log Semaphore Contention          0.0           0.0           0  
>    0.0 %
>     Group Commit Sleeps               0.0           0.0           0  
>    0.0 %
>     Last Log Page Writes              2.2           2.2          11  
>    0.2 %
>     Modify Conflicts                  0.0           0.0           0  
>    0.0 %
>     I/O Device Contention             0.0           0.0           0  
>    0.0 %
>     Network Packet Received           4.6           4.6          23  
>    0.5 %
>     Network Packet Sent              18.8          18.8          94  
>    2.1 %
>     SYSINDEXES Lookup                 0.0           0.0           0  
>    0.0 %
>     Other Causes                    868.0         868.0        4340  
>   96.4 %

> ===============================================================================

> Application Management
> ----------------------

>   Application Statistics Summary (All Applications)
>   -------------------------------------------------
>   Priority Changes                per sec      per xact       count  %
> of total
>   -------------------------  ------------  ------------  ----------
> ----------
>     To High Priority                  0.0           0.0           0  
>    0.0 %
>     To Medium Priority                0.6           0.6           3  
>   75.0 %
>     To Low Priority                   0.2           0.2           1  
>   25.0 %
>   -------------------------  ------------  ------------  ----------
>   Total Priority Changes              0.8           0.8           4

>   Allotted Slices Exhausted       per sec      per xact       count  %
> of total
>   -------------------------  ------------  ------------  ----------
> ----------
>     High Priority                     0.0           0.0           0  
>    0.0 %
>     Medium Priority                   0.4           0.4           2  
>  100.0 %
>     Low Priority                      0.0           0.0           0  
>    0.0 %
>   -------------------------  ------------  ------------  ----------
>   Total Slices Exhausted              0.4           0.4           2

>   Skipped Tasks By Engine         per sec      per xact       count  %
> of total
>   -------------------------  ------------  ------------  ----------
> ----------
>   Total Engine Skips                  0.0           0.0           0  
>    n/a

>   Engine Scope Changes                0.0           0.0           0  
>    n/a

> ===============================================================================

> ESP Management                  

...

read more »

 
 
 

Odd Performance Spikes in ASE 11.9.2

Post by hr » Sat, 14 Dec 2002 04:06:07


A few more things to check:
- When the queries and updates are running slow, have you verified
that they are using the same showplan and doing the same amount of IO
as when they are running fast, and if the same number of IOs, whether
there is a similar split of logical and physical;
- Have you checked that the dataserver definitely allocated the extra
memory correctly and is locked into ISM;
- If I understand correctly, the queries run either slow or fast in a
fairly reproduceable way for a while. That being the case, you could
create benchmarks which do not access any of the over-indexed tables
or even any tables at all, to rule out some of the possible issues
with statistics and disk access.
- What other loading is on the machine?

Regards,
hra

Carl Doan <carl-d...@cinci.rr.com> wrote in message <news:3DF7F629.1070805@cinci.rr.com>...
> Sorry for taking so long to answer, I missed this thread the last few days.

> What is your default packet size set at and is TCP nodelay set to 1?
> This won't make the huge difference, but it can help in the overall
> performance throughout the day.

> I am concerned about your attachment.  Did you edit the output?  The
> cache section is incomplete (or there is something wrong with your
> system), and your devices section all show the same read/write amounts.

> You had several dirty reads take place.  I would investigate the caches
> at this point.  Do you have a better snap shot of cache performance?

> Carl

> David Hunt wrote:
> > Hello, everyone.

> >    Anthony, I have tried local drives for the database and it still
> > seems to exhibit this behavior. :( However, since both you and Carl
> > have pointed it out, I am going to shift it to a local drive again.
> > I'll have to check on the cache partitions with the production DBA; I
> > only have SA on the test box.

> >    I ran a robot over the weekend to track performance on the server
> > while I was home. The test query was massaged to run at transaction
> > level 0 to see if locking was at the heart of the problem.

> >    No dice. :( The queriy weighed in at about 5.5 sec instead of 9 sec
> > without locking. Here is a typical sysmon :

> > ===============================================================================
> >       Sybase Adaptive Server Enterprise System Performance Report
> > ===============================================================================

> > Server Version:        Adaptive Server Enterprise/11.9.2/1062/P/SWR
> > 8505 ESD 7
> > Server Name:           peach5008
> > Run Date:              Dec 07, 2002
> > Statistics Cleared at: 11:20:04
> > Statistics Sampled at: 11:20:09
> > Sample Interval:       00:00:05

> > ===============================================================================

> > Kernel Utilization
> > ------------------

> >   Engine Busy Utilization
> >     Engine 0                        3.6 %
> >     Engine 1                       10.9 %
> >   -----------             ---------------          ----------------
> >   Summary                 Total    14.5 %          Average    7.2 %

> >   CPU Yields by Engine            per sec      per xact       count  %
> > of total
> >   -------------------------  ------------  ------------  ----------
> > ----------
> >   Total CPU Yields                    0.0           0.0           0  
> >    n/a

> >   Network Checks
> >     Non-Blocking                  76212.8       76212.8      381064  
> >   99.1 %
> >     Blocking                        684.0         684.0        3420  
> >    0.9 %
> >   -------------------------  ------------  ------------  ----------
> >   Total Network I/O Checks        76896.8       76896.8      384484
> >   Avg Net I/Os per Check              n/a           n/a     0.00030  
> >    n/a

> >   Disk I/O Checks
> >     Total Disk I/O Checks         77012.8       77012.8      385064  
> >    n/a
> >     Checks Returning I/O              0.0           0.0           0  
> >    0.0 %

> > ===============================================================================

> > Worker Process Management
> > -------------------------
> >                                   per sec      per xact       count  %
> > of total
> >                              ------------  ------------  ----------
> > ----------
> >  Worker Process Requests
> >   Requests Granted                    0.2           0.2           1  
> >  100.0 %
> >   Requests Denied                     0.0           0.0           0  
> >    0.0 %
> >  --------------------------  ------------  ------------  ----------
> >   Total Requests                      0.2           0.2           1

> >   Requests Terminated                 0.0           0.0           0  
> >    0.0 %

> >  Worker Process Usage
> >    Total Used                         0.4           0.4           2  
> >    n/a
> >    Max Ever Used During Sample        0.4           0.4           2  
> >    n/a

> >  Memory Requests for Worker Processes
> >    Succeeded                          5.0           5.0          25  
> >  100.0 %
> >    Failed                             0.0           0.0           0  
> >    0.0 %
> >  --------------------------  ------------  ------------  ----------
> >    Total Requests                     5.0           5.0          25

> >  Avg Mem Ever Used by a WP (in bytes) n/a           n/a       704.0  
> >    n/a

> > ===============================================================================

> > Parallel Query Management
> > -------------------------

> >   Parallel Query Usage            per sec      per xact       count  %
> > of total
> >   -------------------------  ------------  ------------  ----------
> > ----------
> >   Total Parallel Queries              0.2           0.2           1  
> >    n/a
> >   WP Adjustments Made
> >     Due to WP Limit                   0.0           0.0           0  
> >    0.0 %
> >     Due to No WPs                     0.0           0.0           0  
> >    0.0 %

> >   Merge Lock Requests             per sec      per xact       count  %
> > of total
> >   -------------------------  ------------  ------------  ----------
> > ----------
> >     Network Buffer Merge Locks
> >       Granted with no wait         1429.6        1429.6        7148  
> >  100.0 %
> >       Granted after wait              0.0           0.0           0  
> >    0.0 %

> >     Result Buffer Merge Locks
> >       Granted with no wait            0.0           0.0           0  
> >    0.0 %
> >       Granted after wait              0.0           0.0           0  
> >    0.0 %

> >     Work Table Merge Locks
> >       Granted with no wait            0.0           0.0           0  
> >    0.0 %
> >       Granted after wait              0.0           0.0           0  
> >    0.0 %
> >   -------------------------  ------------  ------------  ----------
> >   Total # of Requests              1429.6        1429.6        7148

> >   Sort Buffer Waits               per sec      per xact       count  %
> > of total
> >   -------------------------  ------------  ------------  ----------
> > ----------
> >   Total # of Waits                    0.0           0.0           0  
> >    n/a

> > ===============================================================================

> > Task Management                   per sec      per xact       count  %
> > of total
> > ---------------------------  ------------  ------------  ----------
> > ----------

> >   Connections Opened                  0.4           0.4           2  
> >    n/a

> >   Task Context Switches by Engine
> >     Engine 0                        866.0         866.0        4330  
> >   96.2 %
> >     Engine 1                         34.0          34.0         170  
> >    3.8 %
> >   -------------------------  ------------  ------------  ----------
> >     Total Task Switches:            900.0         900.0        4500

> >   Task Context Switches Due To:
> >     Voluntary Yields                  3.0           3.0          15  
> >    0.3 %
> >     Cache Search Misses               0.0           0.0           0  
> >    0.0 %
> >     System Disk Writes                1.4           1.4           7  
> >    0.2 %
> >     I/O Pacing                        2.0           2.0          10  
> >    0.2 %
> >     Logical Lock Contention           0.0           0.0           0  
> >    0.0 %
> >     Address Lock Contention           0.0           0.0           0  
> >    0.0 %
> >     Log Semaphore Contention          0.0           0.0           0  
> >    0.0 %
> >     Group Commit Sleeps               0.0           0.0           0  
> >    0.0 %
> >     Last Log Page Writes              2.2           2.2          11  
> >    0.2 %
> >     Modify Conflicts                  0.0           0.0           0  
> >    0.0 %
> >     I/O Device Contention             0.0           0.0           0  
> >    0.0 %
> >     Network Packet Received           4.6           4.6          23  
> >    0.5 %
> >     Network Packet Sent              18.8          18.8          94  
> >    2.1 %
> >     SYSINDEXES Lookup                 0.0           0.0           0  
> >    0.0 %
> >     Other Causes                    868.0         868.0        4340  
> >   96.4 %

> > ===============================================================================

> > Application Management
> > ----------------------

> >   Application Statistics Summary (All Applications)
> >   -------------------------------------------------
> >   Priority Changes                per sec      per xact       count  %
> > of total
> >   -------------------------  ------------  ------------  ----------
> > ----------
> >     To High Priority                  0.0           0.0           0  
> >    0.0 %
> >     To Medium Priority            

...

read more »

 
 
 

Odd Performance Spikes in ASE 11.9.2

Post by David Hu » Sun, 15 Dec 2002 01:41:02


Good afternoon, everyone!

Sorry; I've had a bad cold the past couple of days. All right-y :


Quote:> A few more things to check:
> - When the queries and updates are running slow, have you verified
> that they are using the same showplan and doing the same amount of IO
> as when they are running fast, and if the same number of IOs, whether
> there is a similar split of logical and physical;

Nope; I will look into it and get back to you.

Quote:> - Have you checked that the dataserver definitely allocated the extra
> memory correctly and is locked into ISM;

I'm pretty sure it has. Again, I will try to get definitive proof.

Quote:> - If I understand correctly, the queries run either slow or fast in a
> fairly reproduceable way for a while. That being the case, you could
> create benchmarks which do not access any of the over-indexed tables
> or even any tables at all, to rule out some of the possible issues
> with statistics and disk access.

Good point.

Quote:> - What other loading is on the machine?

Only Sybase. The machine is designed as a gargantuan database server,
so there are a 11 dataservers running, 5 xpserverss, and 11
backupservers.

Quote:> Carl Doan said :
> > What is your default packet size set at and is TCP nodelay set to 1?
> > This won't make the huge difference, but it can help in the overall
> > performance throughout the day.

Min/max packet size is at 512 and nodelay is off. I'll muck around and
tune them, tell you what happens! :)

Quote:

> > I am concerned about your attachment.  Did you edit the output?  The
> > cache section is incomplete (or there is something wrong with your
> > system), and your devices section all show the same read/write amounts.

I did not edit the output. What is missing? I'm at a loss to explain
the read/write symmetry...

Quote:

> > You had several dirty reads take place.  I would investigate the caches
> > at this point.  Do you have a better snap shot of cache performance?

The fact about the reads/writes and incomple section makes me worry
about this. I'll give it a good hard gander.

Thank you all so much! Hopefully, I'll wrap this up over the weekend
and be able to give the nitty-gritty details on Monday... :)

David Hunt

 
 
 

Odd Performance Spikes in ASE 11.9.2

Post by Anthony Mandi » Sun, 15 Dec 2002 22:36:29



> > - What other loading is on the machine?

> Only Sybase. The machine is designed as a gargantuan database server,
> so there are a 11 dataservers running, 5 xpserverss, and 11
> backupservers.

        You could share the XP and Backup Servers between them - but
        that might depend on the load. They don't use much memory in
        themselves so its probably a non-issue anyway.

-am     ? 2002

 
 
 

Odd Performance Spikes in ASE 11.9.2

Post by Michael Pepple » Mon, 16 Dec 2002 06:36:30




>> > - What other loading is on the machine?

>> Only Sybase. The machine is designed as a gargantuan database server,
>> so there are a 11 dataservers running, 5 xpserverss, and 11
>> backupservers.

>    You could share the XP and Backup Servers between them - but that might
>    depend on the load. They don't use much memory in themselves so its
>    probably a non-issue anyway.

Actually you can't share the XP server - it is tied to the parent ASE by
the server name (a hack that I guess is used as some sort of minimal
security mechanism).

Michael
--
Michael Peppler                              Data Migrations, Inc.

Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or
long term contract positions - http://www.mbay.net/~mpeppler/resume.html

 
 
 

Odd Performance Spikes in ASE 11.9.2

Post by Anthony Mandi » Mon, 16 Dec 2002 17:30:22



> Actually you can't share the XP server - it is tied to the parent ASE by
> the server name (a hack that I guess is used as some sort of minimal
> security mechanism).

        Ah, yes, that's right. I forgot about the hack. I wonder if
        you could have the multiple names (reflecting the server name)
        with each entry having the same port number in the interfaces
        file and only one real one running. Not sure what the security
        implications are but this would be easy enough to test - you'd
        just need two server instances to try it out.

-am     ? 2002

 
 
 

Odd Performance Spikes in ASE 11.9.2

Post by Rob Verschoo » Mon, 16 Dec 2002 20:52:57




> > Actually you can't share the XP server - it is tied to the parent ASE
by
> > the server name (a hack that I guess is used as some sort of minimal
> > security mechanism).

> Ah, yes, that's right. I forgot about the hack. I wonder if
> you could have the multiple names (reflecting the server name)
> with each entry having the same port number in the interfaces
> file and only one real one running. Not sure what the security
> implications are but this would be easy enough to test - you'd
> just need two server instances to try it out.

> -am ? 2002

Well, you can change the mapping in sysservers so that your own ASE's XP
servername actually maps onto another ASE's XP server.
I tried this a few years ago, but it didn't work: basically the problem was
that the XP server went wild for 2 minutes or so, taking 100% CPU and then
died. I didn't try it anymore afterwards...

HTH,

Rob
-------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0

Author of "The Complete Sybase ASE Quick Reference Guide"
Online orders accepted at http://www.sypron.nl/qr


http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------

 
 
 

1. Serous performance problems on ASE 11.5.0

Hi everybody,
until  this week we were running an Datawarehouse against a Sybase SQL
Sever 11.0.2.2 om Digital Unix 4.0b with a total of 160 Mb for the
server where 80 Mb are assigned to datacache and we had good
performances.
On last Friday we migrated to ASE 11.5.0 and EBF 7751 without any
changes to configuration parameters, and now we encounter havy
performance problems. We are querying via C program and cursor on 4
joined tables and now it is almost impossible to run more than 2
extractions on that server at the same time.
Did somebody  encounter the same problems and what where their
solutions. Every suggestion is welcomed

2. Need advise using Delphi with MS Access

3. ODBC recordset performance ASE 11.9.2 versus 12.0 (select method cursor)

4. cross platform dump/load

5. ASE 11.9.3 stored procedure performance problem

6. Replication over WAN

7. ASE 11.9.2 on Solaris 2.6, Character vs Block device performance

8. Backup & Restore Utilities Using VB

9. ASE 11.5.1 performance experience

10. bcp in Ase 11.5.1 syslogins to ASE 12.0 not working

11. Migrating dbs from ASE 11.0.3.3 (desupported) to ASE 12.x

12. Newbie: Sybase ASE 11.9.2 vs 11.5.1

13. patches for ASE 11.5.1 on HPUX 11