Re: I/O per sec. and tps experiences ?

Hallo John,

comments inline.

"John Anon " wrote:
>
> I understand that the general way to get high (400-10000) tps (transactions
> per second) is more spindles (disks). It seems that this will not always
> work. Your problem has to be parallelizeable is the way I would put it. Further
> you must do the parallelization by either making sure that the locks are
> (usually) on separate tables or pages (I think oracle works by pages right?),
> or that you batch many transactions within a single begin / end trans.
>
> So my questions are :
> 1) Did i describe the situation correctly above ?

No, not quite. Oracle is locking row-wise. If a task is not parallelizable this usually means that you contend for a shared resource, i. e. a dependency between processes. Example: you want to give consecutive numbers to a column. If you want to make sure that no gaps arise you have to protect each request for a new number by a transaction. Here your number source is the resource because if ten tasks at the same time ask for a new number they have to be serialized thus prohibiting parallelization. So look for serialization requests. They prevent parallelization.

You are right in stating that this serialization request may be the request for service by a specific spindle. You may be lucky to spread your requests to several spindles if you stripe your volume. But in the example given (a gapless counter) it is one spot at the disk where a stripeset won't help.

Look out for indexes as well. It is not sufficient to parallelize accesses to your data.

Transactions by definition are guarded against each other. So if you batch them you are changing your processing scheme.

> 2) How often have you all been stuck with legacy code that is not parallelized
> ?

Way too often. Most programmers are not aware of concurrency situations. Further, very often they do not exactly know which sql code is generated by a specific tool.

> 3) How often do you encounter problems which are inherently sequential in
> practice ?

In an OLTP system you may achieve high concurrency but in a datawarehouse you may have to wait for a loading task to finish etc. So it depends on the type of system.

> 4) Is the only solution to (3) using a solid state drive if you need transaction
> integrity ?

No, not the only solution but a very good one if you have heavy writing to the online redologs and put those on the SSD.

Martin Received on Thu Apr 26 2001 - 04:50:35 CDT