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/

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.

Target: 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
Merge pull request #189 from alrossi/fix/2.6/billing_triggers

Fix/2.6/billing triggers