dcache (billing): rewrite triggers to eliminate multiple repeated updates

This is the first in a series of patches which seek to improve the billing insert performance.

The issue was brought forward by U. Michigan (Tier 2) when their 2.2 installation began to experience OOM in the billing domain.  After repeated attempts at diagnosis, with provisional patches deployed and observed using jConsole and their logs rsync'd to a web page, it was finally discovered that the root cause of their problem was the fact that the triggers attached to the main (fine-grained) billing tables, which derive aggregate data to be inserted into a subset of 24-hour tables, were being defeated by the way PostgreSQL implements M.V.C.C.  In particular, each UPDATE is actually a creation of a new row (rather than a change to the existing one). The old row still exists and has an older transaction id associated with it. If these old rows are not cleaned by autovacuum, then eventually we run out of transaction ids and a full vacuum is required, locking up a table completely.  HOT (Heap Only Tuples), which allows in-place updates, does so only for rows that do not contain unindexed columns.

In order to avoid multiple frequent updates, the triggers have been rewritten to check for the maximum dated entry and only insert a new row if the current time has exceeded 24 hours since that time.  The single operation is slightly more expensive, because it aggregates from the maximum time to the present, adding the appropriate missing rows, but our measurements for aggregation over 50M rows+ was approximately 1 minute, which we observed under continuous load was sustainable. In the case of a first entry into an empty aggregate table, the backward aggregation is limited to a 24-hour period.

Two notes are in order.  First, there are no doorinfo triggers, and it is only there that the datestamp frequently reflects the beginning of an operation greater than 24 hours prior to its being registered (since the request could be cached).  The storage and billing data does not usually reflect such long periods (we queried our production systems and found very few instances where the actual mover timestamp suggested a transfer lasting greater than 24 hours, and all of these were from more than two or three years in the past).  The reason that this matters is that once the new tuple/entry is aggregated, it never gets updated after that.  Given the very low frequency of these occurrences, the statistical error of the aggregated value is negligible.  If it becomes desirable to catch these cases, there are a couple of potential remedies:  increase the "window", so that we aggregate only after a longer period (say 48 vs. the current 24 hours), or we simply do an update operation on these outliers (which is slightly more costly), perhaps using a separate trigger.

Second, the triggers are not DRY.  The problem, however, is that to achieve reuse for anything more than the simple alternative values of a parameter on a given table, the function would need to be parameterized against tables and column names, requiring dynamic SQL which would then be executed, defeating the purpose -- which is to make the trigger as efficient as possible.

Finally, we have directly altered the hsqldb liquibase changesets, since the database is recreated when system-tests is rebuilt.

It should also be noted that one field on the billinginfo_tm_daily table has been altered (name changed from totaltime to average) in order to store the average rather than computing it in memory.  The changeset provides the update logic, which should not be extremely costly as the number of rows in the table will be no more than 366 per year of data taken.

A separate patch for version 2.2 will be posted.

Testing:

Built system test and ran. Deployed rpm, ran stress tests (plain SQL and full billing message passing), observing performance and queues.

Further discussion at

https://srm.fnal.gov/twiki/bin/view/DcacheCorner/BillingDatabase#PERFORMANCE_OBSERVATIONS_AND_ISS;

see in particular the final performance graphs:

https://srm.fnal.gov/twiki/bin/view/DcacheCorner/BillingDatabase#Modified_triggers

UMichigan Atlas T2 deployed the triggers on Septeber 5th and have not experienced an OOM so far:

http://head01.aglt2.org:2288/

Target: master
Request: 2.7
Request: 2.6
Patch: http://rb.dcache.org/r/5983/
Bug: http://rt.dcache.org/Ticket/Display.html?id=7915
Require-book: no
Require-notes: yes
Acked-by: Dmitry

RELEASE NOTES:

Fixes a problem with the billing database triggers which under continuous heavy loads (> 300 Hz of billing messages) led eventually to an Out-Of-Memory error in the billing domain.
webadmin: eliminate system-specific dir for unpacking of war file

The directory where the webapp war should be unpacked is currently set to /var/tmp.  This location, however, is susceptible on some systems to periodic deletion, in which case the web application fails to be found by the server.

For this version, we have simply switched this location to /var/lib/dcache/httpd.

Separate patch for 2.6 will be posted.

Testing:

Built rpm and ran instance, checking for presence of webapp directory in /var/lib/dcache/httpd, and for sanity of webadmin pages.

Target: master
Request: 2.7
Patch: http://rb.dcache.org/r/6002
Require-book: no
Require-notes: yes
Bug: http://rt.dcache.org/Ticket/Display.html?id=7979
Acked-by: Paul

RELEASE NOTES:

Fixes bug where on some systems automatic cleanup of temporary directories was eliminating the webapp application files and thus causing the webadmin pages to fail to be found.
srmclient: Fix failure detection

Addresses an issue in failure reporting for failed SRMv1 requests.

SRM v1 on master is currently broken. Our srmclient however provides a secondary
and non-informative failure:

srmcp -1 file:////bin/bash srm://localhost:8443/disk/test-1379612127-1/a
Thu Sep 19 19:35:50 CEST 2013: incorrect number of RequestFileStatusesin RequestStatus expected 1 received 0
Thu Sep 19 19:35:50 CEST 2013: java.io.IOException: incorrect number of RequestFileStatusesin RequestStatus expected 1 received 0
srm copy of at least one file failed or not completed

This is caused by the SRM client checking the number of file-level status
entries before it checks the status of the response itself. This patch resolves
the issue. The client now produces:

srmcp -1 file:////bin/bash srm://localhost:8443/disk/test-1379612984-1/a
Thu Sep 19 19:50:04 CEST 2013: rs.state = Failed rs.error = getRequestStatus(): request #-2147483645 owned by [GidPrincipal[1000,primary], UidPrincipal[1000], S:/127.0.0.1, UserNamePrincipal[behrmann], /O=Grid/O=NorduGrid/OU=nordu.net/CN=Gerd Behrmann] read-write / does not belong to user [GidPrincipal[1000,primary], UidPrincipal[1000], S:/127.0.0.1, UserNamePrincipal[behrmann], /O=Grid/O=NorduGrid/OU=nordu.net/CN=Gerd Behrmann] read-write /
Thu Sep 19 19:50:04 CEST 2013: java.io.IOException: rs.state = Failed rs.error = getRequestStatus(): request #-2147483645 owned by [GidPrincipal[1000,primary], UidPrincipal[1000], S:/127.0.0.1, UserNamePrincipal[behrmann], /O=Grid/O=NorduGrid/OU=nordu.net/CN=Gerd Behrmann] read-write / does not belong to user [GidPrincipal[1000,primary], UidPrincipal[1000], S:/127.0.0.1, UserNamePrincipal[behrmann], /O=Grid/O=NorduGrid/OU=nordu.net/CN=Gerd Behrmann] read-write /
srm copy of at least one file failed or not completed

I am aware of the double logging (caused by logging and throwing an
exception), however that's a different problem and not addressed by
this patch.

Target: trunk
Require-notes: yes
Require-book: no
Acked-by: Paul Millar <paul.millar@desy.de>
Acked-by: Dmitry Litvintsev <litvinse@fnal.gov>
Patch: http://rb.dcache.org/r/6019/
srm-client: Added missing srm-abort-files options in help text

Target: trunk
Require-notes: yes
Require-book: no
Acked-by: Paul Millar <paul.millar@desy.de>
Patch: http://rb.dcache.org/r/6028/
srm: Introduce srm-common module and rename existing srm modules

Addresses bloat in the srmclient packages.

srmclient relies on srm for protocol and utility classes. The dependency
causes the srmclient packages to include lots of transitive dependencies
not actually needed by the client. Those dependencies increase the size
of the RPM and increase the startup time of the clients as the classpath
is longer.

This patch moves those classes to a new srm-common module. The srmclient
module is renamed to srm-client and the srm module to srm-server; this
follows the naming scheme used by other modules.

The patch also restricts the scope of some powermock components to
testing. This avoid that these are included in the final product.

The effect of the pathc is that the RPM shrinks from a bit above 16 MB
to sligthly below 8 MB.

The patch removes a few old build files and build instructions that
no longer apply.

Target: trunk
Require-notes: yes
Require-book: no
Acked-by: Paul Millar <paul.millar@desy.de>
Patch: http://rb.dcache.org/r/6024/
Upgrade BoneCP and Guava

BoneCP and Guava have to be upgraded together as the new BoneCP uses the new
Guava, and the new Guava is incompatible with the old BoneCP.

BoneCP 0.7 contains a bug in which it doesn't restore the auto-commit flag
on recycled connections. We work arround that problem in our code by carefully
resetting the flag before 'closing' the connection, however that only works
as long as you don't mix your code with eg DataNucleus. DataNucleus changes
the auto commit flag and thus you run into problems if your own code relies
on connections haven't auto commit enabled by default (as required by the
JDBC spec).

The problem is fixed in BoneCP 0.8, although that is only available in a release
candidate. rc3 has however been adopted by the Play project, which I consider
a high profile endorsement.

0.8-rc3 contains a bug in that the default auto-commit value is false rather
than true as required by the standard. I add a custom bonecp-config.xml to
work around that problem. The config also enables logging of slow SQL statements,
using a conservative 10 second threshold (this is the only thing worth
mentioning in the release notes).

The BoneCP setReleaseHelperThreads method is deprecated in BoneCP 0.8 and I
removed all calls to it.

Guava removes the Ranges class, which means a number of classes had to be
updated to compensate.

Target: trunk
Require-notes: yes
Require-book: no
Acked-by: Karsten Schwank <karsten.schwank@desy.de>
Patch: http://rb.dcache.org/dashboard/
libs: prevent JNA jar be pulled with NFS code

Ticket: #8020
Acked-by: Paul Millar
Target: master, 2.6, 2.7