Hi Arnau,
For some reason I removed this information from the APEL FAQ page, I'll
put it back:
This is the recipe to remove any old/processed records from your local
accounting database. Please let us know if you have any questions about
this.
Cheers,
Cristina
Q: How to archive processed / old data
Tables in MySQL may crash when they reach 4Gb.
The records that have already been processed in APEL can be manually
archived to reduce the size of the APEL tables.
Create archive tables
In the local MySQL database on the site MON box run the following
commands.
mysql> create table EventRecords_archive like EventRecords;
mysql> create table MessageRecords_archive like MessageRecords;
mysql> create table GkRecords_archive like GkRecords;
mysql> create table BlahdRecords_archive like BlahdRecords;
mysql> create table LcgRecords_archive like LcgRecords;
Copy old processed data to the archive tables
Please substitute YYYY-MM-DD with the date you want to archive the data
until.
Please note: The table LcgRecords doesn't keep a flag indicating if the
record has been published/processed. This information is stored in the
RepublishInfo table instead. Do not archive LcgRecords records with
MeasurementDate newer than the date recorded in the RepublishInfo table.
You can find the latest successful publishing date by running the
following query:
mysql> select * from RepublishInfo;
The following queries copy the old data into the archive tables.
mysql> insert into EventRecords_archive (select * from EventRecords
where EventDate < 'YYYY-MM-DD' and Processed = 1);
mysql> insert into MessageRecords_archive (select * from MessageRecords
where ValidFrom < 'YYYY-MM-DD' and Processed = 1);
mysql> insert into GkRecords_archive (select * from GkRecords where
ValidFrom< 'YYYY-MM-DD' and Processed = 1);
mysql> insert into BlahdRecords_archive (select * from BlahdRecords
where ValidFrom < 'YYYY-MM-DD' and Processed = 1);
mysql> insert into LcgRecords_archive (select * from LcgRecords where
MeasurementDate < 'YYYY-MM-DD');
Remove old records from live tables
If the previous queries run successfully we can now delete the old
records from the live tables.
mysql> delete from EventRecords where EventDate < 'YYYY-MM-DD' and
Processed = 1;
mysql> delete from MessageRecords where ValidFrom < 'YYYY-MM-DD' and
Processed = 1;
mysql> delete from GkRecords where ValidFrom < 'YYYY-MM-DD' and
Processed = 1;
mysql> delete from BlahdRecords where ValidFrom < 'YYYY-MM-DD' and
Processed = 1;
mysql> delete from LcgRecords where MeasurementDate < 'YYYY-MM-DD';
Release unused space from tables
mysql> optimize table EventRecords;
mysql> optimize table MessageRecords;
mysql> optimize table GkRecords;
mysql> optimize table BlahdRecords;
mysql> optimize table LcgRecords;
-----Original Message-----
From: LHC Computer Grid - Rollout [mailto:[log in to unmask]] On
Behalf Of Arnau Bria
Sent: 15 September 2010 10:59
To: [log in to unmask]
Subject: [LCG-ROLLOUT] glite-APEL mysql DB clean
Hi all,
I'm doing mon to apel migration in our site. wiki [1] says that a mysql
dump must be exported from mon and imported to APEL. In our case it's a
14 GB DB and its size increases every day.
my question is: is there any cleaning script in new glite-APEL? If no,
do we have to keep old data in our local DB? may we remove data from,
i.e., last year?
I'm wondering what will happen if we keep only last 3/4 month in local
DB, (assuming that our sync for older months went fine and keeping
periodical local dumps, if needed).
TIA,
Arnau
[1] http://goc.grid.sinica.edu.tw/gocwiki/glite-APEL
|