OneProxy makes MySQL sharding easy

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 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. More Information

The MySQL Transaction Concurrency Control Patch

    I start reading MySQL source code half years ago, and try to make MySQL run as fast as Oracle, and it works now! I made the following changes in my patch set.

  • New Thread Pool Implementation.
  • Memory Optimization, memory allocation greatly reduced in InnoDB.
  • Dead lock detection optimization, not dead lock detection for first lock request.
  • Statically compiled with Google TCMalloc library.
  • Sub pool for simple query, complex query, autocommit transaction and manual transaction.
  • Read only transaction optimization for autocommit query.
  • Dedicate log write thread if innodb_flush_log_at_trx_commit set to 3.
  • Binlog asynchronize real time flush for better data procetion.

    Just download the relaitve mysqld binary, and start your MySQL with the new binary and benchmark it, you will get double performance, and don't be afraid of concurrent massive requests any more.

    In my bench test, I got a 350000 QPS for primary key lookup under MySQL 5.5, and 380000 QPS for primary key loop under MySQL 5.6, higher QPS means better response time, it could save you a lot.

    What's the benifit of this patch to you? If you have 10 MySQL database servers before, you can use just 5 MySQL servers now. If you have 100 MySQL database servers before, you can use just 50 MySQL servers now. If you have 1000 MySQL database servers before, you can use just 500 MySQL servers now. If you have 10000 MySQL database servers before, you can use just 5000 MySQL servers now.

The Chinese Full Text MySQL Plugin

    It's a good idea to build small search site with MySQL's full text search feature, but it does not support Chinese, so I write several full text plugins with different Chinese word identification algorithm. You can download them from here (with file names like ""parser*" pattern).

    Friso is a MMSeg algorithm based Chinese word identification library wrote in C language, the latest version support both GBK and UTF8 character set. Install the plugin by run "INSTALL PLUGIN friso_parser SONAME 'ft_friso'" command.

    LibMMSeg is a MMSeg algorithm based Chinese word identification library wrote in C language, it support UTF-8 only. Install the plugin by run "INSTALL PLUGIN mmseg_parser SONAME 'ft_mmseg'" command.

    wcws is a dictionary based Chinese word identification library wrote in C language, support both GBK and UTF8. Install the plugin by run "INSTALL PLUGIN scsw_parser SONAME 'ft_scsw'" command.

    For each plugin, you must set a "<plugin name>_config_path" variable for the configuration files and dictionary files path correctly, before you can use it. For more details contact me by email. You can use different parser by the following command.

  • CREATE FULLTEXT INDEX index_name ON table_name (column_name) WITH PARSER parser_name;

    The plugin will automaticall choose the right character set for Friso and wcws, you don't care whether it's GBK or UTF8 character set. Have fun!


(©)Copyright 2006-2010 AnySQL.net. All rights reserved.