Redis Cache Queue +mysql +php task script scheduled batch Storage

Original address: http://blog.jobbole.com/99567/

Requirements background: There is a call to the statistical log storage and statistical requirements, the requirements stored in MySQL, storage data peaks can reach tens of millions of million, the bottleneck is the direct storage concurrency is too high, you may be able to dry MySQL.

Problem analysis

Thinking: Applying the latest framework and tool technology is the best choice in the evolution of the application site architecture, but it is not an attempt to improve yourself if you can put forward simple and dependent solutions based on existing frameworks. 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.

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.

Connect (' xx ', 6379); $redis->auth ("password"); // add time stamp to queue $now _time Date ("y-m-d h:i:s"); $redis $interface _info $now _time ); $redis-Close (); /*  */?>

Three: Data timed batch storage.

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

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($countLpop ("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); EchoN; //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 ();?>

View CodeFour: Offline day-level statistics and cleanup data scripts

?PHP/** * Static log: Daily offline statistics code log and delete five days ago Log * * @Author: [email protected]* 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 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

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 Cache Queue +mysql +php task script scheduled batch Storage