Background: There is a call statistics log storage and statistics requirements, requiring storage in mysql; The peak storage data can reach tens of millions per day. The bottleneck lies in the high concurrency of direct database entry, which may dry mysql down.

Problem analysis

Consider: In the evolution of web architecture, it is best to use the latest framework and tool technology; However, it is an attempt to improve yourself if you can come up with a simple and reliable solution based on the existing framework. Solution:

  • Problem 1: the best log storage requirements; However, mysql does not support batch loading, done. [Reference article on performance difference between batch storage and direct storage]

  • Problem two: Batch entry requires a high concurrency message queue, decided to use redis List simulation implementation, and convenient rollback.

  • Problem three: After all, there is a large amount of logs, so it is enough to save the latest 30 logs, so I decide to write an offline statistics and cleaning script with PHP.

Done, the following is a simple implementation of small drag

One: design database tables and storage

  • Considering that the log system has more performance on the database, stability and security is not so high, the storage engine naturally only supports select INSERT without index archive. If you do have an update requirement, you can also use myISAM.

  • Considering that log is all data recorded in real time, the number may be huge, the primary key is bigINT, increment can be.

  • Considering that the log system is mainly write-based and the statistics are calculated offline, the index of fields should not appear, because on the one hand, the efficiency of data insertion may be affected, and on the other hand, deadlock may occur during reading, affecting data write.

Two: Redis stores data to form message queues

Because of the high concurrency, as simple as possible, direct, code.

connect('xx', 6379);
$redis->auth("password");

$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 is stored regularly and in batches.

The data in redis message queue is read regularly and stored in batches.

connect('ip', port);
$redis_xx->auth("password");


$count = 0; $max = $redis_xx->lLen("call_log");


$insert_SQL = "insert into fb_call_log (interface_name, createtime) values ";


$roll_back_arr = array();


while ($count lPop("call_log"); $roll_back_arr = $log_info; if ($log_info == 'nil' || ! isset($log_info)) { $insert_sql .= ";" ; break; }


$log_info_arr = explode("%",$log_info); $insert_sql .= " ('".$log_info_arr[0]."','".$log_info_arr[1]."'),"; $count++; }


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 the database log and the database result; echo date("Y-m-d H:i:s")."insert ".$count." log info result:"; echo json_encode($res); echo "n";


// Database insert failed rollback if(! $res){ foreach($roll_back_arr as $k){ $redis_xx->rPush("call_log", $k); }}


Mysql_free_result ($res); mysql_close($link_2004); }


Redis $redis_cq01->close(); ? >



Four: offline day level statistics and data clearing scripts

? PHP /** * static log: daily offline statistics code log and delete five days ago ** @author :[email protected] * 2015-11-06 /


$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);


$day_time = date(" y-m-d ", time() -60 60 24 * 1); $day_time = date(" y-m-d ", time() -60 60 24 * 1); $static_sql = "get sql";


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


// Get the result into the database


$before_15_day = date(" y-m-d ", time() -60 60 24 15); $before_15_day = date(" y-m-d ", time() -60 60 24 15); $delete_sql = "delete from xxx where createtime

Five: Code deployment

It is mainly used for deployment, batch entry script call and day level statistics script, crontab routine running.

Batch loading scripts/ 2,   / home/cuihuan/XXX/lamp/php5 / bin/PHP/home/cuihuan/XXX/batchLog. PHP > > / home/cuihuan/XXX/batchLog log# script 0 5 day level statistics  * /home/cuihuan/xxx/php5/bin/php/home/cuihuan/xxx/staticLog.php>>/home/cuihuan/xxx/staticLog.log

Summary: Compared to other complex ways to deal with high concurrency, this solution is simple and effective: redis cache stress, mysql batch load to solve database bottlenecks, offline computing to solve statistics, and regular cleaning to ensure the size of the library.