Our DB is currently 165GB and continuing to grow. You just have to keep
at the constant task of tuning it.
One thing that you have to watch very carefully is the Database Cache
Hit Ratio. We were hung up by lack of granularity in it. We had a
problem I didn't even know about. It was always showing above 99% -
which gave me false confidence. That was because we were looking at it,
and resetting it, only once a day. Turned out that during some periods
of the day it would go as low as 90%, which explained why expiration
would never complete and deadly database bloat was setting in. I set a
schedule of checking and resetting it hourly, which has provided much
better data, and as a result I have greatly grown the Database Cache -
adding a gigiabyte of real memory to it. Basically, I threw real memory
at the Database Cache until AIX reported that it was beginning to page.
The database is much, much faster and happier now. I've ordered more
memory for this system, and I plan to put much of it into DB Cache.
If you get behind on expiration, it may take WEEKS to catch up.
Striping and other forms of RAID is probably a poor idea. Think about
it. The DB has a very large number of randomly distributed small I/Os,
so making more than one disk disk arm move per I/O is a waste. Instead
that other disk arm is better used moving for another separate I/O
request. One arm per thread, but lots of arms for lots of threads. The
ITSM Database works best on a large number of separate JBOD disks,
mirrored by ITSM. I'm using (two mirrored pairs of) 20 9gb dbvols, which
are arranged two dbvols per 18gb 10,000RPM physical volume. I am told
that it works best to have two dbvols per physical volume, and I have
set it up this way, though I have no proof that this is best.
Be sure to set mirrorwrite DB to Parallel. I have definite proof that
this speeds things up a lot.
One of the hugest payoffs in tuning database performance was much, much
faster client backup times. We had big clients who were taking longer
than 24 hours to to back up, that now take 4 hours. I may actually be
able to LOWER the session limits.
One pitfall of speeding up the database is that you can easily fill the
Log during Database backup. Especially if you're in catch-up mode on
expiration. Remember that DB Backup pins the Log. Expiration adds to the
log, so if you've made expiration run faster, you've got a problem. I
found I had to cancel expiration during database backup.
At this point, the greatest bottlenecks I see in an ever-growing
database are reclamation and database backup. Expiration is a solveable
problem - just make the database run fast enough for everything else,
and you can expire in time. I am considering attacking the reclamation
issue with a process that monitors volume contents and issues MOVE DATA
commands when not enough reclamation is underway and empty tape drives
are available.
Database backup (and restore) times have remained pretty much unchaged
as the database has grown, because tape drive speed has grown at about
the same pace. I recall having panic attacks when it was just 3GB, on
IBM 3480 drives, and I'm actually less concerned about it this these
days with Super-DLT drives and a 165gb database. My rule-of-thumb here
is that if the database backup fits on one tape cartridge, you're OK. So
if you've got SDLT2 drives, you can easily go to 300gb.
The one hard and fast limit that I keep having to figure out how to work
around, is the limit of 13gb Log. I have to monitor Log fullness
constantly. THAT is forcing me to consider that this databse should
really not grow much larger. If it does, Log fillup emergencies will
become more frequent, and Murphy's Law says the Log will always fill up
on a Saturday during nice weather.
I'm planning to split this server image into 6 or 8 images, on the same
physical machine, but that is a long-term project, to follow the example
of my esteemed colleague Alan Rout of U. Florida who has done exactly
this. But that is a long-term project.
So, my approach to a 165gb database is to live with it, tune it, throw
faster hardware at it, watch it grow carefully, and plan to break it
apart in the future.
Roger Deschner University of Illinois at Chicago rogerd AT uic DOT edu
======I have not lost my mind -- it is backed up on tape somewhere.=====
|