POALRDB performance experience based on smart home scenarios

Polardb is a relational database developed by Ali Cloud, fully compatible with mysql, and its performance is 6 times, with high throughput, low delay and other characteristics;

In this test, the performance of PolarDB is experienced by simulating the terminal scene that controls the smart home switch.

1. Environment construction

1.1 polardb configuration

First of all, we need to purchase PolarDB. The price varies according to the performance of processor and memory. The lowest configuration is the 2-core 4G specification of Polar.

Once you buy, you can connect through https://polardb.console.aliyun.com/, enter the relevant configuration polardb console; Select a region node [such as East China 1], create an instance, and then select a cluster list to create a cluster. POLARDB (MySQL 5.6), payment type and expiration time are monthly and one month respectively. The initial account and password can be used when the system is created. On the cluster page, click the management connection on the right to enter the cluster information page. You can see the public network connection address, which starts with the user account name, followed by the port number 3306 commonly used by mysql.

Cluster connection address (public) : mypolardb.mysql.polardb.rds.aliyuncs.com: 3306, here is my account is configured to mypolardb, so begin with mypolardb here;

In the whitelist list above, click the pen change button at the back to add the ECS server address we used to connect to the database. Here my server address is 47.94.219.162.

1.2 ECS Server Configuration:

To test the results on a Web page, install the nginx server using the following command line:

sudo apt-get install nginx

After installing nGINx, enter the ECS address in the browser. The nginx welcome screen is displayed, indicating a successful installation.

To install PHP, enter the following command in the console window:

sudo apt-get install php*

Once installed, type the following command to modify the nginx configuration:

sudo vi /etc/nginx/sites-available/default

Open the following four paragraphs in the file:

location ~ \.php$ {

include snippets/fastcgi-php.conf;

Fastcgi_pass Unix: / run/PHP/php7.0 – FPM. The sock.

}

Save and exit, then restart nginx:

service nginx restart

/var/ WWW/HTML = /var/ WWW/HTML = /var/ WWW/HTML = /var/ WWW/HTML

vi phpinfo.php

If you visit this file in your browser and see PHP information, the PHP installation is successful.

Enter the domain name console, select the domain name to resolve, click Resolve connection, add A record A, the host record an easy to remember name, such as polardb, record value select ECS IP address, such as: After the domain name is created, wait 5 minutes to access the new domain name in the browser, such as polardb.xunyun17.xyz. If you can also see the welcome interface of nginx, it indicates that the domain name has been resolved successfully.

Since this smart home scenario uses mysql development experience, you also need to install mysql in Ubuntu and run the following command to install it:

sudo apt install mysql-client

After installing mysql, create a shell script to test whether you can connect to Polardb:

vi p.sh

mysql -h mypolardb.mysql.polardb.rds.aliyuncs.com –user=mypolardb –password=mypolardb

./p.sh

If you can see the mysql prompt and the mysql > sign prompt after running the script, it indicates that the connection is successful.

2, development,

Create a database and a table to store the current information of the living room and equipment:

./p.sh

create database keting;

use keting;

CREATE TABLE status (devname VARCHAR(20), value int(4),changetime DATETIME);

insert into status(devname,value,changetime) values

(‘TV0001’,0,NOW()),

(‘LAMP01’,1,NOW()),

(‘CURN01’,1,NOW());

Then, through the MYSQL C language API interface, write a program to read the device status in the status table:

////////////////////////////////////////query lamp device

sprintf(str_cmd,”select value from status where devname=’LAMP01′”);

//printf(“\n%s\n.”,str_cmd);

no_res = 0;

if(mysql_query(conn, str_cmd))

{

printf(“<<<<<<<\n”);

exit(1);

}

res = mysql_use_result(conn);

if(no_res==0){

while((row = mysql_fetch_row(res)) ! = NULL)

{

// printf(“%s \n”, row[0]);

if(selno==17) printf(“%s”, row[0]);

else printf(“%s\t %s\t %s”, row[0],row[1],row[2]);

}

}

mysql_free_result(res);

sprintf(str_cmd,”select value from status where devname=’CURN01′”);

//printf(“\n%s\n.”,str_cmd);

no_res = 0;

The next step is to call this code with a PHP script to query the device status and display it on a web page

Use the following PHP code:

if(! empty($_REQUEST[‘arguments’])){

exec(‘./mysqlv ‘.$_REQUEST[‘arguments’].’ LAMP03 1′,$result);

//print_r($result);

//echo $result[0][0].'<br>’;

//echo $result[0][1].'<br>’;

//echo $result[0][2];

if($result[0][0]==0) $s1 = ‘OFF’;

else $s1 = ‘ON’;

if($result[0][1]==0) $s2 = ‘OFF’;

else $s2 = ‘ON’;

if($result[0][2]==0) $s3 = ‘OFF’;

else $s3 = ‘ON’;

if($_REQUEST[‘arguments’]==17) echo “<center><table border=\”1\”>

<tr>

The < th > < / th >

< th > light < / th >

The curtain of the < th > < / th >

</tr>

<tr>

<td>”.$s1.”</td>

<td>”.$s2.”</td>

<td>”.$s3.”</td>

</tr>

</table></center>”;

Open your browser and run the PHP script. Click the “Run” button and you will see a table of device status displayed on the page, including whether the TV, lights and curtains are on or off.

Next, use C language to write a socket listener, used to monitor the subsequent remote device to the smart home update request, and achieve the status update, the code is as follows:

n = read(sock,buffer,255);

if (n < 0) {

perror(“ERROR reading from socket”);

exit(1);

}

strncpy(devname,buffer,6);

devname[6]=0;

devval=buffer[6]-‘0’;

Memset (CMDSTR, 0256);

sprintf(cmdstr,”./mysqlv 15 %s %i”,devname,devval);

printf(“%s.\n”,cmdstr);

system(cmdstr);

printf(“Here is the message: %s\n”,buffer);

n = write(sock,”I got your message”,18);

Once the server listener is written, you can start writing programs that simulate the device side:

The socket function of C language is also used to write, and the command line data is sent to the server:

/* Now connect to the server */

if (connect(sockfd, (struct sockaddr*)&serv_addr, sizeof(serv_addr)) < 0) {

perror(“ERROR connecting”);

exit(1);

}

/* Now ask for a message from the user, this message

* will be read by server

* /

printf(“Please enter the message: “);

bzero(buffer,256);

strcpy(buffer, argv[3]);

//fgets(buffer,255,stdin);

/* Send message to the server */

n = write(sockfd, buffer, strlen(buffer));

if (n < 0) {

perror(“ERROR writing to socket”);

exit(1);

}

Finally, we need to modify our mysqlv.c database call code to change the command-line arguments to the database:

sprintf(str_cmd,”update status set value=%d where devname=\’%s\'”,atoi(argc[3]),argc[2]);

3, test,

When both the server and client programs are complete, you can test them; First on the server, run the listener in the background with the following command line:

./s&

Then run the emulated device program locally. For example, if we want to run the command to turn the lights on, use the following command line:

. / c 47.94.219.162 8266 LAMP011

If the command output from the server is received, the message is successfully sent.

Open the browser, run the PHP script, click the “Run” button, and you can see the table information of the latest device status on the page.

Conclusions and Suggestions: It can be seen that the new polarDB database is compatible with mysql, and it is convenient to operate;

If ali Cloud products can also provide related restful API interface, it will make the development more convenient and fast;

Appendix and screenshots

Operating mysqLV for PolarDB

#include <mysql/mysql.h>

#include <stdio.h>

#include <stdlib.h>

#include <string.h>

int main(int argv,char**argc)

{

MYSQL *conn;

MYSQL_RES *res;

MYSQL_ROW row;

Char server [] = “47.98.24.120”;

char user[] = “mypolardb”;

char password[] = “mypolardb”;

char database[] = “mysql”;

char str_cmd[250];

int no_res = 0;

int selno=0;

if(argv<2) {

printf(“mysqlv funcno.\n”);

return 0;

}

conn = mysql_init(NULL);

if(! mysql_real_connect(conn, server, user, password, database, 0, NULL, 0))

{

printf(“>>>>>>>\n”);

exit(1);

}

selno = atoi(argc[1]);

// printf(“selno:%d\n<br>”,selno);

switch(selno){

case 1:

strcpy(str_cmd,”select version()”);

break;

case 2:

strcpy(str_cmd,”select current_date”);

break;

case 3:

strcpy(str_cmd,”show databases”);

break;

case 4:

strcpy(str_cmd,”create database keting”);

no_res = 1;

break;

case 5:

strcpy(str_cmd,”drop database keting”);

no_res = 1;

break;

case 6:

strcpy(str_cmd,”create database woshi”);

no_res = 1;

break;

case 7:

strcpy(str_cmd,”drop database woshi”);

no_res = 1;

break;

case 8:

strcpy(str_cmd,”create database chufang”);

no_res = 1;

break;

case 9:

strcpy(str_cmd,”drop database chufang”);

no_res = 1;

break;

case 10:

strcpy(str_cmd,”use keting”);

no_res = 1;

break;

case 11:

strcpy(str_cmd,”use woshi”);

no_res = 1;

break;

case 12:

strcpy(str_cmd,”use chufang”);

no_res = 1;

break;

#if 0

case 13:

strcpy(str_cmd,”show tables”);

//no_res = 1;

break;

/*table operate for keting*/

case 14:

strcpy(str_cmd,”CREATE TABLE prop (devname VARCHAR(20), value int(4))”);

//no_res = 1;

break; n

#endif

case 15:

strcpy(str_cmd,”describe prop”);

//no_res = 1;

break;

case 16:

strcpy(str_cmd,”drop table if exists prop”);

//no_res = 1;

break;

}

if(selno==13){

strcpy(str_cmd,”use keting”);

if(mysql_query(conn, str_cmd))

{

printf(“<<<<<<<\n”);

exit(1);

}

strcpy(str_cmd,”show tables”);

no_res = 0;

}

if(selno==14){

strcpy(str_cmd,”use keting”);

if(mysql_query(conn, str_cmd))

{

printf(“<<<<<<<\n”);

exit(1);

}

strcpy(str_cmd,”select * from prop;” );

no_res = 0;

}

if(selno==15){

strcpy(str_cmd,”use keting”);

if(mysql_query(conn, str_cmd))

{

printf(“<<<<<<<\n”);

exit(1);

}

// sprintf(str_cmd,”insert into prop(devname,value,changetime) values (\’%s\’,%i,NOW())”,argc[2],atoi(argc[3]));

sprintf(str_cmd,”update status set value=%d where devname=\’%s\'”,atoi(argc[3]),argc[2]);

printf(“\n%s\n.”,str_cmd);

no_res = 1;

}

if(selno==16){

strcpy(str_cmd,”use keting”);

if(mysql_query(conn, str_cmd))

{

printf(“<<<<<<<\n”);

exit(1);

}

sprintf(str_cmd,”delete from prop where devname=’my_dev'”);

printf(“\n%s\n.”,str_cmd);

no_res = 1;

}

if(selno==17){

strcpy(str_cmd,”use keting”);

if(mysql_query(conn, str_cmd))

{

printf(“<<<<<<<\n”);

exit(1);

}

////////////////////////////////////////query tv device

sprintf(str_cmd,”select value from status where devname=’TV0001′”);

//printf(“\n%s\n.”,str_cmd);

no_res = 0;

if(mysql_query(conn, str_cmd))

{

printf(“<<<<<<<\n”);

exit(1);

}

res = mysql_use_result(conn);

if(no_res==0){

while((row = mysql_fetch_row(res)) ! = NULL)

{

// printf(“%s \n”, row[0]);

if(selno==17) printf(“%s”, row[0]);

else printf(“%s\t %s\t %s”, row[0],row[1],row[2]);

}

}

mysql_free_result(res);

////////////////////////////////////////query lamp device

sprintf(str_cmd,”select value from status where devname=’LAMP01′”);

//printf(“\n%s\n.”,str_cmd);

no_res = 0;

if(mysql_query(conn, str_cmd))

{

printf(“<<<<<<<\n”);

exit(1);

}

res = mysql_use_result(conn);

if(no_res==0){

while((row = mysql_fetch_row(res)) ! = NULL)

{

// printf(“%s \n”, row[0]);

if(selno==17) printf(“%s”, row[0]);

else printf(“%s\t %s\t %s”, row[0],row[1],row[2]);

}

}

mysql_free_result(res);

sprintf(str_cmd,”select value from status where devname=’CURN01′”);

//printf(“\n%s\n.”,str_cmd);

no_res = 0;

}

//printf(“finish! \n”);

if(mysql_query(conn, str_cmd))

{

printf(“<<<<<<<\n”);

exit(1);

}

res = mysql_use_result(conn);

if(no_res==0){

if(selno==14)

printf(“DEVICE VALUE TIMESTAMP<br>”);

while((row = mysql_fetch_row(res)) ! = NULL)

{

// printf(“%s”, row[0]);

if(selno==17) printf(“%s\t “, row[0]);

else printf(“%s\t %s\t %s”, row[0],row[1],row[2]);

}

}

mysql_free_result(res);

mysql_close(conn);

/ *

printf(“finish! \n”);

printf(“finish! <br>”);

* /

return 0;

}

Server. c Complete program that the server listens to:

#include <stdio.h>

#include <stdlib.h>

#include <netdb.h>

#include <netinet/in.h>

#include <string.h>

void doprocessing (int sock);

int main( int argc, char *argv[] ) {

int sockfd, newsockfd, portno, clilen;

char buffer[256];

struct sockaddr_in serv_addr, cli_addr;

int n, pid;

/* First call to socket() function */

sockfd = socket(AF_INET, SOCK_STREAM, 0);

if (sockfd < 0) {

perror(“ERROR opening socket”);

exit(1);

}

/* Initialize socket structure */

bzero((char *) &serv_addr, sizeof(serv_addr));

portno = 8266; / / 5001;

serv_addr.sin_family = AF_INET;

serv_addr.sin_addr.s_addr = INADDR_ANY;

serv_addr.sin_port = htons(portno);

/* Now bind the host address using bind() call.*/

if (bind(sockfd, (struct sockaddr *) &serv_addr, sizeof(serv_addr)) < 0) {

perror(“ERROR on binding”);

exit(1);

}

/* Now start listening for the clients, here

* process will go in sleep mode and will wait

* for the incoming connection

* /

listen(sockfd,5);

clilen = sizeof(cli_addr);

while (1) {

newsockfd = accept(sockfd, (struct sockaddr *) &cli_addr, &clilen);

if (newsockfd < 0) {

perror(“ERROR on accept”);

exit(1);

}

/* Create child process */

pid = fork();

if (pid < 0) {

perror(“ERROR on fork”);

exit(1);

}

if (pid == 0) {

/* This is the client process */

close(sockfd);

doprocessing(newsockfd);

exit(0);

}

else {

close(newsockfd);

}

} /* end of while */

}

void doprocessing (int sock) {

int n;

char buffer[256];

char cmdstr[256];

char devname[10];

int devval=0;

bzero(buffer,256);

n = read(sock,buffer,255);

if (n < 0) {

perror(“ERROR reading from socket”);

exit(1);

}

strncpy(devname,buffer,6);

devname[6]=0;

devval=buffer[6]-‘0’;

Memset (CMDSTR, 0256);

sprintf(cmdstr,”./mysqlv 15 %s %i”,devname,devval);

printf(“%s.\n”,cmdstr);

system(cmdstr);

printf(“Here is the message: %s\n”,buffer);

n = write(sock,”I got your message”,18);

if (n < 0) {

perror(“ERROR writing to socket”);

exit(1);

}

if(buffer[0]==’z’) exit(1);

}

Client. C Complete program of client simulation device:

#include <stdio.h>

#include <stdlib.h>

#include <netdb.h>

#include <netinet/in.h>

#include <string.h>

int main(int argc, char *argv[]) {

int sockfd, portno, n;

struct sockaddr_in serv_addr;

struct hostent *server;

char buffer[256];

if (argc < 3) {

fprintf(stderr,”usage %s hostname port devsw\n”, argv[0]);

exit(0);

}

portno = atoi(argv[2]);

/* Create a socket point */

sockfd = socket(AF_INET, SOCK_STREAM, 0);

if (sockfd < 0) {

perror(“ERROR opening socket”);

exit(1);

}

server = gethostbyname(argv[1]);

if (server == NULL) {

fprintf(stderr,”ERROR, no such host\n”);

exit(0);

}

bzero((char *) &serv_addr, sizeof(serv_addr));

serv_addr.sin_family = AF_INET;

bcopy((char *)server->h_addr, (char *)&serv_addr.sin_addr.s_addr, server->h_length);

serv_addr.sin_port = htons(portno);

/* Now connect to the server */

if (connect(sockfd, (struct sockaddr*)&serv_addr, sizeof(serv_addr)) < 0) {

perror(“ERROR connecting”);

exit(1);

}

/* Now ask for a message from the user, this message

* will be read by server

* /

printf(“Please enter the message: “);

bzero(buffer,256);

strcpy(buffer, argv[3]);

//fgets(buffer,255,stdin);

/* Send message to the server */

n = write(sockfd, buffer, strlen(buffer));

if (n < 0) {

perror(“ERROR writing to socket”);

exit(1);

}

/* Now read server response */

bzero(buffer,256);

n = read(sockfd, buffer, 255);

if (n < 0) {

perror(“ERROR reading from socket”);

exit(1);

}

printf(“%s\n”,buffer);

return 0;

}

P. p. script results show the complete program;

<html>

<head>

</head>

<body>

if(! empty($results)){

// echo $results;

}

? >

Xiaobai smart Home

<form method=”post” action=””>

<select name=”arguments”>

</select>

</form>

</body>

</html>

if(! empty($_REQUEST[‘arguments’])){

exec(‘./mysqlv ‘.$_REQUEST[‘arguments’].’ LAMP03 1′,$result);

//print_r($result);

//echo $result[0][0].'<br>’;

//echo $result[0][1].'<br>’;

//echo $result[0][2];

if($result[0][0]==0) $s1 = ‘OFF’;

else $s1 = ‘ON’;

if($result[0][1]==0) $s2 = ‘OFF’;

else $s2 = ‘ON’;

if($result[0][2]==0) $s3 = ‘OFF’;

else $s3 = ‘ON’;

if($_REQUEST[‘arguments’]==17) echo “<center><table border=\”1\”>

<tr>

The < th > < / th >

< th > light < / th >

The curtain of the < th > < / th >

</tr>

<tr>

<td>”.$s1.”</td>

<td>”.$s2.”</td>

<td>”.$s3.”</td>

</tr>

</table></center>”;

}

? >