mysql的mysql-udf-http效率测试小记_MySQL
程序员文章站
2023-12-29 20:51:52
...
看到张宴的博客上关于"http/rest客户端的文章"，怎样安装啥的直接都跳过，下面直接进入测试阶段，测试环境：虚拟机 
View Code View Code View Code
[root@localhost ~]# uname -a
Linux sunss 2.6.18-128.el5 #1 SMP Wed Jan 21 10:44:23 EST 2009 i686 i686 i386 GNU/Linux
内存和交换分区：
[root@localhost ~]# free -m
total used free shared buffers cached
Mem: 376 363 13 0 23 105
-/+ buffers/cache: 233 142
Swap: 1023 133 890
mysql：
[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or /g.
Your MySQL connection id is 57
Server version: 5.1.26-rc-log Source distribution
Type 'help;' or '/h' for help. Type '/c' to clear the buffer.
mysql>
使用的表结构：
DROP TABLE IF EXISTS `mytable`;
CREATE TABLE `mytable` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`addtime` int(10) NOT NULL,
`title` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
php操作MySQL的程序：
1 2 $type = $_GET['type'];
3 print_r($_GET);
4 include_once("gettime.php");
5 $btime = getmicrotime();
6 $loop_cnt= 1000; //循环次数
7 $db_host = '127.0.0.1'; //
8 $db_user = 'sunss'; //
9 $db_pass = '123456'; //
10 $db_name = 'test'; //
11 $db_link = mysql_connect($db_host, $db_user, $db_pass) or die("Connected failed: ".mysql_error()."/n");
12 mysql_query('set names utf8');
13 mysql_db_query($db_name, $db_link);
14 if ("put" == $type) {//修改
15 $i = 1;
16 while ($i 17 $title = "jkjkjkjkjkjkjkjkjkjkjkjkjk";
18 $tt = time();
19 $sql = "update mytable set addtime=".$tt.",title='".$title."' where id='".$i."'";
20 $res = mysql_query($sql);
21 if (FALSE == $res) {
22 echo "update failed!/n";
23 }
24 $i++;
25 }
26 } else if ("delete" == $type) { //删除
27 $i = 1;
28 while ($i 29 $sql = "delete from mytable where id='".$i."'";
30 echo "delete sql: ".$sql."
";
31 $res = mysql_query($sql);
32 if (FALSE == $res) {
33 echo "delete failed!/n";
34 }
35 $i++;
36 }
37
38 } else if ("post" == $type) { //添加
39 $i = 0;
40 while ($i 41 $title = "hahahahahahahahahahahahahahahahahaha";
42 $tt = time();
43 $sql = "insert into mytable(addtime, title) values($tt, '".$title."')";
44 //print "SQL: ".$sql."
";
45 $res = mysql_query($sql);
46 if (FALSE == $res) {
47 echo "insert failed!/n";
48 }
49 $i++;
50 }
51 }
52 mysql_close();
53 $etime = getmicrotime();
54 $runTime = round($etime - $btime, 4);
55 echo "runTime: ".$runTime."/r/n
";
56 ?>
单独执行php连接MySQL，单条连接添加1000条记录需要：0.9s左右
php操作memcache的程序：
1 2 include_once("gettime.php");
3 $btime = getmicrotime();
4 //杩炴帴
5 $mem_host = "192.168.0.134";
6 $mem_port = "11311";
7 $timeout = 3600;
8 $i = 0;
9 $cnt = 1000;
10 while ($i 11 $mem = new Memcache;
12 $mem->connect($mem_host, $mem_port) or die("Could not connect!");
13 $ret = $mem->set($i, "11111111111", 0, $timeout);
14 if (false == $ret) {
15 file_put_contents("insert_failed.log", "post failed!/n", FILE_APPEND);
16 }
17 $mem->close();
18 $i++;
19 }
20
21 //鍏抽棴杩炴帴
22 $etime = getmicrotime();
23 $runTime = round($etime - $btime, 4);
24 echo "runTime: ".$runTime."/r/n
";
25 ?>
单条连接添加1000条记录，需要0.8s左右，
创建触发器：
DELIMITER $$
DROP TRIGGER /*!50032 IF EXISTS */ `test`.`mytable_insert`$$
CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `mytable_insert` AFTER INSERT ON `mytable`
FOR EACH ROW BEGIN
SET @tt_resu = (SELECT http_put(CONCAT('http://192.168.0.134/mem_ss.php?type=post&id=', NEW.id, "&data=", NEW.addtime), 11));
END;
$$
为触发器写个php更新memcache,代码如下：
$id = $_GET['id'];
$type = $_GET['type'];
$json_data = $_GET['data'];
var_dump($_GET);
//杩炴帴
$mem_host = "192.168.0.134";
$mem_port = "11211";
$timeout = 3600;
$mem = new Memcache;
$mem->connect($mem_host, $mem_port) or die("Could not connect!");
if ("get" == $type ) {
$val = $mem->get($id);
echo $val;
//$arr = jsonDecode($val,'utf-8');
//print_r($arr);
} else if ("put" == $type) {
$ret = $mem->replace($id, $json_data, 0, $timeout);
if (false == $ret) {
file_put_contents("replace_failed.log", "replace failed!/n", FILE_APPEND);
}
} else if ("delete" == $type) {
$ret = $mem->delete($id);
if (false == $ret) {
file_put_contents("delete_failed.log", "delete failed!/n", FILE_APPEND);
}
} else if ("post" == $type) {
$ret = $mem->set($id, $json_data, 0, $timeout);
if (false == $ret) {
file_put_contents("post_failed.log", "post failed!/n", FILE_APPEND);
}
}
$mem->close();
?>
使用php触发MySQL添加1000条记录，同时触发器触动php更新memcache，使用时间9s左右，
因为每次都关闭链接memcache，看是不是关闭链接导致慢，又写了一个程序：View Code
include_once("gettime.php");
$btime = getmicrotime();
//连接
$mem_host = "192.168.0.134";
$mem_port = "11311";
$timeout = 3600;
$i = 0;
$cnt = 1000;
while ($i $mem = new Memcache;
$mem->connect($mem_host, $mem_port) or die("Could not connect!");
$ret = $mem->set($i, "11111111111", 0, 3600);
if (false == $ret) {
file_put_contents("insert_failed.log", "post failed!/n", FILE_APPEND);
}
$mem->close();
$i++;
}
//关闭连接
$etime = getmicrotime();
$runTime = round($etime - $btime, 4);
echo "runTime: ".$runTime."/r/n
";
?>
耗时0.9s左右，比一个连接慢不了多少。
为了定位是触发器慢还是http_put慢，创建一个临时表
tmp_mytable，表结构如下：CREATE TABLE `mytable` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`addtime` int(10) NOT NULL,
`title` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
再次修改触发器，如下：
DELIMITER $$
DROP TRIGGER /*!50032 IF EXISTS */ `test`.`mytable_insert`$$
CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `mytable_insert` AFTER INSERT ON `mytable`
FOR EACH ROW BEGIN
insert into tmp_mytable values(NEW.id,NEW.addtime,NEW.title);
END;
$$
再次用php向MySQL中添加1000条记录，消耗时间0.7s左右，证明效率消耗在http_put，也就是mysql-udf-http慢。
不知道我的测试有错没？还请正在使用mysql-udf-http的高手，或者对mysql-udf-http有研究的高手指教。
推荐阅读
-
mysql的mysql-udf-http效率测试小记_MySQL
-
mysql的insert与update效率提高上万倍的经历
-
mysql - ezsql如果没有开启缓存效率和php的一样吗
-
PHP,Mysql-根据一个给定经纬度的点，进行附近地点查询–合理利用算法，效率提高2125倍，mysql-2125倍_PHP教程
-
mysql 普通索引 唯一索引的创建与效率比较
-
提高MySQL数据库查询效率的几个技巧[php程序员必看]
-
测试PHP连接MYSQL成功与否的代码_PHP教程
-
高效的MySQL分页语句的效率
-
MySQL的MMM高可用架构测试_MySQL
-
初学者必读：提高SQL执行效率的几点建议_MySQL