remove records with a max number and loop so it doesnt fail

From: Ben Beecher
Sent: Thursday, December 08, 2016 10:47 AM
To: Hyzer, Chris
Cc: grouper-users
Subject: Re: [grouper-users] old records not removed from grouper_change_log_entry table

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: grouper-users-request grouper-users-request On Behalf Of Ben Beecher
Sent: Tuesday, November 29, 2016 11:18 AM
To: grouper-users
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