"High concurrency Simple Solution" Redis queue cache + MySQL bulk warehousing + PHP offline integration

Original address: 1190000004136250
Requirement background: There is a 调用统计日志存储和统计需求 requirement to store the data in MySQL, and the bottleneck is that the peak of storage can reach the daily average 直接入库并发太高，可能会把mysql干垮 .

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.

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/***************************************************************************** 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 Span class= "Hljs-bullet" >***///get info$interface_info = $_get[  ' info '; //into Redis queue $redis = new Redis (); $redis->connect ( ' xx ', 6379); $redis->auth ( "password") ; //plus timestamp in 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/** * Gets the Redis message queue script, stitching SQL, bulk storage. *@update 2015-11-07 add failed Message Queuing 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 Sql$insert_sql ="INSERT into Fb_call_log (' interface_name ', ' createtime ') values";Rollback 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 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 storage 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 in storage slightly //cleanup data before 15 days $before_15_day = Date ("y-m-d", Time ()-$ * *    ); $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 statistical 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 + PHP offline integration