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

Change DDL of rdb$user_privileges index: pair (rdb$privilege,rdb$field_name) can be added to index with key = (rdb$user) [CORE5465] #5735

Open
firebird-automations opened this issue Jan 22, 2017 · 2 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @pavel-zotov

Attachments:
improve-DDL-of-rdb_user_privileges.7z

Every connect to current 4.0 databases leads to auto-run following query by system attachment:

with recursive role\_tree as \(
     select rdb$relation\_name as nm, 0 as ur
     from rdb$user\_privileges
     where
         rdb$privilege = 'M'
         and rdb$field\_name = 'D'
         and rdb$user = ? \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\- is substituted by current user 
         and rdb$user\_type = 8
     UNION ALL
     select rdb$role\_name as nm, 1 as ur
     from rdb$roles
     where rdb$role\_name = ? \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\- is substituted by current role
     UNION ALL
     select p\.rdb$relation\_name as nm, t\.ur
     from rdb$user\_privileges p
     join role\_tree t on t\.nm = p\.rdb$user
     where
         p\.rdb$privilege = 'M'
         and \(p\.rdb$field\_name = 'D' or t\.ur = 1\)
\)
select  r\.rdb$role\_name,  r\.rdb$system\_privileges
from role\_tree t
join rdb$roles r on t\.nm = r\.rdb$role\_name;

===

For SYSDBA with role = NONE on empty DB this query requires ~615 indexed reads of RDB$USER_PRIVILEGES table.
The more objects in DB (users/roles, tables, etc) - the more IRs will be done. This can lead to perormance problems when lot of users make connect / disconnect frequently.

This is because table RDB$USER_PRIVILEGES has _no_ index with key: (rdb$user,rdb$privilege,rdb$field_name)
But it HAS index with starting part of this expr: (rdb$user).

So, my suggestion is: ADD two fields to this index, i.e. make its key = (rdb$user,rdb$privilege,rdb$field_name).
#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠

One may to estimate number of unnecessary IRs:

* download scripts from attach;

* create empty DB in 4.0, make its FW = OFF (for sped); assign alias 'e40' for this DB;

* run: C:\FB\40SS\isql /:e40 -i roles-ddl.sql 2>roles-ddl.err
(it can take 3-4 minute; ensure that file with errors "roles-ddl.err " is empty after finish)

NOTE that script 'roles-ddl.sql' has following tail:

recreate table user_priv_copy ( ------------- this table is used for comparison, see script 'roles-run.sql'
< ... DDL as in rdb$user_privileges ... >
);
insert into user_priv_copy select * from rdb$user_privileges;
create index up_rel_name on user_priv_copy (rdb$relation_name);
create index up_user_priv_fldname on user_priv_copy (rdb$user,rdb$privilege,rdb$field_name); -- modified key here: add two fields.
...

* launch trace session with following config (it's also in attached .7z):

database
\{
	#&#x2060; Do we trace database events or not
	enabled = true
	
	#&#x2060; Put sql statement execution finish\\fetch to eof records 
	log\_statement\_finish = true

	#&#x2060; Print access path \(plan\) with sql statement
	print\_plan = true

	#&#x2060; Use legacy \(false\) or explained \(true\) plan format
	explain\_plan = true

	#&#x2060; Print detailed performance info when applicable
	print\_perf = true

	#&#x2060; Put xxx\_finish record only if its timing exceeds this number of milliseconds
	time\_threshold = 0

	#&#x2060; Maximum length of SQL string logged 
	#&#x2060; Beware when adjusting max\_xxx parameters\! Maximum length of log record
	#&#x2060; for one event should never exceed 64K\.
	max\_sql\_length = 16384

	#&#x2060; Maximum length of blr request logged 
	#&#x2060;max\_blr\_length = 500

	#&#x2060; Maximum length of dyn request logged 
	#&#x2060;max\_dyn\_length = 500

	#&#x2060; Maximum length of individual string argument we log 
	#&#x2060;max\_arg\_length = 80

	#&#x2060; Maximum number of query arguments to put in log 
	max\_arg\_count = 50
\}

===

* run file 'roles-run.sql' from attach. It will make connect to alias 'e30' and then imitate the same query as ATT_0 does.

* open trace log. You will see that ATT_0 did

param0 = varchar(252), "U_1999"
param1 = varchar(252), "R_19"

1 records fetched
11 ms, 15233 fetch(es)

Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
RDB$USER_PRIVILEGES 15021
RDB$ROLES 2

* Compare with possible statistics when use index on table with increased key

1 records fetched
0 ms, 3 read(s), 12 fetch(es)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
RDB$ROLES 2

PS.

Interesting that for query from roles-run.sql:

with recursive role\_tree as \(
     select rdb$relation\_name as nm, 0 as ur
     from USER\_PRIV\_COPY 
     \.\.\. \.\.\. \.\.\.
\)
\.\.\.\.

===

-- trace log does NOT contain line with 'USER_PRIV_COPY' in statistics per table section. Only 'RDB$ROLES' can be seen there.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

Attachment: improve-DDL-of-rdb_user_privileges.7z [ 13059 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

assignee: Alexander Peshkov [ alexpeshkoff ]

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