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，是很好的入门教材，对于这套软件，定要保持关注。
21 三月, 2008 19:11
转自cu:http://linux.chinaunix.net/docs/2007-04-02/4101.shtml
使用MySQL，安全问题不能不注意。以下是MySQL提示的23个注意事项：
使用MySQL，安全问题不能不注意。以下是MySQL提示的23个注意事项：
1.如果客户端和服务器端的连接需要跨越并通过不可信任的网络，那么就需要使用SSH隧道来加密该连接的通信。
2.用set password语句来修改用户的密码，三个步骤，先“mysql -u root”登陆数据库系统，然后“mysql> update mysql.user set password=password('newpwd')”，最后执行“flush privileges”就可以了。
3.需要提防的攻击有，防偷听、篡改、回放、拒绝服务等，不涉及可用性和容错方面。对所有的连接、查询、其他操作使用基于ACL即访问控制列表的安全措施来完成。也有一些对SSL连接的支持。
4.除了root用户外的其他任何用户不允许访问mysql主数据库中的user表;
加密后存放在user表中的加密后的用户密码一旦泄露，其他人可以随意用该用户名/密码相应的数据库;
5.用grant和revoke语句来进行用户访问控制的工作;
6.不使用明文密码，而是使用md5()和sha1()等单向的哈系函数来设置密码;
7.不选用字典中的字来做密码;
8.采用防火墙来去掉50%的外部危险，让数据库系统躲在防火墙后面工作，或放置在DMZ区域中;
9.从因特网上用nmap来扫描3306端口，也可用telnet server_host 3306的方法测试，不能允许从非信任网络中访问数据库服务器的3306号TCP端口，因此需要在防火墙或路由器上做设定;
10.为了防止被恶意传入非法参数，例如where ID=234，别人却输入where ID=234 OR 1=1导致全部显示，所以在web的表单中使用''或""来用字符串，在动态URL中加入%22代表双引号、%23代表井号、%27代表单引号;传递未检 查过的值给mysql数据库是非常危险的;
11.在传递数据给mysql时检查一下大小;
12.应用程序需要连接到数据库应该使用一般的用户帐号，只开放少数必要的权限给该用户;
13.在各编程接口(C C++ PHP Perl Java JDBC等)中使用特定‘逃脱字符’函数;
在因特网上使用mysql数据库时一定少用传输明文的数据，而用SSL和SSH的加密方式数据来传输;
14.学会使用tcpdump和strings工具来查看传输数据的安全性，例如tcpdump -l -i eth0 -w -src or dst port 3306 strings。以普通用户来启动mysql数据库服务;
15.不使用到表的联结符号，选用的参数 --skip-symbolic-links;
16.确信在mysql目录中只有启动数据库服务的用户才可以对文件有读和写的权限;
17.不许将process或super权限付给非管理用户，该mysqladmin processlist可以列举出当前执行的查询文本;super权限可用于切断客户端连接、改变服务器运行参数状态、控制拷贝复制数据库的服务器;
18.file权限不付给管理员以外的用户，防止出现load data '/etc/passwd'到表中再用select 显示出来的问题;
19.如果不相信DNS服务公司的服务，可以在主机名称允许表中只设置IP数字地址;
20.使用max_user_connections变量来使mysqld服务进程，对一个指定帐户限定连接数;
21.grant语句也支持资源控制选项;
22.启动mysqld服务进程的安全选项开关，--local-infile=0或1 若是0则客户端程序就无法使用local load data了，赋权的一个例子grant insert(user) on mysql.user to 'user_name'@'host_name';若使用--skip-grant-tables系统将对任何用户的访问不做任何访问控制，但可以用 mysqladmin flush-privileges或mysqladmin reload来开启访问控制;默认情况是show databases语句对所有用户开放，可以用--skip-show-databases来关闭掉。
23.碰到Error 1045(28000) Access Denied for user 'root'@'localhost' (Using password:NO)错误时，你需要重新设置密码，具体方法是:先用--skip-grant-tables参数启动mysqld，然后执行 mysql -u root mysql,mysql>update user set password=password('newpassword') where user='root';mysql>Flush privileges;，最后重新启动mysql就可以了。
14 四月, 2007 06:46
花了半个多月的时间，终于找到一间合适的房间了。
说是房间，当然是只租一套房子的其中一个房间，也就是单间吧。今天交了订金了，要到5月5日才能入住，现在里面有人住着。
在北京找房子还真不容易，要么太贵，要么太远。有朋友说，在北京找房子比找工作还难，呵呵。
14 四月, 2007 06:46
花了半个多月的时间，终于找到一间合适的房间了。
说是房间，当然是只租一套房子的其中一个房间，也就是单间吧。今天交了订金了，要到5月5日才能入住，现在里面有人住着。
在北京找房子还真不容易，要么太贵，要么太远。有朋友说，在北京找房子比找工作还难，呵呵。
14 四月, 2007 06:46
花了半个多月的时间，终于找到一间合适的房间了。
说是房间，当然是只租一套房子的其中一个房间，也就是单间吧。今天交了订金了，要到5月5日才能入住，现在里面有人住着。
在北京找房子还真不容易，要么太贵，要么太远。有朋友说，在北京找房子比找工作还难，呵呵。
12 四月, 2007 18:29
1.使用MySQL命令
SELECT columns FROM table_name WHERE whatever='something' INTO OUTFILE "/tmp/outfile.txt"; 2.命令行方式
2.1.管道方式
echo "SELECT columns FROM table_name WHERE whatever='something'" | /path/to/mysql -uUSERNAME -pPASSWORD DATABASENAME > /tmp/outfile.txt; 2.2.使用SQL文件
/path/to/mysql -uUSERNAME -pPASSWORD DATABASENAME < /tmp/sql.sql > /tmp/outfile.txt;
12 二月, 2007 16:54
由于对MySQL的并发插入数据能力没有一个很好的评估，因此在些多进程并发程序时，忽略了MySQL的堵塞问题。
以至程序时不时因为MySQL的堵塞，导致子进程一直在等待MySQL释放堵塞，完成INSERT 指令。
故障现象：
- 堵塞的子进程都是 sbwait 状态
- 父进程，一直在等待子进程结束，是wait状态
- 如果不手工kill掉堵塞的子进程，这些进程一直存在
原因排查：
开始怀疑是socket部分的问题。以为是由于连接服务器时，在等待对方关闭连接而引起的堵塞。
花了很长一段时间来检查和调试socket部分的代码，几次以为已经解决了的时候，又出现故障，都是以失败告终。
这个周末，重新将整个socket连接，数据库连接逐一检查。发现，sbwait 状态时，是由于MySQL的堵塞引起的。多进程并发的情况下，同时抢占MySQL的资源。而MySQL默认表类型，是表锁定的。当A子进程锁定进行插入时，B子进程只能等待。以至并发时，发生堵塞现象。
解决办法：
- 优化表结构和数据结构
- 更改INSERT INTO为 INSERT DELAYED INTO
- 更改程序结构，让每个子进程各自打开一个MySQL连接
说明： INSERT DELAYED INTO，是客户端提交数据给MySQL，MySQL返回OK状态给客户端。而这是并不是已经将数据插入表，而是存储在内存里面等待排队。当mysql有空余时，再插入。
这样的好处是，提高插入的速度，客户端不需要等待太长时间。坏处是，不能返回自动递增的ID，以及系统崩溃时，MySQL还没有来得及插入数据的话，这些数据将会丢失。
观测：
做这些调整后，运行了一天，没有出现堵塞情况。并且运行时间也缩短了。
通过phpMyAdmin观测MySQL的进程，提交后，会有一些用户为DELAYED，状态为Waiting for INSERT的进程。过一会，数据完全插入后就消失了。
总结：
一个系统的东西，就要系统的去考虑存在的问题和可能将要发生的问题。不能过于片面的自以为是。
MySQL不是万能的，写程序时，一定要注意MySQL的性能问题。
11 二月, 2007 21:30
你能用这个命令得到mysqld服务器缺省缓冲区大小：
shell> mysqld --help
这个命令生成一张所有mysqld选项和可配置变量的表。输出包括缺省值并且看上去象这样一些东西：
Possible variables for option --set-variable (-O) are:
back_log current value: 5
connect_timeout current value: 5
delayed_insert_timeout current value: 300
delayed_insert_limit current value: 100
delayed_queue_size current value: 1000
flush_time current value: 0
interactive_timeout current value: 28800
join_buffer_size current value: 131072
key_buffer_size current value: 1048540
lower_case_table_names current value: 0
long_query_time current value: 10
max_allowed_packet current value: 1048576
max_connections current value: 100
max_connect_errors current value: 10
max_delayed_threads current value: 20
max_heap_table_size current value: 16777216
max_join_size current value: 4294967295
max_sort_length current value: 1024
max_tmp_tables current value: 32
max_write_lock_count current value: 4294967295
net_buffer_length current value: 16384
query_buffer_size current value: 0
record_buffer current value: 131072
sort_buffer current value: 2097116
table_cache current value: 64
thread_concurrency current value: 10
tmp_table_size current value: 1048576
thread_stack current value: 131072
wait_timeout current value: 28800
如果有一个mysqld服务器正在运行，通过执行这个命令，你可以看到它实际上使用的变量的值：
shell> mysqladmin variables
每个选项在下面描述。对于缓冲区大小、长度和栈大小的值以字节给出，你能用于个后缀“K”或“M” 指出以K字节或兆字节显示值。例如，16M指出16兆字节。后缀字母的大小写没有关系；16M和16m是相同的。
你也可以用命令SHOW STATUS自一个运行的服务器看见一些统计。见7.21 SHOW语法(得到表、列的信息)。
back_log
要求MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。 Unix listen(2)系统调用的手册页应该有更多的细节。检查你的OS文档找出这个变量的最大值。试图设定back_log高于你的操作系统的限制将是无效的。
connect_timeout
mysqld服务器在用Bad handshake（糟糕的握手）应答前正在等待一个连接报文的秒数。
delayed_insert_timeout
一个INSERT DELAYED线程应该在终止之前等待INSERT语句的时间。
delayed_insert_limit
在插入delayed_insert_limit行后，INSERT DELAYED处理器将检查是否有任何SELECT语句未执行。如果这样，在继续前执行允许这些语句。
delayed_queue_size
应该为处理INSERT DELAYED分配多大一个队列(以行数)。如果排队满了，任何进行INSERT DELAYED的客户将等待直到队列又有空间了。
flush_time
如果这被设置为非零值，那么每flush_time秒所有表将被关闭(以释放资源和sync到磁盘)。
interactive_timeout
服务器在关上它前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对mysql_real_connect()使用CLIENT_INTERACTIVE选项的客户。也可见wait_timeout。
join_buffer_size
用于全部联结(join)的缓冲区大小(不是用索引的联结)。缓冲区对2个表间的每个全部联结分配一次缓冲区，当增加索引不可能时，增加该值可得到一个更快的全部联结。（通常得到快速联结的最佳方法是增加索引。）
key_buffer_size
索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，系统将开始换页并且真的变慢了。记住既然MySQL不缓存读取的数据，你将必须为OS文件系统缓存留下一些空间。为了在写入多个行时得到更多的速度，使用LOCK TABLES。见7.24LOCK TABLES/UNLOCK TABLES语法。
long_query_time
如果一个查询所用时间超过它(以秒计)，Slow_queries记数器将被增加。
max_allowed_packet
一个包的最大尺寸。消息缓冲区被初始化为net_buffer_length字节，但是可在需要时增加到max_allowed_packet个字节。缺省地，该值太小必能捕捉大的(可能错误)包。如果你正在使用大的BLOB列，你必须增加该值。它应该象你想要使用的最大BLOB的那么大。
max_connections
允许的同时客户的数量。增加该值增加mysqld要求的文件描述符的数量。见下面对文件描述符限制的注释。见18.2.4 Too many connections错误。
max_connect_errors
如果有多于该数量的从一台主机中断的连接，这台主机阻止进一步的连接。你可用FLUSH HOSTS命令疏通一台主机。
max_delayed_threads
不要启动多于的这个数字的线程来处理INSERT DELAYED语句。如果你试图在所有INSERT DELAYED线程在用后向一张新表插入数据，行将被插入，就像DELAYED属性没被指定那样。
max_join_size
可能将要读入多于max_join_size个记录的联结将返回一个错误。如果你的用户想要执行没有一个WHERE子句、花很长时间并且返回百万行的联结，设置它。
max_sort_length
在排序BLOB或TEXT值时使用的字节数(每个值仅头max_sort_length个字节被使用；其余的被忽略)。
max_tmp_tables
（该选择目前还不做任何事情)。一个客户能同时保持打开的临时表的最大数量。
net_buffer_length
通信缓冲区在查询之间被重置到该大小。通常这不应该被改变，但是如果你有很少的内存，你能将它设置为查询期望的大小。（即，客户发出的SQL语句期望的长度。如果语句超过这个长度，缓冲区自动地被扩大，直到max_allowed_packet个字节。）
record_buffer
每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。
sort_buffer
每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。见18.5 MySQL在哪儿存储临时文件。
table_cache
为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。MySQL对每个唯一打开的表需要2个文件描述符，见下面对文件描述符限制的注释。对于表缓存如何工作的信息，见10.2.4 MySQL怎样打开和关闭表。
tmp_table_size
如果一张临时表超出该大小，MySQL产生一个The table tbl_name is full形式的错误，如果你做很多高级GROUP BY查询，增加tmp_table_size值。
thread_stack
每个线程的栈大小。由crash-me测试检测到的许多限制依赖于该值。缺省队一般的操作是足够大了。见10.8 使用你自己的基准。
wait_timeout
服务器在关闭它之前在一个连接上等待行动的秒数。也可见interactive_timeout。
MySQL使用是很具伸缩性的算法，因此你通常能用很少的内存运行或给MySQL更多的被存以得到更好的性能。
如果你有很多内存和很多表并且有一个中等数量的客户，想要最大的性能，你应该一些象这样的东西：
shell> safe_mysqld -O key_buffer=16M -O table_cache=128
-O sort_buffer=4M -O record_buffer=1M &
如果你有较少的内存和大量的连接，使用这样一些东西：
shell> safe_mysqld -O key_buffer=512k -O sort_buffer=100k
-O record_buffer=100k &
或甚至：
shell> safe_mysqld -O key_buffer=512k -O sort_buffer=16k
-O table_cache=32 -O record_buffer=8k -O net_buffer=1K &
如果有很多连接，“交换问题”可能发生，除非mysqld已经被配置每个连接使用很少的内存。当然如果你对所有连接有足够的内存，mysqld执行得更好。
注意，如果你改变mysqld的一个选项，它实际上只对服务器的那个例子保持。
为了明白一个参数变化的效果，这样做：
shell> mysqld -O key_buffer=32m --help
保证--help选项是最后一个；否则，命令行上在它之后列出的任何选项的效果将不在反映在输出中。
shell> mysqld --help
这个命令生成一张所有mysqld选项和可配置变量的表。输出包括缺省值并且看上去象这样一些东西：
Possible variables for option --set-variable (-O) are:
back_log current value: 5
connect_timeout current value: 5
delayed_insert_timeout current value: 300
delayed_insert_limit current value: 100
delayed_queue_size current value: 1000
flush_time current value: 0
interactive_timeout current value: 28800
join_buffer_size current value: 131072
key_buffer_size current value: 1048540
lower_case_table_names current value: 0
long_query_time current value: 10
max_allowed_packet current value: 1048576
max_connections current value: 100
max_connect_errors current value: 10
max_delayed_threads current value: 20
max_heap_table_size current value: 16777216
max_join_size current value: 4294967295
max_sort_length current value: 1024
max_tmp_tables current value: 32
max_write_lock_count current value: 4294967295
net_buffer_length current value: 16384
query_buffer_size current value: 0
record_buffer current value: 131072
sort_buffer current value: 2097116
table_cache current value: 64
thread_concurrency current value: 10
tmp_table_size current value: 1048576
thread_stack current value: 131072
wait_timeout current value: 28800
如果有一个mysqld服务器正在运行，通过执行这个命令，你可以看到它实际上使用的变量的值：
shell> mysqladmin variables
每个选项在下面描述。对于缓冲区大小、长度和栈大小的值以字节给出，你能用于个后缀“K”或“M” 指出以K字节或兆字节显示值。例如，16M指出16兆字节。后缀字母的大小写没有关系；16M和16m是相同的。
你也可以用命令SHOW STATUS自一个运行的服务器看见一些统计。见7.21 SHOW语法(得到表、列的信息)。
back_log
要求MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。 Unix listen(2)系统调用的手册页应该有更多的细节。检查你的OS文档找出这个变量的最大值。试图设定back_log高于你的操作系统的限制将是无效的。
connect_timeout
mysqld服务器在用Bad handshake（糟糕的握手）应答前正在等待一个连接报文的秒数。
delayed_insert_timeout
一个INSERT DELAYED线程应该在终止之前等待INSERT语句的时间。
delayed_insert_limit
在插入delayed_insert_limit行后，INSERT DELAYED处理器将检查是否有任何SELECT语句未执行。如果这样，在继续前执行允许这些语句。
delayed_queue_size
应该为处理INSERT DELAYED分配多大一个队列(以行数)。如果排队满了，任何进行INSERT DELAYED的客户将等待直到队列又有空间了。
flush_time
如果这被设置为非零值，那么每flush_time秒所有表将被关闭(以释放资源和sync到磁盘)。
interactive_timeout
服务器在关上它前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对mysql_real_connect()使用CLIENT_INTERACTIVE选项的客户。也可见wait_timeout。
join_buffer_size
用于全部联结(join)的缓冲区大小(不是用索引的联结)。缓冲区对2个表间的每个全部联结分配一次缓冲区，当增加索引不可能时，增加该值可得到一个更快的全部联结。（通常得到快速联结的最佳方法是增加索引。）
key_buffer_size
索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，系统将开始换页并且真的变慢了。记住既然MySQL不缓存读取的数据，你将必须为OS文件系统缓存留下一些空间。为了在写入多个行时得到更多的速度，使用LOCK TABLES。见7.24LOCK TABLES/UNLOCK TABLES语法。
long_query_time
如果一个查询所用时间超过它(以秒计)，Slow_queries记数器将被增加。
max_allowed_packet
一个包的最大尺寸。消息缓冲区被初始化为net_buffer_length字节，但是可在需要时增加到max_allowed_packet个字节。缺省地，该值太小必能捕捉大的(可能错误)包。如果你正在使用大的BLOB列，你必须增加该值。它应该象你想要使用的最大BLOB的那么大。
max_connections
允许的同时客户的数量。增加该值增加mysqld要求的文件描述符的数量。见下面对文件描述符限制的注释。见18.2.4 Too many connections错误。
max_connect_errors
如果有多于该数量的从一台主机中断的连接，这台主机阻止进一步的连接。你可用FLUSH HOSTS命令疏通一台主机。
max_delayed_threads
不要启动多于的这个数字的线程来处理INSERT DELAYED语句。如果你试图在所有INSERT DELAYED线程在用后向一张新表插入数据，行将被插入，就像DELAYED属性没被指定那样。
max_join_size
可能将要读入多于max_join_size个记录的联结将返回一个错误。如果你的用户想要执行没有一个WHERE子句、花很长时间并且返回百万行的联结，设置它。
max_sort_length
在排序BLOB或TEXT值时使用的字节数(每个值仅头max_sort_length个字节被使用；其余的被忽略)。
max_tmp_tables
（该选择目前还不做任何事情)。一个客户能同时保持打开的临时表的最大数量。
net_buffer_length
通信缓冲区在查询之间被重置到该大小。通常这不应该被改变，但是如果你有很少的内存，你能将它设置为查询期望的大小。（即，客户发出的SQL语句期望的长度。如果语句超过这个长度，缓冲区自动地被扩大，直到max_allowed_packet个字节。）
record_buffer
每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。
sort_buffer
每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。见18.5 MySQL在哪儿存储临时文件。
table_cache
为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。MySQL对每个唯一打开的表需要2个文件描述符，见下面对文件描述符限制的注释。对于表缓存如何工作的信息，见10.2.4 MySQL怎样打开和关闭表。
tmp_table_size
如果一张临时表超出该大小，MySQL产生一个The table tbl_name is full形式的错误，如果你做很多高级GROUP BY查询，增加tmp_table_size值。
thread_stack
每个线程的栈大小。由crash-me测试检测到的许多限制依赖于该值。缺省队一般的操作是足够大了。见10.8 使用你自己的基准。
wait_timeout
服务器在关闭它之前在一个连接上等待行动的秒数。也可见interactive_timeout。
MySQL使用是很具伸缩性的算法，因此你通常能用很少的内存运行或给MySQL更多的被存以得到更好的性能。
如果你有很多内存和很多表并且有一个中等数量的客户，想要最大的性能，你应该一些象这样的东西：
shell> safe_mysqld -O key_buffer=16M -O table_cache=128
-O sort_buffer=4M -O record_buffer=1M &
如果你有较少的内存和大量的连接，使用这样一些东西：
shell> safe_mysqld -O key_buffer=512k -O sort_buffer=100k
-O record_buffer=100k &
或甚至：
shell> safe_mysqld -O key_buffer=512k -O sort_buffer=16k
-O table_cache=32 -O record_buffer=8k -O net_buffer=1K &
如果有很多连接，“交换问题”可能发生，除非mysqld已经被配置每个连接使用很少的内存。当然如果你对所有连接有足够的内存，mysqld执行得更好。
注意，如果你改变mysqld的一个选项，它实际上只对服务器的那个例子保持。
为了明白一个参数变化的效果，这样做：
shell> mysqld -O key_buffer=32m --help
保证--help选项是最后一个；否则，命令行上在它之后列出的任何选项的效果将不在反映在输出中。
11 二月, 2007 20:56
MySQL服务器有几个影响其操作的参数（变量）。如果缺省的参数值不合适，可以将其修改为对服务器运行环境更合适的值。例如，如果您有大量的内存，可以告诉服务为磁盘和索引操作使用较大的缓冲区。这将使内存持有更多的信息并减少了必须进行的磁盘访问的数量。如果是一般的系统，可以告诉服务器使用较小的缓冲区，以防止它扰乱系统资源损害其他的进程。
系统变量的当前值可以通过执行mysqladmin variables 命令来检查。变量可利用- - set - variable var_name = value 选项在命令行设置（ -ovar_name = value 是等价的）。如果要想设置几个变量，可使用多个--set-variable 选项，还可以使用下列语法在一个选项文件的[mysqld] 组中设置变量：
set -variale=var_name=value
在附录E的mysql程序的条款下给出了服务器变量的全部清单。有关性能优化比较常用的变量已在以下列表中给出。您还可以在MySQL参考手册的“从MySQL中获得最高性能”一章中找到该主题的附加讨论。
back_log 引入的客户机连接请求的数量，这些请求在从当前客户机中处理时排队。如果您有一个很忙的站点，可以增加该变量的值。
delayed_queue_size 此变量控制被排队的INSERT DELAYED 语句中的行数。如果该队列已满，则更多的INSERT DELAYED 将堵塞，直到队列有空间为止，这样可防止发布那些语句的客户机继续进行操作。如果您有许多执行这种INSERT 的客户机，且发现它们正在堵塞，那么应增加该变量，使更多的客户机更快地进行工作（ INSERT D E L AYED 在4 . 5节“调度与锁定问题”中讨论）
flush_time 如果系统有问题并且经常锁死或重新引导，应将该变量设置为非零值，这将导致服务器按flush_time 秒来刷新表的高速缓存。用这种方法来写出对表的修改将降低性能，但可减少表讹误或数据丢失的机会。
在Windows 中，可以在命令行上用--flush 选项启动服务器，以迫使表的修改在每次更新后被刷新。
key _ buffer_size 用于存放索引块缓冲区的大小。如果增加该变量值，将加快创建和修改索引操作的时间。值越大MySQL就越有可能在内存中查找键值，这将减少索引处理所需的磁盘访问次数。
在MySQL3.23 以前的版本中，该变量名为key _ buffer。MySQL3.23 及后来的版本同时识别这两个名字。
max_allowed_packet 客户机通信所使用的缓冲区大小的最大值。如果有客户机要发送大量的BLOB 或TEXT 的值，该服务器变量值可能需要增大。
客户机目前使用大小为24MB 的缺省缓冲区。如果有使用较小缓冲区的旧客户机。可能需要使该客户机的缓冲区大一些。例如， mysql可以按如下调用来指定一个2 4 MB 信息包的限制值：
mysql--set-varibale max_allowed_packet=24M
max_connections 服务器允许的客户机同时连接的最大数量。如果服务器繁忙，可能需要增加该值。例如，如果您的MySQL服务器被Web 服务器使用来处理由DBI 或PHP 脚本产生的查询，并且还有大量的Web 通信，如果该变量设置太低的话，则您站点的访问者会发现请求被拒绝。
table_cache 表的高速缓存的大小。增加该值可以使mysqld 保持更多的表，同时打开并减少必须进行的文件打开和关闭操作的次数。
如果增加了max_connections 或table_cache 值的大小，服务器将需要大量的文件描述符。这将引起有关操作系统对 文件描述符总进程数量限定的问题，在这种情况下您需要增加该限制值或逐步解决它。由于增加文件描述符数量的限制值，过程会发生变化，所以您可能会在一个运 行脚本中使用ulimit 命令时来这样做，该脚本可用于启动服务器，或用于重新配置您的系统。有些系统可以通过编辑系统描述文件来简单地配置和重新引导。对于其他一些系统，则必须 编辑一个内核描述文件并重建该内核。如何继续进行下去，请参考您系统的文档。
解决总进程文件描述符限制的一个方法是：将数据目录分离成多个数据目录并运行多个服务器。这样，通过运行多个服务器使可用的描述符数量成倍增长。但另一方面，其他的复杂因素可能会引起问题。由于命名了两个服务器，您不能从一个单个的服务器上访问不同数
据目录中的数据库，并且还需要在不同服务器之间复制授权表的权限，以便用户需要访问一个以上的服务器。
有两个变量是管理员为提高性能时常增加的，它们是record _ buffer 和sort _ buffer。这些缓冲区在连接和分类操作中使用，但其值是属于每个连接的。也就是说，每个客户机都获得属于自己的缓冲区。如果使这些变量的值很大，性能 可能会由于昂贵的系统资源的消耗而遭受实际的损失。如果想要修改这些变量，先执行mysqladmin variables 查看一下它们当前的值，然后增量调整其值。这个操作使您能估计为减少严重的性能降低所进行的修改的效果。
系统变量的当前值可以通过执行mysqladmin variables 命令来检查。变量可利用- - set - variable var_name = value 选项在命令行设置（ -ovar_name = value 是等价的）。如果要想设置几个变量，可使用多个--set-variable 选项，还可以使用下列语法在一个选项文件的[mysqld] 组中设置变量：
set -variale=var_name=value
在附录E的mysql程序的条款下给出了服务器变量的全部清单。有关性能优化比较常用的变量已在以下列表中给出。您还可以在MySQL参考手册的“从MySQL中获得最高性能”一章中找到该主题的附加讨论。
back_log 引入的客户机连接请求的数量，这些请求在从当前客户机中处理时排队。如果您有一个很忙的站点，可以增加该变量的值。
delayed_queue_size 此变量控制被排队的INSERT DELAYED 语句中的行数。如果该队列已满，则更多的INSERT DELAYED 将堵塞，直到队列有空间为止，这样可防止发布那些语句的客户机继续进行操作。如果您有许多执行这种INSERT 的客户机，且发现它们正在堵塞，那么应增加该变量，使更多的客户机更快地进行工作（ INSERT D E L AYED 在4 . 5节“调度与锁定问题”中讨论）
flush_time 如果系统有问题并且经常锁死或重新引导，应将该变量设置为非零值，这将导致服务器按flush_time 秒来刷新表的高速缓存。用这种方法来写出对表的修改将降低性能，但可减少表讹误或数据丢失的机会。
在Windows 中，可以在命令行上用--flush 选项启动服务器，以迫使表的修改在每次更新后被刷新。
key _ buffer_size 用于存放索引块缓冲区的大小。如果增加该变量值，将加快创建和修改索引操作的时间。值越大MySQL就越有可能在内存中查找键值，这将减少索引处理所需的磁盘访问次数。
在MySQL3.23 以前的版本中，该变量名为key _ buffer。MySQL3.23 及后来的版本同时识别这两个名字。
max_allowed_packet 客户机通信所使用的缓冲区大小的最大值。如果有客户机要发送大量的BLOB 或TEXT 的值，该服务器变量值可能需要增大。
客户机目前使用大小为24MB 的缺省缓冲区。如果有使用较小缓冲区的旧客户机。可能需要使该客户机的缓冲区大一些。例如， mysql可以按如下调用来指定一个2 4 MB 信息包的限制值：
mysql--set-varibale max_allowed_packet=24M
max_connections 服务器允许的客户机同时连接的最大数量。如果服务器繁忙，可能需要增加该值。例如，如果您的MySQL服务器被Web 服务器使用来处理由DBI 或PHP 脚本产生的查询，并且还有大量的Web 通信，如果该变量设置太低的话，则您站点的访问者会发现请求被拒绝。
table_cache 表的高速缓存的大小。增加该值可以使mysqld 保持更多的表，同时打开并减少必须进行的文件打开和关闭操作的次数。
如果增加了max_connections 或table_cache 值的大小，服务器将需要大量的文件描述符。这将引起有关操作系统对 文件描述符总进程数量限定的问题，在这种情况下您需要增加该限制值或逐步解决它。由于增加文件描述符数量的限制值，过程会发生变化，所以您可能会在一个运 行脚本中使用ulimit 命令时来这样做，该脚本可用于启动服务器，或用于重新配置您的系统。有些系统可以通过编辑系统描述文件来简单地配置和重新引导。对于其他一些系统，则必须 编辑一个内核描述文件并重建该内核。如何继续进行下去，请参考您系统的文档。
解决总进程文件描述符限制的一个方法是：将数据目录分离成多个数据目录并运行多个服务器。这样，通过运行多个服务器使可用的描述符数量成倍增长。但另一方面，其他的复杂因素可能会引起问题。由于命名了两个服务器，您不能从一个单个的服务器上访问不同数
据目录中的数据库，并且还需要在不同服务器之间复制授权表的权限，以便用户需要访问一个以上的服务器。
有两个变量是管理员为提高性能时常增加的，它们是record _ buffer 和sort _ buffer。这些缓冲区在连接和分类操作中使用，但其值是属于每个连接的。也就是说，每个客户机都获得属于自己的缓冲区。如果使这些变量的值很大，性能 可能会由于昂贵的系统资源的消耗而遭受实际的损失。如果想要修改这些变量，先执行mysqladmin variables 查看一下它们当前的值，然后增量调整其值。这个操作使您能估计为减少严重的性能降低所进行的修改的效果。
13 十月, 2006 18:21
key_buffer_size 控制索引缓冲内存，值越高，索引可以使用的内存越多，性能约好。建议设置为系统内存的25%-30%左右。
table_cache 控制表高速缓存可以使用内存的数量，以及同一时间内MySQL可以处理表的打开总量。 相关的变量有 max_connections ，MySQL手册建议公式： table_cache = max_connections x N ，N为标准连接中表的数量。
sort_buffer 可以提高ORDER BY GROUP BY
read_rnd_buffer_size 提高读分类行的速度
read_buffer_size 提高SELECT查询的速度，是的SELECT能顺利的顺序扫描表。
binlog_cache_size 增加二进制日志的缓存，对于写操作频繁的服务器有提高性能。
thread_cache_size 对于负载高，读写频繁的服务器，增加会提高线程连接效率。