该功能主要是用来改善外部程序对数据库的反复连接、以及长期不释放的连接对数据库带来的性能和稳定性问题。
准备步骤:
1、 在测试之前准备了两台linux(redhat5.4)虚拟机(机器1:bogon,IP:10.122.3.73;机器2:bogon_2,IP:10.122.3.69),分别安装了MySQL5.6.27。
2、 按照“1.OneProxy5.8.1新的目录组织结构运行试验”的步骤在bogon安装OneProxy 。
3、 在bogon的test数据库中创建测试表t,并分别插入一条记录用于区分,数据如下:
在bogon上:
mysql> select * from t;
+---+-------+
| a | b |
+---+-------+
| 1 | bogon |
+---+-------+
1 row in set (0.00 sec)
在bogon_2上:
mysql> select * from t;
+---+---------+
| a | b |
+---+---------+
| 1 | bogon_2 |
+---+---------+
1 row in set (0.00 sec)
测试内容:
通过连续创建100个会话的脚本来分别测试通过OneProxy来连接数据库的是否可以实际减少数据会话的创建。
脚本1(直接连MySQL)内容为:
# create by yb # coding=gbk # function: connect mysql test import time import os import sys import mysql.connector
# 创建连接 config = { 'user':'test', 'password':'test', 'host':'10.122.3.73', 'port':3306, 'database':'test'}
conn_arr=[] for i in range(100): conn = mysql.connector.connect(**config) # 创建游标 cur = conn.cursor() # 执行查询SQL sql = "SELECT a,b FROM t WHERE a=1" cur.execute(sql) # 获取查询结果 result_set = cur.fetchall() if result_set: for row in result_set: print "%d, %s" % (row[0],row[1]) conn_arr.append(conn) print "100 connect opened!" time.sleep(100) # 关闭游标和连接 cur.close() conn.close() |
脚本2(通过OneProxy来连接)内容为:
# create by yb # coding=gbk # function: connect mysql test import time import os import sys import mysql.connector
# 创建连接 config = { 'user':'test', 'password':'test', 'host':'10.122.3.73', 'port':3307, 'database':'test'}
conn_arr=[] for i in range(100): conn = mysql.connector.connect(**config) # 创建游标 cur = conn.cursor() # 执行查询SQL sql = "SELECT a,b FROM t WHERE a=1" cur.execute(sql) # 获取查询结果 result_set = cur.fetchall() if result_set: for row in result_set: print "%d, %s" % (row[0],row[1]) conn_arr.append(conn) print "100 connect opened!" time.sleep(100) # 关闭游标和连接 cur.close() conn.close() |
测试步骤:
1、 执行脚本1,可以观察执行后数据库的连接数直接增加了100个:
执行前会话情况:
mysql> show full processlist;
+----+------+-------------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-------------+------+---------+------+-------+-----------------------+
| 39 | root | bogon:31777 | NULL | Query | 0 | init | show full processlist |
+----+------+-------------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)
执行后会话情况:
mysql> show full processlist;
+-----+------+-------------------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-------------------+------+---------+------+-------+-----------------------+
| 39 | root | bogon:31777 | NULL | Query | 0 | init | show full processlist |
| 49 | test | 10.122.3.11:53775 | test | Sleep | 4 | | NULL |
| 50 | test | 10.122.3.11:53776 | test | Sleep | 4 | | NULL |
| 51 | test | 10.122.3.11:53777 | test | Sleep | 4 | | NULL |
| 52 | test | 10.122.3.11:53778 | test | Sleep | 4 | | NULL |
| 53 | test | 10.122.3.11:53779 | test | Sleep | 4 | | NULL |
| 54 | test | 10.122.3.11:53780 | test | Sleep | 4 | | NULL |
| 55 | test | 10.122.3.11:53781 | test | Sleep | 4 | | NULL |
| 56 | test | 10.122.3.11:53782 | test | Sleep | 4 | | NULL |
| 57 | test | 10.122.3.11:53783 | test | Sleep | 4 | | NULL |
| 58 | test | 10.122.3.11:53784 | test | Sleep | 4 | | NULL |
| 59 | test | 10.122.3.11:53785 | test | Sleep | 4 | | NULL |
| 60 | test | 10.122.3.11:53786 | test | Sleep | 4 | | NULL |
…部分省略
101 rows in set (0.00 sec)
2、 启动bogon上的OneProxy,并执行脚本2,可以观察到启动OneProxy后数据库增加了11个会话,单执行脚本后数据库的连接数没有增加,因此说明通过OneProxy连接数据库有建立了会话连接池机制,因此可以有效的保护数据库会话的使用:
启动OneProxy后执行脚本前会话情况:
mysql> show full processlist;
+-----+------+-------------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-------------+------+---------+------+-------+-----------------------+
| 39 | root | bogon:31777 | NULL | Query | 0 | init | show full processlist |
| 149 | test | bogon:23836 | test | Sleep | 1 | | NULL |
| 150 | test | bogon:23839 | test | Sleep | 13 | | NULL |
| 151 | test | bogon:23841 | test | Sleep | 13 | | NULL |
| 152 | test | bogon:23843 | test | Sleep | 13 | | NULL |
| 153 | test | bogon:23846 | test | Sleep | 12 | | NULL |
| 154 | test | bogon:23848 | test | Sleep | 12 | | NULL |
| 155 | test | bogon:23850 | test | Sleep | 12 | | NULL |
| 156 | test | bogon:23853 | test | Sleep | 11 | | NULL |
| 157 | test | bogon:23855 | test | Sleep | 11 | | NULL |
| 158 | test | bogon:23857 | test | Sleep | 11 | | NULL |
| 159 | test | bogon:23859 | test | Sleep | 10 | | NULL |
+-----+------+-------------+------+---------+------+-------+-----------------------+
12 rows in set (0.00 sec)
执行脚本后会话情况:
mysql> show full processlist;
+-----+------+-------------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-------------+------+---------+------+-------+-----------------------+
| 39 | root | bogon:31777 | NULL | Query | 0 | init | show full processlist |
| 260 | test | bogon:24137 | test | Sleep | 0 | | NULL |
| 261 | test | bogon:24139 | test | Sleep | 98 | | NULL |
| 262 | test | bogon:24141 | test | Sleep | 98 | | NULL |
| 263 | test | bogon:24143 | test | Sleep | 98 | | NULL |
| 264 | test | bogon:24146 | test | Sleep | 97 | | NULL |
| 265 | test | bogon:24148 | test | Sleep | 97 | | NULL |
| 266 | test | bogon:24150 | test | Sleep | 97 | | NULL |
| 267 | test | bogon:24153 | test | Sleep | 96 | | NULL |
| 268 | test | bogon:24155 | test | Sleep | 96 | | NULL |
| 269 | test | bogon:24157 | test | Sleep | 96 | | NULL |
| 270 | test | bogon:24160 | test | Sleep | 95 | | NULL |
+-----+------+-------------+------+---------+------+-------+-----------------------+
12 rows in set (0.00 sec)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7913676/viewspace-1985204/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7913676/viewspace-1985204/