DB Performance problems under 10.20 - shmmax?

DB Performance problems under 10.20 - shmmax?

Post by davidpoy.. » Wed, 02 Aug 2000 04:00:00



We have severe database performance problems on a K450 with 4 x 160MHz CPUs,
2GB memory and 3 x Model 20 (Nike) disk arrays having 4GB disks. These are
serviced by two FWD busses. No VM activity to speak of.

There are 14 LUNS spread across these arrays, mainly RAID 1. The logical
Sybase devices (raw disk) are arranged over many LUNs to spread the load.
However, two LUNs are taking over 90% of the load! These are in the same
array, but on separate SP controllers. Other disks are barely touched. Read
cache is 50MB per SP and write is 10MB, chosen due to the heavy
read-intensive application (Arbor/BP running on a separate server).

shmmax has been upped from the 10.20 'maximum' of 1GB to 1.74GB - Sybase is
using almost all of this in a couple of big chunks: 1GB & 700MB and a few
smaller ones, according to ipcs -mob.

A simple select count (*) takes forever to return and hits lots of disks
hard, whereas the same query takes 2 minutes on a less powerful K250 with a
dump/loaded copy of the DB.

adb does not run - reason unknown. Simply returns a command prompt.

Now, have we screwed up by setting shmmax too high? If we reset it to 1GB
will Sybase be able to use multiple segments?

Any help gratefully received - thanks in advance.

David Poyner

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

DB Performance problems under 10.20 - shmmax?

Post by Mark Green » Wed, 02 Aug 2000 04:00:00




> We have severe database performance problems on a K450 with 4 x 160MHz
CPUs,
> 2GB memory and 3 x Model 20 (Nike) disk arrays having 4GB disks. These
are
> serviced by two FWD busses. No VM activity to speak of.

> There are 14 LUNS spread across these arrays, mainly RAID 1. The
logical
> Sybase devices (raw disk) are arranged over many LUNs to spread the
load.
> However, two LUNs are taking over 90% of the load! These are in the
same
> array, but on separate SP controllers. Other disks are barely touched.
Read
> cache is 50MB per SP and write is 10MB, chosen due to the heavy
> read-intensive application (Arbor/BP running on a separate server).

Have you tried running from within Sybase sp_sysmon to see what
databases/tables Sybase is utilizing on those LUNS?

--
mark

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

DB Performance problems under 10.20 - shmmax?

Post by Tarjei T. Jense » Thu, 03 Aug 2000 04:00:00



>We have severe database performance problems on a K450 with 4 x 160MHz CPUs,
>2GB memory and 3 x Model 20 (Nike) disk arrays having 4GB disks. These are
>serviced by two FWD busses. No VM activity to speak of.

>There are 14 LUNS spread across these arrays, mainly RAID 1. The logical
>Sybase devices (raw disk) are arranged over many LUNs to spread the load.
>However, two LUNs are taking over 90% of the load! These are in the same
>array, but on separate SP controllers. Other disks are barely touched. Read
>cache is 50MB per SP and write is 10MB, chosen due to the heavy
>read-intensive application (Arbor/BP running on a separate server).

[snip]

Quote:>A simple select count (*) takes forever to return and hits lots of disks
>hard, whereas the same query takes 2 minutes on a less powerful K250 with a
>dump/loaded copy of the DB.

Have you checked your memory usage. E.g. how much of the memory is actually
used, how much memory is allocated for file system buffering (dbc_max_pct) and
is maxdsize sensibly large?

It might be useful to attend a HP-UX specific tuning course for oracle. That
may give you the neccessary hints.

 
 
 

DB Performance problems under 10.20 - shmmax?

Post by davidpoy.. » Thu, 03 Aug 2000 04:00:00


Yes, there are two tables being hit very hard: BMF_DISTRIBUTION &
CMF_BALANCE. Kenan doesn't see a problem with the sizes.

David.



Quote:> Have you tried running from within Sybase sp_sysmon to see what
> databases/tables Sybase is utilizing on those LUNS?

> --
> mark

> Sent via Deja.com http://www.deja.com/
> Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.
 
 
 

DB Performance problems under 10.20 - shmmax?

Post by davidpoy.. » Thu, 03 Aug 2000 04:00:00


Almost all of our 2GB is used. We are using using raw disks for all DB
devices except tempdb (which is hardly being touched at all), so buffer
cache doesn't really come into the equation (or does it?). dbc_max_pct
is set to 20, but is not growing above 100MB anyhow.

Do HP run a course for Sybase?

David.



Quote:> Have you checked your memory usage. E.g. how much of the memory is
actually
> used, how much memory is allocated for file system buffering
(dbc_max_pct) and
> is maxdsize sensibly large?

> It might be useful to attend a HP-UX specific tuning course for
oracle. That
> may give you the neccessary hints.

Sent via Deja.com http://www.deja.com/
Before you buy.
 
 
 

DB Performance problems under 10.20 - shmmax?

Post by Mark Green » Thu, 03 Aug 2000 04:00:00




> Yes, there are two tables being hit very hard: BMF_DISTRIBUTION &
> CMF_BALANCE. Kenan doesn't see a problem with the sizes.

> David.

Ok, some general questions with regard to these tables:

 - are they spread over multiple LUNS?
 - How many pages are they?
 - How many rows (i.e., how "full" is the table when compared to the
physical space they take)?
 - how many clustered indices does each have? How many non-clustered?
 - Are the Sybase devices on which they reside mirrored by Sybase, and
if so what LUNS are the mirrors on?
 - Are the LUNS involved all on the same SCSI controller?
 - What does vmstat tell you, particularly the iowait?

--
mark

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

DB Performance problems under 10.20 - shmmax?

Post by Da.. » Fri, 04 Aug 2000 04:00:00


I forgot to mention: maxdsiz is set to 96MB.

Thanks for your interest!



Quote:> Have you checked your memory usage. E.g. how much of the memory is
actually
> used, how much memory is allocated for file system buffering
(dbc_max_pct) and
> is maxdsize sensibly large?

Sent via Deja.com http://www.deja.com/
Before you buy.
 
 
 

DB Performance problems under 10.20 - shmmax?

Post by Tarjei T. Jense » Sat, 05 Aug 2000 04:00:00



>Almost all of our 2GB is used. We are using using raw disks for all DB
>devices except tempdb (which is hardly being touched at all), so buffer
>cache doesn't really come into the equation (or does it?). dbc_max_pct
>is set to 20, but is not growing above 100MB anyhow.

2GB is not be that much. Perhaps you need more memory.

BTW. we have set dbc_max_pct to 10. Since we want oracle to use the memory. If
we used raw devices we would probably reduce that to around 5 %.

All I can do is to advice you to study sar -b, sar -d and sar output. You
should probably make sure you have a resolution of at least 15 minutes on the
one that get stored by the system. The shorter the interval is, the better you
are able to se how performance varies. In addtion you should do some
measurements by hand with a resolution of around 5 to 60 seconds, depending on
how well you are doing. E.g. "sar -b 5 5".

You should probably also try out iostat as well.

We have glance which allows us to watch various parameters on the system on a X
display. My main use of this is to check how memory is allocated.

Quote:>Do HP run a course for Sybase?

Don't know.

Greetings,

 
 
 

1. What happens if SHMMAX is set GT 132k (ie: SHMMAX=200,000,000) on HPUX 10.x

Has anyone ever set the SHMMAX value this high? Any speculation or
historical information on side effects of such a setting? Does HP even
support?

I have an application which requires a large contigious chunk of Sharded
Memory and know that NCR has given an OK to similar large allocations
(ie approx 200 Meg).

------------------------------------------------------------------------
Fred Hebbel, Hebbel Consulting, Inc. (Distributed OO Architecture &
Design)

PO Box 41624, St. Petersburg, FL 33743-1624 Phone:(813)398-6099
FAX:(813)398-5928

2. External commands: paragraph splitter for SPOOL.CMD and file splitter

3. Clone AIX ORACLE DB to HP ORACLE DB???

4. Twilight with Icons with 5.2?

5. shmmax problem

6. Books on Warp/Internet Package

7. same ndbm dbs on both hpux 9.05 and 10.20?

8. Cisco 2509 and USR modem pool

9. db 1.85 and HP-UX 10.20 for Sendmail 8.8.6

10. Building Berkeley 'db' on HP-UX 10.20?

11. Performance problem with HP-UX 10.20

12. How to solve IO-Bound problem (Progress DB)

13. 2.75GB shmmax and oracle