Re: real time data load

Leon Rzhemovskiy <lrzhemov_at_home.com> wrote:

>Any other recommendation for high volume real time data processing
>would be appreciated.

In no particular order:

Table storage parameter tuning becomes important, if there are many concurrent processes inserting this rate of data, lots of freelists, big extents, and possibly freelist groups (even on a single instance they can help under extreme load by giving multiple freelist header blocks).

You'll be generating a lot of redo data, I've seen more than 3MB/s (while doing 25000 updates/inserts per second). Log files become a real pain, at this rate, even very large logfiles roll-over very quickly, e.g. a 3 gigabyte logfile will switch every 15 minutes or so. Make sure you have fast enough disks to cope with this.

On the same point, I'd recommend the trick of a large log buffer and a low _log_io_size as the best way to avoid log buffer waits while keeping LGWR steadily active.

Be careful about how often you commit. If you can get away with not commiting after every record it'll be a big win. I've seen effective use made of large array inserts, and commiting after each array.

Try and keep the indexes on the relevent tables as lightweight as possible.

If all else fails, throw disk at the problem, wide stripes and raw datafiles work wonders for performance. :-)

-- 
Andrew Mobbs - http://www.chiark.greenend.org.uk/~andrewm/