This cheat sheet supplies suggestions for establishing and managing ProxySQL for database connections. ProxySQL is a high-performance MySQL proxy that gives connection pooling, load balancing, question routing, and caching functionalities.
Connection and Ports
- Admin Interface: 6032
- MySQL Default Port: 3306
- MySQL Interface through ProxySQL: 6033
Connecting to ProxySQL Admin Interface
mysql -uadmin -padmin -h127.0.0.1 -P6032Normal Configuration Settings
Set Connection Reuse
To allow connection reuse, use the next instructions:
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 utmost variety 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;Question Cache Configuration
Set the TTL for question caching:
UPDATE mysql_query_rules set cache_ttl=5000 WHERE lively=1 AND destination_hostgroup=1;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;Including a Backend Server and Customers
Add your backend MySQL server to ProxySQL:
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'mysqldb01.instance.com',3306);
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;Add customers 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 Consumer Setup
ProxySQL requires a monitor person to examine the well being of the MySQL backend. Create this person in your MySQL server:
CREATE USER 'monitor'@'%' IDENTIFIED BY 'password';
GRANT USAGE, REPLICATION CLIENT ON *.* TO 'monitor'@'%';
FLUSH PRIVILEGES;Configure the monitor person 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;Helpful Instructions and Queries
Question Digest Statistics
Retrieve detailed statistics about question efficiency:
SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;Connection Pool Stats
Present used connections:
SELECT hostgroup, srv_host, ConnUsed, ConnFree, ConnOK, ConnERR, MaxConnUsed, Queries, Bytes_data_sent, Bytes_data_recv FROM stats_mysql_connection_pool;Present what number of occasions queries have been used:
SELECT * FROM stats_mysql_query_digest ORDER BY count_star DESC;
SELECT * FROM stats_mysql_global WHERE Variable_Name LIKE 'Query_Cache%';Verify Whole 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
- Be certain that you tailor the
INSERT INTO mysql_usersstatements with the right usernames, passwords, and schema names per your setting. - At all times save the configurations to disk after loading them to runtime to make them persistent throughout restarts.
Wrapping Up the ProxySQL Cheat Sheet
Following these directions will arrange ProxySQL with optimum settings for connection reuse, monitoring, and environment friendly question dealing with. Alter the parameters in response to your particular use case and workload necessities.
References
ProxySQL Official Documentation: That is probably the most authoritative supply for ProxySQL data. It covers all the pieces from set up to superior configuration.
