OneProxy for MySQL = Load Balance + Table Sharding + SQL Firewall + More

OneProxy is a MySQL protocol compatiable database middleware. It offers better performance and more features compares to existing proxies, you can scale and secure the database system freely, as it can be an effective connection pool for different applications and laguages, or a SQL router for load balances and read/write splitting / failover,  or a data sharding (horizontal partition) router for applications, it can also be the database firewall for SQL anti-injection. So you can scale and secure your databases freely just as switchs / routers / firewalls for networking.

oneproxy_architecture

OneProxy sit between the application server and the MySQL databases as a standalone server, it can parse the MySQL communication protocol packet, and make an intelligent choise based on the query text by table name or by where codition of the partition key column. Form application side, OneProxy is a real MySQL server, the backend MySQL servers are invisible to application developers, every SQL query are routed by OneProxy transparently.

OneProxy can be quickly deplied on any private cloud and public cloud, just follow the first step of OneProxy. Major features including :

  1. Connection Pool, OneProxy will authorize the application client’s login requests, and different applications and system can share the same connection pool.
  2. Read Failover, OneProxy can route query statement to slave according to the policy you specified, while the application will not feel the master node failure at all for read operation.
  3. Read Write Splitting, OneProxy can route query statement to slaves in round lobbin logic according to the policy you sepcified, all the write operations (DMLs or transactions) will be routed to master only, totally transparent to exist applications, such as adding or removing slave node.
  4. Database Sharding, OneProxy can parse the query text, and can route the query by table name (vertical partition) or by partition key value in where condition (horizontal partition) without application code change. Just create a partition configuration file, DDL can be routed too for easier database management.
  5. Distributed Query, query involve multiple sharding pieces are supported with some restriction, including result set merge, queries with order by and group by are supported, no extra client coding required.
  6. Parallel Query,  queries involving multiple sharding pieces can be parallelized,  such as summary billion rows in few seconds.
  7. Security Control, you can disable DDLs or DMLs for specific MySQL server group, or set them at table level, or disable the queries without an explicit where condition, of cause you can audit every SQL client sent to proxy.
  8. SQL Firewall, you can setup a IP white list for access control, or build a SQL white list to protect you database from SQL injection. Or you can enable the client based QoS control to protect your database, and forward the client IP address into SQL as comment for tracing.
  9. Performance Statstics, OneProxy have performance statistics data summarized by query statement, by tables and by client ip address. You can access them by any client utility with MySQL protol support.
  10. High Availability, there is keepalive daemon process to start a new OneProxy instance on process failure, and you can setup a virtual ip address to quick failover for server failure. Most of the options can be adjusted dynamically.

OneProxy has be deploied on production by more than 10 customers and users, some for connection pool feature, some for read/write splitting,  and some for freely database sharding. There is another good news that OneProxy for PostgreSQL protocol is comming soon!

OneProxy is CPU and network sensitive program, higher frequency CPU (single Intel® Xeon® Processor E5-2697 v3 socket is enough for 500K QPS) and 10Gb network interface are prefered, here is Checking List for 500K QPS.