Posted inMySQL

Setting Up and Configuring ProxySQL: A Connection Pooler and Load Balancer Solution for MySQL Production

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

Why is ProxySQL Needed in a MySQL Production Environment?

Working with MySQL, especially in high-traffic systems, I frequently encounter performance and stability issues. One of the biggest bottlenecks is connection management.

Every time an application needs to interact with the database, it opens a new connection, consuming resources and time. When traffic spikes, the number of connections can exceed MySQL’s limits, leading to application errors and system crashes. I recall a time when the users table surpassed 10 million rows, the system started experiencing strange slow queries, and that’s when I realized it was time to find a more efficient connection management solution.

That’s when I turned to ProxySQL – an intelligent proxy that acts as a connection pooler, load balancer, and reverse proxy for MySQL. It not only helps reuse existing connections but also has the ability to route queries, distribute load among servers, and automatically handle server failures. This is extremely important to ensure system stability and scalability.

Quick Start: Install ProxySQL in 5 Minutes

To give you a quick overview of ProxySQL, I’ll guide you through a basic installation on Ubuntu/Debian. Let’s assume you already have a running MySQL server.

1. Install ProxySQL

First, add the ProxySQL repository and install it:


# Install necessary packages
sudo apt-get update
sudo apt-get install -y wget gnupg2

# Add ProxySQL GPG key
wget -O - https://repo.proxysql.com/ProxySQL/repo_pub_key | sudo apt-key add -

# Add ProxySQL repository (e.g., for Ubuntu 22.04 LTS)
echo "deb https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/$(lsb_release -sc)/ ./" | sudo tee /etc/apt/sources.list.d/proxysql.list

# Update and install ProxySQL
sudo apt-get update
sudo apt-get install -y proxysql

2. Start and Basic Configuration

After installation, ProxySQL will start automatically. You can connect to ProxySQL’s administrative interface via port 6032 (default) using any MySQL client:


mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt='ProxySQL> '

Inside the ProxySQL console, we will add the MySQL backend server and user for the application.

Add MySQL Backend Server

Suppose your MySQL server has the IP 192.168.1.10 and listens on port 3306. We will add it to hostgroup 10 (typically used for master/write):


INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (10, '192.168.1.10', 3306);
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

Add User for Application

ProxySQL needs the MySQL user information that the application will use to connect through it. This user must exist on the MySQL backend server:


INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('app_user', 'app_password', 10);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

Configure Listener and Apply Changes

By default, ProxySQL listens on port 6033 for client applications. After making changes, you need to apply them to runtime and save them to disk:


-- If you want to change the listener (e.g., port 3306 so clients don't need to change ports)
-- UPDATE global_variables SET variable_value='6033' WHERE variable_name='mysql-listener_port';
-- LOAD MYSQL VARIABLES TO RUNTIME;
-- SAVE MYSQL VARIABLES TO DISK;

-- After adding servers and users, always load and save
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;

3. Verification

Now, try connecting from your application (or using a MySQL client) to ProxySQL:


mysql -u app_user -papp_password -h 127.0.0.1 -P 6033 --prompt='AppClient> '

If the connection is successful and you can query the database, then ProxySQL is working!

Detailed Explanation: How Does ProxySQL Work?

ProxySQL is not merely a TCP proxy. It is an application layer (layer 7) proxy that understands the MySQL protocol, allowing it to perform more intelligent tasks.

Basic Architecture

  • Frontend (Client-facing): ProxySQL listens for connections from applications (clients) on a specific port (default 6033).
  • Backend (Server-facing): It maintains a pool of connections to actual MySQL servers (backends).
  • Runtime Configuration: All configurations are stored in an internal SQLite database and can be changed dynamically without requiring a restart.

Key Features

  • Connection Pooling: Reuses existing connections to the MySQL server, significantly reducing the overhead of creating and closing new connections. This is especially useful for applications with many short-lived connections.
  • Query Routing: Routes queries to appropriate MySQL backend servers based on query rules. For example: sending all SELECT queries to replicas and INSERT/UPDATE/DELETE queries to the master.
  • Read/Write Splitting: Automatically splits read and write queries to different hostgroups (e.g., master for writes, slave for reads).
  • Load Balancing: Distributes query load among servers within the same hostgroup according to specific algorithms (e.g., round-robin).
  • High Availability (HA) & Failover: Monitors the status of MySQL backend servers. When a server failure is detected, it automatically removes that server from the pool and routes queries to the remaining servers.
  • Query Rewriting: Can rewrite SQL queries before sending them to the backend.

Advanced Configuration: Optimizing for Production Environments

Once the basics are understood, let’s dive deeper into advanced configurations to truly unleash ProxySQL’s power in production.

1. Read/Write Splitting with Hostgroups

This is one of the features I use most often. I typically configure one hostgroup for the Master (writes) and one or more hostgroups for Slaves (reads).


-- Assume MySQL Master has IP 192.168.1.10 (hostgroup_id 10)
-- MySQL Slave has IP 192.168.1.11 (hostgroup_id 20)

-- Add Slave to hostgroup 20
INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections) VALUES (20, '192.168.1.11', 3306, 200);
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
```</pre>

<p>Next, I will define rules to separate read and write queries:</p>
<pre><code class="sql">
-- Rule 1: Route all SELECT queries to hostgroup 20 (Slave)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, '^SELECT', 20, 1);

-- Rule 2: Remaining queries (INSERT, UPDATE, DELETE...) will go to hostgroup 10 (Master)
-- Note: This rule would have destination_hostgroup = 10, but since the user's default_hostgroup is already 10,
-- there's no need to create a separate rule for INSERT/UPDATE/DELETE unless there are special requirements.
-- However, for clarity, the following rule can be added (with a lower rule_id for higher priority):
-- INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
-- VALUES (0, 1, '^(INSERT|UPDATE|DELETE)', 10, 1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Important Note: The smaller the rule_id, the higher the priority. Rules will be checked in ascending order of rule_id. When a query matches a rule and apply=1, the rule checking process will stop.

2. User and Default Hostgroup Management

In mysql_users, the default_hostgroup field is very important. It specifies the default hostgroup that the user will connect to if no other rules are applied.

3. Health Checks and Failover

ProxySQL continuously checks the status of backend servers. I configure global variables to adjust this behavior:


-- Configure health check timeout (ms)
UPDATE global_variables SET variable_value = '5000' WHERE variable_name = 'mysql-monitor_interval_ms';

-- Number of consecutive failures to consider a server offline
UPDATE global_variables SET variable_value = '3' WHERE variable_name = 'mysql-monitor_count_retries';

-- Retry wait time when server is offline (ms)
UPDATE global_variables SET variable_value = '10000' WHERE variable_name = 'mysql-monitor_offline_interval_ms';

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

When a server is marked as OFFLINE_SOFT or OFFLINE_HARD, ProxySQL will automatically stop sending queries to it. This allows my system to self-recover without manual intervention.

4. Query Rewriting

Sometimes, I need to modify queries on the fly. For example, adding SQL_NO_CACHE to read queries to ensure the latest data is read:


INSERT INTO mysql_query_rules (rule_id, active, match_pattern, replace_pattern, destination_hostgroup, apply)
VALUES (5, 1, '^(SELECT)(.*)', 'SELECT SQL_NO_CACHE\2', 20, 1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

This is a powerful tool, but it needs to be used carefully, as incorrect rewriting can lead to unintended consequences.

Practical Tips and Hard-Earned Experience

After many “struggles” with ProxySQL, I’ve gathered some experiences I want to share:

1. Understand Your Database Structure and Application Query Patterns

To optimize mysql_query_rules, you need to clearly understand which applications are calling which queries, and whether those queries need to read/write on the master or slave. Don’t hesitate to use pt-query-digest or the MySQL Slow Query Log (which I’ve already provided a guide for) to analyze common queries. If you don’t understand them thoroughly, routing can be counterproductive.

2. Test, Test, and Test Again

Whenever I change ProxySQL configurations, especially query_rules, I always have a thorough set of tests. From small unit tests in a dev environment to stress tests in a staging environment. Never push to production without thorough testing, especially with a critical component like ProxySQL.

3. Monitor ProxySQL Metrics

ProxySQL provides many useful metrics through tables like stats_mysql_connection_pool, stats_mysql_commands, and stats_mysql_query_digest. I often integrate these metrics into Prometheus and Grafana to monitor connection counts, connection pool hit ratios, the number of routed queries, etc. Close monitoring helps me detect issues early and adjust configurations promptly.


-- Example: view connections in the pool
SELECT hostgroup_id, srv_host, srv_port, status, ConnUsed, ConnFree, ConnOK, ConnERR FROM stats_mysql_connection_pool;

4. Note on Session State

When using ProxySQL, especially with read/write splitting, be careful with queries or configurations based on session state (e.g., SET @variable = 'value', LAST_INSERT_ID()). ProxySQL might route these queries to different servers, leading to a loss of session state. In such cases, you can use session_sticky=1 in mysql_query_rules to ensure a specific session is always routed to the same backend server, or redesign the application to be less dependent on session state.

5. Optimize Global Variables

There are many global variables in ProxySQL that can be adjusted to optimize performance, such as:

  • mysql-threads: The number of threads ProxySQL uses to handle client connections.
  • mysql-max_connections: The total maximum number of connections ProxySQL can handle.
  • mysql-connect_timeout_server: Timeout when ProxySQL connects to the backend server.

These variables need to be adjusted based on server resources and actual access load. Don’t let default values limit your system.

Conclusion

Implementing ProxySQL can be a valuable upgrade for any system using MySQL, especially when you need to handle high traffic or want to enhance availability. I’ve seen ProxySQL significantly reduce the load on MySQL servers, improve application response times, and help me sleep much better without worrying about database connection overload.

This tool is powerful, but it requires time to understand and configure correctly. I hope my shared practical experiences will help you get a smooth start with ProxySQL.

Share:
Tags: