Using Envoy Proxy's PostgreSQL & TCP Filters to Collect Yugabyte SQL Statistics

Using Envoy Proxy's PostgreSQL & TCP Filters to Collect Yugabyte SQL Statistics

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.

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.

databases distributed sql docker how to postgresql envoy layer 7

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

Distributed SQL: An Evolution of the Database

The next step in the evolution of database architecture is distributed SQL. Take a look at some of the characteristics here.As organizations transition to the cloud, they eventually find that the legacy relational databases that are behind some of their most critical applications simply do not take advantage of the promise of the cloud and are difficult to scale.

Distributed SQL Tips and Tricks for PostgreSQL and Oracle DBAs – Aug 2020

In this article, take a look at distributed SQL tips for PostgreSQL and Oracle DBAs. Welcome to this week’s tips and tricks blog where we explore both beginner and advanced YugabyteDB topics for PostgreSQL and Oracle DBAs. First things first, for those of you who might be new to either distributed SQL or YugabyteDB.

Row Counts of Tables in a SQL Schema & Database - PostgreSQL and YugabyteDB

See how to get total row counts of data in tables across various dimensions (per-table, per-schema, and in a given database)–a useful SQL technique!

Distributed SQL Change Management With Liquibase and YugabyteDB on GKE

In this article, look at database SQL change management with Liquibase and Yugabyte on GKE. Liquibase is an open source and extensible change management project that supports a variety of databases including Snowflake, MySQL, and PostgreSQL via JDBC.