19 六月, 2008 06:17
结婚一周年了。
很快，很快……
15 六月, 2008 04:20
MySQL5.1地的确提供了好多有力的工具来帮助我们DBA进行数据库管理。
现在看一下这个压力测试工具mysqlslap.
关于他的选项手册上以及--help介绍的很详细。
我解释一下一些常用的选项。
这里要注意的几个选项：
--concurrency代表并发数量，多个可以用逗号隔开，当然你也可以用自己的分隔符隔开，这个时候要用到--delimiter开关。
--engines代表要测试的引擎，可以有多个，用分隔符隔开。
--iterations代表要运行这些测试多少次。
--auto-generate-sql 代表用系统自己生成的SQL脚本来测试。
--auto-generate-sql-load-type 代表要测试的是读还是写还是两者混合的（read,write,update,mixed）
--number-of-queries 代表总共要运行多少次查询。每个客户运行的查询数量可以用查询总数/并发数来计算。比如倒数第二个结果2=200/100。
--debug-info 代表要额外输出CPU以及内存的相关信息。
--number-int-cols 代表示例表中的INTEGER类型的属性有几个。
--number-char-cols 意思同上。
--create-schema 代表自己定义的模式（在MySQL中也就是库）。
--query 代表自己的SQL脚本。
--only-print 如果只想打印看看SQL语句是什么，可以用这个选项。
现在来看一些我测试的例子。
1、用自带的SQL脚本来测试。
MySQL版本为5.1.23
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.063 seconds
Minimum number of seconds to run all queries: 0.063 seconds
Maximum number of seconds to run all queries: 0.063 seconds
Number of clients running queries: 50
Average number of queries per client: 4
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.070 seconds
Minimum number of seconds to run all queries: 0.070 seconds
Maximum number of seconds to run all queries: 0.070 seconds
Number of clients running queries: 100
Average number of queries per client: 2
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.092 seconds
Minimum number of seconds to run all queries: 0.092 seconds
Maximum number of seconds to run all queries: 0.092 seconds
Number of clients running queries: 200
Average number of queries per client: 1
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.115 seconds
Minimum number of seconds to run all queries: 0.115 seconds
Maximum number of seconds to run all queries: 0.115 seconds
Number of clients running queries: 50
Average number of queries per client: 4
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.134 seconds
Minimum number of seconds to run all queries: 0.134 seconds
Maximum number of seconds to run all queries: 0.134 seconds
Number of clients running queries: 100
Average number of queries per client: 2
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.192 seconds
Minimum number of seconds to run all queries: 0.192 seconds
Maximum number of seconds to run all queries: 0.192 seconds
Number of clients running queries: 200
Average number of queries per client: 1
User time 0.06, System time 0.15
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 5803, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 8173, Involuntary context switches 528
我来解释一下结果的含义。
拿每个引擎最后一个Benchmark示例。
对于INNODB引擎，200个客户端同时运行这些SQL语句平均要花0.192秒。相应的MYISAM为0.092秒。
2、用我们自己定义的SQL 脚本来测试。
这些数据在另外一个MySQL实例上。版本为5.0.45
先看一下这两个表的相关数据。
1）、总记录数。
2）、总列数。
3）、调用的存储过程
4）、我们用mysqlslap来测试
以下得这个例子代表用mysqlslap来测试并发数为25,50,100的调用存储过程，并且总共调用5000次。
Enter password:
Benchmark
Average number of seconds to run all queries: 3.507 seconds
Minimum number of seconds to run all queries: 3.507 seconds
Maximum number of seconds to run all queries: 3.507 seconds
Number of clients running queries: 25
Average number of queries per client: 200
平均每个并发运行200个查询用了3.507秒。
Benchmark
Average number of seconds to run all queries: 3.742 seconds
Minimum number of seconds to run all queries: 3.742 seconds
Maximum number of seconds to run all queries: 3.742 seconds
Number of clients running queries: 50
Average number of queries per client: 100
Benchmark
Average number of seconds to run all queries: 3.697 seconds
Minimum number of seconds to run all queries: 3.697 seconds
Maximum number of seconds to run all queries: 3.697 seconds
Number of clients running queries: 100
Average number of queries per client: 50
User time 0.87, System time 0.33
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 1877, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 27218, Involuntary context switches 3100
看一下SHOW PROCESSLIST 结果
mysql> show processlist;
+------+------+--------------------+--------------------+---------+-------+--------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+--------------------+--------------------+---------+-------+--------------------+------------------------------------------------------------------------------------------------------+
…………
| 3177 | root | % | t_girl | Query | 0 | NULL | select a.* from article as a inner join (select a.aid from article as a inner join (select cid from |
| 3178 | root | % | t_girl | Query | 0 | NULL | select a.* from article as a inner join (select a.aid from article as a inner join (select cid from |
| 3179 | root | % | t_girl | Query | 0 | NULL | select a.* from article as a inner join (select a.aid from article as a inner join (select cid from |
| 3181 | root | % | t_girl | Query | 0 | NULL | select a.* from article as a inner join (select a.aid from article as a inner join (select cid from |
| 3180 | root | % | t_girl | Query | 0 | NULL | select a.* from article as a inner join (select a.aid from article as a inner join (select cid from |
| 3182 | root | % | t_girl | Query | 0 | NULL | select a.* from article as a inner join (select a.aid from article as a inner join (select cid from |
| 3183 | root | % | t_girl | Query | 0 | NULL | select a.* from article as a inner join (select a.aid from article as a inner join (select cid from |
| 3187 | root | % | t_girl | Query | 0 | removing tmp table | select a.* from article as a inner join (select a.aid from article as a inner join (select cid from |
| 3186 | root | % | t_girl | Query | 0 | NULL | select a.* from article as a inner join (select a.aid from article as a inner join (select cid from |
| 3194 | root | % | t_girl | Query | 0 | NULL | select a.* from article as a inner join (select a.aid from article as a inner join (select cid from |
| 3203 | root | % | t_girl | Query | 0 | NULL | deallocate prepare s1 |
…………
| 3221 | root | % | t_girl | Query | 0 | NULL | select a.* from article as a inner join (select a.aid from article as a inner join (select cid from |
| 3222 | root | % | t_girl | Query | 0 | NULL | select a.* from article as a inner join (select a.aid from article as a inner join (select cid from |
| 3223 | root | % | t_girl | Query | 0 | NULL | select a.* from article as a inner join (select a.aid from article as a inner join (select cid from |
| 3224 | root | % | t_girl | Query | 0 | removing tmp table | select a.* from article as a inner join (select a.aid from article as a inner join (select cid from |
| 3225 | root | % | t_girl | Query | 0 | NULL | select a.* from article as a inner join (select a.aid from article as a inner join (select cid from |
| 3226 | root | % | t_girl | Query | 0 | NULL | select a.* from article as a inner join (select a.aid from article as a inner join (select cid from |
+------+------+--------------------+--------------------+---------+-------+--------------------+------------------------------------------------------------------------------------------------------+
55 rows in set (0.00 sec)
上面的测试语句其实也可以这样写
小总结一下。
mysqlslap对于模拟多个用户同时对MySQL发起“进攻”提供了方便。同时详细的提供了“高负荷攻击MySQL”的详细数据报告。
而且如果你想对于多个引擎的性能。这个工具再好不过了。
现在看一下这个压力测试工具mysqlslap.
关于他的选项手册上以及--help介绍的很详细。
我解释一下一些常用的选项。
这里要注意的几个选项：
--concurrency代表并发数量，多个可以用逗号隔开，当然你也可以用自己的分隔符隔开，这个时候要用到--delimiter开关。
--engines代表要测试的引擎，可以有多个，用分隔符隔开。
--iterations代表要运行这些测试多少次。
--auto-generate-sql 代表用系统自己生成的SQL脚本来测试。
--auto-generate-sql-load-type 代表要测试的是读还是写还是两者混合的（read,write,update,mixed）
--number-of-queries 代表总共要运行多少次查询。每个客户运行的查询数量可以用查询总数/并发数来计算。比如倒数第二个结果2=200/100。
--debug-info 代表要额外输出CPU以及内存的相关信息。
--number-int-cols 代表示例表中的INTEGER类型的属性有几个。
--number-char-cols 意思同上。
--create-schema 代表自己定义的模式（在MySQL中也就是库）。
--query 代表自己的SQL脚本。
--only-print 如果只想打印看看SQL语句是什么，可以用这个选项。
现在来看一些我测试的例子。
1、用自带的SQL脚本来测试。
MySQL版本为5.1.23
[root@localhost ~]# mysqlslap --defaults-file=/usr/local/mysql-maria/my.cnf --concurrency=50,100,200 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=myisam,innodb --number-of-queries=200 --debug-info -uroot -p1 -S/tmp/mysql_3310.sockBenchmark
Running for engine myisam
Average number of seconds to run all queries: 0.063 seconds
Minimum number of seconds to run all queries: 0.063 seconds
Maximum number of seconds to run all queries: 0.063 seconds
Number of clients running queries: 50
Average number of queries per client: 4
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.070 seconds
Minimum number of seconds to run all queries: 0.070 seconds
Maximum number of seconds to run all queries: 0.070 seconds
Number of clients running queries: 100
Average number of queries per client: 2
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.092 seconds
Minimum number of seconds to run all queries: 0.092 seconds
Maximum number of seconds to run all queries: 0.092 seconds
Number of clients running queries: 200
Average number of queries per client: 1
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.115 seconds
Minimum number of seconds to run all queries: 0.115 seconds
Maximum number of seconds to run all queries: 0.115 seconds
Number of clients running queries: 50
Average number of queries per client: 4
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.134 seconds
Minimum number of seconds to run all queries: 0.134 seconds
Maximum number of seconds to run all queries: 0.134 seconds
Number of clients running queries: 100
Average number of queries per client: 2
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.192 seconds
Minimum number of seconds to run all queries: 0.192 seconds
Maximum number of seconds to run all queries: 0.192 seconds
Number of clients running queries: 200
Average number of queries per client: 1
User time 0.06, System time 0.15
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 5803, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 8173, Involuntary context switches 528
我来解释一下结果的含义。
拿每个引擎最后一个Benchmark示例。
对于INNODB引擎，200个客户端同时运行这些SQL语句平均要花0.192秒。相应的MYISAM为0.092秒。
2、用我们自己定义的SQL 脚本来测试。
这些数据在另外一个MySQL实例上。版本为5.0.45
先看一下这两个表的相关数据。
1）、总记录数。
mysql> select table_rows as rows from information_schema.tables where table_schema='t_girl' and table_name='article';
+--------+
| rows |
+--------+
| 296693 |
+--------+
1 row in set (0.01 sec)
mysql> select table_rows as rows from information_schema.tables where table_schema='t_girl' and table_name='category';
+------+
| rows |
+------+
| 113 |
+------+
1 row in set (0.00 sec)
2）、总列数。
mysql> select count(*) as column_total from information_schema.columns where table_schema = 't_girl' and table_name = 'article';
+--------------+
| column_total |
+--------------+
| 32 |
+--------------+
1 row in set (0.01 sec)
mysql> select count(*) as column_total from information_schema.columns where table_schema = 't_girl' and table_name = 'category';
+--------------+
| column_total |
+--------------+
| 9 |
+--------------+
1 row in set (0.01 sec)
3）、调用的存储过程
DELIMITER $$
DROP PROCEDURE IF EXISTS `t_girl`.`sp_get_article`$$
CREATE DEFINER=`root`@`%` PROCEDURE `sp_get_article`(IN f_category_id int,
IN f_page_size int, IN f_page_no int
)
BEGIN
set @stmt = 'select a.* from article as a inner join ';
set @stmt = concat(@stmt,'(select a.aid from article as a ');
if f_category_id != 0 then
set @stmt = concat(@stmt,' inner join (select cid from category where cid = ',f_category_id,' or parent_id = ',f_category_id,') as b on a.category_id = b.cid');
end if;
if f_page_size >0 && f_page_no > 0 then
set @stmt = concat(@stmt,' limit ',(f_page_no-1)*f_page_size,',',f_page_size);
end if;
set @stmt = concat(@stmt,') as b on (a.aid = b.aid)');
prepare s1 from @stmt;
execute s1;
deallocate prepare s1;
set @stmt = NULL;
END$$
DELIMITER ;4）、我们用mysqlslap来测试
以下得这个例子代表用mysqlslap来测试并发数为25,50,100的调用存储过程，并且总共调用5000次。
[root@localhost ~]# mysqlslap --defaults-file=/usr/local/mysql-maria/my.cnf --concurrency=25,50,100 --iterations=1 --query='call t_girl.sp_get_article(2,10,1);' --number-of-queries=5000 --debug-info -uroot -p -S/tmp/mysql50.sockEnter password:
Benchmark
Average number of seconds to run all queries: 3.507 seconds
Minimum number of seconds to run all queries: 3.507 seconds
Maximum number of seconds to run all queries: 3.507 seconds
Number of clients running queries: 25
Average number of queries per client: 200
平均每个并发运行200个查询用了3.507秒。
Benchmark
Average number of seconds to run all queries: 3.742 seconds
Minimum number of seconds to run all queries: 3.742 seconds
Maximum number of seconds to run all queries: 3.742 seconds
Number of clients running queries: 50
Average number of queries per client: 100
Benchmark
Average number of seconds to run all queries: 3.697 seconds
Minimum number of seconds to run all queries: 3.697 seconds
Maximum number of seconds to run all queries: 3.697 seconds
Number of clients running queries: 100
Average number of queries per client: 50
User time 0.87, System time 0.33
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 1877, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 27218, Involuntary context switches 3100
看一下SHOW PROCESSLIST 结果
mysql> show processlist;
+------+------+--------------------+--------------------+---------+-------+--------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+--------------------+--------------------+---------+-------+--------------------+------------------------------------------------------------------------------------------------------+
…………
| 3177 | root | % | t_girl | Query | 0 | NULL | select a.* from article as a inner join (select a.aid from article as a inner join (select cid from |
| 3178 | root | % | t_girl | Query | 0 | NULL | select a.* from article as a inner join (select a.aid from article as a inner join (select cid from |
| 3179 | root | % | t_girl | Query | 0 | NULL | select a.* from article as a inner join (select a.aid from article as a inner join (select cid from |
| 3181 | root | % | t_girl | Query | 0 | NULL | select a.* from article as a inner join (select a.aid from article as a inner join (select cid from |
| 3180 | root | % | t_girl | Query | 0 | NULL | select a.* from article as a inner join (select a.aid from article as a inner join (select cid from |
| 3182 | root | % | t_girl | Query | 0 | NULL | select a.* from article as a inner join (select a.aid from article as a inner join (select cid from |
| 3183 | root | % | t_girl | Query | 0 | NULL | select a.* from article as a inner join (select a.aid from article as a inner join (select cid from |
| 3187 | root | % | t_girl | Query | 0 | removing tmp table | select a.* from article as a inner join (select a.aid from article as a inner join (select cid from |
| 3186 | root | % | t_girl | Query | 0 | NULL | select a.* from article as a inner join (select a.aid from article as a inner join (select cid from |
| 3194 | root | % | t_girl | Query | 0 | NULL | select a.* from article as a inner join (select a.aid from article as a inner join (select cid from |
| 3203 | root | % | t_girl | Query | 0 | NULL | deallocate prepare s1 |
…………
| 3221 | root | % | t_girl | Query | 0 | NULL | select a.* from article as a inner join (select a.aid from article as a inner join (select cid from |
| 3222 | root | % | t_girl | Query | 0 | NULL | select a.* from article as a inner join (select a.aid from article as a inner join (select cid from |
| 3223 | root | % | t_girl | Query | 0 | NULL | select a.* from article as a inner join (select a.aid from article as a inner join (select cid from |
| 3224 | root | % | t_girl | Query | 0 | removing tmp table | select a.* from article as a inner join (select a.aid from article as a inner join (select cid from |
| 3225 | root | % | t_girl | Query | 0 | NULL | select a.* from article as a inner join (select a.aid from article as a inner join (select cid from |
| 3226 | root | % | t_girl | Query | 0 | NULL | select a.* from article as a inner join (select a.aid from article as a inner join (select cid from |
+------+------+--------------------+--------------------+---------+-------+--------------------+------------------------------------------------------------------------------------------------------+
55 rows in set (0.00 sec)
上面的测试语句其实也可以这样写
[root@localhost ~]# mysqlslap --defaults-file=/usr/local/mysql-maria/my.cnf --concurrency=25,50,100 --iterations=1 --create-schema='t_girl' --query='call sp_get_article(2,10,1);' --number-of-queries=5000 --debug-info -uroot -p -S/tmp/mysql50.sock
小总结一下。
mysqlslap对于模拟多个用户同时对MySQL发起“进攻”提供了方便。同时详细的提供了“高负荷攻击MySQL”的详细数据报告。
而且如果你想对于多个引擎的性能。这个工具再好不过了。
11 六月, 2008 05:38
What is MySQL Proxy?
MySQL Proxy is a simple program that sits between your client and MySQL server(s) that can monitor, analyze or transform their communication. Its flexibility allows for unlimited uses; common ones include: load balancing; failover; query analysis; query filtering and modification; and many more.
Commandline Syntax
To use the MySQL Proxy:[code]$ mysql-proxy --help-all
Usage:
mysql-proxy [OPTION...] - MySQL Proxy
Help Options:
-?, --help Show help options
--help-all Show all help options
--help-admin Show options for the admin-module
--help-proxy Show options for the proxy-module
admin module
--admin-address=<host:port> listening address:port of internal admin-server (default: :4041)
proxy-module
--proxy-address=<host:port> listening address:port of the proxy-server (default: :4040)
--proxy-read-only-backend-addresses=<host:port> address:port of the remote slave-server (default: not set)
--proxy-backend-addresses=<host:port> address:port of the remote backend-servers (default: 127.0.0.1:3306)
--proxy-skip-profiling disables profiling of queries (default: enabled)
--proxy-fix-bug-25371 fix bug #25371 (mysqld > 5.1.12) for older libmysql versions
--proxy-lua-script=<file> filename of the lua script (default: not set)
--no-proxy Don't start proxy-server
Application Options:
-V, --version Show version
--daemon Start in daemon-mode
--pid-file=<file> PID file in case we are started as daemon[/code]
Connecting
As a simple test, just start it and try to connect to port 4040 with your mysql-client.
[code]$ mysql-proxy &
$ mysql --host=127.0.0.1 --port=4040 --user=... --password[/code]
* The MySQL Proxy will pass the connection through to port 3306 at 127.0.0.1
* IMPORTANT: The MySQL server should be 5.0.x or later. Testing has not been performed with Version 4.1 however feedback is welcome from the community.
Proxy Module
The proxy module is split into two parts:
* a core written in C
* a lua interface
The core handles the basics of packet forwarding tries to be fast and have low latency as possible and handles more than 1000 connections in parallel. Part of the core are:
* config-file handling
* mysql-protocol encoding
* socket handling
* load balancing
* fail over
[code]$ mysql-proxy --help-proxy
Usage:
mysql-proxy [OPTION...] - MySQL Proxy[/code]
[code]proxy-module
--proxy-address=<ip:port> listening address:port of the proxy-server (default: :4040)
--proxy-read-only-address=<ip:port> listening address:port of the proxy-server for read-only connection (default: :4042)
--proxy-backend-addresses=<ip:port> address:port of the remote backend-servers (default: not set)
--proxy-profiling enable profiling of queries
--proxy-fix-bug-25371 fix bug #25371 (mysqld > 5.1.12) for older libmysql versions
--proxy-lua-script=<file> filename of the lua script (default: not set)[/code]
The --proxy-address is the port where mysql connects to get forwarded to one of the backends.
The backends are announced with --proxy-backend-addresses which defaults to 127.0.0.1:3306. You can specify this option several times to add more backends.
Admin Server
The admin-server is the most basic implementation of the MySQL server protocol and can respond to some basic queries. It implements:
* socket handling
* the life-cycle of a connection
* mysql wire-protocol
* len-encoding of some fields
* field-types
* result-sets
While the design is based on the ideas from lighttpd in the way that it is using non-blocking network-io the network-protocol is based on the information available in the internals document from dev.mysql.com
The admin-servers implements 2 basic queries which are issued by the mysql command-line client:
[code]select @@version_comment LIMIT 1;
select USER();[/code]
Using the admin server you can implement the functionality in a way that every mysql client (php, jdbc, odbc, perl, ...) can execute them.
We use it to export the current config and to track the open connections:
[code]> select * from proxy_connections;
+------+--------+-------+-------+
| id | type | state | db |
+------+--------+-------+-------+
| 2 | proxy | 8 | world |
| 3 | server | 8 | |
+------+--------+-------+-------+[/code]
and the config:
[code]> select * from proxy_config;
+---------------------------------+----------------+
| option | value |
+---------------------------------+----------------+
| admin.address | :4041 |
| proxy.address | :4040 |
| proxy.backend_addresses[0] | 127.0.0.1:3306 |
| proxy.backend_addresses[1] | 127.0.0.1:3307 |
| proxy.fix_bug_25371 | 0 |
| repclient.master_address | |
+---------------------------------+----------------+[/code]
Load Balancing & Failover
How about some load-balancing and fail-over?
[code]$ mysql-proxy
--proxy-backend-addresses=10.0.1.2:3306
--proxy-backend-addresses=10.0.1.3:3306 &[/code]
Run your tests, shut down one of the backends and see how the MySQL Proxy sends all traffic to the one which is still alive.
Scripting
MySQL Proxy includes lua script support. Lua is a simple and fast embeddable script language. Tutorial scripts are posted as snippets here; we encourage you to contribute your own! Add new snippets here, and please tag them with mysqlproxy.
We use a state-machine which maps the basic stages of the MySQL protocol:
[img]http://forge.mysql.com/w/images/6/6e/Mysql-proto-state.png[/img]
With the lua scripts you can hook into 3 stages right now:
* connect_server
* read_query
* read_query_result
If you want to write a load balancer you can hook into connect_server which is called before we connect to a backend server. The load-balancer can pick a backend from a list of backends.
read_query is the stage where we read the query from the client before we send it to the server. In this stage you can decide if you want to pass the query on as is, rewrite it, inject more queries or respond directly to the client without forwarding the packet to the server.
For example you can dump all the data which is transfered between client and server (after the authentication stage):
[code] (sqf) taking 127.0.0.1:3306, clients: 0
.--- mysql result packet
| query.len = 13
| query.packet = 03 73 68 6f 77 20 65 6e 67 69 6e 65 73
| .--- query
| | command = COM_QUERY
| | query = "show engines"
| '---
|
| result.len = 1
| result.packet = 06
| .---
| | command = COM_QUERY
| | num-cols = 6
| | field[0] = { type = 253, name = Engine }
| | field[1] = { type = 253, name = Support }
| | field[2] = { type = 253, name = Comment }
| | field[3] = { type = 253, name = Transactions }
| | field[4] = { type = 253, name = XA }
| | field[5] = { type = 253, name = Savepoints }
| | row[0] = { ndbcluster, DISABLED, Clustered, fault-tolerant tables, YES, NO, NO }
| | row[1] = { MRG_MYISAM, YES, Collection of identical MyISAM tables, NO, NO, NO }
| | row[2] = { BLACKHOLE, YES, /dev/null storage engine (anything you write to it disappears), NO, NO, NO }
| | row[3] = { CSV, YES, CSV storage engine, NO, NO, NO }
| | row[4] = { MEMORY, YES, Hash based, stored in memory, useful for temporary tables, NO, NO, NO }
| | row[5] = { FEDERATED, YES, Federated MySQL storage engine, YES, NO, NO }
| | row[6] = { ARCHIVE, YES, Archive storage engine, NO, NO, NO }
| | row[7] = { InnoDB, YES, Supports transactions, row-level locking, and foreign keys, YES, YES, YES }
| | row[8] = { MyISAM, DEFAULT, Default engine as of MySQL 3.23 with great performance, NO, NO, NO }
| '---
'---[/code]
MySQL Proxy is a simple program that sits between your client and MySQL server(s) that can monitor, analyze or transform their communication. Its flexibility allows for unlimited uses; common ones include: load balancing; failover; query analysis; query filtering and modification; and many more.
Commandline Syntax
To use the MySQL Proxy:[code]$ mysql-proxy --help-all
Usage:
mysql-proxy [OPTION...] - MySQL Proxy
Help Options:
-?, --help Show help options
--help-all Show all help options
--help-admin Show options for the admin-module
--help-proxy Show options for the proxy-module
admin module
--admin-address=<host:port> listening address:port of internal admin-server (default: :4041)
proxy-module
--proxy-address=<host:port> listening address:port of the proxy-server (default: :4040)
--proxy-read-only-backend-addresses=<host:port> address:port of the remote slave-server (default: not set)
--proxy-backend-addresses=<host:port> address:port of the remote backend-servers (default: 127.0.0.1:3306)
--proxy-skip-profiling disables profiling of queries (default: enabled)
--proxy-fix-bug-25371 fix bug #25371 (mysqld > 5.1.12) for older libmysql versions
--proxy-lua-script=<file> filename of the lua script (default: not set)
--no-proxy Don't start proxy-server
Application Options:
-V, --version Show version
--daemon Start in daemon-mode
--pid-file=<file> PID file in case we are started as daemon[/code]
Connecting
As a simple test, just start it and try to connect to port 4040 with your mysql-client.
[code]$ mysql-proxy &
$ mysql --host=127.0.0.1 --port=4040 --user=... --password[/code]
* The MySQL Proxy will pass the connection through to port 3306 at 127.0.0.1
* IMPORTANT: The MySQL server should be 5.0.x or later. Testing has not been performed with Version 4.1 however feedback is welcome from the community.
Proxy Module
The proxy module is split into two parts:
* a core written in C
* a lua interface
The core handles the basics of packet forwarding tries to be fast and have low latency as possible and handles more than 1000 connections in parallel. Part of the core are:
* config-file handling
* mysql-protocol encoding
* socket handling
* load balancing
* fail over
[code]$ mysql-proxy --help-proxy
Usage:
mysql-proxy [OPTION...] - MySQL Proxy[/code]
[code]proxy-module
--proxy-address=<ip:port> listening address:port of the proxy-server (default: :4040)
--proxy-read-only-address=<ip:port> listening address:port of the proxy-server for read-only connection (default: :4042)
--proxy-backend-addresses=<ip:port> address:port of the remote backend-servers (default: not set)
--proxy-profiling enable profiling of queries
--proxy-fix-bug-25371 fix bug #25371 (mysqld > 5.1.12) for older libmysql versions
--proxy-lua-script=<file> filename of the lua script (default: not set)[/code]
The --proxy-address is the port where mysql connects to get forwarded to one of the backends.
The backends are announced with --proxy-backend-addresses which defaults to 127.0.0.1:3306. You can specify this option several times to add more backends.
Admin Server
The admin-server is the most basic implementation of the MySQL server protocol and can respond to some basic queries. It implements:
* socket handling
* the life-cycle of a connection
* mysql wire-protocol
* len-encoding of some fields
* field-types
* result-sets
While the design is based on the ideas from lighttpd in the way that it is using non-blocking network-io the network-protocol is based on the information available in the internals document from dev.mysql.com
The admin-servers implements 2 basic queries which are issued by the mysql command-line client:
[code]select @@version_comment LIMIT 1;
select USER();[/code]
Using the admin server you can implement the functionality in a way that every mysql client (php, jdbc, odbc, perl, ...) can execute them.
We use it to export the current config and to track the open connections:
[code]> select * from proxy_connections;
+------+--------+-------+-------+
| id | type | state | db |
+------+--------+-------+-------+
| 2 | proxy | 8 | world |
| 3 | server | 8 | |
+------+--------+-------+-------+[/code]
and the config:
[code]> select * from proxy_config;
+---------------------------------+----------------+
| option | value |
+---------------------------------+----------------+
| admin.address | :4041 |
| proxy.address | :4040 |
| proxy.backend_addresses[0] | 127.0.0.1:3306 |
| proxy.backend_addresses[1] | 127.0.0.1:3307 |
| proxy.fix_bug_25371 | 0 |
| repclient.master_address | |
+---------------------------------+----------------+[/code]
Load Balancing & Failover
How about some load-balancing and fail-over?
[code]$ mysql-proxy
--proxy-backend-addresses=10.0.1.2:3306
--proxy-backend-addresses=10.0.1.3:3306 &[/code]
Run your tests, shut down one of the backends and see how the MySQL Proxy sends all traffic to the one which is still alive.
Scripting
MySQL Proxy includes lua script support. Lua is a simple and fast embeddable script language. Tutorial scripts are posted as snippets here; we encourage you to contribute your own! Add new snippets here, and please tag them with mysqlproxy.
We use a state-machine which maps the basic stages of the MySQL protocol:
[img]http://forge.mysql.com/w/images/6/6e/Mysql-proto-state.png[/img]
With the lua scripts you can hook into 3 stages right now:
* connect_server
* read_query
* read_query_result
If you want to write a load balancer you can hook into connect_server which is called before we connect to a backend server. The load-balancer can pick a backend from a list of backends.
read_query is the stage where we read the query from the client before we send it to the server. In this stage you can decide if you want to pass the query on as is, rewrite it, inject more queries or respond directly to the client without forwarding the packet to the server.
For example you can dump all the data which is transfered between client and server (after the authentication stage):
[code] (sqf) taking 127.0.0.1:3306, clients: 0
.--- mysql result packet
| query.len = 13
| query.packet = 03 73 68 6f 77 20 65 6e 67 69 6e 65 73
| .--- query
| | command = COM_QUERY
| | query = "show engines"
| '---
|
| result.len = 1
| result.packet = 06
| .---
| | command = COM_QUERY
| | num-cols = 6
| | field[0] = { type = 253, name = Engine }
| | field[1] = { type = 253, name = Support }
| | field[2] = { type = 253, name = Comment }
| | field[3] = { type = 253, name = Transactions }
| | field[4] = { type = 253, name = XA }
| | field[5] = { type = 253, name = Savepoints }
| | row[0] = { ndbcluster, DISABLED, Clustered, fault-tolerant tables, YES, NO, NO }
| | row[1] = { MRG_MYISAM, YES, Collection of identical MyISAM tables, NO, NO, NO }
| | row[2] = { BLACKHOLE, YES, /dev/null storage engine (anything you write to it disappears), NO, NO, NO }
| | row[3] = { CSV, YES, CSV storage engine, NO, NO, NO }
| | row[4] = { MEMORY, YES, Hash based, stored in memory, useful for temporary tables, NO, NO, NO }
| | row[5] = { FEDERATED, YES, Federated MySQL storage engine, YES, NO, NO }
| | row[6] = { ARCHIVE, YES, Archive storage engine, NO, NO, NO }
| | row[7] = { InnoDB, YES, Supports transactions, row-level locking, and foreign keys, YES, YES, YES }
| | row[8] = { MyISAM, DEFAULT, Default engine as of MySQL 3.23 with great performance, NO, NO, NO }
| '---
'---[/code]
11 六月, 2008 05:36
请修改这里：注意，本版本尚未经过严格测试与实际检验！！发出来只为提供一种思路，使用时请根据实际情况修改，风险自担。
-- 将分到另一个库中的表明列在这里：
local remote_tables = {"table1", "table2"}
在启动的时候：
mysql-proxy
--proxy-backend-addresses=10.0.0.2:3306
--proxy-backend-addresses=10.0.0.3:3306
--proxy-lua-script=./mysql_proxy_store.lua
lua脚本：
-- mysql_proxy_store.lua
-- for mercury
-- author: Albert Lee
--
-- for billions samples store requirement, we split the samples & store table
--
--[[
Copyright (C) 2007 MySQL AB
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; version 2 of the License.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
--]]
---
-- a flexible statement based load balancer with connection pooling
--
-- * build a connection pool of min_idle_connections for each backend and
-- maintain its size
-- * reusing a server-side connection when it is idling
--
--- config
--
-- 将分到另一个库中的表明列在这里：
local remote_tables = {"table1", "table2"}
local min_idle_connections = 1
local max_idle_connections = 2
-- debug
local is_debug = true
--- end of config
---
-- read/write splitting sends all non-transactional SELECTs to the slaves
--
-- is_in_transaction tracks the state of the transactions
local is_in_transaction = 0
---
-- get a connection to a backend
--
-- as long as we don't have enough connections in the pool, create new connections
--
function connect_server()
-- make sure that we connect to each backend at least ones to
-- keep the connections to the servers alive
--
-- on read_query we can switch the backends again to another backend
if is_debug then
print()
print("[connect_server] ")
end
local least_idle_conns_ndx = 0
local least_idle_conns = 0
for i = 1, #proxy.backends do
local s = proxy.backends[i]
local pool = s.pool -- we don't have a username yet, try to find a connections which is idling
local cur_idle = pool.users[""].cur_idle_connections
if is_debug then
print(" [".. i .."].connected_clients = " .. s.connected_clients)
print(" [".. i .."].idling_connections = " .. cur_idle)
print(" [".. i .."].type = " .. s.type)
print(" [".. i .."].state = " .. s.state)
end
if s.state ~= proxy.BACKEND_STATE_DOWN then
-- try to connect to each backend once at least
if cur_idle == 0 then
proxy.connection.backend_ndx = i
if is_debug then
print(" [".. i .."] open new connection")
end
return
end
-- try to open at least min_idle_connections
if least_idle_conns_ndx == 0 or
( cur_idle < min_idle_connections and
cur_idle < least_idle_conns ) then
least_idle_conns_ndx = i
least_idle_conns = s.idling_connections
end
end
end
if least_idle_conns_ndx > 0 then
proxy.connection.backend_ndx = least_idle_conns_ndx
end
if proxy.connection.backend_ndx > 0 then
local s = proxy.backends[proxy.connection.backend_ndx]
local pool = s.pool -- we don't have a username yet, try to find a connections which is idling
local cur_idle = pool.users[""].cur_idle_connections
if cur_idle >= min_idle_connections then
-- we have 4 idling connections in the pool, that's good enough
if true or is_debug then
print(" using pooled connection from: " .. proxy.connection.backend_ndx)
end
return proxy.PROXY_IGNORE_RESULT
end
end
if is_debug then
print(" opening new connection on: " .. proxy.connection.backend_ndx)
end
-- open a new connection
end
---
-- put the successfully authed connection into the connection pool
--
-- @param auth the context information for the auth
--
-- auth.packet is the packet
function read_auth_result( auth )
if auth.packet:byte() == proxy.MYSQLD_PACKET_OK then
-- auth was fine, disconnect from the server
proxy.connection.backend_ndx = 0
elseif auth.packet:byte() == proxy.MYSQLD_PACKET_EOF then
-- we received either a
--
-- * MYSQLD_PACKET_ERR and the auth failed or
-- * MYSQLD_PACKET_EOF which means a OLD PASSWORD (4.0) was sent
print("(read_auth_result) ... not ok yet");
elseif auth.packet:byte() == proxy.MYSQLD_PACKET_ERR then
-- auth failed
end
end
---
-- read/write splitting
function read_query( packet )
if is_debug then
print("[read_query]")
print(" authed backend = " .. proxy.connection.backend_ndx)
print(" used db = " .. proxy.connection.client.default_db)
end
if packet:byte() == proxy.COM_QUIT then
-- don't send COM_QUIT to the backend. We manage the connection
-- in all aspects.
proxy.response = {
type = proxy.MYSQLD_PACKET_OK,
}
return proxy.PROXY_SEND_RESULT
end
if proxy.connection.backend_ndx == 0 then
-- we don't have a backend right now
--
-- let's pick a master as a good default
query = string.sub(packet, 2)
for i = 1, #remote_tables do
print (i, remote_tables[i])
if string.find(query, remote_tables[i]) then
proxy.connection.backend_ndx = 2
break
end
proxy.connection.backend_ndx = 1
end
-- if string.find(query, "auth_group") then
-- print("Use Another Database")
-- proxy.connection.backend_ndx = 2
-- else
-- print("Use local database")
-- proxy.connection.backend_ndx = 1
-- end
end
proxy.queries:append(1, packet)
return proxy.PROXY_SEND_QUERY
end
---
-- as long as we are in a transaction keep the connection
-- otherwise release it so another client can use it
function read_query_result( inj )
local res = assert(inj.resultset)
local flags = res.flags
if inj.id ~= 1 then
-- ignore the result of the USE
return proxy.PROXY_IGNORE_RESULT
end
is_in_transaction = flags.in_trans
if not is_in_transaction then
-- release the backend
proxy.connection.backend_ndx = 0
end
end
---
-- close the connections if we have enough connections in the pool
--
-- @return nil - close connection
-- IGNORE_RESULT - store connection in the pool
function disconnect_client()
if is_debug then
print("[disconnect_client]")
end
if proxy.connection.backend_ndx == 0 then
-- currently we don't have a server backend assigned
--
-- pick a server which has too many idling connections and close one
for i = 1, #proxy.backends do
local s = proxy.backends[i]
local pool = s.pool -- we don't have a username yet, try to find a connections which is idling
local cur_idle = pool.users[proxy.connection.client.username].cur_idle_connections
if s.state ~= proxy.BACKEND_STATE_DOWN and
cur_idle > max_idle_connections then
-- try to disconnect a backend
proxy.connection.backend_ndx = i
if is_debug then
print(" [".. i .."] closing connection, idling: " .. cur_idle)
end
return
end
end
end
end
11 六月, 2008 05:33
The trunk version of the MySQL Proxy 0.6.0 just learnt about changing backends within running connection. It is now up to lua-script to decide which backend shall be used to send requests too.
We wrote a complete tutorial which covers everything from:
- building and maintaining a connection pool with high and low water marks
- transparent authentication (no extra auth against the proxy)
- deciding on Query Level which backend to use
and implement a transparent read/write splitter which sends all non-transactional Queries to the slaves and the rest to the master.
As the splitting is in the hands of the lua-scripting level you can use the same to implement sharding or other rules to route traffic on statement level.
Connection Pooling
For R/W Splitting we need a connection pooling. We only switch to another backend if we already have a authenticated connection open to that backend.
The MySQL protocol first does a challenge-response handshake. When we enter the query/result stage it is too late to authenticate new connections. We have to make sure that we have enough open connections to operate nicely.
In the keepalive tutorial we spend quite some code on connection management. The whole connect_servers() function is only to create new connections for all pools.
- create one connection to each backend
- create new connections until we reach min-idle-connections
- if the two above conditions are met, use a connection from the pool
Let's take a glimpse at the code:
--- config
--
-- connection pool
local min_idle_connections = 4
local max_idle_connections = 8
---
-- get a connection to a backend
--
-- as long as we don't have enough connections in the pool, create new connections
--
function connect_server()
-- make sure that we connect to each backend at least ones to
-- keep the connections to the servers alive
--
-- on read_query we can switch the backends again to another backend
local least_idle_conns_ndx = 0
local least_idle_conns = 0
for i = 1, #proxy.servers do
local s = proxy.servers[i]
if s.state ~= proxy.BACKEND_STATE_DOWN then
-- try to connect to each backend once at least
if s.idling_connections == 0 then
proxy.connection.backend_ndx = i
return
end
-- try to open at least min_idle_connections
if least_idle_conns_ndx == 0 or
( s.idling_connections < min_idle_connections and
s.idling_connections < least_idle_conns ) then
least_idle_conns_ndx = i
least_idle_conns = s.idling_connections
end
end
end
if least_idle_conns_ndx > 0 then
proxy.connection.backend_ndx = least_idle_conns_ndx
end
if proxy.connection.backend_ndx > 0 and
proxy.servers[proxy.connection.backend_ndx].idling_connections >= min_idle_connections then
-- we have 4 idling connections in the pool, that's good enough
return proxy.PROXY_IGNORE_RESULT
end
-- open a new connection
end
The real trick is in
---
-- put the authed connection into the connection pool
function read_auth_result(packet)
-- disconnect from the server
proxy.connection.backend_ndx = 0
end
The proxy.connection.backend_ndx = 0 we disconnect us from the current backend (lua starts indexing at index 1, 0 is out of bounds). If a second connection comes in now it can use this authed connection too as it is in the pool, idling.
By setting proxy.connection.backend_ndx you control which backend is used to send your packets too. A backend is defined as a entry of the proxy.servers table. Each connection has (zero or) one backend. The backends all have a address, a type (RW or RO) and a state (UP or DOWN).
As we also might have to many open connections in the pool we close them on shutdown again if necessary:
---
-- close the connections if we have enough connections in the pool
--
-- @return nil - close connection
-- IGNORE_RESULT - store connection in the pool
function disconnect_client()
if proxy.connection.backend_ndx == 0 then
-- currently we don't have a server backend assigned
--
-- pick a server which has too many idling connections and close one
for i = 1, #proxy.servers do
local s = proxy.servers[i]
if s.state ~= proxy.BACKEND_STATE_DOWN and
s.idling_connections > max_idle_connections then
-- try to disconnect a backend
proxy.connection.backend_ndx = i
return
end
end
end
end
We only search for a backend which has to many open idling connections and use it before we enter the default behaviour of disconnect_client: shutdown the server connection. if proxy.connection.backend_ndx == 0 then is the "we don't have backend associated right now". We already saw this in read_auth_result.
Read/Write Splitting
That is our maintainance of the pool. connect_server() adds new auth'ed connections to the pool, disconnect_client() closes them again. The read/write splitting is part of the query/result cycle:
-- read/write splitting
function read_query( packet )
if packet:byte() == proxy.COM_QUIT then
-- don't send COM_QUIT to the backend. We manage the connection
-- in all aspects.
proxy.response = {
type = proxy.MYSQLD_PACKET_ERR,
errmsg = "ignored the COM_QUIT"
}
return proxy.PROXY_SEND_RESULT
end
-- as we switch between different connenctions we have to make sure that
-- we use always the same DB
if packet:byte() == proxy.COM_INIT_DB then
-- default_db is connection global
default_db = packet:sub(2)
end
if proxy.connection.backend_ndx == 0 then
-- we don't have a backend right now
--
-- let's pick a master as a good default
for i = 1, #proxy.servers do
local s = proxy.servers[i]
if s.idling_connections > 0 and
s.state ~= proxy.BACKEND_STATE_DOWN and
s.type == proxy.BACKEND_TYPE_RW then
proxy.connection.backend_ndx = i
break
end
end
end
if packet:byte() == proxy.COM_QUERY and default_db then
-- how can I know the db of the server connection ?
proxy.queries:append(2, string.char(proxy.COM_INIT_DB) .. default_db)
end
proxy.queries:append(1, packet)
Up to now it is only making sure that we behave nicely:
- don't forward
COM_QUITto the backend as he will close the connection on us - intercept the
COM_INIT_DBto know which DB the client wants to work on. If we switch to another backend we have to make sure the same DB is used.
The read/write splitting is now following a simple rule:
- send all non-transactional SELECTs to a slave
- everything else goes to the master
We are still in read_query()
-- read/write splitting
--
-- send all non-transactional SELECTs to a slave
if is_in_transaction == 0 and
packet:byte() == proxy.COM_QUERY and
packet:sub(2, 7) == "SELECT" then
local max_conns = -1
local max_conns_ndx = 0
for i = 1, #proxy.servers do
local s = proxy.servers[i]
-- pick a slave which has some idling connections
if s.type == proxy.BACKEND_TYPE_RO and
s.idling_connections > 0 then
if max_conns == -1 or
s.connected_clients < max_conns then
max_conns = s.connected_clients
max_conns_ndx = i
end
end
end
-- we found a slave which has a idling connection
if max_conns_ndx > 0 then
proxy.connection.backend_ndx = max_conns_ndx
end
else
-- send to master
end
return proxy.PROXY_SEND_QUERY
end
If we found a slave host which has a idling connection we pick it. If all slaves are busy or down, we just send the query to the master.
As soon as we don't need this connection anymore give it backend to the pool:
---
-- as long as we are in a transaction keep the connection
-- otherwise release it so another client can use it
function read_query_result( inj )
local res = assert(inj.resultset)
local flags = res.flags
if inj.id ~= 1 then
-- ignore the result of the USE <default_db>
return proxy.PROXY_IGNORE_RESULT
end
is_in_transaction = flags.in_trans
if is_in_transaction == 0 then
-- release the backend
proxy.connection.backend_ndx = 0
end
end
The MySQL Protocol is nice and offers us a in-transaction-flag. This operates on the state of the transaction and works across all engines. If you want to make sure that several statements go to the same backend, open a transaction with BEGIN. No matter which storage engine you use.
Possible extensions
While we are here in this section of the code think about another use case:
- if the master is down, ban all writing queries and only allow reading selects against the slaves.
It keeps your site up and running even if your master is gone. You only have to handle errors on write-statements and transactions.
Known Problems
We might have a race-condition that idling connection closes before we can use it. In that case we are in trouble right now and will close the connection to the client.
We have to add queuing of connections and awaking them up when the connection becomes available again to handle this later.
Next Steps
Testing, testing, testing.
$ mysql-proxy
--proxy-backend-addresses=10.0.0.1:3306
--proxy-read-only-backend-addresses=10.0.0.10:3306
--proxy-read-only-backend-addresses=10.0.0.12:3306
--proxy-lua-script=examples/tutorial-keepalive.lua
The above code works for my tests, but I don't have any real load. Nor can I create all the error-cases you have in your real-life setups. Please send all your comments, concerns and ideas to the MySQL Proxy forum.
Another upcoming step is externalizing all the load-balancer code and move it into modules to make the code easier to understand and reuseable.
11 六月, 2008 05:30
今天看到了这么一个新项目–MySQL Proxy， 貌似最近比较热门。简单来说就是一个从MySQL Client到Server的一个代理。可能有人认为MySQL这种连接方式不应该会用到代理，因为一般来说都喜欢把MySQL Server放到后端，用内网圈起来，这样一方面保证安全，另一方面用Local Ethernet来避免不稳定因素较多的Internet连接，因此基本没有代理这个角色出现的机会。然而，假如这个代理不仅仅是一个代理，而是一个能 “懂得”连接双方传送内容，并有可能会对双方内容加以控制甚至干涉的一个角色呢？还有没有人觉得他没用？联想一下Squid之于HTTP Client和HTTP Server所能起到的作用，是不是能想到更多了呢？
是的，MySQL Proxy就是这么一个玩艺儿。代理仅仅是其最不值一提的功能，让人激动的功能都是建立在代理这个前提之上，能实现的更好玩的东西，例如以下：
Query Interception
Query Filtering
Query Rewriting
Macro Expansion
可能对于没接触过这个东西的人，这几个概念还不是十分直观，那么这样，在脑子里描绘这样一幅情景：
1，原本为Cilent-Server直连这种拓扑，现在引入MySQL Proxy，变成Client-Proxy-Server。
2，原本为了实现高容载能力，对于Server采取了多台并存，Master/Slave甚至是Master/Master等方式的复制集群，配置管理都比较复杂。而引入了Proxy以后，中间存在了这样一个Store-Forward的proxy环节。
3，在这个环节，我们可以通过一种脚本语言来控制这个proxy的行为，例如对于Client进来的查询依照某种条件过滤，甚至依照某种条件改写，再导向后端的Server。
4，凭借自己实现的filtering或rewriting，我们可以实现很多目的，例如Failover，例如Load balance。或者更无聊些我们可以对Client进来的带有弱智语法错误的SQL语句进行修正。
有了这个东西，几乎是想到什么就能完成什么，这可是大大得扩展了MySQL应用的多样性，可以用它来实现一些高端商业数据库的复杂的企业化功能。相信喜欢MySQL的架构设计师们一定会喜欢这个玩艺儿。
这里有篇Getting Start，是很好的入门教材，对于这套软件，定要保持关注。
09 六月, 2008 04:42
自从毕业出来工作，每年的生日都约了许多同事一起吃饭，喝酒。
唯有今年，在家和家人一起过，只叫了阿进过来。 自己亲自动手，炒了点菜，备了点酒。很平淡很平淡的一个生日。
04 六月, 2008 02:03
PHP5 with Oracle client on FreeBSD tutorial(转)
Posted by 16hot under [ FreeBSD ][ (0) 评论 ] | [ (0) 引用 ]
This post is intented to help those trying to install PHP with Oracle support on FreeBSD. I’ll just note that this setup was tested and implemented in FreeBSD 6.2-STABLE. I also assume that your ports location is /usr/ports.
1. Install necessary FreeBSD ports
- Oracle instant client: /usr/ports/databases/linux-oracle-instantclient-basic/
You’ll need to download a Linux version of instant client from http://www.oracle.com/technology/tech/oci/instantclient/index.html and put it in /usr/ports/distfiles/oracle. The exact version will depend on the freshness of your port revision. At the moment of writing it is “instantclient-basic-linux32-10.2.0.2-20060331.zip”. - Oracle instant client SDK: /usr/ports/databases/linux-oracle-instantclient-sdk/
Another file for download from Oracle (location and destination are the same like in step 1). File name at the moment of writing is “instantclient-sdk-linux32-10.2.0.2-20060331.zip”. - Oracle8-client: /usr/ports/databases/oracle8-client/
Unofficial port of Linux version Oracle client. After doing “make install” go to /usr/local/oracle8-client/freebsd. Execute redefine-syms.sh, then - make, make patch and make install. If make fails with redefinition of gethostbyname_r function error, open oci8stub.c for editing and delete the declaration of this function in this file. Now it should compile correctly. After doing make install, symlink /lib/liboci8stub.a to /usr/local/oracle8-client/lib.
2. Environment setup
- (optional) Set linker options. If you’re using bash, type: export LDFLAGS=”-lcompat -lpthread”
- Set ORACLE_HOME location. If you’re using bash, type: export ORACLE_HOME=”/usr/local/oracle8-client”
3. Install PHP5
- Install PHP5 base from /usr/ports/lang/php5 (make && make install)
- Install PHP5 extensions from /opt/ports/lang/php5-extensions (make - for typical ncurses menu - simple but very convenient) NOTE: you won’t find OCI8 or other database extensions here - they are located in the other location
- Install PHP5-OCI8 extension from /usr/ports/databases/php5-oci8 (you’ll find all other available PHP5 database extensions in /usr/ports/databases)
4. Tips
- It is mandatory that ORACLE_HOME environment variable is set to /usr/local/oracle8-client during startup of Apache (or any other web server of your choice).
- If you want to use tnsnames.ora then put it into /usr/local/oracle8-client/network/admin. Adjust necessary settings in sqlnet.ora as well.
- In some cases setting TNS_ADMIN environment variable to /usr/local/oracle8-client/network/admin is necessary.
Feel free to comment if you encounter any problems with this tutorial.
02 六月, 2008 05:34
今天去剪头发，跟理发师说尽可能剪短一些。结果剪得太短了。
以前总是在尝试，如果这个地方剪的不好，就换个地方。这样总是不断的在换，不断的尝试，也就是不断的当小白鼠。
这次学乖了。发现剪得不好，就给理发师说，我希望的是什么样子的。请他下次为我剪头发的时候，注意别剪得太短了。
理发师也很爽快的答应了。
生活中就这样，是需要不断的尝试，但是更关键的是需要及时的沟通，交换意见。给别人犯错的机会，也是给自己减少当小白鼠的机会。
现在在带团队的过程中也是这样。同样要给下属尝试，犯错的机会。并且要及时交流、纠正，让他们不要重复范同样的错误。
