16hot 的博客

16hot 的博客 http://16hot.blog.isyi.com 16hot 一心想要建设一个公共平台给所有有需要的朋友使用。但是，在这里留给自己一块空间，抒发自己的心情。 16hot 2008-11-19T19:48:41Z 用mysqlslap对MySQL进行压力测试（转） http://16hot.blog.isyi.com/post/1/849 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 <code>[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.sock</code> 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）、总记录数。 <code>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) </code> 2）、总列数。 <code>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) </code> 3）、调用的存储过程 <code>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 ;</code> 4）、我们用mysqlslap来测试 以下得这个例子代表用mysqlslap来测试并发数为25,50,100的调用存储过程，并且总共调用5000次。 <code>[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.sock</code> 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) 上面的测试语句其实也可以这样写 <code>[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</code> 小总结一下。 mysqlslap对于模拟多个用户同时对MySQL发起“进攻”提供了方便。同时详细的提供了“高负荷攻击MySQL”的详细数据报告。 而且如果你想对于多个引擎的性能。这个工具再好不过了。   MySQL 2008-06-15T04:20:39Z 16hot MySQL Proxy Getting Started http://16hot.blog.isyi.com/post/1/848 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 2008-06-11T05:38:47Z 16hot mysql-proxy 按表分发查询的lua脚本 http://16hot.blog.isyi.com/post/1/847 请修改这里：注意，本版本尚未经过严格测试与实际检验！！发出来只为提供一种思路，使用时请根据实际情况修改，风险自担。 -- 将分到另一个库中的表明列在这里： local remote_tables = {"table1", "table2"} 在启动的时候： <pre>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 </pre> lua脚本： <pre>-- 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 </pre> MySQL 2008-06-11T05:36:12Z 16hot MySQL Proxy learns R/W Splitting http://16hot.blog.isyi.com/post/1/846 <div class="entrybody"> 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 <a href="http://svn.mysql.com/svnpublic/mysql-proxy/trunk/examples/tutorial-keepalive.lua">tutorial</a> which covers everything from: <ul><li>building and maintaining a connection pool with high and low water marks</li><li>transparent authentication (no extra auth against the proxy)</li><li>deciding on Query Level which backend to use</li></ul> and implement a transparent read/write splitter which sends all non-transactional Queries to the slaves and the rest to the master. <img src="http://jan.kneschke.de/projects/mysql/mysql-proxy-types-trx-splitting.png" border="0" alt="R/W Splitting" /> 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. <h2>Connection Pooling</h2> 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. <img src="http://forge.mysql.com/w/images/6/6e/Mysql-proto-state.png" border="0" alt="MySQL Protocol States" /> In the <a href="http://svn.mysql.com/svnpublic/mysql-proxy/trunk/examples/tutorial-keepalive.lua">keepalive tutorial</a> we spend quite some code on connection management. The whole connect_servers() function is only to create new connections for all pools. <ol><li>create one connection to each backend</li><li>create new connections until we reach min-idle-connections</li><li>if the two above conditions are met, use a connection from the pool</li></ol> Let's take a glimpse at the code: <pre><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 </code></pre> The real trick is in <pre><code>--- -- put the authed connection into the connection pool function read_auth_result(packet) -- disconnect from the server proxy.connection.backend_ndx = 0 end </code></pre> 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 <code>proxy.connection.backend_ndx</code> you control which backend is used to send your packets too. A backend is defined as a entry of the <code>proxy.servers</code> 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: <pre><code>--- -- 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 </code></pre> 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. <code>if proxy.connection.backend_ndx == 0 then</code> is the "we don't have backend associated right now". We already saw this in <code>read_auth_result</code>. <h2>Read/Write Splitting</h2> That is our maintainance of the pool. <code>connect_server()</code> adds new auth'ed connections to the pool, <code>disconnect_client()</code> closes them again. The read/write splitting is part of the query/result cycle: <pre><code>-- 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) </code></pre> Up to now it is only making sure that we behave nicely: <ul><li>don't forward <code>COM_QUIT</code> to the backend as he will close the connection on us</li><li>intercept the <code>COM_INIT_DB</code> to 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.</li></ul> The read/write splitting is now following a simple rule: <ul><li>send all non-transactional SELECTs to a slave</li><li>everything else goes to the master</li></ul> We are still in <code>read_query()</code> <pre><code> -- 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 </code></pre> 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: <pre><code>--- -- 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 </code></pre> 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. <h3>Possible extensions</h3> While we are here in this section of the code think about another use case: <ul><li>if the master is down, ban all writing queries and only allow reading selects against the slaves. </li></ul> It keeps your site up and running even if your master is gone. You only have to handle errors on write-statements and transactions. <h3>Known Problems</h3> 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. <h2>Next Steps</h2> Testing, testing, testing. <pre><code>$ 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 </code></pre> 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 <a href="http://forums.mysql.com/list.php?146">MySQL Proxy forum</a>. Another upcoming step is externalizing all the load-balancer code and move it into modules to make the code easier to understand and reuseable. </div> MySQL 2008-06-11T05:33:02Z 16hot 发现新玩艺儿—-MySQL Proxy(转) http://16hot.blog.isyi.com/post/1/845 <div class="entry-content"> 今天看到了这么一个新项目–<a href="http://forge.mysql.com/wiki/MySQL_Proxy">MySQL Proxy</a>，貌似最近比较热门。简单来说就是一个从MySQL Client到Server的一个代理。可能有人认为MySQL这种连接方式不应该会用到代理，因为一般来说都喜欢把MySQL Server放到后端，用内网圈起来，这样一方面保证安全，另一方面用Local  Ethernet来避免不稳定因素较多的Internet连接，因此基本没有代理这个角色出现的机会。然而，假如这个代理不仅仅是一个代理，而是一个能 “懂得”连接双方传送内容，并有可能会对双方内容加以控制甚至干涉的一个角色呢？还有没有人觉得他没用？联想一下<a href="http://www.squid-cache.org/">Squid</a>之于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的架构设计师们一定会喜欢这个玩艺儿。 这里有篇<a href="http://www.oreillynet.com/pub/a/databases/2007/07/12/getting-started-with-mysql-proxy.html?page=1">Getting Start</a>，是很好的入门教材，对于这套软件，定要保持关注。 </div> MySQL 2008-06-11T05:30:46Z 16hot 为你的MySQL数据库加铸23道安全门 http://16hot.blog.isyi.com/post/1/810 转自cu:http://linux.chinaunix.net/docs/2007-04-02/4101.shtml 使用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就可以了。 MySQL 2008-03-21T19:11:54Z 16hot 终于定下房子了 http://16hot.blog.isyi.com/post/1/659 花了半个多月的时间，终于找到一间合适的房间了。说是房间，当然是只租一套房子的其中一个房间，也就是单间吧。今天交了订金了，要到5月5日才能入住，现在里面有人住着。 在北京找房子还真不容易，要么太贵，要么太远。有朋友说，在北京找房子比找工作还难，呵呵。 MySQL 2007-04-14T06:46:45Z 16hot 终于定下房子了 http://16hot.blog.isyi.com/post/1/661 花了半个多月的时间，终于找到一间合适的房间了。说是房间，当然是只租一套房子的其中一个房间，也就是单间吧。今天交了订金了，要到5月5日才能入住，现在里面有人住着。 在北京找房子还真不容易，要么太贵，要么太远。有朋友说，在北京找房子比找工作还难，呵呵。 MySQL 2007-04-14T06:46:38Z 16hot 终于定下房子了 http://16hot.blog.isyi.com/post/1/660 花了半个多月的时间，终于找到一间合适的房间了。说是房间，当然是只租一套房子的其中一个房间，也就是单间吧。今天交了订金了，要到5月5日才能入住，现在里面有人住着。 在北京找房子还真不容易，要么太贵，要么太远。有朋友说，在北京找房子比找工作还难，呵呵。 MySQL 2007-04-14T06:46:06Z 16hot 将SELECT结果导到文件的操作 http://16hot.blog.isyi.com/post/1/658 <h2>1.使用MySQL命令</h2> <code> SELECT columns FROM table_name WHERE whatever='something' INTO OUTFILE "/tmp/outfile.txt"; </code>   <h2>2.命令行方式</h2> <h3>2.1.管道方式</h3> <code> echo "SELECT columns FROM table_name WHERE whatever='something'" | /path/to/mysql -uUSERNAME -pPASSWORD DATABASENAME > /tmp/outfile.txt; </code>   <h3>2.2.使用SQL文件</h3> <code> /path/to/mysql -uUSERNAME -pPASSWORD DATABASENAME < /tmp/sql.sql > /tmp/outfile.txt; </code> MySQL 2007-04-12T18:29:50Z 16hot 终于摆脱MySQL堵塞带来的困扰 http://16hot.blog.isyi.com/post/1/652 由于对MySQL的并发插入数据能力没有一个很好的评估，因此在些多进程并发程序时，忽略了MySQL的堵塞问题。以至程序时不时因为MySQL的堵塞，导致子进程一直在等待MySQL释放堵塞，完成INSERT  指令。故障现象：<ul><li>堵塞的子进程都是 sbwait 状态</li><li>父进程，一直在等待子进程结束，是wait状态</li><li>如果不手工kill掉堵塞的子进程，这些进程一直存在 </li></ul> 原因排查：开始怀疑是socket部分的问题。以为是由于连接服务器时，在等待对方关闭连接而引起的堵塞。花了很长一段时间来检查和调试socket部分的代码，几次以为已经解决了的时候，又出现故障，都是以失败告终。这个周末，重新将整个socket连接，数据库连接逐一检查。发现，sbwait 状态时，是由于MySQL的堵塞引起的。多进程并发的情况下，同时抢占MySQL的资源。而MySQL默认表类型，是表锁定的。当A子进程锁定进行插入时，B子进程只能等待。以至并发时，发生堵塞现象。 解决办法：<ul><li>优化表结构和数据结构</li><li>更改INSERT INTO为 INSERT DELAYED INTO</li><li>更改程序结构，让每个子进程各自打开一个MySQL连接</li></ul>说明： INSERT DELAYED INTO，是客户端提交数据给MySQL，MySQL返回OK状态给客户端。而这是并不是已经将数据插入表，而是存储在内存里面等待排队。当mysql有空余时，再插入。这样的好处是，提高插入的速度，客户端不需要等待太长时间。坏处是，不能返回自动递增的ID，以及系统崩溃时，MySQL还没有来得及插入数据的话，这些数据将会丢失。 观测：做这些调整后，运行了一天，没有出现堵塞情况。并且运行时间也缩短了。通过phpMyAdmin观测MySQL的进程，提交后，会有一些用户为DELAYED，状态为Waiting for INSERT的进程。过一会，数据完全插入后就消失了。 总结：一个系统的东西，就要系统的去考虑存在的问题和可能将要发生的问题。不能过于片面的自以为是。MySQL不是万能的，写程序时，一定要注意MySQL的性能问题。   MySQL 2007-02-12T16:54:35Z 16hot 优化配置mysql服务器（转） http://16hot.blog.isyi.com/post/1/651 你能用这个命令得到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文件系统缓存留下一些空间。为了在