Ansicht
Dokumentation
01845 - Database Size affecting Performance
Fill RESBD Structure from EBP Component Structure BAL Application Log DocumentationThis documentation is copyright by SAP AG.
Database Size affecting Performance
Thanks to all for their replies. Excuse the pun, but it appears that sizedoes matter. 🙂
I do have a follow-up question, though:
As an SAP database library grows (let's say doubles from 120GB to 240GB),
what are the benefits, with regards to performance, with:
1. Table reorganization?
2. Disk rebalancing?
For part 1, we typically reorganize tables where we have archived data. For
this, we normally pick just the obvious, large tables associated with that
item (e.g. for SD_VBAK, we will reorg VBAK and VBAP).
a.) What would be the added benefit of reorganizing any table that has
deleted records?
b.) In addition, we have the tables reuse deleted records. Will this
degrade performance? Will reorganizing the tables help abate any performance
degradation, or is there minimal gain since the DB400's algorithms to search
indexes is independent of the tuple order?
c.) Lastly, are there any benefits with reorganizing tables w/out deleted
records? (i.e. in Oracle land, a reorganization of a table w/out deleted
records is helpful to recreate the table in contiguous space, combining all
the extents that were created due to growth).
As for part 2, I am uncertain what command is used for this function
(perhaps a part of DDM?). Our former AS/400 administrator explained its use
as mainly when adding more disks to an ASP. But my question is, will this
help performance as well, even when no disks were added. In other words,
since some tables are now 10x larger than they were on initial installation
and conversion, will a rebalancing help performance by optimizing the spread
of data, at its current size, over several disks/arms?
Sorry to be such a windbag, but inquiring minds want to know. Also, I hope
this is a good topic for the group to discuss.
Thanks in advance for your input.
Regards,
Mike D. Martin
SAP Basis Administrator
SOLA Optical, USA
707-763-9911 x6106
mmartinZs...
-----Original Message-----
From: Ron Schmerbauch [mailto:rschmerbZu...]
Sent: Monday, April 23, 2001 2:07 PM
To: sap400Zm...
Subject: Re: Database Size affecting Performance
You guys are good... you've got all the answers before I have a chance to
reply...
Yes, it will matter some. How much? It depends. Here's some of the
factors that would come into play....
On the negative side,
-Memory... small files might tend to naturally become memory resident if
they are getting used a lot. If they are big, they might not fit anymore
and would get swapped to disk.
-SAP buffers... the memory comment, but with SAP buffers instead of DB
files. Of course, DB files are sometimes in the buffers...
-An index is a binary tree. Searches are "fast", but the bigger the tree,
the longer it may take to traverse it.
-If the extra space is eaten up by deleted rows you are just making the
system wade through garbage.
On the positive side,
-A bigger DB means you will have probably more disk drives, and thus better
spread across more arms.
In general, a 10x increase across a DB lib doesn't mean you need 10x more
CPU and memory, but modest increase needs could be expected.
Ron Schmerbauch 507-253-4880 rschmerbZu...
iSeries 400 - ERP Development - Rochester, MN
"Betsy Strebe" <bstrebeZt...> on 04/23/2001 02:14:33 PM
To: "Mike Martin, IS, Sousa" <MMartinZs...>
cc: sap400Zm...
Subject: Re: Database Size affecting Performance
Mike,
Surely this has to be so, it only makes sense. But I would also say that
in addition to file growth you would have to look at the file organization.
Because a file sorted by it's primary key and purged of deleted records
will perform much better than one left to expand and contract on it's own.
A file that grows in an orderly fashion with minimal deletion won't be as
much of a problem child as one that does. Periodically, I run the deleted
rows analysis of DB02 and look for files with large numbers of deleted rows
and/or using a large of amount of storage. I reorganize these on the
primary key. For example, this helped tremendously after the initial
loading of our CO-PA files, where they were throwing in and deleting large
amounts of history data - and gained a lot of disk space, around 5G.
Is anything performance related ever simple? Ah that it would be.
regards,
Betsy
~~~~~~~~~~~~~~~~~~~~~~
Betsy Strebe
bstrebeZt...
Systems Technical Manager
Trinchero Family Estates
Sutter Home Wines, Inc.
(707) 963-3104 ext. 2439
www.tfewines.com
~~~~~~~~~~~~~~~~~~~~~~~
"Mike Martin,
IS, Sousa" To: "SAP - AS/400 List
(E-mail)" <sap400Zm...>
<MMartinZsola cc:
.com> Subject: Database Size
affecting Performance
04/23/01
10:02 AM
SAPers,
I have a (hopefully) simple and generic question for the group. As
database size grows on DB2/400, will performance degrade?
For example, with an SAP database grows from 150GB to 300GB over two years,
will this correlate into degraded performance? If a table grows from 20MB
to 20GB will it take longer on reads, writes, and deletes? Will more
memory/CPU cycles be used for the index search?
If not, this tells me that a 10GB system will perform the same as a 10TB
system (all other machine specs being equal) and the only difference isthat
one system houses a lot more disk. However, my belief is the
opposite, that DB size does affect system performance. That simply having
larger tables will lead to longer reads, inserts, changes, and deletes.
But that's just my opinion, I could be wrong...What's your opinion?
Thanks in advance,
Mike D. Martin
SAP Basis Administrator
SOLA Optical, USA
707-763-9911 x6106
mmartinZs...
[Non-text portions of this message have been removed]
Durban Tours - Südafrika Safari
Addresses (Business Address Services) General Data in Customer Master
This documentation is copyright by SAP AG.
Length: 7776 Date: 20240603 Time: 175910 sap01-206 ( 3 ms )
