InfiniSQL™ Reference Manual

InfiniSQL™ Reference Manual

2013-12-26 23:35:43

Table of Contents

Preface
I. InfiniSQL™ Configuration Reference Manual
1. Configuration File Format and Parameters
Configuration File — The structure and contents of the InfiniSQL configuration file.
username — user as which infinisqld runs.
sshkey — ssh rsa (or dsa) key filename.
ssh — ssh client filename.
infinisqld — infinisqld filename.
logfile — infinisqld logfile.
globaladminpassword — administrative password
cfghostport — IP:port to listen for configuration commands
cfgremotehostport — destination IP:port for configuration commands
mgmthost — hostname for infinisqld node
listenhost — address to listen on for raw configuration
listenport — TCP port to listen on for raw configuration
ibgatewayhostport — IP:port pair for inter-node cluster communication
pghost — host for SQL communication
pgport — TCP port for SQL communication
transactionagents — number of Transaction Agents
engines — number of Engines
ibgateways — number of Inbound Gateways
obgateways — number of Outbound Gateways
anonymousping — allow anonymous ping or not
badloginmessages — send bad login response messages or not
replica — replica number of which this node is a member
member — index in replica to which this node belongs
userschemamgrnode — node on which the User Schema Manager actor thread runs
deadlockmgrnode — node on which the Deadlock Manager actor thread runs
activereplica — the replica that supports client interaction
infinisqlmgr.conf Examples
infinisqlmgr.conf Examples
II. InfiniSQL™ User Reference Manual
2. Using InfiniSQL
3. User and Schema Management
Perl API for User and Schema Management
connect — open TCP connection to InfiniSQL node
send — send a command
describeresponse — show response of sent command
disconnect — close TCP connection to InfiniSQL node
fireforget — send command without collecting response
getcommand — read commands from standard input
User and Schema Commands
login — login to an InfiniSQL node via the raw interface
logout — logout from an InfiniSQL node via the raw interface
createdomain — create domain
createuser — create a user
createschema — create schema
createtable — create a table
addcolumn — adds a column and optional index to a table
compile — compile a SQL statement
loadprocedure — load a stored procedure
ping — Verify up and running
4. SQL Reference
INSERT — Inserts records into a database.
SELECT — Retrieves records from a database.
UPDATE — Modifies records in a database.
DELETE — Deletes records in a database.
SELECTSTOREDPROC — Execute stored procedure.
TRANSACTIONS — Perform transactions.
Future SQL Capabilities
Future SQL Capabilities
5. Stored Procedure API
storedproc — Stored Procedure Programming
III. InfiniSQL™ Command and File Reference Manual
6. Files For Operating InfiniSQL
Executables
infinisqlmgr.py — InfiniSQL Cluster Startup
infinisqld — InfiniSQL daemon node process
prime_regression.pl — Prepares InfiniSQL for regression test
regression.pl — SQL regression test
entercommands.pl — User and schema management command shell.
ping.pl — Sends raw interface ping command
bm.pl — Launch benchmark job
collectcpudaemons.sh — Start resource collection jobs
deploy.sh — Install InfiniSQL on multiple hosts
fillkeyval.pl — Inserts data for keystore benchmarking
fill.pl — Inserts data for benchmarking 3-update+ stored procedures
primebm.pl — Creates users and tables for benchmarking
getlogs.sh — Collect node info and error logs
killbm.sh — Kill benchmark jobs
killdaemons.sh — Kill infinisqld processes
moforker.pl — Launch benchmark jobs
report.pl — Report a Benchmark Run
Configs
infinisql.conf — Configure InfiniSQL
clients — List of benchmark client hosts
daemonhosts — List of hosts with infinisqld processes
daemons — List of each infinisqld node
Other Files
lib — library
storedprocs — Compiled stored procedures
infinisql.plib — Perl subroutines
pgbenchpatch — Patch for pgbench
pgbenchtests — pgbench scripts
regression.out-sample — Sample output from regression test

List of Examples

1.1. username configuration
1.2. sshkey configuration
1.3. ssh configuration
1.4. infinisqld configuration
1.5. infinisqld configuration with format string interpolation
1.6. logfile configuration
1.7. logfile configuration with format string interpolation
1.8. globaladminpassword configuration
1.9. cfghostport configuration
1.10. cfgremotehostport configuration
1.11. cfgremotehostport configuration with interpolation
1.12. mgmthost configuration
1.13. mgmthost configuration with interpolation
1.14. listenhost configuration
1.15. listenport configuration
1.16. ibgatewayhostport configuration
1.17. ibgatewayhostport configuration with interpolation
1.18. pghost configuration
1.19. pgport configuration
1.20. transactionagents configuration
1.21. engine configuration
1.22. ibgateway configuration
1.23. obgateway configuration
1.24. anonymousping configuration
1.25. badloginmessages configuration
1.26. replica configuration
1.27. member configuration for the 8th node in a replica
1.28. userschemamgrnode
1.29. deadlockmgrnode
1.30. activereplica
1. Single Node, infinisqlmgr.py on same host as infinisqld node
2. Single Node, infinisqlmgr.py on separate host from infinisqld node
3. Eight Nodes, infinisqlmgr.py on separate host from infinisqld
4. Two Nodes, each running on same host with infinisqlmgr.py
3.1. login example
3.2. logout example
3.3. createdomain example
3.4. createuser example
3.5. createschema example
3.6. createtable example
3.7. addcolumn example
3.8. compile example
3.9. loadprocedure example
3.10. ping example
4.1. Connecting via psql.
4.2. INSERT example
4.3. SELECT example
4.4. UPDATE example
4.5. DELETE example
4.6. SELECTSTOREDPROC example

Preface

This is the reference manual for InfiniSQL. It describes configuration settings and user capabilities such as SQL support, schema and user management, and using the API for creating stored procedures. It is based on the 0.1.2-alpha release, available on GitHub.

Part I. InfiniSQL™ Configuration Reference Manual

These entries describe all available configuration options for InfiniSQL as well as the format of the config file.

Table of Contents

1. Configuration File Format and Parameters
Configuration File — The structure and contents of the InfiniSQL configuration file.
username — user as which infinisqld runs.
sshkey — ssh rsa (or dsa) key filename.
ssh — ssh client filename.
infinisqld — infinisqld filename.
logfile — infinisqld logfile.
globaladminpassword — administrative password
cfghostport — IP:port to listen for configuration commands
cfgremotehostport — destination IP:port for configuration commands
mgmthost — hostname for infinisqld node
listenhost — address to listen on for raw configuration
listenport — TCP port to listen on for raw configuration
ibgatewayhostport — IP:port pair for inter-node cluster communication
pghost — host for SQL communication
pgport — TCP port for SQL communication
transactionagents — number of Transaction Agents
engines — number of Engines
ibgateways — number of Inbound Gateways
obgateways — number of Outbound Gateways
anonymousping — allow anonymous ping or not
badloginmessages — send bad login response messages or not
replica — replica number of which this node is a member
member — index in replica to which this node belongs
userschemamgrnode — node on which the User Schema Manager actor thread runs
deadlockmgrnode — node on which the Deadlock Manager actor thread runs
activereplica — the replica that supports client interaction
infinisqlmgr.conf Examples
infinisqlmgr.conf Examples

Chapter 1. Configuration File Format and Parameters

Table of Contents

Configuration File — The structure and contents of the InfiniSQL configuration file.
username — user as which infinisqld runs.
sshkey — ssh rsa (or dsa) key filename.
ssh — ssh client filename.
infinisqld — infinisqld filename.
logfile — infinisqld logfile.
globaladminpassword — administrative password
cfghostport — IP:port to listen for configuration commands
cfgremotehostport — destination IP:port for configuration commands
mgmthost — hostname for infinisqld node
listenhost — address to listen on for raw configuration
listenport — TCP port to listen on for raw configuration
ibgatewayhostport — IP:port pair for inter-node cluster communication
pghost — host for SQL communication
pgport — TCP port for SQL communication
transactionagents — number of Transaction Agents
engines — number of Engines
ibgateways — number of Inbound Gateways
obgateways — number of Outbound Gateways
anonymousping — allow anonymous ping or not
badloginmessages — send bad login response messages or not
replica — replica number of which this node is a member
member — index in replica to which this node belongs
userschemamgrnode — node on which the User Schema Manager actor thread runs
deadlockmgrnode — node on which the Deadlock Manager actor thread runs
activereplica — the replica that supports client interaction

Name

Configuration File — The structure and contents of the InfiniSQL configuration file.

Description of Configuration File

InfiniSQL has only one configuration file, etc/infinisqlmgr.conf, and it is read once upon startup. It is planned in the near future to be a dynamically configurable system. But for the time being, there is a single config file. It is read upon startup by infinisqlmgr/infinisqlmgr.py, which uses Python's ConfigParser module to process configuration parameters. There is a sample configuration file shipped with the source called etc/infinisqlmgr.conf-sample. This file contains all necessary parameters to start up a single process InfiniSQL database. General configuration syntax, such as parameter assignments and commenting, should be pretty self-explanatory. But they are explained in detail in the ConfigParser documentation. All parameters are required. Some may have blank values, but each parameter must be defined in the config file for InfiniSQL to start correctly.

The following sections are defined in the config file:

  • [DEFAULT]

    This section contains parameters which pertain to other sections. If a parameter does not appear explicitly in a particular section, but does appear in [DEFAULT], then the parameter is still accessible from that section. This section is described as part of Python's ConfigParser documentation.

  • [global]

    This section contains parameters that apply to all nodes within an InfiniSQL cluster.

  • [node_[0-9]+]

    Each infinisqld process within a cluster is defined within these sections. InfiniSQL defines a node as a running infinisqld process-- any number of nodes may execute within a single host operating system instance. Likewise, any number of hosts may have infinisqld node processes running upon them. Each node has a nodeid, which is defined by the numerals following the underscore in the section definition. All nodes must have unique nodeid's, and they should start with 1 and increase in single unit increments, such as [node_1], [node_2], etc. Parameters which are unique to the node, such as member, must be defined in the node's own section.

Format Strings

ConfigParser allows definition of format strings (which behave a lot like variables, or symbol definitions). etc/infinisqlmgr.conf-sample includes two such items, DEPLOYMENT_DIRECTORY and IPADDR. DEPLOYMENT_DIRECTORY refers to the directory root into which InfiniSQL has been installed. IPADDR refers to an IPV4 address. These values are interpreted by ConfigParser, and not used by infinisqlmgr.py. So you are free to use any format string you want, if any, and to discard those used in the sample. The ConfigParser documentation describes how format strings are interpolated.

Name

username — user as which infinisqld runs.

username

This is the system userid as which infinisqld runs.

Example 1.1. username configuration

username: infinisql


Name

sshkey — ssh rsa (or dsa) key filename.

sshkey

This is the filename of the ssh private key used to log in to each host upon which infinisqld node processes are to execute. This key should be passphrase-less so that it does not require manual intervention after executing infinisqlmgr.py. This key pair needs to be present in this location only on the host from which infinisqlmgr.py is executed. The public portion of the key needs to be in .ssh/authorized_keys on each host running infinisqld.

Example 1.2. sshkey configuration

sshkey: /home/infinisql/.ssh/id_rsa


Name

ssh — ssh client filename.

ssh

This is the location of the ssh client on the system running infinisqlmgr.py. Obviously, an sshd process needs to be running on each host.

Example 1.3. ssh configuration

ssh: /usr/bin/ssh


Name

infinisqld — infinisqld filename.

infinisqld

This is the location of the infinisqld executable on the target host. The InfiniSQL build process places infinisqld within the sbin directory wherein InfiniSQL is installed.

Example 1.4. infinisqld configuration

infinisqld: /home/infinisql/infinisql_built/sbin/infinisqld


Example 1.5. infinisqld configuration with format string interpolation

infinisqld: %(DEPLOYMENT_DIRECTORY)s/sbin/infinisqld


Name

logfile — infinisqld logfile.

logfile

This is where error and information log messages are written by each infinisqld process. Actually, there are three files. The two additional files are logfile with extension .out and .err for stdout and stderr streams from the process, respectively. Obviously, this is something which needs to be cleaned up as development progresses--but this is how to find informational messages from running infinisqld processes on each host. Multiple nodes running on a particular host should each have a logfile entry defined to distinguish messages from each particular process. The InfiniSQL build process creates a var directory upon installation. This is intended to contain the logfiles, but they can be written anywhere for which username has write permission.

Example 1.6. logfile configuration

logfile: /home/infinisql/infinisql_built/var/infinisqld.log


Example 1.7. logfile configuration with format string interpolation

infinisqld: %(DEPLOYMENT_DIRECTORY)s/var/infinisqld.log


Name

globaladminpassword — administrative password

globaladminpassword

This is the password for the admin user in the _global domain. This is the administrative user for the entire cluster, and is the only user that exists after the cluster is started. Therefore, all administrative activities are made possible through this user.

Example 1.8. globaladminpassword configuration

globaladminpassword: passw0rd


Name

cfghostport — IP:port to listen for configuration commands

cfghostport

This is the IP:tcp port pair upon which infinisqld listens for configuration commands coming from infinisqlmgr.py. The IP portion must be numeric dotted-quad IPv4, or * (to listen on all interfaces), and cannot be a host or other name. It must be reachable by infinisqlmgr.py.

Example 1.9. cfghostport configuration

cfghostport: *:11520


Name

cfgremotehostport — destination IP:port for configuration commands

cfgremotehostport

The is the IP:tcp port pair by which infinisqlmgr.py reaches this infinisqld process to send it configuration commands. It corresponds to the cfghost parameter. Having distinct configuration parameters allows, for instance, clusters to be managed through network address translation, but it does add a bit of configuration complexity. Obviously, infinisqlmgr.py must be able to reach.

Example 1.10. cfgremotehostport configuration

cfgremotehostport: 10.101.2.1:11520


Example 1.11. cfgremotehostport configuration with interpolation

cfgremotehostport: %(IPADDR)s:11520


Name

mgmthost — hostname for infinisqld node

mgmthost

The hostname passed to ssh to log into to launch infinisqld. If this entry is blank, then infinisqlmgr.py will launch infinisql directly on the existing host.

Example 1.12. mgmthost configuration

mgmthost: node1.infinisql.org


Example 1.13. mgmthost configuration with interpolation

mgmthost: %(IPADDR)s


Name

listenhost — address to listen on for raw configuration

listenhost

IPv4 address or hostname for infinisqld to service configuration commands from clients. This is used for user account and schema manipulation. This interface will likely be deprecated in the near future. An '*' means to listen on all interfaces.

Example 1.14. listenhost configuration

listenhost: *


Name

listenport — TCP port to listen on for raw configuration

listenport

TCP port infinisqld to service configuration commands from clients. Corresponds to listenhost.

Example 1.15. listenport configuration

listenport: 11521


Name

ibgatewayhostport — IP:port pair for inter-node cluster communication

ibgatewayhostport

Other nodes in the cluster communicate to this infinisqld node via this IP:port. If there are multiple ibgateways, then those after the first instance are assigned ports in units ascending. So make sure that there are plenty of ports available to listen upon directly above this value.

Example 1.16. ibgatewayhostport configuration

ibgatewayhostport: 10.10.10.5:11530


Example 1.17. ibgatewayhostport configuration with interpolation

mgmthost: %(IPADDR)s:11530


Name

pghost — host for SQL communication

pghost

Clients connect to this hostname or address, or * for all interfaces on the host. infinisqld uses the PostgreSQL Frontend/Backend Protocol on this interface.

Example 1.18. pghost configuration

pghost: *


Name

pgport — TCP port for SQL communication

pgport

Clients connect to this TCP port, which corresponds to pghost.

Example 1.19. pgport configuration

pgport: 15432


Name

transactionagents — number of Transaction Agents

transactionagents

The number of Transaction Agent actor threads to create for this node.

Example 1.20. transactionagents configuration

transactionagents: 8


Name

engines — number of Engines

engines

The number of Engine actor threads to create for this node.

Example 1.21. engine configuration

engine: 4


Name

ibgateways — number of Inbound Gateways

ibgateways

The number of Inbound Gateway actor threads to create for this node.

Example 1.22. ibgateway configuration

ibgateway: 1


Name

obgateways — number of Outbound Gateways

obgateways

The number of Outbound Gateway actor threads to create for this node.

Example 1.23. obgateway configuration

obgateway: 1


Name

anonymousping — allow anonymous ping or not

anonymousping

Whether to allow non logged-in users to send an InfiniSQL ping command through the raw interface.

Example 1.24. anonymousping configuration

anonymousping: 1


Name

badloginmessages — send bad login response messages or not

badloginmessages

Whether to send replies to failed logins through the raw interface.

Example 1.25. badloginmessages configuration

badloginmessages: 1


Name

replica — replica number of which this node is a member

replica

The replica number, starting with 0, to which the node belongs. Currently, only replica 0 is available. This parameter must be set to 0.

Example 1.26. replica configuration

replica: 0


Name

member — index in replica to which this node belongs

member

Each replica has one or more member nodes. This parameter defines the member number for the particular node, starting at member 0. All member numbers must be contiguous whole numbers in a replica. Each node must have a unique member number.

Example 1.27. member configuration for the 8th node in a replica

member: 7


Name

userschemamgrnode — node on which the User Schema Manager actor thread runs

userschemamgrnode

This parameter must be only in the [global] section. It defines the node upon which the User Schema Manager actor runs. There can only be one User Schema Manager per replica.

Example 1.28. userschemamgrnode

userschemamgrnode: 2


Name

deadlockmgrnode — node on which the Deadlock Manager actor thread runs

deadlockmgrnode

This parameter must be only in the [global] section. It defines the node upon which the Deadlock Manager actor runs. There can only be one Deadlock Manager per replica.

Example 1.29. deadlockmgrnode

deadlockmgrnode: 2


Name

activereplica — the replica that supports client interaction

activereplica

This parameter must be only in the [global] section. It defines the replica which is the master for all data, to which other replicas are replicated. Currently, only a single replica is functional, defined as replica 0. This value must be 0.

Example 1.30. activereplica

activereplica: 0


infinisqlmgr.conf Examples

Table of Contents

infinisqlmgr.conf Examples

infinisqlmgr.conf Examples

Example 1. Single Node, infinisqlmgr.py on same host as infinisqld node

[DEFAULT]
DEPLOYMENT_DIRECTORY=/home/infinisql/infinisql_built
username: infinisql
sshkey: /home/infinisql/.ssh/id_rsa
ssh: /usr/bin/ssh
infinisqld: %(DEPLOYMENT_DIRECTORY)s/sbin/infinisqld
logfile: %(DEPLOYMENT_DIRECTORY)s/var/infinisqld1.log
globaladminpassword: passw0rd
cfghostport: *:11520
cfgremotehostport: %(IPADDR)s:11520
mgmthost:
listenhost: *
listenport: 11521
ibgatewayhostport: %(IPADDR)s:11530
pghost: *
pgport: 15432
transactionagents: 8
engines: 4
ibgateways: 1
obgateways: 1
anonymousping: 1
badloginmessages: 1

[global]
userschemamgrnode: 1
deadlockmgrnode: 1
activereplica: 0

[node_1]
IPADDR=127.0.0.1
replica: 0
member: 0

Example 2. Single Node, infinisqlmgr.py on separate host from infinisqld node

[DEFAULT]
DEPLOYMENT_DIRECTORY=/home/infinisql/infinisql_built
username: infinisql
sshkey: /home/infinisql/.ssh/id_rsa
ssh: /usr/bin/ssh
infinisqld: %(DEPLOYMENT_DIRECTORY)s/sbin/infinisqld
logfile: %(DEPLOYMENT_DIRECTORY)s/var/infinisqld1.log
globaladminpassword: passw0rd
cfghostport: *:11520
cfgremotehostport: %(IPADDR)s:11520
mgmthost: %(IPADDR)s
listenhost: *
listenport: 11521
ibgatewayhostport: %(IPADDR)s:11530
pghost: *
pgport: 15432
transactionagents: 8
engines: 4
ibgateways: 1
obgateways: 1
anonymousping: 1
badloginmessages: 1

[global]
userschemamgrnode: 1
deadlockmgrnode: 1
activereplica: 0

[node_1]
IPADDR=10.102.2.1
replica: 0
member: 0

Example 3. Eight Nodes, infinisqlmgr.py on separate host from infinisqld

[DEFAULT]
DEPLOYMENT_DIRECTORY=/home/infinisql/infinisql_built
username: infinisql
sshkey: /home/infinisql/.ssh/id_rsa
ssh: /usr/bin/ssh
infinisqld: %(DEPLOYMENT_DIRECTORY)s/sbin/infinisqld
logfile: %(DEPLOYMENT_DIRECTORY)s/var/infinisqld1.log
globaladminpassword: passw0rd
cfghostport: *:11520
cfgremotehostport: %(IPADDR)s:11520
mgmthost: %(IPADDR)s
listenhost: *
listenport: 11521
ibgatewayhostport: %(IPADDR)s:11530
pghost: *
pgport: 15432
transactionagents: 8
engines: 4
ibgateways: 1
obgateways: 1
anonymousping: 1
badloginmessages: 1

[global]
userschemamgrnode: 1
deadlockmgrnode: 1
activereplica: 0

[node_1]
IPADDR=10.102.2.1
replica: 0
member: 0

[node_2]
IPADDR=10.102.2.2
replica: 0
member: 1

[node_3]
IPADDR=10.102.2.3
replica: 0
member: 2

[node_4]
IPADDR=10.102.2.4
replica: 0
member: 3

[node_5]
IPADDR=10.102.2.5
replica: 0
member: 4

[node_6]
IPADDR=10.102.2.6
replica: 0
member: 5

[node_7]
IPADDR=10.102.2.7
replica: 0
member: 6

[node_8]
IPADDR=10.102.2.8
replica: 0
member: 70

Example 4. Two Nodes, each running on same host with infinisqlmgr.py

[DEFAULT]
DEPLOYMENT_DIRECTORY=/home/infinisql/infinisql_built
username: infinisql
sshkey: /home/infinisql/.ssh/id_rsa
ssh: /usr/bin/ssh
infinisqld: %(DEPLOYMENT_DIRECTORY)s/sbin/infinisqld
logfile: %(DEPLOYMENT_DIRECTORY)s/var/infinisqld1.log
globaladminpassword: passw0rd
cfghostport: *:11520
cfgremotehostport: %(IPADDR)s:11520
mgmthost:
listenhost: *
listenport: 11521
ibgatewayhostport: %(IPADDR)s:11530
pghost: *
pgport: 15432
transactionagents: 8
engines: 4
ibgateways: 1
obgateways: 1
anonymousping: 1
badloginmessages: 1

IPADDR=127.0.0.1

[global]
userschemamgrnode: 1
deadlockmgrnode: 1
activereplica: 0

[node_1]
replica: 0
member: 0

[node_2]
replica: 0
member: 1
cfghostport: *:21520
cfgremotehostport: %(IPADDR)s:21520
listenport: 21521
ibgatewayhostport: *:21530
logfile: %(DEPLOYMENT_DIRECTORY)s/var/infinisqld2.log
pgport: 15433

Part II. InfiniSQL™ User Reference Manual

This is the reference manual for using InfiniSQL. It includes descriptions of SQL capabilities, schema management, user management, and the stored procedure API. There's also an overview of how they tie together.

Table of Contents

2. Using InfiniSQL
3. User and Schema Management
Perl API for User and Schema Management
connect — open TCP connection to InfiniSQL node
send — send a command
describeresponse — show response of sent command
disconnect — close TCP connection to InfiniSQL node
fireforget — send command without collecting response
getcommand — read commands from standard input
User and Schema Commands
login — login to an InfiniSQL node via the raw interface
logout — logout from an InfiniSQL node via the raw interface
createdomain — create domain
createuser — create a user
createschema — create schema
createtable — create a table
addcolumn — adds a column and optional index to a table
compile — compile a SQL statement
loadprocedure — load a stored procedure
ping — Verify up and running
4. SQL Reference
INSERT — Inserts records into a database.
SELECT — Retrieves records from a database.
UPDATE — Modifies records in a database.
DELETE — Deletes records in a database.
SELECTSTOREDPROC — Execute stored procedure.
TRANSACTIONS — Perform transactions.
Future SQL Capabilities
Future SQL Capabilities
5. Stored Procedure API
storedproc — Stored Procedure Programming

Chapter 2. Using InfiniSQL

Users can interact with a running InfiniSQL cluster over TCP on each node in the activereplica. For the time being, that needs to be configured as 0. From a user's standpoint, there is no difference between any of the nodes in a replica other than TCP/IP connection settings. All users, schemata, and data are accessible from each node in the cluster.

The workflow for using InfiniSQL, once up and running, is as follows:

  1. Login as administrative user and create a domain and a user associated with the new domain.

  2. Login as newly-created user. Create schema and tables and indices.

  3. Optionally compile SQL statements, create stored procedures, and load them.

  4. Login as newly-created user on SQL interface. Manipulate data via SQL.

User and Schema Management is accomplished via a distinct TCP/IP port and wire protocol from those used for SQL. This is because the means to manipulate users and schema objects was implemented in InfiniSQL before the SQL capabilities were added. It is planned to deprecate the original, raw interface, in favor of the SQL interface, but for the time, this raw interface is the only way to accomplish user and schema administration tasks.

Chapter 3. User and Schema Management

Table of Contents

Perl API for User and Schema Management
connect — open TCP connection to InfiniSQL node
send — send a command
describeresponse — show response of sent command
disconnect — close TCP connection to InfiniSQL node
fireforget — send command without collecting response
getcommand — read commands from standard input
User and Schema Commands
login — login to an InfiniSQL node via the raw interface
logout — logout from an InfiniSQL node via the raw interface
createdomain — create domain
createuser — create a user
createschema — create schema
createtable — create a table
addcolumn — adds a column and optional index to a table
compile — compile a SQL statement
loadprocedure — load a stored procedure
ping — Verify up and running

Each schema and user is associated with one and only one particular domain. There is a special domain called “_global” from which all other domains and users are created. This is the only domain contained in an InfiniSQL cluster when it is started. Tied to the global domain is the username admin. This user's password is set in the configuration parameter globaladminpassword. The TCP/IP connection settings for this interface are described in the configuration parameters listenhost and listenport.

All user and schema management capabilities are available through perl subroutines contained in scripts/infinisql.plib in the source tree. The specific wire protocol format is expressed in this file, as well as in infinisqld/TransactionAgent.cc. But using the perl subroutines is definitely the fastest way to get up and running. Simply put a line that says “require 'infinisql.plib';” early in a perl script, and use the routines with the appropriate commands.

Perl API for User and Schema Management

Examples for use of these functions abound in the scripts/ and scripts/benchmark/ directories.

Name

connect — open TCP connection to InfiniSQL node

Synopsis

require 'infinisql.plib';

&connect(host, port);

Description

connect to InfiniSQL node on host:port.

Name

send — send a command

Synopsis

require 'infinisql.plib';

&send(command, args...);

Description

send command with list of arguments. Command and all arguments must be string-type. Either enclosed in single or double-quotes, or SCALAR variables whose values were assigned in quotes. So, sending the argument 4 must be done as “4”, for instance.

Name

describeresponse — show response of sent command

Synopsis

require 'infinisql.plib';

&describeresponse(&send(host, port));

Description

Describes response from a command submitted via send function. There are at least 2 lines of output. The first line has 3 integer values separated by tabs. The first 2 values are the length of the response string from the server. The 3rd value is the response code. 0 means successful command completion, non-zero is otherwise. The response codes are defined in infinisqld/defs.h. Subsequent lines of output are results of the command, if any. If the command returns no data, then a 0 is present on the second line of output.

Name

disconnect — close TCP connection to InfiniSQL node

Synopsis

require 'infinisql.plib';

&disconnect;

Description

disconnect the socket created by a previous call to connect.

Name

fireforget — send command without collecting response

Synopsis

require 'infinisql.plib';

&fireforget(command, args...);

Description

Like the &send subroutine, but don't wait for response from server. This cannot be used in conjunction with &describeresponse.

Name

getcommand — read commands from standard input

Synopsis

require 'infinisql.plib';

&describeresponse(&send(&getcommand));

Description

Reads commands from standard input. The first line is the command itself. Subsequent commands are arguments. EOF (CTRL-d) on an empty line submits the command and arguments. Output of &getcommand should be fed to &send.

User and Schema Commands

These commands are used to login via the raw TCP interface and then to administer user and schema objects. The InfiniSQL perl subroutine &send can be used to submit these commands. Each command and argument should be sent as a quoted string, including numeric values. This interface will be deprecated in the near future, but is currently the only way to do necessary activities, such as creating users, tables, and so on. The basic steps necessary to prepare for SQL data manipulation are as follows:

  1. &connect(...)

  2. &send("login", <admin credentials>)

  3. &send("createdomain", ...)

  4. &send("createuser", ...)

  5. &send("logout")

  6. &connect(...)

  7. &send("login", <credentials created above>)

  8. &send("createschema")

  9. &send("createtable", ...)

  10. &send("addcolumn", <1st column info>)

  11. &send("addcolumn", <2nd column info>)

  12. &send("addcolumn", <nth column info>)

  13. &send("compile", <UPDATE accounttable SET balance += 1000000 where username='mystore'>)

  14. &send("loadprocedure", <GimmeMoney.so>)

Note

The previous commands are not necessarily syntactically correct, but are displayed for illustration of the workflow. Refer to the specific reference entries for each command for correct usage.

From here, it's possible to log in and manipulate data in the created tables either with ad hoc SQL commands and with any stored procedures that have been loaded.

Name

login — login to an InfiniSQL node via the raw interface

login

Parameters:

  • domainname

  • username

  • password

Login to InfiniSQL. For a newly-started cluster, the only domain is "_global" and its only user is "admin". The password is set in the config parameter globaladminpassword.

Example 3.1. login example

&send("login", "_global", "admin", "passw0rd");


Name

logout — logout from an InfiniSQL node via the raw interface

logout

No parameters.

Logout of InfiniSQL. The TCP/IP connection is also severed, so &connect needs to be submitted before login to login again.

Example 3.2. logout example

&send("logout");


Name

createdomain — create domain

createdomain

Parameters:

  • domainname

Create a domain called domainname. This command can only be executed by the admin user of the _global domain.

Example 3.3. createdomain example

&send("createdomain", "texas");


Name

createuser — create a user

createuser

Parameters:

  • domainname

  • username

  • password

Creates user username associated with domain domainname with password password. This command can only be executed by the admin user of the _global domain.

Example 3.4. createuser example

Create user mayor in domain texas with password austin:

&send("createuser", "texas", "mayor", "austin");


Name

createschema — create schema

schema

No Parameters.

Create a schema associated with the logged in domain. Only one schema can exist per domain, and a schema must be present for tables and indices to be created.

Example 3.5. createschema example

&send("createschema");


Name

createtable — create a table

createtable

Parameters:

  • tablename

Returns numeric tableid.

Create a table within schema of logged-in domain. Unlike SQL's CREATE TABLE, this only creates a bare table with no columns or indices.

Example 3.6. createtable example

&send("createtable", "mastertable");


Name

addcolumn — adds a column and optional index to a table

addcolumn

Parameters:

  • tableid

  • type

  • length (0 for all types other than charx

  • columnname

  • indextype

Returns numeric columnid of newly created column.

Create a new column with optional index. At least one column per table must have an index, or else SELECT will not be able to retrieve data. tableid is the return value from the createtable call on the associated table.

The possible data types are as follows, with their descriptions.

  • int: 64bit signed integer

  • bool: 0 or 1

  • float: 128bit floating point

  • char: single 8-byte character

  • charx: length 8-byte characters

  • varchar: characters of arbitrary length

Indextypes are:

  • none: no index type

  • unique: all values must be unique (while allowing multiple nulls)

  • nonunique: values need not be unique

  • uniquenotnull: values unique, with no nulls. Equivalent to SQL PRIMARY KEY

  • nonuniquenotnull: values need not be unique, and no nulls allowed

Example 3.7. addcolumn example

Create integer column accountid on tableid 1 with unique and not null constraints:

&send("addcolumn", "1", "int", "0", "accountid", "uniquenotnull");

Other examples abound under the scripts/ directory.


Name

compile — compile a SQL statement

compile

Parameters:

  • statementname

  • statement

Returns statementname.

Compile a SQL statement for use in stored procedures. Parameters for the statement are within the statement, and are of the form :[0-9]+. Parameters must start at 0, and increment in single units. Allowed parameters are for column values to insert, update, or in search expressions. Table and column names cannot be stored procedure parameters.

Example 3.8. compile example

&send("compile", "creditseller", "UPDATE accountstable SET balance = balance + :0 where accountid = :1");


Name

loadprocedure — load a stored procedure

loadprocedure

Parameters:

  • pathname

  • procedurename

Returns procedurename.

Load a previously-compiled stored procedure into each running infinisqld instance in the cluster. This stored procedure is a C++ module which gets dynamically loaded. It must be present as pathname on every host which runs 1 or more infinisqld nodes. Stored procedures must be loaded after SQL statements which they contain are compiled. The procedurename must correspond to the procedure name within the stored procedure source, explained more fully in a separate part of this documentation.

Example 3.9. loadprocedure example

&send("loadprocedure", "/home/infinisql/infinisql_built/procs/PgbenchNoinsertProc.so", "PgbenchNoInsert");


Name

ping — Verify up and running

ping

No Parameters.

Verify that infinisqld node is up and running and able to answer user and schema management commands.

Example 3.10. ping example

&describeresponse(&send("ping"));


Chapter 4. SQL Reference

Table of Contents

INSERT — Inserts records into a database.
SELECT — Retrieves records from a database.
UPDATE — Modifies records in a database.
DELETE — Deletes records in a database.
SELECTSTOREDPROC — Execute stored procedure.
TRANSACTIONS — Perform transactions.

InfiniSQL uses the PostgreSQL Frontend/Backend Protocol, Version 3. This means that any PostgreSQL client version 7.4 or later, regardless of platform or language, should be able to connect to InfiniSQL. InfiniSQL does not support PostgreSQL compiled statements, which are frequently implemented by default on client side libraries, such as for perl. InfiniSQL has been tested using the psql command-line tool as well as the DBD::Pg perl library. For perl, the pg_server_prepare parameter to connect via DBD::Pg must be set to 0. Similar configuration for other languages may need to be performed in order for those libraries to work with InfiniSQL.

Note

No code from PostgreSQL, except for macro definitions (in infinisqld/pgoids.h) necessary to implement the Frontend/Backend Protocol, is included in InfiniSQL.

InfiniSQL does not support SSL (yet), so connections are unencrypted. Also, InfiniSQL only listens on TCP/IP ports, and not UNIX domain sockets. The TCP/IP host and port for each node are configured by the pghost and pgport parameters in etc/infinisqlmgr.conf. Clients can connect to any running node. All database contents are available identically from all nodes. InfiniSQL only supports clear text passwords currently--clients normally negotiate this properly, but if you try to force InfiniSQL to use some other method, such as MD5 hashing, then authentication will fail. The InfiniSQL notion of domain is equivalent to PostgreSQL's database. So, connecting to a database via PostgreSQL client maps to the domain created previously.

Example 4.1.  Connecting via psql. 

PGSSLMODE=disable psql -h 127.0.0.1 -p 15432 -d texas -U mayor

Connects to infinisqld node on localhost port 15432. Domain name is "texas" and userid "mayor". A password prompt appears next. If no dbname is sent, then the server will attempt to authenticate the username with the same domainname, just like a PostgreSQL server


Note

Not all capabilities available through commands in the psql client are functional when connected to an InfiniSQL database. If they send a command to the server which is currently unsupported, such as \d, then an error message will be returned. Those commands which do not communicate with the backend work properly.

If a query does not succeed, then an error response is generated. Only a handful of error types and messages are currently emitted.

Note

One error type returned is 55P03 “lock not available”. This happens when a transaction attemps to lock a record which is already locked. A workaround is to retry the transaction until the message goes away. If the record never unlocks, it's because some other transaction has never released it. Deadlock managment is currently not implemented. These limitations will be corrected in a release very soon.

Name

insert — Inserts records into a database.

insert

INSERT INTO <tablename> VALUES (<value list>);

Note

All column values for a record must be listed, comma-separated, in column order. InfiniSQL does not yet support default column values.

Example 4.2. INSERT example

INSERT INTO mastertable VALUES (5, false, 88);


Name

select — Retrieves records from a database.

select

SELECT <* | column list> FROM <tablename> [WHERE <search expression>] [FOR UPDATE] [NO LOCK];

Only a single table can be included--InfiniSQL currently does not support joins. The search expression supports the following SQL-92 predicates: COMPARISON (=, <>, <, >, <=, and >=), BETWEEN, NULL, IN, LIKE, NOT BETWEEN, NOT NULL, NOT IN, and NOT LIKE.

These are the most common search expression predicates in use. These predicates can be combined with AND, and OR, such as: “WHERE cola=7 AND colc BETWEEN 7 AND 43”. NOT is only supported where listed above. “NOT (WHERE cola=7 OR colc BETWEEN 7 AND 43)”, for example, is not supported by InfiniSQL. Other predicates, such as EXISTS and UNIQUE, will be implemented in an upcoming release. The fields in the search expression must have some index type assigned when created by addcolumn.

By default, a read lock is acquired for rows returned. That can be over-ridden by optional "FOR UPDATE" and "NO LOCK" clauses. The FOR UPDATE clause, if present, puts a write lock on any records returned. A write lock is necessary for subsequent SQL commands in the transaction, such as UPDATE or DELETE, to modify the record. NO LOCK, if present, places no type of lock on the record. NO LOCK may have performance advantages, but also data quality implications.

The script scripts/regression.pl attempts to test all of the supported predicate types and combinations.

Example 4.3. SELECT example

SELECT * FROM mastertable WHERE accountid >= 25;


Name

update — Modifies records in a database.

update

UPDATE <tablename> SET <list of fieldname [=, +=, -=, *=, /=] [-+]value, ...> [WHERE <search expression>];

The search expression capabilities are the same as for SELECT.

Example 4.4. UPDATE example

UPDATE intuniquetable set intunique2=60 WHERE intunique2=50;


Name

delete — Deletes records in a database.

update

DELETE FROM <tablename> [WHERE <search expression>];

The search expression capabilities are the same as for SELECT.

Example 4.5. DELETE example

DELETE FROM liketable WHERE charxcol LIKE 'abcdefghijklmnopqrstuvwxyz';


Name

selectstoredproc — Execute stored procedure.

selectstoredproc

SELECT <procname> (<parameterlist>);

The results of this query are formatted like those from a SELECT query.

Example 4.6. SELECTSTOREDPROC example

SELECT SqlProc (20, 90, 35);


Name

transactions — Perform transactions.

“BEGIN [WORK];” to start transactions. “ROLLBACK [WORK]” to roll back. “COMMIT [WORK]” to commit. “END” is a synonym for “COMMIT”.

InfiniSQL behaves with auto-commit based on the variable session_isautocommit in the Pg class (infinisqld/Pg.cc & infinisqld_Pg.h). It is currently set to true in the source. Auto-commit is where the server puts each individual statement into a transaction and executes it if there is no active transaction when the statement was typed. If a transaction is manually started with BEGIN, then auto-commit does not apply. If a statement causes an error while in a transaction, then no further statements will be executed until ROLLBACK is received.

Future SQL Capabilities

Table of Contents

Future SQL Capabilities

Future SQL Capabilities

Put simply, if the SQL capabilities are not described above, then they likely aren't available in InfiniSQL as of release 0.1.2-alpha. Places to look in the code mainly include infinisqld/lexer.ll, parser.yy, Asts.cc, Asts.h, Larxer.cc, Larxer.h, Transaction.cc, Transaction.h, Pg.cc, and Pg.h. Missing standard features are planned for future releases. Some are more simple to implement than others. The InfiniSQL code base is able to support all standard SQL features while still maintaining its uniquely scalable transaction processing capabilities. Much of the code for these features has already been written, but not yet completed. It's just a matter of development effort to bring these capabilities to life. These include, but aren't limited to:

  • Lock & deadlock management

  • Joins

  • Subqueries

  • ORDER BY, GROUP BY (aggregate), HAVING clauses

  • CREATE TABLE, CREATE COLUMN, etc.: all data definition, to deprecate the commands in the raw interface, such as createtable, addcolumn, and the like.

Chapter 5. Stored Procedure API

Table of Contents

storedproc — Stored Procedure Programming

Name

storedproc — Stored Procedure Programming

storedproc

Working examples of InfiniSQL stored procedures and how they are built are located in the procs/ directory of the source distribution. The makem.sh script builds them and deploys to the procs/ directory in the installation directory corresponding to the --prefix option to ./configure. This installation directory must be passed as an argument to makem.sh.

Stored procedures are dynamically-loaded custom C++ classes. The structure and contents of the source files are driven in part by requirements for dynamically loaded C++ classes and functions. This HOWTO is a good basis for understanding how dynamic C++ modules are implemented. Stored procedures link against a single library, <installationdir>/lib/libinfinisql.so. They must include the header file <installationdir>/include/infinisql.h. Class factory function prototypes to instantiate and delete stored procedure objects are as follows:

extern "C" ApiInterface* InfiniSQL_<domainname>_<procedurename>_create(taPtr,  
 pgPtr,  
 destructorPtr); 
TransactionAgent *taPtr ;
ApiInterface *pgPtr ;
void destructorPtr ;
 

extern "C" ApiInterface* InfiniSQL_<domainname>_<procedurename>_destroy(p); 
ApiInterface *p ;
 

domainname is that submitted by createdomain and procedurename is from loadprocedure, and invoked through SQL with “SELECT <procname> (<parameterlist>)”. Since the class factories are global functions, this ensures that no name space conflict occurs between domains.

Note

Since stored procedures are dynamically loaded C++ programs, there is no way for infinisqld to protect from fatal errors caused by custom stored procedure code.

To avoid name conflicts, the custom class should follow a similar naming convention as that of the factory functions--but the author has set a bad example in the shipped procedures themselves. The class must inherit from class ApiInterface.

Beyond syntactic requirements--which should be inferable quite easily from the source code anyway--a very important characteristic of stored procedure programming is that of continuations. infinisqld makes heavy use of asynchronous event processing to allow a relatively small number of threads to handle massive concurrent workloads. Most transaction operations, such as performing SQL queries, involve message passing between multiple threads. No threads block waiting for replies. Instead, they store the state of the transaction and proceed to work on other activities as events occur. When an appropriate response message is received, the transaction picks up where it left off. InfiniSQL implements continuations by storing the return function and its intended parameter values to be called later. This is home-brew continuations. Better solutions likely exist, such as setjmp/longjmp, but that can be solved in the future through refactoring. The return function for stored procedures must have the following definition:

void <continueFunc[12]>(entrypoint,  
 statePtr); 
int64_t entrypoint ;
void *statePtr ;
 

Allowable function names are from the class ApiInterface, and are continueFunc1 and continueFunc2. Other continuation functions which must be defined in each stored procedure, but which also must not be used, are continuePgFunc, continuePgCommitimplicit, continuePgCommitexplicit, continuePgRollbackimplicit, and continuePgRollbackexplicit. These functions are used by class Pg, which also inherits from class ApiInterface. All custom logic for the stored procedure must occur either in the constructor function, continueFunc1 or continueFunc2. entrypont can be used to keep track of state, such as through a switch statement within contnueFunc1 or continueFunc2. Alternately, state can be maintained in class member variables.

Relevant ApiInterface Member Variables

  • ApiInterface *pgPtr: Pg object which called stored procedure. Also the object to which control will return when procedure exits.

  • TransactionAgent *taPtr: TransactionAgent actor thread object associated with pgPtr

  • int64_t domainid: numeric indentifier for associated domainname

  • Transaction *transactionPtr: associated Transaction object (or NULL if no open transaction)

  • vector<std::string> pgPtr->statementPtr->queries[0].storedProcedureArgs: Arguments passed to stored procedure as formatted strings

  • struct results_s results: results from transaction function calls

ApiInterface Functions

This is where the storedprocedure interacts with the database. With the exception of beginTransaction, all functions require continuation to occur. The stored procedure must inform these functions how and where to return to the stored procedure for execution once the requested activity has been accomplished.

Note

Perhaps the most important thing to remember about calling these functions is that control flow must exit the stored procedure as soon as possible after calling! It is a good idea to use a return; statement as soon as possible after calling. This is to ensure correct program flow. Returning from the stored procedure causes control to flow back to allow processing of other transaction events.

Note

Don't block or sleep! Each stored procedure invocation is performed in a thread which may be handling thousands of other transactions at the same time. This means that time-wasting activities such as sleep or blocking i/o, will cause processing to slow down for all other transactions handled by the TransactionAgent thread.

beginTransaction

void beginTransaction(); 
 

Creates a Transaction object. Places a pointer to the object in transactionPtr. Only one Transaction can be open at a time for the stored procedure. The stored procedure should check the value of transactionPtr before calling this--if it is not null, then it likely means that something has created a transaction already. Calling this function will cause another open transaction to leak away. This function is not the start of a continuation, so program flow following this function can proceed directly.

execStatement

bool execStatement(stmtname,  
 args,  
 reentryfunction,  
 reentrypoint,  
 reentrydata); 
const char *stmtname;
vector<string>& args;
apifPtr reentryfunction;
int64_t reentrypoint;
void reentrydata;
 

Executes statement stmtname corresponding to that compiled previously, with args as parameters. The index values of the vector correspond to parameter numbers. args[0] & args[1] become parameters :0 & :1, respectively, and so on. reentryfunction should be a function pointer to the function which will continue once stmtname executes, most likely either &ApiInterface::continueFunc1 or &ApiInterface::continueFunc2. reentrypoint and reentrydata are arguments to be passed to reentryfunction.

args is a vector of formatted strings representing type and value for each parameter intended for stmtname. args[n][0] is a character of type corresponding to symbol definitions like OPERAND_.* in infinisqld/defs.h. Remaining characters represent the value of the parameter. Types OPERAND_INTEGER and OPERAND_BOOLEAN are stored as int64_t, and OPERAND_FLOAT as long double. OPERAND_STRING constitutes a string::sutbstr() from pos 1 through string::npos. For example, Ast::evaluate() in infinisqld/Asts.cc is a place where these valuables are manipulated.

Return Value

This function returns immediately with either true or false. true means that stmtname exists to be executed. false means the opposite. Make sure that loadprocedure for stmtname executes successfuly before calling execStatement.

Results

Status in results.statementStatus. Anything other than STATUS_OK (0) should be cause to rollback. Results from SELECT are stored in results.SelectFields and results.selectResults. From UPDATE, INSERT, DELETE, in results.statementResults.

commit

void commit(re,  
 recmd,  
 reptr); 
apifPtr re;
int64_t recmd;
void *reptr;
 

Commits transactionPtr.

Results

results.statementStatus gives status. If not 0, then a rollback should be attempted. transactionPtr needs to be deleted once successfully committed or else it will leak memory.

rollback

void rollback(re,  
 recmd,  
 reptr); 
apifPtr re;
int64_t recmd;
void *reptr;
 

Rolls back transactionPtr.

Results

results.statementStatus gives status. If not 0, then there isn't much to do. In case of success or failure, delete transactionPtr to avoid a memory leak.

Sending Results and Exiting

Stored procedure results are sent as rows and columns from a SELECT query. For InfiniSQL, this means populating the following:

  • pgPtr->results.selectFields

  • pgPtr->results.selectResults, and

  • pgPtr->results.statementStatus

Those objects are described in infinisqld/Pg.h.

Exiting requires deleting the Statement object associated with the client's stored procedure invocation "SELECT Proc (...)", deleting the current object and then executing the function to continue the Pg object which called the stored procedure. An example is in procs/PgbenchNoinsertProc.cc exitProc.

Part III. InfiniSQL™ Command and File Reference Manual

Table of Contents

6. Files For Operating InfiniSQL
Executables
infinisqlmgr.py — InfiniSQL Cluster Startup
infinisqld — InfiniSQL daemon node process
prime_regression.pl — Prepares InfiniSQL for regression test
regression.pl — SQL regression test
entercommands.pl — User and schema management command shell.
ping.pl — Sends raw interface ping command
bm.pl — Launch benchmark job
collectcpudaemons.sh — Start resource collection jobs
deploy.sh — Install InfiniSQL on multiple hosts
fillkeyval.pl — Inserts data for keystore benchmarking
fill.pl — Inserts data for benchmarking 3-update+ stored procedures
primebm.pl — Creates users and tables for benchmarking
getlogs.sh — Collect node info and error logs
killbm.sh — Kill benchmark jobs
killdaemons.sh — Kill infinisqld processes
moforker.pl — Launch benchmark jobs
report.pl — Report a Benchmark Run
Configs
infinisql.conf — Configure InfiniSQL
clients — List of benchmark client hosts
daemonhosts — List of hosts with infinisqld processes
daemons — List of each infinisqld node
Other Files
lib — library
storedprocs — Compiled stored procedures
infinisql.plib — Perl subroutines
pgbenchpatch — Patch for pgbench
pgbenchtests — pgbench scripts
regression.out-sample — Sample output from regression test

Chapter 6. Files For Operating InfiniSQL

Table of Contents

Executables
infinisqlmgr.py — InfiniSQL Cluster Startup
infinisqld — InfiniSQL daemon node process
prime_regression.pl — Prepares InfiniSQL for regression test
regression.pl — SQL regression test
entercommands.pl — User and schema management command shell.
ping.pl — Sends raw interface ping command
bm.pl — Launch benchmark job
collectcpudaemons.sh — Start resource collection jobs
deploy.sh — Install InfiniSQL on multiple hosts
fillkeyval.pl — Inserts data for keystore benchmarking
fill.pl — Inserts data for benchmarking 3-update+ stored procedures
primebm.pl — Creates users and tables for benchmarking
getlogs.sh — Collect node info and error logs
killbm.sh — Kill benchmark jobs
killdaemons.sh — Kill infinisqld processes
moforker.pl — Launch benchmark jobs
report.pl — Report a Benchmark Run
Configs
infinisql.conf — Configure InfiniSQL
clients — List of benchmark client hosts
daemonhosts — List of hosts with infinisqld processes
daemons — List of each infinisqld node
Other Files
lib — library
storedprocs — Compiled stored procedures
infinisql.plib — Perl subroutines
pgbenchpatch — Patch for pgbench
pgbenchtests — pgbench scripts
regression.out-sample — Sample output from regression test

The files described in this reference are executables and configuration files used in operating InfiniSQL. They don't include all of the source files used for building. There are two main directories in which these files are contained: sourcedir and installationdir. sourcedir is created by doing "git clone git://github.com/infinisql/infinisql.git", or from extracting a compressed tarfile to initially retrieve the source. installationdir is the destination passed as the value for --prefix passed to the ./configure script in the resulting build process.

Note

installationdir should be independent from all other directories, and not be a shared system directory, such as /usr/local. This is to ease management of InfiniSQL.

Executables

Name

infinisqlmgr.py — InfiniSQL Cluster Startup

infinisqlmgr.py

Directory: <sourcedir>/infinisqlmgr

Usage: ./infinisqlmgr.py

Takes no arguments. Reads contents of <sourcedir>etc/infinisqlmgr.conf and launches all infinisqld node processes. Uses password-less ssh (unless mgmthost parameter is blank). Exits after completion. Hangs or prints out a python stack trace if encounters any errors.

Name

infinisqld — InfiniSQL daemon node process

infinisqld

Directory: <installationdir>/sbin

Usage: this is not executed directly, but by infinisqlmgr.py

InfiniSQL server process. Writes informational and error messages based on logfile config parameter.

Name

prime_regression.pl — Prepares InfiniSQL for regression test

prime_regression.pl

Directory: <sourcedir>/scripts

Usage: ./prime_regression.pl -h <hostname>

Sets up user & schema objects to prepare for regression test. Connects to hostname on TCP port 11521. Can be any node in the cluster. Config parameters listenhost and listenport are what this script needs to connect to.

Name

regression.pl — SQL regression test

regression.pl

Directory: <sourcedir>/scripts

Usage: ./regression.pl -h <hostname>

Performs SQL regression test. Hundreds of queries. Connects to hostname on TCP port 11521. Can be any node in the cluster. Config parameters listenhost and listenport are what this script needs to connect to.

Name

entercommands.pl — User and schema management command shell.

entercommands.pl

Directory: <sourcedir>/scripts

Usage: ./entercommands.pl

Connects to node on localhost:11521 and sends arbitrary commands through the raw format, such as login, createtable, and so on. Data entry is described in the description for &getcommand.

Name

ping.pl — Sends raw interface ping command

Directory: <sourcedir>/scripts

Usage: ./ping.pl

Sends InfiniSQL ping to localhost:11521.

Name

bm.pl — Launch benchmark job

bm.pl

Directory: <sourcedir>/scripts/benchmark

Usage: ./bm.pl -test=<testtype> [--accountids=<#>][--duration=<# seconds>][--nclients=<#>] [--bmresultsdir=<somedir>]

Executes benchmark. Distributes client traffic across hosts listed in clients, to hit servers in daemons. Testtypes are either to fill benchmark tables, or to execute the benchmark. To fill benchmark tables, testtype is either preparepgbench or preparekeyval, with accountids sets of rows. Benchmark testtypes are procedure, procedurenoinsert, multistatements, setkey, and getkey. These correspond with files ending in .pgb.

bm.pl ssh's into each client host and executes moforker.pl to launch either fill scripts or InfiniSQL's patched version of pgbench. pgbench tests run for --duration seconds and launch a total number of --nclients, distributed round-robin across hosts listed in clients. pgbench output is written on each benchmark client into directory --bmresultsdir.

bm.pl outputs a number representing UNIX time, which is also written in the files for this particular test written in --bmresultsdir. Just note the output number and go analyze the data later. Since it's in UNIX time format, it can be correlated with system resource collection jobs easily.

Examples:

./bm.pl --test=preparepgbench --accountids=1000000

Inserts 1,000,000 rows into each of 3 tables in benchmark database.

./bm.pl --test=procedurenoinsert --duration=180 --bmresultsdir=/home/infinisql/bmresults --nclients=504

Connect to InfiniSQL with 100,800 clients (assuming each pgbench connects with 200) and execute stored procedure with 3 updates and a select for 180 seconds.

Name

collectcpudaemons.sh — Start resource collection jobs

collectcpudaemons.sh

Directory: <sourcedir>/scripts/benchmark

Usage: ./collectcpudaemons.sh

Starts system resource collection jobs on each host in daemonhosts. These scripts are not included in the source distribution, but are easy to write. The intention of these scripts is to collect data from things such as top, mpstat, bwm-ng, or anything else, during benchmark runs.

Name

deploy.sh — Install InfiniSQL on multiple hosts

deploy.sh

Directory: <sourcedir>/scripts/benchmark

Usage: ./deploy.sh <installationdir> <sourcedir>

rsyncs directories on command line (must be 2 and only 2) to each host in daemonhosts. Very handy for duplicating installation files across any number of hosts.

Name

fillkeyval.pl — Inserts data for keystore benchmarking

fillkeyval.pl

Directory: <sourcedir>/scripts/benchmark

Usage: Called by moforker.pl, not directly

Name

fill.pl — Inserts data for benchmarking 3-update+ stored procedures

fill.pl

Directory: <sourcedir>/scripts/benchmark

Usage: Called by moforker.pl, not directly

Name

primebm.pl — Creates users and tables for benchmarking

primebm.pl

Directory: <sourcedir>/scripts/benchmark

Usage: ./primebm.pl -h <host>

Sets up user & schema objects to prepare for benchmark tests. Connects to hostname on TCP port 11521. Can be any node in the cluster. Config parameters listenhost and listenport are what this script needs to connect to.

Name

getlogs.sh — Collect node info and error logs

getlogs.sh

Directory: <sourcedir>/scripts/benchmark

Usage: ./getlogs <unixsecond>

Collects logs from <installationdir>/var on each host listed in daemonhosts and copies them to /home/mtravis/infinisql_built/var. A directory is created with the unixsecond and daemon hostname. This script should probably be modified somewhat unless your userid is mtravis.

Name

killbm.sh — Kill benchmark jobs

killbm.sh

Directory: <sourcedir>/scripts/benchmark

Usage: ./killbm.sh

Kills pgbench and perl jobs on each host in clients.

Name

killdaemons.sh — Kill infinisqld processes

killdaemons.sh

Directory: <sourcedir>/scripts/benchmark

Usage: killdaemons.sh

Kills infinisqld processes on each host in daemonhosts.

Name

moforker.pl — Launch benchmark jobs

moforker.pl

Directory: <sourcedir>/scripts/benchmark

Usage: Not called directly, but by bm.pl.

bm.pl executes moforker.pl on each host in clients, which in turn invokes sets of either fill.pl, fillkeyval.pl, or pgbench, depending on test type passed to bm.pl.

Name

report.pl — Report a Benchmark Run

report.pl

Directory: <sourcedir>/scripts/benchmark

Usage: ./report.pl -t TESTTYPE -s UNIXSECOND

Create a report based on a benchmark run. -t and -s arguments are used to read files on each benchmark client machine within bmresults directory.

Configs

These are configuration files.

Name

infinisql.conf — Configure InfiniSQL

Directory: sourcedir/etc

Configuration file for InfiniSQL, read by infinisqlmgr.py. Parameters described in Configuration File.

Name

clients — List of benchmark client hosts

clients

Directory: <sourcedir>/scripts/benchmark

Hosts to be used for benchmark clients. Need to be accessible by passwordless ssh.

Name

daemonhosts — List of hosts with infinisqld processes

daemonhosts

Directory: <sourcedir>/scripts/benchmark

Hosts with InfiniSQL daemon processes running. Need to be accessible by passwordless ssh.

Name

daemons — List of each infinisqld node

Directory: <sourcedir>/scripts/benchmark

List of each node to be benchmarked. Tab-delimited hostname and pgport. This is the way that benchmark clients will reach each node.

Other Files

Name

lib — library

lib

Directory: <installationdir>/lib

Library to be linked with for stored procedures. Shared and static versions.

Name

storedprocs — Compiled stored procedures

storedprocs

Directory: <installationdir>/procs

Compiled stored procedure shared objects.

Name

infinisql.plib — Perl subroutines

infinisql.plib

Directory: <sourcedir>/scripts

Perl subroutines described in the section called “ Perl API for User and Schema Management ”. Edit the variables $SCRIPTDIR and $PROCDIR to suit your environment.

Name

pgbenchpatch — Patch for pgbench

pgbenchpatch

Directory: <sourcedir>/misc

Apply pgbench_persecond-v1.patch against PostgreSQL 9.2.4. Adds features to facilitate benchmarking.

In a PostgreSQL 9.2.4 source directory, patch pgbench: 

patch -p1 < <infinisqlsourcedir>/misc/pgbench_persecond-v1.patch

If you have not already done so, ./configure and then make PostgreSQL. Then: 

cd contrib/pgbench
make
sudo cp pgbench /usr/local/bin

The command line arguments and features that this patch adds are as follows:

  • -I: do not abort connection if transaction error is encountered. InfiniSQL returns an error if records are locked, so pgbench was patched to tolerate this. This is pending a fix, but until then, pgbench needs to carry on. The specific error emitted from the server is written to stderr for each occurrence. The total quantity of transactions is not incremented if there's an error.

  • --per-second=NUM: report per-second throughput rate on stdout. NUM is the quantity of transactions in each batch that gets counted. The higher the value, the less frequently gettimeofday gets called. gettimeofday invocation can become a limiting factor as throughput increases, so minimizing it is beneficial. For example, with NUM of 100, time will be checked every 100 transactions, which will cause the per-second output to be in multiples of 100.

  • -P PASSWORD: pass the db password on the command line. This is necessary for InfiniSQL benchmarking because hundreds or more separate pgbench processes can be launched, and InfiniSQL requires password authentication. Having to manually enter all those passwords would making benchmarking impossible.

  • --urandom: use /dev/urandom to provide seed values for randomness. Without this, multiple pgbench processes are likely to generate the same sequence of "random" numbers. This was noticeable in InfiniSQL benchmarking because of the resulting extremely high rate of locked records from having stored procedures invoked with identical parameter values.

Name

pgbenchtests — pgbench scripts

pgbenchtests

Directory: <sourcedir>/scripts/benchmark

Files with .pgb extension. Tests used by pgbench. Format described under Custom Scripts heading of pgbench docs.

Name

regression.out-sample — Sample output from regression test

regression.out-sample

Directory: <sourcedir>/scripts

This is output from a successful regression.pl test. Use it to compare with output from testing your own installation. The line count can vary--this is due to inexact matching of floating point values. Those queries should probably be removed from the regression test at some point for consistency in test output.