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

Server hangs with 100% CPU usage on NOT IN predicate [CORE1137] #1559

Closed
firebird-automations opened this issue Feb 20, 2007 · 15 comments
Closed

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @pcisar

Is duplicated by CORE2097
Is duplicated by CORE1423
Is related to QA298

Attachments:
SWSDATA.rar

It doesn't show up all the time (tested on the same db structure but with bogus data and it doesn't show up). But it always happen with attached database on next statement:

select * from stamm
where
Status in (8,18,19)
and
Stammid not in (select Stammid from Kontakte where Art='abgelegt')

Plan
PLAN (KONTAKTE NATURAL)
PLAN (STAMM INDEX (STAMM_ISTATUS, STAMM_ISTATUS, STAMM_ISTATUS))

Use of Stammid <> ALL (select..) doesn't make any difference.

But next statement without NOT works ok:

select * from stamm
where
Status in (8,18,19)
and
Stammid in
(select Stammid from Kontakte
where Art='abgelegt'
)

Plan
PLAN (KONTAKTE INDEX (KONTAKTERSTAMMID))
PLAN (STAMM INDEX (STAMM_ISTATUS, STAMM_ISTATUS, STAMM_ISTATUS))

Verified that this bug is not present in 1.5.4.

Commits: b4c98c2 3e1f88a

@firebird-automations
Copy link
Collaborator Author

Commented by: @pcisar

Windows database with data for issue replication.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Attachment: SWSDATA.rar [ 10270 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

RelNotes, page 102, "ALL predicate may be slow". It applies to NOT IN the same way.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Execute time = 7m 50s. Everything is "as designed" (although I do understand that not everybody's happy, sigh).

@firebird-automations
Copy link
Collaborator Author

Commented by: @pcisar

Now I see, it's THIS one. Well, I would not expect it's so bad on just few records. The sample database has:

KONTAKTE: Average record length: 37.77, total records: 223228, Data pages: 17769
STAMM: Average record length: 18.93, total records: 9448, Data pages: 572

As it utilizes CPU on 100%, it makes FB unusable in this case especially in multi-user environment. Anyway, it's not a bug as it fixes another critical bug, but it's an important regression, so I'll change it to improvement with critical priority. We have to do something about it soon.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

issuetype: Bug [ 1 ] => Improvement [ 4 ]

description: This is probably data sensitive (character set or collation could be important) issue, as it doesn't show up all the time (tested on the same db structure but with bogus data and it doesn't show up). But it always happen with attached database on next statement:

select * from stamm
where
Status in (8,18,19)
and
Stammid not in (select Stammid from Kontakte where Art='abgelegt')

Plan
PLAN (KONTAKTE NATURAL)
PLAN (STAMM INDEX (STAMM_ISTATUS, STAMM_ISTATUS, STAMM_ISTATUS))

Use of Stammid <> ALL (select..) doesn't make any difference.

But next statement without NOT works ok:

select * from stamm
where
Status in (8,18,19)
and
Stammid in
(select Stammid from Kontakte
where Art='abgelegt'
)

Plan
PLAN (KONTAKTE INDEX (KONTAKTERSTAMMID))
PLAN (STAMM INDEX (STAMM_ISTATUS, STAMM_ISTATUS, STAMM_ISTATUS))

Verified that this bug is not present in 1.5.4.

=>

It doesn't show up all the time (tested on the same db structure but with bogus data and it doesn't show up). But it always happen with attached database on next statement:

select * from stamm
where
Status in (8,18,19)
and
Stammid not in (select Stammid from Kontakte where Art='abgelegt')

Plan
PLAN (KONTAKTE NATURAL)
PLAN (STAMM INDEX (STAMM_ISTATUS, STAMM_ISTATUS, STAMM_ISTATUS))

Use of Stammid <> ALL (select..) doesn't make any difference.

But next statement without NOT works ok:

select * from stamm
where
Status in (8,18,19)
and
Stammid in
(select Stammid from Kontakte
where Art='abgelegt'
)

Plan
PLAN (KONTAKTE INDEX (KONTAKTERSTAMMID))
PLAN (STAMM INDEX (STAMM_ISTATUS, STAMM_ISTATUS, STAMM_ISTATUS))

Verified that this bug is not present in 1.5.4.

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

I do not think it's possible to do something with it. To satisfy SQL standard, we can not use indices. As long as we do not use them, 100% CPU utilization is typical. I had to review all applications, when moving to FB2, in order to change "not in" to "not exists", which works fine and does exactly what "not in" did before.

BTW, may be SORT + MERGE may be used in such cases?

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

The only more-or-less easy solution is to cache the subsquery rowset. The engine never did that (except for SORT, but that's just an implementation detail), so some major changes are requred. I have RsbBuffer implemented locally, but I don't think we should add it to the public codebase yet.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is duplicated by CORE1423 [ CORE1423 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 11622 ] => Firebird [ 15463 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is duplicated by CORE2097 [ CORE2097 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Dmitry Yemanov [ dimitr ] => Adriano dos Santos Fernandes [ asfernandes ]

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.5 Beta 1 [ 10251 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA298 [ QA298 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants