用Matomo/Piwik定时导入日志实现统计网站访问情况

安装并配置piwik以后,就需要导入access.log中的访问数据了,

首先需要一个导入日志的脚本,这个脚本运行时,需要把当前access.log中的日志导入pwiki,同时再把导入过的备份到别的地方。脚本如下:

#!/bin/sh

LOG_DIR=/var/log/nginx

LOG_ACCESS=LOG_DIR/access.log
LOG_IMPORTING=LOG_DIR/access.log.importing
LOG_IMPORTED=LOG_DIR/imported.log

# move current log file to a tmp file, and restart log server
mvLOG_ACCESS LOG_IMPORTING 2>/dev/null
kill -USR1(cat /var/run/nginx.pid 2>/dev/null) 2>/dev/null

# import temp log file
/www/piwik/misc/log-analytics/import_logs.py \
    --idsite=1 \
    --url=piwik.rehiy.com \
    --log-format-name=ncsa_extended \
    LOG_IMPORTING

# join importing log to imported
catLOG_IMPORTING >>LOG_IMPORTED

# remove temp file
rmLOG_IMPORTING 2>/dev/null

最后还需要配置cron每个小时运行一次导入脚本,再配置logrotate不要处理access.log。

手动删除Matomo/Piwik历史日志(MySQL删除大量数据)

不知不觉间,Piwik数据的容量已经达到500GB,虽然并没有感觉到性能有什么影响,但也是时候考虑如何清理其最占空间的访问记录数据了。于是决定拿备份库演练一番:

首先想到的是使用后台的自动清理功能,但是发现其每次10000条的删除能力,对于此等量级的数据可谓是杯水车薪。最后只能翻看源码,得出最基本的两条删除语句。

DELETE FROM `wk_log_conversion` WHERE server_time < '2021-01-01 00:00:00';
DELETE FROM `wk_log_visit` WHERE visit_last_action_time < '2021-01-01 00:00:00';
DELETE FROM `wk_log_link_visit_action` WHERE server_time < '2021-01-01 00:00:00';

至于 wk_log_action 这个表里是被引用的内容,下次遇到相同的引用内容,仍然需要添加一条记录。不删除反而可以确保idaction的唯一性。

至此,我们将遇到第二个问题:

如果要从InnoDB大表中删除许多行,则可能会超出表的锁定表大小InnoDB。也许删除500G的数据需要8个小时左右。

为了避免这个问题,或者只是为了最小化表保持锁定的时间,官方给出以下策略(根本不使用 DELETE):

  • 选择不要删除的行到与原始表具有相同结构的空表中

    INSERT INTO t_copy SELECT * FROM t WHERE ... ;
  • 使用RENAME TABLE以原子移动原始表的方式进行,并重新命名拷贝到原来的名称

    RENAME TABLE t TO t_old, t_copy TO t;
  • 删除原始表

    DROP TABLE t_old;

最终,我们优化查询如下

CREATE TABLE wk_log_conversion_copy LIKE wk_log_conversion;
INSERT INTO wk_log_conversion_copy SELECT * FROM wk_log_conversion WHERE server_time >= '2021-01-01 00:00:00';
RENAME TABLE wk_log_conversion TO wk_log_conversion_old, wk_log_conversion_copy TO wk_log_conversion;

CREATE TABLE wk_log_visit_copy LIKE wk_log_visit;
INSERT INTO wk_log_visit_copy SELECT * FROM wk_log_visit WHERE visit_last_action_time >= '2021-01-01 00:00:00';
RENAME TABLE wk_log_visit TO wk_log_visit_old, wk_log_visit_copy TO wk_log_visit;

CREATE TABLE wk_log_link_visit_action_copy LIKE wk_log_link_visit_action;
INSERT INTO wk_log_link_visit_action_copy SELECT * FROM wk_log_link_visit_action WHERE server_time >= '2021-01-01 00:00:00';
RENAME TABLE wk_log_link_visit_action TO wk_log_link_visit_action_old, wk_log_link_visit_action_copy TO wk_log_link_visit_action;

DROP TABLE wk_log_conversion_old, wk_log_visit_old, wk_log_link_visit_action_old;

官方文档链接 https://matomo.org/faq/how-to/faq_20184/