Background Deploy a single-node Exasol database as a Docker image for testing purposes Blog snapshot This blog will show you: How to deploy a single-node Exasol database as a Docker image for testing purposes Before we go into the step-by-step guide, please read through the following prerequisites and recommendations to make sure that you're prepared Prerequisites Host OS: Currently, Exasol only supports Docker on Linux. It’s not possible to use Docker for Windows to deploy the Exasol database. The requirement for Linux OS is O_DIRECT access. Docker installed Linux machine: In this article, I’m going to use Centos 7.6 virtual machine with the latest version of docker (currently Version 19.03). Privileged mode: Docker privileged mode is required for permissions management, UDF support, and environment configuration and validation (sysctl, hugepages, block-devices, etc.). Memory requirements for the host environment: Each database instance needs at least 2 GiB RAM. Exasol recommends that the host reserves at least 4 GiB RAM for each running Exasol container. Since in this article I’m going to deploy a single node container I will use 6 GiB RAM for VM. Service requirements for the host environment: NTP should be configured on the host OS. Also, the RNG daemon must be running to provide enough entropy for the Exasol services in the container. Recommendations Performance optimization: Exasol strongly recommends setting the CPU governor on the host to performance, to avoid serious performance problems. You can use the cpupower utility or the command below to set it. Using cpupower utility     $ sudo cpupower -c all frequency-set -g powersave     Change the content of scaling_governor files:     $ for F in /sys/devices/system/cpu/cpu*/cpufreq/scaling_governor; do echo performance >$F; done     Hugepages: Exasol recommends enabling hugepages for hosts with at least 64GB RAM. To do so, we have to set the Hugepages option in EXAConf to either auto, host, or the number of hugepages per container. If we will set it to auto, the number of hugepages will be determined automatically, depending on the DB settings. When setting it to host the nr. of hugepages from the host system will be used (i. e. /proc/sys/VM/nr_hugepages will not be changed). However, /proc/sys/VM/hugetlb_shm_group will always be set to an internal value! Resource limitation: It's possible to limit the resources of the Exasol container with the following docker run options:     $ docker run --cpuset-cpus="1,2,3,4" --memory=20g --memory-swap=20g --memory-reservation=10g exasol/docker-db:<version>       This is especially recommended if we need multiple Exasol containers (or other services) on the same host. In that case, we should evenly distribute the available CPUs and memory throughout your Exasol containers. Find more detailed information here https://docs.docker.com/config/containers/resource_constraints/ How to deploy a single-node Exasol database as a Docker image Step 1 Create a directory to store data from container persistently To store all persistent data from the container I’m going to create a directory. I will name it “container_exa” and create it in the home folder of the Linux user.     $ mkdir $HOME/container_exa/     Set the CONTAINER_EXA variable to the folder:     $ echo ‘export CONTAINER_EXA="$HOME/container_exa/"’ >> ~/.bashrc && source ~/.bashrc     Step 2 Create a configuration file for Exasol database and docker container The command for creating a configuration file is:     $ docker run -v "$CONTAINER_EXA":/exa --rm -i exasol/docker-db:<version> init-sc --template --num-nodes 1     Since I’m going to use the latest version of exasol (currently 6.2.6). I will use the latest tag. Num-nodes is the number of containers. We need to change the value of this if we want to deploy a cluster.     $ docker run -v "$CONTAINER_EXA":/exa --rm -i exasol/docker-db:latest init-sc --template --num-nodes 1     NOTE: You need to add --privileged option because the host directory belongs to root.   After the command has finished, the directory $CONTAINER_EXA contains all subdirectories as well as an EXAConf template (in /etc).   Step 3 Complete a configuration file The configuration has to be completed before the Exasol DB container can be started. The configuration file is EXAConf and it’s stored in the “$CONTAINER_EXA/etc” folder. To be able to start a container these options have to be configured:   A private network of all nodes (Public network is not mandatory in docker version of Exasol DB) EXAStorage device(s) EXAVolume configuration Network port numbers Nameservers Different options can be configured in the EXAConf file. I will post articles about most of them. 1)  A private network of the node     $ vim $CONTAINER_EXA/etc/EXAConf [Node : 11] PrivateNet = 10.10.10.11/24 # <-- replace with the real network       In this case, the IP address of Linux the virtual machine is 10.1.2.4/24. 2) EXAStorage device configuration Use the dev.1 file as an EXAStorage device for Exasol DB and mount the LVM disk to it.     3) EXAVolume configuration Configure the volume size for Exasol DB before starting the container. There are 3 types of volumes available for Exasol. Volumes in Exasol serve three different purposes. You can find detailed information in https://docs.exasol.com/administration/on-premise/manage_storage/volumes.htm?Highlight=volumes Since it’s recommended to use less disk space than the size of LVM disk (because Exasol will create a temporary volume and there should be a free disk space for it) I’d recommend using 20 GiB space for volume. The actual size of the volume increases or decreases depending on the data stored.   4) Network port numbers Since you should use the host network mode (see "Start the cluster" below), you have to adjust the port numbers used by the Exasol services. The one that's most likely to collide is the SSH daemon, which is using the well-known port 22. I’m going to change it to 2222 in EXAConf file:   The other Exasol services (e. g. Cored, BucketFS, and the DB itself) are using port numbers above 1024. However, you can change them all by editing EXAConf. In this example, I’m going to use the default ports.     Port 22 – SSH connection Port 443 – for XMLRPC Port 8888 – port of the Database Port 6583 – port for bucketfs     5) Nameservers We can define a comma-separated list of nameservers for this cluster in EXAConf under the [Global] section. Use the google DNS address 8.8.8.8. Set the checksum within EXAConf to 'COMMIT'. This is the EXAConf integrity check (introduced in version 6.0.7-d1) that protects EXAConf from accidental changes and detects file corruption. It can be found in the 'Global' section, near the top of the file. Please also adjust the Timezone depending on your requirements.   Step 5 Create the EXAStorage device files EXAStorage is a distributed storage engine. All data is stored inside volumes. It also provides a failover mechanism. I’d recommend using a 32 GB LVM disk for EXAStorage:     $ lsblk         IMPORTANT: Each device should be slightly bigger (~1%) than the required space for the volume(s) because a part of it will be reserved for metadata and checksums. Step 5 Start the cluster The cluster is started by creating all containers individually and passing each of them its ID from the EXAConf. Since we’ll be deploying a single node Exasol DB the node ID will be n11 and the command would be:     $ docker run --name exasol-db --detach --network=host --privileged -v $CONTAINER_EXA:/exa -v /dev/mapper/db-storage:/exa/data/storage/dev.1 exasol/docker-db:latest init-sc --node-id 11     NOTE: This example uses the host network stack, i.e. the containers are directly accessing a host interface to connect. There is no need to expose ports in this mode: they are all accessible on the host. Let’s user the “docker logs” command to check the log files.     $ docker logs -f exasoldb       We can see 5 different stages in the logs. Stage 5 is the last and if we can see the node is online and the stage is finished this means the container and database started successfully.     $ docker container ls       Let’s get a bash shell in the container and check the status of the database and volumes     $ docker exec -it exasol-db bash     Inside of the container, you can run some exasol specific commands to manage the database and services. You can find some of these commands below: $ dwad_client shortlist: Gives an output about the names of the databases. $ dwad_client list: Gives an output about the current status of the databases.   As we can see the name of the database is DB1 (this can be configured in EXAConf) and the state is running. The “Connection state: up” means we can connect to the database via port 8888. $ csinfo -D – Print HDD info:   csinfo -v print information about one (or all) volume(s):   As we can see the size of the data volume is 20.00 GiB. You can also find information about the temporary volume in the output of the csinfo -v command. Since the database is running and the connection state is up let’s try to connect and run for example SQL queries. You can use any SQL clients or Exaplus CLI to connect. I’m going to use DBeaver in this article. You can find more detailed information in https://docs.exasol.com/connect_exasol/sql_clients/dbeaver.htm I’m using the public IP address of the virtual machine and port 8888 which configured as a database port in EXAConf.   By default, the password of the sys user is “exasol”. Let's run an example query:     SELECT * FROM EXA_SYSCAT;         Conclusion In this article, we deployed a single-node Exasol database in a docker container and went through the EXAConf file. In the future, I will be sharing new articles about running Exasol on docker and will analyze the EXAConf file and Exasol services in-depth. Additional References https://github.com/EXASOL/docker-db https://docs.docker.com/config/containers/resource_constraints/ https://docs.exasol.com/administration/on-premise/manage_storage/volumes.htm?Highlight=volumes  

Table of Contents What is this article about? Today I would like to give you an insight into how you can monitor the development of your database with regard to size, usage, hardware resources and SQL statements. At Exasol we use our customer dashboards to evaluate the progression of customer systems in these areas.   In this article, I will show you what the dashboards look like, on which metadata they are based and what you should pay attention to during the evaluation. A small note: We developed our dashboards on the basis of Tableau. My goal is that this article will help you to develop your own dashboards based on the technology you prefer. Lets take a look at the statistic tables we need. Exasol provides a large number of different system, metadata, and statistics tables that can be used to build a monitoring dashboard. We are now focusing on the progression of statistics over a longer period of time. The tables can of course also be used for other Monitoring Use Cases. E.g. real-time monitoring of the current database status. There are no limits to the imagination when developing new applications. There is an overview of all system tables in our documentation here: https://docs.exasol.com/sql_references/metadata/metadata(systemtables).htm Let's focus on the statistical tables: https://docs.exasol.com/sql_references/metadata/statistical_system_table.htm#Statistical_System_Tables The tables that are important to us are here EXA_DB_SIZE_* EXA_MONITOR_* EXA_USAGE_* EXA_SQL_* It is important to understand that these tables each come with 4 different levels of granularity. These include LAST_DAY, HOURLY, DAILY and MONTHLY. The EXA_SQL_LAST_DAY includes, for example, every SQL statement with the statement type, the CPU power used, the TEMP_DB_RAM generated, etc. ... at the level of transactions. Since the data is only kept for 24 hours at this granularity level, the LAST_DAY tables are not suitable for long-term monitoring - an exception would be if the tables are imported into another table on a daily basis in order to keep them here long-term. The counterpart to the EXA_SQL_LAST_DAY described above is the EXA_SQL_HOURLY. As the name suggests, the values are aggregated here on an hourly level. This means values such as CPU usage or generated TEMP_DB_RAM are provided in an AVG and a MAX value for all statement types on an hourly basis. Which table and granularity is the right one depends entirely on the KPIs and their observation period. In the following, I will show you which tables we have used for our dashboards and I will explain the respective decision on the source tables. How we build our dashboards In the following I would show you 4 of our most important customer dashboards with you. They are about the following topics in detail: Database size Hardware usage SQL statements Concurrent Users & Queries Database size Let's look at the database size dashboard. This consists of three different graphics that consider the database size, the size of the indexes and the size of the recommended RAM over a longer period of time. The dashboard is designed for a long-term view. For this reason, we decided to use EXA_DB_SIZE_DAILY as the data source, since the corresponding values are already aggregated here at the day level. In order to compare the corresponding values with the current RAM, we also use the DB_RAM_SIZE column from the EXA_SYSTEM_EVENTS table. Source: https://docs.exasol.com/sql_references/metadata/statistical_system_table.htm#EXA_DB_SIZE_DAILY    DB SIZE diagram Let's start with a detailed analysis of the individual graphics. The DB Size graphic shows us the development of the database size - compressed and not compressed. As well as the ratio of the size to the available RAM.  Data sources: Table: EXA_DB_SIZE_DAILY - Due to the long observation period, we choose the maximum of the corresponding day for the columns RAW and MEM_OBJECT_SIZE. We also use the DB_RAM_SIZE from the EXA_SYSTEM_EVENTS table. Details of the graphic: A line is drawn for the values of RAW_OBJECT_SIZE_MAX and MEM_OBJECT_SIZE_MAX. The INTERVAL_START column serves as the time factor. Due to the aggregation there is a value for each day. These values allow the database size to be analyzed over a longer period of time. Simple math makes it possible to read the average data growth over a period of time from the graph. Also peaks in the data filling or the deletion of data can be traced. Important: The graphic is also used to compare the amount of raw data with the available RAM. With an in-memory database, it is very important that the hot data fit permanently in the RAM so that optimal performance can be ensured. For this reason there is the so-called ten percent rule - this says that ten percent of the raw data volume should fit into the RAM. Therefore, another line is drawn in the graphic for the existing RAM. So that this rule can be monitored in the graphic, the RAW_OBJECT_SIZE_MAX line turns red if the RAM is less than 10% of the RAW_OBJECT value.   Auxiliary diagram Similar to the previous graphic, the size of the indexes is examined and compared with the RAM. Data sources: Table: EXA_DB_SIZE_DAILY. The index size is saved as the daily maximum in the column AUXILIARY_SIZE_MAX. We also use the DB_RAM_SIZE from the EXA_SYSTEM_EVENTS table. Details of the graphic: Both values are shown as lines over time. Important: In order to guarantee optimal performance, all indexes should fit together in the RAM. For this reason, the AUXILIARY line is displayed in red when the size exceeds the RAM.   Recommended RAM diagram On the basis of the current database usage, e.g. the created TEMP_DB_RAM, the database calculates an optimal amount of RAM at every point in time. This value is considered in the third graphic. Data sources: Table: EXA_DB_SIZE_DAILY - RECOMMENDED_DB_RAM_SIZE_AVG (Average of the day), DB_RAM_SIZE Details of the graphic: Line diagram Important:The Recommended RAM_SIZE is a value estimated by the system and represents a recommendation for an ideal RAM size. Our experience has shown that the system can already be operated optimally with half this value. We have made this factor easy to maintain in the dashboard. If the RECOMMENDED_RAM_SIZE divided by FACTOR is greater than the RAM, the RECOMMENDED_RAM_SIZE is displayed in red. Our experience has shown that NOT (RECOMMENDED> 2 * RAM) turned out to be a good metric.   Hardware usage As a distributed in-memory database system, the performance of Exasol depends on the following four hardware components: CPU, RAM, network and hard disk access. For this reason, the following dashboard consists of four graphics, each dealing with one of these components. We use the EXA_MONITOR_DAILY table as the data source.   Source: https://docs.exasol.com/sql_references/metadata/statistical_system_table.htm#EXA_MONITOR_DAILY   CPU Diagram As a MPP database an Exasol cluster has a variable number of Nodes with its own CPUs. Data sources: Table EXA_MONITOR_DAILY - CPU_MAX Details of the graphic: Line Diagram Important:The highest possible CPU utilization is desirable. The maximum values of the day are used for this. The average value is not meaningful because IDLE times have a massive influence on the result. The goal is a maximum utilization of over 85%. If the CPU load falls below this value on a day, this is displayed in red in the diagram   HDD read diagram With an in-memory database, the most important data should always fit in the RAM. Whenever a data block is not in the RAM, it has to be read from the hard disk, which costs performance. Data sources: Table EXA_MONITOR_DAILY - HDD_READ_AVG, HDD_READ_MAX Details of the graphic: Line diagram Important:The HDD_READ values should be as low as possible. We have used AVG as a window function in our BI tool to determine whether values are increasing permanently or not.  This ensures that the line is displayed in red if there is a permanent incline. For a long-term view, the Average is very important because peaks can occur from time to time.   NET diagram If data is distributed to different Nodes, it is important to set the distribution key and use replication border (see: https://docs.exasol.com/performance/best_practices.htm?Highlight=Best) to avoid too many global operations. Global operations like global joins and global aggregations cannot be avoided completely. Therefore the network traffic should be monitored. Data sources: Table EXA_MONITOR_DAILY - NET_AVG, NET_MAX Details of the graphic: Line diagram Important:For the reason mentioned above, the NET values should be as low as possible. We have used AVG as a window function in our BI tool to determine whether values are increasing permanently or not. This ensures that the line is displayed in red if there is a permanent incline.   Temp DB RAM diagram Every database operation generates a temporary amount of data in the RAM. If the sum of all temporarily generated data no longer fits in the RAM, this is reflected in the performance of the operations. Data sources: Table EXA_MONITOR_DAILY - TEMP_DB_RAM_AVG, TEMP_DB_RAM_MAX Details of the graphic: Line diagram Important: For the reason mentioned above, the TEM DB RAM values should be monitored. We have used AVG as a window function in our BI tool to determine whether values are increasing permanently or not. This ensures that the line is displayed in red if there is a permanent incline.   SQL statements This dashboard is intended to give an overview of the average runtimes of different queries. The EXA_SQL_DAILY table is used as the data source for this. Source: https://docs.exasol.com/sql_references/metadata/statistical_system_table.htm#EXA_SQL_DAILY  SQL Queries by class diagram The graphic shows over a longer timeline how many queries of a COMMAND_CLASS have been executed per day. The database operator can use this view to determine whether a change in performance can be justified, for example, by a strong increase or decrease in queries. Data sources: Table EXA_SQL_DAILY - COUNT, COMMAND_CLASS, EXECUTION_MODE, SUCCESS Details of the graphic: Line diagram Important: The graphic shows differently colored lines. Each line stands for a COMMAND_CLASS. The statements are filtered on EXECUTION_MODE = 'EXECUTE' and SUCCESS. There is also a filter on the COMMAND_CLASS.   Executed and successful Select Statements diagram The following graphic gives an overview of the average and maximum duration of DQL statements. Data sources: Table EXA_SQL_DAILY - DURATION_AVG, DURATION_AVG Details of the graphic: Line diagram Important: In the BI Tool we have placed a trend line on the development of the curves for a better overview. The Data is filtered on the COMMAND_CLASS "DQL", EXECUTION_MODE = "EXECUTE" and SUCCESS.       Commit Duration diagram The following graphic gives an overview of the average and maximum duration of Commit statements. Data sources: Table EXA_SQL_DAILY - DURATION_AVG, DURATION_AVG Details of the graphic: Line diagram Important: In the BI TOOL we have placed a trend line on the development of the curves for a better overview. The Data is filtered on the COMAND_CLASS "COMMIT", EXECUTION_MODE = "EXECUTE" and SUCCESS. Concurrent Users & Queries We would like to use this dashboard to provide an overview of the use of the database. In order to be able to show the usage over the different days of the week and the time of day, we use a finer granularity here. For this reason, we are accessing a _HOURLY table for the first time in this article. This is the EXA_USAGE_HOURLY table. With this table we can evaluate on an hourly basis how many queries were processed by the database at the same time or how often the database was idle. Source: https://docs.exasol.com/sql_references/metadata/statistical_system_table.htm#EXA_USAGE_HOURLY Idle diagram By combining the different types of diagrams, this graphic provides an insight into how the system is being used on different days and times of the week. Data sources: Table EXA_USAGE_HOURLY - IDLE Column from EXA_USAGE_HOURLY Details of the graphic: This graphic contains both a bar and a matrix diagram. The bar chart shows the average idle time by day of the week. The matrix diagram shows the idle time broken down into day of the week and hour of the day. To do this, the INTERVAL_START field is broken down to the weekday and hour level. Important: The IDLE column indicates the percentage of the hour (EXA_USAGE_HOURLY) for which the database has no queries to process. Depending on the percentage, the field is displayed in a different color. Concurrent Queries diagram The database describes in the table EXA_USAGE_LAST_DAY exactly how many queries are processed in parallel at the transaction level. For the evaluation over a longer period of time we use the table EXA_USAGE_HOURLY. This table shows the average value and the maximum for queries per hour. Data sources: Table EXA_USAGE_HOURLY - QUERIES_AVG, QUERIES_MAX Details of the graphic:  Line diagram Important: The limit of parallel queries in Exasol is 100 by default. In the dashboard, the user has the option of specifying a limit value from which he sees the database load as critical. If this limit is exceeded, both the line for the average and the line for the maximum turn red.   Final conclusion I hope I was able to give you an insight with this article into how you can monitor an Exasol database in a long-term perspective. The statistical system tables DB_SIZE, MONITORING, SQL and USAGE already give you a good overview of what is happening on the system. However, there are many other system tables that are suitable for different monitoring scenarios. There are no limits to creativity here.   We're happy to get your experiences and feedback on this article or your own Monitoring scenarios or Dashboards below! 

Symptoms: SFTP only delivers a network bandwidth of up to 10 MB/s Explanation: When using SFTP for IMPORT/EXPORT SQL statements or remote backup schedules a speed of only up to 10 MB/s is delivered. This is due to a limitation in a 3rd party software required for remote access and may change in the future. Solution: If a higher network bandwidth is required, use alternative protocols like FTP (unencrypted) or FTPS (implicit or explicit). These usually deliver maximum network bandwidth.  

Exasol on Docker has multiple options when it comes to data storage. You can either use a partition/disk or a storage file located in a specific location on the Docker host. The examples below all use a file system to store the dev.1 file which is used by EXAStorage. Create a logical volume 1. Add a physical device to LVM: $ pvcreate /dev/vda 2. Create a volume group: $ vgcreate VG1 /dev/vda 3. Create a logical volume: $ lvcreate --size 30G VG1 4. Add a filesystem: $ mkfs.ext4 /dev/VG1/lvol0 5. Mount: $ mount /dev/VG1/lvol0 /mnt/ 5.1. or Mount Permanently by adding it to /etc/fstab: $ echo   When you reach the limit of your storage device you can increase its capacity by executing the commands below: Increase the size of the physical device 1. Increase the size of the physical device: $ pvresize /dev/vda 2. Extend logical volume and resize the file system: $ lvextend -r -L +20GB /dev/VG1/lvol0 2.1. or if lvextend was executed without -r: $ resize2fs /dev/VG1/lvol0   If you prefer using a storage file, you can do the following: Add a disk to an existing volume group and logical volume 1. Add physical device to LVM: $ pvcreate /dev/vdb 2. Extend volume group: $ vgextend VG1 /dev/vdb 3. Extend logical volume and resize FS: $ lvextend -r -L+100G /dev/VG1/lvol0 3.1. or if lvextend was executed without -r: $ resize2fs /dev/VG1/lvol0 4. Enter container and enlarge file device: $ truncate --size=+10GB /exa/data/storage/dev.1 $ cshdd --enlarge -n 11 -h /exa/data/storage/dev.1  

  Background Deploying 2+1 Exasol Cluster on Amazon Web Service (AWS) Post snapshot: This post will show you: How to deploy a 2+1 Exasol Cluster on Amazon Web Services (AWS) Before we go into the step-by-step guide, please read through the following prerequisites and recommendations to make sure that you're prepared Prerequisites AWS Account: Make sure you have an AWS account with the relevant permissions. If you do not have an AWS account, you can create one from the Amazon Console. AWS Key Pair: You have a Key Pair created. AWS uses public-key cryptography to secure the log-in information for your instance. For more information on how to create a Key Pair, see Amazon EC2 Key Pairs in the AWS documentation. Subscription on AWS Marketplace: You must have subscribed to one of the following Exasol subscriptions on AWS Marketplace: Exasol Analytic Database (Single Node / Cluster, Bring-Your-Own-License) Exasol Analytic Database (Single Node / Cluster, Pay-As-You-Go) How to deploy a 2+1 Exasol Cluster Step 1 Open https://cloudtools.exasol.com/ to access the cloud deployment wizard in your browser and choose your cloud provider. In this case, the Cloud Provider should be Amazon Web Services. Select your region from the drop-down list. I'm going to deploy our cluster in Frankfurt   Step 2 On the Configuration screen, by default, you see the Basic Configuration page. You can choose one of the existing configurations made by Exasol. Basic Configuration: Shows a minimum specification for your data size. Balanced Configuration: Shows an average specification for your data size for good performance. High-Performance Configuration: Shows the best possible specification for your data size for high performance. In this case, I'm going to choose the Advanced Configuration option. If you are going to deploy a cluster for production purposes we recommend discussing sizing options with the Exasol support team or use one of the existing configurations made by Exasol. RAW Data Size (in TB): You can add the required raw data size on your own, otherwise, it will be calculated automatically after setting Instance type and node count. License Model: Pay as you go (PAYG) Pay as you go (PAYG) license model is a flexible and scalable license model for Exasol's deployment on a cloud platform. In this mode, you pay for your cloud resources and Exasol software through the cloud platform's billing cycle. You can always change your setup later to scale up or down your system and the billing changes accordingly. Bring your own license (BYOL) Bring your own license (BYOL) license model lets you choose a static license for Exasol software and a dynamic billing for the cloud resources. In this model , you need to purchase a license from Exasol and add it to your cloud instance. This way, you pay only for the cloud resources through the cloud platform's billing cycle and there is no billing for the software. You can always change your setup later to scale up or down your system and the billing changes accordingly. However, there is a limit for the maximum scaling based on your license type (DB RAM or raw data size). You can find detailed information about licensing in https://docs.exasol.com/administration/aws/licenses.htm System Type: You can choose one of the Exasol Single Node and Enterprise Cluster options. I'm going to choose the Enterprise Cluster option. Instance Family: You can choose one of the instance types of AWS EC2 service to deploy virtual machines for Exasol nodes. You can find detailed information about instance types of AWS EC2 in https://aws.amazon.com/ec2/instance-types/ The number of DB Nodes: We need to determine the total number of active data nodes in this section.     After finishing the configuration we can see the RAW data size calculated automatically for us. On the left side of the screen, we can see the details of our setup on AWS. If you have a license from Exasol please choose the BYOL option in License Model, this will cause a decrease in Estimated Costs. Step 3 After click Continue to proceed with the deployment, we can see the Summary page. We can overview the cluster configuration and choose a deployment option. We have the option to select create new VPC or use existing VPC for the CloudFormation stack. Create New VPC that will create a new VPC and provision of all resources within it. Use Existing VPC will provision Exasol to use an existing VPC subnet of your choice. For more information on VPC, see Amazon Virtual Private Cloud. Based on this VPC selection, the parameters in the stack creation page on AWS will change when you launch the stack. For more information on the stack parameters, see Template Parameters. If you want to download the configuration file and upload them later to your AWS stack through CloudFormation Console, you can click the CloudFormation Templates option on the left side. Click Launch Stack. You will be redirected to the Quick create stack page on AWS.   Step 4 After redirecting to the Quick create stack page on AWSReview and I'm going to fill the required stack parameters: Stack Name Key Pair, SYS User Password, or ADMIN User Password. In the VPC/Network/Security section, the Public IPs are set to false by default. I'm going to set this to true. If you want to keep the Public IP address set to false, then you need to enable VPN or other methods to be able to access your instance. (Optional) License is applicable if your subscription model is Bring-your-own-license. Paste the entire content of the license file you have in the space provided. Click Create Stack to continue deploying Exasol in the CloudFormation Console. You can view the stack you created under AWS CloudFormation > Stacks, with the status CREATE_IN_PROGRESS . Once the stack is created successfully, the status is changed to CREATE_COMPLETE . Additionally, you can monitor the progress in the Events tab for the stack. For more information about the stack parameters, please check the table here https://docs.exasol.com/cloud_platforms/aws/installation_cf_template.htm?Highlight=Template%20Parameters After filling the required parameters I'm going to click Create Stack to continue deploying Exasol in the CloudFormation Console. We can view the stack created under AWS CloudFormation > Stacks, with the status CREATE_IN_PROGRESS . Once the stack is created successfully, the status is changed to CREATE_COMPLETE .   Additionally, we can monitor the progress in the Events tab for the stack. Step 5 Determine the Public IP Address We need the Public IP or DNS name displayed in the EC2 Console to connect to the database server or launch the instance. To know the Public IP or DNS name: Open the EC2 Dashboard from the AWS Management Console. Click on Running Instance. The Instances page is displayed with all the running instances. Select the name of the instance you created. (In this case exasol-cluster-management_node and exasol-cluster-management_node). We need the IP address of management node In the Description section, the IP address displayed for Public DNS(IPv4) is the IP address of the database server. If the Public IP parameter for your stack is set to false,  you need to enable VPN or other methods to connect to the database server via the private IP address of the instances. Step 6 Access to Initialization page Copy and paste this IP address prefixed with https in a browser. In the case of an Exasol cluster deployment, I need to copy the IP address or DNS name of the management node. After confirming the digital certificate the following screen is displayed.   Once the installation is complete, I will be redirected to the EXAoperation screen. It may take up to 45 minutes for the EXAoperation to be online after deployment. You can login with the admin user name and password provided while creating your stack.  Step 7 Connect to the database In this case (a 2+1 cluster deployment), I need to use the Public IP address of the data node along with the admin user name and password to connect to the SQL client. I can also connect to all the data nodes by entering the pubic IP address of all the nodes separated by a comma. Additional Notes Connect to Exasol After installing Exasol on AWS, you can do the following: Install drivers required to connect to other tools. Connect SQL clients to Exasol. Connect Business Intelligence tools (BI tools) to Exasol. Connect Data Integration - ETL tool to Exasol. Connect Data Warehouse Automation tools to Exasol.   Load Data After you have connected your choice of tool to Exasol, you can load your data into Exasol and process further. To know more about loading data into Exasol, see Loading Data. Conclusion In this article, we deployed a 2+1 Exasol cluster on AWS. In the future, I will be sharing new articles about managing the Exasol cluster on AWS, using lambda functions to schedule the start/stop of a cluster, etc. Additional References https://cloudtools.exasol.com https://docs.exasol.com/administration/aws.htm

With this article, you will learn how to add and change database parameters and their values. 1. Log in to your Exasol container: $ docker exec -it <container_name> /bin/bash 2. Inside the container go to the /exa/etc/ folder and open the EXAConf file with a text editor of your choice: $ cd /exa/etc $ vim EXAConf 3. Under the DB section, right above the [[JDBC]] sub-section add a line that says Params and the necessary parameters: [DB : DB1] Version = 6.1.5 MemSize = 6 GiB Port = 8563 Owner = 500 : 500 Nodes = 11,12,13 NumActiveNodes = 3 DataVolume = DataVolume1 Params = -useIndexWrapper=0 -disableIndexIteratorScan=1 [[JDBC]] BucketFS = bfsdefault Bucket = default Dir = drivers/jdbc [[Oracle]] BucketFS = bfsdefault Bucket = default Dir = drivers/oracle 4. Change the value of Checksum in EXAConf: $ sed -i '/Checksum =/c\ Checksum = COMMIT' /exa/etc/EXAConf 5. Commit the changes: $ exaconf commit 6. At this point you have 2 options: 6.1. Restart the container: $ dwad_client stop-wait <database_instance> # Stop the database instance (inside the container) $ csctrl -d # Stop the storage service (inside the container) $ exit # Exit the container $ docker restart <container_name> # Restart the container $ docker exec -it <container_name> # Log in to the container's BASH environment $ dwad_client setup-print <database_instance> # See the database parameters ... PARAMS: -netmask= -auditing_enabled=0 -lockslb=1 -sandboxPath=/usr/opt/mountjail -cosLogErrors=0 -bucketFSConfigPath=/exa/etc/bucketfs_db.cfg -sysTZ=Europe/Berlin -etlJdbcConfigDir=/exa/data/bucketfs/bfsdefault/.dest/default/drivers/jdbc:/usr/opt/EXASuite-6/3rd-party/JDBC/@JDBCVERSION@:/usr/opt/EXASuite-6/EXASolution-6.1.5/jdbc -useIndexWrapper=0 -disableIndexIteratorScan=1 ... As you can from the output mentioned above, the parameters have been added. However, rebooting the cluster can cause some downtime. In order to shorten the duration of your downtime, you can try the method below. 6.2. Use a configuration file to change the parameters by just rebooting the database, not container: $ dwad_client setup-print <database_instance> > db1.cfg # See the database parameters $ vim db1.cfg # Edit the configuration file When you open the file, find the line starting with PARAMS and the parameter you need, like: PARAMS: -netmask= -auditing_enabled=0 -lockslb=1 -sandboxPath=/usr/opt/mountjail -cosLogErrors=0 -bucketFSConfigPath=/exa/etc/bucketfs_db.cfg -sysTZ=Europe/Berlin -etlJdbcConfigDir=/exa/data/bucketfs/bfsdefault/.dest/default/drivers/jdbc:/usr/opt/EXASuite-6/3rd-party/JDBC/@JDBCVERSION@:/usr/opt/EXASuite-6/EXASolution-6.1.5/jdbc -useIndexWrapper=0 -disableIndexIteratorScan=1 After adding the parameters, save the file and execute the following commands: $ dwad_client stop-wait <database_instance> # Stop the database instance (inside the container) $ dwad_client setup <database_instance> db1.cfg # Setup the database with the db1.cfg configuration file (inside the container) $ dwad_client start-wait <database_instance> # Start the database instance (inside the container) This will add the database parameters, but will not be persistent throughout reboots. Therefore, by adding the parameters this way you shorten your downtime, but the changes aren't permanent. After doing this, we would recommend to also do method 6.1, in case you decide to reboot sometime in the future. 7. Verify the parameters: 7.1. With dwad_client list:             7.2. With dwad_list print-setup <database_instance>:                

Synopsis This article depicts the steps required to start a cluster when all nodes are powered off and how to shut a cluster down using the EXAoperation XML-RPC interface. The python snippets are mere examples for the usage of the XML-RPC function calls and provided as-is. Please refer to the EXAoperation manual for details and further information on XML-RPC. Alphabetical list of referenced XML-RPC calls Function Context Description callPlugin Cluster Execute a call to an EXAoperation plugin getEXAoperationMaster Cluster Return the node of the current EXAoperation master node getDatabaseConnectionState Database instance Get connection state of an EXASolution instance getDatabaseConnectionString Database instance Return the connection string of an EXASolution instance as used by EXAplus and EXASolution drivers getDatabaseList Cluster Lists all database instances defined on the cluster getDatabaseOperation Database instance Get current operation of an EXASolution instance getDatabaseState Database instance Get the runtime state of an EXASolution instance getHardwareInformation Cluster Reports information about your system's hardware as provided by   dmidecode getNodeList Cluster Lists all defined cluster nodes except for license server(s) getServiceState Cluster List the cluster services and their current runtime status logEntries Logservice Fetch messages collected y a preconfigured EXAoperation logservice shutdownNode Cluster Shutdown (and power off) a cluster node startDatabase Database instance Start an EXASolution instance startEXAStorage Storage service Start the EXAStorage service startupNode Cluster Cold start a cluster node stopDatabase Database instance Stop an EXASolution instance stopEXAStorage Storage service Stop the EXAStorage service   Establishing the connection to EXAoperation To send XML-RPC requests to EXAoperation, please connect to the EXAoperation HTTP or HTTPS listener and provide the base URL matching to the context of a function call as described in the EXAoperation manual (chapter "XML-RPC interface") and listed in the table below. The code examples in this article are written in Python (tested in versions 2.7 and 3.4). import sys if sys.version_info[0] > 2: # Importing the XML-RPC library in python 3 from xmlrpc.client import ServerProxy else : # Importing the XML-RPC library in python 2 from xmlrpclib import ServerProxy # define the EXAoperation url cluster_url = "https: //user:password@license-server/cluster1" # create a handle to the XML-RPC interface cluster = ServerProxy(cluster_url) Startup of a cluster 1. Power-on the license server and wait for EXAoperation to start License servers are the only nodes able to boot from the local hard disk. All other (database/compute) nodes receive their boot images via PXE. Hence, you need to have at least one license server up and running to kick-start the rest of the cluster. Physically Power-on the license server and wait until the EXAoperation interfaces are connectible. cluster_url = "https: //user:password@license-server/cluster1" while True: try : cluster = ServerProxy(cluster_url) if cluster.getNodeList(): print( "connected\n" ) break except: continue 2. Start the database/compute nodes Please note that The option to power-on the database nodes using startupNode() is only usable if the nodes are equipped with an out-of-band management interface (like HP iLO or Dell iDRAC) and if this interface is configured in EXAoperation. Virtualized environments (such as vSphere) provide means to automate the startup of servers on a sideband channel. for node in cluster.getNodeList(): cluster.startupNode(node) The function getNodeList returns the list of database nodes currently configured in EXAoperation but it does not provide information about the availability in the cluster. You may check if a node is online by querying the node's hardware inventory. for node in cluster.getNodeList(): if 'dmidecode' in cluster.getHardwareInformation(node): print( "node {} is online\n" .format(node)) else : print( "node {} is offline\n" .format(node)) The boot process itself can be monitored by following the messages in an appropriate logservice. Look for messages like 'Boot process finished after XXX seconds' for every node. logservice_url = "https: //user:password@license-server/cluster1/logservice1" logservice = ServerProxy(logservice_url) logservice.logEntries() It is vital that all cluster nodes are up and running before you proceed with the next steps. 3. Start the EXAStorage service EXAStorage provides volumes as persistence layer for EXASolution databases. This service does not start on boot automatically. The startEXAStorage function returns 'OK' on success or an exception in case of a failure. cluster_url = "https: //user:password@license-server/cluster1" storage_url = "https: //user:password@license-server/cluster1/storage" cluster = ServerProxy(cluster_url) storage = ServerProxy(storage_url) # start the Storage service storage.startEXAStorage() # check the runtime state of all services cluster.getServiceState() The getServiceState call returns a list of all cluster services. Ensure that all of them indicate the runtime state 'OK' before you proceed. [[ 'Loggingd' , 'OK' ], [ 'Lockd' , 'OK' ], [ 'Storaged' , 'OK' ], [ 'DWAd' , 'OK' ]] 4. Start the EXASolution instances Iterate over the EXASolution instances and start them: for db in cluster.getDatabaseList(): instance_url = "https: //user:password@license-server/cluster1/db_{}" .format(db) instance = ServerProxy(instance_url) instance.startDatabase() while True: if 'Yes' == instance.getDatabaseConnectionState(): print( "database {} is accepting connections at {}\n" .format( db, instance.getDatabaseConnectionString())) break Again, you may monitor the database startup process by following an appropriate logservice. Wait for messages indicating that the given database is accepting connections. 5. Start services from EXAoperation plugins Some third-party plugins for EXAoperation may require further attention. This example shows how to conditionally start the VMware tools Daemon. plugin = 'Administration.vmware-tools' # Restart the service on the license server # to bring it into the correct PID namespace cluster.callPlugin(plugin, 'n0010' , 'STOP' ) cluster.callPlugin(plugin, 'n0010' , 'START' ) for node in cluster.getNodeList(): if 'vmtoolsd is running' not in cluster.callPlugin(plugin, node, 'STATUS' )[1]: cluster.callPlugin(plugin, node, 'START' ) Shutdown of a cluster The shutdown of a cluster includes all actions taken for the startup in reverse order. To prevent unwanted effects and possible data loss, it's commendable to perform additional checks on running service operations. Example license_server_id = "n0010" exaoperation_master = cluster.getEXAoperationMaster() if exaoperation_master != license_server_id: print( "node {} is the current EXAoperation master but it should be {}\n" .format( exaoperation_master, license_server_id)) If the license server is not the EXAoperation master node, please log into EXAoperation and move EXAoperation to the license server before you continue. 1. Shutdown of the EXASolution instances Iterate over the EXASolution instances, review their operational state and stop them. for db in cluster.getDatabaseList(): instance_url = "https: //user:password@license-server/cluster1/db_{}" .format(db) instance = ServerProxy(instance_url) state = instance.getDatabaseState() if 'running' == state: operation = instance.getDatabaseOperation() if 'None' == operation: instance.stopDatabase() while True: if 'setup' == instance.getDatabaseState(): print( "database {} stopped\n" .format(db)) break else : print( "Database {} is currently in operation state {}\n" .format(db, operation)) else : print( "Database {} is currently in runtime state {}\n" .format(db, state)) 2. Shutdown of the EXAStorage service Please assure yourself that all databases are shut down properly before stopping EXAStorage! cluster_url = "https: //user:password@license-server/cluster1" storage_url = "https: //user:password@license-server/cluster1/storage" cluster = ServerProxy(cluster_url) storage = ServerProxy(storage_url) storage.stopEXAStorage() cluster.getServiceState() The state of the Storaged will switch to 'not running': [[ 'Loggingd' , 'OK' ], [ 'Lockd' , 'OK' ], [ 'Storaged' , 'not running' ], [ 'DWAd' , 'OK' ]] 3. Shutdown of the cluster nodes and of the license server(s) at last for node in cluster.getNodeList(): cluster.shutdownNode(node) license_servers = [ 'n0010' ,] for ls in license_servers: cluster.shutdownNode(ls) The last call triggers the shutdown of the license server(s) and therefore terminate all EXAoperation instances.

Background ConfD is the EXASOL configuration and administration daemon that runs on all nodes of an EXASOL cluster. It provides an interface for cluster administration and synchronizes the configuration across all nodes. In this article, you can find examples to manage the Exasol docker cluster using XML-RPC.   Prerequisites and Notes Please note that this is still under development and is not officially supported by Exasol. We will try to help you as much as possible, but can't guarantee anything. Note: Any SSL checks disabled for these examples in order to avoid exceptions with self-signed certificates Note: If you got an error message like xmlrpclib.ProtocolError: <ProtocolError for root:testing@IPADDRESS:443/: 401 Unauthorized> please login to cluster and reset root password via the exaconf passwd-user command. Note: All of the examples tested with Exasol version 6.2.7 and python 2.7   Explanation & Examples We need to create a connection and get a master IP before running any ConfD job via XML-RPC. You can find how to do it below: Import required modules and get the master IP: >>> import xmlrpclib, requests, urllib3, ssl >>> urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning) Get current master IP (you can use any valid IP in the cluster for this request) >>> master_ip = requests.get( "https: //11.10.10.11:443/master" , verify = False).content In this case, 11.10.10.11 is the IP address of one of the cluster nodes Create connection: Note: We assume you've set the root password "testing". You can set a password via exaconf passwd-user command >>> connection_string = "https: //root:testing@%s:443/" % master_ip >>> sslcontext = ssl._create_unverified_context() >>> conn = xmlrpclib.ServerProxy(connection_string, context = sslcontext, allow_none=True)   The list of examples: Example 1 - 2: Database jobs Example 3: Working with archive volumes Example 4: Cluster Node Jobs Example 5: EXAStorage Volume Jobs Example 6: Working with backups   Example 1: Database jobs How to use ConfD jobs to get the database status and information about a database Run a job to check the status of the database: Note: In this example we assume the database name is "DB1". Please adjust the database name. conn.job_exec( 'db_state' , { 'params' : { 'db_name' : 'DB1' }})  Output: { 'result_name' : 'OK' , 'result_output' : 'running' , 'result_desc' : 'Success' , 'result_jobid' : '12.2' , 'result_code' : 0} As you can see in the output the 'result_output' is  'running' and 'result_desc' is 'Success'. This means the database is up and running. Note: If you want to format the JSON output you can use pprint module Run a job to get information about the database: >>> import pprint >>> pprint.pprint(conn.job_exec( 'db_info' , { 'params' : { 'db_name' : 'DB1' }})) { 'result_code' : 0, 'result_desc' : 'Success' , 'result_jobid' : '11.89' , 'result_name' : 'OK' , 'result_output' : { 'connectible' : 'Yes' , 'connection string' : '192.168.31.171:8888' , 'info' : '', 'name' : 'DB1' , 'nodes' : { 'active' : [ 'n11' ], 'failed' : [], 'reserve' : []}, 'operation' : 'None' , 'persistent volume' : 'DataVolume1' , 'quota' : 0, 'state' : 'running' , 'temporary volume' : 'v0001' , 'usage persistent' : [{ 'host' : 'n11' , 'size' : '10 GiB' , 'used' : '6.7109 MiB' , 'volume id' : '0' }], 'usage temporary' : [{ 'host' : 'n11' , 'size' : '1 GiB' , 'used' : '0 B' , 'volume id' : '1' }]}}   Example 2: Database jobs. How to list, start and stop databases   Run a job to list databases in cluster: conn.job_exec( 'db_list' ) Output example: >>> pprint.pprint(conn.job_exec( 'db_list' )) { 'result_code' : 0, 'result_desc' : 'Success' , 'result_jobid' : '11.91' , 'result_name' : 'OK' , 'result_output' : [ 'DB1' ]}   Stop the DB1 database: Run a job to stop database DB1 in cluster: >>> conn.job_exec( 'db_stop' , { 'params' : { 'db_name' : 'DB1' }}) { 'result_name' : 'OK' , 'result_desc' : 'Success' , 'result_jobid' : '12.11' , 'result_code' : 0}   Run a job to confirm the state of the database DB1: >>> conn.job_exec( 'db_state' , { 'params' : { 'db_name' : 'DB1' }}) { 'result_name' : 'OK' , 'result_output' : 'setup' , 'result_desc' : 'Success' , 'result_jobid' : '12.12' , 'result_code' : 0}  Note: 'result_output': 'setup': the status of the database is "setup"    Run a job to start database DB1 in cluster: >>> conn.job_exec( 'db_start' , { 'params' : { 'db_name' : 'DB1' }}) { 'result_name' : 'OK' , 'result_desc' : 'Success' , 'result_jobid' : '12.13' , 'result_code' : 0}   Run a job to verify the state of the database of DB1 is up and running: >>> conn.job_exec( 'db_state' , { 'params' : { 'db_name' : 'DB1' }}) { 'result_name' : 'OK' , 'result_output' : 'running' , 'result_desc' : 'Success' , 'result_jobid' : '12.14' , 'result_code' : 0}   Example 3: Working with archive volumes Example 3.1: Add a remote archive volume to cluster Name Description Parameters remote_volume_add Add a remote volume vol_type, url optional: remote_volume_name, username, password, labels, options, owner, allowed_users substitutes: remote_volume_id allowed_groups: root, exaadm, exastoradm notes: * 'ID' is assigned automatically if omitted (10000 + next free ID) 'ID' must be >= 10000 if specified 'name' may be empty (for backwards compat.) and is generated from 'ID' in that case ("r%04i" % ('ID' - 10000)) if 'owner' is omitted, the requesting user becomes the owner     >>> conn.job_exec( 'remote_volume_add' , { 'params' : { 'vol_type' : 's3' , 'url' : 'http: //bucketname.s3.amazonaws.com' ,'username': 'ACCESS-KEY','password': 'BASE64-ENCODED-SECRET-KEY'}}) { 'result_revision' : 18, 'result_jobid' : '11.3' , 'result_output' : [[ 'r0001' , 'root' , '/exa/etc/remote_volumes/root.0.conf' ]], 'result_name' : 'OK' , 'result_desc' : 'Success' , 'result_code' : 0}   Example 3.2: list all containing  remote volume names Name Description Parameter Returns remote_volume_list List all existing remote volumes None a list containing all remote volume names     >>> pprint.pprint(conn.job_exec( 'remote_volume_list' )) { 'result_code' : 0, 'result_desc' : 'Success' , 'result_jobid' : '11.94' , 'result_name' : 'OK' , 'result_output' : [ 'RemoteVolume1' ]}   Example 3.3: Connection state of the given remote volume Name Description Parameter Returns remote_volume_state Return the connection state of the given remote volume, online / Unmounted / Connection problem remote_volume_name substitutes: remote_volume_id List of the connection state of the given remote volume on all nodes   >>> conn.job_exec( 'remote_volume_state' , { 'params' : { 'remote_volume_name' : 'r0001' }}) { 'result_name' : 'OK' , 'result_output' : [ 'Online' ], 'result_desc' : 'Success' , 'result_jobid' : '11.10' , 'result_code' : 0}   Example 4: Manage cluster nodes Example 4.1: get node list Name Description Parameter Returns node_list List all cluster nodes (from EXAConf)  None Dict containing all cluster nodes.   >>> pprint.pprint( conn.job_exec( 'node_list' )) { 'result_code' : 0, 'result_desc' : 'Success' , 'result_jobid' : '11.95' , 'result_name' : 'OK' , 'result_output' : { '11' : { 'disks' : { 'disk1' : { 'component' : 'exastorage' , 'devices' : [ 'dev.1' ], 'direct_io' : True, 'ephemeral' : False, 'name' : 'disk1' }}, 'docker_volume' : '/exa/etc/n11' , 'exposed_ports' : [[8888, 8899], [6583, 6594]], 'id' : '11' , 'name' : 'n11' , 'private_ip' : '192.168.31.171' , 'private_net' : '192.168.31.171/24' , 'uuid' : 'C5ED84F591574F97A337B2EC9357B68EF0EC4EDE' }}}    Example 4.2: get node state Name Description Parameter Returns node_state State of all nodes (online, offline, deactivated)  None  A list containing a string representing the current node state.     >>> pprint.pprint(conn.job_exec( 'node_state' )) { 'result_code' : 0, 'result_desc' : 'Success' , 'result_jobid' : '11.96' , 'result_name' : 'OK' , 'result_output' : { '11' : 'online' , 'booted' : { '11' : 'Tue Jul 7 14:14:07 2020' }}}   other available options: node_add Add a node to the cluster priv_net optional: id, name, pub_net, space_warn_threshold, bg_rec_limit allowed_groups: root, exaadm int node_id node_remove Remove a node from the cluster node_id optional: force allowed_groups: root, exaadm None node_info Single node info with extended information (Cored, platform, load, state) None See the output of  cosnodeinfo node_suspend Suspend node, i. e. mark it as "permanently offline". node_id allowed_groups: root, exaadm mark one node as suspended node_resume Manually resume a suspended node. node_id allowed_groups: root, exaadm unmark one suspended node   Example 5: EXAStorage volume jobs  Example 5.1: list EXAStorage volumes Name Description Parameter Returns st_volume_list List all existing volumes in the cluster. none List of dicts     >>> pprint.pprint(conn.job_exec( 'st_volume_list' )) { 'result_code' : 0, 'result_desc' : 'Success' , 'result_jobid' : '11.97' , 'result_name' : 'OK' , 'result_output' : [{ 'app_io_enabled' : True, 'block_distribution' : 'vertical' , 'block_size' : 4096, 'bytes_per_block' : 4096, 'group' : 500, 'hdd_type' : 'disk1' , 'hdds_per_node' : 1, 'id' : '0' , 'int_io_enabled' : True, 'labels' : [ '#Name#DataVolume1' , 'pub:DB1_persistent' ], 'name' : 'DataVolume1' , 'nodes_list' : [{ 'id' : 11, 'unrecovered_segments' : 0}], 'num_master_nodes' : 1, 'owner' : 500, 'permissions' : 'rwx------' , 'priority' : 10, 'redundancy' : 1, 'segments' : [{ 'end_block' : '2621439' , 'index' : '0' , 'nid' : 0, 'partitions' : [], 'phys_nid' : 11, 'sid' : '0' , 'start_block' : '0' , 'state' : 'ONLINE' , 'type' : 'MASTER' , 'vid' : '0' }], 'shared' : True, 'size' : '10 GiB' , 'snapshots' : [], 'state' : 'ONLINE' , 'stripe_size' : 262144, 'type' : 'MASTER' , 'unlock_conditions' : [], 'use_crc' : True, 'users' : [[30, False]], 'volume_nodes' : [11]}, { 'app_io_enabled' : True, 'block_distribution' : 'vertical' , 'block_size' : 4096, 'bytes_per_block' : 4096, 'group' : 500, 'hdd_type' : 'disk1' , 'hdds_per_node' : 1, 'id' : '1' , 'int_io_enabled' : True, 'labels' : [ 'temporary' , 'pub:DB1_temporary' ], 'name' : 'v0001' , 'nodes_list' : [{ 'id' : 11, 'unrecovered_segments' : 0}], 'num_master_nodes' : 1, 'owner' : 500, 'permissions' : 'rwx------' , 'priority' : 10, 'redundancy' : 1, 'segments' : [{ 'end_block' : '262143' , 'index' : '0' , 'nid' : 0, 'partitions' : [], 'phys_nid' : 11, 'sid' : '0' , 'start_block' : '0' , 'state' : 'ONLINE' , 'type' : 'MASTER' , 'vid' : '1' }], 'shared' : True, 'size' : '1 GiB' , 'snapshots' : [], 'state' : 'ONLINE' , 'stripe_size' : 262144, 'type' : 'MASTER' , 'unlock_conditions' : [], 'use_crc' : True, 'users' : [[30, False]], 'volume_nodes' : [11]}]}    Example 5.2: Get information about volume with id "vid" Name Description Parameter Returns st_volume_info Return information about volume with id vid vid       >>> pprint.pprint(conn.job_exec( 'st_volume_info' , { 'params' : { 'vid' : 0}})) { 'result_code' : 0, 'result_desc' : 'Success' , 'result_jobid' : '11.98' , 'result_name' : 'OK' , 'result_output' : { 'app_io_enabled' : True, 'block_distribution' : 'vertical' , 'block_size' : '4 KiB' , 'bytes_per_block' : 4096, 'group' : 500, 'hdd_type' : 'disk1' , 'hdds_per_node' : 1, 'id' : '0' , 'int_io_enabled' : True, 'labels' : [ '#Name#DataVolume1' , 'pub:DB1_persistent' ], 'name' : 'DataVolume1' , 'nodes_list' : [{ 'id' : 11, 'unrecovered_segments' : 0}], 'num_master_nodes' : 1, 'owner' : 500, 'permissions' : 'rwx------' , 'priority' : 10, 'redundancy' : 1, 'segments' : [{ 'end_block' : '2621439' , 'index' : '0' , 'nid' : 0, 'partitions' : [], 'phys_nid' : 11, 'sid' : '0' , 'start_block' : '0' , 'state' : 'ONLINE' , 'type' : 'MASTER' , 'vid' : '0' }], 'shared' : True, 'size' : '10 GiB' , 'snapshots' : [], 'state' : 'ONLINE' , 'stripe_size' : '256 KiB' , 'type' : 'MASTER' , 'unlock_conditions' : [], 'use_crc' : True, 'users' : [[30, False]], 'volume_nodes' : [11]}}   other options: EXAStorage Volume Jobs     Name description Parameters st_volume_info Return information about volume with id vid vid st_volume_list List all existing volumes in the cluster. None st_volume_set_io_status Enable or disable application / internal io for volume app_io, int_io, vid st_volume_add_label Add a label to specified volume vid, label st_volume_remove_label Remove given label from the specified volume vid label st_volume_enlarge Enlarge volume by blocks_per_node vid, blocks_per_node st_volume_shrink Shrink volume by blocks_per_node vid, blocks_per_node st_volume_append_node Append nodes to a volume. storage.append_nodes(vid, node_num, node_ids) -> None vid, node_num, node_ids st_volume_move_node Move nodes of specified volume vid, src_nodes, dst_nodes st_volume_increase_redundancy Increase volume redundancy by delta value vid, delta, nodes st_volume_decrease_redundancy decrease volume redundancy by delta value vid, delta, nodes st_volume_lock Lock a volume vid optional: vname st_volume_lock Unlock a volume vid optional: vname st_volume_clear_data Clear data on (a part of) the given volume vid, num__bytes, node_ids optional: vname    Example 6: Working with backups Example 6.1: start a new backup Name Description Parameter Returns db_backup_start Start a backup of the given database to the given volume db_name, backup_volume_id, level, expire_time substitutes: dackup_volume_name       >>> conn.job_exec( 'db_backup_start' , { 'params' : { 'db_name' : 'DB1' , 'backup_volume_name' : 'RemoteVolume1' , 'level' : 0, 'expire_time' : '10d' }}) { 'result_name' : 'OK' , 'result_desc' : 'Success' , 'result_jobid' : '11.77' , 'result_code' : 0}   Example 6.2: abort backup Name Description Parameter Returns db_backup_abort Aborts the running backup of the given database db_name     >>> conn.job_exec( 'db_backup_abort' , { 'params' : { 'db_name' : 'DB1' }}) { 'result_name' : 'OK' , 'result_desc' : 'Success' , 'result_jobid' : '11.82' , 'result_code' : 0}   Example 6.3: list backups Name Description Parameter Returns db_backup_list Lists available backups for the given database db_name       >>> pprint.pprint(conn.job_exec( 'db_backup_list' , { 'params' : { 'db_name' : 'DB1' }})) { 'result_code' : 0, 'result_desc' : 'Success' , 'result_jobid' : '11.99' , 'result_name' : 'OK' , 'result_output' : [{ 'bid' : 11, 'comment' : '', 'dependencies' : '-' , 'expire' : '', 'expire_alterable' : '10001 DB1/id_11/level_0' , 'expired' : False, 'id' : '10001 DB1/id_11/level_0/node_0/backup_202007071405 DB1' , 'last_item' : True, 'level' : 0, 'path' : 'DB1/id_11/level_0/node_0/backup_202007071405' , 'system' : 'DB1' , 'timestamp' : '2020-07-07 14:05' , 'ts' : '202007071405' , 'usable' : True, 'usage' : '0.001 GiB' , 'volume' : 'RemoteVolume1' }]}    other options: Jobs to manage backups     Name description Parameters db_backups_delete Delete given backups of given database db_name, backup list (as returned by 'db_backup_list()') db_backup_change_expiration Change expiration time of the given backup files backup volume ID backup_files: Prefix of the backup files, like exa_db1/id_1/level_0 ) expire_time : Timestamp in seconds since the Epoch on which the backup should expire. db_backup_delete_unusable Delete all unusable backups for a given database db_name db_restore Restore a given database from given backup db_name, backup ID, restore type ('blocking' | 'nonblocking' | 'virtual access') db_backup_add_schedule Add a backup schedule to an existing database db_name, backup_name, volume, level, expire, minute, hour, day, month, weekday, enabled notes: * 'level' must be  int * 'expire' is string (use  common/util.str2sec to convert) 'backup_name' is  string (unique within a DB) db_backup_remove_schedule Remove an existing backup schedule  db_name, backup_name db_backup_modify_schedule Modify an existing backup schedule  db_name, backup_name   optional: hour, minute, day, month, weekday, enabled      We will continue to add more examples and we will add more options to this article. Additional References https://github.com/EXASOL/docker-db https://github.com/exasol/exaoperation-xmlrpc You can find another article about deploying a exasol database as an docker image in https://community.exasol.com/t5/environment-management/how-to-deploy-a-single-node-exasol-database-as-a-docker-image/ta-p/921

Background Enlarge EXAStorage disk(s) after changing disk size of the ec2 instances Prerequisites To complete these steps, you need access to the AWS Management Console and have the permissions to do these actions in EXAoperation Please ensure you have a valid backup before proceeding. The below approach works only with the cluster installation. How to enlarge disk space in AWS Stop all databases and stop EXAStorage in EXAoperation Stop your EC2 instances, except the license node (ensure they don’t get terminated on shutdown; check shutdown behavior http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ebs-expand-volume.html) Modify the disk on AWS console (Select Volume -> Actions -> Modify -> Enter the new size -> Click Modify) Ensure Storage disk size is set to “Rest” <EXAoperation node setting>, if d03_storage/d04_storage is not set to "Rest", set INSTALL flag for all nodes adjust the setting and set the ACTIVE flag for all nodes, otherwise nodes will be reinstalled during boot (data loss)! Start instances Start EXAStorage Enlarge each node device using the “Enlarge Button” in EXAoperation/EXAStorage/n00xx/h000x/ Re-Start database Additional References https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ebs-modify-volume.html  

Background This article describes the calculation of the optimal (maximum) DB RAM on a: 4+1 system with one database (dedicated environment) 4+1 system with two databases (shared environment) The calculation of the OS Memory per Node stays the same for both environments. Shared environments are not recommended for production systems. Example Setup: The 4+1 cluster contains four active data nodes and one standby node. Each node has 384GiB of main memory. How to calculate Database RAM OS Memory per Node It is vital for the database that there is enough memory allocatable through the OS. We recommend using at least 10% of the main memory on each node. This prevents the nodes from swapping on high load (many sessions). Main Memory per Node * 0.1 = OS Memory per Node 384 * 0.1 = 38,4 -> 38GiB In order to set this value, the database needs to be shut down. EXAoperation 'Configuration > Network' - "OS Memory/Node (GiB)" Maximum DB RAM (dedicated environment) (Main Memory per Node - OS Memory per Node) * Number of active Nodes = Maximum DB RAM Example: 4 x data nodes with 384GiB (Main Memory per Node) - 38GiB (OS Memory per Node) (384GiB - 38 GiB) * 4 = 1380GiB Maximum DB RAM (shared environment) Example Database "one" on four data nodes (exa_db1) Database "two" on two data nodes (exa_db2) As before the "Maximum DB RAM" is 1380GiB. With two databases sharing the Maximum DB RAM, we need to recalculate and redistribute it. Maximum DB RAM / Number of Databases = Maximum DB RAM per database 1380GiB / 2 = 690GiB For database "one" (exa_db1), which is running on all four nodes 690GiB DB RAM can be configured. The smaller database "two" (exa_db2) is running on two nodes, therefore "Maximum DB RAM per database" needs to be divided by the number of data nodes it's running on (2). Maximum DB RAM per database / Number of active Nodes = Maximum DB RAM per database 690GiB / 2 = 345GiB       Additional References Sizing Considerations  

Background In this tutorial we will show you how to enable Auditing on a Docker-based Exasol system   Prerequisites Access to the Docker host   How to enable Auditing on a docker-based Exasol system In this section we will show you how to: Edit the EXAConf file and add the Auditing parameter Commit the changes   Step 1. Log in to the Docker host and edit your EXAConf file Log in to your Docker host via ssh (or console), log in to your Exasol container: $ docker exec -it <your_exasol_container_name> /bin/bash Edit the EXAConf file with your preferred text editor and add the following line to your database parameters:  EnableAuditing = yes The database section of your EXAConf file should look like this: [DB : DB1] Version = 7.0.3 MemSize = 2 GiB Port = 8563 Owner = 500 : 500 Nodes = 11 EnableAuditing = yes NumActiveNodes = 1 DataVolume = DataVolume1   Step 2. Commit the changes Once the changes are done, run the following command inside the container: $ sed -i '/Checksum =/c\ Checksum = COMMIT' /exa/etc/EXAConf If you are running a cluster then be sure to also sync the file: $ cos_sync_files /exa/etc/EXAConf   Step 3. Restart the container(s) Once the changes are done, restart the container(s) on you Docker hosts $ docker restart <your_exasol_container_name>   Step 4. Verify the parameter's value Once the container is restarted, log in to the container and run the following command: $ dwad_client list The output should be similar to:  

How to install Zabbix Agents via XML-RPC Step 1 Upload Plugin.Administration.Zabbix-3.0.10-1.0.0-2018-09-25.pkg to EXAoperation Login to EXAoperation (User privilege Administrator) Upload pkg Configuration>Software>Versions>Browse>Submit Step 2 Connect to EXAoperation via XML-RPC (this example uses Python) We suggest to use an interactive python session to install this plugin. Just copy the following snippet, fill out "userName", "password" and "hostName" and post it directly into your interactive session. import ssl from urllib import quote_plus from xmlrpclib import ServerProxy from pprint import pprint userName = "admin" password = "admin" hostName = "10.0.0.10" def XmlRpcCall(urlPath = ''): url = 'https://%s:%s@%s/cluster1%s' % (quote_plus(userName), quote_plus(password), hostName, urlPath) if hasattr(ssl, 'SSLContext'): sslcontext = ssl.SSLContext(ssl.PROTOCOL_TLSv1_2) sslcontext.verify_mode = ssl.CERT_NONE sslcontext.check_hostname = False return ServerProxy(url, context=sslcontext) else: return ServerProxy(url) cluster = XmlRpcCall('/') Step 3 Show plugin functions >>> cluster.showPluginList() ['Administration.Zabbix-3.0.10-1.0.0'] >>> pprint(cluster.showPluginFunctions('Administration.Zabbix-3.0.10-1.0.0')) {'INSTALL': 'Install and start Zabbix agent', 'READ_CONF': 'Read /etc/zabbix/zabbix_agentd.conf configuration file', 'RESTART': 'Restarts Zabbix agent', 'START': 'Start Zabbix agent manually', 'STOP': 'Stop Zabbix agent manually', 'UNINSTALL': 'Uninstall Zabbix agent', 'WRITE_CONF': 'Write /etc/zabbix/zabbix_agentd.conf configuration file'} Step 4 >>> for node in cluster.getNodeList(): ... status, ret = cluster.callPlugin('Administration.Zabbix-3.0.10-1.0.0', node, 'INSTALL') ... print node, ret ... Step 5 >>> config = file('your/local/machine/zabbix_agentd.conf').read() >>> for node in cluster.getNodeList(): ... status, ret = cluster.callPlugin('Administration.Zabbix-3.0.10-1.0.0', node, 'WRITE_CONF', config) ... print node, status Step 6 >>> config = file('your/local/machine/zabbix_agentd.conf').read() >>> for node in cluster.getNodeList(): ... status, ret = cluster.callPlugin('Administration.Zabbix-3.0.10-1.0.0', node, 'RESTART') ... print node, status

Background In this article, you can find how to calculate the available database disk space. Prerequisites To calculate the available database disk space we need some informations first: available disk sizes on nodes how many volumes does exist the size of these volumes nodes are used by these volumes the redundancy of these volumes Explanation Let's explain the calculation with an example: available disk space on the "d03_storage" partition on all nodes:   Node Available Disk Size (GiB) n0011 1786 n0012 1786 n0013 1786 n0014 1786 existing volumes, sizes, and redundancies: Volume Type Size Redundancy v0000 Archive 1024 2 v0001 Data 320 2 v0002 Data, tmp 60 1 v0003 Data 120 2 v0004 Data, tmp 15 1 Calculation of the free disk space   The first step is to divide the required size of the volumes by the number of used nodes to get the segment size (example for v0000): Size / Number of Nodes = Segment Size 1024 GiB / 3 Nodes = 341.3 GiB/Node Next step is to multiply the segment size by the redundancy of the volume: Segment Size * Redundancy = Used Disk Space per Node 341.3 GiB/Node * 2 = 682.6 GiB/Node This has to be done for every volume. After that we're able to fill a table with the used disk space per node like this:       Now we can simply substract the used sizes from the available disk size per node: n0011: 1786 GiB - 213 GiB - 20 GiB = 1553 GiB n0012: 1786 GiB - 683 GiB - 213 GiB - 20 GiB = 870 GiB n0013: 1786 GiB - 683 GiB - 213 GiB - 20 GiB - 120 GiB - 7 GiB = 743 GiB n0014: 1786 GiB - 683 GiB - 120 GiB - 7 GiB = 976 GiB The minimum value over all nodes gives us the free available space: 743 GiB with a redundancy of 1. The reason for the minimum is that all segments of a volume need to have the same size.   Calculation of the available space in point of view of the database instance The database instance is able to control the size of its own data volume: data volumes can grow and they can be shrunken. Shrinking of a data volume is an expensive operation and creates a high amount of disk and network usage. To limit the usage the process will only shrink a few blocks after a defined amount of COMMIT statements. That is the reason why data volumes won't shrink immediately when data in the database has been deleted. This results in the data volume usually aren't used completely by the database and there is an amount of free space: Database Volumes v0001 + v0002 v0001 Used 200 GiB Unused 120 GiB Redundancy=2 2 * 120 GiB = 240 GiB Free 2 * 200 GiB = 400 GiB Used v0002 Used 30 GiB Unused 20 GiB Redundancy=1 1 * 20 GiB = 20 GiB Free 1 * 30 GiB = 30 GiB Used         260 GiB Free 430 GiB Used   Now we can calculate the available space for the database which is using the volumes v0001 and v0002: Free = available space for volumes + available space inside the DB volume Free = 743 GiB + 260 GiB = 1003 GiB Free (with a redundancy of 1) Usage = (1 - (free space / (free space + used space))) * 100% Usage = (1 - (1003 GiB / (1003 GiB + 430 GiB))) * 100% = 30% How to get the necessary data for monitoring the free space To monitor the free space of an EXASolution database instance we need the following information: the available disk space of the storage partition all EXAStroage volumes all sizes of these volumes the redundancy of this volume the data volumes used by the database instance we want to check (data + temp) the usage of those data volumes All those data are provided by the EXAoperation XMLRPC interface since EXASuite 4.2. You can use the following functions: node.getDiskStates() information about the available space of the storage partition database.getDatabaseInfo() volumes and its usages used by the database storage.getVolumeInfo(volume) volume sizes and redundancies   Please check the EXAoperation user manual for a full description of how to use those functions. You can find this manual on our user portal: https://www.exasol.com/portal/display/DOWNLOAD/6.0 Additional References https://www.exasol.com/portal/display/DOWNLOAD/6.0

Background In this tutorial we are going to show you how to create a BucketFS and Buckets inside it for Exasol on Docker.   Prerequisites Access to the Docker host Access to firewall/cloud management console to open port (in our example, AWS will be used) Very basic knowledge of the curl command   How to create a BucketFS and Buckets inside it In this section, we will show you the whole process of: Creating a BucketFS Creating a bucket inside it (Optional) Opening the required port(s) in the AWS management console for newly created BucketFS Committing the Changes Uploading some files via the curl command   Step 1. Go to your Docker host, log in to the container and create a BucketFS Log in to your host via ssh (or console) and get inside your Exasol on Docker container: $ docker exec -it <your_exasol_container_name> /bin/bash You can find the container's name by running docker ps. Once inside the container run the following command to create a BucketFS: $ exaconf add-bucketfs --name newbucketfs* --http-port 6932** --https-port 0*** --owner 500:500 * - This will be the name of your new BucketFS ** - This will be the port for communicating via HTTP *** - This will be the port for communicating via HTTPS This will create the BucketFS config required in the /exa/etc/EXAConf file.   Step 2. Create a bucket inside your newly created BucketFS Once the BucketFS is created run the following command to create the bucket inside the aforementioned BucketFS: $ exaconf add-bucket --name newbucketfs-bucket* --bfs-name newbucketfs** --read-passwd $(echo -n "newread"*** | base64) --write-passwd $(echo -n "newwrite"**** | base64) * - This will be the name of your new Bucket ** - This is the name of your newly created BucketFS *** - This will be your new password for read (r) **** - This will be your new password for write (w) Once the bucket is created, open the /exa/etc/EXAConf file and check if you can see the newly added BucketFS/Bucket there: $ cat /exa/etc/EXAConf Output should be similar to:   Step 3. (Optional) Open the firewall port for the new BucketFS (in this example, AWS) Go to the Security Group that you used while deploying the Docker node and add the port that you used for the new BucketFS:   1 - Go to the Secuirty Group and edit the inbound rules 2 - When in the security group rules' configuration press Add Rule 3 - Select Custom TCP add the port number and the source address 4 - Save the changes If you are using an other cloud provider please check their documentation on how to open ports for services.   Step 4. Committing the Changes Once the changes are done, run the following command inside the container: $ sed -i '/Checksum =/c\ Checksum = COMMIT' /exa/etc/EXAConf Once that is done, run the following command: $ exaconf commit Once the commit is done your new bucket should be available via the curl command.   Step 5. Using the curl command to upload/download/delete To upload a file via curl use: $ curl -X PUT -T testfile.jar http://w:newwrite@<docker_host_ip>:6932/newbucketfs-bucket/testfile.jar To delete a file via curl use: $ curl -X DELETE http://w:newwrite@34.243.141.132:6932/newbucketfs-bucket/testfile.jar To list the contents of the bucket: $ curl http://r:newread@<docker_host_ip>:6932/newbucketfs-bucket/    

Background Get debug information and log files from docker based systems Prerequisites Access to host where is the docker container(s) running How to get debug information In order to get debug information and log files "exasupport" tool can be used. There is no installation required, the tool comes preinstalled with docker image Step 1 Launch to launch a Bash terminal within a container: docker exec -it {CONTAINER_NAME} bash Step 2 "exasupport --help" command will return detailed information about the command [root@n11 /]# exasupport --help Usage: exasupport [options] Retrieve support information Options: -h, --help show this help message and exit -d DEBUGINFO, --debug-info=DEBUGINFO Debuginfo to retrieve, separated by comma: 1 = EXAClusterOS logs, 2 = Coredumps, 3 = EXAStorage metadata or 0 for all -s START_DATE, --start-time=START_DATE Start time of logs (YYYY-MM-DD [HH:MM]) -t STOP_DATE, --stop-time=STOP_DATE Stop time of logs (YYYY-MM-DD [HH:MM]) -e EXASOLUTION, --exasolution=EXASOLUTION EXASolution logs (System names, separated by comma or "All databases") -x EXASOLUTION_LOG_TYPE, --exasolution-log-type=EXASOLUTION_LOG_TYPE EXASolution log type, separated by comma (1 = All, 2 = SQL processes, 3 = Server processes) -i SESSION, --session=SESSION Get logs from specific sessions, separated by comma -b BACKTRACES, --backtraces=BACKTRACES Process backtraces 1 = EXASolution server processes, 2 = EXASolution SQL processes, 3 = EXAClusterOS processes, 4 = ETL JDBC Jobs -n NODES, --nodes=NODES Nodes (default: all online nodes) -a, --only-archives Only download archives -f, --only-open-files Only download open files -m, --estimate Only estimate size of debug information -o OUTFILE, --outfile=OUTFILE Output file Step 3 An example to get all server processes and sql log file for specific session ID and date period you can use: exasupport -d 0 -s 2020-10-25 -t 2020-10-26 -e {DATABASE_NAME} -x 1 -i {SESSION_ID} If you don't know the database name you can use "dwad_client shortlist" command to get it. Step 4 The command above will create a tar.gz file in /exa/tmp/support folder. [root@n11 /]# exasupport -d 0 -s 2020-10-25 -t 2020-10-26 -e {DATABASE_NAME} -x 1 -i {SESSION_ID} Successfully stored debug information into file /exa/tmp/support/exacluster_debuginfo_2020_10_26-11_20_01.tar.gz [root@n11 /]# ls -lrt /exa/tmp/support/ total 492 -rwxr-xr-x 1 root root 503265 Oct 26 11:20 exacluster_debuginfo_2020_10_26-11_20_01.tar.gz [root@n11 /]# Step 4 You can access to this folder from the host system. If you used the article https://community.exasol.com/t5/environment-management/how-to-deploy-a-single-node-exasol-database-as-a-docker-image/ta-p/921 then all of your configuration and log files stored in $CONTAINER_EXA (/root/container_exa) folder. root@host001:~# ls $CONTAINER_EXA/tmp/support/ exacluster_debuginfo_2020_10_26-11_20_01.tar.gz Additional References https://community.exasol.com/t5/environment-management/how-to-deploy-a-single-node-exasol-database-as-a-docker-image/ta-p/921

This article goes through how to synchronize archive volumes between clusters using a Python UDF

This article shows you how to allow internet access for the Community Edition running on VMWare

Backup Synchronization Plugin Installation Overview The backup synchronization process involves synchronization of data and metadata between clusters on different networks. This document provides you with the steps on how to install the Backup Synchronization plugin on two clusters using XML-RPC. XML-RPC lets you establish communication between the two clusters quickly and easily. Exasol’s XML-RPC support is implemented using the xmlrpclib (http://docs.python.org/library/xmlrpclib.html) library that is included with Python 2.2 and later. Recommendations Knowledge of XML-RPC Knowledge of SSH Authentication Plugin Installation You can follow the below steps to install the backup synchronization plugin on clusters. As an example, let us consider two clusters EDU01 and EDU02. Step 1: Uninstall Older Plugins Versions on All Clusters Any older plugin installed on the clusters must be uninstalled before you can install the latest plugin. The following sample script deactivates and uninstalls the older plugins on the two clusters EDU01 and EDU02.       Sample Script Sample Script to Uninstall Plugin on EDU001from xmlrpclib import Server as xmlrpc import ssl from pprint import pprint as pp from base64 import b64encode server = xmlrpc( 'https: //FTPbackup:***PW***@10.60.101.10/cluster1/' ) pp(server.showPluginList()) # [ 'Administration.BackupSync-1.0.1' ] pname = 'Administration.BackupSync-1.0.1' nlist = server.getNodeList() pp([[node] + server.callPlugin(pname, node, 'STATUS' , '') for node in nlist]) ##[[ 'n0011' , 0, 'activated' ], ## [ 'n0012' , 0, 'activated' ], ## [ 'n0013' , 0, 'activated' ]] pp([[node] + server.callPlugin(pname, node, 'DEACTIVATE' , '') for node in nlist]) pp([[node] + server.callPlugin(pname, node, 'UNINSTALL' , '') for node in nlist]) exit()  Sample Script to Uninstall Plugin on EDU02from xmlrpclib import Server as xmlrpc import ssl from pprint import pprint as pp from base64 import b64encode server = xmlrpc( 'https: //FTPbackup:***PW***@10.60.102.10/cluster1/' ) pp(server.showPluginList()) pname = 'Administration.BackupSync-1.0.1' nlist = server.getNodeList() pp([[node] + server.callPlugin(pname, node, 'DEACTIVATE' , '') for node in nlist]) pp([[node] + server.callPlugin(pname, node, 'UNINSTALL' , '') for node in nlist]) exit()    Note: All credentials and files names used in the sample scripts are example credentials and file names. Please replace them with the correct one for your clusters. Step 2: Remove Plugin from EXAoperation Once you have deactivated and uninstalled the previously installed plugin, you must remove the entry of the plugin from EXAoperation for all the clusters. Doing this will clear the folders directories previously created.  You can follow the below steps to remove the plugin from EXAoperation: Log in to EXAoperation for the desired cluster and navigate to Software under Configuration On the right-hand side, select the checkbox next to the plugin you want to remove. Click Delete to remove the plugin. Step 3: Upload Latest Plugin to EXAoperation You can receive the latest Backup Synchronization Plugin by contacting the Exasol support team. To be able to use the Backup Synchronization Plugin, it must be first uploaded to EXAoperation and then installed on the clusters. The lasted plugin must be uploaded through EXAoperation on all clusters (in this example, it must be uploaded to EDU01 and EDU02) by following these steps: In EXAoperation, navigate to Software under Configuration On the right-hand side of the screen, click Choose File next to Software Update File and click Submit. Once the upload is complete, you can see the plugin is listed in the below section of the screen.  Step 4: Install the Latest Plugin After the latest plugin is uploaded through EXAoperation (refer to step 3), you must install it on both the clusters (EDU01 and EDU02). The following sample scripts will install the latest plugins on the clusters.  Sample Script to Install Plugin on EDU01nlist = server.getNodeList() pp(server.showPluginFunctions(pname)) { 'ACTIVATE' : 'Activate plugin.' , 'DEACTIVATE' : 'Deactivate plugin.' , 'GETLOG' : 'Return the log output.' , 'INSTALL' : 'Install plugin.' , 'SSHKEY' : 'Prepare public key for ssh.' , 'STATUS' : 'Show status of plugin (not installed, activated, not activated).' , 'UNINSTALL' : 'Install plugin.' , 'UPLOAD_CONFIG' : 'Upload configuration.' , 'UPLOAD_KEY' : 'Upload public key for ssh' } pp([[node] + server.callPlugin(pname, node, 'INSTALL' , '') for node in nlist]) pp([[node] + server.callPlugin(pname, node, 'STATUS' , '') for node in nlist]) Sample Script to Install Plugin on EDU02nlist = server.getNodeList() pp([[node] + server.callPlugin(pname, node, 'INSTALL' , '') for node in nlist]) pp([[node] + server.callPlugin(pname, node, 'STATUS' , '') for node in nlist]) Step 5: Create a User in EXAoperation You must create a new user with admin role. The user account will be used to access the Exasol backup via FTP. You can use any existing user account with admin role, however, creating a new user for this purpose makes it easy to segregate specific tasks to users. Note: This user must be created on both the clusters. In this case, a user is created through EXAoperation for EDU01 and EDU02 clusters. To create a new user: In EXAoperation, navigation to Configuration > Access Management. Click Users tab and click Add. Complete the following information for the new user. For example: Login: FTPBackup Title: 4FTPbackup Description: 4FTPbackup Identification by: Internal Password: Enter the desired password Click Add. A user is created. The new user created will have the role of ‘User’ by default. To set the role as ‘Administrator’ for this new user, click the Roles tab and select Administrator from the Roles dropdown list. Click Apply. Step 6: Create Archive Volume from EXAoperation An archive volume must be created on all the clusters (in this example, an archive volume must be created on EDU01 and EDU02) for storing the database backups. As a rule of thumb, for up to 1-3 nodes in a cluster which are also the source nodes, at least one archive node on the destination cluster is recommended. For more information on Archive volume creation, see Create Archive Volume. Step 7: Create and Upload Configuration File To establish a connection between the clusters, you need to create a configuration file which contains the following information: The database names The start cycles – Unix-like cron format Local URL to the backup Remote URL to the backup Remote nodes and SSH The configuration file enables you to establish a connection between two clusters. However, if you are connecting to more than one cluster, then you need an exclusive configuration file to connect to each cluster. For example, you need a configuration file to establish a connection between EDU01 and EDU02. Now, if you have a third cluster EDU03, and you want to establish a connection between EDU02 and EDU03, or from EDU03 to EDU01. You must create a new configuration file for each of these connections. The following are sample configuration files for the clusters:  Sample Configuration File for EDU01 #Filename on SupportHost: /home/ssh/client.cfg Connection1 { DATABASE_NAME = exa_db1 START_CYCLE = */2 * * * * LOCAL_URL = ftp: //FTPbackup:**PW**@%s/v0003 REMOTE_URL = ftp: //FTPbackup:**PW**@%s/v0002 REMOTE_NODES = 10.60.102.11; 10.60.102.12 Verbose = true SSH = client }  Sample Configuration File for EDU02#Filename on SupportHost: /home/ssh/server.cfg Connection1 { Verbose = true SSH = server } Note: The connection name in the configuration files must be the same for the source and destination clusters. Setting the value for Verbose to true in the configuration file enables verbose logging – which captures more details on  syncing of files – in the EXACluster Monitoring Service.  If this not enabled only errors are logged. This option is disabled by default.   The configuration files created must be uploaded to the clusters. The following sample scripts can be used to upload the configuration files:  Sample Script to Upload Configuration File on EDU01nlist = server.getNodeList() config = b64encode(open( '/home/ssh/client.cfg' ).read()) pp([[node] + server.callPlugin(pname, node, 'UPLOAD_CONFIG' , config) for node in nlist])  Sample Script to Upload Configuration File on EDU02nlist = server.getNodeList() config = b64encode(open( '/home/ssh/server.cfg' ).read()) pp([[node] + server.callPlugin(pname, node, 'UPLOAD_CONFIG' , config) for node in nlist])  Step 8: Create SSH Key SSH key can be used to establish secure connections between the clusters. The following sample script generates the SSH key on the client machine with respect to the connection specified in the configuration file.  SSH Keygen Sample Scriptnlist = server.getNodeList() pp(server.showPluginFunctions(pname)) pp([[node] + server.callPlugin(pname, node, 'SSHKEY' , 'Connection1' ) for node in nlist]) >>> pp([[node] + server.callPlugin(pname, node, 'SSHKEY' , 'Connection1' ) for node in nlist]) [[ 'n0011' , 0, 'ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEA8V3Au7gr7jmoWIZlbTTNb/ 3Lkku44mlxeC/gTHHvFjgYQSjtFvWZl7i3NIQqrJk4ApQDcqBTRLT8/VNT4PHWyRt+3I ImmGH0D3V9rl+NmCQVjJh/sSKttI5cMR3P6JSg76mhaIjkKddnILHIJVW3R1Q2g+bgr5 R1qaCXQghb9M/mdHdbfTkk7zI41tAchlZrjbcRfRwOMAYOGSHIdegB1qs1kMBbEivcS9 3sKCyXG46dLchQspIeShdwHFjCJDDRYGIWiH4N6M2P50PjGM4lQTyFrJzAD89LYV IfMiN6d+2XTCYCy7W0uezp7OqwBsp2UY31omw9jtSqDn3g5KOIZQ== root@n0011.c0001.exacluster.local']] >>> Note: The ssh key generated can be used only with the connection it is linked to.  A public key is created for each node.  This public key must be copied and uploaded to the remote server to be able to use SSH authentication. Step 9: Upload SSH Key The public key generated for the node must be uploaded to the remote server to be able to authenticate using SSH. Create a file (for example - /home/ssh/connection1_ssh_key_file.key) and copy the key generated in the previous step for the node into this file. The sample script below uploads SSH key for the specific connection to the remote server (in this example, the SSH key is uploaded to EDU02).  Sample Script to Upload SSH Key on EDU02nlist = server.getNodeList() # Upload KEY key = b64encode(open( '/home/ssh/Connection1_ssh_key_file.key' ).read ()) pp([[node] + server.callPlugin(pname, node, 'UPLOAD_KEY' , key) for node in nlist]) Activate SSH key for "Connection1" pp([[node] + server.callPlugin(pname, node, 'SSHKEY' , 'Connection1' ) for node in nlist]) Step 10: Activate Connection between the two clusters Once you have uploaded the SSH key file, the connections on the clusters must be activated to be able to establish a secure connection between them.  Sample Script to Activate Connection on EDU01nlist = server.getNodeList() # Upload KEY key = b64encode(open( '/home/ssh/Connection1_ssh_key_file.key' ).read ()) pp([[node] + server.callPlugin(pname, node, 'UPLOAD_KEY' , key) for node in nlist]) # Activate SSH key for "Connection1" pp([[node] + server.callPlugin(pname, node, 'ACTIVATE' , 'Connection1' ) for node in nlist])  Step 11: Deactivate Connection between two cluster In case you want to deactivate this connection, you can follow the below sample script: Sample Script to Deactivate Connection on EDU01nlist = server.getNodeList() pp([[node] + server.callPlugin(pname, node, 'DEACTIVATE' , 'Connection1' ) for node in [ 'n0011' , 'n0012' ]])   

WHAT WE'LL LEARN? In this article you will learn how to update a Docker-based Exasol system. HOW-TO 1. Ensure that your Docker container is running with persistent storage. This means that your docker run command should contain a -v statement, like the example below: $ docker run --detach --network=host --privileged --name <container_name> -v $CONTAINER_EXA:/exa exasol/docker-db:6.2.8-d1 init-sc --node-id <node_id> 2. Log in to your Docker container's BASH environment: $ docker exec -it <container_name> /bin/bash  3. Stop the database, storage services and exit the container: $ dwad_client stop-wait <database_instance> $ csctrl -d $ exit 4. Stop the container: $ docker stop $container_name 5. Rename the existing container. Append with old, so that you know that this is the container which you won't be using anymore $ docker rename <container_name> <container_name_old> 6. Create a new tag for the older container image: $ docker tag exasol/docker-db:latest exasol/docker-db:older_image 7. Remove the "latest" tag for the "older_image": $ docker rmi exasol/docker-db:latest 8. Pull the latest Docker-based Exasol image: $ docker image pull exasol/docker-db:latest 8.1. Or pull the specific version you want. You can view the available versions and pull one of them with the commands bellow: $ wget -q https://registry.hub.docker.com/v1/repositories/exasol/docker-db/tags -O - | sed -e 's/[][]//g' -e 's/"//g' -e 's/ //g' | tr '}' '\n' | awk -F: '{print $3}' ... 6.2.3-d1 6.2.4-d1 6.2.5-d1 ... $ docker image pull exasol/docker-db:<image_version>  9. Run the following command to execute the update: $ docker run --privileged --rm -v $CONTAINER_EXA:/exa -v <all_other_volumes> exasol/docker-db:latest update-sc or $ docker run --privileged --rm -v $CONTAINER_EXA:/exa -v <all_other_volumes> exasol/docker-db:<image_version> update-sc Output should be similar to this: Updating EXAConf '/exa/etc/EXAConf' from version '6.1.5' to '6.2.0' Container has been successfully updated! - Image ver. : 6.1.5-d1 --> 6.2.0-d1 - DB ver. : 6.1.5 --> 6.2.0 - OS ver. : 6.1.5 --> 6.2.0 - RE ver. : 6.1.5 --> 6.2.0 - EXAConf : 6.1.5 --> 6.2.0  10. Run the container(s) the same way as you did before. Example: $ docker run --detach --network=host --privileged --name <container_name> -v $CONTAINER_EXA:/exa exasol/docker-db:latest init-sc --node-id <node_id> 11. You can check the status of your booting container (optional): $ docker logs <container_name> -f 12. You can remove the old container (optional): $ docker rm <container_name_old>

Background This article guides you through the steps required to configure the automatic time synchronization via NTP servers on EXASolution clusters. Prerequisites If the time shift is greater than one hour it is required to set the time at first manually, which is described in Manually setting time via EXAoperation . There is no downtime need if the time shift is less than one hour. ⚠️ Please note that: The cluster nodes constantly exchange configuration and vitality information and depend on proper time synchronization. While it is possible to manually set the time on EXASolution clusters, it is highly recommended to supply NTP servers for time synchronization. The tasks performed in EXAoperation requires a user with at least "Administrator" privileges. Please ensure that the NTP servers provided to EXAoperation are connectible from the cluster (port filtering, firewalling) and can be addressed by name or IP address (i.e.: hostnames must be resolvable through DNS). Check the gap between the currently configured time and the actual time Procedure 1.1 Check currently configured time on the cluster Open 'Services > Monitoring' Check the following value in the field: Open 'Configuration > Network' Check the value in the field "Time Zone" Make a rough estimation of the mismatch. ⚠️ If the mismatch is greater than one hour please set the time manually, which is described in Manually setting time via EXAoperation . 1.2 Configure NTP Server Open 'Configuration > Network' Click on "Edit"  Add the IP-addresses of the server  Apply the new configuration 1.3. Synchronize time on the cluster Open 'Service > Monitoring' Click on "Synchronize Time" Now the cluster will synchronize the time with the configured NTP servers constantly.