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.
Top comments (0)