1. How to configure connection pooling in ProxySQL ?
The variable has been documented here : mysql-free_connections_pct
More information with examples : Connection pooling
2. How does ProxySQL handle the
USE dbname Command ?
Some user has raised a question asking why will
USE database always succeed in ProxySQL even when the database doesn’t exist.
This document will explain how ProxySQL deals with default schema and -D [dbname] : USE databasename
3. Monitor module responsibilities in more details
The core of ProxySQL also observes the success/failure of backend servers, but it has some limitations (More details later on)
But the Monitor module extends the functionality of the core of ProxySQL.
How to configure monitoring in ProxySQL: Configure Monitoring
The responsibilities have also been documented: here
More in detail : click here
4. How does ProxySQL handle database failover?
An important point to note is that ProxySQL is an agent and it doesn’t handle any kind of failover by its own.
But it is developed to handle DB failovers initiated by external tools.
The below documents will explain how ProxySQL deals with failover to achieve high availability.
ProxySQL + MHA ProxySQL and MHA integration
ProxySQL + mysqlrpladmin ProxySQL and mysqlrpladmin
How to configure MHA Setup MHA
5. How do we avoid the problem of ProxySQL being a single point of failure?
ProxySQL itself doesn’t have a built-in HA solution, but it is very easy to architecture its deployment in order to avoid SPOF.
A few months back Percona published some articles where it is highlighted how to avoid single points of failure.
Few more examples on how to implement MySQL HA (high-availability) solutions: here
6. What is Mirroring in ProxySQL and when to use it ?
ProxySQL’s mirror feature allows us to send real application traffic to a completely separate server without touching the application.
Mirroring functionality is not like replication topology in MySQL, but it has some use cases.
For more information: In detail
7. How to use
apply to improve performance if you have multiple query rules?
If you have more query rules, then all your queries have to match against all of them and this has a serious impact on performance. How can we avoid that?
apply – They work together and apply logic into your rules so that even if you have more rules, you will get better performance.
flagIN, flagOUT, apply – these allow us to create a “chain of rules” that get applied one after the other. An input flag
value is set to 0, and only rules with flagIN=0 are considered at the beginning. When a matching rule is found for a
specific query, flagOUT is evaluated and if NOT NULL the query will be flagged with the specified flag in flagOUT. If
flagOUT differs from flagIN, the query will exit the current chain and enter a new chain of rules having flagIN as the
new input flag. If flagOUT matches flagIN, the query will be re-evaluated again against the first rule with said flagIN.
This happens until there are no more matching rules, or apply is set to 1 (which means this is the last rule to be
Benchmark Result : Too many query rules
How to create a chain of rules : example
8. Is there any monitoring tool available to get statistic report for ProxySQL servers?
This feature is documented in detail, kindly refer to document
9. How to manage ProxySQL configuration across multiple servers?
Actually it is easy to reconfigure ProxySQL at runtime , we can use a variety of approaches, like using a configuration management tool (Puppet, Chef, Ansible, Salt etc..) or a service discovery toos (Consul, Etcd, Zookeeper) to automatically reconfigure ProxySQL if needed.
But now it got more simple by using ProxySQL Cluster
10. How to gracefully shutdown the ProxySQL process?
kill is the right way to the graceful shutdown of ProxySQL.
When we execute the
kill command, internally it sends a SIGTERM signal to the ProxySQL process.
SIGTERM 15 – Software termination signal (sent by kill by default)
More details : click here
11. No Hostgroup 0 has been configured, then why do we get “Max connect timeout reached while reaching hostgroup 0 after 10000ms”?
This is how MySQL Query Rules work while selecting hostgroups.
When you set any query rules inside table
mysql_query_rules, then your query gets analyzed by the Query Processor to decide which destination hostgroup it should be forwarded to. (according to mysql_query_rules.destination_hostgroup)
When your Query Processor doesn’t find any query matching the query rule then the default hostgroup for the specific user is applied (according to mysql_users.default_hostgroup)
Example : Default Hostgroup for User
12. What is Multiplexing and How does it work (enable/disable)?
Multiplexing – Reduce the number of connections against mysqld.
Many clients connections (tens of thousands) can use few backend connections (few hundreds).
So it is possible for the requests coming from a single client to be evenly distributed among all the backends of the same hostgroup.
But at same time ProxySQL understands the requirement of transactions execution.
If a transaction is running, then multiplexing will get disabled until the transaction would rollback or commit.
So in that case, ProxySQL makes clients to remember the backend and executes all queries on same backend server.
Default value for
mysql-multiplexing is true
mysql> select * from global_variables where variable_name like '%multiplexing%';
| variable_name | variable_value |
| mysql-multiplexing | true |
1 row in set (0.01 sec)
More on multiplexing : Here
13. How to configure ProxySQL using the config file?
Yes, the option is available to start ProxySQL from the config file using the
proxysql --initial -f -c /etc/proxysql.cnf
There are a few things you should know before using this flag : Initial flag
14. Why do entries in mysql_servers duplicated?
A few user have raised the question of why the writer host gets duplicated into the reader hostgroup.
Okay, so this behaviour is intensional!
And it is controlled by
mysql-monitor_writer_is_also_reader in Monitor Module
When we load
MYSQL SERVERS, our writer host also gets configured in the reader hostgroup automatically by ProxySQL to handle all those queries which are redirected to reader hostgroup in case no slaves are online.
This feature is dependent on reader/writer hostgroup which we configured in table
LOAD MYSQL SERVERS TO RUNTIME processes both
15. How can I kill a connection ?
You can find the processlist information in the ProxySQL Admin stats table by executing
select * from stats_mysql_processlist;. This table contains the following fields
ThreadID: Thread Identifier
SessionID: Session Identifier
user: Authentication User
db: Connected Database
cli_host: Client host / IP
cli_port: Client port
hostgroup: Hostgroup Identifier
l_srv_host: Listening host / IP (ProxySQL)
l_srv_port: Listening port (ProxySQL
srv_host: MySQL host / IP (Backend MySQL instance)
srv_port: MySQL port (Backend MySQL instance)
command: Command State
time_ms: Execution time
info: SQL Statement executing
From here you can identify the
KILL CONNECTION e.g.:
ProxySQL Admin> KILL CONNECTION 1;
16. When does query routing get disabled?
When we enable
transaction_persistent for a specific user and application to execute
transactions, it will always use the same host to execute all queries to get more accurate results.
Please note that it disables query routing.
Admin> SELECT username, default_hostgroup, transaction_persistent, fast_forward FROM mysql_users;
| username | default_hostgroup | transaction_persistent | fast_forward |
| root | 0 | 1 | 0 |
More Detail : Example
Note – By default sysbench uses
To disable transactions and ps make use of
17. How to drain connections from ProxySQL?
proxysql pause stops the listeners so no new connection gets accepted. Current connections and transactions are maintained.
To start listening for new connections again it is necessary to run