Skip to Content.
Sympa Menu

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

Subject: Grouper Users - Open Discussion List

List archive

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


Chronological Thread 
  • From: "Hyzer, Chris" <>
  • To: "Black, Carey M." <>, "" <>
  • Subject: [grouper-users] RE: How big should your temp change log be? ( Loader questions )
  • Date: Tue, 5 Sep 2017 18:06:58 +0000
  • Accept-language: en-US
  • Authentication-results: spf=none (sender IP is ) ;
  • Ironport-phdr: 9a23:MWBi6RWgewgPz46PaEDfrq3sEjfV8LGtZVwlr6E/grcLSJyIuqrYZRSHtadThVPEFb/W9+hDw7KP9fuxCSpYud6oizMrSNR0TRgLiMEbzUQLIfWuLgnFFsPsdDEwB89YVVVorDmROElRH9viNRWJ+iXhpTEdFQ/iOgVrO+/7BpDdj9it1+C15pbffxhEiCCzbL52Lxi6txndutULioZ+N6g9zQfErGFVcOpM32NoIlyTnxf45siu+ZNo7jpdtfE8+cNeSKv2Z6s3Q6BWAzQgKGA1+dbktQLfQguV53sTSXsZnxxVCAXY9h76X5Pxsizntuph3SSRIMP7QawoVTmk8qxmUwHjhjsZODEl8WHXks1wg7xdoBK9vBx03orYbJiIOPZiYq/ReNUXTndDUMlMTSxMGoOyYZUSAeodM+hWrIf9qFkPrRSiCgejC/jiyiNRhnPqwaE2z/gtHR/A0Qc9H9wOqnPUrNDtOascU+60zKjJwivZb/9OxDzw9ojIcgogofGXU757bM3cyUwzGAPFjlSRqJLqPymO2+QMqGiU8/BsVf+3i2E5rQFxpCKjydkxhYnUn48YzE3P+yt+wIYwP9K4SUh7bMahEJRKrSGaNpF5Td45TG1ypCk6zbgGtJimdyYJ0JQq3wDTZOabf4SV5x/vSfudLSp9iXJrd7+zmwq+/E2ix+LiWcS4zFNHoy9bntXQuH0BzwHf58abRvdn+kqtxCyD2x7O5uxAO0w4iKjWJp8nz7UtjJQcq17DETXzmEjujK+ZaEEk+u+w5ur/frjooYOQO5Jthg/jKqoih9WzAeMjPQcQRWSb/vm81KH48k3+XbVKiOA5nrPBsJDAIsQburC2DBNJ0oYi7Ba/CS2q0NMFnXkbKFJFfxWHj4vzN17SJ/D4CO+zg1WqkDh12/DLJqPtDY/RInTelbrtYKtx51NZxQo9wt1T+45YB7QDLf3tVUL9qNnVAxonPwCozevrENB92ZkfWWKLDK+ZKqTSsVqQ6+IqP+aMZI8UuTfjJPg//PPujGQ5lkMHcam0xZsYdna4HvJ8L0qHfHXsn88NEX0WsQomUOzqlFqCXCZcZ3aoWKI8+yk7B5y8DYvaW4Ctm6KO3D2gHpBNYmBGC0uMEWvzd4maQfsMaSSSItN/nTweU7iuVZMh2Q+0uADk1rVnM7mcxipN/7jn3dN2o6X4nAs/5HRRSY7Vh2uJRmp32DpSHBcxx705rEBgnBPLm6dihOFAGMYW+uhESBwSNJjAwvZ8BsyoHA/NY53BHFm8Rci+DCt0U8k82cQmYkBhFs+kgwyZmSemHulGuaaMAcl+0rPO0mK1b+190XfdnuF1ilImU9lCL0WnnaU56hDeAYiPnkmEwfX5PZ8A1TLAoT/QhVGFu1tVBUspCf3I
  • Spamdiagnosticmetadata: NSPM
  • Spamdiagnosticoutput: 1:99

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






Archive powered by MHonArc 2.6.19.

Top of Page