Skip to Content.
Sympa Menu

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

Subject: Grouper Users - Open Discussion List

List archive

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

Chronological Thread 
  • From: "Black, Carey M." <>
  • To: "" <>
  • Subject: [grouper-users] How big should your temp change log be? ( Loader questions )
  • Date: Thu, 31 Aug 2017 03:08:14 +0000
  • Accept-language: en-US
  • Authentication-results: spf=pass (sender IP is;; dkim=none (message not signed) header.d=none;; dmarc=pass action=none;
  • Ironport-phdr: 9a23:aKQ62BT236zKWKgd+EL5uZUJUtpsv+yvbD5Q0YIujvd0So/mwa6zYhGN2/xhgRfzUJnB7Loc0qyN4vCmATRIyK3CmUhKSIZLWR4BhJdetC0bK+nBN3fGKuX3ZTcxBsVIWQwt1Xi6NU9IBJS2PAWK8TW94jEIBxrwKxd+KPjrFY7OlcS30P2594HObwlSijewZbB/IA+qoQnNq8IbnZZsJqEtxxXTv3BGYf5WxWRmJVKSmxbz+MK994N9/ipTpvws6ddOXb31cKokQ7NYCi8mM30u683wqRbDVwqP6WACXWgQjxFFHhLK7BD+Xpf2ryv6qu9w0zSUMMHqUbw5Xymp4qF2QxHqlSgHLSY0/mHJhMJtkKJVrhGvpx1jzIDbb46YL+Z+frrZcN8GWWZNQthdWipcCY28dYsPCO8BMP5Wo4Tgo1sBtwexBQq0COjy1DJHnGX20rM60u88Fg/KxgIsFM8JvXvPqNX6LrsdUeOvwKXV0znOYehb2TDm6IjVaBwuv+yDXa9pfMfX1EIhGQTFjlCKpozkOTOYzuENvHKB7+phT+2glnUopxttrTiow8chjJTCiIENyl3c6yl22po5KcC9RUJmfNKoDZ5dty+VOodqXs8vRm9otzg1x7EYvJO2eSoHxIk5yxLHdvCLbYuF7gj+WOueIDp0nnZodb2lixqs/0WtzvXwVseq31tJsiZIl9zBu3AR2BPP5MiKT+Vx80ig1DmS0g3e5eFJLEI6mKfeN5Eu2aU/mYAVvE/eBCH5gl/2g7WTdkg8+uin9eDnYrL+q5GEK4J6jR3yPrk3lsGiA+s0LBECX2+A9uuizrHj+lD5QKlRgf0xj6nZto3VKd4Dpq6jBA9Vzpgs5AqjDzen19QYm2MLLFVYeBKbi4jpPFbOIPPiAfijhFSslS9nx/HAPrL/HpXANmbPn639cbpg7kNQ1Rc/wc1a6pJaBbwNPO7/V0r/tNPGARI1LQm5zuPlBdlgyI8SQWePDbWYMKPWv1+I/OUvI+yUaYAOvDbyMeQq5/30gXMkh1MQc7Kk3ZwMaHClBPhpPViWYWf0jtcbDWgKphY+TPDtiFCaXj5TfXGyX78k5j4lEoKqFJrDRpuzj7ybxye2BZlWZmFdClCQCnfkaZ+IW/YKaCKOPMBhiDoEWqa9S4M/zx2hqhL1y6c0ZtbTr2cXr5X+zNVvovDImAsp3T1yE8mH1WyRFSd5kn5CD2s5xqdiuUFnj0qY3LJjq/1eCdFJ4f5VCEE3OYOKnMJgDNWnECjFd9yKDB6NS8+rEHkUCJh5l9UKak1+XYz41TjExDfsDrMIwe/YTKco+77RiiCib/12zGzLgfEs
  • Spamdiagnosticmetadata: NSPM
  • Spamdiagnosticoutput: 1:99


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

-- Connection Id: NNNNN
-- User: DB_USER
-- Host: host:port
-- DB: grouper
-- Command: Query
-- Time: 291
-- State: Creating sort index
select 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:
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

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
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

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

Thanks in advance.

Carey Matthew

  • [grouper-users] How big should your temp change log be? ( Loader questions ), Black, Carey M., 08/31/2017

Archive powered by MHonArc 2.6.19.

Top of Page