DB dumps/backups slows the Database server

DB dumps/backups slows the Database server

Post by Maj Sweid » Sat, 27 Jul 2002 06:03:52



Heres a good question for the AIX tuning experts!!

I have an M80 model RS6000 running AIX 4.3.3.10. 4 processors. 8GB of
RAM. 2 Sybase servers using up 2GB of memory each. I have Async AIO
min/max set to 1/10. Swap space set at 12GB on 2 internal disks(hdisk0
and hdisk1).
Swap space is 35% used.
The Sybase servers are about 15GB in size each.

The db and dump devices are setting on an EMC raid5 disk array. The
AIX server has 2 disks/LUNs defined with 60GB each(hdisk2 and 3). Both
the db devices and backup/dump directory are on the raid disks.

When the Sybase db dump runs, the Sybase server slows down to a halt.
I'm running Sybase ASE version 12.0.0.4 with latest fix release. When
I cancel the Sybase backup, the Sybase db goes back to normal response
time.

The iostats below show that the hdisk0 & 1 are 100% busy!! It doesn't
look like the SAN disks are a bottle neck here, but IOWAIT sure looks
high to me.

tty:      tin         tout   avg-cpu:  % user    % sys     % idle    %
iowait
          0.6        966.0              26.5      3.2        0.0    
70.3
Disks:        % tm_act     Kbps      tps    Kb_read   Kb_wrtn
hdisk1         100.1     803.4     201.4         52      3985
hdisk0         100.1     812.9     203.8         84      4001
cd0              0.0       0.0       0.0          0         0
hdisk2           4.4     493.5      41.6       2384        96
hdisk3           1.6     571.5      16.9          0      2872

1) Sybase recommends having number of AIO servers set same as number
of disks. All I have is 4 disks defined, So, shouldn't a max of 10 aio
async servers be suffient?
2) I'm aware from oracle DBAs that they set max aio servers to
#of_CPUs*10? Has anobody noticed improvements with higher number of
aio servers?
3) Does increasing "max num of requests" for AIO make a difference
here?
4) Anybody has any suggestions for tunning or lowering down the bad
affect of the Sybase backups running?
Thanks
Maj

 
 
 

DB dumps/backups slows the Database server

Post by Claudiu Costi » Sat, 27 Jul 2002 08:21:58


**** Post for FREE via your newsreader at post.usenet.com ****

 Hi Maj,

 It's very clear that when you backup/dump
the database is _trashing_! From hdisk2 & hdisk3
it's clear there's no signifiant DB activity.
I suggest a
        while true; do ps -a; sleep 3; done
and see how swap consume increase/decrease.
Also where do you make dump? What activity is
there?


> Heres a good question for the AIX tuning experts!!

> I have an M80 model RS6000 running AIX 4.3.3.10. 4 processors. 8GB of
> RAM. 2 Sybase servers using up 2GB of memory each. I have Async AIO
> min/max set to 1/10. Swap space set at 12GB on 2 internal disks(hdisk0
> and hdisk1).
> Swap space is 35% used.
> The Sybase servers are about 15GB in size each.

> The db and dump devices are setting on an EMC raid5 disk array. The
> AIX server has 2 disks/LUNs defined with 60GB each(hdisk2 and 3). Both
> the db devices and backup/dump directory are on the raid disks.

> When the Sybase db dump runs, the Sybase server slows down to a halt.
> I'm running Sybase ASE version 12.0.0.4 with latest fix release. When
> I cancel the Sybase backup, the Sybase db goes back to normal response
> time.

> The iostats below show that the hdisk0 & 1 are 100% busy!! It doesn't
> look like the SAN disks are a bottle neck here, but IOWAIT sure looks
> high to me.

> tty:      tin         tout   avg-cpu:  % user    % sys     % idle    %
> iowait
>           0.6        966.0              26.5      3.2        0.0
> 70.3
> Disks:        % tm_act     Kbps      tps    Kb_read   Kb_wrtn
> hdisk1         100.1     803.4     201.4         52      3985
> hdisk0         100.1     812.9     203.8         84      4001
> cd0              0.0       0.0       0.0          0         0
> hdisk2           4.4     493.5      41.6       2384        96
> hdisk3           1.6     571.5      16.9          0      2872

> 1) Sybase recommends having number of AIO servers set same as number
> of disks. All I have is 4 disks defined, So, shouldn't a max of 10 aio
> async servers be suffient?
> 2) I'm aware from oracle DBAs that they set max aio servers to
> #of_CPUs*10? Has anobody noticed improvements with higher number of
> aio servers?
> 3) Does increasing "max num of requests" for AIO make a difference
> here?
> 4) Anybody has any suggestions for tunning or lowering down the bad
> affect of the Sybase backups running?
> Thanks
> Maj

kind regards,
--

Linux-KDE Romania http://www.ro.kde.org

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
 *** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
                      http://www.usenet.com
Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

 
 
 

DB dumps/backups slows the Database server

Post by Joerg Brueh » Sat, 27 Jul 2002 18:58:49


Hi!


> [...]

> The db and dump devices are setting on an EMC raid5 disk array. The
> AIX server has 2 disks/LUNs defined with 60GB each(hdisk2 and 3). Both
> the db devices and backup/dump directory are on the raid disks.

Do I get you right - the database dump writes to the same
disk drives (= spindle, access arm, track cache) the DB
storage area resides on?

If yes:
- Why on earth does it surprise you that a copy within a disk drive,
  that makes the arm jump between the two posaitions "read from"
  and "write to", is a severe performance hit?
- Most disk drives have an internal track cache that will help
  in sequential reads, but its contents will always be that of
  the track accessed last. If you were saving to anywhere but
  the DB disks, chances are that most reads would profit from
  that track cache - but not if you write to the very same disk.
- Are you aware of the fact that any damage to any drive in the
  RAID set will immediately affect both the DD area and the backup?

Setting all that aside: RAID 5 has no good write performance, AFAIK.

Quote:

> [...]
> 4) Anybody has any suggestions for tunning or lowering down the bad
> affect of the Sybase backups running?

By all means, avoid disk trashing - ensure that the backup data
does not go to those physical units where it is taken from.
My recommendation is to save to tape immediately.

Regards,
Joerg Bruehe

--
Joerg Bruehe, SQL Datenbanksysteme GmbH, Berlin, Germany
     (speaking only for himself)

 
 
 

DB dumps/backups slows the Database server

Post by Claudiu COSTI » Sat, 27 Jul 2002 21:46:10


          Hi Maj,

 Also you can try to have swaps on EMC drives. As you may see
the hdisk0 and hdisk1 are on the _same_ SCSI bus. So, you fill
the bus at 8MB/s. You can't do more.
 A solution will be to have a swap space on hdisk0 and
another big one on EMC drives.


> Heres a good question for the AIX tuning experts!!

> I have an M80 model RS6000 running AIX 4.3.3.10. 4 processors. 8GB of
> RAM. 2 Sybase servers using up 2GB of memory each. I have Async AIO
> min/max set to 1/10. Swap space set at 12GB on 2 internal disks(hdisk0
> and hdisk1).
> Swap space is 35% used.
> The Sybase servers are about 15GB in size each.

[snip]

have a nice day,
--
Claudiu Costin

 
 
 

DB dumps/backups slows the Database server

Post by Maj Sweid » Sat, 27 Jul 2002 22:29:19


Quote:>  Hi Maj,

>  It's very clear that when you backup/dump
> the database is _trashing_! From hdisk2 & hdisk3
> it's clear there's no signifiant DB activity.
> I suggest a
>         while true; do ps -a; sleep 3; done

I assume u ment to do 'lsps -a' above.

Quote:> and see how swap consume increase/decrease.
> Also where do you make dump? What activity is
> there?

The backups/dump dir is on hdisk3. Nothing else is hapenning on that
disk while the backup is running.
Thanks
Maj
 
 
 

DB dumps/backups slows the Database server

Post by Claudiu COSTI » Sat, 27 Jul 2002 22:42:18


        Dear Joerg,


> Hi!


>> [...]

>> The db and dump devices are setting on an EMC raid5 disk array. The
>> AIX server has 2 disks/LUNs defined with 60GB each(hdisk2 and 3). Both
>> the db devices and backup/dump directory are on the raid disks.

> Do I get you right - the database dump writes to the same
> disk drives (= spindle, access arm, track cache) the DB
> storage area resides on?

> If yes:
> - Why on earth does it surprise you that a copy within a disk drive,
>   that makes the arm jump between the two posaitions "read from"
>   and "write to", is a severe performance hit?

  Maj say it read and write from/to EMC disks, so all the processes
goes on EMC cache then write/read in *lot* of disk chunks, but in
no case an EMC internal disk goes to 100% activity.

Quote:> - Most disk drives have an internal track cache that will help
>   in sequential reads, but its contents will always be that of
>   the track accessed last. If you were saving to anywhere but
>   the DB disks, chances are that most reads would profit from
>   that track cache - but not if you write to the very same disk.

   See above.

Quote:> - Are you aware of the fact that any damage to any drive in the
>   RAID set will immediately affect both the DD area and the backup?

   What are RAID5 good after all, if not to ensure no data loss
and also even a good performance?

Quote:

> Setting all that aside: RAID 5 has no good write performance, AFAIK.

   It's a trade off of safeness and performance. Considering the EMC
big cache and also the fact that it use RAID5, then it clear
it can _sustain_ easyly >40MB/s (if I'm not wrong)

Quote:

>> [...]
>> 4) Anybody has any suggestions for tunning or lowering down the bad
>> affect of the Sybase backups running?

> By all means, avoid disk trashing - ensure that the backup data
> does not go to those physical units where it is taken from.
> My recommendation is to save to tape immediately.

> Regards,
> Joerg Bruehe

kind regards,
--
Claudiu Costin

 
 
 

DB dumps/backups slows the Database server

Post by Joerg Brueh » Sat, 27 Jul 2002 23:45:34


Hi Claudiu,


>         Dear Joerg,


> > [...]

> > If yes:
> > - Why on earth does it surprise you that a copy within a disk drive,
> >   that makes the arm jump between the two posaitions "read from"
> >   and "write to", is a severe performance hit?
>   Maj say it read and write from/to EMC disks, so all the processes
> goes on EMC cache then write/read in *lot* of disk chunks, but in
> no case an EMC internal disk goes to 100% activity.

1) A "write cache" in the EMC unit may be used (we do not know),
   but that again introduces risks unless it has a UPS to ensure
   disk write even after power fail. Being a DBMS developer and
   supporter, I have acquired some scepticism here.

2) A "read cache" will probably not help much when the backup
   reads all the data - the access is once only, not repeated.
   For the continued DB operation, I trust that any decent DBMS
   will do better caching (due to its knowledge of page type
   and access pattern) than the EMC cache (which has no such
   info).

1+2) "Cache" sounds great, but for DB operation one had better
   invest in RAM that the DBMS can administer.

3) The fact remains that it is a copy operation within the same
   drives that makes the disk arms alternate between positions.

Quote:

> [...]

> > - Are you aware of the fact that any damage to any drive in the
> >   RAID set will immediately affect both the DD area and the backup?
>    What are RAID5 good after all, if not to ensure no data loss
> and also even a good performance?

A very good question for which I would like to see an answer.

By the design principle of RAID 5, any write operation needs to
access several disks, so its response time must be longer than
that of a single disk, also AFAIK it may require a previous read
to compute the new redundancy pattern - the performance must be
worse than that of a single (similar) disk.

Whether one accepts that performance loss in order to get a
fail-safe improvement at moderate costs is a completely different
issue. IMO, it should only be done for areas where performance
is not critical.

In the DBMS area, you might use it for "data", but do not use it
for "log" (log write must be synchronous) if you want high
transaction throughput. If Maj also has the log areas on the
same RAID 5 disks, the perfrmance breakdown does not surprise me.

My remark about "any damage to any drive" is more oriented towards
the fact that this backup onto the same disk spindles does _not_
give protection against catastrophic disk failures (like double
faults, ...) or admin errors ("rm -rf ...") - the backup is as
safe as the original data area. It only protects against (some)
internal DBMS malfunction, whereas a tape backup does more.

Quote:

> > Setting all that aside: RAID 5 has no good write performance, AFAIK.
>    It's a trade off of safeness and performance. Considering the EMC
> big cache and also the fact that it use RAID5, then it clear
> it can _sustain_ easyly >40MB/s (if I'm not wrong)

I do not know the figures - you may be right. However, depending
on the area placement this disk-to-disk copy may be a case with
large seek distances, yielding figures worse than average.

I readily admit that the real issue may be with hdisk0 & hdisk1,
I have no idea why there is so much write traffic to them.

Regards,
Joerg Bruehe

--
Joerg Bruehe, SQL Datenbanksysteme GmbH, Berlin, Germany
     (speaking only for himself)

 
 
 

DB dumps/backups slows the Database server

Post by Claudiu COSTI » Sun, 28 Jul 2002 00:58:02


           Dear Joerg,


> 1) A "write cache" in the EMC unit may be used (we do not know),

   We know for _sure_, trust me. EMC have very strict policy.
You even cannot have a bad configured Symetrix because they
don't allow you. You cannot just request some little cache
and lot of storage space just  because they don't want
rummors about EMC not have performance. Ask your EMC support
guys.

Quote:>    but that again introduces risks unless it has a UPS to ensure
>    disk write even after power fail. Being a DBMS developer and
>    supporter, I have acquired some scepticism here.

   You cannot damage EMC. Even the power fail or goes up/down
randomly, the UPS have capacity for 15 minutes of more, suficiently
for destaging cache on disks and powerdown procedure. I can't
emphasys more: EMC or ESS or any other high performance storange
simply cannot aford such silly things like power failure. They
have redundant power supply and almost all internals are redundant.
ESS "Shark" even have redundant cache!

Quote:

> 2) A "read cache" will probably not help much when the backup
>    reads all the data - the access is once only, not repeated.

     Yes, but as say data is splited accross many disks, lets
say 5-6 and on different SCSI buses. This translate to huge
IO bandwidth.

Quote:>    For the continued DB operation, I trust that any decent DBMS
>    will do better caching (due to its knowledge of page type
>    and access pattern) than the EMC cache (which has no such
>    info).

     DBMS have no knwoledge about filesystems internals and even
physical disks. The recomandation is to split database to more
filesystems in the hope these corespond to different physical disks
While we use LVM which hide physical aspect, is up to system
administrator that logical volumes for database indeed reside on
physical different HDDs. This is very important for performance
especially when working with SSAs or SCSI drives.
When come to storages like EMC or ESS, this simply will not make
sense. The storage give you LUNs and it follow own internal
information organization to minimize IO "hot spots".  

Quote:

> 1+2) "Cache" sounds great, but for DB operation one had better
>    invest in RAM that the DBMS can administer.

   When working with SAN storages which employ large caches,
the AIX disk IO cache affect performance because EMC/ESS over FC
have very low latency and high bandwidth. AIX IO cache just
add another processing layer.

Quote:

> 3) The fact remains that it is a copy operation within the same
>    drives that makes the disk arms alternate between positions.

   As I said above this statement is false for EMC or ESS.

Quote:

> A very good question for which I would like to see an answer.

> By the design principle of RAID 5, any write operation needs to
> access several disks, so its response time must be longer than
> that of a single disk, also AFAIK it may require a previous read
> to compute the new redundancy pattern - the performance must be
> worse than that of a single (similar) disk.

  It's very clear that. But the real point is that IO read
bandwidth is very high. But EMC isn't a plain RAID5, but a storage
with big cache. It can afford to delay destaging your IO writes.
You just write to cache and not to disks. The performance
will drop when you fill the cache before it can write all on disk.
Like on a very huge copy. I wonder if RAID5 is not a plain one,
but with chunks accross many disks. For the moment I don't know.

Quote:

> Whether one accepts that performance loss in order to get a
> fail-safe improvement at moderate costs is a completely different
> issue. IMO, it should only be done for areas where performance
> is not critical.

   I can't follow.

Quote:

> In the DBMS area, you might use it for "data", but do not use it
> for "log" (log write must be synchronous) if you want high
> transaction throughput. If Maj also has the log areas on the
> same RAID 5 disks, the perfrmance breakdown does not surprise me.

  Not true as I said above.

Quote:

> My remark about "any damage to any drive" is more oriented towards
> the fact that this backup onto the same disk spindles does _not_
> give protection against catastrophic disk failures (like double
> faults, ...) or admin errors ("rm -rf ...") - the backup is as
> safe as the original data area. It only protects against (some)
> internal DBMS malfunction, whereas a tape backup does more.

  disk faults are different animal from user/program errors.
  EMC protect from hardware errors, so here come RAID5 + hotspare.
  or RAID1+hotspare.

  User, programs errors or disasters are managed by backups.

Quote:

>> > Setting all that aside: RAID 5 has no good write performance, AFAIK.
>>    It's a trade off of safeness and performance. Considering the EMC
>> big cache and also the fact that it use RAID5, then it clear
>> it can _sustain_ easyly >40MB/s (if I'm not wrong)

> I do not know the figures - you may be right. However, depending
> on the area placement this disk-to-disk copy may be a case with
> large seek distances, yielding figures worse than average.

   Cache is the solution and that's why is implemented at HDD level
on it's electronics. The more cache your HDD have the more performance
(and of course lot of money :^)

Quote:

> I readily admit that the real issue may be with hdisk0 & hdisk1,
> I have no idea why there is so much write traffic to them.

    I suspect the backup program allocate huge ammount of memory
so it just goes system on trashing.

have a nice day,
--
Claudiu Costin

 
 
 

DB dumps/backups slows the Database server

Post by Joerg Brueh » Sun, 28 Jul 2002 01:54:06


Dear Claudiu,


>            Dear Joerg,


> > 1) A "write cache" in the EMC unit may be used (we do not know),
>    We know for _sure_, trust me. EMC have very strict policy.
> [...] Ask your EMC support guys.

Obviously you know much more about EMC than I do,
so I will not uphold my general RAID 5 remarks
in the context of EMC use.

Quote:

> [...]

> >    For the continued DB operation, I trust that any decent DBMS
> >    will do better caching (due to its knowledge of page type
> >    and access pattern) than the EMC cache (which has no such
> >    info).
>      DBMS have no knwoledge about filesystems internals and even
> physical disks. The recomandation is to split database to more
> filesystems in the hope these corespond to different physical disks
> While we use LVM which hide physical aspect, is up to system
> administrator that logical volumes for database indeed reside on
> physical different HDDs. This is very important for performance
> especially when working with SSAs or SCSI drives.

Very true and correct - except that I would bypass the file system
(see below).

Quote:> When come to storages like EMC or ESS, this simply will not make
> sense. The storage give you LUNs and it follow own internal
> information organization to minimize IO "hot spots".

The best thing which any disk subsystem can do is to speed up I/O.
The DBMS can use the machine RAM (its own "data cache") to totally
avoid I/O, provided there is re-use of the data already accessed.
As even the fastest disk subsystem must be slower than direct
RAM access (the OS is involved to get the data from the subsystem,
which is then put into the RAM used by the DBMS), IMO the RAM in
the machine will help more than that in the subsystem.

Quote:

> > 1+2) "Cache" sounds great, but for DB operation one had better
> >    invest in RAM that the DBMS can administer.
>    When working with SAN storages which employ large caches,
> the AIX disk IO cache affect performance because EMC/ESS over FC
> have very low latency and high bandwidth. AIX IO cache just
> add another processing layer.

I do not refer to the AIX buffer cache (nor to file systems),
I talk about DBMSs using "raw disk" (AIX: "raw logical volume")
for storage, so that they avoid both the buffer cache and the
file system indirection overhead. If the file system were used,
you were right - but our recommendation with Adabas D is to
totally bypass it for various reasons.

Quote:

> [...]

Sorry about any misunderstandings,
Joerg

--
Joerg Bruehe, SQL Datenbanksysteme GmbH, Berlin, Germany
     (speaking only for himself)

 
 
 

DB dumps/backups slows the Database server

Post by Jose Pina Coelh » Tue, 30 Jul 2002 06:51:14



>           Hi Maj,

>  Also you can try to have swaps on EMC drives. As you may see
> the hdisk0 and hdisk1 are on the _same_ SCSI bus. So, you fill
> the bus at 8MB/s. You can't do more.

- If your swap is being filled at 8MBps, you *need* more ram.
- Only on very particular situations will you get SCSI constraints
  for just two disks on a fast/wide bus, with the usual I/O paterns
  the disk tends to be the limit.

Quote:>  A solution will be to have a swap space on hdisk0 and
> another big one on EMC drives.

Personaly I like to be able to pull the FC connection without having
it cause an O/S panic.
de gustibus non disputandum

I think the problem is sybase configuration:
First it's two sybase servers with 2GB for each,
4 lines down it's 15GB for the two of them.

What gives ?

--
Doing AIX support was the most monty-pythonesque
activity available at the time.
Eagerly awaiting my thin chocolat mint.

 
 
 

DB dumps/backups slows the Database server

Post by Claudiu COSTI » Tue, 30 Jul 2002 19:16:40


           Dear Joerg,

[snip]

Quote:

> I do not refer to the AIX buffer cache (nor to file systems),
> I talk about DBMSs using "raw disk" (AIX: "raw logical volume")
> for storage, so that they avoid both the buffer cache and the
> file system indirection overhead. If the file system were used,
> you were right - but our recommendation with Adabas D is to
> totally bypass it for various reasons.

   Indeed. I forgot about filesystem layer overhead.

have a nice Monday,
--
Claudiu Costin

 
 
 

DB dumps/backups slows the Database server

Post by Maj Sweid » Thu, 01 Aug 2002 00:07:04



> Hi Maj,

>  Also you can try to have swaps on EMC drives. As you may see
> the hdisk0 and hdisk1 are on the _same_ SCSI bus. So, you fill
> the bus at 8MB/s. You can't do more.
>  A solution will be to have a swap space on hdisk0 and
> another big one on EMC drives.

Thats a good idea. I think I may try that next.

To clarify my earlier desc of the Sybase servers. Each Sybase server
uses up 2GB of RAM. Each Sybase server takes up about 16GB of disk
space.
I tried upping the min/max I/O server from 1/10 to 10/40, but it made
no difference.
The bottle neck seems to be on hdisk0 & hdisk1 (where swap space
resides).
I can't figure out why it has to page so much to these disks. There is
plenty of RAM on the server. I have 8GB of RAM, while Sybase servers
only using a total of 4GB. Swap space is 12GB, so thats 1.5 times
physical memory on the box.

I ran an iostat script every 5 mins during the backup to see the
IOWAIT on the disk. Heres what I saw:
tty:      tin         tout   avg-cpu:  % user    % sys     % idle    %
iowait
          0.0          0.0              12.9      1.3        0.1    
85.7

Disks:        % tm_act     Kbps      tps    Kb_read   Kb_wrtn
hdisk1          98.3     846.5     208.7         52      4280
hdisk0          98.3     875.0     215.7        282      4196
cd0              0.0       0.0       0.0          0         0
hdisk2           4.5     497.9      72.3       2548         0
hdisk3           2.5     525.3      10.6          0      2688

tty:      tin         tout   avg-cpu:  % user    % sys     % idle    %
iowait
          0.0          0.0              13.3      1.0        1.0    
84.7

Disks:        % tm_act     Kbps      tps    Kb_read   Kb_wrtn
hdisk1         100.0     905.6     222.2        112      4416
hdisk0         100.0     937.6     229.8        228      4460
cd0              0.0       0.0       0.0          0         0
hdisk2           4.0     574.4      89.0       2864         8
hdisk3           2.0     563.2      10.4          0      2816

While its reading from hdisk2(db devices) and writing to hdisk3(dump
directory), hdisk0 & 1 clearly start trashing, but I don't know why!!
IOWAIT of about 80% is way too high. It looks like the server does not
have enough memory, but it actually has 4GB for the OS alone!!

lslv -l hd6
hd6:N/A
PV                COPIES        IN BAND       DISTRIBUTION
hdisk0            384:000:000   27%           091:105:082:101:005
hdisk1            384:000:000   28%           077:108:108:091:000

 
 
 

DB dumps/backups slows the Database server

Post by Clive Spark » Thu, 01 Aug 2002 16:55:58




> > Hi Maj,

> >  Also you can try to have swaps on EMC drives. As you may see
> > the hdisk0 and hdisk1 are on the _same_ SCSI bus. So, you fill
> > the bus at 8MB/s. You can't do more.
> >  A solution will be to have a swap space on hdisk0 and
> > another big one on EMC drives.

> Thats a good idea. I think I may try that next.

> To clarify my earlier desc of the Sybase servers. Each Sybase server
> uses up 2GB of RAM. Each Sybase server takes up about 16GB of disk
> space.
> I tried upping the min/max I/O server from 1/10 to 10/40, but it made
> no difference.
> The bottle neck seems to be on hdisk0 & hdisk1 (where swap space
> resides).
> I can't figure out why it has to page so much to these disks. There is
> plenty of RAM on the server. I have 8GB of RAM, while Sybase servers
> only using a total of 4GB. Swap space is 12GB, so thats 1.5 times
> physical memory on the box.

> I ran an iostat script every 5 mins during the backup to see the
> IOWAIT on the disk. Heres what I saw:
> tty:      tin         tout   avg-cpu:  % user    % sys     % idle    %
> iowait
>           0.0          0.0              12.9      1.3        0.1    
> 85.7

> Disks:        % tm_act     Kbps      tps    Kb_read   Kb_wrtn
> hdisk1          98.3     846.5     208.7         52      4280
> hdisk0          98.3     875.0     215.7        282      4196
> cd0              0.0       0.0       0.0          0         0
> hdisk2           4.5     497.9      72.3       2548         0
> hdisk3           2.5     525.3      10.6          0      2688

> tty:      tin         tout   avg-cpu:  % user    % sys     % idle    %
> iowait
>           0.0          0.0              13.3      1.0        1.0    
> 84.7

> Disks:        % tm_act     Kbps      tps    Kb_read   Kb_wrtn
> hdisk1         100.0     905.6     222.2        112      4416
> hdisk0         100.0     937.6     229.8        228      4460
> cd0              0.0       0.0       0.0          0         0
> hdisk2           4.0     574.4      89.0       2864         8
> hdisk3           2.0     563.2      10.4          0      2816

> While its reading from hdisk2(db devices) and writing to hdisk3(dump
> directory), hdisk0 & 1 clearly start trashing, but I don't know why!!
> IOWAIT of about 80% is way too high. It looks like the server does not
> have enough memory, but it actually has 4GB for the OS alone!!

> lslv -l hd6
> hd6:N/A
> PV                COPIES        IN BAND       DISTRIBUTION
> hdisk0            384:000:000   27%           091:105:082:101:005
> hdisk1            384:000:000   28%           077:108:108:091:000

I'd suggest using /usr/bin/filemon from the perfagent.tools fileset to
confirm that it's the paging space being hammered with the writes to
hdisk0 & 1.

Question, is this a mirrored hd6?

Also you could try using /usr/samples/kernel/vmtune from the
bos.adt.samples fileset to reduce the fs cache. ie
/usr/sampls/kernel/vmtune -p 5 -P 10

--
Regards,
Clive

 
 
 

DB dumps/backups slows the Database server

Post by Richard Hensch » Fri, 02 Aug 2002 00:08:41


We run Sybase 11.5 on AIX 4.1.5 and the disk activity on hdisk2 and hdisk3
is no worse than what we see during a dump and I might add with old, slow
hardware with no RAID (other than SYBASE's own mirroring turned on).
Our server does not slow down completely during a dump, and we don't see
paging activity with Sybase using 120 Meg on a 256 Meg machine running
other stuff than SYBASE. This looks like memory over committed badly!
AIX 4.3.3 (we have some of these two) will generally use very small swap
(we see in the order of 5 to 10% of the real memory used on swap)
unless memory is grossly overcommitted... this always surprises me because
it is so much different behavior than AIX 3.2.5 and 4.1.5 was.
What else are you running other than the SYBASE servers?
'ipcs -a -m' should show shared memory segments (SYBASE uses these)
in use; see if this is consistent with what you think SYBASE is using.
Run topas or monitor and find out what else is using memory on your system.

As far as counting on RAID to save your data; I have seen and read here
many cases of failures, often involving multiple disks, occuring on
'perfectly' safe and redundant RAID systems.  I trust raid 1/10 more than 4/5.
I trust backups to tapes more...


> >  Hi Maj,

> >  It's very clear that when you backup/dump
> > the database is _trashing_! From hdisk2 & hdisk3
> > it's clear there's no signifiant DB activity.
> > I suggest a
> >         while true; do ps -a; sleep 3; done

> I assume u ment to do 'lsps -a' above.

> > and see how swap consume increase/decrease.
> > Also where do you make dump? What activity is
> > there?

> The backups/dump dir is on hdisk3. Nothing else is hapenning on that
> disk while the backup is running.
> Thanks
> Maj

 
 
 

DB dumps/backups slows the Database server

Post by Dave Put » Sun, 04 Aug 2002 06:27:42


Hi Maj,

        You may want to have your AIX system admin take
a look at the system. We ran tests on an AIX 4.3.3 box that
showed that high paging was caused by an overlap in size
between the configurations for the file system buffer cache in AIX
and the shared memory segments in ASE. The problem is resolved by
setting the AIX parameters minperm, maxperm, and strict_maxperm
so that the OS does not attempt to use memory that could lead
to paging. It seems that AIX considers almost all
memory fair game for paging out to use for file system buffer cache.
Hope this helps,

                                - Dave


> Heres a good question for the AIX tuning experts!!

> I have an M80 model RS6000 running AIX 4.3.3.10. 4 processors. 8GB of
> RAM. 2 Sybase servers using up 2GB of memory each. I have Async AIO
> min/max set to 1/10. Swap space set at 12GB on 2 internal disks(hdisk0
> and hdisk1).
> Swap space is 35% used.
> The Sybase servers are about 15GB in size each.

> The db and dump devices are setting on an EMC raid5 disk array. The
> AIX server has 2 disks/LUNs defined with 60GB each(hdisk2 and 3). Both
> the db devices and backup/dump directory are on the raid disks.

> When the Sybase db dump runs, the Sybase server slows down to a halt.
> I'm running Sybase ASE version 12.0.0.4 with latest fix release. When
> I cancel the Sybase backup, the Sybase db goes back to normal response
> time.

> The iostats below show that the hdisk0 & 1 are 100% busy!! It doesn't
> look like the SAN disks are a bottle neck here, but IOWAIT sure looks
> high to me.

 
 
 

1. Oracle NT client slow when DB server on AIX ?

Have someone experienced performance problems with oracle client on NT
connected to Oracle DB on AIX 4.3.3. Same client with DB on NT server
runs 5-10 times faster (elapsed time). AIX server is not short of
resources.
We have tested on two different RS/6000 models with same result.
This seems to be a TCP/IP windowing problem ??!!
--
----------------------------------------------------------
Dagfinn Amundsen     Norsk Hydro a.s,  5020 Bergen, Norway
Phone: +47 55 996966, Fax: +47 55 996495

----------------------------------------------------------

2. GNU tar compress on the fly?

3. Any other way to redefine a database device without dumping all databases and reloading?

4. 3 NICs (8139too): too much ?

5. HELP: Sharing Access Database on Linux File Server is Slow

6. R'utils not working in Slackware 3.0

7. Backup under Linux (Re: Dump Backup failed!!!)

8. will there be a "mod_jk.so" for apache 2.0 and tomcat 4.0?

9. backup using tape is slow very slow

10. Slow backup to Win2k ARCserve server

11. Secure backup on remote untrusted server over slow line?

12. Unix database functions: Berkeley DB

13. Database client libraries with multi-DB engine support?