Software

• CentOS, RedHat, Debian or Ubuntu operating system x86_64 version.

Find information for cases of other linux systems

• postgreSQL 9.4 relational database (preferred download page) for JPC versions up to 2.0

• postgreSQL 9.5 relational database for JPC versions starting from 2.1

See help to upgrade of postgreSQL 9.4 to postgreSQL 9.5

• postgreSQL 10 relational database for JPC versions starting from 3.0

• availability of hstore extension of postgresql (e.g., contrib package)

• Java8 runtime environment

Hardware

The memory need of JChem PostgreSQL Cartridge strongly depends on the format of the chemical structures to be stored. In the next table, the approximate memory need of 10 M PubChem molecules in SD file format and in SMILES format are compared. The presented memory setup is optimized for execution speed. Lower amount of memory may be used, in this case performance drop is expected. Further details can be found in the Performance tuning section.

Install PostgreSQL Cartridge

Download the latest version of PostgreSQL Cartridge from here. In order to install the latest version (as root), change the x.y (version information) in the following statement to the current one:

In CentOS:

In RedHat or Debian:

See also Getting started guide for PostgreSQL and Java8 installation.

Before the first use

1. Install your license file

   Copy a valid ChemAxon license to /etc/chemaxon/license.cxl or set its location in /etc/chemaxon/jchem-psql.conf file. Required license in the license file is 'Postgres Cartridge'.
   The jchem-psql user should have read access to the license file.

2. Initialize index directory

   sudo service jchem-psql init

   If you encounter any problem in your java installation, set your JAVA_HOME in the file /etc/default/jchem-psql.

   Initialization does not start the service, you must start it manually.

3. First start

   sudo service jchem-psql manual-start

Create extension in a database

Each chemical database should have the following extensions created separately.

1. Create a new user and database

   sudo su postgres

   createuser testuser

   createdb testdb -O testuser

2. Install the extensions (you must be postgres user or any other user who has create extension privilege)

   psql testdb

   testdb> CREATE EXTENSION chemaxon_type;

   testdb> CREATE EXTENSION hstore;

   testdb> CREATE EXTENSION chemaxon_framework;

   If you experience any error during the CREATE EXTENSION steps, please check this page which gives hints relating non-standard PostgreSQL setup.

3. Checking JChem PostgreSQL Cartridge installation

   If jchem-psql service is running, the following query can be executed without any problem:

   testdb> SELECT 'C'::Molecule('sample') |<| 'CC'::Molecule;

   This select statement executes a substructure search, with a Carbon atom as the query and ethane as the target structure. You must receive true (t) as output. See details of Molecule type below.

Service

The service can be started/stopped using:

ENABLED=1 (default) setting in /etc/default/jchem-psql file makes the service start on system boot and start manually by start.

If you want the service not to start on system boot, set ENABLED=0 in the /etc/default/jchem-psql file. In that case, manual-start must be run.

Configure JChem-psql server

The following configuration parameters are available on PostgreSQL side:

They - with the exception of shared_buffers - can be edited as an ordinary Postgres configuration parameter:

• in the postgresql.conf file (e.g., /etc/postgresql/9.4/main/postgresql.conf)

• can be overwritten in the current session:

  SET chemaxon.hit_retrieval_batch_size to 1000;

• to see the current value:

  SHOW chemaxon.hit_retrieval_batch_size;

The following configuration parameters are available on JChem-psql server side:

These parameters can be configured in the /etc/chemaxon/jchem-psql.conf file.

Changing these parameters except maxOpenSessionCount requires new initialization of jchem-psql service. Please note that this will purge all jchem-psql index data hence the indexes need to be dropped and recreated again.

Upgrade with keeping existing molecule type data

If not only JChem PostgreSQL Cartridge, but postgreSQL database upgrade is also needed ( e. g., from JPC 2.1 postgreSQL 9.5 is required), find an example of how to upgrade of postgreSQL 9.4 to postgreSQL 9.5.

1. Stop the service

   sudo service jchem-psql stop

2. Install the new version

3. Initialize the service (this step deletes the old indexes)

   sudo service jchem-psql init

   Only the index contents are deleted, the molecule records stored in the PostgreSQL database stay untouched.

4. Start the service

   sudo service jchem-psql manual-start

5. Upgrade your existing postgresql database

   1. Update the extensions

      sudo su postgres

      psql

       

      # ALTER EXTENSION chemaxon_type UPDATE;

      # ALTER EXTENSION chemaxon_framework UPDATE;

      In some cases of the upgrade process, you have to drop the indexes before altering the chemaxon_framework extension. If you receive an error message like

      "ERROR: cannot drop operator class chemindex_int_ops for access method chemindex because other objects depend on it

      DETAIL: index upg_ind depends on operator class chemindex_int_ops for access method chemindex

      HINT: Use DROP ... CASCADE to drop the dependent objects too.",

      you have to drop the index referred in the DETAIL part of the message and repeat step

      # ALTER EXTENSION chemaxon_framework UPDATE;

   2. Reindex the database

      Upgrade to 2.7 or above

      When you upgrade to JPC 2.7 or above and want to take the advantage of performance improvements implemented in 2.7, you have to update the tables storing chemical structures. The indexes should be dropped before the upgrade and recreated thereafter.

      update table_name set structure_column_name = structure_column_name::text::molecule('molecule_type_name');

      Example:

      drop index myindex;

      update mytable set mol = mol::text::molecule('sample');

      create index myindex on mytable using chemindex(mol);

      //or

      create index myindex on mytable using sortedchemindex(mol);

      If the above table update is skipped, reindex database is necessary:

      psql

      # reindex database <database name>;

      If you miss this step, all indexes of type chemindex will not function properly.

Upgrade without keeping existing molecule type data

If not only JChem PostgreSQL Cartridge, but postgreSQL database upgrade is also needed ( e. g., from JPC 2.1 postgreSQL 9.5 is required), find an example of how to upgrade of postgreSQL 9.4 to postgreSQL 9.5.

1. Before installing the new version, drop the following extensions

   DROP EXTENSION chemaxon_framework CASCADE;

   DROP EXTENSION chemaxon_type CASCADE;

   If you drop the chemaxon_type extension, it will implicitly drop all chemical columns.

2. Stop the service

   sudo service jchem-psql stop

3. Delete the content of the index directory

   sudo rm -rf /var/lib/jchem-psql/store/*

4. Install the new version

Cache sizes

The table below shows the cache memory needs of the PostgreSQL Cartridge when a chemical index of 1 million structures is in use. Memory size values are represented in megabytes. The first value in each cell shows the size of cache memory required by the corresponding backend for every 1 million of stored chemical structures. Values in parentheses represent the same memory need in the case when not only duplicate, full fragment, substructure, similarity search but also superstructure search is to be carried out. In this case an increased amount of cache is necessary.

Values in square brackets depend on the number of chemical indexes present in database. Number of chemical indexes is denoted by ‘idx’. These values do not scale with number of structures but depend only on the number of chemical indexes.

For example, in case of 10 million structures when a single sorted chemindex is used with 1024 bits fingerprint, fingerprint cache requires 10 * 132 MB + 88 MB = 1408 MB.

Required size of molecule cache depends significantly on the molecule set stored. In general larger structures need more space, and smiles format is more compact than mrv or mol format. Cache sizes in the table are measured using the PubChem database in mol format.

Java VM heap memory setup

Maximum memory allowed for the Java VM has to be configured, as well. The amount of required memory can be calculated as:

MaxMem = 2500 MB + 1.33 * Cache size (fingerprint + molecule + label + auxiliary caches)

Maximal VM memory can be configured in file /etc/default/jchem-psql

The setting ‘-Xmx’ in entry ‘JCHEM_PSQL_OPTS’ has to be modified.

PostgreSQL settings

If the user would like to execute queries

1. on big tables - containing many entries and big row data. E.g., the molecule source is a verbose one (sdf, mrv, ...)

2. and PostgreSQL needs to fetch majority of the rows. E.g., sql query without any additional restricting condition or limit parameter

then it is advisable to increase the size of PostgreSQL shared buffer so that PostgreSQL can fetch all rows rapidly using cache. In order to achieve this, set the shared_buffers parameter in postgresql.conf to be able to store your table.

Table size can be checked by \dt+ <tablename> command from the psql client.

Additionally, to enhance PostgreSQL performance, it may be advisable to increase linux's shared memory with the following command:

The default value of page size is usually 4096. It can be checked by getconf PAGESIZE.To store the value of kernel.shmall permanently, add it it to the sysctl.conf file. For more details about required shared memory settings for PostgreSQL server please visit the PostgreSQL documentation.

To reduce the needed buffer size, you may consider the following possibilities.

1. Change the input format to a concise one: smiles, smarts, ...
   (e.g., 8M rows of a PubChem dataset need ca. 17 GB when the molecules are in sdf format, but only ca. 1.4 GB when they are in smiles format).

2. Avoid queries that require the fetching of all table data through adding additional "where" condition or limit parameter.

   For example, instead of

   SELECT <id_column_name> FROM <table_name> WHERE 'c1ccccc1' |<| mol;

   which may return several millions of hits, use the following statement applying LIMIT <n> and relevance sorting in order to obtain the most relevant n hits:

   SELECT <id_column_name> FROM <table_name> WHERE 'c1ccccc1' |<| mol ORDER BY relevance LIMIT 100;

3. Increase the fillfactor to 90% instead of the above recommended 50% and perform vacuum regularly.

4. As PostgreSQL is not optimized for the COUNT() method, it may take a long time if it returns large value. For obtaining an estimation of the count of hits we rather suggest using the EXPLAIN command.

Please also check that the JVM heap size plus the PostgreSQL shared buffer size is not more than 2/3-rd of the total available memory to avoid slow-down of the operation system.

If the required amount of physical memory is not available, then for optimizing the performance it is more important to setup proper JVM heap size than to increase the PostgreSQL shared buffer size.