Teach you how to design a large Oracle database
- 2020-06-19 11:56:09
- OfStack
Introduction to 1.
The characteristics of vLSI are as follows:
1. More than 1 million users, some more than 10 million, and more than 1TB database;
2, the system must provide real-time response function, the system should not stop running, the system is required to have high availability and scalability.
In order to achieve the above requirements, in addition to the need for superior performance of computers and mass storage equipment, but also the need for advanced database structure design and optimization of the application system.
The super - large system adopts double or multi - machine cluster system. The following is an example of using Oracle 8.0.6 parallel server to talk about the design method of very large database:
Determine the system's ORACLE parallel server application partition strategy
Design of physical structure of database
System hard disk division and distribution
Consideration of backup and recovery strategy
2. Oracle parallel server applies partition strategy
The Oracle parallel server allows multiple INSTANCES of INSTANCE on different nodes to access one database simultaneously to improve system availability, scalability, and performance. Each INSTANCE instance in the Oracle parallel server can read a block of data from a table or index in the Shared database into a local buffer, which means that one block of data can exist in the SGA block of multiple INSTANCE instances. It is important to keep the data of these buffers at 1. Oracle USES PCM(Parallel Cache Management) locks to maintain 1 alignment of buffers, Oracle simultaneously implements PCM locks through I DLM(integrated distributed lock manager), and data alignment between INSTANCE instances is achieved through specialized LCK processes.
Consider the case where INSTANCE1 modifies the BLOCK X block and INSTANCE2 also modifies the BLOCK X block. Oracle parallel server USES PCM locking mechanism to make BLOCK X write to the database data file from SGA block of INSTANCE 1, and read BLOCK X block into SGA block of INSTANCE2 from the data file. This happens to be an PING. PING makes the work that one MEMORY IO could do become two DISK IO and one MEMORY IO. If there are too many PING in the system, the performance of the system will be greatly reduced.
Each PCM lock in the Oracle parallel server can manage multiple blocks of data. The number of blocks managed by the PCM lock is related to the number of PCM locks allocated to a data file and the size of the data file. When INSTANCE 1 and INSTANCE 2 operate on different BLOCK, PING still occurs if these BLOCK are managed with one PCM lock. These PING are called FALSE PING. The PING produced when multiple INSTANCE visit the same BLOCK is TRUE PING.
Reasonable application segmentation enables different applications to access different data, which can avoid or reduce TRUE PING. The number of FALSE PING can be reduced by allocating more PCM locks to more FALSE PING data files. Increasing PCM locks does not reduce TRUE PING.
Therefore, the design of the Oracle parallel server aims to make the system transaction processing reasonably distributed among INSTANCE instances to minimize PING, and at the same time reasonably allocate PCM locks to reduce FALSE PING. The key to design is to identify the conflicts that may arise to determine the strategy for applying the partitioning. There are four ways to apply division as follows:
1. According to functional modules, different nodes run different applications
2. According to user division, different types of users run on different nodes
3. Different nodes access different data or indexes according to data partitioning
4. According to the time division, different applications run in different time periods
Two important principles to apply partition are to minimize PING and to roughly balance the load on the nodes.
3. Design of physical structure of database
Database physical structure design includes determining the physical storage parameters of tables and indexes, determining and allocating database table space, determining the initial rollback segment, temporary table space, redo log files, and determining the main initialization parameters. The purpose of physical design is to improve the performance of the system. The parameters of the whole physical design can be adjusted according to the actual operation.
Table and index data volume estimation and physical storage parameter setting
The storage capacity estimates for tables and indexes are based on the length of their records and the maximum number of records estimated. The header overhead of data block and the header overhead of records and fields are considered in capacity calculation. The initial and next storage parameters for tables and indexes are generally set to equal and pctincrease to 0.
The characteristics of vLSI are as follows:
1. More than 1 million users, some more than 10 million, and more than 1TB database;
2, the system must provide real-time response function, the system should not stop running, the system is required to have high availability and scalability.
In order to achieve the above requirements, in addition to the need for superior performance of computers and mass storage equipment, but also the need for advanced database structure design and optimization of the application system.
The super - large system adopts double or multi - machine cluster system. The following is an example of using Oracle 8.0.6 parallel server to talk about the design method of very large database:
Determine the system's ORACLE parallel server application partition strategy
Design of physical structure of database
System hard disk division and distribution
Consideration of backup and recovery strategy
2. Oracle parallel server applies partition strategy
The Oracle parallel server allows multiple INSTANCES of INSTANCE on different nodes to access one database simultaneously to improve system availability, scalability, and performance. Each INSTANCE instance in the Oracle parallel server can read a block of data from a table or index in the Shared database into a local buffer, which means that one block of data can exist in the SGA block of multiple INSTANCE instances. It is important to keep the data of these buffers at 1. Oracle USES PCM(Parallel Cache Management) locks to maintain 1 alignment of buffers, Oracle simultaneously implements PCM locks through I DLM(integrated distributed lock manager), and data alignment between INSTANCE instances is achieved through specialized LCK processes.
Consider the case where INSTANCE1 modifies the BLOCK X block and INSTANCE2 also modifies the BLOCK X block. Oracle parallel server USES PCM locking mechanism to make BLOCK X write to the database data file from SGA block of INSTANCE 1, and read BLOCK X block into SGA block of INSTANCE2 from the data file. This happens to be an PING. PING makes the work that one MEMORY IO could do become two DISK IO and one MEMORY IO. If there are too many PING in the system, the performance of the system will be greatly reduced.
Each PCM lock in the Oracle parallel server can manage multiple blocks of data. The number of blocks managed by the PCM lock is related to the number of PCM locks allocated to a data file and the size of the data file. When INSTANCE 1 and INSTANCE 2 operate on different BLOCK, PING still occurs if these BLOCK are managed with one PCM lock. These PING are called FALSE PING. The PING produced when multiple INSTANCE visit the same BLOCK is TRUE PING.
Reasonable application segmentation enables different applications to access different data, which can avoid or reduce TRUE PING. The number of FALSE PING can be reduced by allocating more PCM locks to more FALSE PING data files. Increasing PCM locks does not reduce TRUE PING.
Therefore, the design of the Oracle parallel server aims to make the system transaction processing reasonably distributed among INSTANCE instances to minimize PING, and at the same time reasonably allocate PCM locks to reduce FALSE PING. The key to design is to identify the conflicts that may arise to determine the strategy for applying the partitioning. There are four ways to apply division as follows:
1. According to functional modules, different nodes run different applications
2. According to user division, different types of users run on different nodes
3. Different nodes access different data or indexes according to data partitioning
4. According to the time division, different applications run in different time periods
Two important principles to apply partition are to minimize PING and to roughly balance the load on the nodes.
3. Design of physical structure of database
Database physical structure design includes determining the physical storage parameters of tables and indexes, determining and allocating database table space, determining the initial rollback segment, temporary table space, redo log files, and determining the main initialization parameters. The purpose of physical design is to improve the performance of the system. The parameters of the whole physical design can be adjusted according to the actual operation.
Table and index data volume estimation and physical storage parameter setting
The storage capacity estimates for tables and indexes are based on the length of their records and the maximum number of records estimated. The header overhead of data block and the header overhead of records and fields are considered in capacity calculation. The initial and next storage parameters for tables and indexes are generally set to equal and pctincrease to 0.