ProxySQL Configuration and Management Cheat Sheet

This cheat sheet provides tips for setting up and managing ProxySQL for database connections. ProxySQL is a high-performance MySQL proxy that provides connection pooling, load balancing, query routing, and caching functionalities.

Connection and Ports

• Admin Interface: 6032
• MySQL Default Port: 3306
• MySQL Interface via ProxySQL: 6033

Connecting to ProxySQL Admin Interface

mysql -uadmin -padmin -h127.0.0.1 -P6032

General Configuration Settings

Set Connection Reuse

To enable connection reuse, use the following commands:

UPDATE global_variables SET variable_value='1' WHERE variable_name='mysql-connection_reuse';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

Set Max Connections

Configure the maximum number of connections ProxySQL will open to the backend database:

UPDATE global_variables SET variable_value='100' WHERE variable_name='mysql-max_connections';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

Query Cache Configuration

Set the TTL for query caching:

UPDATE mysql_query_rules set cache_ttl=5000 WHERE active=1 AND destination_hostgroup=1;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Adding a Backend Server and Users

Add your backend MySQL server to ProxySQL:

INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'mysqldb01.example.com',3306);
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

Add users for accessing the MySQL backend:

INSERT INTO mysql_users(username,password,default_hostgroup, default_schema) VALUES ('mysqldb_user','password',1, 'mysqldb_schema');
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

Monitor User Setup

ProxySQL requires a monitor user to check the health of the MySQL backend. Create this user on your MySQL server:

CREATE USER 'monitor'@'%' IDENTIFIED BY 'password';
GRANT USAGE, REPLICATION CLIENT ON *.* TO 'monitor'@'%';
FLUSH PRIVILEGES;

Configure the monitor user in ProxySQL:

UPDATE global_variables SET variable_value='proxysql_monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='password' WHERE variable_name='mysql-monitor_password';
UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

Useful Commands and Queries

Query Digest Statistics

Retrieve detailed statistics about query performance:

SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

Connection Pool Stats

Show used connections:

SELECT hostgroup, srv_host, ConnUsed, ConnFree, ConnOK, ConnERR, MaxConnUsed, Queries, Bytes_data_sent, Bytes_data_recv FROM stats_mysql_connection_pool;

Show how many times queries were used:

SELECT * FROM stats_mysql_query_digest ORDER BY count_star DESC;
SELECT * FROM stats_mysql_global WHERE Variable_Name LIKE 'Query_Cache%';

Check Total Connections to a Database in MySQL

SELECT 
    IFNULL(DB, 'No Database') AS DatabaseName, 
    COUNT(*) AS Connections
FROM 
    information_schema.PROCESSLIST
GROUP BY 
    DB
ORDER BY 
    Connections DESC;

Notes

• Ensure that you tailor the INSERT INTO mysql_users statements with the correct usernames, passwords, and schema names per your environment.
• Always save the configurations to disk after loading them to runtime to make them persistent across restarts.

Wrapping Up the ProxySQL Cheat Sheet

Following these instructions will set up ProxySQL with optimal settings for connection reuse, monitoring, and efficient query handling. Adjust the parameters according to your specific use case and workload requirements.

References

ProxySQL Official Documentation: This is the most authoritative source for ProxySQL information. It covers everything from installation to advanced configuration.

• URL: https://proxysql.com/documentation/

You May Also Be Interested In

• runtime environment
• MySQL
• Determine the cause of too many connections in MySQL/MariaDB
• Hard drive
• MariaDB

Support the Cause

Support Anto Online and buy us a coffee. Anything is possible with coffee and code.

Buy me a coffee