Billing Database
From GridPP Wiki
Revision as of 13:35, 1 December 2006 by Greig cowan (Talk | contribs)
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?
