clickhouse查询耗时30多秒还有优化的地方?
发布于 9 天前 作者 helloboy 123 次浏览 来自 问答

一天5千多万数据查询耗时30多秒,查询语句如下:select link_id, src_ip, dst_ip, sum(session_num) as session_num, sum(byte_up + byte_down) as total_byte, sum(byte_up) as n_byte_up, sum(byte_down) as n_byte_down, sum(pkt_up + pkt_down) as total_pkt, sum(pkt_up) as n_pkt_up, sum(pkt_down) as n_pkt_down, sum(tcp_syn_num) as n_tcp_syn_num, sum(tcp_syn_ack_num) as n_tcp_syn_ack_num, sum(tcp_rst_up) as n_tcp_rst_up, sum(tcp_rst_down) as n_tcp_rst_down, ROUND(sum(ave_rtt_up),2) as rtt_up, ROUND(sum(ave_rtt_down),2) as rtt_down, sum(tcp_retrans_up) as tcp_reack_up, sum(tcp_retrans_down) as tcp_reack_down, sum(tcp_0win_up) as n_tcp_0win_up, sum(tcp_0win_down) as n_tcp_0win_down, ROUND(total_byte/85886,2) as total_byte_s, ROUND(n_byte_up/85886,2) as byte_up_s, ROUND(n_byte_down/85886,2) as byte_down_s, ROUND(total_pkt/85886,2) as total_pkt_s, ROUND(n_pkt_up/85886,2) as pkt_up_s, ROUND(n_pkt_down/85886,2) as pkt_down_s from aggr_log_app_ip_tcp where link_id = 0 and vlink_id = 0 and (start_time BETWEEN 1528554524 and 1528640410) and (create_time BETWEEN 20180609 and 20180610) group by link_id, src_ip, dst_ip order by total_byte desc limit 0,50 物理内存峰值7G多,cpu状态如下:QQ图片20180611142815.png

2 回复

物理内存不要看ClickHouse用了多少,看OS的page cache多少。也就是你的数据是不是都被OS cache住了,这样可以减少IO层面的压力。

另外,关注一下主键,性能可能会好一些。

不过看你这SQL,感觉已经很不错了。

请贴一下完整的建表语句,让我们看看分区策略和主键设计,这对性能至关重要。

回到顶部