database reorg for large databases?
Greetings,
My upper management is interested in what other companies do as far as
reorg'ing large database(s). In my case I have a Peoplesoft database that
is 336 gigs (used). Recent testing proved that a full database reorg
(export/import) takes ~90 hrs. The last time I performed a reorg my
database was ~220 gig and I was able to complete this in a weekend.
I have presented several options (listed below) to management but they are
very much interested in knowing how others deal with large databases and
reorgs. If you have a moment and perform(ed) some type of database reorgs
on a regular basis please share with me your approach.
TIA
Option 1
Full database reorg using dbexport/dbimport.
Option 2
Break 1 large database into multiple databases (3-4) (by module has been
discussed).
Use synonyms from the primary database to point to tables located in
other databases.
Tables reside in "db specific dbspaces (ie, databaseA dbspace1 -->
dbspace6, databaseB dbspace6 --> dbspace12,.....you get the idea)"
Use dbexport/dbimport to reorg smaller databases over multiple weekends.
(one thing to note here, psoft admins would not be fond due to dddaudit
reports)
Option 3
(I like to call this a "reorg by dbspace") Group tables and indexes
together based on a percent of the database into a set of dbspaces.
Unload all tables, drop all tables, recreate tables and load 1 after the
other, recreate indexes in this set of dbspaces. This would basically
represent a bastardized export and import of all tables in the specific
dbspaces thus, the ultimate goal, eliminating interleaving.
Little info about my env:
IDS 9.40 FC2
HPUX 11
6 way nClass w/550's
6 gig memory
LOCKS 5000000 # Maximum number of locks
BUFFERS 1400000 # Maximum number of shared buffers
NUMAIOVPS 4 # Number of IO vps
PHYSBUFF 256 # Physical log buffer size (Kbytes)
LOGBUFF 128 # Logical log buffer size (Kbytes)LOGSMAX 200 # Maximum number of logical log files
CLEANERS 128 # Number of buffer cleaner processes
SHMBASE 0x0 # Shared memory base address
SHMVIRTSIZE 800000 # initial virtual shared memory segmentsize
SHMADD 32768 # Size of new shared memory segments
(Kbytes)
SHMTOTAL 0 # Total shared memory (Kbytes).
0=>unlimited
CKPTINTVL 300 # Check point interval (in sec)
LRUS 128 # Number of LRU queues
LRU_MAX_DIRTY 2 # LRU percent dirty begin cleaning limit
LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit
LTXHWM 50 # Long transaction high water markpercentage
LTXEHWM 60 # Long transaction high water mark
(exclusive)
TXTIMEOUT 0x12c # Transaction timeout (in sec)
STACKSIZE 64 # Stack size (Kbytes)
Dear
Darren,
Option 4 : (variant of option 3 - how sapdba works)
Instead of unloading and reloading all tables:
for all dbspaces, do
make sure you have a recent L-0 archive --just to be on the save side
turn off logging of the database ;
create new_dbspace;
for all tables in dbspace, do
save schema of table;
create new_table (same schema) in new_dbspace;
lock table in exclusive mode; --just to be sure
INSERT INTO new_table SELECT * from table;check number of rows is the same
drop table;
rename new_table to original name of table;
recreate indices;
done
rename new_dbspace to dbspace (new option in IDS >= 9.40.FC3)
run update staistics for all tables
get a L-0 archive, turn on logging
done
This saves the need to unload all data to the file system.
Also, I suggest to adapt first extent size when creating the new
tables to ensure all current rows a loaded in one or only few extents.
Also, dbspaces can be done one by one.
With kind regards
Tilman
--
Tilman Model-Bosch
IBM Data Managment Solutions, Informix Advanced Support
c\\\\o SAP AG
TECHDEV 05
Neurrotstr.16
69190 Walldorf
forum.subscriber@iiug.org wrote on 11/03/2005 21:42:52:
> Greetings,
>
> My upper management is interested in what other companies do as far as
> reorg'ing large database(s). In my case I have a Peoplesoft database
that
> is 336 gigs (used). Recent testing proved that a full database reorg
> (export/import) takes ~90 hrs. The last time I performed a reorg my
> database was ~220 gig and I was able to complete this in a weekend.
>
> I have presented several options (listed below) to management but they
are
> very much interested in knowing how others deal with large databases and
> reorgs. If you have a moment and perform(ed) some type of database
reorgs
> on a regular basis please share with me your approach.
>
> TIA
>
> Option 1
> Full database reorg using dbexport/dbimport.
>
> Option 2
> Break 1 large database into multiple databases (3-4) (by module has
been
> discussed).
> Use synonyms from the primary database to point to tables located in
> other databases.
> Tables reside in "db specific dbspaces (ie, databaseA dbspace1 -->
> dbspace6, databaseB dbspace6 --> dbspace12,.....you get the idea)"
> Use dbexport/dbimport to reorg smaller databases over multiple
weekends.
> (one thing to note here, psoft admins would not be fond due to
dddaudit
> reports)
>
> Option 3
> (I like to call this a "reorg by dbspace") Group tables and indexes
> together based on a percent of the database into a set of dbspaces.
> Unload all tables, drop all tables, recreate tables and load 1 after the
> other, recreate indexes in this set of dbspaces. This would basically
> represent a bastardized export and import of all tables in the specific
> dbspaces thus, the ultimate goal, eliminating interleaving.
>
>
> Little info about my env:
> IDS 9.40 FC2
> HPUX 11
> 6 way nClass w/550's
> 6 gig memory
>
> LOCKS 5000000 # Maximum number of locks
> BUFFERS 1400000 # Maximum number of shared buffers
> NUMAIOVPS 4 # Number of IO vps
> PHYSBUFF 256 # Physical log buffer size (Kbytes)
> LOGBUFF 128 # Logical log buffer size (Kbytes)> LOGSMAX 200 # Maximum number of logical log files
> CLEANERS 128 # Number of buffer cleaner processes
> SHMBASE 0x0 # Shared memory base address
> SHMVIRTSIZE 800000 # initial virtual shared memory segment> size
> SHMADD 32768 # Size of new shared memory segments
> (Kbytes)
> SHMTOTAL 0 # Total shared memory (Kbytes).
> 0=>unlimited
> CKPTINTVL 300 # Check point interval (in sec)
> LRUS 128 # Number of LRU queues
> LRU_MAX_DIRTY 2 # LRU percent dirty begin cleaning limit
> LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit
> LTXHWM 50 # Long transaction high water mark> percentage
> LTXEHWM 60 # Long transaction high water mark
> (exclusive)
> TXTIMEOUT 0x12c # Transaction timeout (in sec)
> STACKSIZE 64 # Stack size (Kbytes)>
>
>
If your
tables are fragmented, you will gain a lot of speed setting pdqpriority before
copying each table, that will allow multiples threads per each table. Same for
indexes on fragmented tables. Actually I would copy all the tables, set an
onconfig suitable for indexes creation and then create all indexes.
In our shop we use update statistics low, if that is your case we found that
this sentence ...
update statistics low for table (tio)run slower than...
update statistics low for table tio(idxcol1,idxcol2,idxcol3,idxcol4);being idxcol(1-4) columns which are part of one or several indexes of that
table. We are running 9.3 on several platforms.BTW we have OPTCOMPIND set to 0.
-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On
Behalf Of Tilman Mode....
Sent: Friday, March 11, 2005 4:37 PM
To: ids@iiug.org
Subject: Re: database reorg for large databases? [4483]
Dear Darren,
Option 4 : (variant of option 3 - how sapdba works)
Instead of unloading and reloading all tables:
for all dbspaces, do
make sure you have a recent L-0 archive --just to be on the save side
turn off logging of the database ;
create new_dbspace;
for all tables in dbspace, do
save schema of table;
create new_table (same schema) in new_dbspace;
lock table in exclusive mode; --just to be sure
INSERT INTO new_table SELECT * from table;check number of rows is the same
drop table;
rename new_table to original name of table;
recreate indices;
done
rename new_dbspace to dbspace (new option in IDS >= 9.40.FC3)
run update staistics for all tables
get a L-0 archive, turn on logging
done
This saves the need to unload all data to the file system.
Also, I suggest to adapt first extent size when creating the new
tables to ensure all current rows a loaded in one or only few extents.
Also, dbspaces can be done one by one.
With kind regards
Tilman
--
Tilman Model-Bosch
IBM Data Managment Solutions, Informix Advanced Support
c\\\\o SAP AG
TECHDEV 05
Neurrotstr.16
69190 Walldorf
forum.subscriber@iiug.org wrote on 11/03/2005 21:42:52:
> Greetings,
>
> My upper management is interested in what other companies do as far as
> reorg'ing large database(s). In my case I have a Peoplesoft database
that
> is 336 gigs (used). Recent testing proved that a full database reorg
> (export/import) takes ~90 hrs. The last time I performed a reorg my
> database was ~220 gig and I was able to complete this in a weekend.
>
> I have presented several options (listed below) to management but they
are
> very much interested in knowing how others deal with large databases and
> reorgs. If you have a moment and perform(ed) some type of database
reorgs
> on a regular basis please share with me your approach.
>
> TIA
>
> Option 1
> Full database reorg using dbexport/dbimport.
>
> Option 2
> Break 1 large database into multiple databases (3-4) (by module has
been
> discussed).
> Use synonyms from the primary database to point to tables located in
> other databases.
> Tables reside in "db specific dbspaces (ie, databaseA dbspace1 -->
> dbspace6, databaseB dbspace6 --> dbspace12,.....you get the idea)"
> Use dbexport/dbimport to reorg smaller databases over multiple
weekends.
> (one thing to note here, psoft admins would not be fond due to
dddaudit
> reports)
>
> Option 3
> (I like to call this a "reorg by dbspace") Group tables and indexes
> together based on a percent of the database into a set of dbspaces.
> Unload all tables, drop all tables, recreate tables and load 1 after the
> other, recreate indexes in this set of dbspaces. This would basically
> represent a bastardized export and import of all tables in the specific
> dbspaces thus, the ultimate goal, eliminating interleaving.
>
>
> Little info about my env:
> IDS 9.40 FC2
> HPUX 11
> 6 way nClass w/550's
> 6 gig memory
>
> LOCKS 5000000 # Maximum number of locks
> BUFFERS 1400000 # Maximum number of shared buffers
> NUMAIOVPS 4 # Number of IO vps
> PHYSBUFF 256 # Physical log buffer size (Kbytes)
> LOGBUFF 128 # Logical log buffer size (Kbytes)> LOGSMAX 200 # Maximum number of logical log files
> CLEANERS 128 # Number of buffer cleaner processes
> SHMBASE 0x0 # Shared memory base address
> SHMVIRTSIZE 800000 # initial virtual shared memory segment> size
> SHMADD 32768 # Size of new shared memory segments
> (Kbytes)
> SHMTOTAL 0 # Total shared memory (Kbytes).
> 0=>unlimited
> CKPTINTVL 300 # Check point interval (in sec)
> LRUS 128 # Number of LRU queues
> LRU_MAX_DIRTY 2 # LRU percent dirty begin cleaning limit
> LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit
> LTXHWM 50 # Long transaction high water mark> percentage
> LTXEHWM 60 # Long transaction high water mark
> (exclusive)
> TXTIMEOUT 0x12c # Transaction timeout (in sec)
> STACKSIZE 64 # Stack size (Kbytes)>
>
>
Hello,
why do you want to do a full database reorg at all? We are running
SAP systems between 40 GB and 2 TB, but we reorganize only individual
tables (or indices) if necessary (extent limits/size limits) or if
there is a lot of wasted space after some logical reorganization.
I think Peoplesoft will behave like SAP that most reads/writes are
done with indexes. Therefore many extents shouldn't hurt you much.
If you have some big SAN storage system (like EMC2,HP-Surestore,Hitachi,..)
with RAID, striping and disk caches even sequential reads should
perform fine.
Regards,
Andreas Kutsche
> -----Ursprüngliche Nachricht-----
> Von: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]Im
> Auftrag von Darren_Jaco....
> Gesendet: Freitag, 11. März 2005 21:43
> An: ids@iiug.org
> Betreff: database reorg for large databases? [4482]
>
>
> Greetings,
>
> My upper management is interested in what other companies do as far as
> reorg'ing large database(s). In my case I have a Peoplesoft
> database that
> is 336 gigs (used). Recent testing proved that a full database reorg
> (export/import) takes ~90 hrs. The last time I performed a reorg my
> database was ~220 gig and I was able to complete this in a weekend.
>
> I have presented several options (listed below) to management
> but they are
> very much interested in knowing how others deal with large
> databases and
> reorgs. If you have a moment and perform(ed) some type of
> database reorgs
> on a regular basis please share with me your approach.
>
> TIA
>
> Option 1
> Full database reorg using dbexport/dbimport.
>
> Option 2
> Break 1 large database into multiple databases (3-4) (by
> module has been
> discussed).
> Use synonyms from the primary database to point to tables
> located in
> other databases.
> Tables reside in "db specific dbspaces (ie, databaseA dbspace1 -->
> dbspace6, databaseB dbspace6 --> dbspace12,.....you get the idea)"
> Use dbexport/dbimport to reorg smaller databases over
> multiple weekends.
> (one thing to note here, psoft admins would not be fond
> due to dddaudit
> reports)
>
> Option 3
> (I like to call this a "reorg by dbspace") Group tables and indexes
> together based on a percent of the database into a set of dbspaces.
> Unload all tables, drop all tables, recreate tables and load
> 1 after the
> other, recreate indexes in this set of dbspaces. This would
> basically
> represent a bastardized export and import of all tables in
> the specific
> dbspaces thus, the ultimate goal, eliminating interleaving.
>
>
> Little info about my env:
> IDS 9.40 FC2
> HPUX 11
> 6 way nClass w/550's
> 6 gig memory
>
> LOCKS 5000000 # Maximum number of locks
> BUFFERS 1400000 # Maximum number of shared buffers
> NUMAIOVPS 4 # Number of IO vps
> PHYSBUFF 256 # Physical log buffer size (Kbytes)
> LOGBUFF 128 # Logical log buffer size (Kbytes)> LOGSMAX 200 # Maximum number of logical log files
> CLEANERS 128 # Number of buffer cleaner processes
> SHMBASE 0x0 # Shared memory base address
> SHMVIRTSIZE 800000 # initial virtual shared> memory segment
> size
> SHMADD 32768 # Size of new shared memory segments
> (Kbytes)
> SHMTOTAL 0 # Total shared memory (Kbytes).
> 0=>unlimited
> CKPTINTVL 300 # Check point interval (in sec)
> LRUS 128 # Number of LRU queues
> LRU_MAX_DIRTY 2 # LRU percent dirty begin> cleaning limit
> LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit
> LTXHWM 50 # Long transaction high water mark> percentage
> LTXEHWM 60 # Long transaction high water mark
> (exclusive)
> TXTIMEOUT 0x12c # Transaction timeout (in sec)
> STACKSIZE 64 # Stack size (Kbytes)>
>
>
>