Improve the performance of PHP/MySQL through ProxySQL method

Related learning recommendations: mysql tutorial

A few days ago I introduced how to implement the Redis connection pool through Twemproxy. Thereby improving the performance of PHP/Redis. Today I want to introduce ProxySQL, through which MySQL connection pool can be implemented, thereby improving the performance of PHP/MySQL. In fact, the principles are similar. There is no need to write another article to elaborate on it, but in the process of configuring and using ProxySQL, I encountered some minor problems, and I feel like I should record them.

Regarding the installation process, there is a detailed description in the official Wiki. Mainly look at the configuration file /etc/proxysql.cnf:

datadir="/var/lib/proxysql"
admin_variables=
{
admin_credentials="admin:admin"
mysql_ifaces="0.0.0.0:6032"
}
mysql_variables=
{
threads=4
max_connections=2048
default_query_delay=0
default_query_timeout=36000000
have_compress=true
poll_timeout=2000
interfaces="/var/run/proxysql.sock"
default_schema="information_schema"
stacksize=1048576
server_version="5.5.30"
connect_timeout_server=3000
monitor_username="monitor"
monitor_password="monitor"
monitor_history=600000
monitor_connect_interval=60000
monitor_ping_interval=10000
monitor_read_only_interval=1500
monitor_read_only_timeout=500
ping_interval_server_msec=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
}
mysql_servers =
(
{
address="..."
port=3306
}
)
mysql_users:
(
{
username = "..."
password = "..."
}
)

The most important thing to note is: ProxySQL uses SQLite to save configuration information. The configuration file is only valid when it is started for the first time. Subsequently, it is obtained from SQLite. configuration. If you want to force the use of a configuration file, you need to use the initial command.

First of all, pay attention to admin_credentials in admin_variables. The default value is "admin:admin", which means the default username and password are both admin, and mysql_ifaces, whose default value is " 0.0.0.0:6032" means listening to port 6032 of all network interfaces. Imagine that you have a network interface that can be accessed by the external network. Then malicious users will be able to use the default username and password to enter your admin system, so do not use Default username and password, and do not default to listening on the external network interface, remember! Remember! Remember!

Secondly, pay attention to the interfaces in mysql_variables, because PHP needs to request the ProxySQL connection pool through the local Unix Domain Socket, so the interfaces should not use the ip:port form, but set it to the local Unix Domain Socket form. , in this example it is set to /var/run/proxysql.sock. It should be reminded that many people like to put Socket files under the /tmp path. This is not a good habit. After all, /tmp does not look like a path from its name. Women from good families, everyone wants to do it twice, maybe it will be bad if someone rms it once.

Finally, pay attention to monitor_username and monitor_password in mysql_variables. It sets the relevant information of the monitoring user so that ProxySQL can keep track of the status of the back-end MySQL server. Be sure to remember to create the corresponding account on the back-end MySQL server in advance. , I did not create a monitoring account at the beginning, and as a result, ProxySQL stopped responding after running for a while.

This is because when ProxySQL continues to use the monitoring account and password in the configuration to try to access the back-end server, it will A lot of "Access denied for user 'monitor'@'...'" errors are generated. When a certain threshold is reached, "Host '...' is blocked because of many connection errors" will result. At this time, ProxySQL cannot respond to the request. It must On MySQL, "mysqladmin flush-hosts" will work. Relevant log information can be viewed in "SELECT * FROM monitor.mysql_server_ping_log".

Let us perform a stress test to see how the performance is. The test script test.php is as follows:

<?php
$host = &#39;...&#39;;
$user = &#39;...&#39;;
$password = &#39;...&#39;;
$database = &#39;...&#39;;
$charset = &#39;utf8mb4&#39;;
$socket = &#39;/var/run/proxysql.sock&#39;;
$dsn = "mysql:dbname={$database};charset={$charset}";
if (empty($_GET[&#39;proxysql&#39;])) {
    $dsn .= ";host={$host}";
} else {
    $dsn .= &#39;;unix_socket={$socket}&#39;;
}
$dbh = new PDO($dsn, $user, $password);
$sql = &#39;SELECT * FROM foo LIMIT 10&#39;;
$value = $dbh->query($sql);
foreach ($value as $v) {
    var_dump($v);
}
?>

Simulate a high-concurrency scenario through ab, and perform a stress test to see if the performance is improved:

shell> ab -k -n 10000 -c 100 "http://path/test.php?proxysql=0"
shell> ab -k -n 10000 -c 100 "http://path/test.php?proxysql=1"

In the end, on a generally configured server, I got about 1,500 RPS without ProxySQL, and about 2,000 RPS with ProxySQL. In other words, ProxySQL brought a 25% performance improvement. .

If you want to know more related articles, please pay attention to the php mysql column!

The above is the detailed content of Improve the performance of PHP/MySQL through ProxySQL method. For more information, please follow other related articles on the PHP Chinese website!