1. Objectives
This document is meant to explain how to manually set up and run the system alongside the most trivial Qserv installation known as the "mono-node". Eventually, this recipe may be used to extend the Qserv installation scripts to make the System an integral part of each Qserv deployment.
2. Setting up Qserv
All examples presented in the document are based on an assumption that Qserv is going to be installed into the following folder:
/home/gapon/qserv-run
It's also advised to set the environment variable:
export QSERV_INSTALL_DIR=/home/gapon/qserv-run
Please, change it to suit your specific needs.
Also, the examples mentioned in the document should work equally well from inside the Qserv Docker containers or the bare OS environment. There would be additional steps for launching the Docker containers though.
Get local copies of the qserv and qserv_testdata packages:
mkdir /home/gapon/development cd /home/gapon/development git clone git@github.com:lsst/qserv.git git clone git@github.com:lsst/qserv_testdata.git
Build the latest version of Qserv based on the latest version of the LSST Stack. For example:
cd /home/gapon/development/qserv source /qserv/stack/loadLSST.bash setup -t qserv-dev qserv_distrib setup -r . scons -j6 install
The next step is to deploy the mono-node version of Qserv at the following folder:
export QSERV_INSTALL_DIR=/home/gapon/qserv-run mkdir $QSERV_INSTALL_DIR
The next step is to configure Qserv:
cd /home/gapon/development/qserv source /qserv/stack/loadLSST.bash setup -t qserv-dev qserv_distrib setup -r . cd ../qserv_testdata/ setup -r . -k qserv-configure.py -a -R $QSERV_INSTALL_DIR
If everything goes well then run the integration test:
$QSERV_INSTALL_DIR/bin/qserv-start.sh qserv-test-integration.py
It would take a few minutes for the test to finish.
3. Setting up and starting the Replication System
To make examples in this section shorter, the MySQL command has been replaced with the following environment variable:
export MYSQL_CMD='mysql --protocol=tcp -hlocalhost -P3306 -uroot -pCHANGEME'
Make additional folders required by the catalog Ingest and table Export services of the Replication System:
mkdir $QSERV_INSTALL_DIR/var/lib/ingest/ mkdir $QSERV_INSTALL_DIR/var/lib/export
Check that Qserv is still running (start it if it's not), and then create a database and a MySQL user required by the Replication system:
$MYSQL_CMD -e "CREATE DATABASE qservReplica" $MYSQL_CMD -e "CREATE USER qsreplica@localhost" $MYSQL_CMD -e "GRANT ALL ON qservReplica.* TO qsreplica@localhost"
The database will be storying a persistent state and a live Configuration of the System.
At the next step we're going to modify a unique identifier of the (only) Qserv worker which is present in the "mono-node" setup. This is done just once:
$MYSQL_CMD -e "UPDATE qservw_worker.Id SET id='worker'"
NOTE: by default the worker node's id would be some long auto-generated 64-character UUID. Although the long identifiers would be perfectly useable by the Replication System, it's not quite convenient to see such long identifiers in the log files and among the command line parameters of some applications. Though, one may just stick to the long identifiers. Just be aware that the identifier would have to be used in the Configuration of the System as it will be explained later in the document.
The last operation requires restarting Qserv so that it would recognize and properly use its new identity. Please, do NOT skip this step! Otherwise, the Replication system won't be able to communicate with Qserv worker over the XrootD/SSI protocol.
$QSERV_INSTALL_DIR/bin/qserv-stop.sh $QSERV_INSTALL_DIR/bin/qserv-start.sh
Now we need to load schema into database qservReplica. The schema is found in a local repo of Qserv (IMPORTANT: please, use the latest main branch of the package to get the right version of the schema):
cat /home/gapon/development/qserv/core/modules/replica/sql/replication.sql | $MYSQL_CMD qservReplica
Now it's time to configure the Replication system by populating the tables created above. For the purpose of this setup, one has to execute the following SQL statement in the context of a database qservReplica:
Please, revisit the SQL statements below and find all occurrences of a specific folder referring to this example of installing Qserv /home/gapon/qserv-run. Replace this path with the same value where your instance of Qserv was installed as explained in Section 2 of this document.
Another important aspect of the configuration is related to the port numbers mentioned in the sample configuration below:
- Ports open by the Master Replication Controller
25080- HTTP port for the REST API
- Ports open by the Replication/Ingest workers:
25000- for receiving/processing requests from the Replication Controller25001- for serving local replicas to other workers of a setup (used for actual chunk replication)25002- for uploading user'sTSV/CSVfiles into Qserv tables of a worker during ingests25003- for exporting the content of tables from Qserv workers to a user's local filesystem25004- for uploading user'sTSV/CSVfiles into Qserv tables of a worker during ingests (the "pull" mode via the HTTP protocol)
The default values of the ports (as per the sample configuration) may not work in all environments as some of the ports may already be taken by some other applications. There are many ways to check which ports are already in use in Linux. One would be to use the following utility:
netstat -tl
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 ;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 ;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL' ;
-----------------------------------------------------------
-- Preload configuration parameters for testing purposes --
-----------------------------------------------------------
-- Common parameters of all types of servers
INSERT INTO `config` VALUES ('common', 'request_buf_size_bytes', '131072');
INSERT INTO `config` VALUES ('common', 'request_retry_interval_sec', '5');
-- Controller-specific parameters
INSERT INTO `config` VALUES ('controller', 'num_threads', '2');
INSERT INTO `config` VALUES ('controller', 'http_server_port', '25080');
INSERT INTO `config` VALUES ('controller', 'http_server_threads', '2');
INSERT INTO `config` VALUES ('controller', 'request_timeout_sec', '60');
INSERT INTO `config` VALUES ('controller', 'job_timeout_sec', '60');
INSERT INTO `config` VALUES ('controller', 'job_heartbeat_sec', '0'); -- temporarily disabled
INSERT INTO `config` VALUES ('controller', 'empty_chunks_dir', '/home/gapon/qserv-run/var/lib/qserv');
-- Database service-specific parameters
INSERT INTO `config` VALUES ('database', 'services_pool_size', '2');
INSERT INTO `config` VALUES ('database', 'qserv_master_host', 'localhost');
INSERT INTO `config` VALUES ('database', 'qserv_master_port', '3306');
INSERT INTO `config` VALUES ('database', 'qserv_master_user', 'qsmaster');
INSERT INTO `config` VALUES ('database', 'qserv_master_name', 'qservMeta');
INSERT INTO `config` VALUES ('database', 'qserv_master_services_pool_size', '2');
INSERT INTO `config` VALUES ('database', 'qserv_master_tmp_dir', '/home/gapon/qserv-run/var/lib/ingest');
-- Connection parameters for the Qserv Management Services
INSERT INTO `config` VALUES ('xrootd', 'auto_notify', '1');
INSERT INTO `config` VALUES ('xrootd', 'host', 'localhost');
INSERT INTO `config` VALUES ('xrootd', 'port', '1094');
INSERT INTO `config` VALUES ('xrootd', 'request_timeout_sec', '60');
-- Default parameters for all workers unless overwritten in worker-specific
-- tables
INSERT INTO `config` VALUES ('worker', 'technology', 'FS');
INSERT INTO `config` VALUES ('worker', 'svc_port', '25000');
INSERT INTO `config` VALUES ('worker', 'fs_port', '25001');
INSERT INTO `config` VALUES ('worker', 'num_svc_processing_threads', '2');
INSERT INTO `config` VALUES ('worker', 'num_fs_processing_threads', '2'); -- double compared to the previous one to allow more elasticity
INSERT INTO `config` VALUES ('worker', 'fs_buf_size_bytes', '4194304'); -- 4 MB
INSERT INTO `config` VALUES ('worker', 'data_dir', '/home/gapon/qserv-run/var/lib/mysql');
INSERT INTO `config` VALUES ('worker', 'db_port', '3306');
INSERT INTO `config` VALUES ('worker', 'db_user', 'root');
INSERT INTO `config` VALUES ('worker', 'loader_port', '25002');
INSERT INTO `config` VALUES ('worker', 'loader_tmp_dir', '/home/gapon/qserv-run/var/lib/ingest');
INSERT INTO `config` VALUES ('worker', 'exporter_port', '25003');
INSERT INTO `config` VALUES ('worker', 'exporter_tmp_dir', '/home/gapon/qserv-run/var/lib/export');
INSERT INTO `config` VALUES ('worker', 'http_loader_port', '25004');
INSERT INTO `config` VALUES ('worker', 'http_loader_tmp_dir', '/home/gapon/qserv-run/var/lib/ingest');
INSERT INTO `config` VALUES ('worker', 'num_loader_processing_threads', '2');
INSERT INTO `config` VALUES ('worker', 'num_exporter_processing_threads', '2');
INSERT INTO `config` VALUES ('worker', 'num_http_loader_processing_threads', '2');
-- Preload parameters for runnig all services on the same host
INSERT INTO `config_worker` VALUES ('worker', 1, 0,
'localhost', NULL,
'localhost', NULL, NULL,
'localhost', NULL, NULL,
'localhost', NULL, NULL,
'localhost', NULL, NULL,
'localhost', NULL, NULL
);
-- This is the default database family for LSST production
INSERT INTO `config_database_family` VALUES ('production', 1, 340, 3, 0.01667);
SET SQL_MODE=@OLD_SQL_MODE ;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS ;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS ;
To make tings simple, the above shown configuration commands could be put into some file replication_config_mono.sql before applying them at MySQL:
cat replication_config_mono.sql | $MYSQL_CMD qservReplica
If there was a problem in the configuration then one would simple have to recreate the database by:
$MYSQL_CMD -e "DROP DATABASE qservReplica" $MYSQL_CMD -e "CREATE DATABASE qservReplica" cat /home/gapon/development/qserv/core/modules/replica/sql/replication.sql | $MYSQL_CMD qservReplica
And then fix the local configuration in file replication_config_mono.sql and load a correct version as before:
cat replication_config_mono.sql | $MYSQL_CMD qservReplica
At this point we should be ready to start the Master Replication Controller and Replication Worker processes. Just a few simple steps are left. To make the log files of these two processes more readable it's recommended to create the following configuration file for the LSST Logger and pass it to the applications via the corresponding environment variable as shown below:
% cat $PWD/log4cxx.replication.properties
log4j.rootLogger=ERROR, CONSOLE
log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
log4j.appender.CONSOLE.layout.ConversionPattern=%d{yyyy-MM-ddTHH:mm:ss.SSSZ} LWP %-5X{LWP} %-5p %m%n
log4j.logger.lsst.qserv.replica=DEBUG
% export LSST_LOG_CONFIG=$PWD/log4cxx.replication.properties
The last required step is to make the following environment variable which would be required by all Replication System's applications (servers) and tools. The variable stores a connection string to the configuration of the System. All processes would need it to know how to bootstrap:
export CONFIG="mysql://qsreplica@localhost:3306/qservReplica"
Now we're ready to start two servers of the System:
% qserv-replica-master-http \ --debug --config=$CONFIG --instance-id=mono --qserv-db-password=CHANGEME \ >& $QSERV_INSTALL_DIR/var/log/qserv-replica-master-http.log& % qserv-replica-worker "worker" \ --debug --config=$CONFIG --instance-id=mono --qserv-db-password=CHANGEME \ >& $QSERV_INSTALL_DIR/var/log/qserv-replica-worker.log&
Please, make sure neither of the processes exits immediately after it's started. The most typical reason why the services may fail to start would be if they couldn't allocate ports required by the above-presented configuration. Inspect the log files for a presentation of the following error at the very end of the log files:
... 2020-07-20T09:09:46.868-0700 LWP 3796984 DEBUG SERVICE-PROVIDER run main() the application failed, exception: bind: Address already in use
If that would be the case then revisit instructions for configuring the Replication/Ingest system, fix the configuration, and start the process of installing the system over.
4. Testing the System and learning how it works for ingesting a small catalog
This section is not nearly a substitute for the complete The workflow developer's guide for the Qserv Ingest system. To learn about the Ingest System one has to read that document. The document also contains the reference info for all REST API services which are needed/used by the ingest workflow. Please, read it if you would run into problems with Web services, or to understand results reported by the services.
In this test a tiny catalog will be ingested into the system. The catalog's name will be test101. It will have just one partitioned (which is also the director) table Object. And there will be just one chunk number 0. The partitioning parameters of the table will be:
- num-stripes: 340
- num-sub-stripes: 3
- overlap: 0.01667
Databases partitioned this way automatically join the production family of databases preloaded earlier into the Configuration of the system. Though, one is free to use different parameters. Those above were used just as an example.
So, the first step is to check if the REST API of the System is up and responding to requests. The simplest way of doing it would be by checking a version of the REST API:
% curl 'http://localhost:25080/meta/version' -X GET -H "Content-Type: application/json"
{"error":"","error_ext":{},"success":1,"version":1}
If the result is like shown above then everything is fine, and (at least) the REST server is running.
The first actual step in ingesting the test catalog would be to tell the Ingest system about the new database which is going to be ingested. Put the JSON configuration of the database into the file test101.json:
{"database":"test101",
"num_stripes":340,
"num_sub_stripes":3,
"overlap":0.01667,
"auto_build_secondary_index":1,
"local_load_secondary_index": 1,
"auth_key":""
}
And then invoking the following service:
curl http://localhost:25080/ingest/database -X POST -H "Content-Type: application/json" -d@test101.json
Check the status JSON object returned by the operation to ensure status=1.
The next step is to register the table (Object) that is going to be ingested. Make another JSON file with a description of the table test101_Object.json:
{"database":"test101",
"table":"Object",
"is_partitioned":1,
"chunk_id_key":"chunkId",
"sub_chunk_id_key":"subChunkId",
"is_director":1,
"director_key":"objectId",
"latitude_key":"dec",
"longitude_key":"ra",
"schema":[
{"name":"objectId","type":"BIGINT NOT NULL"},
{"name":"ra","type":"DOUBLE NOT NULL"},
{"name":"dec","type":"DOUBLE NOT NULL"},
{"name":"property","type":"DOUBLE"},
{"name":"chunkId","type":"INT UNSIGNED NOT NULL"},
{"name":"subChunkId","type":"INT UNSIGNED NOT NULL"}
],
"auth_key":""
}
Then pass this file to this service:
curl http://localhost:25080/ingest/table -X POST -H "Content-Type: application/json" -d@test101_Object.json
Likewise the previous operation, the service is supposed to return status=1 to indicate the success of the operation.
Now its time to begin the first so-called super-transaction by invoking this service:
curl http://localhost:25080/ingest/trans -X POST -H "Content-Type: application/json" -d'{"database":"test101","auth_key":""}'
The most important result of the operation would be a unique identifier of the super-transaction. It's in the id attribute of a JSON object returned by the operation. For the very first transaction, the identifier would be set to 1. Then it would get incremented for any subsequent transactions started by the service. We need to memorize this identifier as it would be used by some other ingest operations to be mentioned later.
The next step is to ask the System where it would like to place the chunk number 0. This is done by (note an identifier of the transaction in the request):
curl http://localhost:25080/ingest/chunk -X POST -H "Content-Type: application/json" -d'{"transaction_id":1,"chunk":0,"auth_key":""}'
The result of this call would be another JSON object of which we're interested in the: worker's host and port attributes. In the "mono-node" setup explained in the document these would always be:
host=localhost- port=25002
Now, let's make a one-row (object) contribution into the fake chunk number 0 by putting just(!) one line into the file chunk_0.txt:
1,0.1,-89.5,123.456,0,1
One may also want to double-check that an object with ra=0.1 and dec=-89.5 really falls within boundaries of the partition 0. There is a tool that prints a layout of the partition in the spherical geometry:
% sph-layout --part.num-stripes=340 --part.num-sub-stripes=3 0
| RA [degree] | DECL [degree] | |
id |--------------+--------------+--------------+--------------| Area [sr] | Worker
| Min | Max | Min | Max | |
-------+--------------+--------------+--------------+--------------+--------------+------------
0 | 0.000 | 360.000 | -90.000 | -89.471 | 0.000268 |
To load the chunk into the catalog one has to use the following ready-to-use command-line tool which is built earlier when building Qserv:
qserv-replica-file INGEST FILE localhost 25002 1 Object P chunk_0.txt --auth_key=<key> --verbose
COMMENTS: see description of the command line parameters of the tool at The workflow developer's guide for the Qserv Ingest system#qserv-replica-file-ingest
if the optional --verbose the option is specified one should also see some summary stats on the operation.
The next steps would be to commit the transaction and publish the database:
curl 'http://localhost:25080/ingest/trans/1?abort=0' -X PUT -H "Content-Type: application/json" -d '{"auth_key":""}'
curl 'http://localhost:25080/ingest/database/test101' -X PUT -H "Content-Type: application/json" -d '{"auth_key":""}'
If all successful, and before testing results, one would have to make another restart of Qserv by:
$QSERV_INSTALL_DIR/bin/qserv-stop.sh $QSERV_INSTALL_DIR/bin/qserv-start.sh
This unfortunate step is needed because of some problem in the xrootd implementation, or in the current implementation of the Replication-to-Qserv integration which prevents Qserv from immediately seeing newly published catalogs.
4.1. Deleting the database and starting ingest from scratch
In cases (when "something went wrong with the ingest effort" and) when it's not clear at which state a database is, one may consider restarting the ingest from scratch. The easiest (and the first) way of doing this would by invoking the following REST service:
curl 'http://localhost:25080/ingest/database/test101?delete_secondary_index=1' \
-X DELETE -H "Content-Type: application/json" \
-d '{"auth_key":""}'
If that wouldn't work then there is the second (low-level) solution, which requires two steps. The first step would be to connect to the MySQL server and launch the following queries:
DELETE FROM qservReplica.config_database WHERE `database`='test101' DROP DATABASE IF EXISTS test101
After that one has to locate, stop, and restart both services which were started earlier:
qserv-replica-master-http qserv-replica-worker
It would also be a good idea to restart Qserv:
$QSERV_INSTALL_DIR/bin/qserv-stop.sh $QSERV_INSTALL_DIR/bin/qserv-start.sh
4.2. Testing results
Just launch one of those queries via Qserv's mysql-proxy port 4040:
% mysql --protocol=tcp -P4040 -uqsmaster -e 'SELECT * FROM test101.Object' +----------------+----------+------+-------+----------+---------+------------+ | qserv_trans_id | objectId | ra | dec | property | chunkId | subChunkId | +----------------+----------+------+-------+----------+---------+------------+ | 1 | 1 | 0.1 | -89.5 | 123.456 | 0 | 1 | +----------------+----------+------+-------+----------+---------+------------+