Retention of monitoring data stored in MySQL DB

[arrabito]

Dear all,

I would have some questions about the retention policy of monitoring data.

As they are intended to be primarly stored on OpenSearch I guess that it's sufficient to define a retention policy for the different indexes in OpenSearch.

Now, as our OpenSearch infrastructure is not ready yet, we are currently storing them in the MySQL AccountingDB, but some tables, especially ac_type_<setup>_WMSHistory , are growing quite fast, so that I would like to implement some retention policy for the monitoring data in the AccountingDB (for info we are running DIRAC v8 for this instance).

However, I'm not totally sure in which tables monitoring data are stored and how safe it is to simply delete entries based on the date.

Note that we don't want to remove Accounting data, but only Monitoring ones.

Could you please tell among the list of tables in the attached file CTA_AccountingTables.txt which are the ones hosting the monitoring data?

As far as I understand for WMSHistory monitoring data, these are the following ones:

ac_bucket_<setup>_WMSHistory                           
ac_in_<setup>_WMSHistory                                   
ac_type_<setup>_WMSHistory  

but I'm not totally sure and also I'm not sure about which are the tables for the other categories of monitoring data.

Would it be safe to remove entries from the concerned tables, for instance older than 1 month, based on startTime or endTime column values?

Thank you,

Luisa

[fstagni]

Hi,
the WMSHistory table growing fast was the original issue we used for moving something to OpenSearch.
The retentions are indeed policies in OpenSearch.

I personally do not have experience on selective deletes from the accounting DB. For WMSHistory, once we migrated to OpenSearch, we left both in parallel for a while and at some point we deleted the whole in MySQL.
You should not delete from the in table. IIRC the type is basically the raw data (which means that, again IIRC it can be deleted. The bucket is what is used for display.

[arrabito]

Hi @fstagni,

thanks for these explanations. So I understand that the best would be that we setup OpenSearch as soon as possible.

In the meanwhile, is it safe to completely remove the content of all the table ac_type__WMSHistory?

I mean can you please confirm that it only impacts the plots obtained through the following selection?

If yes, we don't care to keep these data indeed and as an immediate result we would free 460 GB.

Thank you,

Luisa

[fstagni]

That would be the main impact, but there's also the "ShareCorrectors": https://dirac.diracgrid.org/en/latest/AdministratorGuide/Configuration/ConfReference/Operations/JobScheduling/index.html

which I believe will not impact you, for this I would prefer that @atsareg intervene

[arrabito]

Hi @fstagni thank you for the confirmation.
We don't use "ShareCorrectors" and anyway we mostly run all jobs with the same group, so I don't think that there will be an impact.
Now the question is, could we simply drop the table and create a new empty one?
I see indeed that there is already an automatic process deleting entries older than 14 months, but the delete queries take very long to execute so that it would be much faster to drop the table.
In this case, will it be created automatically by restarting the AccountingDataStore service?

[fstagni]

Yes, you can drop the current tables, and yes I think they will be re-created automatically.

[arrabito]

Hi @fstagni I've first tried with our certification instance:

drop table ac_type_WMSHistory;

and I confirm that restarting the Accounting_DataStore service recreates an empty table.

However, I see that I can still generate non empty plots for WMSHistory.

Is this due to the fact that I didn't dropped the ac_bucket_WMSHistory as well?

Is it safe to remove it as well?

Thank you.

[fstagni]

Yes, you can remove all the "WMSHistory" ones.

[arrabito]

ok even:
ac_in_WMSHistory?
I thought you said that we shouldn't from the in table...

[fstagni]

IIRC the in table is like a buffer before data goes into the bucket.

[arrabito]

ok thanks a lot.
I've just tried on the certification instance to drop the 3 tables and everything restarts correctly then.

[arrabito]

Hi @fstagni just to let you know that I've dropped the WMSHistory tables, restarted the Accounting DataStore and everything is working fine. Thank you.

I would have another question, indeed I took the opportunity of this clean up to also drop WMSHistory tables for a specific old DIRAC Setup, called CTA-Data, that we don't use anymore and that it has been also removed since a while from the CS.

However in the logs of the AccountingDataStore I get errors related to the dropped tables of this old CTA-Data' Setup:

2025-12-17 12:43:03 UTC Accounting/DataStore/Accounting/AccountingDB ERROR: _update (UPDATE `ac_in_CTA-Data_WMSHistory` SET taken=0): Execution failed. 1146: Table 'AccountingDB.ac_in_CTA-Data_WMSHistory' doesn't exist
2025-12-17 12:43:03 UTC Accounting/DataStore ERROR: Error while initializing Accounting/DataStore: MySQL Error ( 1131 : Execution failed.: ( 1146: Table 'AccountingDB.ac_in_CTA-Data_WMSHistory' doesn't exist ))

How can I avoid this error?

Should I also remove the key tables for this old Setup?

| ac_key_CTA-Data_WMSHistory_ApplicationStatus |
| ac_key_CTA-Data_WMSHistory_JobGroup          |
| ac_key_CTA-Data_WMSHistory_JobSplitType      |
| ac_key_CTA-Data_WMSHistory_MinorStatus       |
| ac_key_CTA-Data_WMSHistory_Site              |
| ac_key_CTA-Data_WMSHistory_Status            |
| ac_key_CTA-Data_WMSHistory_User              |
| ac_key_CTA-Data_WMSHistory_UserGroup         |
+----------------------------------------------+

service

[fstagni]

If you do not use anymore the CTA-Data setup, then all these tables should be removed. But also:

• what do you have in the table ac_catalog_types ? Probably a select name from ac_catalog_Types` is enough
• do you still have, somewhere in your cfgs (also the local ones) CTA-Data?

And one reminder: for DIRAC v9 all the Setup are disappearing.

[arrabito]

OK I will remove the CTA-Data tables and indeed in the ac_catalog_types I see also the old setups, e.g. :

mysql> select * from ac_catalog_Types where name like '%CTA-Data%';
+-------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------+----------------------------------+
| bucketsLength                                                                                   | valueFields                                                                                                                                           | keyFields                                                                                      | name                             |
+-------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------+----------------------------------+
| lti259200ei900eeti691200ei3600eeti15552000ei86400eeti31104000ei604800eee                        | TransferSize,TransferTime,RegistrationTime,TransferOK,TransferTotal,RegistrationOK,RegistrationTotal                                                  | OperationType,User,ExecutionSite,Source,Destination,Protocol,FinalStatus                       | CTA-Data_DataOperation           |
| lti691200ei3600eeti3024000ei14400eeti15552000ei86400eeti31536000ei172800eeti51840000ei604800eee | CPUTime,NormCPUTime,ExecTime,InputDataSize,OutputDataSize,InputDataFiles,OutputDataFiles,DiskSpace,InputSandBoxSize,OutputSandBoxSize,ProcessedEvents | User,UserGroup,JobGroup,JobType,JobClass,ProcessingType,Site,FinalMajorStatus,FinalMinorStatus | CTA-Data_Job                     |
| lti259200ei900eeti691200ei3600eeti15552000ei86400eeti31104000ei604800eee                        | Jitter,OneWayDelay,PacketLossRate                                                                                                                     | SourceIP,DestinationIP,SourceHostName,DestinationHostName,Source,Destination                   | CTA-Data_Network                 |
| lti691200ei3600eeti15552000ei86400eeti31104000ei604800eee                                       | Jobs                                                                                                                                                  | User,UserGroup,Site,GridCE,GridMiddleware,GridResourceBroker,GridStatus                        | CTA-Data_Pilot                   |
| lti172800ei900eeti864000ei9000eeti3024000ei18000eeti15552000ei86400eeti51840000ei604800eee      | NumTotal,NumSucceeded                                                                                                                                 | HostName,SiteDirector,Site,CE,Queue,Status                                                     | CTA-Data_PilotSubmission         |
| lti15552000ei86400eeti31104000ei604800eee                                                       | AvailableSpace,UsedSpace,TotalOnline,UsedOnline,FreeOnline,TotalNearline,UsedNearline,FreeNearline,ReservedNearline                                   | Site,Hostname,SpaceTokenDesc                                                                   | CTA-Data_SRMSpaceTokenDeployment |
| lti172800ei3600eeti864000ei21600eeti3456000ei43200eeti15552000ei172800eeti51840000ei604800eee   | Space                                                                                                                                                 | Site,Endpoint,StorageElement,SpaceType                                                         | CTA-Data_StorageOccupancy        |
| lti172800ei900eeti864000ei9000eeti3024000ei18000eeti15552000ei86400eeti51840000ei604800eee      | Jobs,Reschedules                                                                                                                                      | Status,MinorStatus,ApplicationStatus,Site,User,UserGroup,JobGroup,JobSplitType                 | CTA-Data_WMSHistory              |
+-------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------+----------------------------------+

So I will also remove those entries right?

• Then I don't have anymore any other reference to this old Setup in any cfgs.

Concerning v9, yes I'm aware, this is still the legacy instance running in v8, but at some moment we will also move it to v9.

[fstagni]

Yes, remove those lines from ac_catalog_types

[arrabito]

ok thanks.

And I guess that for the old Setup I should also remove not only the WMSHistory tables but also the other types:

_CTA-Data_DataOperation                         |
_CTA-Data_Job                                   |
_CTA-Data_Network                               |
_CTA-Data_Pilot                                 |
_CTA-Data_PilotSubmission                       |
_CTA-Data_SRMSpaceTokenDeployment               |
_CTA-Data_StorageOccupancy   

correct?

[fstagni]

Yes!

[arrabito]

Perfect thanks! All done and all working fine. Thanks a lot.