Re: Bottleneck?, Insert and select on the same table.....

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Wed, 08 Nov 2006 14:49:20 +0100
Message-ID: <4re5j0Fr1hi6U1@mid.individual.net>

On 08.11.2006 12:03, mariorossi2010_at_yahoo.it wrote:
> I should make an analysis about an infrastructure that a customer would
> be organize for its business.
>
> The goal of the activities is to make available, for online
> consultation a big amount of data.
> The problem is that the data should be availabile in a table
> simultaneously to the insertion of the same ones.
>

>>From an initial analysis, we have to manipulate about 6Gb of data for a

> day.
> There are 12 server which will produce, every x minutes, a text file .
> These files will have to be converted and inserted into a table,
> probably through SQLLoader, meanwhile users
> could perform queries on the same table.
> Initially this table is empty.
> Every second will be produced 14 rows for each server(so 14*12=168 rows
> in total); considering each file
> it would have to be generated every x minutes, we have 168*60*x=10080*x
> rows every x minutes.
> A row size is about 420 byte, so in total we have 10080*x*420=4233600*x
> byte (4134,375 kbyte) every x minutes.
>
> Considering this amount of data, are there any condition that could
> produce a bottleneck?
> What is an approach to solve the problem related to make insert and
> select at the same time on the same table?
>
> I refer to the amount of data manipulates on disk I/O.
> I thought that a great amount of data could create slowing down and
> therefore decay of the performances.
>
> I hope to have introduced clearly the situation, so you can help to
> relieve the customer to make the right choice.

You'll certainly have to carefully design your IO subsystem. Ideally you will have separate physical channels you can store data, logs - maybe also a separate channel for index data and temp. That all heavily depends on the nature of the data and queries.

If I am not mistaken you have 70,560 bytes / sec raw data which does not sound too dramatic. Even if the DB will write more than this (management data in blocks, indexes, rollback, undo etc.) this still seems pretty far from modern IO subsystem throughputs. That's the write side.

For the read side we have no information about the nature of the data and the queries and how often they are executed etc. This can increase requirements for IO dramatically (for example if queries access the whole table vs. only recent added rows which might be still in the cache).

And then there is also deletion. You mention that you want to insert 6GB raw data per day. Do you also want to delete? If so what's the retention period? etc.

Kind regards

        robert Received on Wed Nov 08 2006 - 07:49:20 CST