Issue Details (XML | Word | Printable)

Key: CORE-1137
Type: Improvement Improvement
Status: Resolved Resolved
Resolution: Fixed
Priority: Critical Critical
Assignee: Adriano dos Santos Fernandes
Reporter: Pavel Cisar
Votes: 0
Watchers: 3
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Server hangs with 100% CPU usage on NOT IN predicate

Created: 20/Feb/07 12:40 PM   Updated: 23/Feb/11 11:58 AM
Component/s: Engine
Affects Version/s: 2.0.1
Fix Version/s: 2.5 Beta 1

Time Tracking:
Not Specified

File Attachments: 1. File SWSDATA.rar (3.33 MB)

Environment:
1. Suse Linux 9.3, SuperServer for Linux i686 (NPTL) and Classic Server Linux I686.
2. Windows XP SuperServer (standard windows setup)
Issue Links:
Duplicate
 
Relate
 


 Description  « Hide
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.

 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 20/Feb/07 02:14 PM
RelNotes, page 102, "ALL predicate may be slow". It applies to NOT IN the same way.

Dmitry Yemanov added a comment - 20/Feb/07 02:38 PM
Execute time = 7m 50s. Everything is "as designed" (although I do understand that not everybody's happy, sigh).

Pavel Cisar added a comment - 21/Feb/07 02:56 AM
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.

Alexander Peshkov added a comment - 21/Feb/07 03:37 AM
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?

Dmitry Yemanov added a comment - 21/Feb/07 03:57 AM
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.