hi all.
Растолкуйте, плз, тему про "пол-ведра компрессии", а именно - про утрамбовку ключей в индексах.
Есть таблица, в ней два поля: e32 int и e64 big int.
Для каждой строки в эти поля пишется одно и то же значение: {1, 1}, {2, 2} etc.
Сначала заливаем строки со значениями от 1 до 16387064.
Далее создаем вторую таблицу, и передаём в неё данные из первой, увеличенные в каждой строке на 2 млрд.
Затем создаём в каждой таблице по два индекса (НЕ составных) - первый по e32, второй по e64.
DDL:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
[root@oel64 16:23:03 ~]$ /opt/fb30ss/bin/isql -q
SQL> create database '/3333:/var/db/fb30/tx005.fdb';
SQL> exit;
[root@oel64 16:50:28 ~]$ /opt/fb30ss/bin/gfix -w async /var/db/fb30/tx005.fdb
[root@oel64 16:50:40 ~]$ /opt/fb30ss/bin/isql -q /3333:/var/db/fb30/tx005.fdb
Database: /3333:/var/db/fb30/tx005.fdb, User: SYSDBA
SQL> recreate table test1(e32 int, e64 big int); commit;
SQL> recreate table test2(e32 int, e64 big int); commit;
SQL> create sequence g; commit;
SQL> insert into test1(e32, e64) select gen_id(g,0), gen_id(g,1) from rdb$types,rdb$types,rdb$types; commit;
SQL> insert into test2(e32, e64) select 2000000000+e32, 2000000000+e64 from test1; commit;
SQL> set autoddl off;
SQL> create index test1_e32 on test1(e32);
SQL> create index test1_e64 on test1(e64);
SQL> create index test2_e32 on test2(e32);
SQL> create index test2_e64 on test2(e64);
SQL> commit;
А затем вызываем gstat -r и смотрим данные.
gstat -r: 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
. . .
Index TEST1_E32 (0)
Root page: 218222, depth: 3, leaf buckets: 12175, nodes: 16387064
Average node length: 5.99, total dup: 0, max dup: 0
Average key length: 3.00 , compression ratio: 1.56
Average prefix length: 3.68, average data length: 1.00
Clustering factor: 108524, ratio: 0.01
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 12175
Index TEST1_E64 (1)
Root page: 230200, depth: 3, leaf buckets: 22983, nodes: 16387064
Average node length: 11.31, total dup: 0, max dup: 0
Average key length: 8.32 , compression ratio: 1.08
Average prefix length: 3.68, average data length: 5.32
Clustering factor: 108524, ratio: 0.01
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 1
80 - 99% = 22982
. . .
. . .
Index TEST2_E32 (0)
Root page: 253412, depth: 3, leaf buckets: 12176, nodes: 16387064
Average node length: 6.00, total dup: 0, max dup: 0
Average key length: 3.01 , compression ratio: 1.91
Average prefix length: 4.74, average data length: 1.00
Clustering factor: 108524, ratio: 0.01
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 0
80 - 99% = 12175
Index TEST2_E64 (1)
Root page: 265494, depth: 3, leaf buckets: 20744, nodes: 16387064
Average node length: 10.20, total dup: 0, max dup: 0
Average key length: 7.21 , compression ratio: 1.25
Average prefix length: 4.79, average data length: 4.21
Clustering factor: 108524, ratio: 0.01
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 20743
. . .
полный вывод gstat'a 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
Database "/var/db/fb30/tx005.fdb"
Database header page information:
Flags 0
Generation 28
System Change Number 0
Page size 8192
ODS version 12.0
Oldest transaction 20
Oldest active 21
Oldest snapshot 21
Next transaction 23
Sequence number 0
Next attachment ID 11
Implementation HW=AMD/Intel/x64 little-endian OS=Linux CC=gcc
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Sep 20, 2015 16:50:23
Attributes
Variable header data:
*END*
Database file sequence:
File /var/db/fb30/tx005.fdb is the only file
Analyzing database pages ...
TEST1 (129)
Primary pointer page: 181, Index root page: 182
Total formats: 1, used formats: 1
Average record length: 13.99, total records: 16387064
Average version length: 0.00, total versions: 0, max versions: 0
Average fragment length: 0.00, total fragments: 0, max fragments: 0
Average unpacked length: 16.00, compression ratio: 1.14
Pointer pages: 67, data page slots: 108528
Data pages: 108528, average fill: 57%
Primary pages: 108528, secondary pages: 0, swept pages: 0
Empty pages: 4, full pages: 108523
Fill distribution:
0 - 19% = 4
20 - 39% = 1
40 - 59% = 108523
60 - 79% = 0
80 - 99% = 0
Index TEST1_E32 (0)
Root page: 218222, depth: 3, leaf buckets: 12175, nodes: 16387064
Average node length: 5.99, total dup: 0, max dup: 0
Average key length: 3.00 , compression ratio: 1.56
Average prefix length: 3.68, average data length : 1.00
Clustering factor: 108524, ratio: 0.01
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 12175
Index TEST1_E64 (1)
Root page: 230200, depth: 3, leaf buckets: 22983, nodes: 16387064
Average node length: 11.31, total dup: 0, max dup: 0
Average key length: 8.32 , compression ratio: 1.08
Average prefix length: 3.68, average data length: 5.32
Clustering factor: 108524, ratio: 0.01
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 1
80 - 99% = 22982
TEST2 (130)
Primary pointer page: 183, Index root page: 184
Total formats: 1, used formats: 1
Average record length: 15.00, total records: 16387064
Average version length: 0.00, total versions: 0, max versions: 0
Average fragment length: 0.00, total fragments: 0, max fragments: 0
Average unpacked length: 16.00, compression ratio: 1.07
Pointer pages: 67, data page slots: 108528
Data pages: 108528, average fill: 59%
Primary pages: 108528, secondary pages: 0, swept pages: 0
Empty pages: 4, full pages: 108523
Fill distribution:
0 - 19% = 4
20 - 39% = 1
40 - 59% = 108523
60 - 79% = 0
80 - 99% = 0
Index TEST2_E32 (0)
Root page: 253412, depth: 3, leaf buckets: 12176, nodes: 16387064
Average node length: 6.00, total dup: 0, max dup: 0
Average key length: 3.01, compression ratio: 1.91
Average prefix length: 4.74, average data length: 1.00
Clustering factor: 108524, ratio: 0.01
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 0
80 - 99% = 12175
Index TEST2_E64 (1)
Root page: 265494, depth: 3, leaf buckets: 20744, nodes: 16387064
Average node length: 10.20, total dup: 0, max dup: 0
Average key length: 7.21, compression ratio: 1.25
Average prefix length: 4.79, average data length: 4.21
Clustering factor: 108524, ratio: 0.01
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 20743
Q-1. Почему для int'a показатель компрессии всегда лучше, да и листовых страниц в 1.6 раза меньше, чем для bigint'a, если все значения в e64 всё равно меньше 2^31 и, след-но, при сохранении от них должны отбрасываться незначащие биты ?
Q-2. В таблице test2 сидят значения, превышающие 2 млрд. След-но, там явно побольше значащих битиков, чем в test1.e64 :-)
Но средняя длина ключа в ней меньше, чем для test1. И листовых страниц - тоже меньше, на 2 тыс. Отчего это ? 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
Index TEST1_E64 (1)
Root page: 230200, depth: 3, leaf buckets: 22983, nodes: 16387064
Average node length: 11.31, total dup: 0, max dup: 0
Average key length: 8.32 , compression ratio: 1.08
Average prefix length: 3.68, average data length: 5.32
Clustering factor: 108524, ratio: 0.01
== vs ==
Index TEST2_E64 (1)
Root page: 265494, depth: 3, leaf buckets: 20744, nodes: 16387064
Average node length: 10.20, total dup: 0, max dup: 0
Average key length: 7.21 , compression ratio: 1.25
Average prefix length: 4.79, average data length: 4.21
Clustering factor: 108524, ratio: 0.01
|
