Grouper Users - Open Discussion List

[Ben Beecher <>]

Chris,

I ran that query and I found that the daily maintenance job was failing every day because there were too many records to delete.  I tried removing the old records with a SQL command.  I got an error if I tried to remove more than 2 million records at a time.  It took about 45 minutes to remove each chunk so it would have taken a few days to remove them all.  I asked the DBA to truncate the grouper_change_entry_log table and the daily job is working again now.  Thanks for your help with this.

grp1p> alter session set nls_date_format='yyyy-mm-dd hh24:mi';

Session altered.

grp1p> select status, started_time, ended_time, millis,job_message from grouper_loader_log where JOB_NAME='MAINTENANCE_cleanLogs';

STATUS     STARTED_TIME     ENDED_TIME   MILLIS

-------------------- ---------------- ---------------- ----------

JOB_MESSAGE

--------------------------------------------------------------------------------

STARTED     2016-12-07 06:36

SUCCESS     2016-12-08 06:13 2016-12-08 06:13      911

Deleted 20880 records from grouper_loader_log older than 1 days old.  Deleted 0

records from grouper_change_log_entry older than 1 days old. (1481109208896000)

Ben

On Wed, Nov 30, 2016 at 6:42 PM, Hyzer, Chris <> wrote:

 

Run this query:  select status, started_time, ended_time, millis, job_message from grouper_loader_log where JOB_NAME = 'MAINTENANCE_cleanLogs';

 

SUCCESS	11/23/2016 6:00	11/23/2016 6:03	197574	Deleted 22677 records from grouper_loader_log older than 7 days old.  Deleted 19232 records from grouper_change_log_entry older than 14 days old. (1478689222184000)
SUCCESS	11/24/2016 6:00	11/24/2016 6:02	135615	Deleted 22722 records from grouper_loader_log older than 7 days old.  Deleted 7957 records from grouper_change_log_entry older than 14 days old. (1478775621878000)
SUCCESS	11/25/2016 6:00	11/25/2016 6:01	78362	Deleted 22723 records from grouper_loader_log older than 7 days old.  Deleted 4357 records from grouper_change_log_entry older than 14 days old. (1478862015949000)
SUCCESS	11/26/2016 6:00	11/26/2016 6:07	431774	Deleted 22723 records from grouper_loader_log older than 7 days old.  Deleted 26273 records from grouper_change_log_entry older than 14 days old. (1478948430228000)
SUCCESS	11/27/2016 6:00	11/27/2016 6:02	130949	Deleted 22725 records from grouper_loader_log older than 7 days old.  Deleted 4504 records from grouper_change_log_entry older than 14 days old. (1479034827254000)
SUCCESS	11/28/2016 6:00	11/28/2016 6:00	38121	Deleted 21311 records from grouper_loader_log older than 7 days old.  Deleted 191 records from grouper_change_log_entry older than 14 days old. (1479121223390000)
SUCCESS	11/29/2016 6:00	11/29/2016 6:01	79169	Deleted 22702 records from grouper_loader_log older than 7 days old.  Deleted 2439 records from grouper_change_log_entry older than 14 days old. (1479207627390000)
SUCCESS	11/30/2016 6:00	11/30/2016 6:05	308820	Deleted 22709 records from grouper_loader_log older than 7 days old.  Deleted 30572 records from grouper_change_log_entry older than 14 days old. (1479294025078000)

 

Are you getting successes?

 

I think what can happen is the database can choke on the delete query since there are so many records.  Can you remove the records with a loop of SQL?  If we need to change the logic we can delete records a chunk at a time…  though I haven’t seen this problem before in Grouper (I have seen it in other systems though)

 

Thanks

Chris

 

 

 

 

From: [mailto:] On Behalf Of Ben Beecher
Sent: Tuesday, November 29, 2016 11:18 AM
To:
Subject: [grouper-users] old records not removed from grouper_change_log_entry table

 

For the past two weeks we've been dealing with disk space issues in our Grouper database.  On 11/10 the data tablespace grew very large, apparently because we had assigned view privileges to a large number of users.  We have about 41700 reference groups and we had assigned the view privilege to each group for about 130000 users.  This produced a large number of transactions and the grouper_change_log_entry table grew very large.  On 11/17 there were 148 million records in that table and it was growing by 16 million records per day.  Our data tablespace grew from about 30GB to 171GB.  We truncated the grouper_change_log_entry table and we added these lines to the grouper-loader.properties file and restarted the loader:

 

loader.retain.db.logs.days=1

loader.retain.db.change_log_entry.days=1

 

We removed the view privileges for those 130000 users to the reference groups and that created several million new transactions.  We expected the table to shrink but it still has 131 million records.  It is currently growing by about 4133 records per day.  The old records are not being removed.  Our data tablespace has grown to 179GB.

 

Why are the old records not being removed from the grouper_change_log_entry table?

Should we truncate the table again?

 

The created_on field should contain milliseconds since epoch but those values are much larger than expected (E+15 instead of E+12):

 

sql> select (sysdate - to_date('01-Jan-1970', 'dd-Mon-yyyy')) * 86400000 from dual;

 

1.4804E+12   (millis since epoch for today)

 

sql> select created_on,TO_DATE('1970-01-01','YYYY-MM-DD') + created_on / 86400000 from grouper_change_log_entry where sequence_number=166996954;

 

CREATED_ON TO_DATE('

---------- ---------

1.4794E+15 25-OCT-14  (millis since epoch for the oldest record in that table)

 

sql> select created_on,TO_DATE('1970-01-01','YYYY-MM-DD') + created_on / 86400000 from grouper_change_log_entry where sequence_number=297672536;

 

CREATED_ON TO_DATE('

---------- ---------

1.4804E+15 23-AUG-46  (millis since epoch for the newest record in that table)

 

I set log level to DEBUG and I did not see any error messages in the log regarding the grouper_change_log_entry table.

 

Ben