Change DDL of rdb$user_privileges index: pair (rdb$privilege,rdb$field_name)
can be added to index with key = (rdb$user)
------------------------------------------------------------------------------------------------------------------------
Key: CORE-5465
URL: http://tracker.firebirdsql.org/browse/CORE-5465
Project: Firebird Core
Issue Type: Improvement
Affects Versions: 4.0 Initial
Reporter: Pavel Zotov
Priority: Trivial
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
{
# Do we trace database events or not
enabled = true
# Put sql statement execution finish\fetch to eof records
log_statement_finish = true
# Print access path (plan) with sql statement
print_plan = true
# Use legacy (false) or explained (true) plan format
explain_plan = true
# Print detailed performance info when applicable
print_perf = true
# Put xxx_finish record only if its timing exceeds this number of
milliseconds
time_threshold = 0
# Maximum length of SQL string logged
# Beware when adjusting max_xxx parameters! Maximum length of log record
# for one event should never exceed 64K.
max_sql_length = 16384
# Maximum length of blr request logged
#max_blr_length = 500
# Maximum length of dyn request logged
#max_dyn_length = 500
# Maximum length of individual string argument we log
#max_arg_length = 80
# 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.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, SlashDot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
