Redis Queue cache + MySQL bulk warehousing + PHP offline integration

Problem analysis

Thinking: Applying the latest framework and tool technology is the best choice in the evolution of the application site architecture, but if it can be现有的框架的基础上提出简单可依赖的解决方案, it is not an attempt to promote yourself.

Solve:

• Problem one: The requirement log is the best storage; however, the direct storage MySQL does not carry, batch storage no problem, done. "Bulk inbound and direct inbound performance Variance reference article"

• Problem two: Batch storage needs to have high concurrent message queue, decided to use Redis list simulation implementation, and easy rollback.

• Question three: After all, the log volume is large, save the last 30 is enough, decided to write a PHP offline statistics and cleanup scripts.

One: Design database tables and storage

• Considering that the log system has more performance on the database, the stability and security is not that high The storage engine naturally only supports archives without select inserts. If you do have an update requirement, you can also use MyISAM.
  
  Given that log is all data recorded in real time, the number can be huge primary key adopts bigint, which can be increased automatically.
  
  Consider the log system is mainly written, statistics are calculated offline, and no indexes should appear in the fields

• , because on the one hand may affect the efficiency of the insertion of data, in addition to read time will cause deadlock, affecting writing data.

Two: Redis storage data Forms Message Queuing

Because of the high concurrency, as simple as possible, directly on the code.

<?php
/***************************************************************************
*
* Get to the call log, which 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 '];

Deposit to Redis queue
$redis = new Redis ();
$redis->connect (' xx ', 6379);
$redis->auth ("password");

Add time stamp to 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: Data timed batch storage.

Periodically read the data inside the Redis message queue and batch storage.

<?php
/**
* Get the scripts in Redis Message Queuing, splice SQL, bulk storage.
* @update 2015-11-07 Add 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 an existing message queue
$count = 0;
$max = $redis _xx->llen ("Call_log");

Gets the contents of the message queue, stitching the SQL
$insert _sql = "INSERT INTO Fb_call_log (' interface_name ', ' createtime ') values";

Rolling back an 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
}

Cutting 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 log and storage results;
echo Date ("Y-m-d h:i:s"). " Insert ". $count." Log info result: ";
echo Json_encode ($res);
echo "</br>\n";

Database insert failed rollback
if (! $res) {
foreach ($roll _back_arr as $k) {
$redis _xx->rpush ("Call_log", $k);
}
}

Release connection
Mysql_free_result ($res);
Mysql_close ($link _2004);
}

Release Redis
$redis _cq01->close ();
?>

Four: Offline day-level statistics and cleanup data scripts

? php
/**
* Static log: Daily offline statistics code log and delete logs 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);

Count 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 results into the library slightly

Clean up data up to 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

The main is the deployment, bulk inbound script calls and the day-level statistical script, crontab routine run.

# Bulk Inbound 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: Dealing with high concurrency relative to other complex approaches, this solution is simple and effective: through Redis cache compression, MySQL batch storage to solve the database bottleneck, offline computing to solve the statistics, through regular cleanup to ensure the size of the library.

Redis Queue cache + MySQL bulk warehousing + PHP offline integration