InfiniDB®

Administrator’s Guide

 

 

 

 

 

 

 

                             

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Release:  4.5

Document Version:  4.5-1


 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

InfiniDB Administrator’s Guide

March 2014

Copyright © 2014 InfiniDB Corporation. All Rights Reserved.

 

InfiniDB, the InfiniDB logo and any other product or service names or slogans contained in this document are trademarks of InfiniDB and its suppliers or licensors, and may not be copied, imitated or used, in whole or in part, without the prior written permission of InfiniDB or the applicable trademark holder.

 

Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of InfiniDB.

 

InfiniDB may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering  subject matter in this document. Except as expressly provided in any written license agreement from InfiniDB, the furnishing of this document does not give you any license to these patents, trademarks copyrights, or other intellectual property. The information in this document is subject to change without notice. InfiniDB shall not be liable for any damages resulting from technical errors or omissions which may be present in this document, or from use of this document.
Contents

Introduction. 8

Audience. 8

List of Documentation. 8

Obtaining documentation. 8

Documentation feedback. 8

Additional resources. 8

InfiniDB Console Overview. 9

Logging on and off the InfiniDB Console. 9

InfiniDB Console tips. 10

Help command. 10

Case sensitivity. 10

Recall command history. 11

Command repeat option. 11

Linux and InfiniDB Console command prompts. 11

Complete list of InfiniDB commands. 12

System Maintenance. 14

Upgrading InfiniDB. 14

InfiniDB software. 14

Upgrading InfiniDB. 14

Storage Information. 14

System Operations. 14

Stopping the system.. 15

Starting the system or modules. 15

Restarting the system.. 15

Shutting down the system.. 15

Disabling and Enabling System Modules. 16

System Status. 17

Viewing system status. 17

Viewing process status. 18

Viewing network configuration. 19

Viewing module information. 19

Monitoring module usage and threshholds. 20

Module disk usage. 21

Memory usage. 22

Swap space usage. 22

Disk thresholds. 23

CPU thresholds. 23

Monitoring alarms. 24

Viewing active alarms. 24

Viewing archived alarms. 25

Viewing top CPU users. 27

Monitoring resource usage. 28

Configuring System Status Monitoring. 29

Management console overview. 29

InfiniDB configuration files. 29

Linux shell 29

Configuring system.. 29

Setting module heartbeats. 31

Configuring Disk thresholds. 31

Configuring archived transaction log periods. 32

Configuring modules. 33

Configuring module CPU thresholds. 33

Configuring module disk usage. 33

Configuring swap space usage. 33

Configuring alarms. 34

Steps to configure alarms. 34

Configuring logging. 34

Enabling and disabling logging. 35

Loading Data. 36

Import overview. 36

Estimating load sizes. 36

Import Tasks. 36

Import process. 36

Directory structure. 36

Importing as non-root user 37

Copy source files. 37

Source file formats. 37

Source file names. 37

Import directory. 38

Import Usage. 38

Importing multiple tables. 38

Distributed Imports. 38

Bulk Load from a central location (Mode 1) 38

Single Bulk Load Command, Partitioned Source (Mode 2) 39

Independent Bulk Load Command, Partitioned Source (Mode 3) 40

Mode Availability. 41

Generate job file. 42

Import data. 44

Using cpimport 44

STDIN Processing. 48

Bulk Insert with Select From Table(s) 49

Bulk Insert with Binary Source. 49

Enclosed By (-E) and Escape Character (-C) Usage. 52

Advanced Data Mapping to Table. 53

Data Mapping Example. 53

Troubleshooting import 54

Location of error log and rejected data. 55

Data mismatch. 55

Number of error rows exceeds threshhold. 55

Transactions. 56

Transaction overview. 56

ACID Compliance. 56

Performing Backup and Recovery. 58

Backing up data. 58

Suspending database writes. 58

Backing up front-end database. 59

Backing up InfiniDB database. 59

Resuming database writes. 59

Recovering from a backup. 60

Restoring front-end database. 60

Restoring InfiniDB database. 60

Dropping the InfiniDB database. 61

Performance Tuning. 62

Query Tuning Guidelines - Data Access Operations. 62

Operations used by InfiniDB include: 62

Gathering Performance Metrics - calgetstats() 62

Gathering Performance Metrics. 63

Using /var/log/Calpont/debug.log. 63

Using calsettrace(1); 63

Using /var/log/mysqld.log. 64

Understanding Extent Map Range Partitioning. 64

Scaling. 64

For performance. 64

For concurrency. 64

For data capacity. 64

Configuring Modules. 65

Before configuring modules. 65

Module IDs. 65

Automatic configuration. 65

Manual configuration. 66

Adding DBRoots. 66

Creating the physical dbroot 66

Assign DBRoots to Performance Module. 66

Moving DBRoots. 68

Adding modules. 68

Removing modules. 70

Granting Access for Users. 71

Cross-Engine Table Access. 72

User Resource Allocation. 73

User Priority Management 73

Enabling User Priorities. 73

User Priority Processing. 74

Query Performance Monitoring. 75

Enablement of Query Statistics Collection. 75

Query Statistics table. 75

Column Definitions. 76

Query Statistics Viewing. 77

Operating Mode. 79

Decimal to Double Math. 80

Enable/Disable Decimal to Double Math. 80

Decimal Scale. 81

Enable/Disable Decimal Scale. 81

Set Decimal Scale Level 81

Compression Mode. 82

Local PM Query. 84

Enable/Disable Local PM Query. 84

Partition Management 85

System Utilities. 86

configxml.sh. 86

colxml 86

cpimport 86

viewtablelock. 86

cleartablelock. 87

healthCheck. 87

redistributeDB. 87

calpontSupport 87

databaseSizeReport 90

Using UTF-8 Character Sets. 93

UTF-8 Character Set 93

my.cnf 93

Calpont.xml 93

Importing Data. 93

Known Issues and Limitations. 94

Troubleshooting. 95

ERROR 1070 (42000): Too many key parts specified; max 0 parts allowed. 95

UM memory exceeded error / distributed join performance. 95

Error in Aggregation Data Overflow. 95

Circular join detected error 95

Imports executing slower with concurrent queries. 96

Delay in data retrieval immediately after a CREATE TABLE. 96

Lost connection when using stdin with cpimport 97

Appendix A - 3rd Party Licenses. 98

GNU GENERAL PUBLIC LICENSE. 98

Preamble. 98

TERMS AND CONDITIONS FOR COPYING, DISTRIBUTION AND MODIFICATION. 99

NO WARRANTY. 102

END OF TERMS AND CONDITIONS. 102

RSA Data Security. 103

Paul E. Jones. 104

Apache. 104

Gary S. Brown. 107

Brian M. Clapper 107

Net-SNMP. 108

MySQL AB / Sun Microsystems. 115

Lasse Mikkel Reinhold. 115

Glossary. 116

ad-hoc query. 116

business intelligence (BI) 116

InfiniDB Console. 116

column-based relational database. 116

COTS (commercial off the shelf components) 116

cron (Command Run ON) 116

data definition language (DDL) 116

data manipulation language (DML) 116

data warehouse. 117

DDL (See data definition language) 117

DML (See data manipulation language) 117

DNS (See domain name server) 117

domain. 117

domain name. 117

domain name server (DNS) 117

gateway. 117

host name. 117

IP address. 117

NIC (network interface card) 117

NMS (network monitoring system) 117

redundant array of independent disks (RAID) 118

SSH (secure shell) 118

tokenization. 118

 


Introduction

This guide includes information for installing, configuring, maintaining, performance tuning, and troubleshooting InfiniDB.

 

Some of the content in this guide is only available with InfiniDB Enterprise.

Audience

This guide is intended for database administrators and IT administrators who are responsible for setting up and maintaining InfiniDB.

List of Documentation

The InfiniDB Database Platform documentation consists of several guides intended for different audiences. The documentation is described in the following table:

 

Document

Description

InfiniDB Apache HadoopTM Configuration Guide

Installation and Administration of InfiniDB for Apache Hadoop system.

InfiniDB Concepts Guide

Introduction to the InfiniDB analytic database.

InfiniDB Minimum Recommended Technical Specifications

Lists the minimum recommended hardware and software specifications for implementing InfiniDB.

InfiniDB Installation Guide

Contains a summary of steps needed to perform an install of InfiniDB.

InfiniDB Multiple UM Configuration Guide

Provides information for configuring multiple User Modules.

InfiniDB SQL Syntax Guide

Provides syntax native to InfiniDB.

Performance Tuning for the InfiniDB Analytics Database

Provides help for tuning the InfiniDB analytic database for parallelization and scalability.

InfiniDB Windows Installation and Administrator’s Guide

Provides information for installing and maintaining InfiniDB for Windows.

 

Obtaining documentation

These guides reside on our http://www.infinidb.co website.  Contact support@infinidb.co for any additional assistance.

Documentation feedback

We encourage feedback, comments, and suggestions so that we can improve our documentation. Send comments to support@infinidb.co along with the document name, version, comments, and page numbers.

Additional resources

If you need help installing, tuning, or querying your data with InfiniDB, you can contact support@infinidb.co.

 


InfiniDB Console Overview

The InfiniDB Console allows you to configure, monitor, and manage the InfiniDB system and servers. This chapter explains how to use the InfiniDB Console and gives a complete listing of InfiniDB commands. For more detailed information, see the following chapters: Configuring InfiniDB, Monitoring InfiniDB, and Managing InfiniDB System and Servers.

Logging on and off the InfiniDB Console

You can log on to the InfiniDB Console using a SSH client.  This chapter describes both processes.

 

If you use a SSH client to connection to a Linux shell, you run the InfiniDB Console in the Linux shell. Many of the Linux-shell features are available to the InfiniDB Console such as:

·         Entering partial command names to execute commands

·         Recalling previous commands using the Up and Down arrows

·         Using hot keys to edit and navigate commands

The following procedures explain how to log on and off of the InfiniDB Console. You can choose to execute InfiniDB commands from a Linux prompt. See “Linux and InfiniDB Console command prompts” :

 

To log on to the Management Console with SSH client:

1.       Connect to Linux using your SSH client.

You will be prompted to enter a user name and password.

2.       Type your Linux user name and password and press Enter.

The server factory defaults are:

User Name: root

Password: Calpont1

3.  At the Linux prompt, type cmconsole

The cmconsole prompt appears.

To log off of the InfiniDB Console:

1.  At the cmconsole prompt, type exit or quit

The Linux prompt appears.


 InfiniDB Console tips

The following tips and tricks help you use the InfiniDB Console.

Help command

The help command displays supported commands. You can view brief help definitions or verbose definitions. You can also enter partial command names with the help command to view verbose definitions.

 

To view HELP commands:

1.  At the Linux prompt, type cmconsole

2.    Type help

An alphabetical list of support commands appears with brief descriptions.

 

You can enter the help command follow by a command name or partial command name to display the verbose description of the command.

 

For example, type help restartModule to get the verbose definition of the restart server command as shown in the figure below.

 

Case sensitivity

Commands are not case sensitive; however parameters and device names, like server and processes, are case sensitive. For example, the GetAlarmLog command can be entered as getalarmlog.

 

Below are examples of the setsystemconfig command followed by the

ProcessHeartbeatPeriod parameter.

 

setsystemconfig    ProcessHeartbeatPeriod 5

SetSystemConfig    ProcessHeartbeatPeriod 5

 

Recall command history

To browse the history of commands entered, use the Up and Down arrow keys on your keyboard to scroll through commands.

Command repeat option

You can run a command continuously using the -r” option. This is useful to check status in real-time mode.

 

The repeat option repeats a command every 5 seconds. You can change the repeat interval to be between 1 and 60 seconds by adding the number of seconds after the command.

 

For example to repeat the GetProcess Status command every 2 seconds, type:

GetProcessStatus –r2

 

NOTE:  To exit out of a repeating command, press Ctrl-C to exit back to a Linux prompt, or Ctrl-D to return to the InfiniDB Console.

Linux and InfiniDB Console command prompts

For easier command execution without going back and forth between Linux and the Management Console, you can choose to execute Linux commands from the Management Console or execute InfiniDB commands from a Linux prompt.

 

To execute a Linux system command from InfiniDB Console:

 

Type system (and the Linux command name) and press Enter. This processes the Linux command and displays output to the console.

 

For example, to view the Linux manual for the copy command from the InfiniDB prompt, type:

system man cp

 

To execute an InfiniDB Console from the Linux prompt:

 

Type cmconsole InfiniDB_command

 

For example, to view the system status from the Linux command prompt:

cmconsole getSystemStatus

 


Complete list of InfiniDB commands

Below are the commands you can run from the Management Console to configure, manage, and operate the InfiniDB System.

 

Command

Description

?

Help

addDbroot

Add DBRoot Disk Storage to the InfiniDB System

addExternalDevice

Add External Device to Configuration file

addModule

Add a Module within the InfiniDB System

alterSystem-disableModule

Disable a Module and Alter the InfiniDB System

alterSystem-enableModule

Enable a Module and Alter the InfiniDB System

assignDbrootPmConfig

Assign currently unassigned DBroots to Performance Module.

assignElasticIPAddress

Assign Amazon Elastic IP Address to a module

disableLog

Disable the levels of process and debug logging

enableLog

Enable the levels of process and debug logging

exit

Exit from the Console tool

getActiveAlarms

Get Active Alarm list

getActiveSQLStatements

Get List Active SQL Statements within the System

getAlarmConfig

Get Alarm Configuration Information

getAlarmHistory

Get system alarms

getAlarmSummary

Get Summary counts of Active Alarm

getCalpontSoftwareInfo

Get the InfiniDB RPM detailed information

getExternalDeviceConfig

Get External Device Configuration Information

getLogConfig

Get the System log file configuration

getModuleConfig

Get Module Name Configuration Information

getModuleCpu

Get a Module CPU usage

getModuleCpuUsers

Get a Module Top Processes utilizing CPU

getModuleDisk

Get a Module Disk usage

getModuleMemory

Get a Module Memory usage

getModuleMemoryUsers

Get a Module Top Processes utilizing Memory

getModuleResourceUsage

Get a Module Resource usage

getModuleTypeConfig

Get Module Type Configuration Information

getProcessConfig

Get Process Configuration Information

getProcessStatus

Get InfiniDB Process Statuses

getStorageConfig

Get System Storage Configuration Information

getStorageStatus

Get System Storage Status.

getSystemConfig

Get System Configuration Information

getSystemCpu

Get System CPU usage on all modules

getSystemCpuUsers

Get System Top Processes utilizing CPU

getSystemDisk

Get System Disk usage on all modules

getSystemInfo

Get the Over-all System Statuses

getSystemMemory

Get System Memory usage on all modules

getSystemMemoryUsers

Get System Top Processes utilizing Memory

getSystemNetworkConfig

Get System Network Configuration Information

getSystemResourceUsage

Get System Resource usage on all modules

getSystemStatus

Get System and Modules Status

help

Get help on the Console Commands

monitorAlarms

Monitor alarms in realtime mode

movePmDbrootConfig

Move DBroots from one Performance Module to another.

quit

Exit from the Console tool

removeDbroot

Remove DBRoot Disk storage from the InfiniDB System

removeExternalDevice

Remove External Device to Configuration file

removeModule

Remove a Module within the InfiniDB System

resetAlarm

Resets an Active Alarm

restartSystem

Restarts the Processes within the InfiniDB System

resumeDatabaseWrites

Resume performing writes to the InfiniDB Database

setAlarmConfig

Set a Alarm Configuration parameter

setExternalDeviceConfig

Set a External Device Configuration parameter

setModuleTypeConfig

Set a Module Type Configuration parameter

setProcessConfig

Set a Process Configuration parameter

setSystemConfig

Set a System Configuration parameter

shutdownSystem

Shutdowns the InfiniDB System

startSystem

Starts a stopped InfiniDB System

stopSystem

Stops the processing of the InfiniDB System

suspendDatabaseWrites

Suspend performing writes to the InfiniDB Database

switchParentOAMModule

Switches the Active Parent OAM Module to another Performance Module.

system

Execute a system shell  command.

unassignElasticIPAddress

Unassign Amazon Elastic IP Address

 


 

System Maintenance

This chapters describes how to upgrade InfiniDB software and how to use the InfiniDB Console for server operations.

 

Using the InfiniDB Console, you can perform the following system and server operations: stop, start, restart, and shut down.

Upgrading InfiniDB

InfiniDB issues new releases and software patches as needed to fix issues or provide better performance.

InfiniDB software

The InfiniDB software packages that you can patch:

·         InfiniDB Platform Software - This software configures the modules and provides the InfiniDB performance capabilities and InfiniDB Console interface.

When advised by InfiniDB Technical Support, you should apply recommended patches.

Upgrading InfiniDB

The InfiniDB System must be taken offline. The front-end database and InfiniDB database need to be backed up before you upgrade InfiniDB. InfiniDB keeps and uses the existing Calpont.xml file during the upgrade process.

1.       Stop InfiniDB. See “Stopping the system16.

2.       Back up front-end database and InfiniDB database. See “Performing Backup and Recovery“.

3.       In a browser address window, go to ftp://ftp.calpont.com and press Enter.

4.       Download the RPM(s).

5.       Extract the files.

6.    Install the upgrade.   Please see the  “InfiniDB Installation Guide” for installation/upgrade instruction.

Storage Information

The InfiniDB System is typically configured with RAID 0+1 or commonly called RAID10, which is a stripe of mirrors and the system is partitioned with logical unit numbers (LUN).  To better understand when additional storage capacity is needed, “ Performance Tuning  on page  63

System Operations

You can stop, start, restart or shut down the system. Additionally, you can disable and enable modules.

 

If you disable a module and then restart the system, the disabled module does not restart, but stays offline until you enable it.

 

There following options are available when stopping the system or servers:

·         Graceful (Default option)- Processes perform any gradual shutdown operations they might need.

·         Forceful - Processes are stopped no matter what state they are in.

·         Acknowledgement Yes - The system informs you when the operation has been successfully or unsuccessfully performed.

·         Acknowledgement No (Default option) - The system returns a success message when the command is received, but the operation might still be processing.

Stopping the system

Stopping the system stops the application processes. The platform process that supports the Management Console and System Alarms remain active. If you want to stop the system or server and immediately start the processes again (typically if the system or a server hangs), you can restart the system or servers as shown in the section “Restarting the system16.

 

To stop the system:

From the InfiniDB Console, type stopSystem

Press y

The system processes stop.

Starting the system or modules

You can start the system or module application processes with the following commands:

 

To start the system:

From the InfiniDB Console, type startSystem

The system processes start.

Restarting the system

When you want to stop and immediately start application processes, you can perform a restart. You restart the system or modules application processes with the following commands:

 

To restart the system:

From the InfiniDB Console, type restartSystem

The system processes stop and restart.

Shutting down the system

When you perform a shutdown, all InfiniDB processes are stopped.  This command would be used mainly when performing software upgrades.

 

To shutdown the system:

From the InfiniDB Console, type shutdownSystem

Press y

The system is shutdown.

Disabling and Enabling System Modules

When you want to disable or enable a module either with the system ACTIVE or OFFLINE, you can do it with the following commands:

 

Note: Disabling a module may result in data loss if the data is local to the PM.  If the data is SAN mounted, the dbroots would need to be moved to other PMs.  Please refer to “Database Files (DBRoots)” of the Installation Guide for more information on DBRoots and see “Moving DBRoots” of this guide for more information on moving DBRoots.

 

To disable a module:

From the InfiniDB Console, type alterSystem-disableModule module_id

Example: alterSystem-disablemodule PM2, PM3

Press y

The module is stopped and disabled.

To enable a module:

From the InfiniDB Console, type alterSystem-enableModule module_id    

Example: altersSystem-enableModule PM2, PM3

Press y

The module is enabled and started.

 


System Status

The InfiniDB Console allows you to monitor the system without using a third-party Network Monitoring System (NMS). InfiniDB recommends you use a third-party NMS for more detailed system monitoring. The system is set up with factory defaults. To configure system monitoring, see “Configuring System Status Monitoring”.

Viewing system status

The system status shows the status of the system and all equipped servers. The table below shows the available system and server statuses.

 

To view the system status:

Type getSystemStatus

The system and server status appears.

 

Table 1 - System and Module Statuses

Status

Definition

Active

The system, server, or Network Interface Card (NIC)  is available to process database requests.

Auto Disabled

Disabled as a result of a server failure.

Auto Init

Auto initilization mode during a fault recovery.

Auto Offline

The system or server is offline due to a fault.

Busy_Init

The module/system is performing an initialization task at startup time before going to the ACTIVE state.

Degraded

The server is active, but the performance is degraded. A server is degraded when a NIC is not working.

Down

Communication failure.

Failed

A stop/start/restart request for the system or a server failed.

Initial

Initial state after a system reboot or install and before any action is taken.

Man Disabled

Disabled as a result of executing the altersystem-disableModule command.

Man Init

Manual initialization mode during a start or restart command.

Man Offline

The system or server was taken offline with the stop or shutdown command.

Up

Successfully communicating.

 

When all servers are active, then the system status is active. If one server is man offline and the others are active, the system is man offline. All equipped servers must be active before the system is shown as active.

 

Viewing process status

The Process Configuration data is stored in the ProcessConfig.xml file in the /usr/local/Calpont/etc directory.

 

To view process status:

Type getProcessStatus

The processes status appears.

 

The table below shows the supported process states.

 

Table 2 - Process Statuses

Status

Definition

Active

The process is fully functional.

Auto Init

Auto initialization mode during a fault recovery

Auto Offline

The process is offline due to a fault.

Busy Init

The process is performing an initialization task at startup time before going to the ACTIVE state.

Failed

A stop/start/restart request for a process failed.

Hot Standby

The process is functional in a standby/ready state in case a failover occurs.

Initial

State after a system reboot or install and before any action is taken

Man Init

Manual initialization mode during a start or restart command

Man Offline

The process was taken offline with the stop or shutdown command.

Standby Init

Manual initialization mode during a start or restart command of a Hot Standby process.

 


Viewing network configuration

The network configuration lists the servers and states of the NIC cards.

 

To view network system configuration:

Type getSystemNetworkConfig

The system and server status appears.

Table 3 - Network Configuration Statuses

Term

Definition

Device Name

The server configuration type. Example: UMs or PMs.

Device Description

The server configuration type and unique numbering scheme. Example: User Module #1.

NIC ID

This field shows the available NICs on each server.

Host Name

Host name of each server.

IP Address

IP Address of each server.

NIC State

A NIC is either UP or DOWN.

 

Viewing module information

The module configuration provides the server names and IP addresses for all modules.

 

To view the module configuration:

Type getModuleConfig

 

Module Name Configuration

 

Module 'um1' Configuration information

 

ModuleType = um

ModuleDesc = User Module #1

ModuleIPAdd NIC ID 1 = 10.100.7.83

ModuleHostName NIC ID 1 = qaftest7

ModuleIPAdd NIC ID 2 = 10.100.107.83

ModuleHostName NIC ID 2 = qaftest7b

 

Module 'pm1' Configuration information

 

ModuleType = pm

ModuleDesc = Performance Module #1

ModuleIPAdd NIC ID 1 = 10.100.7.10

ModuleHostName NIC ID 1 = srvqaperf2

ModuleIPAdd NIC ID 2 = 10.100.107.10

ModuleHostName NIC ID 2 = srvqaperf2b

DBRootIDs assigned  = 1

 


Monitoring module usage and threshholds

This section explains the module thresholds that are monitored by InfiniDB and how to view them.

 

To view module usage and threshholds:

Type getmoduletype

 

ModuleType 'um' Configuration information

 

ModuleDesc = User Module

RunType = LOADSHARE

ModuleCount = 1

ModuleHostName and ModuleIPAddr for NIC ID 1 on  module 'um1' = qaftest7 , 10.100.7.83

ModuleHostName and ModuleIPAddr for NIC ID 2 on  module 'um1' = qaftest7b , 10.100.107.83

 

ModuleCPUCriticalThreshold % = 0

ModuleCPUMajorThreshold % = 0

ModuleCPUMinorThreshold % = 0

ModuleCPUMinorClearThreshold % = 0

ModuleDiskCriticalThreshold % = 90

ModuleDiskMajorThreshold % = 80

ModuleDiskMinorThreshold % = 70

ModuleMemCriticalThreshold % = 90

ModuleMemMajorThreshold % = 0

ModuleMemMinorThreshold % = 0

ModuleSwapCriticalThreshold % = 90

ModuleSwapMajorThreshold % = 80

ModuleSwapMinorThreshold % = 70

ModuleDiskMonitorFileSystem#1 = /

 

ModuleType 'pm' Configuration information

 

ModuleDesc = Performance Module

RunType = SIMPLEX

ModuleCount = 6

ModuleHostName and ModuleIPAddr for NIC ID 1 on  module 'pm1' = srvqaperf2 , 10.100.7.10

ModuleHostName and ModuleIPAddr for NIC ID 2 on  module 'pm1' = srvqaperf2b , 10.100.107.10

ModuleHostName and ModuleIPAddr for NIC ID 1 on  module 'pm2' = srvswdev10 , 10.100.7.5

ModuleHostName and ModuleIPAddr for NIC ID 2 on  module 'pm2' = srvswdev10b , 10.100.107.5

ModuleHostName and ModuleIPAddr for NIC ID 1 on  module 'pm3' = srvqaperf3 , 10.100.7.15

ModuleHostName and ModuleIPAddr for NIC ID 2 on  module 'pm3' = srvqaperf3b , 10.100.107.15

ModuleHostName and ModuleIPAddr for NIC ID 1 on  module 'pm4' = srvqaperf4 , 10.100.7.81

ModuleHostName and ModuleIPAddr for NIC ID 2 on  module 'pm4' = srvqaperf4b , 10.100.107.81

ModuleHostName and ModuleIPAddr for NIC ID 1 on  module 'pm5' = srvqaperf5 , 10.100.7.82

ModuleHostName and ModuleIPAddr for NIC ID 2 on  module 'pm5' = srvqaperf5b , 10.100.107.82

ModuleHostName and ModuleIPAddr for NIC ID 1 on  module 'pm6' = srvqaperf7 , 10.100.7.35

ModuleHostName and ModuleIPAddr for NIC ID 2 on  module 'pm6' = srvqaperf7b , 10.100.107.35

DBRootIDs assigned to module 'pm1' = 1

DBRootIDs assigned to module 'pm2' = 2

DBRootIDs assigned to module 'pm3' = 3

DBRootIDs assigned to module 'pm4' = 4

DBRootIDs assigned to module 'pm5' = 5

DBRootIDs assigned to module 'pm6' = 6

ModuleCPUCriticalThreshold % = 0

ModuleCPUMajorThreshold % = 0

ModuleCPUMinorThreshold % = 0

ModuleCPUMinorClearThreshold % = 0

ModuleDiskCriticalThreshold % = 90

ModuleDiskMajorThreshold % = 80

ModuleDiskMinorThreshold % = 70

ModuleMemCriticalThreshold % = 90

ModuleMemMajorThreshold % = 0

ModuleMemMinorThreshold % = 0

ModuleSwapCriticalThreshold % = 90

ModuleSwapMajorThreshold % = 80

ModuleSwapMinorThreshold % = 70

ModuleDiskMonitorFileSystem#1 = /

 

Module disk usage

The file systems on each local server can be monitored for the percentage used. There are three thresholds you can set for the disk usage for each server: Critical, Major, and Minor. When a threshold is crossed, an alarm is issued.

 

The alarms that are issued when the configured disk file system usage crosses each threshold are shown below:

·         DISK_USAGE_LOW is set when usage percentage goes above threshold ModuleDiskMinorThreshold

·         DISK_USAGE_MED is set when usage percentage goes above threshold ModuleDiskMajorThreshold

·         DISK_USAGE_HIGH is set when usage percentage goes above threshold ModuleDiskCriticalThreshold

These same alarms are cleared as the usage percentage starts going down. The clearing is done in this manner:

·         DISK_USAGE_HIGH is cleared when the usage percentage usage goes below the threshold ModuleDiskCriticalThreshold

·         DISK_USAGE_MED is cleared when the usage percentage usage goes below the threshold ModuleDiskMajorThreshold

·         DISK_USAGE_LOW is cleared when the usage percentage usage goes below the threshold ModuleDiskMinorThreshold

 

See “Configuring module disk usage34.


Memory usage

The memory usage can be monitored for the percentage used. There are three thresholds you can set for the disk usage for each server: Critical, Major and Minor. When a threshold is crossed, an alarm is issued.

 

The alarms that are issued when the configured disk file system usage crosses each threshold are shown below:

·         MEMORY_USAGE_LOW is set when usage percentage goes above threshold ModuleMemMinorThreshold

·         MEMORY_USAGE_MED is set when usage percentage goes above threshold ModuleMemMajorThreshold

·         MEMORY_USAGE_HIGH is set when usage percentage goes above threshold ModuleMemCriticalThreshold

These same alarms are cleared as the usage percentage starts going down. The clearing is done in this manner:

·         MEMORY_USAGE_HIGH is cleared when the usage percentage usage goes below the threshold ModuleMemCriticalThreshold

·         MEMORY_USAGE_MED is cleared when the usage percentage usage goes below the threshold ModuleMemMajorThreshold

·         MEMORY_USAGE_LOW is cleared when the usage percentage usage goes below the threshold ModuleMemMinorThreshold

Swap space usage

The swap space usage can be monitored for the percentage used. There are three thresholds you can set for the swap space usage for each server: Critical, Major, and Minor. When a threshold is crossed, an alarm is issued.

 

The alarms that are issued when the Configured Disk File System usage crosses each threshold are shown below:

·         SWAP_USAGE_LOW is set when usage percentage goes above threshold ModuleSwapMinorThreshold

·         SWAP_USAGE_MED is set when usage percentage goes above threshold ModuleSwapMajorThreshold

·         SWAP_USAGE_HIGH is set when usage percentage goes above threshold ModuleSwapCriticalThreshold

These same alarms are cleared as the usage percentage starts going down. The clearing is done in this manner:

·         SWAP_USAGE_HIGH is cleared when the usage percentage usage goes below the threshold ModuleSwapCriticalThreshold

·         SWAP_USAGE_MED is cleared when the usage percentage usage goes below the threshold ModuleSwapMajorThreshold

·         SWAP_USAGE_LOW is cleared when the usage percentage usage goes below the threshold ModuleSwapMinorThreshold.

Disk thresholds

You can set thresholds to alert you when a specified percentage of a file system is being used on a server.

 

There are three thresholds you can set for the disk usage for each server: Critical, Major, and Minor. When a threshold is crossed, an alarm is issued.

 

The alarms that are issued when the Disk File System usage crosses each threshold are shown below:

·         DISK_USAGE_LOW is set when usage percentage goes above threshold RAIDMinorThreshold.

·         DISK_USAGE_MED is set when usage percentage goes above threshold RAIDMajorThreshold.

·         DISK_USAGE_HIGH is set when usage percentage goes above threshold RAIDCriticalThreshold.

These same alarms are cleared as the usage percentage starts going down as shown below:

·         DISK_USAGE_HIGH is cleared when the usage percentage usage goes below the threshold RAIDCriticalThreshold.

·         DISK_USAGE_MED is cleared when the usage percentage usage goes below the threshold RAIDMajorThreshold.

·         DISK_USAGE_LOW is cleared when the usage percentage usage goes below the threshold RAIDMinorThreshold.

CPU thresholds

You can set thresholds to alert you when the CPU usage on a server reaches a specific percentage.

 

There are four thresholds for the server CPU usage: Critical, Major, Minor and MinorClear. When a threshold is crossed, an alarm is issued. To disable thresholds, set the thresholds to 0.

 

The alarms that are issued when the Server total CPU usage crosses each threshold are shown below:

·         CPU_USAGE_LOW is set when CPU goes above threshold ModuleCPUMinorThreshold

·         CPU_USAGE_MED is set when CPU goes above threshold ModuleCPUMajorThreshold

·         CPU_USAGE_HIGH is set when CPU goes above threshold ModuleCPUCriticalThreshold

These same alarms are cleared as the CPU usage starts going down. To prevent alarm thrashing, the clearing is done in this manner:

·         CPU_USAGE_HIGH is cleared when the CPU usage goes below the threshold ModuleCPUMajorThreshold

·         CPU_USAGE_MED is cleared when the CPU usage goes below the threshold ModuleCPUMinorThreshold

·         CPU_USAGE_LOW is cleared when the CPU usage goes below the threshold ModuleCPUMinorClearThreshold

Monitoring alarms

Alarms are triggered when system, servers, processes, or hardware are down, power on test has warning, process fail or time out, or when threshold percentages are met. These thresholds are set for the following:

·         Module disk usage

·         Memory usage

·         Swap space usage

·         RAID usage

·         CPU usage

·         This section explains how to view alarms, and describes the alarm definitions.  To change  default thresholds, see “Configuring System Status Monitoring”.

Viewing active alarms

To view active alarms:

Type getActiveAlarms

Below is a sample alarm file.

 


Viewing archived alarms

Alarms are archived each day at midnight. The InfiniDB system saves seven days of alarm history.

 

To view historical alarms:

Type getAlarmHistory (yyyymmdd)

In place of the date, you can type today to get the alarm log for the current day.

Example statements:

getAlarmHistory today

getAlarmHistory 20070923

 

Table 4 - Alarm Definitions

Description

Definition

Alarm ID#1 CPU_USAGE_HIGH

The usage on the indicated CPU has exceeded its high threshold

Alarm ID#2 CPU_USAGE_MED

The usage on the indicated CPU has exceeded its medium threshold

Alarm ID#3 CPU_USAGE_LOW

The usage on the indicated CPU has exceeded its low threshold

Alarm ID#4 DISK_USAGE_HIGH

The usage on the indicated disk drive has exceeded its high threshold

Alarm ID#5 DISK_USAGE_MED

The usage on the indicated disk drive has exceeded its medium threshold

Alarm ID#6 DISK_USAGE_LOW

The usage on the indicated disk dr ive has exceeded its low threshold

Alarm ID#7 MEMORY_USAGE_HIGH

The usage on the indicated memory has exceeded its high threshold

Alarm ID#8 MEMORY_USAGE_MED

The usage on the indicated memory has exceeded its medium threshold

Alarm ID#9 MEMORY_USAGE_LOW

The usage on the indicated memory has exceeded its low threshold

Alarm ID #10 SWAP_USAGE_HIGH

The usage on the indicated swap has exceeded its high threshold

Alarm ID #11 SWAP_USAGE_MED

The usage on the indicated swap has exceeded its medium threshold

Alarm ID #12 SWAP_USAGE_LOW

The usage on the indicated swap has exceeded its low threshold

Alarm ID #13 PROCESS_DOWN_AUTO

Process is auto out-of-service

Alarm ID #14 MODULE_DOWN_AUTO

Module is auto out-of-service

Alarm ID #15 SYSTEM_DOWN_AUTO

System is auto out-of-service

Alarm ID #16 POWERON_TEST_SEVERE

A Module Power-On Severe Warning error occurred. Test failures indicate that the system is running in a degraded state

Alarm ID #17 POWERON_TEST_WARNING

Warning error occurred. Test failures indicate that the system is running with little or no degradation in performance

Alarm ID #18 HARDWARE_HIGH

A  hardware device's resource has exceeded its high threshold

Alarm ID #19 HARDWARE_MED

A hardware device's resource has exceeded its medium threshold

Alarm ID #20 HARDWARE_LOW

A hardware device's resource has exceeded its low threshold

Alarm ID #21 PROCESS_DOWN_MANUAL

Process is manually out-of-service

Alarm ID #22 MODULE_DOWN_MANUAL

Module is manually out-of-service

Alarm ID #23 SYSTEM_DOWN_MANUAL

System is manually out-of-service

Alarm ID #24 EXT_DEVICE_DOWN_AUTO

External Device is auto out-of-service

Alarm ID #25 PROCESS_INIT_FAILURE

Process Initialzation failed or Timed-out

Alarm ID #26 NIC_DOWN_AUTO

NIC Interface is auto out-of-service

Alarm ID #27 DBRM_LOAD_DATA_ERROR

Error occurred during loading DBRM data

Alarm ID #28 INVALID_SW_VERSION

Invalid software version found on a module which doesn’t match the version running on the User Module

Alarm ID #29 STARTUP_DIAGNOSTICS_FAILURE

A failure occurred when the system startup diagnostics was executed on a module.  Log files can be viewed for details on the failure

Alarm ID #30 CONN_FAILURE

A connection failure occurred that could affect the performance of system processing.

Alarm ID #31 DBRM_READ_ONLY

The DBRM is now read-only and updates to the database are not possible.

ALARM ID #33 MODULE_SWITCH_ACTIVE

An outage of Parent OAM Module or the execution of the switchParentOAMModule command initiated a change of the Parent OAM Module.

 

Table 5 - Alarm Severity

Severity

Description

Critical

A condition occurred that requires immediate attention and could result in loss of data.

Major

A condition occurred that impairs service and needs attention.

Minor

A condition occurred that hasn’t impaired service, but needs attention so that it doesn’t impair service in the future.

 

Viewing top CPU users

You can view the top users on a single module or the top users on all modules in InfiniDB. The default is to display the top five users, but you can specify to display a range from 1-10 processes. Processes that use less than 1% CPU are not displayed.

 

To monitor top users on a single module:

Type getmodulecpuusers module_ID

 

To monitor to the top CPU users on User Module 1, the command is:

getmodulecpuusers um1

 

To monitor top users on InfiniDB:

getsystemcpuusers


 Monitoring resource usage

You can monitor the system CPU usage, memory, cache and swap space for the system by module.

 

To monitor system resources:

getsystemresourceusage


Configuring System Status Monitoring

This chapter describes how to configure the system, processes, servers, CPU usage, disk usage, RAID, memory usage, and swap space.

 

For more information about monitoring the system, see “System Status” and for a complete list of InfiniDB  Commands, see “Complete list of InfiniDB commands”.

Management console overview

This chapter explains how to configure the following:

·         System

·         Modules

·         Processes

·         Alarms

·         Logging

InfiniDB configuration files

When you configure the system using the Management Console, the changes are stored in XML files that reside in the “etc” directory. Some of these configuration items are updated by InfiniDB Processes and the InfiniDB postConfig script at installation time.

·         AlarmConfig.xml - Alarm configuration file.

·         Calpont.xml - System and module configuration file.

·         ConsoleCmds.xml - Management Console tool help file.

·         ProcessConfig.xml - Process configuration file.

Linux shell

You need a SSH client to connect to the Linux shell. Once you are connected to the Linux shell, you enter the command to run the Management Console and it runs in the Linux shell. Many of the Linux-shell features are available to the Management Console such as:

·         Entering partial command names to execute commands

·         Recalling previous commands using the Up and Down arrows

·         Using hot keys to edit and navigate commands

·         For tips on using the Management Console, “ InfiniDB Console tips”.

Configuring system

You can modify the following system settings: the heartbeat period and counts, RAID thresholds, monitoring, alarm, and logging configurations. The system configuration file also displays the current software and hardware versions used with the InfiniDB Database Platform. This can be useful troubleshooting information.

 

Note: Most of the data in the configuration file is stored in the Calpont.xml file located in the /usr/local/Calpont/etc directory. The master copy of the configuration file is kept on the primary Performance Module. Upon startup, the system reads the information from the configuration file and allocates the necessary resources (e.g. data caches, etc.) needed for system activity.  Any changes to this file in a multi-server configuration should be done on this primary Performance Module so the changes can get distributed to all modules.

 

Some data is extracted from other sources when the getSystemConfig command is executed.

 

To view the system configuration:

At the Linux prompt, type cmconsole.

Type getSystemConfig and press Enter.

getsystemconfig   Wed Mar 28 10:17:49 2012

 

System Configuration

 

SystemName = sn-qaperf-1

SoftwareVersion = 3.0

SoftwareRelease = 0

ParentOAMModuleName = pm1

StandbyOAMModuleName = pm2

NMSIPAddr = 0.0.0.0

ModuleHeartbeatPeriod = 1

ModuleHeartbeatCount = 3

DBRootCount = 6

DBRoot1 = /usr/local/Calpont/data1

DBRoot2 = /usr/local/Calpont/data2

DBRoot3 = /usr/local/Calpont/data3

DBRoot4 = /usr/local/Calpont/data4

DBRoot5 = /usr/local/Calpont/data5

DBRoot6 = /usr/local/Calpont/data6

DBRMRoot = /usr/local/Calpont/data1/systemFiles/dbrm/BRM_saves

ExternalCriticalThreshold = 90

ExternalMajorThreshold = 80

ExternalMinorThreshold = 70

MaxConcurrentTransactions = 1000

SharedMemoryTmpFile = /tmp/CalpontShm

VersionBufferFileSize = 1

OIDBitmapFile = /usr/local/Calpont/data1/systemFiles/dbrm/oidbit­map

FirstOID = 3000

TransactionArchivePeriod = 10

 

 

To view the storage configuration, use the getstorageconfig command to see what dbroots are assigned to which pms along with the system storage configuration.

 

To view the storage configuration:

At the Linux prompt, type cmconsole.

Type getStorageConfig and press Enter.

getstorageconfig   Wed Mar 28 10:40:34 2012

 

System Storage Configuration

 

Storage Type = internal

System DBRoot count = 6

DBRoot IDs assigned to 'pm1' = 1

DBRoot IDs assigned to 'pm2' = 2

DBRoot IDs assigned to 'pm3' = 3

DBRoot IDs assigned to 'pm4' = 4

DBRoot IDs assigned to 'pm5' = 5

DBRoot IDs assigned to 'pm6' = 6

 

Setting module heartbeats

Heartbeat monitoring occurs between modules in the InfiniDB Database Platform. The module heartbeat settings are the same for all modules in the InfiniDB Database Platform.

 

The module heartbeat period refers to how often the heartbeat test is performed. For example, if you set the period to 5, then the heartbeat test is performed every 5 seconds.

 

The module heartbeat count refers to how many failures in a row must take place before a fault is processed. To disable heartbeat monitoring, set the period to -1.

 

To set the server heartbeat period and count:

1.    At the Linux prompt, type cmconsole.

2.       Type setSystemConfig ModuleHeartbeatPeriod n (where n= number of seconds) and press Enter.

A message appears when the period is successfully set.

3.       Type setSystemConfig ModuleHeartbeatCount n (where n= number of failures) and press Enter.

A message appears when the count is successfully set.

Configuring Disk thresholds

You can set thresholds to alert you when a specified percentage of a file system is being used on a server.

 

There are three thresholds you can set for the disk usage for each server: Critical, Major and Minor. When a threshold is crossed, an alarm is issued. To disable thresholds, set the threshold to 0.

 

The alarms that are issued when the Disk File System usage crosses each threshold are shown below:

·         DISK_USAGE_LOW is set when usage percentage goes above threshold EXTERNALMinorThreshold.

·         DISK_USAGE_MED is set when usage percentage goes above threshold EXTERNALMajorThreshold.

·         DISK_USAGE_HIGH is set when usage percentage goes above threshold EXTERNALCriticalThreshold.

 

These same alarms are cleared as the usage percentage starts going down as shown below:

·         DISK_USAGE_HIGH is cleared when the usage percentage usage goes below the threshold EXTERNALCriticalThreshold.

·         DISK_USAGE_MED is cleared when the usage percentage usage goes below the threshold EXTERNALMajorThreshold.

·         DISK_USAGE_LOW is cleared when the usage percentage usage goes below the threshold EXTERNALMinorThreshold.

The system configuration parameters are:

·         EXTERNALMinorThreshold

·         EXTERNALMajorThreshold

·         EXTERNALCriticalThreshold

To configure Disk thresholds:

1.    At the Linux prompt, type cmconsole.

2.       Type setSystemConfig EXTERNAL(Minor/Major/Critical) Threshold n (where n= percentage of file system used) and press Enter.

A message appears when the threshold is successfully set.

For example, to modules to issue a disk usage medium alarm when the file system is 25% full, enter the following command:

 

setSystemConfig EXTERNALMajorThreshold 25

 

TIP: When setting thresholds, minor should be less than major, and major should be less than critical.

Configuring archived transaction log periods

You can change how often the transaction log file is archived by configuring the systemconfiguration file. The default period is set to 10 minutes. You can select between 0 and 60 minutes.

 

To modify archived transaction log period:

Connect to Linux using your SSH client.

You will be prompted to enter a user name and password.

Type your Linux user name and password and press Enter.

A Linux prompt appears.

At the Linux prompt, type cmconsole.

The cmconsole prompt appears.

Type setsystemconfig TransactionArchivePeriod (log period) and press Enter.


Configuring modules

You can configure the following module items:

·         CPU thresholds

·         Disk monitor file system usage

·         Disk thresholds

·         Module memory thresholds

·         Module swap thresholds

Configuring module CPU thresholds

You can set thresholds to alert you when the CPU usage on a module reaches a specific percentage. For more information about CPU Thresholds, see “CPU thresholds”.

 

There are four thresholds for the module CPU usage: Critical, Major, Minor and MinorClear. When a threshold is crossed, an alarm is issued. To disable thresholds, set the thresholds to 0.

 

To configure the CPU thresholds:

At the Linux prompt, type cmconsole.

Type setModuleTypeConfig (module name) ModuleCPU(Clear/ Minor/Major/Critical)Threshold n (where n= percentage of CPU usage) and press Enter.

The CPU threshold is set and a message appears.

For example, to set a module alert to Minor at 50%, type the following:

setModuleTypeConfig pm1 ModuleCPUMinorThreshold 50.

 

TIP: When setting thresholds, minor should be less than major, and major should be less than critical.

Configuring module disk usage

The file systems on each local module can be monitored for the percentage used. For more information about server disk usage, see “Module disk usage”.

 

To configure the module disk usage:

At the Linux prompt, type cmconsole.

Type setModuleTypeConfig (module name) ModuleDisk(Minor/ Major/Critical)Threshold n (where n= percentage of disk system used) and press Enter.

The module disk usage threshold is set and a message appears.

TIP: When setting thresholds, minor should be less than major, and major should be less than critical.

Configuring swap space usage

The swap space usage can be monitored for the percentage used. There are three thresholds you can set for the swap space usage for each server: Critical, Major and Minor. When a threshold is crossed, an alarm is issued. To disable thresholds, set the thresholds to 0.

For more information about swap space usage, see “Swap space usage”.

 

To configure the module swap space usage:

At the Linux prompt, type cmconsole.

Type setModuleTypeConfig (module name) ModuleSwap(Minor/ Major/Crictical)Threshold n (where n= percentage of swap space used) and press Enter.

The server swap space usage threshold is set and a message appears.

TIP: When setting thresholds, minor should be less than major, and major should be less than critical.

Configuring alarms

In this chapter, you learned how to configure the system and servers to trigger alarms for specific events or conditions. You can also set the total number of alarms that can be triggered in a 30-minute period. The InfiniDB system comes with a pre-configured alarm file, stored in the CalpontAlarm.xml file, that has the threshold setting for all alarms at 100. This means that an alarm can be triggered a maximum of 100 times in 30 minutes before the alarm quits being triggered.

 

To disable a specific alarm from being reported, set the alarm threshold to 0.

Steps to configure alarms

To configure alarms, you use the setalarmconfig command with the Alarm ID#, and the parameter to be changed.

At the Linux prompt, type cmconsole.

Type setAlarmConfig (alarmID#) Threshold n (where n= maximum number of times an alarm can be triggered in 30 minutes), and press Enter.

For example, if you want to change Alarm ID#23 to have a threshold of 25 you would type the following at the InfiniDB prompt:

setAlarmConfig 23 Threshold 25.

Configuring logging

The Log Configuration can be displayed using the following command: getLogConfig.

 

There are 5 levels of logging:

·         Critical

·         Error

·         Warning

·         Info

·         Debug

 

Each server within the InfiniDB System can have a different log level configured. Since InfiniDB logging is supported through the Syslog functionality, the Syslog service should always be running on each server.

 

The Log Configuration can be enabled and disabled using the EnableLog and DisableLog commands.  The five logging levels can be individually enabled and disabled or you can enable and disable all five by entering "all" for the log level.

 

Messages are identified by a unique number. In the following syslog message, items 1-4 are generated automatically by syslog and items 5-8 come from the message object itself.

 

1.       Date & time

2.       Host of origin

3.       Subsystem name

4.       PID

5.       Seconds and microseconds of message

6.       Session ID, transaction ID & thread ID

7.       Message severity level - A single character message-class prefix:

·         D - debug

·         I - info

·         W - warning

·         E - error

·         C - critical

8.       Subsystem ID - A 2-digit subsystem ID. 

9.       Message number - A 4-digit error number. Error message ID's (and thus error message texts) can be shared across subsystem boundaries. This allows reuse of common error messages.

10.   Message text - formatted error text after all positional arguments have been substituted.

These log files are stored on each server in /var/log/Calpont directory.

Enabling and disabling logging

To enable logging:

At the Linux prompt, type cmconsole.

Type enableLog (module_ID)(log_level) and press Enter.

For example, to enable Crictical logging on server PM1, the command is: enableLog PM1 Crictical.

 

To disable logging:

At the Linux prompt, type cmconsole.

Type disableLog (module_ID)(log_level) and press Enter.

For example, to disable Info logging on server PM1, the command is:

disableLog PM1 Info.

Loading Data

This chapter describes how to import data into the InfiniDB System. This includes data migrations from other systems or periodic updates during the maintenance window.

Import overview

Before importing data, you must complete the InfiniDB Installation as documented in the InfiniDB Installation Guide. This includes creating a InfiniDB system catalog and creating the tables to which you want to import data.

Estimating load sizes

Business requirements for data latency drive the size of data loads. If access to new data is needed every 30 seconds, then data should be loaded in smaller loads so that it can be written to the database quickly and available for queries.

 

If access to new data is needed within 24 hours, then data can be loaded overnight in larger data loads.

Import Tasks

Perform the tasks below to import data:

·         Copy data files to be imported into the correct directory

·         Optionally generate job file depending on usage

·         Import data

Import process

InfiniDB does the following during the import:

·         Reads data

·         Transforms the data into a column orientation

·         Tokenizes data values

·         Loads tokenized data into database

Directory structure

The default directory structure below is created when InfiniDB is installed.  These locations and file names may be overridden using the “-f” and “-l” options described below (i.e., STDIN processing).  The bulk load process may now be initiated from any server.

 

/usr/local/Calpont/data/bulk/

job - Contains the job description XML files that map tables and columns.

data/import - Contains the data files to be imported.  This is the default unless you override or run the simpler interface.

log - Contains the log file for each executed job. The file extension is .log.

 

BulkRoot definition

There is an entry in the Calpont.XML file called BulkRoot.  This entry allows the user to modify the default of /usr/local/Calpont/data/bulk to another directory if so desired.

Importing as non-root user

If colxml and cpimport are to be executed using a non-root user, the access mode for these utilities must be altered by the system administrator.  For example:

·         chmod  4755  /usr/local/Calpont/bin/colxml

·         chmod  4755  /usr/local/Calpont/bin/cpimport

Copy source files

Source files need to be formatted, named correctly, and placed in the InfiniDB import folder if using the default location for InfiniDB to import the data.  This may be overridden a number of ways (-p option, using the simpler interface.)

Source file formats

The default delimiter is the ‘|’ (pipe) character.

 

You can configure the system to accept other formats by providing an optional delimiter parameter during the import. For most character delimiters, use -d and the delimiter character on colxml.  You may use the -s if using cpimport.

 

Examples:

-d ,

-d |

 

For special characters such as tab, single quote, or tilde, add a backwards slash “\” and then the special character. Note:  If you use the backwards slash for special characters, it must be wrapped in a single quotes.  The examples for each are as follows:

 

-d ‘\t’

-d ‘\~’

Importing UTF-8 data

When using the cpimport utility to import UTF-8 data, the input file must be converted to UTF-8 data.  The Linux program iconv is one utility to convert it.

 

iconv -f ISO-8859-1 -t UTF-8 < input.txt > output.txt

 

Source file names

For the full interface, the source files must be named with the table name that the data should be imported by default. You may use the -l option to override. For the simple interface, the source file name may be placed on the command line if different than the default table name.

 

If source data resides in a different directory, you can use the ln command to create a symbolic link “import” instead.  For example:

 

ln -s /mnt/source /usr/local/Calpont/data/bulk/data/import

Import directory

If using the default, you must place source data to be imported into the following directory:

 

/usr/local/Calpont/data/bulk/data/import

 

This may be overridden by using the -p option and/or using the simple interface.

Import Usage

The import tool can be used 2 different ways:

·         The first usage is a simple interface to import a single table and no XML job file (colxml) is required.

·         The second usage is the full way of using cpimport in that you must first run the colxml utility to generate the XML job file. This way must be used when importing multiple tables at once but can be used in all scenarios as well.

Importing multiple tables

Multiple tables may be simultaneously imported using the import utility.  With the simple interface, multiple jobs may be simultaneously submitted as long as the tables are unique per import or the PMs are unique per import.  With the full interface, multiple tables may be imported by either importing all tables within a schema or listing specific tables using the -t option in colxml.  Multiple jobs may be submitted with the traditional interface as long as the tables and jobids are unique.

Distributed Imports

The import utility has now been expanded to allow for multiple load methods to allow for the ease of use of a single, centralized load process, as well as the scalability to load from separate files without any single bottleneck that may limit overall load performance.

 

The following sections and diagrams illustrate the various modes available in InfiniDB:

Bulk Load from a central location (Mode 1)

Executing Bulk Load from a central location provides the option to load a single file, with a single command, and have that data distributed properly across a suite of disk resources.   Characterized by:

·         Single source file

·         Single bulk load command

cpimport with Mode 1 will read in the source file and distribute it across either all PMs or specific PMs (-P option).  The source file will be split into 10,000 row increments and send them to the respective PMs for import.  In a 2 PM example like above, a 100,000 row source file wlll result in 50,000 rows on each PM.

 

Note:  The splitter sends 10,000 row increments to DBRoots.  For example, if you have an unbalanced number of dbroots (PM1 has 1 dbroot and PM2 has 2 dbroots), PM2 will import twice the data that PM1 will.

 

Examples: