hi all
Дано:
1) инстанс LI-V2.5.3.26737, в режиме SuperServer, GCpolicy = default ("combined").
2) новая база, FW =OFF, page_size=4096
3) скрипт, скармливаемый isql'ю (коннект -
единственный , никаких других к базе нету!):
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.
recreate table t(id int, s01 varchar(36) , s02 varchar(36) , s03 varchar(36) );
commit;
create index t_s01 on t(s01);
create index t_s02 on t(s02);
create index t_s03 on t(s03);
commit;
set term ^;
execute block as
begin
begin
execute statement 'create sequence g';
when any do begin end
end
end^
set term ;^
alter sequence g restart with 0;
commit;
-- 1'000'000 ==> 352 Mb
-- 5'000'000 ==> 1700Mb, ~7 min
set stat on;
set term ^;
execute block as
declare n int = 1000000;
begin
while (n>0) do
insert into t(id, s01, s02, s03)
values( :n +iif( mod(:n,1000)=0, 0*gen_id(g,1000), 0),
uuid_to_char(gen_uuid()),
uuid_to_char(gen_uuid()),
uuid_to_char(gen_uuid())
) returning :n-1 into n;
end^
set term ;^
set echo on;
commit;
select count(*) from t;
delete from t;
commit;
set echo off;
show version;
show database;
exit;
4) Вывод в окне этого скрипта:
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.
Current memory = 10102056
Delta memory = 89584
Max memory = 11080776
Elapsed time= 74.02 sec
Cpu = 0.00 sec
Buffers = 2048
Reads = 2604140
Writes = 2647079
Fetches = 17175784
commit;
Current memory = 10080144
Delta memory = -21912
Max memory = 11080776
Elapsed time= 0.00 sec
Cpu = 0.00 sec
Buffers = 2048
Reads = 1
Writes = 1391
Fetches = 2
select count(*) from t;
COUNT
============
1000000
Current memory = 10091056
Delta memory = 8984
Max memory = 11080776
Elapsed time= 0.54 sec
Cpu = 0.00 sec
Buffers = 2048
Reads = 40080
Writes = 0
Fetches = 2080087
delete from t;
Current memory = 10103072
Delta memory = 12016
Max memory = 11080776
Elapsed time= 1.91 sec
Cpu = 0.00 sec
Buffers = 2048
Reads = 40043
Writes = 38030
Fetches = 5080044
commit;
Current memory = 10095504
Delta memory = -7568
Max memory = 11080776
Elapsed time= 0.01 sec
Cpu = 0.00 sec
Buffers = 2048
Reads = 1
Writes = 1917
Fetches = 2
set echo off;
ISQL Version: LI-V2.5.3.26737 Firebird 2.5
Server version:
Firebird/linux AMD64 (access method), version "LI-V2.5.3.26737 Firebird 2.5"
Firebird/linux AMD64 (remote server), version "LI-V2.5.3.26737 Firebird 2.5/tcp (oel64)/P12"
Firebird/linux AMD64 (remote interface), version "LI-V2.5.3.26737 Firebird 2.5/tcp (oel64)/P12"
on disk structure version 11.2
Database: localhost/3253:/var/db/fb30/gfixtest25.fdb
Owner: SYSDBA
PAGE_SIZE 4096
Number of DB pages allocated = 84480
Sweep interval = 20000
Forced Writes are OFF
Transaction - oldest = 30
Transaction - oldest active = 31
Transaction - oldest snapshot = 31
Transaction - Next = 32
ODS = 11.2
Default Character set: NONE
(т.е. видно, что действительно было добавлено 1 млн записей)
5) Данные 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.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
Database "/var/db/fb30/gfixtest25.fdb"
Database header page information:
Flags 0
Checksum 12345
Generation 36
Page size 4096
ODS version 11.2
Oldest transaction 30
Oldest active 31
Oldest snapshot 31
Next transaction 32
Bumped transaction 1
Sequence number 0
Next attachment ID 3
Implementation ID 24
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Feb 11, 2014 19:37:29
Attributes
Variable header data:
*END*
Database file sequence:
File /var/db/fb30/gfixtest25.fdb is the only file
Analyzing database pages ...
T (129)
Primary pointer page: 171, Index root page: 172
Average record length: 0.00, total records: 999750
Average version length: 122.98, total versions: 999750 , max versions: 1
Data pages: 39990, data page slots: 40000, average fill: 96%
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 39990
Index T_S01 (0)
Depth: 4, leaf buckets: 14513, nodes: 999750
Average data length: 31.75, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 292
20 - 39% = 345
40 - 59% = 4724
60 - 79% = 5625
80 - 99% = 3527
Index T_S02 (1)
Depth: 4, leaf buckets: 14480, nodes: 999750
Average data length: 31.75, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 248
20 - 39% = 369
40 - 59% = 4764
60 - 79% = 5547
80 - 99% = 3552
Index T_S03 (2)
Depth: 4, leaf buckets: 14487, nodes: 999750
Average data length: 31.75, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 252
20 - 39% = 345
40 - 59% = 4805
60 - 79% = 5596
80 - 99% = 3489
6) trace при повторном коннекте и выполнении в нём select count(*) from t:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
Statement 35:
-------------------------------------------------------------------------------
select count(*) from t
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
PLAN (T NATURAL)
1 records fetched
42245 ms, 3136067 read(s), 533875 write(s), 21219284 fetch(es), 5339667 mark(s)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
T 999750
Он удалил 999750 записей. "Кто" тогда удалил оставшиеся 250, фоновый сборщик ? Если да, то почему он прекратил (судя по всему) свою деятельность сразу после завершения единственного коннекта ?
PS. От запуска к запуску число мусорных версий, собранных вторым коннектом, меняется: было и 999600, и 999800.
