1. How to configure connection pooling in ProxySQL ?

Variable has been documented here : mysql-free_connections_pct

More information with example : Connection pooling

2. How ProxySQL handle USE dbname Command ?

Some user has raised question asking, why USE database will always succeed in ProxySQL even when database doesn't exist.

This document will explain , How ProxySQL deal with default schema and -D [dbname] : USE databasename

3. Monitor module responsibilities more in details

The core of ProxySQL also observes success/failure of backend servers , but it has some limitation (More details later on)

But the Monitor module extends the functionality of the core of ProxySQL.

How to configure monitoring in ProxySQL: Configure Monitoring

And responsibilities has been documented: here

More in detail : click here

4. How ProxySQL handle database failover?

Important point to note 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.

Below documents will explain how ProxySQL deal 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.Few months back Percona published some articles where it is highlighted how to avoid single points of failure.

http://proxysql.com/blog/multiple-proxysql-on-same-ports

http://proxysql.com/blog/how-to-run-multiple-proxysql-instances

https://www.percona.com/blog/2017/01/19/setup-proxysql-for-high-availability-not-single-point-failure/

https://www.percona.com/blog/2016/09/16/consul-proxysql-mysql-ha/

Few more example to implement MySQL HA (high-availability) solution : 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 flagIN, flagOUT, apply to improve performance , If you have multiple query rules ?

If you have more query rules , Then all your queries has to match against all of them and this has serious impact on performance . How can we avoid that?

flagIN, flagOUT, apply - It works 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 "chain of rules" that get applied one after the other. An input flagvalue is set to 0, and only rules with flagIN=0 are considered at the beginning. When a matching rule is found for aspecific query, flagOUT is evaluated and if NOT NULL the query will be flagged with the specified flag in flagOUT. IfflagOUT differs from flagIN , the query will exit the current chain and enters a new chain of rules having flagIN as thenew input flag. If flagOUT matches flagIN, the query will be re-evaluate 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 beapplied)

Benchmark Result : Too many query rules

How to create chain of rules : example

8. Any monitoring tool available to get statistic report for ProxySQL servers ?

This feature is documented in detail , Kindly refer 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 tools (Consul, Etcd, zookeeper) to automatically reconfigure ProxySQL if needed.

But now it got more simpler using ProxySQL Cluster

10. How to gracefully shutdown ProxySQL process ?

kill is the right way to graceful shutdown of proxysql.

When we execute kill command , Internally it send SIGTERM signal to ProxySQL process.

SIGTERM15 - Software termination signal (sent by kill by default)

More details : click here

11. No Hostgroup 0 has been configured , then why 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 get analyzed by the Query Processor to decide which destination hostgroup it should forward. (according to mysql_query_rules.destination_hostgroup)

  • When your Query Processor doesn't find any query matching to 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 it works (enable/disable) ?

Multiplexing - Reduce the number of connections against mysqld.Many clients connections (tens of thousands) can use few backend connections (few hundreds)So there is possibility that the requests coming from a single client are evenly distributed among all the backends of the same hostgroup.

But at same time ProxySQL understand 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 make clients to remember the backend and execute all queries on same backend server.

Default value for mysql-multiplexing is true

  1. mysql> select * from global_variables where variable_name like '%multiplexing%';
  2. +--------------------+----------------+
  3. | variable_name      | variable_value |
  4. +--------------------+----------------+
  5. | mysql-multiplexing | true           |
  6. +--------------------+----------------+
  7. 1 row in set (0.01 sec)

More on multiplexing : Here

13. How to configure ProxySQL using config file ?

Yes , Option is available to start proxysql from config file using —initial flag.

Example :

  1. proxysql --initial -f -c /etc/proxysql.cnf

Few things you should know before using this flag : Initial flag

14. Why entries in mysql_servers get duplicated ?

Few user raised question on why writer host get duplicates into reader hostgroup.

Okay , So this behaviour is intensional !Ans it is controlled by mysql-monitor_writer_is_also_reader in Monitor Module

When we load MYSQL SERVERS , Our writer host also get configured in 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 depend on reader/writer hostgroup which we configured in table mysql_replication_hostgroups.

Note : LOAD MYSQL SERVERS TO RUNTIME processes both mysql_servers and mysql_replication_hostgroups tables.

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

  1.   ThreadID: Thread Identifier
  2.  SessionID: Session Identifier
  3.       user: Authentication User
  4.         db: Connected Database
  5.   cli_host: Client host / IP
  6.   cli_port: Client port
  7.  hostgroup: Hostgroup Identifier
  8. l_srv_host: Listening host / IP (ProxySQL)
  9. l_srv_port: Listening port (ProxySQL
  10.   srv_host: MySQL host / IP (Backend MySQL instance)
  11.   srv_port: MySQL port (Backend MySQL instance)
  12.    command: Command State
  13.    time_ms: Execution time
  14.       info: SQL Statement executing

From here you can identify the SessionID and KILL CONNECTION e.g.:

  1. ProxySQL Admin> KILL CONNECTION 1;

16. When query routing get disable?

When we enable transaction_persistent for specific user and application execute transactions , it will always use same host to execute all queries to get more accurate result.Please note it disable query routing.

  1. Admin> SELECT username, default_hostgroup, transaction_persistent, fast_forward FROM mysql_users;
  2. +----------+-------------------+------------------------+--------------+
  3. | username | default_hostgroup | transaction_persistent | fast_forward |
  4. +----------+-------------------+------------------------+--------------+
  5. | root     | 0                 | 1                      | 0            |
  6. +----------+-------------------+------------------------+--------------+

More Detail : Example

Note - By default sysbench use transactions and prepared statements.To disable transactions and ps make use of —oltp-test-mode=nontrx and —db-ps-mode=disable respectively.

原文: https://github.com/sysown/proxysql/wiki/Frequently-Asked-Questions