Re: Help with 20 million records

Hi Emery,

Emery Lam schrieb:
> We are planning to build an archive and retrieval system to store one
> year of multimedia data. The database needs to hold 20 millon records
> total, with 1.5 million items added each month and 1.5 million items
> deleted each month after the initial year. The indexing requirement
> is rather minimum, the data will mostly be PDF with average size of
> 50KB, but it could be XML, HTML or JPEG/TIFF. Retrieval volume has
> not been determined yet but it should be fairly low, ~20%.
>
> We are touting a few ideas:
>
> 1. Store everything in one table with data stored as BLOBs. This is
> the simplest approach but I am concern with performance, both with
> insert and delete. I would love to do this but may have difficulties
> convincing management that preformance is not an issue.
>
> 2. Store index data only in one table and use the filesystem for the
> multimedia data. We have used this approach with a 1 million item
> table and it performs very well. However I worry about disk
> fragmentation for 20M files.
>
> 3. Create one table of index and BLOBs data for each month and a
> lookup scheme to locate the appropriate table for retrieval. Deletion
> (rolloff) is simple under this approach and performance should be
> quite deterministic.
>
> Hopefully someone out there can shred some lights on these approaches.
> I am mostly interested in real life volume data, is 20M record too
> much for one table? How many insert/delete can we expect? I are
> fairly new to Oracle, particularly with storing BLOBs data, does it
> significantly degrade performance?

About two years ago we did a feasibility study and a comparison between different RDBMS for a new production control system. We investigated for a system that should store up to 20 millions of records per year, each with a blob to store up to 15 KB of binary data.

Using Oracle 8.1.5, we could insert up to about 10000 records per minute in an table with up to 15 millions records (there was no more disk storage to continue testing). Deleting was quite as fast. The hardware was a 4 CPU system with 500 MHz Xeon CPU, 4 GB RAM and an independent storage subsystems, connected via a SCSI loop to the CPU. We had different fixed disks for OS, TEMP, SYSTEM TABLESPACE, USER TABLESPACE, INDEX TABLESPACE, ROLLBACK SEGMENTS, REDO LOGS to optimize througput and we did not use partitioning for the table containing the blobs.

But keep in mind that you have about 50KB * 20M = 1 TB of raw data to store and administer.

We reduced the size of our database due to backup and size reasons by reducing the retaining time for the records in the database. But as far as i understand what you want to do, you cannot reduce this value. Test the backup and restore time you need for this db and check, if you can be fast enough.

-- 
Due to spam, this email address is only used for newsgroup postings.

All emails sent to this address are immediately deleted. Therefore any
eMails sent directly to this address never will be answered.

Mit freundlichen Grüßen / Best Regards
Michael Gast
SEPP MED GmbH