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
Garbage in the compound index after several updates [CORE5877] #6136
Comments
Modified by: @hvladassignee: Vlad Khorsun [ hvlad ] |
Commented by: @hvlad Is it really necessary to create descending index to reproduce the issue ? |
Commented by: Jesus Angel Garcia Zarco (cointec) I have observed this issue in ascending and descending index. |
Commented by: Roman KIslukhin (roman.kisluhin) Count of nodes increses on any index independently of direction or count of fields. But only on descending compound indices it affects to performance if index used for ordering: create table test( create index idx1 on test (receiver); execute block as gstat: Query Plan Query Time Memory Operations Enchanced Info: After several updates like this: gstat: Query Plan Query Time Memory Operations Enchanced Info: As you can see both index reads and fetches increased. |
Commented by: @hvlad Roman KIsluhin, > Count of nodes increses on any index independently of direction or count of fields. This sounds logical and correct, of course > But only on descending compound indices it affects to performance if index used for ordering: You show no proof of *only*. I see no reason to blame nor descending, nor compound indices. |
Commented by: @hvlad All, this is old and more-or-less known limitation and it was done intentionally - to avoid much bigger problem. By initial design, Interbase (and later Firebird) doesn't add second index entry with the same key and record number. This doesn't work in concurrent environment, unfortunately, and could produce index with missed entries (which is So, many years ago (when FB 2.0 was developed, iirc), algorithm above was changed a bit - since then Firebird So far i have no good solution for this problem. |
Commented by: Roman KIslukhin (roman.kisluhin) Should not we remove duplicate garbage from indices in such way as for tables? Depending on last interesting transaction numbers etc.? Is there in the index structure some data helps to do it? |
Commented by: @hvlad Roman, index entries contains no transaction numbers. |
Commented by: Roman KIslukhin (roman.kisluhin) Is there any workaround for the issue? We have a big issue with tables containing such indices. E.g, table with 6k rows executes queries for 30-600 seconds (sic!) instead of several ms depending of its life. Recreating indices returns performance, but it is not possible in 24x7 environment. I think that the behaviour may produce noticeable overhead in whole system too. |
Commented by: @hvlad As a workaround you may : |
Commented by: Roman KIslukhin (roman.kisluhin) The second approach does not helps as for unique indices the same performance penalty exists. |
Commented by: @hvlad Also, i want to add that with cooperative garbage collection and if GC is not blocked by stuck OST this problem should be much less visible |
Commented by: @hvlad > The second approach does not helps as for unique indices the same performance penalty exists. Let me not believe you. |
Commented by: Roman KIslukhin (roman.kisluhin) Moreover, adding and sequentally deleting record produces nodes too. So, we can't edit or delete records in tables without the issue. |
Commented by: Roman KIslukhin (roman.kisluhin) >Let me not believe you. |
Commented by: @hvlad Roman, test case could help to explain what happens. |
Commented by: Roman KIslukhin (roman.kisluhin) Vlad, |
Commented by: Roman KIslukhin (roman.kisluhin) create table test( create index idx1 on test (receiver) execute block as Query Plan Operations INDEX READS: 100 update test set state=2; Query (0 records fetched!) Plan Operations INDEX READS: 1000 update test set state=3; Query Plan Operations INDEX READS: 2100 |
Commented by: Roman KIslukhin (roman.kisluhin) insert into test (id, state, priority, receiver) values(1001, 2, 1, 4); Query Plan Operations Adding and deleting one record increase fetches by one page... So... |
Commented by: @hvlad Well, i was not fully correct, sorry change by First version can't make index entries unique, obviously |
Commented by: Roman KIslukhin (roman.kisluhin) delete from test; select count(*) from test; (0) Query Plan Operations And we have several milliones fetches in our case, so it is not suitable... |
Commented by: @hvlad Another suggestion: avoid updates where same value assigned at every second update. About insert, delete, insert... case: let garbage be collected after delete. |
Submitted by: @dmitry-starodubov
Votes: 2
create table test(
id numeric(15),
state smallint,
priority smallint);
create descending index idx_desc on test(priority, state);
set term ^;
execute block as
declare variable i integer;
begin
i = 0;
while (i < 1000) do begin
insert into test (id, state, priority)
values(:i, 1, 1);
i = i + 1;
end
end^
After this "gstat -r" shows expected 1000 records and 1000 index nodes.
But after several updates too many nodes are created.
update test set state=2;
update test set state=3;
commit;
I repeat it 5 times and get such statistics:
TEST (128)
Primary pointer page: 166, Index root page: 167
Average record length: 13.74, total records: 1000
Average version length: 13.74, total versions: 1000, max versions: 1
...
Index IDX_DESC (0)
Depth: 2, leaf buckets: 5, nodes: 5000
Average data length: 0.01, total dup: 4999, max dup: 4999
And these nodes are not deleted after cleaning table and garbage collecting.
SQL> delete from test;
SQL> commit;
SQL> select * from test;
TEST (128)
Primary pointer page: 166, Index root page: 167
Average record length: 0.00, total records: 0
Average version length: 0.00, total versions: 0, max versions: 0
...
Index IDX_DESC (0)
Depth: 2, leaf buckets: 5, nodes: 4000
Average data length: 0.01, total dup: 3999, max dup: 3999
The text was updated successfully, but these errors were encountered: