Shulou(Shulou.com)06/01 Report--

The concept of tablespace

A database consists of parts called table spaces. Tablespaces are the locations where tables are stored. When you create a table, you can decide to store data for specific objects, such as indexes and large objects, separately from the rest of the table data. Tablespaces can also be distributed over one or more physical storage devices.

Our section focuses on how to create tablespaces (such as what parameters need to be used to create the most appropriate tablespaces)

Tablespace type selection

Table space can be divided into SMS table space and DMS table space

In a nutshell, SMS tablespaces are easy to manage and DMS tablespaces perform better.

SMS tablespaces:

1) according to the need, the system allocates according to the demand

2) since there is no need to predefine containers, creating tablespaces requires less initial work.

3) the space is managed by the operating system, and the data is continuous logically, but not physically.

DMS tablespaces:

1) containers can be added or expanded through ALTER TABLESPACE to increase the tablespace size, and existing data can be rebalanced in the newly added container collection to maintain the best Icano efficiency.

2) data can be split and stored in multiple table spaces to improve performance and space storage capacity.

3) the location of data on the disk can be controlled (with the permission of the operating system)

4) well-designed DMS performs better than SMS tablespaces (for applications involving a large number of DML operations, DMS tablespaces are recommended)

5) the data is continuous on the physical disk.

Note: SMS tablespaces are the easiest to manage for personal databases. For growing large databases, it is recommended to use SMS table spaces as temporary table spaces and system catalog table spaces, while separate DMS table spaces with multiple containers are used for each table. It is also recommended to store long field (LF) data and indexes in their own tablespaces.

Consider tablespace selection in terms of the amount of data in the table

SMS tablespaces:

If you plan to store many small tables in one table space, you should consider using SMS table spaces. (for small bids, DMS is less important in terms of Imax O and spatial management efficiency.)

DMS tablespaces:

If the table is large or requires faster access to data in the table, consider DMS tablespaces with smaller extended block sizes.

Consider tablespaces in terms of table data types

1) some tables contain historical data that are not used frequently, and users may be willing to accept longer response times. Separate tablespaces can be used for history tables and assigned to cheaper physical devices with lower access rates.

2) some tables require quick data response and need to be assigned to the tablespaces of fast physical devices. Solid state drives can be used to store the most frequently accessed tables.

3) using DMS table spaces, you can distribute table data in 3 different table spaces:

One stores index data

A storage of large object (LOB) and long field (LF) data

One stores regular table data.

If you distribute tables across DMS tablespaces, you should consider backing up and restoring those tablespaces together when enabling tablespace level backup recovery. SMS tablespaces do not support the distribution of data across all tablespaces in this manner.

If you need to delete and recreate specific tables frequently, you should create separate DMS tablespaces for such tables, because deleting DMS tablespaces is more efficient than deleting tables.

Select the appropriate data page size

Data pages can choose to use 4KB, 8KB, 16KB, and 32KB page sizes. When selecting a data page size, you need to consider both space requirements and business types (performance requirements) to make a choice.

Each page size in DB2 limits the maximum length of rows that can be stored and the maximum value of table space that can be stored, so you need to consider these when choosing a page size.

4KB-the maximum row length that can be stored is 4005 bytes

Tablespaces specific to page size restrictions

Tablespace type

4KB

8KB

16KB

32KB

SMS tablespace

64GB

128GB

256GB

512GB

DMS tablespaces (general)

64GB

128GB

256GB

512GB

DMS tablespace (large)

8TB

16TB

32TB

64TB

Automatically store tablespaces (general)

64GB

128GB

256GB

512GB

Automatically store tablespaces (large)

8TB

16TB

32TB

64TB

Temporary tablespace

64GB

128GB

256GB

512GB

Improper selection of data page size may result in a waste of space.

It is recommended that when common table spaces, try to create large table spaces, large table space data pages can store more capacity and number of rows.

Select the appropriate page size according to the business type (OLTP, OLAP, batch, report, mixed type)

Characteristics of online transaction processing (OLTP) workloads: transactions require random access to data, involving frequent insert or update activities and queries that return a small group of data. For OLTP applications with high performance requirements, you can consider creating some frequently accessed tables on solid state drives. (DMS tablespaces that use bare device containers perform best in this case)

Characteristics of OLAP query workload: transactions require sequential or partial sequential access to data, and often return large datasets. (DMS tablespaces that use multiple device containers, each on a separate disk, are most likely to provide efficient parallel preaccess.) The value of the PREFETCHSIZE parameter should be set to the product of the value of the EXTENTSIZE parameter multiplied by the number of container devices. And the prefetch size can be set to-1 (AUTOMATIC), which allows the database manager to prefetch from all containers in parallel. If the number of containers changes, or if you need to prefetch more or less, you can use the ALTER TABLESPACE statement to change the PREFETCHSIZE value in response; it is highly recommended to set PREFETCHSIZE to AUTOMATIC or-1.

The goal of mixed workloads: for OLTP workloads, to make a single Imax O request as efficient as possible, and for OLAP query workloads, to maximize the efficiency of parallel Imax O.

Tablespace page size selection Note 1) for OLTP applications that perform random row read and write operations, it is usually best to use smaller page sizes (4KB, 8KB) so that unnecessary rows do not waste buffer pool space

2) for decision support systems (DSS) and OLAP applications that access a large number of consecutive rows at a time, page size points (16KB, 32KB) are better, which reduces the number of Icano requests required to read a specific number of rows. Larger page sizes also allow you to reduce the number of layers in the index because more row pointers can be retained in a page.

3) the larger the page, the longer the rows supported. The appropriate data page should be selected according to the business needs.

4) 4KB-the table has 500 columns; 8KB, 16KB, and 32KB support 1012 columns.

5) the maximum size of the tablespace is proportional to the page size of the tablespace (see Table 3-3)

Considerations when making size selection for extended data blocks

EXTENTSIZE specifies the number of container PAGESIZE pages that can be written before jumping to the next container. The database manager repeatedly balances the use of all containers when storing data. This parameter works only if there are multiple containers in the tablespace. A reasonable EXTENTSIZE can have a significant impact on the performance of tablespaces. This parameter can only be defined when the tablespace is created and cannot be modified later.

The following rules of thumb are based on the average size of each table in the tablespace:

1) if it is less than 50MB, the EXTENTSIZE is 8.

2) if it is between 50MB and 500MB, the EXTENTSIZE is 16

3) if it is between 500MB and 5GB, the EXTENTSIZE is 32

4) if the value is greater than 5GB, the value of EXTENTSIZE is 64.

For OLAP databases and most access to tables, including tables that query or deal with large amounts of data (only queries), or fast-growing tables, prefetching data from tables can significantly improve performance, using a larger extent or, conversely, a smaller extent.

Prefetchsize size selection

In order to improve the buffer pool hit ratio, the database reads the data before querying the data needed by the prefetch operation, because the data is already in memory, and the query does not have to wait for the Ihand O to be executed when using the data. The database manager determines whether the prefetch operation helps improve performance.

The prefetch size can be modified through ALTER TABLEPSPACE. The general optimal settings are as follows:

Prefetch Size = (# Containers of the table space on different physical disks) * Extent Size

If the tablespace resides on a disk array, set it as follows:

Prefetch Size = Extent Size * (# of non-parity disks in array)

Note: after the DB2 V9 release, you can automatically prefetch the size when you create a tablespace.

After adding or removing containers, update the prefetch size of the tablespace in a timely manner, otherwise database performance will be significantly reduced. You can specify Prefetchsize as Automatic when you create the tablespace, which sets the automatic prefetch size. You can check whether automatic prefetching is set in the following ways:

Db2 get snapshot for tablespaces on dbname | more

The size setting of Prefetchsize is related to the setting of Extentsize, so you need to set the Extentsize size reasonably, and then set the Prefetchsize according to the Extentsize size. It is a good suggestion to use automatic storage when creating the database so that the database manager can automatically set the size of Prefetchsize and Extentsize.

File system (CIO/DIO) and bare devices

You can choose to use a file system or a bare device when creating an DMS tablespace container.

To avoid an extra cache by the operating system (the database has already been cached once through buffer pool), a bare device can be used as a storage device for data files. Bare devices, also known as bare partitions (Raw Partition), are disk partitions that are not mount into the operating system's file system and are accessed by character device drivers. The read and write of the bare device is not controlled by the operating system, but directly controlled by the application system (such as database).

Advantages of bare devices:

1. Better performance (masking file system buffers for direct reading and writing). Direct reading and writing to the hard disk means eliminating the need for synchronization between the hard disk and the file system. This is very useful for pure OLTP systems because they are so random in reading and writing that once the data is read and written, it will not be used again for a long time in the future. Improve the performance of decision support system (DSS) applications:

²sorting: there are a lot of sorting requirements in the DSS environment, and the direct read and write capabilities provided by bare devices are useful because they are faster to write to temporary tablespaces.

²Sequential access: bare devices are well suited for sequential I cando O actions. The common order in DSS is Istroke O (full table scan of a table / index)

two。 Read and write directly without going through the operating system-level cache.

3. It avoids the cache pre-reading function of the operating system, and reduces the Imax O.

4. Avoid the overhead of the file system, such as maintaining i-node, free blocks, and so on.

Disadvantages of bare devices:

1. The space size of bare devices is inflexible. The space use of bare equipment needs to be planned in advance, and some of the bare equipment should be retained to cope with emergencies.

two。 Operating system root user intervention is required, because the creation, change of permissions and expansion of bare devices all need to be done by root users, which increases the management cost.

Advantages of the file system:

Easy to manage and maintain, such as basic file management, security and backup, etc.

Disadvantages of the file system:

The performance is not as good as that of bare equipment.

When selecting a tablespace container, use bare devices as far as possible in terms of performance; if you use automatic storage to create databases and tablespaces, bare devices are not supported. Or to use the file system approach for ease of management, you need to set file system-related options and tablespace-related options reasonably.

Direct DIO can improve memory performance by bypassing caching at the file system level. This process reduces CPU overhead and makes more memory available for database instances.

Concurrent I _ CIO has the advantages of DIO and eliminates serialized write access. The CIO/DIO mechanism increases throughput when there are a large number of transactional workloads and rollbacks compared to using file system buffering Istroke O.

Note: see the latest DB2 information center for specific support lists for DIO and CIO.

The keywords NO FILE SYSTEM CACHING and FILE SYSTEM CACHING are part of the CREATE and ALTER TABLEPACE statements.

When NO FILE SYSTEM CACHING is valid, the database manager tries to use "concurrent I CIO O (CIO)" whenever possible. When CIO is not supported (for example, using JFS), DIO will be used instead.

It is recommended that you enable or disable unbuffered I _ swap O in the operating system at the tablespace level. This will allow you to enable or disable unbuffered Iswap O on a specific tablespace while avoiding any dependencies in the physical layout of the database. In addition, you can allow the database manager to determine which Iripple O is most appropriate for each file, buffered or unbuffered.

The NO FILE SYSTEM CACHING clause is used to enable unbuffered iCandle O, thereby disabling file caching for a particular tablespace. Once the unbuffered Istroke O is enabled, the database manager automatically determines whether to use direct or concurrent Imax O based on the platform. Using CIO improves performance, and the database manager is enabled as long as CIO is supported.

FILE SYSTEM CACHING is commonly used by applications to retrieve LOB and LONG data, which cannot pass through the database buffer pool.

To see if the FILE SYSTEM CACHING property is enabled, you can use:

Db2 get snapshot for tablespaces on | more

Db2pd-d-tablespaces

Db2look-dl

It is recommended that when creating a tablespace, the container in the tablespace should be a bare device or a file system that supports concurrent Imax O or direct Icano.

Set up OVERHEAD and TRANSFERRATE

These two parameters are used to determine the Imax O cost during query optimization. Both values are measured in milliseconds, and they are the average of all container overhead and transfer rates. The cost is the time associated with the Imax O controller activity, disk seek time, and rotation delay time. The transfer rate is the amount of time necessary to read a page into memory. Their default values are 24.1 and 0.9, respectively. These values can be calculated based on hardware specifications:

Transrate = (1 / transfer rate) * 1000bot 1024000mm 4096 (assuming 4KB page size)

Overhead = average seek time + ((1 / disk speed) * 60mm 1000) / 2)

The average seek time, disk rotation speed and transfer rate are determined by the hard disk itself (the physical characteristics of the underlying hard disk can be obtained through operating system commands or from the hard disk manufacturer)

Therefore, setting these two values reasonably can make the optimizer understand the physical characteristics of the underlying storage and make an optimal execution plan.

Optimize the performance of tablespaces on RAID devices

Nowadays, many application systems store databases on "redundant array of independent disks" (RAID) devices. To optimize the performance of tablespaces stored on RAID devices, you can follow the following guidelines:

1. When creating tablespaces on a set of RAID devices, tablespace containers should be created on multiple RAID GROUP.

two。 Select the appropriate extended data block (extent) size for the tablespace. Ideally, the extended block size should be equal to the underlying disk strip size or its multiples, where the strip size is equal to the strip size times the number of active disks. The Strip size indicates how much data the disk controller writes to one physical disk before moving to the next.

If the strip size is 64KB and the page size is 16KB, the appropriate extended block size may be 256KB (64KB*4)

3. Use the DB2_PARALLEL_IO registration variable to enable parallel Imax O for all tablespaces and specify the number of physical disks for each container.

The DB2_PARALLEL_IO registration variable is used to determine the number of underlying physical hard disks for each container and its impact on the parallel Imax O on the tablespace. Parallel prefetching is started for tablespaces when multiple containers are set for tablespaces or when DB2_PARALLEL_IO registration variables are set. If DB2_PARALLEL_IO is not set, the parallelism of the tablespace is equal to the number of containers. Otherwise, the parallelism of the table space is determined by the prefetch size of the tablespace and the EXTENT size. It is recommended that the prefetch size be set to AUTOMATIC, and the prefetch size is automatically calculated by DB2, so that DB2 can choose the most appropriate parallelism.

DB2_PARALLEL_IO = TablespaceID: [n], if n is not specified, the default value of 6 is used (that is, assuming the container spans six RAID underlying physical disks).

DB2_PARALLEL_IO = * (* means that all tablespaces have parallel Imax O enabled, and no n is specified, so all tablespaces use the default value that the number of container disks equals 6. The prefetch request is divided into "6x containers" parallel requests, and the read size of each request is the extent size.

DB2_PARALLEL_IO = *: 3 (* means that all tablespaces are enabled with parallel I _ hand OMagol 3 means that the number of container disks used by all tablespaces is equal to 3. The prefetch request is decomposed into "3x containers" of parallel requests, each with a read size of extent size.)

DB2_PARALLEL_IO = *: 3 1:1 (* means that all tablespaces are enabled with parallel I _ hand OMagol 3 means that the number of container disks used by all tablespaces is equal to 3. The prefetch request is divided into "3x containers" parallel requests, and the read size of each request is the extent size. For a tablespace with an ID of 1, the prefetch request is decomposed into "1x containers" parallel requests, and the read size of each request is the extent size.)

If the prefetch size of the tablespace is set to AUTOMATIC, the database manager uses the physical disk value specified for DB2_PARALLEL_IO to determine the prefetch size. If the prefetch size is not set to AUTOMATIC, you can set it manually, considering the RAID stripe size, which is the value produced by multiplying the strip size by the number of active disks:

L equals the RAID stripe size times the number of RAID parallel devices (or an integer representation of this product)

L is an integer representation of the size of the extended data block.

Do not set the DB2_USE_PAGE_CONTAINER_TAG registration variable, if set to ON, a single-page container tag will be used and the extended data block will not align with the RAID stripe. The tablespace should be created with an extended block size equal to the RAID stripe size or its multiples.

Give an example

Having said so much about the parameter selection for creating a tablespace, let's give an example for practical operation. Here is the complete statement for creating a tablespace:

Create a single-extent tablespace

CREATE REGULAR TABLESPACE "TBS_BASS_WEB" IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 32768 MANAGED BY DATABASE

USING (FILE'/ db2home/db2inst1/db2inst1/NODE0000/tbs_bass_web'655360

FILE'/ db2home/db2inst1/db2inst1/NODE0000/tbs_bass_web1'655360

FILE'/ db2data/tbs/tbs_bass_web7'655360

FILE'/ db2data/tbs/tbs_bass_web8'655360)

EXTENTSIZE 32

PREFETCHSIZE AUTOMATIC

BUFFERPOOL BASS_WEB

OVERHEAD 24.100000

TRANSFERRATE 0.900000

FILE SYSTEM CACHING

DROPPED TABLE RECOVERY ON

Create a multi-partitioned tablespace (multiple partitions "DBPARTITIONNUMS" are specified)

CREATE LARGE TABLESPACE "TBS_DWD" IN DATABASE PARTITION GROUP NDGRP11 PAGESIZE 32768 MANAGED BY DATABASE

USING (DEVICE'/ dev/md/vgmt01/rdsk/d503' 655360)

DEVICE'/ dev/md/vgmt01/rdsk/d509'655360

DEVICE'/ dev/md/vgmt01/rdsk/d510'6553600) ON DBPARTITIONNUMS (1)

USING (DEVICE'/ dev/md/vgmt02/rdsk/d503' 655360)

DEVICE'/ dev/md/vgmt02/rdsk/d509'655360

DEVICE'/ dev/md/vgmt02/rdsk/d510'6553600) ON DBPARTITIONNUMS (2)

USING (DEVICE'/ dev/md/vgmt03/rdsk/d503' 655360)

DEVICE'/ dev/md/vgmt03/rdsk/d509'655360

DEVICE'/ dev/md/vgmt03/rdsk/d510'6553600) ON DBPARTITIONNUMS (3)

USING (DEVICE'/ dev/md/vgmt04/rdsk/d503' 655360)

DEVICE'/ dev/md/vgmt04/rdsk/d509'655360

DEVICE'/ dev/md/vgmt04/rdsk/d510'6553600) ON DBPARTITIONNUMS (4)

EXTENTSIZE 16

PREFETCHSIZE 32

BUFFERPOOL POOL_32K

OVERHEAD 7.500000

TRANSFERRATE 0.060000

FILE SYSTEM CACHING

DROPPED TABLE RECOVERY ON

If you do not go through detailed parameter settings, you can also use the following simple ways, and other parameters will be set by default

Db2 create tablespace test managed by DATABASE

Db2 create tablespace test managed by SYSTEM

Db2 create tablespace test managed by AUTOMATIC STORAGE; (automatic storage, automatic allocation of containers)

Note: when creating DMS tablespaces, the tablespace file container is created automatically by DB2, but the bare device container cannot be created automatically and requires the participation of root users.

Summary

Compared with DMS tablespaces, SMS tablespaces are particularly suitable for general use.

SMS tablespaces can provide some performance with low administrative costs.

If you need to achieve the best performance, you should choose the DMS tablespace.

Because double buffering occurs when moving data using a file container or SMS tablespace (buffering the data first at the database manager level, and then buffering the data again on the file system, which is double buffering, using a device container may provide better performance.

As for the choice, we still have to seek truth from facts!

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.