Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the differences between OLTP and OLAP". In daily operation, I believe many people have doubts about the differences between OLTP and OLAP. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the questions of "what are the differences between OLTP and OLAP?" Next, please follow the editor to study!

OLTP, OLAP, VDI and SPC-1 are three common business scenarios in current performance evaluation. SPC-1 is a random IOPS IO model commonly used in the industry, which is often used to evaluate the performance when the actual business type is not clear. The simple IO characteristics of the four models are shown in the following table.

Oracle database is a typical OLTP business model, which is widely used in core IT business systems. OLTP-type Oracle databases often carry enterprise core business support systems, such as ERP, CRM, etc., and there are problems in their performance and availability. This chapter focuses on the main differences between OLTP and OLAP, planning methods and best practices based on Oracle.

Introduction of OLTP and OLAP

Data processing can be divided into two categories: online transaction processing OLTP (On-line transaction processing) and online analytical processing OLAP (On-Line Analytical Processing). OLTP is the main application of traditional relational database, which mainly deals with basic and daily transactions, such as bank transactions. OLAP is the main application of data warehouse system, supporting complex analysis operations, focusing on decision support, and providing intuitive and easy-to-understand query results.

Comparison between OLTP and OLAP:

IO characteristics of OLTP applications

OLTP usually refers to a highly transactional online system, which focuses on small transactions and small queries. When evaluating its system, it generally looks at the number of Transaction and Execute SQL executed per second. In such a system, a single database often processes more than hundreds or thousands of Transaction per second, and the execution of Select statements is thousands or even tens of thousands per second. Typical OLTP systems include e-commerce systems, banks, securities, etc.

Each I-2KB~8KB O is very small, usually the one

The location of accessing disk data is very random.

At least 30% of the data is random write operations.

Online redo logs are written very frequently sequentially.

1, business characteristics: each transaction read, write, change the amount of data involved is very small, at the same time, there are many users connected to the database, using the database, requiring the database to have a fast response time, usually a transaction completed in a few seconds, delay requirements are generally 10-20ms.

2. IO characteristics: for DATA LUN, the random small IO,IO size is mainly 8KB (the IO size is the same as the Block block size of the database), the read-write ratio is about 3:2, the read is completely random, and there is a certain combination of writes. For LOG LUN, the multi-channel sequence is small IO, the size is variable, almost all write IO.

In addition to the CPU of the server, the place where the OLTP system is most prone to bottleneck is the processing capacity of the storage system IOPS. Because in the OLTP environment, the hard disk physical read is generally db file sequential read, that is, a single block of physical read, but the frequency of this read is very frequent. If it is so frequent that the hard subsystem is unable to host its IOPS, there will be major performance problems.

IO characteristics of OLAP applications

OLAP system, also known as DSS decision support system, is what we call data warehouse. In such a system, most of the time, report jobs are running on the database, performing basically aggregate SQL operations, such as Group by, and scanning a very large number of rows at the same time, a query will take hours or even days, and a large amount of data can be read at a time; generally, there are no data modifications, or only very few data modifications:

A single 64KB~1MB O is very large, and the typical value is

The read operation is a sequential read

When a read operation occurs, the write operation that occurs is usually in the temporary tablespace

There are usually very few writes to online logs, except when bulk loading data

1. Business characteristics: there are generally few data modifications, except when bulk loading data; the system calls very complex query statements and scans a very large number of rows at the same time; a query will take hours or even days; it mainly depends on the complexity of the query statement; the output of the query is usually a statistical value, obtained by group by and order by; when the read operation is carried out, the write operation that occurs is usually in the temporary table space Usually, there are few writes to online logs, except when bulk loading data; analytical services generally do not require delay.

2. IO features: for DATA LUN, multi-channel sequential large IO (can be approximately regarded as random large IO), IO size is related to the striping size set on the host side (such as 512KB), more than 90% are read business, mixed intermittent read and write. For TMP LUN, random IO, mixed read and write (write first and then read, write when computing, read when reading temporary table, mostly write, accounting for a small part of the whole business IO), the size of IO is basically a large IO above 200KB.

The most likely bottleneck of OLTP system is the bandwidth of storage system. The bandwidth of the array often depends on the number of front-end networks and back-end hard disks from the host to the array. At this time, the array CACHE is basically ineffective, and the read and write types of the database are basically db file scattered read and direct path read/write.

In practical application, since a large amount of detailed data is stored in OLTP, why not analyze and process it directly on OLTP?

Because OLTP is mainly designed to manipulate data, it is used to handle known tasks and loads: common optimizations lie in master code indexing and hashing, retrieving specific records. To optimize some specific query statements.

On the other hand, OLAP is designed to analyze data (data warehouse). The way of query is often complex and unknown, which usually involves the calculation of a large amount of data after summarization. The performance of this data operation based on multi-dimensional view on OLTP will be very poor and extremely dangerous.

But OLAP system data sources and a variety of OLTP databases. Because the data stored in OLTP system is often heterogeneous, OLAP system needs to ETL and merge all kinds of heterogeneous data from OLTP.

Separate design and optimization

Special attention should be paid to the design, such as in the highly available OLTP environment, do not blindly bring the OLAP technology to use.

Such as partitioning technology, assuming that partitioning keywords are not widely used, but other fields are used as where conditions, then if it is a local index, multiple indexes will have to be scanned, and the performance will become even worse. If it is a global index, it loses the meaning of partitioning.

The same is true of parallel technology, which is generally used only when completing large tasks, such as in real life, when translating a book, you can arrange multiple people to translate different chapters first, which can improve the speed of translation. If you just translate a page of a book, assign different people to translate different lines, and then combine them, it will not be necessary, because in the time assigned to work, a person may have already finished the translation.

The same is true of bitmap indexes, which can easily cause blocking and deadlocks if used in an OLTP environment. However, in the OLAP environment, the query speed of OLAP may be improved because of its unique characteristics. MV is basically the same, including triggers, etc., in DML frequent OLTP systems, it is easy to become a bottleneck, or even Library Cache waiting, while in OLAP environment, it may improve the query speed because of proper use.

Database template

Prior to Oracle 10g, the templates available for database building are: Data Warehouse (data warehouse), General Purpose (general purpose, general purpose), New Database, and Transaction Processing (transaction processing).

In the process of building a database for the version of Oracle 11g, there are templates to choose from: general purpose or transaction processing, customized database, data warehouse, etc. Personal understanding of these templates is:

Online analytical processing (OLAP) has a large amount of data and less DML. Using data Warehouse templates

Online transaction processing (OLTP), with less data, frequent DML and more parallel transactions, is generally very short. Use general purpose or transaction templates.

Decision support system (DDS,Decision support system), the typical operation is full table scan, long query, long transaction, but the number of general transactions is very small, often a transaction exclusive system.

Best practic

Oracle database is widely used in the core IT business system, and the planning and configuration of storage subsystem is very important. Unreasonable storage planning often leads to poor performance of IT system, even availability and data reliability can not be guaranteed. OLTP-type Oracle databases often carry the core business support systems of the enterprise, such as ERP, CRM, etc., and their performance and availability problems will directly lead to inefficiency or even interruption of enterprise operations.

In this paper, the OLTP service test model is verified by SwingBench Order Entry. An online order service is defined in this business model, which simulates a large number of users to log in to the system to execute the most common operations of the trading system, such as product query, order issuing, order processing, order viewing and so on. There are two main performance indicators of the business model: transactions per minute (TPM) and average transaction response time. TPM represents the volume of transactions that the system can handle per unit time, and TPM is high, which represents greater productivity. Transaction response time directly affects the speed of user operation completion, and the transaction response time is low, which represents a better user experience.

The Order Entry business model defines 9 tables to record product, customer, order, warehouse, login and other information. When performing a load test, 50% are query operations, 30% are insert operations, 20% are update operations, and there are no delete operations. From the perspective of OLTP O layer, the business model is small data block random access, and the read-write ratio is 6:4, which represents the most typical IPUP business model.

In SAN (Storage Area Network) networking, two physically independent switching planes are used (each switching plane includes one switch or multiple cascaded switches), each database node is connected to two switching planes, and each storage controller is connected to two switching planes.

Schematic diagram of Oracle RAC networking

For Oracle databases, the queue depth of Imax O is an important parameter that affects performance. There are two parameters in the operating system layer that affect the queue depth: block device queue depth and HBA card queue depth. It is recommended to configure the block device queue depth and the HBA card queue depth according to the following policy.

For the Linux operating system, the maximum queue depth of the block device is 128, and the queue parameters of the HBA card are related to the card type and driver. Please refer to the specification values given by the HBA manufacturer, such as Qlogic 8Gbps FC dual-port HBA card, which limits the maximum queue depth per LUN to 32. On the other hand, it is suggested to increase the number of LUN to improve the depth of the overall Imax O queue.

For AIX operating systems, Huawei recommends installing UltraPath multipathing, rather than using system multipathing or third-party multipathing. With Huawei UltraPath multipathing installed, the maximum queue depth of block devices is adjusted to 32. If Huawei UltraPath is not used, the system default maximum queue depth of block devices is 5. It is recommended to modify this value to 32 or higher. The default maximum queue depth of AIX's HBA card is 200, which can be adjusted according to the actual business needs.

For Windows operating systems, the maximum I _ max O queue depth of a single LUN also depends on the specification given by the HBA card manufacturer.

Under the Oracle 11g database OLTP service, it is recommended to adjust the following parameters, and the optimal value of the parameters should be tested and adjusted according to the actual business to obtain the best performance and reliability. The following table lists the meaning and recommended values of the key parameters:

SwingBench test is used to configure the number of user sessions. The tested performance is as follows:

Best practices describe a planned configuration scenario for deploying an Oracle database based on a storage system and provide a validated planning configuration reference architecture. When planning and deploying Oracle 11g, users can use the networking, parameter setting, test methods and other information provided to guide them in practice, so as to reduce the burden of scheme planning and the risk in the process of implementation.

At this point, the study on "what is the difference between OLTP and OLAP" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.