Billing Database
In addition to writing out information to the billing logs (/opt/d-cache/billing), dCache can write the information into a postgreSQL database (for v1.6.6 onwards). To enable this, a postgreSQL server is expected to run on the admin node with a database user “srmdcache"? and a database “billing"?. The user srmdcache should previously have been created. Create the database,
# createdb -U srmdcache billing
and then add:
billingToDb=yes
to /opt/d-cache/config/dCacheSetup. Now restart the core dCache services.
If you then enter postgres, you can see the new database and look at its contents.
# su - postgres $ psql postgres=# \l List of databases Name | Owner | Encoding -----------+------------+----------- admin | pnfsserver | SQL_ASCII alice | pnfsserver | SQL_ASCII atlas | pnfsserver | SQL_ASCII billing | srmdcache | UTF8 cms | pnfsserver | SQL_ASCII companion | srmdcache | UTF8 data1 | pnfsserver | SQL_ASCII dcache | postgres | SQL_ASCII dteam | pnfsserver | SQL_ASCII lhcb | pnfsserver | SQL_ASCII postgres | postgres | UTF8 replicas | srmdcache | SQL_ASCII sixt | pnfsserver | SQL_ASCII template0 | postgres | UTF8 template1 | postgres | UTF8 (15 rows)
To connect to the database and then list the tables that it contains, do the following.
postgres=# \c billing You are now connected to database "billing". billing=# \d List of relations Schema | Name | Type | Owner --------+-------------+-------+----------- public | billinginfo | table | srmdcache public | costinfo | table | srmdcache public | doorinfo | table | srmdcache public | hitinfo | table | srmdcache public | storageinfo | table | srmdcache (5 rows)
To see the DN of a user who initiated a file transfer, run the following command.
billing=# select * from doorinfo; datestamp | cellname | action | owner | mappeduid | mappedgid | client | transaction | pnfsid | connectiontime | queuedtime | errorcode | errormessage -------------------------+----------------------------------------+---------+-----------------------------------------------------+-----------+-----------+----------------------+-------------------------------------------------------------+--------------------------+----------------+------------+-----------+-------------- 2005-11-30 18:15:12.752 | GFTP-wn4-Unknown-101@gridftp-wn4Domain | request | /C=UK/O=eScience/OU=Edinburgh/L=NeSC/CN=greig cowan | 18118 | 2688 | ui.epcc.ed.ac.uk | door:GFTP-wn4-Unknown-101@gridftp-wn4Domain:1133374512752-1 | 0004000000000000000019C8 | 1133374512748 | 0 | 0 | 2005-11-30 18:15:41.098 | GFTP-wn4-Unknown-102@gridftp-wn4Domain | request | /C=UK/O=eScience/OU=Edinburgh/L=NeSC/CN=greig cowan | 18118 | 2688 | ui.epcc.ed.ac.uk | door:GFTP-wn4-Unknown-102@gridftp-wn4Domain:1133374541098-2 | 0004000000000000000019C8 | 1133374541098 | 0 | 0 | 2005-11-30 18:16:01.406 | GFTP-wn4-Unknown-103@gridftp-wn4Domain | request | /C=UK/O=eScience/OU=Edinburgh/L=NeSC/CN=greig cowan | 18118 | 2688 | dcache.epcc.ed.ac.uk | door:GFTP-wn4-Unknown-103@gridftp-wn4Domain:1133374561406-3 | 0004000000000000000019C8 | 1133374561406 | 0 | 0 | (3 rows)
Respectively, these entries show the output from an srmPut, srmGet and srmCopy (from this dCache into another dCache). There is no logging for the case where this dCache copies a file from another SRM. This is because billing information is only stored when a transfer goes through dCache's own gridftp door implementation. When a pool gridftp door is used, then the globus implementation of the gridftp client is used and there is no record in the billing DB.
Contents
Update for dCache v1.7.0
With this release of dCache, there has been an change to the schema used in the billing database. The information here shows the entries that are created in the billinginfo and doorinfo tables upon different GridFTP copy requests. The other tables in the database remain empty.
srmcp
The order of the operations was srmPut, srmGet, srmCopy (dCache as destination), srmCopy (dCache as source).
billing=# select * from billinginfo where datestamp like '2006-12-01%' and storageclass like 'dteam:STA%'; datestamp | cellname | action | transaction | pnfsid | fullsize | transfersize | storageclass | isnew | client | connectiontime | errorcode | errormessage | protocol | initiator -------------------------+----------------------+----------+----------------------------------------------+--------------------------+----------+--------------+------------------+-------+--------------------------+----------------+-----------+--------------+--------------------------+--------------------------------------------------------------------- 2006-12-01 10:35:41.729 | pool1_03@pool1Domain | transfer | pool:pool1_03@pool1Domain:1164969341729-5235 | 000E000000000000001C8B98 | 583 | 583 | dteam:STATIC@osm | t | pool2.epcc.ed.ac.uk | 376 | 0 | | GFtp-1.0 | door:GFTP-pool2-Unknown-7982@gridftp-pool2Domain:1164969341401-1082 2006-12-01 10:39:37.866 | pool1_03@pool1Domain | transfer | pool:pool1_03@pool1Domain:1164969577866-5236 | 000E000000000000001C8B98 | 583 | 583 | dteam:STATIC@osm | f | pool2.epcc.ed.ac.uk | 2 | 0 | | GFtp-1.0 | <undefined> 2006-12-01 10:43:50.587 | pool1_03@pool1Domain | transfer | pool:pool1_03@pool1Domain:1164969830587-5240 | 000E000000000000001C8BC8 | 583 | 583 | dteam:STATIC@osm | t | gftp0444.gridpp.rl.ac.uk | 3943 | 0 | | RemoteGsiftpTransfer-1.1 | <undefined> 2006-12-01 10:46:30.375 | pool1_03@pool1Domain | transfer | pool:pool1_03@pool1Domain:1164969990375-5242 | 000E000000000000001C8BC8 | 583 | 583 | dteam:STATIC@osm | f | 130.246.179.19 | 68 | 0 | | GFtp-1.0 | <undefined> (4 rows)
billing=# select * from doorinfo where datestamp like '2006-12-01%' and owner like '%cowan'; datestamp | cellname | action | owner | mappeduid | mappedgid | client | transaction | pnfsid | connectiontime | queuedtime | errorcode | errormessage | path -------------------------+---------------------------------------------+---------+-----------------------------------------------------+-----------+-----------+-------------------+---------------------------------------------------------------------+--------------------------+----------------+------------+-----------+--------------+------------------------------------------------------------------- 2006-12-01 10:35:41.401 | GFTP-pool2-Unknown-7982@gridftp-pool2Domain | request | /C=UK/O=eScience/OU=Edinburgh/L=NeSC/CN=greig cowan | 12000 | 1040 | ui.epcc.ed.ac.uk | door:GFTP-pool2-Unknown-7982@gridftp-pool2Domain:1164969341401-1082 | 000E000000000000001C8B98 | 1164969341401 | 0 | 0 | | /pnfs/epcc.ed.ac.uk/data/dteam/greig_test_dir/20061201_103527.txt 2006-12-01 10:39:37.574 | GFTP-pool2-Unknown-7983@gridftp-pool2Domain | request | /C=UK/O=eScience/OU=Edinburgh/L=NeSC/CN=greig cowan | 12000 | 1040 | ui.epcc.ed.ac.uk | door:GFTP-pool2-Unknown-7983@gridftp-pool2Domain:1164969577574-1083 | 000E000000000000001C8B98 | 1164969577574 | 0 | 0 | | /pnfs/epcc.ed.ac.uk/data/dteam/greig_test_dir/20061201_103527.txt 2006-12-01 10:46:30.095 | GFTP-pool1-Unknown-8152@gridftp-pool1Domain | request | /C=UK/O=eScience/OU=Edinburgh/L=NeSC/CN=greig cowan | 12000 | 1040 | csfnfs63.rl.ac.uk | door:GFTP-pool1-Unknown-8152@gridftp-pool1Domain:1164969990095-1063 | 000E000000000000001C8BC8 | 1164969990095 | 0 | 0 | | /pnfs/epcc.ed.ac.uk/data/dteam/greig_test_dir/20061201_104400.txt (3 rows)
There is no entry in doorinfo for srmCopy (dCache as destination operation).
globus-url-copy
billing=# select * from billinginfo where datestamp like '2006-12-01 12%' and storageclass like 'dteam:STA%'; datestamp | cellname | action | transaction | pnfsid | fullsize | transfersize | storageclass | isnew | client | connectiontime | errorcode | errormessage | protocol | initiator -------------------------+----------------------+----------+----------------------------------------------+--------------------------+----------+--------------+------------------+-------+---------------------+----------------+-----------+--------------+----------+--------------------------------------------------------------------- 2006-12-01 12:00:31.283 | pool1_03@pool1Domain | transfer | pool:pool1_03@pool1Domain:1164974431283-5262 | 000E000000000000001C8BF8 | 583 | 583 | dteam:STATIC@osm | t | pool1.epcc.ed.ac.uk | 331 | 0 | | GFtp-1.0 | door:GFTP-pool1-Unknown-8169@gridftp-pool1Domain:1164974430982-1075 2006-12-01 12:01:30.726 | pool1_03@pool1Domain | transfer | pool:pool1_03@pool1Domain:1164974490726-5263 | 000E000000000000001C8BF8 | 583 | 583 | dteam:STATIC@osm | f | pool1.epcc.ed.ac.uk | 2 | 0 | | GFtp-1.0 | <undefined> 2006-12-01 12:02:45.971 | pool1_03@pool1Domain | transfer | pool:pool1_03@pool1Domain:1164974565971-5264 | 000E000000000000001C8C28 | 583 | 583 | dteam:STATIC@osm | t | pool1.epcc.ed.ac.uk | 1639 | 0 | | GFtp-1.0 | door:GFTP-pool1-Unknown-8173@gridftp-pool1Domain:1164974565722-1078 2006-12-01 12:03:24.207 | pool1_03@pool1Domain | transfer | pool:pool1_03@pool1Domain:1164974604207-5265 | 000E000000000000001C8C28 | 583 | 583 | dteam:STATIC@osm | f | 130.246.179.5 | 68 | 0 | | GFtp-1.0 | <undefined> (4 rows)
There is also now a single entry for each operation. client in each case is the hostname of the machine running the guc client.
billing=# select * from doorinfo where datestamp like '2006-12-01 12%' and owner like '%cowan'; datestamp | cellname | action | owner | mappeduid | mappedgid | client | transaction | pnfsid | connectiontime | queuedtime | errorcode | errormessage | path -------------------------+---------------------------------------------+---------+-----------------------------------------------------+-----------+-----------+------------------+---------------------------------------------------------------------+--------------------------+----------------+------------+-----------+--------------+------------------------------------------------------------------- 2006-12-01 12:00:30.982 | GFTP-pool1-Unknown-8169@gridftp-pool1Domain | request | /C=UK/O=eScience/OU=Edinburgh/L=NeSC/CN=greig cowan | 12000 | 1040 | ui.epcc.ed.ac.uk | door:GFTP-pool1-Unknown-8169@gridftp-pool1Domain:1164974430982-1075 | 000E000000000000001C8BF8 | 1164974430982 | 0 | 0 | | /pnfs/epcc.ed.ac.uk/data/dteam/greig_test_dir/20061201_120029.txt 2006-12-01 12:01:30.458 | GFTP-pool1-Unknown-8170@gridftp-pool1Domain | request | /C=UK/O=eScience/OU=Edinburgh/L=NeSC/CN=greig cowan | 12000 | 1040 | ui.epcc.ed.ac.uk | door:GFTP-pool1-Unknown-8170@gridftp-pool1Domain:1164974490458-1076 | 000E000000000000001C8BF8 | 1164974490458 | 0 | 0 | | /pnfs/epcc.ed.ac.uk/data/dteam/greig_test_dir/20061201_120029.txt 2006-12-01 12:02:45.722 | GFTP-pool1-Unknown-8173@gridftp-pool1Domain | request | /C=UK/O=eScience/OU=Edinburgh/L=NeSC/CN=greig cowan | 12000 | 1040 | ui.epcc.ed.ac.uk | door:GFTP-pool1-Unknown-8173@gridftp-pool1Domain:1164974565722-1078 | 000E000000000000001C8C28 | 1164974565722 | 0 | 0 | | /pnfs/epcc.ed.ac.uk/data/dteam/greig_test_dir/20061201_120244.txt 2006-12-01 12:03:23.906 | GFTP-pool1-Unknown-8174@gridftp-pool1Domain | request | /C=UK/O=eScience/OU=Edinburgh/L=NeSC/CN=greig cowan | 12000 | 1040 | ui.epcc.ed.ac.uk | door:GFTP-pool1-Unknown-8174@gridftp-pool1Domain:1164974603906-1079 | 000E000000000000001C8C28 | 1164974603906 | 0 | 0 | | /pnfs/epcc.ed.ac.uk/data/dteam/greig_test_dir/20061201_120244.txt (4 rows)
FTS managed transfer on a channel set to use urlcopy
FTS can only perform SRM->SRM transfers. 1st line is dCache as destination, second is dCache as source. The information here is the same as that for the 3rd party gridftp transfers in the previous section. In the FTS case the doorinfo.client is the FTS server used to mediate the transfer.
billinginfo
datestamp | cellname | action | transaction | pnfsid | fullsize | transfersize | storageclass | isnew | client | connectiontime | errorcode | errormessage | protocol | initiator -------------------------+----------------------+----------+----------------------------------------------+--------------------------+----------+--------------+------------------+-------+---------------------+----------------+-----------+--------------+----------+--------------------------------------------------------------------- 2006-12-01 12:24:34.479 | pool1_03@pool1Domain | transfer | pool:pool1_03@pool1Domain:1164975874479-5282 | 000E000000000000001C8C58 | 583 | 583 | dteam:STATIC@osm | t | pool2.epcc.ed.ac.uk | 1183 | 0 | | GFtp-1.0 | door:GFTP-pool2-Unknown-8011@gridftp-pool2Domain:1164975874187-1104 2006-12-01 12:56:25.401 | pool1_03@pool1Domain | transfer | pool:pool1_03@pool1Domain:1164977785401-5295 | 000E000000000000001C8C58 | 583 | 583 | dteam:STATIC@osm | f | 194.80.35.14 | 45 | 0 | | GFtp-1.0 | <undefined>
doorinfo
datestamp | cellname | action | owner | mappeduid | mappedgid | client | transaction | pnfsid | connectiontime | queuedtime | errorcode | errormessage | path -------------------------+---------------------------------------------+---------+-----------------------------------------------------+-----------+-----------+------------------+---------------------------------------------------------------------+--------------------------+----------------+------------+-----------+--------------+------------------------------------------------------------------- 2006-12-01 12:24:34.187 | GFTP-pool2-Unknown-8011@gridftp-pool2Domain | request | /C=UK/O=eScience/OU=Edinburgh/L=NeSC/CN=greig cowan | 12000 | 1040 | lcgfts0370.gridpp.rl.ac.uk | door:GFTP-pool2-Unknown-8011@gridftp-pool2Domain:1164975874187-1104 | 000E000000000000001C8C58 | 1164975874187 | 0 | 0 | | /pnfs/epcc.ed.ac.uk/data/dteam/greig_test_dir/20061201_104400-1.txt 2006-12-01 12:56:25.142 | GFTP-pool1-Unknown-8236@gridftp-pool1Domain | request | /C=UK/O=eScience/OU=Edinburgh/L=NeSC/CN=greig cowan | 12000 | 1040 | lcgfts0370.gridpp.rl.ac.uk | door:GFTP-pool1-Unknown-8236@gridftp-pool1Domain:1164977785142-1094 | 000E000000000000001C8C58 | 1164977785142 | 0 | 0 | | /pnfs/epcc.ed.ac.uk/data/dteam/greig_test_dir/20061201_104400-1.txt
How do we get all of this information into a form that can be publihsed into GridView?