Grouper Users - Open Discussion List

["Black, Carey M." <>]

Chris,

Thank you for the details.

FWIW: 
        I stopped running all of the loader jobs.
        The  grouper_change_log_entry_temp table has cleared itself  over a 
few days.

        So I think it is reasonable to believe that the jobs were submitting 
more records into the temp table than the loader process was able to convert 
to the "grouper_change_log_entry" table.
        Assuming that the select from the temp table was a "slow point", then 
maybe the index will help the loader to keep up with the queue. :)
                If the index does not resolve it, then I would ask if the 
"thread counts" match between the loader processes that generate the "_temp" 
entries vs the ones moving the rows to the "grouper_change_log_entry"?
        
        For now, I will add the index and start up a few of the jobs and see 
if I can find the "max rate" point for our system. 


Just a thought:
        Since there may be a local need for "DB customizations", maybe 
supporting some kind of "local sql script" to run after an upgrade would be 
helpful?

        If there were a standard way(config file?) to register/track local DB 
changes then the upgrade could be "pre-wired" for the upgrade to auto apply 
the local changes too.
        I do recognize that some SQL might not work after the upgrade. 
However "safe things" (like adding indexes and adding custom Tables) should 
work. Views based on the grouper tables might fail due to grouper RDBMS 
structure changes. But that would be a good thing to find in the upgrade 
process anyways.
        Maybe the SQL file could be conditionally run at startup of the 
loader too? ( Kind of an "All Sync" type option?)

-- 
Carey Matthew 



-----Original Message-----
From: Hyzer, Chris 
[mailto:]
 
Sent: Tuesday, September 5, 2017 2:07 PM
To: Black, Carey M. 
<>;
 

Subject: RE: How big should your temp change log be? ( Loader questions )

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