Showing entries 11 to 20 of 190
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: proxysql (reset)
ProxySQL Behavior in the Percona Kubernetes Operator for Percona XtraDB Cluster

The Percona Kubernetes Operator for Percona XtraDB Cluster(PXC) comes with ProxySQL as part of the deal. And to be honest, the behavior of ProxySQL is pretty much the same as in a regular non-k8s deployment of it. So why bother to write a blog about it? Because what happens around ProxySQL in the context of the operator is actually interesting.

ProxySQL is deployed on its own POD (that can be scaled as well as the PXC Pods can). Each ProxySQL Pod has its own ProxySQL Container and a sidecar container. If you are curious, you can find out which node holds the pod by running

kubectl describe pod cluster1-proxysql-0 | grep Node:
Node: ip-192-168-37-111.ec2.internal/192.168.37.111

Login into and ask for the running containers. You will see something like this:

[root@ip-192-168-37-111 ~]# docker ps | grep -i proxysql …
[Read more]
Achieving Consistent Read and High Availability with Percona XtraDB Cluster 8.0

In real life, there are frequent cases where getting a running application to work correctly is strongly dependent on consistent write/read operations. This is no issue when using a single data node as a provider, but it becomes more concerning and challenging when adding additional nodes for high availability and/or read scaling. 

In the MySQL dimension, I have already described it here in my blog Dirty Reads in High Availability Solution.

We go from the most loosely-coupled database clusters with primary-replica async replication, to the fully tightly-coupled database clusters with NDB Cluster (MySQL/Oracle).

Adding components like ProxySQL to the architecture can, from one side, help in improving high availability, and from the other, it can amplify and randomize the negative effect of …

[Read more]
Join ProxySQL Tech Talks with Percona on June 4th, 2020!

Long months of the pandemic lockdown have brought to life many great online events enabling the MySQL community to get together and stay informed about the very recent developments and innovations available to MySQL users. It isn’t over yet! Next Thursday, June 4th, Percona & ProxySQL are co-hosting the ProxySQL Tech Talks with Percona virtual meetup covering ProxySQL, MySQL and Percona XtraDB Cluster.

The attendees are invited to participate in the two-hour deep-dive event with plenty of time for questions and answers (we will have two 40-minute sessions + 20 minutes allocated for Q&A). Get prepared, come with your burning questions and true war stories – we’ll have our speakers answer and comment on them! And here come the speakers:

  • René Cannaò, ProxySQL author and CEO of ProxySQL …
[Read more]
Releasing ProxySQL 2.0.12

ProxySQL is proud to announce the fast track release of the latest stable version of ProxySQL 2.0.12 on 18th of May 2020

ProxySQL is a high performance, high availability, protocol aware proxy for MySQL, with a GPL license! It can be downloaded here or alternatively from the ProxySQL Repository, and freely usable and accessible according to the GNU GPL v3.0 license.

Release Overview Highlights Enhancements

  • Added tracking capability for variable group_concat_max_len #2709
  • Do not compile if GIT_VERSION is not set #2768
  • Several new automated …
[Read more]
Group Replication and Percona XtraDB Cluster: Overview of Common Operations

In this blog post I would like to give an overview of the most common failover scenarios and operations when using MySQL Group Replication 8.0.19 (aka GR) and Percona XtraDB Cluster 8 (PXC) (which is based on Galera), and explain how each technology handles each situation. I have created a three-node cluster with Group Replication using a single Primary and a three-node PXC, both with default settings. I am also going to use ProxySQL to interface with both clusters.

In both clusters, the name of the nodes are mysql1, mysql2,  and mysql3. In Group Replication the Primary node is where the writes go if we are using single primary configuration. In PXC, I will use the same term as well and will call the node Primary where I am sending the writes. Just to note, in PXC there is no concept of primary node, …

[Read more]
Using MySQL Workbench to Connect Through ProxySQL 2

So, I admit the title for this post is a bit ambiguous. Not only is it the second post I’ve written in a short period of time, but it’s also focused on ProxySQL 2.0. As promised in the previous post, I’ve upgraded my testing environment to ProxySQL 2.0 and was interested to see if there are any differences in behaviour from the previous version. As it turns out, there are.

In the comments section of the previous post, you’ll find a comment by ProxySQL Founder and CEO, René Cannaò:

Indeed ProxySQL doesn’t support caching_sha2_password authentication plugin, but since ProxySQL 2.0.3 (March 2019) a client connection using caching_sha2_password will be automatically switched to mysql_native_password.

With regards to “OPT_CHARSET_NAME=utf8”, the problem is that your backend it is not MySQL 8.0 …

[Read more]
Benchmarking: More Stable Results with CPU Affinity Setting

When I run a benchmark and want to measure the CPU efficiency of something, I find it’s often a good choice to run a benchmark program, as well as the database, on the same server. This is in order to eliminate network impact and to look at single-thread performance, to eliminate contention.

Usually, this approach gives rather stable results; for example, benchmarking MySQL with Sysbench OLTP Read-Only workload I get a variance of less than one percent between 1-minute runs.

In this case, though, I was seeing some 20 percent difference between the runs, which looked pretty random and would not go away even with longer 10-minute runs.

The benchmark I did was benchmarking MySQL through ProxySQL (all running on the same machine):

Sysbench -> ProxySQL -> MySQL 

As I thought more about possible reasons, I thought CPU scheduling might be a problem. As requests pass …

[Read more]
ProxySQL 2.0.9 Introduces Firewall Whitelist Capabilities

In this blog, we will test a new security feature added in ProxySQL 2.0.9. Since a time ago, we have had the ability to block queries using mysql_query_rules table matching a group of queries using reg exp like a blacklist. Check out a previous blog for how to config “ProxySQL Firewalling” using the mysql_query_rules table.

You can improve a whitelist using the mysql_query_rules table, but it is difficult if you have hundreds of queries.

ProxySQL 2.0.9 introduces two new tables for the firewall whitelist algorithm:

Admin> SELECT name AS tables FROM main.sqlite_master WHERE type='table' AND name IN ('mysql_firewall_whitelist_rules','mysql_firewall_whitelist_users') ORDER BY name;
+--------------------------------+
| tables                         | …
[Read more]
Configuring a Read-Only Web Interface for Orchestrator

In the MySQL ecosystem, orchestrator is the most popular and well-respected high availability and topology management tool, integrating well with other solutions such as ProxySQL. It facilitates automatic (or manual) discovery, refactoring and recovery of a replicated MySQL environment, and comes complete with both command-line (CLI) and web interfaces for both humans and machines to interact with.

As we all know, humans are prone to errors and as such accidents can happen, particularly when humans and computers interact with each other! Recently, one of these situations related to the web interface of orchestrator during topology refactoring with its drag-and-drop capabilities, where a drop occurred unintentionally and thus had an impact on replication.

When …

[Read more]
How to Use ProxySQL 2 on Percona XtraDB Cluster for Failover

If you are thinking of using ProxySQL in our Percona XtraDB Cluster environment, I’ll explain how to use ProxySQL 2 for failover tasks.

How to Test

ProxySQL uses the “weight” column to define who is the WRITER node. For this example, I’ll use the following list of hostnames and IPs for references:

+-----------+----------------+
| node_name | ip             |
+-----------+----------------+
| pxc1      | 192.168.88.134 |
| pxc2      | 192.168.88.125 |
| pxc3      | 192.168.88.132 |
+-----------+----------------+

My current WRITER node is the “pxc1” node, but how can I see who is the current WRITER? It’s easy, just run the following query:

proxysql> select hostgroup_id, comment, hostname, status, weight from runtime_mysql_servers;

This is the output: …

[Read more]
Showing entries 11 to 20 of 190
« 10 Newer Entries | 10 Older Entries »