Grouper Users - Open Discussion List

["Black, Carey M." <>]

All,

I suspect that I have been pushing the loader process a bit harder than I 
should. (Or I am finding yet more configuration that I need to "tune" to keep 
things humming along.)

My DBA reported a "slow query" and I think this is related to loader jobs not 
"completing".

-- Connection Id: NNNNN
-- User: DB_USER
-- Host: host:port
-- DB: grouper
-- Command: Query
-- Time: 291
-- State: Creating sort index
select changeloge0_.id as id1_14_, changeloge0_.change_log_type_id as 
change_l2_14_, changeloge0_.context_id as context_3_14_, 
changeloge0_.created_on as created_4_14_, changeloge0_.string01 as 
string5_14_, changeloge0_.string02 as string6_14_, changeloge0_.string03 as 
string7_14_, changeloge0_.string04 as string8_14_, changeloge0_.string05 as 
string9_14_, changeloge0_.string06 as string10_14_, changeloge0_.string07 as 
string11_14_, changeloge0_.string08 as string12_14_, changeloge0_.string09 as 
string13_14_, changeloge0_.string10 as string14_14_, changeloge0_.string11 as 
string15_14_, changeloge0_.string12 as string16_14_ from 
grouper_change_log_entry_temp changeloge0_ order by changeloge0_.created_on 
limit 1000

While that is a bit odd to my eye... it basically is a:
  Select a, b, c ... from grouper_change_log_entry_temp changeloge0_ order by 
changeloge0_.created_on limit 1000

Some insight into the data in the table....

        select count(*) from grouper_change_log_entry_temp -- '6,745,965'   
That is "only" 6.7 M rows, in a "temp" table. ( Uh...? )
        select min(created_on) from grouper_change_log_entry_temp; -- 
'1503703639031000' --? Friday, August 25, 2017 11:27:19 PM GMT
        select max(created_on) from grouper_change_log_entry_temp; -- 
'1504121955115000' --? Wednesday, August 30, 2017 7:39:15 PM GMT 

        -- Assuming I am converting those dates properly... 
        -- That is just under 5 day's worth of data. ( And I think it will 
not start to trim it till 14 days. ( default value for: 
loader.retain.db.change_log_entry.days   Or does that apply to the "non-temp" 
loader table?  )


When I look at the grouper_change_log_entry_temp table, I do not see an index 
on the created_on column.

Based on reading of mariadb v10.2.8:
   REF: 
https://mariadb.com/kb/en/the-mariadb-library/improvements-to-order-by/
   It seems like there really should be an index on the created_on column to 
avoid performance issues and speed up the "limit" function of that select 
statement.

Can anyone tell me If:
        This "temp table" is auto dropped/created by the system?
        When is the temp table "cleaned up"? (or should it never has "stuck 
rows" after the job finishes?)
        Can I just stop the loader then remove all rows from this temp table?
        There is some reason to NOT add an index to the created_on column?
        Is it "safe" to do things like adding indexes to grouper's "core 
tables"?
        Are there any other indexes that I should be manually adding? ( or 
verifying that they were created properly?)
        Why is the default 14 days before the change log is trimmed? ( Why 
not 0, 1 or 2? )


On maybe a more general "DB Config" topic...
    select count(*) from grouper_memberships_all_v;   -- produces a 30 sec 
timeout result. No count returned. DB connection dead.  Uh.... Is there some 
overarching DB/DB client configuration issue that is causing me pain?
        Did I miss a section on Grouper's required DB configuration settings 
somewhere?

Is there any additional information/ configuration details that could help 
any of these questions be answered? ( Let me know.)

Thanks in advance.

-- 
Carey Matthew