Showing Posts From

ClickHouse

Soulock
2026年05月28日
- 技术

Laravel + ClickHouse 统计数据推送飞书卡片

用 Laravel 定时任务对接 ClickHouse 日志，每小时/每天推送应用流量报告到飞书群。核心架构 ClickHouse (request_logs) → Laravel Artisan Command → 飞书 Webhook → 交互式卡片定时任务两种模式：push:feishu-stats — 每小时统计近 1h / 3h 流量 push:feishu-stats daily — 每天 0 点统计近 24h / 3d / 7dArtisan Command <?phpnamespace App\Console\Commands;use Illuminate\Console\Command; use Illuminate\Support\Facades\Http; use Carbon\Carbon; use App\Services\ClickHouseService;class PushFeishuStats extends Command { protected $signature = 'push:feishu-stats {type=hourly}'; protected $description = '推送应用访问统计数据到飞书群'; protected ClickHouseService $clickHouseService; public function __construct(ClickHouseService $clickHouseService) { parent::__construct(); $this->clickHouseService = $clickHouseService; } public function handle() { $type = $this->argument('type'); try { $type === 'daily' ? $this->handleDailyPush() : $this->handleHourlyPush(); return Command::SUCCESS; } catch (\Throwable $e) { $this->error($e->getMessage()); return Command::FAILURE; } } protected function handleHourlyPush() { $now = Carbon::now(); $oneHourAgo = $now->copy()->subHour()->toDateTimeString(); $threeHoursAgo = $now->copy()->subHours(3)->toDateTimeString(); $sql = " SELECT JSONExtractString(request_params, 'appid') AS appid, COUNTIf(timestamp >= '{$oneHourAgo}') AS count_1h, COUNT(1) AS count_3h FROM request_logs WHERE timestamp >= '{$threeHoursAgo}' GROUP BY appid HAVING count_3h > 10 ORDER BY count_3h DESC LIMIT 20 "; $rows = $this->query($sql); $this->sendToFeishu('📊 每小时应用流量监控', $rows, 'hourly'); } protected function handleDailyPush() { $now = Carbon::now(); $oneDayAgo = $now->copy()->subDay()->toDateTimeString(); $threeDaysAgo = $now->copy()->subDays(3)->toDateTimeString(); $sevenDaysAgo = $now->copy()->subDays(7)->toDateTimeString(); $sql = " SELECT JSONExtractString(request_params, 'appid') AS appid, COUNTIf(timestamp >= '{$oneDayAgo}') AS count_24h, COUNTIf(timestamp >= '{$threeDaysAgo}') AS count_3d, COUNT(1) AS count_7d FROM request_logs WHERE timestamp >= '{$sevenDaysAgo}' GROUP BY appid HAVING count_7d > 50 ORDER BY count_7d DESC LIMIT 20 "; $rows = $this->query($sql); $this->sendToFeishu('📅 每日应用流量大盘', $rows, 'daily'); } protected function query(string $sql): array { return $this->clickHouseService->getClient()->select($sql)->rows(); } protected function sendToFeishu(string $title, array $rows, string $type = 'hourly') { $webhookUrl = env('FEISHU_WEBHOOK'); if (!$webhookUrl) { $this->error('未配置 FEISHU_WEBHOOK'); return; } $elements = [ ['tag' => 'markdown', 'content' => '**🕒 生成时间：** ' . now()->format('Y-m-d H:i:s')], ['tag' => 'markdown', 'content' => '**📈 应用数量：** ' . count($rows)], ['tag' => 'hr'], ]; foreach ($rows as $index => $row) { $rank = $index + 1; $appid = $row['appid'] ?: 'unknown'; if ($type === 'hourly') { $count1h = (int) $row['count_1h']; $count3h = (int) $row['count_3h']; $avg = max(1, $count3h / 3); $rate = round(($count1h / $avg) * 100); $trend = '🟢'; $status = '正常'; if ($rate >= 200) { $trend = '🚨'; $status = '流量暴涨'; } elseif ($rate >= 150) { $trend = '🟡'; $status = '流量升高'; } elseif ($rate <= 50) { $trend = '🔵'; $status = '流量下降'; } $content = "### {$trend} TOP {$rank} · {$appid}\n\n> {$status}\n\n" . "- 1小时请求量：**" . number_format($count1h) . "**\n" . "- 3小时请求量：**" . number_format($count3h) . "**\n" . "- 当前热度：**{$rate}%**"; } else { $count24h = (int) $row['count_24h']; $count3d = (int) $row['count_3d']; $count7d = (int) $row['count_7d']; $avg = max(1, $count7d / 7); $rate = round(($count24h / $avg) * 100); $trend = $rate >= 180 ? '🚨' : ($rate >= 130 ? '🟡' : '🟢'); $content = "### {$trend} TOP {$rank} · {$appid}\n\n" . "- 24小时请求量：**" . number_format($count24h) . "**\n" . "- 近3天：**" . number_format($count3d) . "**\n" . "- 近7天：**" . number_format($count7d) . "**\n" . "- 当前热度：**{$rate}%**"; } $elements[] = ['tag' => 'markdown', 'content' => $content]; $elements[] = ['tag' => 'hr']; } $elements[] = ['tag' => 'markdown', 'content' => '⚡ Powered By Laravel + ClickHouse']; $payload = [ 'msg_type' => 'interactive', 'card' => [ 'config' => ['wide_screen_mode' => true], 'header' => [ 'title' => ['tag' => 'plain_text', 'content' => $title], 'template' => 'turquoise', ], 'elements' => $elements, ], ]; $response = Http::timeout(10)->post($webhookUrl, $payload); $response->successful() ? $this->info('飞书推送成功') : $this->error('飞书推送失败: ' . $response->body()); } }注册定时任务 // routes/console.php Schedule::command('push:feishu-stats')->hourly(); Schedule::command('push:feishu-stats daily')->dailyAt('00:00');.env 配置 FEISHU_WEBHOOK=https://open.feishu.cn/open-apis/bot/v2/hook/xxxxxxxxClickHouse 性能建议当前用 JSONExtractString(request_params, 'appid') 实时解析 JSON，数据量大时 CPU 开销高。建议在 request_logs 表冗余一个 appid String 字段，插入时直接写入，便于索引和 GROUP BY 加速。飞书卡片效果 📊 每小时应用流量监控 🕒 生成时间：2026-05-28 10:00:00 📈 应用数量：15🚨 TOP 1 · app_main > 流量暴涨 • 1小时请求量：23,412 • 3小时请求量：41,221 • 当前热度：171%🟢 TOP 2 · app_test • 1小时请求量：1,242 • 当前热度：119%比 Markdown 表格可读性高很多，支持趋势颜色标识。

Soulock
2026年05月28日
- 技术

ClickHouse system 日志表清理：TRUNCATE、关闭无用日志和 TTL 配置

ClickHouse 的 system.*_log 表在生产环境不加干预，几个月就能积累几十上百 GB。空间占用查询 SELECT database, table, formatReadableSize(sum(bytes)) size FROM system.parts GROUP BY database, table ORDER BY sum(bytes) DESC;常见爆炸表：表典型症状text_log 大量 exception 或 debug 日志trace_log 开了 profile 或查询 traceasynchronous_metric_log metrics 刷新间隔太低metric_log 运行时间过长part_log 小批量高频 insertquery_log 高频查询立即清理 TRUNCATE TABLE system.text_log; TRUNCATE TABLE system.trace_log; TRUNCATE TABLE system.asynchronous_metric_log; TRUNCATE TABLE system.metric_log; TRUNCATE TABLE system.part_log; TRUNCATE TABLE system.query_log; TRUNCATE TABLE system.latency_log; TRUNCATE TABLE system.processors_profile_log;SYSTEM FLUSH LOGS;TRUNCATE 后空间不会立刻全部回收（还有 deleted parts 和文件系统缓存），重启服务通常能完全释放： systemctl restart clickhouse-server关闭不需要的日志编辑 /etc/clickhouse-server/config.xml 或 /etc/clickhouse-server/config.d/*.xml：  <text_log remove="1"/> <trace_log remove="1"/> <metric_log remove="1"/> <asynchronous_metric_log remove="1"/> <processors_profile_log remove="1"/> <part_log remove="1"/>重启生效： systemctl restart clickhouse-server生产建议：保留 query_log，用于慢查询分析。其余视需要选择性保留。设置 TTL 限制保留天数不想完全关闭，只保留最近几天： <query_log> <database>system</database> <table>query_log</table> <flush_interval_milliseconds>7500</flush_interval_milliseconds> <ttl>event_date + INTERVAL 7 DAY DELETE</ttl> </query_log>part_log 异常：小批量写入问题 part_log 几千万行通常意味着：每条记录单独 INSERT（正确做法是批量几千到几万行） Kafka consumer batch 配置太小频繁小事务导致 parts 积累、merge 压力大查看各表的活跃 parts 数量： SELECT table, count() FROM system.parts WHERE active GROUP BY table ORDER BY count() DESC;正常表的 parts 数在百到千量级；如果单表几万 parts，说明写入模式有问题。 query_log 分析高频查询： SELECT query, count() FROM system.query_log GROUP BY query ORDER BY count() DESC LIMIT 20;频繁报错的查询： SELECT exception, count() FROM system.query_log WHERE type = 'ExceptionWhileProcessing' GROUP BY exception ORDER BY count() DESC LIMIT 20;不要直接删目录 rm -rf /var/lib/clickhouse/data/system/* 可能导致 metadata 不一致、启动失败或权限异常，优先使用 TRUNCATE、TTL 或 remove="1" 配置。

Soulock
2026年05月28日
- 技术

ClickHouse system.*_log 表几十 GB？先清后关

线上一台 ClickHouse 跑了两个月，磁盘吃掉几十 GB。查一下： SELECT database, table, formatReadableSize(sum(bytes)) AS size, sum(rows) AS rows FROM system.parts WHERE active GROUP BY database, table ORDER BY sum(bytes) DESC LIMIT 10;结果： system text_log 34.15 GiB 9亿行 system trace_log 11.41 GiB 5亿行 system asynchronous_metric_log 9.36 GiB 347亿行 system metric_log 7.75 GiB 3600万行 system part_log 4.65 GiB 6800万行 system query_log 3.59 GiB 3800万行几乎全是 ClickHouse 自己写的内部监控日志。业务数据加起来才几个 G。第一步：立刻清理 TRUNCATE 把大头清空： TRUNCATE TABLE system.text_log; TRUNCATE TABLE system.trace_log; TRUNCATE TABLE system.asynchronous_metric_log; TRUNCATE TABLE system.metric_log; TRUNCATE TABLE system.part_log; TRUNCATE TABLE system.processors_profile_log; -- query_log 可以选择保留，用于事后排查 TRUNCATE TABLE system.latency_log;query_log 是"这个 ClickHouse 都处理过什么 SQL"的记录，日常排查很有用，多数情况留着。清完之后： SYSTEM FLUSH LOGS;再查一次磁盘： du -sh /var/lib/clickhouse/data/system/*空间没立刻回收是正常的。因为：MergeTree 的 parts 只是标记删除，等 merge 文件系统 cache delete_from_disk 有 TTL可以强制一下： OPTIMIZE TABLE system.query_log FINAL;或者最粗暴： sudo systemctl restart clickhouse-server启动时会跳过被标记删除的 parts。第二步：从根源关掉不需要的日志清了以后不管，几周后又长回来。要真省心就编辑 config，把不用的日志表直接关掉。编辑： sudo nano /etc/clickhouse-server/config.d/logs.xml内容： <clickhouse>  <text_log remove="1"/> <trace_log remove="1"/> <asynchronous_metric_log remove="1"/> <metric_log remove="1"/> <part_log remove="1"/> <processors_profile_log remove="1"/>  <query_log> <database>system</database> <table>query_log</table> <partition_by>toYYYYMM(event_date)</partition_by> <ttl>event_date + INTERVAL 7 DAY DELETE</ttl> <flush_interval_milliseconds>7500</flush_interval_milliseconds> </query_log> </clickhouse>remove="1" 直接不启用这类表 <ttl> 让 ClickHouse 自动过期删除老数据重启生效： sudo systemctl restart clickhouse-server为什么这些表会爆 asynchronous_metric_log 每秒都在采几百个指标，一天几千万行是正常的。trace_log 记录每个 query 的 profile 事件，非常细。这些表默认全开，对于开发/测试很有用，对生产就是纯磁盘杀手。生产环境的经验：保留：query_log（+ 7 天 TTL）可选保留：query_thread_log 排查慢查询用关掉：text_log、trace_log、asynchronous_metric_log、metric_log、part_log、processors_profile_log如果需要采指标，用 Prometheus 拉 /metrics 接口，别依赖 ClickHouse 内部 metric_log。顺手加个磁盘水位报警 SELECT name, formatReadableSize(free_space) AS free, formatReadableSize(total_space) AS total, round(free_space / total_space * 100, 1) AS free_percent FROM system.disks;低于 20% 该发告警了。一句话总结 ClickHouse 磁盘被吃是 system.*_log 表的锅。先 TRUNCATE 清空、再 config 里 remove="1" 关掉大部分、给 query_log 加个 TTL。生产上从第一天就该这么配。