Research on possibility of RDBMS to have performance benchmarks like no-sql Databases (part 5) 

We are continuing our discovery for a new OLTP Design (https://ratneshparihar.wordpress.com/2014/07/31/research-on-possibility-of-rdbms-to-have-performance-benchmarks-like-no-sql-databases-part-3/) .

in last post we looked into Main memory changes (https://ratneshparihar.wordpress.com/2014/08/05/research-on-possibility-of-rdbms-to-have-performance-benchmarks-like-no-sql-databases-part-4/)

Today we will look into multi threading.

 

 

23

 

OLTP transactions are very lightweight. For example, the heaviest transaction in TPC-C reads about 400 records. In a main memory environment, the useful work of such a transaction consumes less than one millisecond on a low-end machine. In addition, most OLTP environments we are familiar with do not have “user stalls”. 

For example, when an Amazon user clicks “buy it”, he activates an OLTP transaction which will only report back to the user when it finishes. Because of an absence of disk operations and user stalls, the elapsed time of an OLTP transaction is minimal. In such a world it makes sense to run all SQL commands in a transaction to completion with a single threaded execution model, rather than paying for the overheads of isolation between concurrently executing statements. 

Current RDBMSs have elaborate multi-threading systems to try to fully utilize CPU and disk resources. This allows several-to-many queries to be running in parallel. Moreover, they also have resource governors to limit the multiprogramming load, so that other resources (IP connections, file handles, main memory for sorting, etc.) do not become exhausted. These features are irrelevant in a single threaded execution model. 

No resource governor is required in a single threaded system. In a single-threaded execution model, there is also no reason to have multi-threaded data structures. Hence the elaborate code required to support, for example, concurrent B-trees can be completely removed. This results in a more reliable system, and one with higher performance. 

At this point, one might ask “What about long running commands?” In real-world OLTP systems, there aren’t any for

two reasons: First, operations that appear to involve long-running transactions, such as a user inputting data for a purchase on a web store, are usually split into several transactions to keep transaction time short. In other words, good application design will keep OLTP queries small. Second, longer-running ad-hoc queries are not processed by the OLTP system; instead such queries are directed to a data warehouse system, optimized for this activity. 

There is no reason for an OLTP system to solve a non-OLTP problem. Such thinking only applies in a “one size fits all” world.