Mysql-udf-http Efficiency Test note

Source: Internet
Author: User
Tags php mysql

I can see the post on "http/rest client" on the blog of Zhang banquet. I will skip all the installation steps. Next I will go directly to the test stage: virtual machine.
Copy codeThe Code is as follows:
[Root @ localhost ~] # Uname-
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 partition:
Copy codeThe Code is as follows:
[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:
Copy codeThe Code is as follows:
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 Operations Program:
Copy codeThe Code is as follows:
<? Php
$ Type = $ _ GET ['type'];
Print_r ($ _ GET );
Include_once ("gettime. php ");
$ Btime = getmicrotime ();
$ Loop_cnt = 1000; // number of cycles
$ Db_host = '2017. 0.0.1 ';//
$ Db_user = 'suns ';//
$ Db_pass = '20140901 ';//
$ 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
$ I = 1;
While ($ I <= $ loop_cnt ){
$ Title = "jkjkjkjkjkjkjkjkjkjkjkjk ";
$ 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> ";
?>

Execute php to connect to MySQL separately, and add 1000 records for a single connection: About S
Php program for memcache operations:
Copy codeThe Code is as follows:
<? Php
Include_once ("gettime. php ");
$ Btime = getmicrotime ();
// Zookeeper APIs
$ 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 ("cocould 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 ++;
}

// Skip
$ Etime = getmicrotime ();
$ RunTime = round ($ etime-$ btime, 4 );
Echo "runTime:". $ runTime. "\ r \ n <br> ";
?>

Add 1000 records for a single connection, which takes about seconds,
Create a trigger:
Copy codeThe Code is 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
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 a php update memcache for the trigger. The Code is as follows:
Copy codeThe Code is as follows:
<? Php
$ Id = $ _ GET ['id'];
$ Type = $ _ GET ['type'];
$ Json_data = $ _ GET ['data'];
Var_dump ($ _ GET );
// Zookeeper APIs
$ Mem_host = "192.168.0.134 ";
$ Mem_port = "11211 ";
$ Timeout = 3600;
$ Mem = new Memcache;
$ Mem-> connect ($ mem_host, $ mem_port) or die ("cocould 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, which takes about 9 s,
Because memcache links are closed every time, it is slow to see if the link is closed, and another program is written:
Copy codeThe Code is as follows:
<? Php
Include_once ("gettime. php ");
$ Btime = getmicrotime ();
// 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 ("cocould not connect! ");
$ Ret = $ mem-& gt; 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 and is not much slower than a connection.
Create a temporary table to locate whether the trigger is slow or http_put is slow.
Tmp_mytable. The table structure is as follows:
Copy codeThe Code 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:
Copy codeThe Code is 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;
$

Using php again to add 1000 records to MySQL, which takes about S, proves that the efficiency consumption is http_put, that is, mysql-udf-http is slow.
I don't know if my test is wrong? You can also use the mysql-udf-http master, or have some knowledge about mysql-udf-http.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.