{{announcement.body}}
{{announcement.title}}

Using Envoy Proxy’s PostgreSQL and TCP Filters to Collect Yugabyte SQL Statistics

DZone 's Guide to

Using Envoy Proxy’s PostgreSQL and TCP Filters to Collect Yugabyte SQL Statistics

In this article, see how to use Envoy Proxy's PostgreSQL and TCP filters to collect Yugabyte SQL statistics.

· Database Zone ·
Free Resource

Layer 7 proxies like NGINX and HAProxy have been popular since the mid-2000s. The term “proxy” refers to their role as an intermediary for the traffic between an application client and an application server. The “layer 7” classification comes from the fact that these proxies take routing decisions based on URLs, IPs, TCP/UDP ports, cookies, or any information present in messages sent over a layer 7 (aka application layer) networking protocol like HTTP and gRPC. Modern applications use these proxies for a variety of needs including load balancing, security, and web acceleration.

What Is Envoy Proxy?

Envoy is a layer 7 proxy and communication bus designed for large modern service oriented architectures. Its home page has the following definition:

Originally built at Lyft, Envoy is a high performance C++ distributed proxy designed for single services and applications, as well as a communication bus and “universal data plane” designed for large microservice “service mesh” architectures. Built on the learnings of solutions such as NGINX, HAProxy, hardware load balancers, and cloud load balancers, Envoy runs alongside every application and abstracts the network by providing common features in a platform-agnostic manner. When all service traffic in an infrastructure flows via an Envoy mesh, it becomes easy to visualize problem areas via consistent observability, tune overall performance, and add substrate features in a single place.

As the recently published Dropbox’s migration from NGINX to Envoy highlights, Envoy is rapidly becoming the default proxy for cloud native applications that need higher performance, observability, extensibility, security, building and testing, and last but not least, deep features (such as HTTP/2, gRPC, and egress proxying). It was the third CNCF project to reach the graduated status, following Kubernetes and Prometheus, and has gained widespread adoption in a relatively short period of time.

Why Use Envoy’s PostgreSQL Filter?

Envoy supports configuration of multiple traffic listeners where each listener is composed of one or more filter chains. An individual filter chain is selected to process the incoming data based on the filter’s match criteria (which includes connection parameters such as destination port/IP, transport protocol name, source port/IP, and more). When a new connection is received on a listener, the matching filter chain is selected and instantiated. The filters then begin processing subsequent events. This generic listener architecture is used to perform the vast majority of different proxy tasks that Envoy is used for including rate limiting, TLS client authentication, HTTP connection management, raw TCP proxy, and more. One such task relevant to database deployments is the ability to instrument the wire protocol of popular databases such as MySQL, MongoDB, Kafka, and Amazon DynamoDB. PostgreSQL was missing from this list but the latest v1.15 release from July 2020 solved that problem by adding a PostgreSQL proxy filter. This filter is based on PostgreSQL frontend/backend protocol version 3.0, which was introduced in PostgreSQL 7.4.

The main goal of the PostgreSQL filter is to capture runtime statistics while remaining completely transparent to the database server. There is no additional monitoring software to deploy or manage in order to collect these vital statistics! As listed in the official docs, the filter currently offers the following features:

  • Decode non SSL traffic, ignore SSL traffic
  • Decode session information
  • Capture transaction information, including commits and rollbacks
  • Expose counters for different types of statements (INSERTs, SELECTs, DELETEs, UPDATEs, etc.)
  • Count frontend, backend, and unknown messages
  • Identify errors and backend responses

YugabyteDB is fully compatible with the PostgreSQL wire protocol and SQL syntax given that its SQL query layer is based on a fork of PostgreSQL 11.2’s query layer. As a result, YugabyteDB is able to leverage the PostgreSQL filter from Envoy without any modifications whatsoever. The rest of this post outlines the instructions to run the most basic YugabyteDB with Envoy setup (including the PostgreSQL & TCP filters) using Docker Compose. Official Envoy sandboxes use the same approach to test out different features and highlight sample configurations.

YugabyteDB With Envoy in action

Install Docker

Ensure that you have docker and docker-compose installed on your local machine. Docker Desktop can be the simplest way to achieve this goal.

Create the “YugabyteDB with Envoy” stack using docker-compose

Create a working directory

Java
 




x


 
1
mkdir yugabyte-envoy


Create the envoy.yaml

Copy the following contents into a file named envoy.yaml.

Java
 




xxxxxxxxxx
1
39


 
1
static_resources:
2
  listeners:
3
  - name: yb_listener
4
    address:
5
      socket_address:
6
        address: 0.0.0.0
7
        port_value: 1999
8
    filter_chains:
9
    - filters:
10
      - name: envoy.filters.network.postgres_proxy
11
        typed_config:
12
          "@type": type.googleapis.com/envoy.extensions.filters.network.postgres_proxy.v3alpha.PostgresProxy
13
          stat_prefix: ysql
14
      - name: envoy.tcp_proxy
15
        typed_config:
16
          "@type": type.googleapis.com/envoy.extensions.filters.network.tcp_proxy.v3.TcpProxy
17
          stat_prefix: tcp_ysql
18
          cluster: yb_cluster
19
 
          
20
  clusters:
21
  - name: yb_cluster
22
    connect_timeout: 1s
23
    type: strict_dns
24
    load_assignment:
25
      cluster_name: yb_cluster
26
      endpoints:
27
      - lb_endpoints:
28
        - endpoint:
29
            address:
30
              socket_address:
31
                address: ysql
32
                port_value: 5433
33
 
          
34
admin:
35
  access_log_path: "/dev/null"
36
  address:
37
    socket_address:
38
      address: 0.0.0.0
39
      port_value: 8001


As we can see above, we have configured an Envoy listener on port 1999 that has a filter chain with two filters, namely PostgreSQL and TCP.

Create the Envoy dockerfile

Copy the following contents into a file named Dockerfile-proxy. When built and instantiated, we will have an envoyproxy container that will use the envoy.yaml configuration we created in the previous step.

Java
 




xxxxxxxxxx
1


 
1
FROM envoyproxy/envoy-dev:latest
2
 
          
3
COPY ./envoy.yaml /etc/envoy.yaml
4
RUN chmod go+r /etc/envoy.yaml
5
CMD /usr/local/bin/envoy -c /etc/envoy.yaml -l debug


Create the yugabytedb dockerfile

Copy the following contents into a file named Dockerfile-yugabyte. When built and instantiated, we will have a single YugabyteDB container with the PostgreSQL-compatible YSQL API available on port 5433.

Java
 




xxxxxxxxxx
1


 
1
FROM yugabytedb/yugabyte:latest
2
 
          
3
CMD ["/home/yugabyte/bin/yugabyted","start","--daemon=false"]


Create the docker-compose.yaml

Copy the following contents into a file named docker-compose.yaml.

Java
 




xxxxxxxxxx
1
34


 
1
version: "3.7"
2
services:
3
 
          
4
  proxy:
5
    build:
6
      context: .
7
      dockerfile: Dockerfile-proxy
8
    networks:
9
      envoymesh:
10
        aliases:
11
          - envoy
12
    expose:
13
      - "1999"
14
      - "8001"
15
    ports:
16
      - "1999:1999"
17
      - "8001:8001"
18
 
          
19
  yugabyte:
20
    build:
21
      context: .
22
      dockerfile: Dockerfile-yugabyte
23
    networks:
24
      envoymesh:
25
        aliases:
26
          - ysql
27
    expose:
28
      - "5433"
29
    ports:
30
      - "5433:5433"
31
 
          
32
networks:
33
  envoymesh:
34
    name: envoymesh


Start the docker-compose stack

Java
 




xxxxxxxxxx
1


 
1
docker-compose pull
2
docker-compose up --build -d
3
docker-compose ps


Output from the ps command is shown below.

Java
 




xxxxxxxxxx
1


 
1
Name                      Command                     State                Ports                                              
2
---------------------------------------------------------------------------------
3
yugabyte_proxy_1      /docker-entrypoint.sh /bin ...   Up      ... 0.0.0.0:1999->1999/tcp, 0.0.0.0:8001->8001/tcp                                                                    
4
yugabyte_yugabyte_1   /home/yugabyte/bin/yugabyt ...   Up      ... 0.0.0.0:5433->5433/tcp, ...


As we can see, two containers have been spun up on a common envoymesh network.

  1. yugabyte_yugabyte_1 is the YugabyteDB container that is ready to interact with PostgreSQL clients on port 5433.

  2. yugabyte_proxy_1 is the Envoy proxy container that is running the PostgreSQL proxy on the 1999 port. Requests to this port get automatically redirected to the port 5433 on the YugabyteDB container.

Connect using ysqlsh via the envoy listener

We are now ready to connect to the YugabyteDB cluster using ysqlsh. However, instead of directly connecting to the 5433 port of the YugabyteDB container, we will connect to the Envoy proxy at the 1999 port.

Java
 




xxxxxxxxxx
1


 
1
docker run --rm -it --network envoymesh yugabytedb/yugabyte /home/yugabyte/bin/ysqlsh "sslmode=disable" -h envoy -p 1999


As highlighted in the Envoy docs, the current PostgreSQL filter decodes only non-SSL (aka unencrypted) traffic and ignores any SSL/encrypted traffic. The sslmode=disable option shown above is mandatory for Envoy to treat the PostgreSQL traffic as unencrypted even though the cluster has been set up without any encryption. Since this behavior is reproducible with both PostgreSQL 12 and 11.2 (Yugabyte SQL is based on a fork of this version), this is most likely a bug in the filter implementation.

Run basic YSQL Commands

Let’s create a table.

Java
 




xxxxxxxxxx
1


 
1
CREATE TABLE links (
2
    id SERIAL PRIMARY KEY,
3
    url VARCHAR(255) NOT NULL,
4
    name VARCHAR(255) NOT NULL,
5
    description VARCHAR (255),
6
      last_update DATE
7
);


Now let us insert four rows into the table we created.

Java
 




xxxxxxxxxx
1
12


 
1
INSERT INTO links (url, name)
2
VALUES('https://www.postgresqltutorial.com','PostgreSQL Tutorial');
3
 
          
4
INSERT INTO links (url, name)
5
VALUES('https://www.oreilly.com','O''Reilly Media');
6
 
          
7
INSERT INTO links (url, name)
8
VALUES('https://docs.yugabyte.com','YugabyteDB Docs');
9
 
          
10
INSERT INTO links (url, name)
11
VALUES('https://blog.yugabyte.com','YugabyteDB Blog') 
12
RETURNING id;


We can now run a SELECT statement to get all the rows we inserted.

SELECT * FROM links;
We can also run a SELECT statement to get the count of rows we inserted.

SELECT count(*) FROM links;

Review YSQL Statistics Collected by Envoy’s Filters

Each of the two filters configured provide us with statistics relevant to the data they observe. All these statistics are available on the Envoy stats page http://localhost:8001/stats.

Using the PostgreSQL Filter

Since we gave the stats prefix as ysql, we see all the statistics with the overall prefix as postgres.ysql. The ones that we can easily verify based on the queries we executed in the ysqlsh session we created are highlighted with "**".

Java
 




xxxxxxxxxx
1
30


 
1
postgres.ysql.errors: 0
2
postgres.ysql.errors_error: 0
3
postgres.ysql.errors_fatal: 0
4
postgres.ysql.errors_panic: 0
5
postgres.ysql.errors_unknown: 0
6
postgres.ysql.messages: 45
7
postgres.ysql.messages_backend: 37
8
postgres.ysql.messages_frontend: 8
9
postgres.ysql.messages_unknown: 0
10
postgres.ysql.notices: 0
11
postgres.ysql.notices_debug: 0
12
postgres.ysql.notices_info: 0
13
postgres.ysql.notices_log: 0
14
postgres.ysql.notices_notice: 0
15
postgres.ysql.notices_unknown: 0
16
postgres.ysql.notices_warning: 0
17
**postgres.ysql.sessions: 1**
18
postgres.ysql.sessions_encrypted: 0
19
**postgres.ysql.sessions_unencrypted: 1**
20
**postgres.ysql.statements: 7**
21
postgres.ysql.statements_delete: 0
22
**postgres.ysql.statements_insert: 4**
23
**postgres.ysql.statements_other: 1**
24
postgres.ysql.statements_parse_error: 2
25
postgres.ysql.statements_parsed: 5
26
**postgres.ysql.statements_select: 2**
27
postgres.ysql.statements_update: 0
28
**postgres.ysql.transactions: 7**
29
**postgres.ysql.transactions_commit: 7**
30
postgres.ysql.transactions_rollback: 0


Using the TCP filter

The TCP statistics relevant to YSQL are available with the tcp.tcp_ysql prefix. As we can see, the statistics are at the network level including bytes transmitted and bytes received.

Java
 




xxxxxxxxxx
1
11


 
1
tcp.tcp_ysql.downstream_cx_no_route: 0
2
tcp.tcp_ysql.downstream_cx_rx_bytes_buffered: 33
3
tcp.tcp_ysql.downstream_cx_rx_bytes_total: 693
4
tcp.tcp_ysql.downstream_cx_total: 1
5
tcp.tcp_ysql.downstream_cx_tx_bytes_buffered: 0
6
tcp.tcp_ysql.downstream_cx_tx_bytes_total: 991
7
tcp.tcp_ysql.downstream_flow_control_paused_reading_total: 0
8
tcp.tcp_ysql.downstream_flow_control_resumed_reading_total: 0
9
tcp.tcp_ysql.idle_timeout: 0
10
tcp.tcp_ysql.upstream_flush_active: 0
11
tcp.tcp_ysql.upstream_flush_total: 0


Remove All the Containers in the Stack

We can remove the containers we have previously spun up using the command below.

Java
 




xxxxxxxxxx
1


 
1
docker rm -f $(docker ps -aq)


Summary

The recently released PostgreSQL filter from Envoy Proxy makes it extremely easy for developers and operations engineers to collect SQL statistics from YSQL, YugabyteDB’s PostgreSQL-compatible fully-relational distributed SQL API. The filter runs inside the Envoy Proxy sidecar container and works by simply sniffing the network traffic in a manner that is completely transparent to the database server. As a result, developers and operations engineers can leverage the integration without deploying and managing any additional software.

We welcome all users to give the integration a try today and provide us feedback via GitHub and Slack.

Topics:
distributed sql, envoy, istio, kubernetes, postgres, postgresql, yugabyte

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}