database reorg for large databases?
Posted in 2005
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)
sending to informix-list