Displaying Pages - Sharded Tables and Indexes

In the microscopic world of databases, page structure is the cornerstone supporting the vast data empire. The optimization of the page structure in the GBase 8s database system directly affects data storage efficiency and query performance. This article will delve into the mysteries of the GBase 8s page structure, exploring from physical addresses to logical addresses, from data pages to index pages, and uncovering the internal world of sharded tables and indexes.

1. Preparing Metadata

drop database if exists test2;
create database test2 with log;
create table t1(c1 int, c2 char(100), c3 varchar(100)) fragment by round robin partition p1 in datadbs1, partition p2 in datadbs2;

-- This index is a partition index
create index i1 on t1(c1) fragment by expression partition ip1 mod(c1, 2) = 0 in datadbs1, partition ip2 mod(c1, 2) = 1 in datadbs2;

2. Preparing Data

Script to Generate Data

[root@centos7_2 ix9111]# cat generate_data.py
#!/usr/bin/python

def main():
    with open("data.unl", "w") as fp:
        for i in range(0, 1000):
            fp.write("%d|%08dxxxxxxxxxxxxxxxxxxx|%08dxxxxxxxxxxxxxxxxxxxxxxxxxx\n" % (i, i, i))

if __name__ == '__main__':
    main()

Generate and Load Data

./generate_data.py
dbaccess test2 -
load from 'data.unl' insert into t1;

3. Finding Pages Based on Physical Address

To obtain the physical address, the yellow-highlighted parts in the following steps indicate the storage locations of data and indexes:

3.1 Displaying Data Pages

oncheck -pP 4 202  # (201 is a bitmap page and should be skipped)

[root@gbasehdr202 ids]# oncheck -pP 4  202
addr             stamp    chksum nslots flag type         frptr frcnt next     prev
4:202            379016   c843    13     1    DATA         1844   148   0        0
        slot ptr   len    flag
        1     24    140   0
        2     164   140   0
        3     304   140   0
        4     444   140   0
        5     584   140   0
        6     724   140   0
        7     864   140   0
        8     1004  140   0
        9     1144  140   0
        10    1284  140   0
        11    1424  140   0
        12    1564  140   0
        13    1704  140   0
slot   1:
    0:   0  0  0  0 30 30 30 30 30 30 30 30 78 78 78 78    ....00000000xxxx
   16: 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 20   xxxxxxxxxxxxxxx
   32: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
   48: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
   64: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
   80: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
   96: 20 20 20 20 20 20 20 20  0 22 30 30 30 30 30 30           ."000000
  112: 30 30 78 78 78 78 78 78 78 78 78 78 78 78 78 78   00xxxxxxxxxxxxxx
  128: 78 78 78 78 78 78 78 78 78 78 78 78               xxxxxxxxxxxx....

Displaying data from another shard:

oncheck -pP 5 58  # (57 is a bitmap page and should be skipped)

[root@gbasehdr202 ids]# oncheck -pP 5  58
addr             stamp    chksum nslots flag type         frptr frcnt next     prev
5:58             379019   c8b1    13     1    DATA         1844   148   0        0
         slot ptr   len   flag
         1    24    140    0
         2    164   140    0
         3    304   140    0
         4    444   140    0
         5    584   140    0
         6    724   140    0
         7    864   140    0
         8    1004  140    0
         9    1144   140   0
         10   1284  140    0
         11   1424  140    0
         12   1564  140    0
         13   1704  140    0
slot    1:
     0:  0  0   0  1 30 30 30 30 30 30 30 31 78 78 78 78   ....00000001xxxx
    16: 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 20   xxxxxxxxxxxxxxx
    32: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
    48: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
    64: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
    80: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
    96: 20 20 20 20 20 20 20 20  0 22 30 30 30 30 30 30            ."000000
   112: 30 31 78 78 78 78 78 78 78 78 78 78 78 78 78 78   01xxxxxxxxxxxxxx
   128: 78 78 78 78 78 78 78 78 78 78 78 78               xxxxxxxxxxxx....

3.2 Displaying Index Pages

oncheck -pP 4 198  # (197 should be skipped)

[root@gbasehdr202 ids]# oncheck -pP 4 198
addr             stamp    chksum nslots flag type         frptr frcnt next     prev
4:198            381318   d141    5      70   BTREE        60     1964  0        0
        slot ptr    len   flag
        1     24    8     0
        2     36    8     0
        3     44    8     0
        4     52    8     0
        5     32    4     0
slot   1:
    0: 80   0  0 e8  0   0  0  3                           ...h............
slot   2:
    0: 80   0  1 d2  0   0  0  2                           ...R............
slot   3:
    0: 80   0  2 bc  0   0  0  4                            ...<............
slot   4:
    0: 80   0  3 a6  0   0  0  5                            ...&............
slot   5:
    0:   0  0  0   6                                        ................

Displaying index from another shard:

oncheck -pP 5 54  # (53 should be skipped)

[root@gbasehdr202 ids]# oncheck -pP 5  54
addr             stamp    chksum nslots flag type         frptr frcnt next     prev
5:54             381326   d1b8    5      70   BTREE        60     1964  0        0
         slot ptr   len   flag
         1    24    8      0
         2    36    8      0
         3    44    8      0
         4    52    8      0
         5    32    4      0
slot    1:
     0: 80  0  0 e9   0  0  0   3                            ...i............
slot    2:
     0: 80  0  1 d3   0  0  0   2                            ...S............
slot    3:
     0: 80  0  2 bd   0  0  0   4                            ...=............
slot    4:
     0: 80  0  3 a7   0  0  0  5                           ...'............
slot    5:
     0:  0  0   0  6                                        ................

4. Finding Pages Based on Logical Address (Part 1)

Retrieve partnum, with the yellow-highlighted parts indicating each shard's partnum.

4.1 Displaying Data Pages

[root@gbasehdr202 ids]# oncheck -pp 4194308 1
addr             stamp    chksum nslots flag type         frptr frcnt next     prev
4:202            379016   c843    13     1    DATA         1844   148   0        0
         slot ptr   len   flag
         1    24    140    0
         2    164   140    0
         3    304   140    0
         4    444   140    0
         5    584   140    0
        6     724   140   0
         7    864   140    0
         8    1004  140    0
         9    1144  140    0
         10   1284  140    0
         11   1424  140    0
         12   1564  140    0
         13   1704  140    0
slot    1:
     0:  0  0   0  0 30 30 30 30 30 30 30 30 78 78 78 78   ....00000000xxxx
    16: 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 20   xxxxxxxxxxxxxxx
    32: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
    48: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
    64: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
    80: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
    96: 20 20 20 20 20 20 20 20  0 22 30 30 30 30 30 30            ."000000
   112: 30 30 78 78 78 78 78 78 78 78 78 78 78 78 78 78   00xxxxxxxxxxxxxx
   128: 78 78 78 78 78 78 78 78 78 78 78 78               xxxxxxxxxxxx....

Displaying the second shard:

[root@gbasehdr202 ids]# oncheck -pp  5242882 1
addr             stamp    chksum nslots flag type         frptr frcnt next     prev
5:58             379019   c8b1    13     1    DATA         1844   148   0        0
         slot ptr   len   flag
         1    24    140    0
         2    164   140    0
         3    304   140    0
         4    444   140    0
         5    584   140    0
         6    724   140    0
         7    864   140    0
         8    1004  140    0
         9    1144  140    0
         10   1284  140    0
         11   1424  140    0
         12   1564  140    0
         13   1704  140    0
slot    1:
     0:  0  0   0  1 30 30 30 30 30 30 30 31 78 78 78 78   ....00000001xxxx
    16: 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 20   xxxxxxxxxxxxxxx
    32: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
    48: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
    64: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
    80: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
    96: 20 20 20 20 20 20 20 20  0 22 30 30 30 30 30 30            ."000000
   112: 30 31 78 78 78 78 78 78 78 78 78 78 78 78 78 78   01xxxxxxxxxxxxxx
   128: 78 78 78 78 78 78 78 78 78 78 78 78               xxxxxxxxxxxx....

4.2 Displaying Index Pages

[root@gbasehdr202 ids]# oncheck -pp   5242883 1
addr             stamp    chksum nslots flag type         frptr frcnt next     prev
5:54             381326   d1b8    5      70   BTREE        60     1964  0        0
         slot ptr   len   flag
         1    24    8      0
         2    36    8      0
         3    44    8      0
         4    52    8      0
         5    32    4      0
slot    1:
     0: 80  0  0 e9   0  0  0   3                            ...i............
slot    2:
     0: 80  0  1 d3   0  0  0   2                            ...S............
slot    3:
     0: 80  0  2 bd   0  0  0   4                            ...=............
slot    4:
     0: 80  0  3 a7   0  0  0   5                            ...'............
slot    5:
     0:  0  0   0  6                                        ................

Displaying index from another shard:

[root@gbasehdr202 ids]# oncheck -pp   5242883 1
addr             stamp    chksum nslots flag type         frptr frcnt next     prev
5:54             381326   d1b8    5      70   BTREE        60     1964  0        0
         slot ptr   len   flag
         1    24    8      0
         2    36    8      0
         3    44    8      0
         4    52    8      0
         5    32    4      0
slot    1:
     0: 80  0  0 e9   0  0  0   3                            ...i............
slot    2:
     0: 80  0  1 d3   0  0  0   2                            ...S............
slot    3:
     0: 80  0  2 bd   0  0  0   4                            ...=............
slot    4:
     0: 80  0  3 a7   0  0  0   5                            ...'............
slot    5:
     0:  0  0   0  6                                        ................

5. Finding Pages Based on Logical Address 2

Retrieve partnum of the data pages.

[root@centos7_2 test]# dbaccess test2 -
Database selected.

> select f.fragtype,f.partn from sysfragments f, systables t where f.tabid=t.tabid and t.tabname='t1';
fragtype       partn 
T            4194391
T            5242884
I            4194392
I            5242885
4 row(s) retrieved.

fragtype is T for data pages and I for index pages. Once partnum is obtained, follow the same method as the previous section to display the pages.

6. Large Pages

6.1 Introduction to Large Pages

The default page size is 4K for AIX and Windows, and 2K for others.
Root dbspace always uses the default page size.
When creating pages, the maximum size is 16K, and it must be a multiple of the default page size.

For a new page size, configure a BUFFERPOOL in onconfig for that page size.

6.2 Displaying Large Pages

The offset of large pages can be confusing. It refers to the offset of the default pages, not the large pages.

The formula is:

pg_offset = (chunk_pgsize / system_pgsize) * page_num

For example, in a Linux environment with an 8K page size, the offset of the 3rd page is:

pg_offset = (8K / 2K) * 3 = 12

To display this large page, use the command:

oncheck -pP chunk_number 12

Through this in-depth analysis, we have unveiled the page structure of GBase 8s, providing practical guidelines for database administrators and developers. Understanding and mastering the internal structure of pages will help you advance further on the path of database performance optimization.

Detailed Analysis of Page Structure in GBase 8s (3)