[#CORE-5966] Slow performance when executing SQL scripts as non-SYSDBA user

Firebird Core

Slow performance when executing SQL scripts as non-SYSDBA user

Created: 18/Nov/18 07:03 PM Updated: 14/Dec/19 10:48 AM

Component/s:

Engine

Affects Version/s:

4.0 Alpha 1, 3.0.3, 3.0.4

Fix Version/s:

3.0.5

File Attachments:	1. prep.sh (0.5 kB)
Environment:	Database should be located on RAM filesystem in order to easier reproduce an issue.

QA Status:

No test

Description

« Hide

When a set of approxiamtely 30-50 SQL scripts containing updates of single records in various tables is executed as non-SYSDBA user granted rights to all required objects it takes 3-4 times more time compared with SYSDBA.

All

Comments

Change History

Subversion Commits

Sort Order:

[ Permalink | « Hide ]

Alexander Peshkov added a comment - 18/Nov/18 07:08 PM

Fixing this issue for FB4 should be done together with fixing security classes cache in order to make it support 'SQL SECURITY DEFINER' correctly, therefore I reassign it.

[ Permalink | « Hide ]

Ivan Pisarevsky added a comment - 02/Feb/19 07:30 AM

Roman, I have test case for this bug (database+script).
https://www.sql.ru/forum/1295388-1/proizvoditelnost-na-zapis-fb-3-0-3
Write to me, if you need a tester.

[ Permalink | « Hide ]

Ivan Pisarevsky added a comment - 03/Feb/19 04:39 PM

I have tested on build Firebird-3.0.5.33100 which included patch for this issue.
422 000 SQL scripts containing updates of single records in various tables is executed as non-SYSDBA user granted rights to all required objects it takes less then 20% more time compared with SYSDBA.
~10 minutes for SYSDBA, ~12 minutes for non-SYSDBA
I think, it is good result.

[ Permalink | « Hide ]

Roman Simakov added a comment - 24/Jul/19 07:09 AM - edited

Fix in https://github.com/FirebirdSQL/firebird/commit/3058fa48d35b24fcbb77df2605616eeedcf831cc

All other tests were passed OK, including my tests for SQL SECURITY.

Ivan, could you to test as well? This should be fixed in snapshot 4.0.0.1566 or higher.

[ Permalink | « Hide ]

Roman Simakov added a comment - 24/Jul/19 07:11 AM - edited

Attached prep.sh is my script I used to test.

The results are:
BEFORE:
roman:bin$ time ~/prep.sh 100000 sysdba

real 0m37,112s
user 0m8,534s
sys 0m10,293s
roman:bin$ time ~/prep.sh 100000 u

real 0m47,582s
user 0m9,598s
sys 0m11,884s

NOW:
roman:bin$ time ~/prep.sh 100000 sysdba

real 0m37,139s
user 0m8,807s
sys 0m10,407s
roman:bin$ time ~/prep.sh 100000 u

real 0m37,043s
user 0m8,915s
sys 0m10,307s

[ Permalink | « Hide ]

Ivan Pisarevsky added a comment - 30/Jul/19 11:59 AM

>Ivan, could you to test as well? This should be fixed in snapshot 4.0.0.1566 or higher.

Two weeks later.

[ Permalink | « Hide ]

Dmitry Yemanov added a comment - 13/Dec/19 08:10 AM

Any status update on this ticket? Ivan, did you have a chance to test the v4 snapshot?

[ Permalink | « Hide ]

Ivan Pisarevsky added a comment - 14/Dec/19 10:48 AM

I'm so sorry... I didn't test v4. migration 2.5 to 3.0 in progress. only one server on 2.5 remained and then test v4.