I am having problems to maintain a very large database. In fact the
product has just been installed and we are trying to fine tune the
application. All the transactions are recorded into a single table
that can't be split (but can be partitioned).
Here are the facts :
- This table has 6 attributes and 1 index on 2 of the attributes,
structure is very simple.
- No more than 10 concurrent users on the system to query data
- Table records 6000 rows /min and it grows 246 MB/day
- Its size by the end of the year is expected to be 90 GB and
it will hold 3 153 600 000 rows …
- At the end of the year all data older than 1 year will be archived
in another table
- So this table will not grow beyond 90GB
- We are going to upgrade Oracle to the Enterprise edition
for partitioning and bitmap indexes
- The system runs on Window 2000 advanced server
- We have a Compaq hardware with two 1266 MHZ CPUs
- 2,4 GB RAM
- Controller N1: (Disk C 17 GB Raid 1: hold OS),
(Disk D 17 GB Raid 0: holds temp space)
(Disk E 17 GB Raid 1: holds Oracle )
- Controller N2: (Disk F 34 GB Raid 5: holds indexes)
(Disk H 101GB Raid 0+1: holds data)
My questions are :
- What type of backup should we use ? (We are thinking about
replication
and incremental backups or maybe a third machine)
- Our write performance is very good. However we have some problems
with
reads (at present we have 15GB of data and 320 000 000 rows). The
first
read for a given query takes 52 seconds. Then the second time the
query
runs in 12 seconds with a 100% cache hit ratio. What type of
hardware
(controllers and disks) should we use to improve performance (52
seconds)?
Is there any thing to do to reduce these 12 seconds cache reads ?
- I have tried to rebuild the index on the table after having dropped
it.
It is still running ... I had to configure a 15GB temporary table
space.
Any advise to speed up the index reconstruction ?
- What would be the benefit of switching from NT to Unix ?
- If somebody has a similar sized system, could you indicate us what
type
of hardware you have ?
Thanks for your time, Andras
Received on Tue Oct 08 2002 - 07:08:40 CDT
