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

Slow performance when executing SQL scripts as non-SYSDBA user [CORE5966] #6220

Closed
firebird-automations opened this issue Nov 18, 2018 · 16 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @reevespaul

Attachments:
prep.sh

Votes: 2

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.

Commits: 2bf6198

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

assignee: Alexander Peshkov [ alexpeshkoff ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

reporter: Alexander Peshkov [ alexpeshkoff ] => Paul Reeves [ paul_reeves ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

assignee: Alexander Peshkov [ alexpeshkoff ] => Roman Simakov [ roman-simakov ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

Fix Version: 3.0.5 [ 10885 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Ivan Pisarevsky (ivan_pisarevsky)

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: Ivan Pisarevsky (ivan_pisarevsky)

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @romansimakov

Fix in 3058fa4

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @romansimakov

Attached http://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

@firebird-automations
Copy link
Collaborator Author

Modified by: @romansimakov

Attachment: http://prep.sh [ 13363 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Ivan Pisarevsky (ivan_pisarevsky)

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

Two weeks later.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

Commented by: Ivan Pisarevsky (ivan_pisarevsky)

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.

@romansimakov
Copy link
Collaborator

Can we close this issue?

@AlexPeshkoff
Copy link
Member

Suggest to close - no reply from author for a lo-o-o-ong time, and existing tests show that it's solved.

@romansimakov
Copy link
Collaborator

Agreed

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