Very Computer

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."

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

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

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 »

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 »

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 »

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

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

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

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

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
-------------------------------------------------------------

Very Computer

Odd Performance Spikes in ASE 11.9.2

Odd Performance Spikes in ASE 11.9.2

Odd Performance Spikes in ASE 11.9.2

Odd Performance Spikes in ASE 11.9.2

Odd Performance Spikes in ASE 11.9.2

Odd Performance Spikes in ASE 11.9.2

Odd Performance Spikes in ASE 11.9.2

Odd Performance Spikes in ASE 11.9.2

Odd Performance Spikes in ASE 11.9.2

Odd Performance Spikes in ASE 11.9.2

Odd Performance Spikes in ASE 11.9.2

Odd Performance Spikes in ASE 11.9.2