Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

How to collect garbage in TEMPORARY table? [CORE5552] #5819

Open
firebird-automations opened this issue May 26, 2017 · 2 comments
Open

How to collect garbage in TEMPORARY table? [CORE5552] #5819

firebird-automations opened this issue May 26, 2017 · 2 comments
Labels
affect-version: 2.5.6 component: engine priority: major type: bug

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Oleg Matveyev (o_matveev)

Attachments:
TEMP_GARBAGE.sql
TEMP_GARBAGE.log

On every step loop in top level procedure P0 temporary table is cleared by delete operator, and just in case is execute "select count(*) from all records".
And then is runing calculation procedure P1 manipulating with data in temporary table.
But every new executiing of the procedure P1 was slowdowned.

The initial speed was restored only after COMMIT.
This is obvious, because temporary table created with option ON COMMIT DELETE ROWS.

      I1         DB\_EXECUTE\_TIME

============ =======================
0 111.9744000000000
1 104.9760000000000
2 114.9984000000000
3 118.9728000000000
4 125.9712000000000
5 137.0304000000000
6 153.0144000000000
7 149.9904000000000
8 158.0256000000000
9 162.0000000000000
10 182.9952000000000
11 181.0080000000000
12 189.9936000000000
13 214.9632000000000
14 210.9888000000000
15 221.0112000000000
16 221.0112000000000
17 221.9616000000000
18 233.9712000000000
19 233.9712000000000

      I1         DB\_EXECUTE\_TIME

============ =======================
20 238.0320000000000
21 259.0272000000000
22 263.0016000000000
23 266.9760000000001
24 271.9872000000000

      I1         DB\_EXECUTE\_TIME

============ =======================
0 290.9952000000000
1 290.9952000000000
2 289.9584000000000
3 299.0304000000000
4 313.0272000000000
5 315.0144000000000
6 324.0000000000000
7 325.9872000000000
8 360.9791999999999
9 351.9936000000000
10 374.0256000000000
11 400.0320000000000
12 372.9888000000001
13 386.9856000000000
14 379.9872000000000
15 390.0096000000000
16 409.0176000000000
17 414.0288000000000
18 421.9776000000000
19 442.0224000000000

      I1         DB\_EXECUTE\_TIME

============ =======================
20 421.0272000000000
21 435.9744000000000
22 445.9968000000000
23 447.9840000000001
24 459.9936000000001
@firebird-automations
Copy link
Collaborator Author

Modified by: Oleg Matveyev (o_matveev)

Attachment: TEMP_GARBAGE.sql [ 13120 ]

Attachment: TEMP_GARBAGE.log [ 13121 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

In general, record version created by some transaction, could be considered as garbage and cleaned up when:
- transaction rolled back, or
- transaction is committed, record is updated by another tran, another tran is committed and OST advances over first tran (i.e. 1st tran < OST)

Of course, for GTT ON COMMIT DELETE is kind of special case as all data created by transaction automatically
cleaned up when transaction finished (despite of its final state).

So, without some special handling for GTT ON COMMIT DELETE it is impossible to clean up deleted records before
the transaction end.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
affect-version: 2.5.6 component: engine priority: major type: bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@firebird-automations