Database partitions
A database partition is a part of a database that has its own data, indexes, configuration files, and transaction logs. It is also sometimes called a node or a database node. A partitioned database environment is a database installation that supports the distribution of data to different database partitions.
Partitioned database environments
A partitioned database environment is a database installation that supports the distribution of data across database partitions.
- A database partition is a part of a database that has its own data, indexes, configuration files, and transaction logs. It is also sometimes called a node or a database node. A partitioned database environment is a database installation that supports the distribution of data to different database partitions.
- A single-partition database is a database which has only one database partition and all data in that database is stored in that single database partition. In this case, database partition groups, though present, provide no additional capability.
- A multi-partition database is a database with two or more database partitions. In this the Tables can be located in one or more database partitions. When a table is in a database partition group consisting of multiple database partitions, some of its rows are stored in one database partition, and other rows are stored in other database partitions.
Database partition and processor environments
This provides an overview of single database partition and multiple database partition configurations. The single database partition includes single processor (uniprocessor) and multiple processor (SMP) configurations, and the multiple database partition include database partitions with one processor (MPP) or multiple processors (cluster of SMPs), and logical database partitions.
Capacity means, the number of users and applications able to access the database. This is mostly determined by memory, agents, locks, I/O, and storage management. Scalability means, the ability of a database to grow and continue to exhibit the same operating characteristics and response times. Capacity and scalability are discussed for each environment which is different.
This environment is made up of memory and disk, but contains only a single CPU (see Figure 1). This database environment best suits the needs of a department or small office, where the data and system resources (including a single processor or CPU) are managed by a single database manager.
This environment enables to add more disks. Having one or more I/O servers for each disk allows for more than one I/O operation to take place at the same time.
The amount of disk space the processor can handle is restricted in a single-processor system. A single CPU might not be able to process user requests faster if the workload increases, regardless of other components, such as memory or disk, that you might add. You can consider moving to a single database partition system with multiple processors if you have reached maximum capacity or scalability.
In this this environment Several equally powerful processors within the same machine (see Figure 2), is typically made up which is called a symmetric multiprocessor (SMP) system. some of the resources which are shared are Disk space and memory.
With multiple processors which are available different database operations can be completed more quickly.
To improve processing speed DB2 database systems can also divide the work of a single query among available processors. Different database operations like loading data, backing up and restoring table spaces, and creating indexes on existing data, can take advantage of multiple processors.
By increasing the number of disks You can increase the I/O capacity of the database partition associated with your processor. You can establish I/O servers to specifically deal with I/O requests. You can increase the I/O capacity of the database partition associated with your processor by increasing the number of disks. You can establish I/O servers to specifically deal with I/O.
More than one I/O operation can take place at the same time by Having one or more I/O servers for each disk. You can move to a system with multiple database partitions If you have reached maximum capacity or scalability.
Multiple database partition configurations
We can divide a database into multiple database partitions, each on its own machine. According to Multiple machines with multiple database partitions we can group them together. The section describes the following database partition configurations:
- Database partitions on systems with one processor
- Database partitions on systems with multiple processors
- Logical database partitions
Database partitions with one processor
There are many database partitions in this environment. Each database partition resides on its own machine, and has its own processor, memory, and disks (Figure 3). The communications facility connects all the machines. This environment is referred by many names like, cluster, cluster of uniprocessors, massively parallel processing (MPP) environment, and shared-nothing configuration. The latter name accurately reflects the arrangement of resources in this environment. An MPP environment has no shared memory or disks like the SMP environment. The limitations introduced through the sharing of memory and disks are removed by the MPP environment.
The data which is distributed remains transparent to most users. A partitioned database environment allows a database to remain a logical whole, despite being physically divided across more than one database partition. Each database manager in each database partition works against its own part of the database, in this way work can be divided.
If BLU is provided by the hardware then it takes advantage of single instruction multiple data (SIMD) processing
SIMD uses the same instruction to be applied to multiple pieces of data in parallel at the hardware level.
BLU algorithms are designed to scale with the number of cores available in the system.
The alternative to a configuration in which each database partition has a single processor, is a configuration in which each database partition has multiple processors. This is called as an SMP cluster (Figure 4).
This configuration combines both SMP and MPP parallelism advantages. This enables a query to be performed in a single database partition across multiple processors. It also enables a query to be performed in parallel across multiple database partitions.
We can add more database partitions, and you can add more processors to existing database partitions in this environment.
A logical database partition differs from a physical partition, it is not given control of an entire machine. The database partitions do not share the resources even though the machine has shared resources. Disks and memory are not shared but Processors are shared.
Scalability is provided by Logical database partitions. Than a single database manager, Multiple database managers running on multiple logical partitions can make fuller use of available resources. Figure 5 illustrates the fact that you can gain more scalability on an SMP machine by adding more database partitions; this is particularly true for machines with many processors. You can administer and recover each database partition separately by distributing the database.
Figure 6 illustrates that you can multiply the configuration shown in Figure 5 to increase processing power.
Figure 6. Partitioned database with symmetric multiprocessor environments clustered together
To dramatically enhance performance Components of a task, such as a database query, can be run in parallel. The nature of the task, the database configuration, and the hardware environment, all determine how the DB2 database product will perform a task in parallel.
These factors are interrelated. We consider them when we work on the physical and logical design of a database. The following types of parallelism are supported by the DB2 database system:
- I/O
- Query
- Utility