Cleaning Up the srmspacefile Table (SRM Space-token Allocations)

We recently found out that our srmspacefile table in dcache was inconsistent with our actual space usage. There were approximately 4 million entries in the srmspacefile table but in all of dCache/Chimera we had only 2.9 million files and a large fraction of those were not in space-token areas.

We have some commands that are useful to "update" the actual space-token usage in the srmspace table based upon the current contents of the srmspacefile table (NOTE: remove the udpate trigger on srmspace and recreate it after):

update srmspace set usedspaceinbytes=(select coalesce (sum(sf.sizeinbytes),0) from srmspace s left outer join srmspacefile sf on s.id=sf.spacereservationid and sf.state=2 and s.id=srmspace.id);

update srmspace set allocatedSpaceInBytes=(select coalesce (sum(sf.sizeinbytes),0) from srmspace s left outer join srmspacefile sf on s.id=sf.spacereservationid and sf.state<2 and s.id=srmspace.id);

update srmlinkgroup set reservedspaceinbytes = coalesce((select sum(sizeinbytes) from srmspace where linkgroupid=srmlinkgroup.id),0);

However in our case the problem is that the contents of the srmspacefile table was not consistent with what was actually stored on our dCache.

To fix this we proceeded as follows:

• Dump ALL pnfsid entries from the srmspacefile table in the dcache DB: \o /tmp/pnfsid_srmspacefile.log; select pnfsid from srmspacefile where state=2;
• Sort the output file: sort /tmp/pnfsid_srmspacefile.log > /tmp/pnfsid_srmspacefile.log.sorted (Edit the file FIRST to remove the header and blank lines)
• Dump ALL pnfsid entries from the t_locationinfo table in the chimera DB: \o /tmp/pnfsid_chimera.log; select ipnfsid from t_locationinfo
• Sort the output file: sort /tmp/pnfsid_chimera.log > /tmp/pnfsid_chimera.log.sorted (Edit the file FIRST to remove the header and blank lines)
• Use the comm command in linux to isolate the set of pnfsid's which are in the srmspacefile table but NOT in the t_locationinfo table (meaning they are non-existent files): comm -2 -3 /tmp/pnfsid_srmspacefile.log.sorted /tmp/pnfsid_chimera.log.sorted > /tmp/pnfsid_srmspacefile_not_in_chimera.log
• Use a simple perl/DBI script to process the list of pnfsid's and delete the corresponding entries from srmspacefile table: delete from srmspacefile where pnfsid?=
• Run the "update" commands listed above to reconstruct the space-token usage after we removed these "ghost" files

That's it. Here is some run info:

perl remove_pnfsid_srmspacefile.pl /tmp/pnfsid_srmspacefile_not_in_chimera.log
 Starting removal at Fri May 22 12:04:04 2009
...
 Processed 2010001 entries at Fri May 22 13:02:48 2009
 Processed 2020001 entries at Fri May 22 13:03:15 2009
 Processed 2030001 entries at Fri May 22 13:03:33 2009
 Finished deleting 2036169 records from srmspacefile...starting space-token update at Fri May 22 13:03:36 2009
 Updated usedspace, now updating allocatedspace at Fri May 22 13:18:43 2009
 Finishing at Fri May 22 13:18:43 2009

-- ShawnMcKee - 22 May 2009