ProxySQL is a very useful tool for gaining high availability, load balancing, query routing, query caching, query rewriting, multiplexing, and data masking. It is a proven tool and is used largely in production. There can be an instance of ProxySQL using a lot of memory on the server. This post is related to one of the causes that can use memory on the ProxySQL server and how we handle it.

Explanation

How do we know where the memory is getting used?

Proxysql has a stats database which we can see with an admin login. This database contains metrics gathered by ProxySQL concerning its internal functioning.

This post mostly talks about stats in MySQL query digest in ProxySQL.

Memory used by query digest can grow unlimited, and you can query the memory usage with the below query with an admin login. The memory usage is shown in bytes. 

MySQL [(none)]> select * from …

Every day we use dozens if not hundreds of applications connecting to some kind of data repository. This simple step is normally executed over the network and, given so, it is subject to possible sniffing with all the possible related consequences.

Given that, it is normally better to protect your connection using data encryption like SSL, or at the minimum, make the information you pass to connect less easy to be intercepted.

At the same time, it is a best practice to not store connection credentials in clear text, not even inside a table in your database. Doing that is the equivalent of writing your password on a sticky note on your desk. Not a good idea.

Instead, the main options are either transforming the passwords to be less identifiable via hashing or storing the information in an external centralized vault.

In MySQL, the passwords are transformed to not be clear text, and several different plugins …

This ProxySQL post is sourced from an error I faced recently ProxySQL has become one of the popular choice as the “proxy” for MySQL databases. This post is tipping you off for debugging connectivity issue and fixtures around it for a specific use-case. ProxySQL Connectivity Ofcourse while to connect ProxySQL we have two choices for […]

The post Can not connect to ProxySQL: reasons and fixtures first appeared on Change Is Inevitable.

Temporary Tables handling in MySQL has a somewhat checkered past. Some time ago, my colleague Przemek wrote a post about differences in the implementations between versions of MySQL. If you are looking for the differences in implementation, that’s a perfect place to check.

In this post, I’d like to focus on Session Temporary Tablespaces, InnoDB on-disk internal temporary tables that come to play when a query requires storing more data than tmp_table_size or TempTable engine allocated more than …

Aurora cluster promises a high availability solution and seamless failover procedure. However, how much is actually the downtime when a failover happens? And how proxySQL can help in minimizing the downtime ? A little sneak peek on the results ProxySQL achieves up to 25x less downtime and the impressive up to ~9800x less errors during unplanned failovers. How proxySQL achieves this: 

1. Less downtime
2. “Queueing” feature when an instance in a hostgroup becomes unavailable.

So what is ProxySQL? ProxySQL is a middle layer between the database and the application. ProxySQL protects databases from high traffic spikes, prevents databases from having high number of connections due to the multiplexing feature and minimizes the impact during planned/unexpected failovers or crashes of DBs. 

This blog will continue with measuring the impact of an unexpected …

ProxySQL 2.3.0 was recently released and when I was reading the release notes, I was really impressed with the Group Replication enhancements and features. I thought of experimenting with those things and was interested to write a blog about them. Here, I have focused on the following two topics:

• When the replication lag threshold is reached, ProxySQL will move the server to SHUNNED state, instead of moving them to OFFLINE hostgroup. When shunning a server, it will be performed gracefully and not immediately drop all backend connections.
• The servers can be taken to maintenance through ProxySQL using “OFFLINE_SOFT”.

Test Environment

To test this, I have configured a three-node GR cluster (gr1,gr2,gr3) in my local environment. I have configured a single primary cluster (1 writer, 2 readers).

mysql> select member_host,member_state,member_role,member_version from …

There are plenty of ways to run ProxySQL in Kubernetes (K8S). For example, we can deploy sidecar containers on the application pods, or run a dedicated ProxySQL service with its own pods.

We are going to discuss the latter approach, which is more likely to be used when dealing with a large number of application pods. Remember each ProxySQL instance runs a number of checks against the database backends. These checks monitor things like server-status and replication lag. Having too many proxies can cause significant overhead.

Creating a Cluster

For the purpose of this example, I am going to deploy a test cluster in GKE. We need to follow these steps:

1. Create a cluster

gcloud container clusters create ivan-cluster --preemptible --project my-project --zone us-central1-c --machine-type n2-standard-4 --num-nodes=3

2. Configure command-line access

gcloud …

Starting with the release of proxysql-admin 2.0.15,  the proxysql-admin 2.x series can now encrypt the credentials needed to access proxysql and cluster nodes. This only applies to the proxysql-admin configuration, this does not change the ProxySQL config, so those credentials are still unencrypted.

The credentials file is the unencrypted file containing the usernames, passwords, hostnames, and ports needed to connect to ProxySQL and PXC (Percona XtraDB Cluster).

The proxysql-login-file tool is used to encrypt the credentials file. This encrypted file is known as a login-file. This login-file can then be used by the proxysql-admin and proxysql-status scripts.

Note: This feature requires OpenSSL v1.1.1 and above (with the exception of Ubuntu 16.04). Please see the …

We are proud to announce the latest release of ProxySQL version 2.2.0

ProxySQL is a high performance, high availability, protocol aware proxy for MySQL, with a GPL license! It can be downloaded from the ProxySQL Repository (instructions here) or for a Docker image check out the Official ProxySQL Docker Repository. ProxySQL is freely usable and accessible according to the GNU GPL v3.0 license.

Release Overview Highlights

ProxySQL v2.2.0 is a minor release comprising of backward compatible changes, enhancements and bug fixes. Going forward ProxySQL will be using the common versioning standard “Major.Minor.Patch” and so this is essentially the first minor release of the 2.1 branch and inclues many fixes and features that were added to the 2.0 branches …

We are proud to announce the latest release of ProxySQL version 2.1.1 on the 21st of April 2021

ProxySQL is a high performance, high availability, protocol aware proxy for MySQL, with a GPL license! It can be downloaded from the ProxySQL Repository (instructions here) or for a Docker image check out the Official ProxySQL Docker Repository. ProxySQL is freely usable and accessible according to the GNU GPL v3.0 license.

Release Overview Highlights

ProxySQL v2.1.1 is a patch release comprising of minor backward compatible changes and bug fixes. This release is the first patch release of the 2.1 branch and inclues many fixes and features that were introduced in the 2.0.x branches after 2.1 was released.

Be sure to try out the ProxySQL …