High concurrency Simple solution ———— redis Queue Cache +mysql Bulk Warehousing

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 warehousing and direct storage performance differences"
• 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.

Done, here is a simple implementation of the small drag process

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 存储引擎自然是只支持select insert 没有索引的archive . 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 主键采用bigint，自增即可 .
• Consider the log system 以写为主，统计采用离线计算，字段均不要出现索引 , 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/***************************************************************************** Gets the call log that is stored in the Redis queue. * $Id $**** ***********************************************************************///Get Info$interface _info=$_get[' Info '];//Deposit to Redis queue$redis=NewRedis ();$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 Redis message queue script, splicing SQL, bulk storage. * @update 2015-11-07 Add failure Message Queue rollback mechanism **///Init Redis$redis _xx=NewRedis ();$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 storageif($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:"; EchoJson_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 **///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 Inbound//cleanup 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){    EchoJson_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.

High concurrency Simple solution ———— redis Queue Cache +mysql Bulk Warehousing