Detailed explanation of improving the performance of PHP/MySQL through ProxySQL

php mysql column introduces how ProxySQL improves the performance of PHP/MySQL.

A few days ago I introduced how to implement the Redis connection pool through Twemproxy to improve 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. Subsequent configurations are obtained from SQLite. If you want to force the use of a configuration file, you need to use the initial command. For more information, please refer to: Detailed explanation of installation and configuration of ProxySQL. In addition, there are some small details to pay attention to:

First of all, pay attention to admin_credentials in admin_variables. The default value is "admin:admin", which means the default user name and password are both admin, and mysql_ifaces, which is missing The default value is "0.0.0.0:6032", which 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 the 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. A woman from a good family, everyone wants to do it twice, who can’t do it? It’s bad after rm.

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 at any time. Be sure to remember to create the corresponding account on the back-end MySQL server in advance. , I didn’t create a monitoring account at the beginning. As a result, ProxySQL stopped responding after running for a while. This is because when ProxySQL keeps trying to access the back-end server using the monitoring account and password in the configuration, it will generate a lot of "Access denied" messages. for user 'monitor'@'...'" error, when it reaches a certain threshold, it will cause "Host '...' is blocked because of many connection errors". At this time, ProxySQL will not be able to respond to the request. You must use "mysqladmin" on MySQL. flush-hosts" will do. 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 Detailed explanation of improving the performance of PHP/MySQL through ProxySQL. For more information, please follow other related articles on the PHP Chinese website!