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.
Researchers can be PM Cell managers
Fixed: Researchers can have the PM Cell "manager" role and still be able to run queries.
'shouldQuerySelf' Parameter Deprecated
Fixed: If you run a shrine adapter on the same shrine node as the shrine hub, list it as a downstream node instead of using 'shouldQuerySelf = true'. shouldQuerySelf has serious concurrency bugs and will be removed in a future release.
If you are not using port 6443
You will need to override a URL in shrine.conf to see progress and results from QUEUED queries in the SHRINE webclient:
shrine { queryEntryPoint { queuedQueryPollUrl = "https://$fqdn:6443/shrine/rest/i2b2/request" // Change to your port number if not 6443 } }
SHRINE Proxy Security Fix
Fixed: A potential security vulnerability within the SHRINE proxy subsystem
To Fix: Please refer to the instructions here: SHRINE 1.25.4 and Earlier Fix for shrine-proxy.war Exploit
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/