Smproxy makes your database operation three times faster!

Time:2021-11-22

Chinese|English

SMProxy

  /$$$$$$  /$$      /$$ /$$$$$$$                                        
 /$$__  $$| $$$    /$$$| $$__  $$                                       
| $$  \__/| $$$$  /$$$$| $$  \ $$ /$$$$$$   /$$$$$$  /$$   /$$ /$$   /$$
|  $$$$$$ | $$ $$/$$ $$| $$$$$$$//$$__  $$ /$$__  $$|  $$ /$$/| $$  | $$
 \____  $$| $$  $$$| $$| $$____/| $$  \__/| $$  \ $$ \  $$$$/ | $$  | $$
 /$$  \ $$| $$\  $ | $$| $$     | $$      | $$  | $$  >$$  $$ | $$  | $$
|  $$$$$$/| $$ \/  | $$| $$     | $$      |  $$$$$$/ /$$/\  $$|  $$$$$$$
 \______/ |__/     |__/|__/     |__/       \______/ |__/  \__/ \____  $$
                                                               /$$  | $$
                                                              |  $$$$$$/
                                                               \______/

github : https://github.com/louislivi/smproxy

Like to invite star

Swoole MySQL Proxy

A MySQL database connection pool developed by swoole based on MySQL protocol.

principle

The database connection is stored in memory as an object. When the user needs to access the database, a connection will be established for the first time. Later, instead of establishing a new connection, an established idle connection object will be taken from the connection pool.
After use, the user does not close the connection, but puts the connection back into the connection pool for access by the next request. The establishment and disconnection of connections are managed by the connection pool itself.

At the same time, you can also set the parameters of the connection pool to control the initial number of connections in the connection pool, the upper and lower limits of connections, as well as the maximum usage times and maximum idle time of each connection.
You can also monitor the number and usage of database connections through its own management mechanism. If the maximum number of connections is exceeded, the collaboration will be suspended. Wait until a connection is closed, and then resume the collaboration to continue operation.

characteristic

  • Support read-write separation
  • Support database connection pool, which can effectively solve the database connection bottleneck caused by PHP
  • Support SQL92 standard
  • Cooperative scheduling
  • Support multiple database connections, multiple databases, multiple users, and flexible collocation
  • Comply with MySQL native protocol, cross language and cross platform general middleware agent
  • MySQL support
  • Handshake V10 protocol version is supported
  • Perfect compatibility with MySQL 4.1 – 8.0
  • Compatible with all major frameworks to seamlessly improve performance

Original design intention

PHP does not have a connection pool, so the database will be full when it is highly concurrent. Some SQL can not be used in database middleware such as MYCAT, for example, it does not support batch addition, and it is too bloated.
Therefore, I wrote this lightweight middleware that only supports connection pool and read-write separation. Using the Poole protocol to schedule handshake V10 protocol forwarding makes the program more stable and does not need to parse all SQL packages like MYCAT, increasing complexity.

environment

  • Swoole 2.1+
  • PHP 7.0+

install

(recommended) download the latest version of phar file directly, extract it and use it immediately:

https://github.com/louislivi/smproxy/relea…

Or use git to switch any version:

git clone https://github.com/louislivi/smproxy.git
Composer install -- no dev # if you want to contribute your code, please do not use the -- no dev parameter.

function

You need to give bin / smproxy execution permission.

  SMProxy [ start | stop | restart | status | reload ] [ -c | --config <configuration_path> ]
  SMProxy -h | --help
  SMProxy -v | --version

Options:

  • Start run service
  • Stop stop service
  • Restart restart service
  • Status query service running status
  • Reload smooth restart
  • -H — help
  • -V — version view the current service version
  • -C — config setting configuration item directory

Smproxy connection test

Testing smproxy is exactly the same as testing mysql. Smproxy connects as MySQL connects.

It is recommended to use the command line test first:
(do not use mysql8.0 client link test)

mysql -uroot -p123456 -P3366 -h127.0.0.1

Tools can also be used for connection.

PHP 7.2.6 without framework

Smproxy makes your database operation three times faster!

Useless: 0.15148401260376, used: 0.040808916091919

Unused connection pool: 0.15148401260376

Smproxy makes your database operation three times faster!

Use connection pool: 0.040808916091919

Smproxy makes your database operation three times faster!

ThinkPHP 5.0

Smproxy makes your database operation three times faster!

Unused connection pool:

Smproxy makes your database operation three times faster!

Use connection pool:

Smproxy makes your database operation three times faster!

Laravel 5.7

Smproxy makes your database operation three times faster!

Unused connection pool:

Smproxy makes your database operation three times faster!

Use connection pool:

Smproxy makes your database operation three times faster!

Number of MySQL connections

Unused connection pool:

Smproxy makes your database operation three times faster!

Use connection pool:

Smproxy makes your database operation three times faster!

Please refer to the actual pressure measurement, which is related to the root data volume, network environment and database configuration.
In the test, because the maximum number of connections is exceeded, the collaboration will be suspended until a connection is closed, and then the collaboration will be resumed to continue operation,
All concurrency is not appropriate to the maxconns setting in the configuration file, which will cause the link to be slower than the original link, mainly to control the number of connections.

communication

QQ group: 722124111

configuration file

  • The configuration file is located atsmproxy/confDirectory, where uppercaseROOTRepresents the current smproxy root directory.

database.json

{
  "database": {
    "account": {
      "Custom user name":{
        "User": "required, database account",
        "Password": "required, database password"
      },
      "...": "one is required. The user-defined user name corresponds to the account in serverinfo"
    },
    "serverInfo": {
      Custom database connection information:{
        "write": {
          "Host": "required. Multiple write library addresses are represented by []",
          "Port": "required, write library port",
          "Timeout": "required, write library connection timeout (seconds)",
          "Account": "required. The user-defined user name corresponds to the user-defined user name in account"
        },
        "read": {
          "Host": "optional. Multiple library read addresses are represented by []",
          "Port": "optional, library reading port",
          "Timeout": "optional, read library connection timeout (seconds)",
          "Account": "optional. The user-defined user name corresponds to the user-defined user name in account"
        }
      },
      "...": "one is required. The user-defined database connection information corresponds to serverinfo in databases. The read library can not be configured"
    },
    "databases": {
      Database name:{
        "Serverinfo": "required. The user-defined database connection information corresponds to the user-defined database connection information in serverinfo",
        "Maxconns": "required, the maximum number of connections of the library service, which supports calculation",
        "Maxspareconns": "required. The maximum number of free connections in the library service supports calculation",
        "Startconns": "optional. The library service starts the number of connections by default and supports calculation",
        "Maxspareexp": "optional, the maximum number of idle connections in the library service, the maximum idle time (seconds), the default is 0, and calculation is supported",
        "Charset": "optional, the library encoding format"
      },
      "....": "1 required, multiple database names, multiple database configurations"
    }
  }
}
  • maxConns,maxSpareConns,startConns
    • Recommended setting isserver.jsonConfigured inworker_numMultiple ofswoole_cpu_num()*N
  • Multiple read libraries and write Libraries
    • At present, random connection is adopted, and it is recommended tomaxConnsstartConnsstartConnsSet to at leastMax (read library, write library) * worker_ numMore than 1 time of

server.json

{
  "server": {
    "User": "required, smproxy service user",
    "Password": "required, smproxy service password",
    "Charset": "optional, smproxy encoding, default utf8mb4",
    "Host": "optional, smproxy address, default 0.0.0.0",
    "Port": "optional, smproxy port, default 3366",
    "Mode": "optional, smproxy operation mode, swing_process multi process mode (default), swing_base basic mode",
    "Sock_type": "optional, sock type, swing_sock_tcp",
    "logs": {
      "Open": "required, log switch, true on, false off",
      "config": {
        "system": {
          "Log_path": "required, smproxy system log directory",
          "Log_file": "required, smproxy system log file name",
          "Format": "required, smproxy system log directory date format"
        },
        "mysql": {
          "Log_path": "required, smproxyymysql log directory",
          "Log_file": "required, smproxyymysql log file name",
          "Format": "required, smproxyymysql log directory date format"
        }
      }
    },
    "swoole": {
      "Worker_num": "required, number of swing worker processes, supporting calculation",
      "Max_coro_num": "required. The number of swing processes is recommended to be no less than 3000",
      "Pid_file": "required, worker process and manager process PID directories",
      "Open_tcp_nodelay": "optional, turn off the Nagle merge algorithm",
      "Daemon": "optional, daemonization, true for daemons, false for daemons",
      "Heartbeat_check_interval": "optional, heartbeat detection",
      "Heartbeat_idle_time": "optional, maximum idle time for heartbeat detection",
      "Reload_async": "optional, asynchronous restart, true to enable asynchronous restart, false to disable asynchronous restart",
      "Log_file": "optional, swing log directory"
    },
    "swoole_client_setting": {
      "Package_max_length": "optional. The maximum package length of swing client is 16777216mysql maximum supported package length by default"
    },
    "swoole_client_sock_setting": {
      "Sock_type": "optional, swing client sock type. The default TCP only supports TCP"
    }
  }
}
  • user,password,port,host
    • bySMProxyAccount | password | port | address (non MySQL database account | password | port | address)
    • Optional settings forSMProxyvalidate logon
    • For example, the default configuration login ismysql -uroot -p123456 -P 3366 -h 127.0.0.1
    • SMProxyMySQL commit will prompt you if you log in successfullyServer version: 5.6.0-SMProxy
  • worker_num
    • Recommended useswoole_cpu_num()orswoole_cpu_num()*N

MySQL8.0

  • SMProxy1.2.4And above can be used directly
  • SMProxy1.2.4The following compatibility processing is required
    MySQL-8.0More secure is used by defaultcaching_sha2_passwordPlug in, if other versions are from5.xUpgrade up, you can directly use allMySQLFunctions, such as newMySQL, need to enterMySQLThe command line does the following to be compatible:
    ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
    flush privileges;

    The'root'@'%'Replace with the user you use,passwordReplace with its password

If it still cannot be used, it should be set in my.cnfdefault_authentication_plugin = mysql_native_password

Other learning materials

This work adoptsCC agreement, reprint must indicate the author and the link to this article

Recommended Today

Apache sqoop

Source: dark horse big data 1.png From the standpoint of Apache, data flow can be divided into data import and export: Import: data import. RDBMS—–>Hadoop Export: data export. Hadoop—->RDBMS 1.2 sqoop installation The prerequisite for installing sqoop is that you already have a Java and Hadoop environment. Latest stable version: 1.4.6 Download the sqoop installation […]