The db2 tutorial I am reading is: DB2 UDB V8.1 Management Study Notes (I). DB2 Basic Concept
Several concepts from top to bottom in DB2:
Instance,
Database,
TableSpace,
Container
In an operating system, the DB2 data service can run multiple instances at the same time (unlike Oracle can only have one instance in a system).
Database definition In an instance, an instance can contain multiple databases. Different databases in the same instance are completely independent and have their own independent system catalogs.
There are two management methods for table space:
DMS (Database management space) method
SMS(System manegement Space) method
DMS and SMS methods are specified when the tablespace is created and cannot be converted after the creation. For DMS mode, a tablespace corresponds to one or more containers, and the container specifies the physical storage location of the data. For SMS mode, only one directory can be specified and cannot be added.
The tablespace has the following types:
System Catalog Table Space (SysCatSpace)
System Temporary Table Space (SysTempSpace)
User tablespace (UserSpace)
User Temporary Table Space (UserTempSpace)
There must be two basic system table spaces in a database, namely the system catalog table space and the system temporary table space. Any object created in the database is reflected in the way of adding records to the system catalog table space. For temporary table space, its disk size is dynamically scalable according to usage, that is, disk space is allocated only when needed and recycled after use. In addition, if the user needs to create a table, he needs to create a user tablespace. If he needs to use a temporary table, he needs to create a user temporary tablespace.
There are three types of containers:
Files Files
Devices
Directory Directory
Files and devices, tablespaces used in DMS mode;
Directory is used for SMS table space. This method does not require manual management of data storage files. DB2 can automatically add storage files in the directory according to the situation, as long as disk space allows.
In essence, table space is the logical location definition of data storage, while container is the physical location definition of data storage.
Database performance
The main factors affecting the performance of a database are the following:
Disk
Memory
Processor (CPU)
Network
Among them, disks are the most significant, and 90% of the performance bottlenecks may come from disk IO competition;
The second is memory. On the one hand, it means that the total amount of physical memory must meet the needs, and on the other hand, it means that the configuration parameters related to memory should be configured correctly;
Of course, the performance of the processor is also very important. Multiple CPUs will have significant effects on complex SQL queries that rely on computing power;
The network is not a main factor, but an objective environmental factor, which means that too slow network speed will affect the transmission of data. Here are some effective ways to improve database performance:
For servers running database services, multiple physical disks can be configured as much as possible, and the capacity of each block does not have to be too large, which can effectively share the competition between data storage and disk IO during the read operation. That is, the performance of using multiple small-capacity disks is better than using only one large-capacity disk.
If conditions permit, try to make the data storage service and the operating system run on physically separate disks.
Table space that adopts DMS (Database Management Space) management method.
Create multiple tablespaces on physically different disks. Then the data and indexes can be stored in different table spaces separately, which can significantly improve performance. You can also split a frequently used large table vertically into multiple small tables, store them in different table spaces, and then combine them with a view.
The DB2 server can manage bare devices, that is, except for the system and DB2 service running disks, it prepares a separate disk for DB2 data storage. It can be multiple blocks and does not require formatting after partitioning. After creating the bare device, it is directly handed over to DB2 for management and storage of data.
The system's temporary table space has a great impact on database performance. When the managed physical memory cannot meet the needs of database operations, DB2 will write temporary data to disk, and the system's temporary table space is used, and this situation often occurs.
Try to place data near the innermost track of the disk, because the disk access speed here is faster.
Main performance-related parameters
The parameter configuration of DB2 is divided into two levels, one is the instance level and the other is the database level. Parameters that have a greater impact on data service performance are mainly configured at the database level. The following are three more important memory configuration parameters:
bufferpagelocklistsortheap
bufferpage: Shared by all objects in the same database.
sortheap: The memory swap area used for sorting is not shared and should not be set too large, otherwise it will easily cause memory exhaustion, because each transaction will apply for independent memory for sorting.
Several concepts from top to bottom in DB2:
Instance,
Database,
TableSpace,
Container
In an operating system, the DB2 data service can run multiple instances at the same time (unlike Oracle can only have one instance in a system).
Database definition In an instance, an instance can contain multiple databases. Different databases in the same instance are completely independent and have their own independent system catalogs.
There are two management methods for table space:
DMS (Database management space) method
SMS(System manegement Space) method
DMS and SMS methods are specified when the tablespace is created and cannot be converted after the creation. For DMS mode, a tablespace corresponds to one or more containers, and the container specifies the physical storage location of the data. For SMS mode, only one directory can be specified and cannot be added.
The tablespace has the following types:
System Catalog Table Space (SysCatSpace)
System Temporary Table Space (SysTempSpace)
User tablespace (UserSpace)
User Temporary Table Space (UserTempSpace)
There must be two basic system table spaces in a database, namely the system catalog table space and the system temporary table space. Any object created in the database is reflected in the way of adding records to the system catalog table space. For temporary table space, its disk size is dynamically scalable according to usage, that is, disk space is allocated only when needed and recycled after use. In addition, if the user needs to create a table, he needs to create a user tablespace. If he needs to use a temporary table, he needs to create a user temporary tablespace.
There are three types of containers:
Files Files
Devices
Directory Directory
Files and devices, tablespaces used in DMS mode;
Directory is used for SMS table space. This method does not require manual management of data storage files. DB2 can automatically add storage files in the directory according to the situation, as long as disk space allows.
In essence, table space is the logical location definition of data storage, while container is the physical location definition of data storage.
Database performance
The main factors affecting the performance of a database are the following:
Disk
Memory
Processor (CPU)
Network
Among them, disks are the most significant, and 90% of the performance bottlenecks may come from disk IO competition;
The second is memory. On the one hand, it means that the total amount of physical memory must meet the needs, and on the other hand, it means that the configuration parameters related to memory should be configured correctly;
Of course, the performance of the processor is also very important. Multiple CPUs will have significant effects on complex SQL queries that rely on computing power;
The network is not a main factor, but an objective environmental factor, which means that too slow network speed will affect the transmission of data. Here are some effective ways to improve database performance:
For servers running database services, multiple physical disks can be configured as much as possible, and the capacity of each block does not have to be too large, which can effectively share the competition between data storage and disk IO during the read operation. That is, the performance of using multiple small-capacity disks is better than using only one large-capacity disk.
If conditions permit, try to make the data storage service and the operating system run on physically separate disks.
Table space that adopts DMS (Database Management Space) management method.
Create multiple tablespaces on physically different disks. Then the data and indexes can be stored in different table spaces separately, which can significantly improve performance. You can also split a frequently used large table vertically into multiple small tables, store them in different table spaces, and then combine them with a view.
The DB2 server can manage bare devices, that is, except for the system and DB2 service running disks, it prepares a separate disk for DB2 data storage. It can be multiple blocks and does not require formatting after partitioning. After creating the bare device, it is directly handed over to DB2 for management and storage of data.
The system's temporary table space has a great impact on database performance. When the managed physical memory cannot meet the needs of database operations, DB2 will write temporary data to disk, and the system's temporary table space is used, and this situation often occurs.
Try to place data near the innermost track of the disk, because the disk access speed here is faster.
Main performance-related parameters
The parameter configuration of DB2 is divided into two levels, one is the instance level and the other is the database level. Parameters that have a greater impact on data service performance are mainly configured at the database level. The following are three more important memory configuration parameters:
bufferpagelocklistsortheap
bufferpage: Shared by all objects in the same database.
sortheap: The memory swap area used for sorting is not shared and should not be set too large, otherwise it will easily cause memory exhaustion, because each transaction will apply for independent memory for sorting.
This news has 2 pages in total, currently on page 1 1 2