Issue Details (XML | Word | Printable)

Key: CORE-5465
Type: Improvement Improvement
Status: Open Open
Priority: Trivial Trivial
Assignee: Alexander Peshkov
Reporter: Pavel Zotov
Votes: 0
Watchers: 1

If you were logged in you would be able to see more operations.
Firebird Core

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

Created: 22/Jan/17 09:50 AM   Updated: 10/Mar/17 09:41 AM
Component/s: None
Affects Version/s: 4.0 Initial
Fix Version/s: None

File Attachments: 1. File improve-DDL-of-rdb_user_privileges.7z (2 kB)

QA Status: No test

 Description  « Hide
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
             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
             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):
     # 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

* 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


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.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
There are no comments yet on this issue.