Reprint: "High Concurrency simple solution | Cui Small pull "redis queue cache + MySQL bulk storage + PHP offline integration

Demand background: There is a call for statistical log storage and statistical requirements, which requires storage in mysql; the peak of stored data can reach 10 million per day. The bottleneck is that the direct warehousing and concurrency are too high, which may dry up mysql.

problem analysis
Thinking: During the evolution of the application website architecture, applying the latest framework and tool technology is certainly the best choice; however, if you can propose a simple and reliable solution based on the existing framework, it is not a kind of self-improvement try.

solve:

Question 1: It is better to require logs to be stored in the database; however, mysql can not be directly stored in the database, and there is no problem in batch storage, done. [Reference articles on performance differences between batch storage and direct storage]

Problem two: high-concurrency message queues are required for batch storage, and it is decided to use redis list simulation to achieve rollback.

Question three: After all, the amount of logs is large. It is enough to save the last 30 items. I decided to write an offline statistics and cleanup script with php.

done, the following is a simple implementation process

One: design database tables and storage
Considering that the log system has more performance on the database, and the stability and security are not as high, the storage engine naturally only supports archives with select inserts and no indexes. If you do need to update, you can also use myISAM.

Considering that log is all data recorded in real time, the number may be huge, and the big key is bigint, which can be increased automatically.

Considering that the log system is mainly write-based, statistics are calculated offline, and no indexes should appear in the field, because on the one hand, it may affect the efficiency of inserting data, and in addition, it will cause a deadlock when reading and affect writing data.

Two: redis stores data to form a message queue
Because of high concurrency, it is as simple and straightforward as possible to code.

<? php
/ ************************************************* **************************
*
* The obtained call log is stored in the redis queue.
* $ Id $
*
************************************************** ************************ /

/ **
* @file saveLog.php
* @date 2015/11/06 20:47:13
* @author: cuihuan
* @version $ Revision $
* @brief
*
** /

// Get info
$ interface_info = $ _GET [‘info‘];

// Store in redis queue
$ redis = new Redis ();
$ redis-> connect (‘xx‘, 6379);
$ redis-> auth ("password");

// Add the time stamp to the queue
$ now_time = date ("Y-m-d H: i: s");
$ redis-> rPush ("call_log", $ interface_info. "%". $ now_time);
$ redis-> close ();


/ * vim: set ts = 4 sw = 4 sts = 4 tw = 100 * /
?>

Three: batch storage of data regularly.
Read the data in the redis message queue regularly, and put them in batches.

<? php
/ **
* Get the scripts in the redis message queue, splice sql, and put them in the batch
* @update 2015-11-07 Add a failed message queue rollback mechanism
*
* @Author: cuihuan
* 2015-11-06
* * /

// init redis
$ redis_xx = new Redis ();
$ redis_xx-> connect (‘ip‘, port);
$ redis_xx-> auth ("password");

// Get the length of the existing message queue
$ count = 0;
$ max = $ redis_xx-> lLen ("call_log");

// Get the content of the message queue, splice sql
$ insert_sql = "insert into fb_call_log (` interface_name`, `createtime`) values";

// Roll back the array
$ roll_back_arr = array ();

while ($ count <$ max) {
$ log_info = $ redis_cq01-> lPop ("call_log");
$ roll_back_arr = $ log_info;
if ($ log_info == ‘nil’ ||! isset ($ log_info)) {
$ insert_sql. = ";";
break;
}

// Cut out time and info
$ log_info_arr = explode ("%", $ log_info);
$ insert_sql. = "(‘ ". $ log_info_arr [0]." ‘,‘ ". $ log_info_arr [1]." ‘),";
$ count ++;
}

// Determine the existence of data, batch storage
if ($ count! = 0) {
$ link_2004 = mysql_connect (‘ip: port’, ‘user’, ‘password’);
if (! $ link_2004) {
die ("Could not connect:". mysql_error ());
}

$ crowd_db = mysql_select_db (‘fb_log’, $ link_2004);
$ insert_sql = rtrim ($ insert_sql, ","). ";";
$ res = mysql_query ($ insert_sql);

// Output storage log and storage result;
echo date ("Y-m-d H: i: s"). "insert". $ count. "log info result:";
echo json_encode ($ res);
echo "</br> \ n";

// Rollback failed after database insertion
if (! $ res) {
foreach ($ roll_back_arr as $ k) {
$ redis_xx-> rPush ("call_log", $ k);
}
}

// release the connection
mysql_free_result ($ res);
mysql_close ($ link_2004);
}

// release redis
$ redis_cq01-> close ();
?>

Four: Offline sky-level statistics and data cleaning script
? php
/ **
* static log: Daily offline statistical code log and log deleted five days ago
*
* @Author: cuihuan
* 2015-11-06
* * /

// Offline statistics
$ link_2004 = mysql_connect (‘ip: port’, ‘user’, ‘pwd’);
if (! $ link_2004) {
die ("Could not connect:". mysql_error ());
}

$ crowd_db = mysql_select_db (‘fb_log’, $ link_2004);

// Statistics yesterday's data
$ day_time = date ("Y-m-d", time ()-60 * 60 * 24 * 1);
$ static_sql = "get sql";

$ res = mysql_query ($ static_sql, $ link_2004);

// Get the results into the library

// Clean up data from 15 days ago
$ before_15_day = date ("Y-m-d", time ()-60 * 60 * 24 * 15);
$ delete_sql = "delete from xxx where createtime <‘ ". $ before_15_day." ‘";
try {
$ res = mysql_query ($ delete_sql);
} catch (Exception $ e) {
echo json_encode ($ e). "\ n";
echo "delete result:". json_encode ($ res). "\ n";
}

mysql_close ($ link_2004);
?>

Five: code deployment
It is mainly deployed, the call of batch storage scripts and day-level statistics scripts, and crontab runs routinely.

# Batch storage script
* / 2 * * * * / home / cuihuan / xxx / lamp / php5 / bin / php /home/cuihuan/xxx/batchLog.php >> / home / cuihuan / xxx / batchlog.log

# Day-level statistics script
0 5 * * * / home / cuihuan / xxx / php5 / bin / php /home/cuihuan/xxx/staticLog.php >> / home / cuihuan / xxx / staticLog.log

 

Summary: Compared with other complicated ways to handle high concurrency, this solution is simple and effective: through redis cache compression, mysql batch storage to solve database bottlenecks, offline calculation to solve statistical data, and regular cleaning to ensure the size of the library.

 

Reprinted URL: 1190000004136250

Reprinted: [Simple solution for high concurrency | Reliable Cui Xiaoli] Redis queue cache + mysql batch storage + php offline integration