Article From:https://www.cnblogs.com/muzishanhe/p/9970260.html

SMProxy

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

swoole MSYQL proxy”. a MySQL database connection pool based on MySQL protocol and swoole development

Database Connection Pool

The database connection pool is responsible for allocating, managing and releasing database connections. It allows applications to reuse an existing database connection rather than re-establish it.Release database connections with idle time exceeding maximum idle time to avoid database connection legacy caused by not releasing database connectionsLeakage.This technology can significantly improve the performance of database operation.

Design Origin

phpThere is no connection pool, so when there is high concurrency, the database will be full of connections.
mycatSome database middleware such as SQL can not be used, for example, does not support batch addition, and is too bloated.
So I wrote this lightweight middleware which only supports connection pool and read-write separation.
Scheduling handshake V10 protocol forwarding with swoole coroutine makes the program more stable without parsing all SQL packages like mycat, increasing complexity.

  • swoole 2.1+
  • php 7.0+

    Running

  • bin/server start : Operation service
  • bin/server stop : Out of Service
  • bin/server restart : Restart service
  • bin/server status : Query Service Running Status
  • bin/server reload : graceful restart
  • bin/server -h : Help

    SMProxy Connection Test

    Testing SMProxy is exactly the same as testing mysql. How does MySQL connect and how does SMProxy connect?

  • Command-line testing is recommended first:

    mysql -uroot -p123456 -P3366 -h127.0.0.1

    Tool connections can also be used.

    Test

    $start = microtime(true);
    print_r(Db::query('select * from account limit 1'));
    print_r(microtime(true)-$start);

    Running without SMProxy connection pool:

    0.17314600944519

    Run with SMProxy connection pool:

    0.073625087738037

    Configuration File:

    ROOT Current SMProxy and Directory

    database.json

    {
      "database": {
        "account": {
          "root": {
            "user": "root", 
            "password": "123456"
          }
        },
        "serverInfo": {
          "server1": {
            "write": {
              "host": "127.0.0.1",
              "port": 3306,
              "timeout": 0.5,
              "flag": 0,
              "account": "root"
            },
            "read": {
              "host": "127.0.0.1",
              "port": 3306,
              "timeout": 0.5,
              "flag": 0,
              "account": "root"
            }
          }
        },
        "databases": {
          "db1": {
            "serverInfo": "server1",
            "maxSpareConns": 10,
            "maxConns": 20,
            "charset": "utf-8"
          }
        }
      }
    }
    account account informationserverInfo Service informationdatabases Database connection pool information
    account.root User ID corresponds to serverInfo… account. rootserverInfo.server1 Service identification corresponds to databases.. ServerInfodatabases.db1 Database name
    account..user User nameserverInfo..write Read Read Library of Read-Write Separation Librarydatabases..serverInfo Service information
    account..password PasswordserverInfo..host Database Connection Addressdatabases..maxSpareConns Maximum number of idle connections
    serverInfo..prot Database Portdatabases..maxConns maximum connection
    serverInfo..timeout Database timeout (seconds)databases..charset Database Encoding Format
    serverInfo..flag TCPType currently supports 0 blocking does not support 1. Non-blocking
    serverInfo..account Corresponding to databases. account

    server.json

    {
      "server": {
        "user":"root",
        "password":"123456",
        "charset":"utf8mb4",
        "host": "0.0.0.0",
        "port": "3366",
        "mode": 3,
        "sock_type": 1,
        "logs": {
          "open":true,
          "config": {
            "system": {
              "log_path": "/var/www/swoole/swoole-mysql-proxy/logs",
              "log_file": "system.log",
              "format": "Y/m/d"
            },
            "mysql": {
              "log_path": "/var/www/swoole/swoole-mysql-proxy/logs",
              "log_file": "mysql.log",
              "format": "Y/m/d"
            }
          }
        },
        "swoole": {
          "worker_num": 2,
          "max_coro_num": 16000,
          "open_tcp_nodelay": true,
          "daemonize": 0,
          "heartbeat_check_interval": 60,
          "heartbeat_idle_time": 600,
          "reload_async": true,
          "log_file": "/var/www/swoole/swoole-mysql-proxy/logs/error.log",
          "pid_file": "/var/www/swoole/swoole-mysql-proxy/logs/pid/server.pid"
        },
        "swoole_client_setting": {
          "package_max_length": 16777216
        },
        "swoole_client_sock_setting": {
          "sock_type": 1,
          "sync_type": 1
        }
      }
    }
    user Service usernamepassword Service passwordcharset Service codinghost Link addressport Service ports are separated by multiple portsmodesock_type 1 tcplogs Log configurationswoole swooleTo configureswoole_client_setting Client Configurationswoole_client_sock_setting Client sock configuration
    logs.open Log switchworker_num workProcess quantitypackage_max_length Maximum packet lengthsock_type 1.tcp
    logs.config Log Configuration Itemsmax_coro_num Maximum number of carrierssync_type 1.asynchronous
    logs.system or mysql Configuration moduleopen_tcp_nodelay Turn off Nagle merge algorithm
    logs..log_path Log directorydaemonize Daemonization
    logs..log_file Log File Nameheartbeat_check_interval runtastic Heart Rate PRO
    logs..format Log date formatheartbeat_idle_time Maximum idle time
    reload_async Asynchronous restart
    log_file Log directory
    pid_file Home process PID directory

    Post Views: 1

    Leave a Reply

    Your email address will not be published. Required fields are marked *