mysql mysql udf http efficiency test notes

  • 2020-05-12 06:17:26
  • OfStack

See zhang yan's blog about "http/rest client article", how to install what directly skip, the following directly into the test phase, test environment: virtual machine
 
[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 

Memory and swap:
 
[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> 

Table structure used:
 
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 procedures for operating MySQL:
 
<?php 
$type = $_GET['type']; 
print_r($_GET); 
include_once("gettime.php"); 
$btime = getmicrotime(); 
$loop_cnt= 1000; // cycles  
$db_host = '127.0.0.1'; // 
$db_user = 'sunss'; // 
$db_pass = '123456'; // 
$db_name = 'test'; // 
$db_link = mysql_connect($db_host, $db_user, $db_pass) or die("Connected failed: ".mysql_error()."\n"); 
mysql_query('set names utf8'); 
mysql_db_query($db_name, $db_link); 
if ("put" == $type) {// Modify the  
$i = 1; 
while ($i <= $loop_cnt) { 
$title = "jkjkjkjkjkjkjkjkjkjkjkjkjk"; 
$tt = time(); 
$sql = "update mytable set addtime=".$tt.",title='".$title."' where id='".$i."'"; 
$res = mysql_query($sql); 
if (FALSE == $res) { 
echo "update failed!\n"; 
} 
$i++; 
} 
} else if ("delete" == $type) { // delete  
$i = 1; 
while ($i <= $loop_cnt) { 
$sql = "delete from mytable where id='".$i."'"; 
echo "delete sql: ".$sql."<br>"; 
$res = mysql_query($sql); 
if (FALSE == $res) { 
echo "delete failed!\n"; 
} 
$i++; 
} 

} else if ("post" == $type) { // add  
$i = 0; 
while ($i < $loop_cnt) { 
$title = "hahahahahahahahahahahahahahahahahaha"; 
$tt = time(); 
$sql = "insert into mytable(addtime, title) values($tt, '".$title."')"; 
//print "SQL: ".$sql."<br>"; 
$res = mysql_query($sql); 
if (FALSE == $res) { 
echo "insert failed!\n"; 
} 
$i++; 
} 
} 
mysql_close(); 
$etime = getmicrotime(); 
$runTime = round($etime - $btime, 4); 
echo "runTime: ".$runTime."\r\n<br>"; 
?> 

To connect php to MySQL separately, adding 1000 records to a single connection requires: 0.9s or so
php procedures for operating memcache:
 
<?php 
include_once("gettime.php"); 
$btime = getmicrotime(); 
// Ma � �  
$mem_host = "192.168.0.134"; 
$mem_port = "11311"; 
$timeout = 3600; 
$i = 0; 
$cnt = 1000; 
while ($i < $cnt) { 
$mem = new Memcache; 
$mem->connect($mem_host, $mem_port) or die("Could not connect!"); 
$ret = $mem->set($i, "11111111111", 0, $timeout); 
if (false == $ret) { 
file_put_contents("insert_failed.log", "post failed!\n", FILE_APPEND); 
} 
$mem->close(); 
$i++; 
} 

// Smoke � � Ma � �  
$etime = getmicrotime(); 
$runTime = round($etime - $btime, 4); 
echo "runTime: ".$runTime."\r\n<br>"; 
?> 

Add 1000 records to a single connection, need about 0.8s,
Create triggers:
 
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; 
$$ 

Write an php update memcache for the trigger as follows:
 
<?php 
$id = $_GET['id']; 
$type = $_GET['type']; 
$json_data = $_GET['data']; 
var_dump($_GET); 
// Ma � �  
$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(); 
?> 

Use php to trigger MySQL to add 1000 records, and trigger php to update memcache at the same time. The usage time is around 9s.
Because the link memcache is closed every time, to see if closing the link leads to slow down, I wrote another program:
 
<?php 
include_once("gettime.php"); 
$btime = getmicrotime(); 
// The connection  
$mem_host = "192.168.0.134"; 
$mem_port = "11311"; 
$timeout = 3600; 
$i = 0; 
$cnt = 1000; 
while ($i < $cnt) { 
$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++; 
} 
// Close the connection  
$etime = getmicrotime(); 
$runTime = round($etime - $btime, 4); 
echo "runTime: ".$runTime."\r\n<br>"; 
?> 

It takes about 0.9s, which is not much slower than one connection.
To determine whether the trigger is slow or http_put is slow, create a temporary table
tmp_mytable, the table structure is as follows:
 
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; 

Modify the trigger again, as follows:
 
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; 
$$ 

Once again, php was used to add 1000 records to MySQL, and the consumption time was around 0.7s, which proved that the efficiency consumption was slow at http_put, that is, mysql-udf-http.
I wonder if there are any mistakes in my test. Please also ask those who are using mysql-udf-http, or those who have studied mysql-udf-http.

Related articles: