[#CORE-1137] Server hangs with 100% CPU usage on NOT IN predicate

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:

SWSDATA.rar (3.33 MB)

Environment:

« Hide

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

This issue is duplicated by:

~~CORE-1423~~ Wrong PLAN in Subquery

Relate

This issue is related to:

QA-298 Test for CORE-1137

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:

[ Permalink | « Hide ]

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.

[ Permalink | « Hide ]

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).

[ Permalink | « Hide ]

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.

[ Permalink | « Hide ]

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?

[ Permalink | « Hide ]

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.