View source for 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.

= 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?

[[Category: DCache]]