User Guide - Crunchy Proxy

Releases

Users can download a compiled version of crunchy-proxy from the github repo site in the Releases tab.

A Docker image is also found in the DockerHub at https://hub.docker.com/r/crunchydata/crunchy-proxy/

Usage

Start

Start an instance of the proxy.

$> crunchy-proxy start

Options:

Option	Default	Description
--config	/etc/crunchy-proxy/config.yaml	the path to the proxy’s configuration file
--background	false	run the proxy in the background
--log-level	info	the logging level

Stop

Stop an instance of the proxy. This command can take optional parameters to specify the host and port of the target proxy to stop.

$> crunchy-proxy stop

Options:

Option	Default	Description
--host	localhost	the host address of the proxy’s admin server
--port	8000	the host port of the proxy’s admin server

Health

Show the health of the nodes configured for an instance of the proxy. The command can take optional paramters to specify the host and port of the target proxy.

$> crunchy-proxy health

Options:

Option	Default	Description
--host	localhost	the host address of the proxy’s admin server
--port	8000	the host port of the proxy’s admin server
--format	plain	the format of the results of the command. Valid formats are 'plain' and 'json'

Node

Show information about the nodes that are configured for an instance of the proxy. This command can take optional parameters to specify the host and port of the target proxy.

$> crunchy-proxy node

Option	Default	Description
--host	localhost	the host address of the proxy’s admin server
--port	8000	the host port of the proxy’s admin server
--format	plain	the format of the results. Valid formats are 'plain' and 'json'

Stats

Show statistics information about the proxy. This command can take optional parameters to specify the host and port of the target proxy.

$> crunchy-proxy stats

Option	Default	Description
--host	localhost	the host address of the proxy’s admin server
--port	8000	the host port of the proxy’s admin server
--format	plain	the format of the results. Valid formats are 'plain' and 'json'

Version

Show version information about the proxy. This command can take optional parameters to specify the host and port of the target proxy.

$> crunchy-proxy version

Option	Default	Description
--host	localhost	the host address of the proxy’s admin server
--port	8000	the host port of the proxy’s admin server

Configuration

The proxy configuration is controlled by a single configuration file which is written in YAML format.

The YAML file is read at startup and is currently not reloaded after execution starts.

Configuration sections:

server

Parameter	Description
proxy:hostport	the host:port that the proxy server will listen to
admin:hostport	the host:port that the proxy admin server will listen to

Example

server:
  proxy:
    hostport: localhost:5432
  admin:
    hostport: localhost:8000

nodes

Parameter	Description
<node>:hostport	the host:port of the <node>
<node>:role	the role of the <node>, valid values are 'master' and 'replica'
<node>:metadata	not implemented

Where <node> is the name given to the node.

nodes:
  master:
    hostport: 192.168.0.100:5432
    role: master
    metadata: {}
  replica1:
    hostport: 192.168.0.101:5432
    role: replica
    metadata: {}

credentials

Parameter	Description
username	the username for the pool connections
database	the database for the pool connections
password	the password for the pool connections
options	connection string options other than those listed above
ssl:enable	enable SSL connections
ssl:sslmode	the SSL mode for establishing pool connections

pool

Parameter	Description
capacity	the number of pool connections to create for each node configured

Example

pool:
  capacity: 2

healthcheck

Parameter	Description
delay	seconds to delay between health checks
query	SQL to user for the health check

healthcheck:
   delay: 60
   query: select now();

Testing

Multiple testing envrionments are provided for testing the proxy.

Docker

A test script is provided that will run a PostgreSQL cluster, with a single master and replica. Run the database script as follows:

$> export CCP_IMAGE_TAG=centos7-9.5-1.2.7
$> ./scripts/docker/run-cluster.sh

This will start two docker containers that execute the PostgreSQL cluster.

The Postgres 9.5 master container listens on localhost:12000 and a replica container listens on localhost:12002

The PostgreSQL user id is postgres, the password is password, and you would connect to these container database like this using psql:

$> psql -h 127.0.0.1 -p 12000 -U postgres postgres
$> psql -h 127.0.0.1 -p 12002 -U postgres postgres

Stop the containers like this:

$> docker stop master
$> docker stop replica

Start the containers like this:

$> docker start master
$> docker start replica

Vagrant

A vagrant configuration is provided that will run a PostgreSQL cluster, with a single master and replica.

To start the environment run:

$> cd ./scripts/vagrant
$> vagrant up master
$> vagrant up replica

The above will create two separate VM’s that are based of off CentOS 7 and provisioned using Ansible.

To stop the environment run:

$> cd ./scripts/vagrant
$> vagrant halt

The PostgreSQL user id is postgres, the password is password, and you would connect to these container database like this using psql:

$> psql -h 192.168.56.100 -U postgres postgres
$> psql -h 192.168.56.101 -U postgres postgres

Test Execution

Start the crunchy-proxy like this:

$> go run main.go start --config=./examples/config.yaml

This will do the following:

start an admin service on localhost:8000
listen on localhost:5432 for client requests
read config.yaml and set up a runtime configuration
route any client messages to the PostgreSQL containers

Benchmark

For some simple benchmark results, run some tests using the crunchy-proxy:

$> ./tests/pgbench/init-tests.sh
$> go run main.go start --config=./tests/pgbench/config.yaml
$> ./tests/pgbench/run-simple-load-test.sh

You can also run the psql command against the proxy as a test client.

Overhead

Overhead of the proxy was measured and shows the following for the typical case of handling a SQL statement:

Test	Proxy	No-Proxy	Overhead
Single SQL Statement	2.240026ms	2.085424ms	+0.154602ms

Proxy Administration

There is an administration port created by the proxy that you can interact with to gain status from the proxy.

Events

Events like a healthcheck status are published to any subscribers using a streaming REST API, you can access the admin events as follows:

curl -i http://localhost:10000/api/stream

As the proxy publishes events, your REST client (e.g. curl) will receive the events.

Current Configuration

You can get the current configuration of the proxy as follows:

curl http://localhost:10000/api/config

Statistics

You can get the current statistics of the proxy as follows:

curl http://localhost:10000/api/stats

Compiling the Source

If you are a developer and want to build the proxy from source code, follow these steps…

Installing Go

Install a golang compiler by following the instructions here and setting GOROOT and GOPATH environment variables accordingly.

Getting the source

$> go get -d -u github.com/crunchydata/crunchy-proxy

Building the binaries

Dependency management is handled by glide. Before building from source, it is necessary to install it by following the installation instructions here.

$> cd $GOPATH/src/github.com/crunchydata/crunchy-proxy
$> make

The resulting crunchy-proxy binary will be created in the build directory.

Building the Documentation

Requirements for building the documentation are as follows:

asciidoctor
asciidoctor-pdf
asciidoc-bootstrap-backend

$> make docs

Design

The example shows a message traveling down this path:

pg client→proxy→pg server→proxy→pg client

Wire Protocol

crunchy-proxy operates at the PostgreSQL wire protocol (network) layer to understand PostgreSQL client authentication requests and SQL statements passed by a client to a PostgreSQL backend.

The proxy does very little processing of the messages sent between a client and an actual backend, mostly examining the SQL statements for a proxy-specific annotation. The annotation is used to route the message to the backend.

Its important to note that the proxy does not implement all features of libpq or provide an application interface similar to a JDBC driver or other language driver.

Connection Pooling

crunchy proxy provisions a connection pool for each backend (master and replica(s)) that is defined in the proxy configuration file. The connection pool is a fixed size currently and established before the proxy begins to accept connections from clients.

The connections in the pool are determined by the pool settings found within the configuration parameters credentials and pool.

Currently crunchy proxy only supports basic PostgreSQL password authentication using username and password.

As client requests come into the proxy, the proxy will choose to which backend to route the SQL statement and then pick a free connection from the backend’s connection pool.

For each connection pool there is a golang channel defined to manage which connections are available for use when processing a SQL statement. After the SQL statement is processed, the connection is returned to the pool. You can think of the pool’s channel as a queue of available connections.

Client Authentication

Each client must authenticate against the master backend before the proxy will process future client requests. crunchy proxy does not include an authentication store itself, but instead relies on the master backend to perform authentication.

Once a client does authenticate, the proxy will terminate the client’s connection to the master and subsequently begin using the connections from the connection pools.

Annotations

SQL statements that start with a SQL comment of a particular format will be used to determine the routing of a SQL statement either to a master or a replica.

To simplify the proxy parsing, we require the annotation begin at the first byte of the SQL statement as follows:

/* read */ select from foo.....

If no annocation is found in a SQL statement, it is assumed the statement is a write.

In certain circumstances, it may be desriable to route all the SQL statements within a transaction to the same backend.

In order to support this case, it is possible include a start annotation in the first SQL statement and a finish annotation in the last SQL statement as follows:

/* start */ begin;
select .....;
/* finish */commit;

/* start,read */ begin;
select .....;
/* finish */commit;

Health Checking

The crunchy-proxy status health check is currently a simple implementation - essentially determining only whether the backend can process a SQL statement.

The health check is performed every few second on each backend by a separate goroutine that runs until the proxy exits.

The backend status is checked by the active connection processing in order to determine which backends are available to process a SQL statement.

As the status of a backend changes, the global configuration is updated.

Health status is captured and placed into an event channel. The event channel is used to publish events to any number of subscribers to the REST API.

Legal Notices

CRUNCHY DATA SOLUTIONS, INC. PROVIDES THIS GUIDE "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF NON INFRINGEMENT, MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE.

Crunchy, Crunchy Data Solutions, Inc. and the Crunchy Hippo Logo are trademarks of Crunchy Data Solutions, Inc.

Files

crunchy-proxy-user-guide.asciidoc

Latest commit

History

crunchy-proxy-user-guide.asciidoc

File metadata and controls

User Guide - Crunchy Proxy

Releases

Usage

Start

Stop

Health

Node

Stats

Version

Configuration

server

Example

nodes

credentials

pool

Example

healthcheck

Testing

Docker

Vagrant

Test Execution

Benchmark

Overhead

Proxy Administration

Events

Current Configuration

Statistics

Compiling the Source

Installing Go

Getting the source

Building the binaries

Building the Documentation

Design

Wire Protocol

Connection Pooling

Client Authentication

Annotations

Health Checking

Legal Notices