Grouper Users - Open Discussion List

["Hyzer, Chris" <>]

Carey, can you add an index on that column and see if it improves things?  
You can explain plan that query to see if it works :)  If so, Shilen, do you 
think we should add that index?

Carey, have you analyzed all your tables recently?  Not sure if rebuilding 
indexes helps in mysql, but it helped us at penn...

>       This "temp table" is auto dropped/created by the system?

No, it's a permanent temp table :)

>       When is the temp table "cleaned up"? (or should it never has "stuck 
> rows" after the job finishes?)

The loader will take rows, slap another timestamp on them, and put them in 
grouper_change_log_entry and remove from grouper_change_log_entry_temp

>       Can I just stop the loader then remove all rows from this temp table?

Change log entries wont be sent out, but yes, you can do that

>       There is some reason to NOT add an index to the created_on column?

I don't think so

>       Is it "safe" to do things like adding indexes to grouper's "core 
> tables"?

Sure.  Document it.  make sure on major upgrades you reapply the index if it 
is removed.  Also let us know if we should consider adding it to core.

>       Are there any other indexes that I should be manually adding? ( or 
> verifying that they were created properly?)

Not that I know of.

>       Why is the default 14 days before the change log is trimmed? ( Why 
> not 0, 1 or 2? )

They should be removed as loader copies them over.  So it should never go up 
to 14.  But if your loader is turned off for a while they should still 
progress through the change log notifications.  If two weeks has gone by, 
then all bets off.

Thanks
Chris

-----Original Message-----
From: 

 
[mailto:]
 On Behalf Of Black, Carey M.
Sent: Wednesday, August 30, 2017 11:08 PM
To: 

Subject: [grouper-users] How big should your temp change log be? ( Loader 
questions )

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