New Features

• Scale up to ACT scales - A burst of 60 active queries on 60 nodes.
• Scalable bar graph display for query breakdown results.
• Support for client-side HTTPS proxies.
• Improved support for 'QUEUED' queries.

Notes on Installation

PM Cell's Database Bug

Fixed: Bug in the i2b2 PM Cell's database where an unneeded compound primary key limits the PM cell to only processing one authentication attempt per second.

To Fix:

bash-4.1$ psql
postgres=# \connect i2b2pm
i2b2pm=# \d+ pm_user_login
                           Table "public.pm_user_login"
    Column     |            Type             | Modifiers | Storage  | Description 
---------------+-----------------------------+-----------+----------+-------------
 user_id       | character varying(50)       | not null  | extended | 
 attempt_cd    | character varying(50)       | not null  | extended | 
 entry_date    | timestamp without time zone | not null  | plain    | 
 changeby_char | character varying(50)       |           | extended | 
 status_cd     | character varying(50)       |           | extended | 
Indexes:
    "pm_user_login_pkey" PRIMARY KEY, btree (entry_date, user_id)
Has OIDs: no

i2b2pm=# ALTER TABLE "pm_user_login" DROP CONSTRAINT "pm_user_login_pkey" ;
ALTER TABLE

i2b2pm=# \d+ pm_user_login
                           Table "public.pm_user_login"
    Column     |            Type             | Modifiers | Storage  | Description 
---------------+-----------------------------+-----------+----------+-------------
 user_id       | character varying(50)       | not null  | extended | 
 attempt_cd    | character varying(50)       | not null  | extended | 
 entry_date    | timestamp without time zone | not null  | plain    | 
 changeby_char | character varying(50)       |           | extended | 
 status_cd     | character varying(50)       |           | extended | 
Has OIDs: no

Allow for More Status Types from the i2b2 CRC

Fixed: Changed the shrine_query_history database's QUERY_RESULT table status column's type from an 'enum' to a 'text' field to accommodate more types from the i2b2 CRC. This will allow some results previously interpreted as errors to be interpreted as QUEUED. See SHRINE-2715 for more information.

To Fix:

For Mysql:

mysql> use shrine_query_history;
mysql> alter table QUERY_RESULT change status status varchar(30) not null;

For Oracle:

-- To find the constraint name on the status column:
SELECT COLUMN_NAME,CONSTRAINT_NAME,TABLE_NAME FROM user_cons_columns WHERE TABLE_NAME = 'QUERY_RESULT'; 
-- To drop that constraint:
ALTER TABLE QUERY_RESULT DROP CONSTRAINT <constraint_name_for_status>;

For MS SQL Server:

USE shrine_query_history;
 
-- To find the constraint name on the status column:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME= 'QUERY_RESULT';

-- To drop that constraint:

ALTER TABLE QUERY_RESULT DROP CONSTRAINT <constraint_name_for_status>;

Separate URL for DSA Email Links

Fixed: To support server-side proxied shrine nodes, the URL for reaching the DSA from an emailed link need not be the same as the one the QEP uses. See SHRINE-2504 (Contributed by Erik Benton at OHSU ) for more information.

To Fix: Add the parameter 'externalStewardBaseUrl = email_address" to the emailDataSteward section within the steward block in shrine.conf .

Support for client-side HTTPS proxies

Added: Added Java system properties to support a client-side HTTPS proxy.

export CATALINA_OPTS="-Dhttp.proxyHost=yourProxyHost -Dhttp.proxyPort=yourProxyHostPort"

Cleanup of context.xml

Fixed: The context.xml file in the past has gotten cluttered and was the source for many database-related issues when installing or configuring SHRINE. Our repo https://repo.open.med.harvard.edu/nexus/content/groups/public/net/shrine/shrine-setup/ now contains a .zip file with many example files to help set up a node. One of those is an up-to-date context.xml . Adopting this as a base will clean up many mild database issues and provide enough connections to support scaling up to ACT. In particular, we recommend the qepAuditDB database should have a maxTotal of 512 connections.

Full schemas for Oracle and MS SQL Server databases

Added: We have now added full schemas for the SHRINE database setups for both Oracle and MS SQL Server hosted on our Nexus repo: (https://repo.open.med.harvard.edu/nexus/content/groups/public/net/shrine/shrine-setup/) 

Cleanup of shrine.conf file

Fixed: Shrine's configuration now makes extensive use of default values. The configuration file you need to use is remarkably simpler. Create a backup copy of your current shrine.conf, then use qep-and-adapter-shrine.conf (or hub-and-qep-shrine.conf if you are setting up a hub) from https://repo.open.med.harvard.edu/nexus/content/groups/public/net/shrine/shrine-setup/ as a starting point. 

shrine {
  queryEntryPoint {
    queuedQueryPollUrl = "https://$fqdn:6443/shrine/rest/i2b2/request" // Change to your port number if not 6443
  }
}

ACT Scaling Notes

Add Indexes to Database Tables

See the schemas in https://repo.open.med.harvard.edu/nexus/content/groups/public/net/shrine/shrine-setup/ for many indexes to add to existing databases to improve performance.

Supporting More Database Connections

MySQL out-of-the-box can only support 151 connections. Other database brands can have similar limits. We performed our scale-up tests on MySQLwith a maximum of 4000 connections. 

Memory Handling for SHRINE

In /opt/shrine/tomcat/bin/setenv.sh , include standard server ergonomics and increase the memory available to at least 2 gigabytes with export CATALINA_OPTS="-server -Xmx2G" .

Core Handling for the i2b2 CRC

While testing for scale-up, we discovered a critical, linear limit within the i2b2 CRC. The CRC can support about 15 concurrent queries per core. To support 60 concurrent queries (our goal load in ACT) the CRC needs 4 cores or more.

More Connections and Database Tuning for the i2b2 CRC

While testing for scale-up, we also discovered limits related to the number of allowed connections into i2b2, and the number of connections out to various i2b2 databases.

We added database connection pools of up to 200 connections for i2b2's CRCBootStrapDS and QueryToolDemoDS database, and 300 connections for i2b2's PMBootStrapDS database.

Inside of /opt/wildfly-10.0.0.Final/standalone/deployments/i2b2.war/WEB-INF/conf/axis2.xml we adjusted the transport.http.max_connections_per_host from 2 to 400 for both HTTP and HTTPS connections.

ACT Scaling on the Hub

Increased the shrine User Thread Limit to 8192 or more

Increase the shrine user's thread limit from 1024 to 8192 via > ulimit -u 8192 .

Increased the Connections Available to shrineDB

In context.xml, increase the number of connections available to shrineDB to 1028.

Fixed Missing Sequence in Oracle Schema

If the hub is using Oracle, add a new sequence for the HUB_QUERY_RESULT table's IDs, HUB_QUERY_RESULT_ID. Find the .ddl in  https://repo.open.med.harvard.edu/nexus/content/groups/public/net/shrine/shrine-setup/