{"id":297376,"date":"2020-01-20T09:00:26","date_gmt":"2020-01-20T09:00:26","guid":{"rendered":"http:\/\/savepearlharbor.com\/?p=297376"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-29T21:00:00","slug":"","status":"publish","type":"post","link":"https:\/\/savepearlharbor.com\/?p=297376","title":{"rendered":"Nginx-log-collector \u0443\u0442\u0438\u043b\u0438\u0442\u0430 \u043e\u0442 \u0410\u0432\u0438\u0442\u043e \u0434\u043b\u044f \u043e\u0442\u043f\u0440\u0430\u0432\u043a\u0438 \u043b\u043e\u0433\u043e\u0432 nginx \u0432 Clickhouse"},"content":{"rendered":"\n<div class=\"post__text post__text-html\" id=\"post-content-body\" data-io-article-url=\"https:\/\/habr.com\/ru\/post\/484640\/\">\n<p>\u0412 \u044d\u0442\u043e\u0439 \u0441\u0442\u0430\u0442\u044c\u0435 \u0431\u0443\u0434\u0435\u0442 \u0440\u0430\u0441\u0441\u043c\u0430\u0442\u0440\u0438\u0432\u0430\u0442\u044c\u0441\u044f \u043f\u0440\u043e\u0435\u043a\u0442 <a href=\"https:\/\/github.com\/avito-tech\/nginx-log-collector\" rel=\"nofollow\">nginx-log-collector<\/a>, \u043a\u043e\u0442\u043e\u0440\u044b\u0439 \u0431\u0443\u0434\u0435\u0442 \u0447\u0438\u0442\u0430\u0442\u044c \u043b\u043e\u0433\u0438 nginx, \u043e\u0442\u043f\u0440\u0430\u0432\u043b\u044f\u0442\u044c \u0438\u0445 \u0432 \u043a\u043b\u0430\u0441\u0442\u0435\u0440 Clickhouse. \u041e\u0431\u044b\u0447\u043d\u043e \u0434\u043b\u044f \u043b\u043e\u0433\u043e\u0432 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u044e\u0442 ElasticSearch. \u0414\u043b\u044f Clickhouse \u0442\u0440\u0435\u0431\u0443\u0435\u0442\u0441\u044f \u043c\u0435\u043d\u044c\u0448\u0435 \u0440\u0435\u0441\u0443\u0440\u0441\u043e\u0432 (\u0434\u0438\u0441\u043a\u043e\u0432\u043e\u0435 \u043f\u0440\u043e\u0441\u0442\u0440\u0430\u043d\u0441\u0442\u0432\u043e, \u041e\u0417\u0423, \u0426\u041f\u0423). Clickhouse \u0431\u044b\u0441\u0442\u0440\u0435\u0435 \u0437\u0430\u043f\u0438\u0441\u044b\u0432\u0430\u0435\u0442 \u0434\u0430\u043d\u043d\u044b\u0435. Clickhouse \u0441\u0436\u0438\u043c\u0430\u0435\u0442 \u0434\u0430\u043d\u043d\u044b\u0435, \u0447\u0442\u043e \u0434\u0435\u043b\u0430\u0435\u0442 \u0434\u0430\u043d\u043d\u044b\u0435 \u043d\u0430 \u0434\u0438\u0441\u043a\u0435 \u0435\u0449\u0435 \u043a\u043e\u043c\u043f\u0430\u043a\u0442\u043d\u0435\u0435. \u041f\u0440\u0435\u0438\u043c\u0443\u0449\u0435\u0441\u0442\u0432\u0430 Clickhouse \u0432\u0438\u0434\u043d\u044b \u043f\u043e 2 \u0441\u043b\u0430\u0439\u0434\u0430\u043c \u0441 \u0434\u043e\u043a\u043b\u0430\u0434\u0430 <a href=\"https:\/\/habr.com\/ru\/company\/ua-hosting\/blog\/483712\/\">\u041a\u0430\u043a VK \u0432\u0441\u0442\u0430\u0432\u043b\u044f\u0435\u0442 \u0434\u0430\u043d\u043d\u044b\u0435 \u0432 ClickHouse \u0441 \u0434\u0435\u0441\u044f\u0442\u043a\u043e\u0432 \u0442\u044b\u0441\u044f\u0447 \u0441\u0435\u0440\u0432\u0435\u0440\u043e\u0432.<\/a><\/p>\n<p>  <\/p>\n<p><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/webt\/2s\/xr\/fw\/2sxrfwyzgj72wfqblvhlu8fphf0.jpeg\"><\/p>\n<p>  <\/p>\n<p><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/webt\/wb\/uv\/ik\/wbuvikg6tm42r0pjhkrtdrcye08.jpeg\"><\/p>\n<p>  <\/p>\n<p>\u0414\u043b\u044f \u043f\u0440\u043e\u0441\u043c\u043e\u0442\u0440\u0430 \u0430\u043d\u0430\u043b\u0438\u0442\u0438\u043a\u0438 \u043f\u043e \u043b\u043e\u0433\u0430\u043c \u0441\u043e\u0437\u0434\u0430\u0434\u0438\u043c \u0434\u0430\u0448\u0431\u043e\u0440\u0434 \u0434\u043b\u044f Grafana.<\/p>\n<p>  <\/p>\n<p>\u041a\u043e\u043c\u0443 \u0438\u043d\u0442\u0435\u0440\u0435\u0441\u043d\u043e, \u0434\u043e\u0431\u0440\u043e \u043f\u043e\u0436\u0430\u043b\u043e\u0432\u0430\u0442\u044c \u043f\u043e\u0434 \u043a\u0430\u0442.<\/p>\n<p><a name=\"habracut\"><\/a>  <\/p>\n<p>\u0423\u0441\u0442\u0430\u043d\u0430\u0432\u043b\u0438\u0432\u0430\u0435\u043c nginx, grafana \u0441\u0442\u0430\u043d\u0434\u0430\u0440\u0442\u043d\u044b\u043c \u0441\u043f\u043e\u0441\u043e\u0431\u043e\u043c.<\/p>\n<p>  <\/p>\n<p>\u0423\u0441\u0442\u0430\u043d\u0430\u0432\u043b\u0438\u0432\u0430\u0435\u043c \u043a\u043b\u0430\u0441\u0442\u0435\u0440 clickhouse \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e ansible-playbook \u043e\u0442 <a href=\"https:\/\/daybydayz.ru\/2018\/02\/05\/%D0%B0%D0%BD%D1%81%D0%B8%D0%B1%D0%BB-%D1%80%D0%BE%D0%BB%D1%8C-%D0%B4%D0%BB%D1%8F-%D1%83%D1%81%D1%82%D0%B0%D0%BD%D0%BE%D0%B2%D0%BA%D0%B8-clickhouse-%D0%B8-zookeper\/\" rel=\"nofollow\">\u0414\u0435\u043d\u0438\u0441\u0430 \u041f\u0440\u043e\u0441\u043a\u0443\u0440\u0438\u043d\u0430<\/a>.<\/p>\n<p>  <\/p>\n<h3 id=\"sozdanie-bd-i-tablic-v-clickhouse\">\u0421\u043e\u0437\u0434\u0430\u043d\u0438\u0435 \u0431\u0434 \u0438 \u0442\u0430\u0431\u043b\u0438\u0446 \u0432 Clickhouse<\/h3>\n<p>  <\/p>\n<p>\u0412 \u044d\u0442\u043e\u043c <a href=\"https:\/\/github.com\/avito-tech\/nginx-log-collector\/blob\/master\/etc\/examples\/clickhouse\/table_schema.sql\" rel=\"nofollow\">\u0444\u0430\u0439\u043b\u0435<\/a> \u043e\u043f\u0438\u0441\u0430\u043d\u044b SQL \u0437\u0430\u043f\u0440\u043e\u0441\u044b \u0434\u043b\u044f \u0441\u043e\u0437\u0434\u0430\u043d\u0438\u044f \u0431\u0434 \u0438 \u0442\u0430\u0431\u043b\u0438\u0446 \u0434\u043b\u044f nginx-log-collector \u0432 Clickhouse.<\/p>\n<p>  <\/p>\n<p>\u041a\u0430\u0436\u0434\u044b\u0439 \u0437\u0430\u043f\u0440\u043e\u0441 \u0434\u0435\u043b\u0430\u0435\u043c \u043f\u043e\u043e\u0447\u0435\u0440\u0435\u0434\u043d\u043e \u043d\u0430 \u043a\u0430\u0436\u0434\u043e\u043c \u0441\u0435\u0440\u0432\u0435\u0440\u0435 \u043a\u043b\u0430\u0441\u0442\u0435\u0440\u0430 Clickhouse.<\/p>\n<p>  <\/p>\n<p>\u0412\u0430\u0436\u043d\u043e\u0435 \u0437\u0430\u043c\u0435\u0447\u0430\u043d\u0438\u0435. \u0412 \u044d\u0442\u043e\u0439 \u0441\u0442\u0440\u043e\u043a\u0435 logs_cluster \u043d\u0443\u0436\u043d\u043e \u0437\u0430\u043c\u0435\u043d\u0438\u0442\u044c \u043d\u0430 \u0432\u0430\u0448\u0435 \u043d\u0430\u0437\u0432\u0430\u043d\u0438\u0435 \u043a\u043b\u0430\u0441\u0442\u0435\u0440\u0430 \u0438\u0437 \u0444\u0430\u0439\u043b\u0430 clickhouse_remote_servers.xml \u043c\u0435\u0436\u0434\u0443 &quot;remote_servers&quot; and &quot;shard&quot;.<\/p>\n<p>  <\/p>\n<pre><code class=\"bash\">ENGINE = Distributed('logs_cluster', 'nginx', 'access_log_shard', rand())<\/code><\/pre>\n<p>  <\/p>\n<h3 id=\"ustanavlivka-i-nastroyka-nginx-log-collector-rpm\">\u0423\u0441\u0442\u0430\u043d\u0430\u0432\u043b\u0438\u0432\u043a\u0430 \u0438 \u043d\u0430\u0441\u0442\u0440\u043e\u0439\u043a\u0430 nginx-log-collector-rpm<\/h3>\n<p>  <\/p>\n<p>Nginx-log-collector \u043d\u0435 \u0438\u043c\u0435\u0435\u0442 rpm. \u0417\u0434\u0435\u0441\u044c <a href=\"https:\/\/github.com\/patsevanton\/nginx-log-collector-rpm\" rel=\"nofollow\">https:\/\/github.com\/patsevanton\/nginx-log-collector-rpm<\/a> \u0441\u043e\u0437\u0434\u0430\u0435\u043c \u0435\u043c\u0443 rpm. \u0421\u043e\u0431\u0438\u0440\u0430\u0442\u044c\u0441\u044f rpm \u0431\u0443\u0434\u0435\u0442 \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e <a href=\"https:\/\/copr.fedorainfracloud.org\/coprs\/antonpatsev\/nginx-log-collector-rpm\/\" rel=\"nofollow\">Fedora Copr<\/a> <\/p>\n<p>  <\/p>\n<p>\u0423\u0441\u0442\u0430\u043d\u0430\u0432\u043b\u0438\u0432\u0430\u0435\u043c rpm \u043f\u0430\u043a\u0435\u0442 nginx-log-collector-rpm<\/p>\n<p>  <\/p>\n<pre><code class=\"bash\">yum -y install yum-plugin-copr yum copr enable antonpatsev\/nginx-log-collector-rpm yum -y install nginx-log-collector systemctl start nginx-log-collector<\/code><\/pre>\n<p>  <\/p>\n<p>\u041f\u0440\u0430\u0432\u0438\u043c \u043a\u043e\u043d\u0444\u0438\u0433 \/etc\/nginx-log-collector\/config.yaml:<\/p>\n<p>  <\/p>\n<pre><code class=\"plaintext\">  .......   upload:     table: nginx.access_log     dsn: http:\/\/ip-\u0430\u0434\u0440\u0435\u0441-\u043a\u043b\u0430\u0441\u0442\u0435\u0440\u0430-clickhouse:8123\/  - tag: &quot;nginx_error:&quot;   format: error  # access | error   buffer_size: 1048576   upload:     table: nginx.error_log     dsn: http:\/\/ip-\u0430\u0434\u0440\u0435\u0441-\u043a\u043b\u0430\u0441\u0442\u0435\u0440\u0430-clickhouse:8123\/<\/code><\/pre>\n<p>  <\/p>\n<h3 id=\"nastroyka-nginx\">\u041d\u0430\u0441\u0442\u0440\u043e\u0439\u043a\u0430 nginx<\/h3>\n<p>  <\/p>\n<p>\u041e\u0431\u0449\u0438\u0439 \u043a\u043e\u043d\u0444\u0438\u0433 nginx:<\/p>\n<p>  <\/p>\n<pre><code class=\"nginx\">user  nginx; worker_processes  auto;  #error_log  \/var\/log\/nginx\/error.log warn; pid        \/var\/run\/nginx.pid;  events {     worker_connections  1024; }  http {     include       \/etc\/nginx\/mime.types;     default_type  application\/octet-stream;      log_format  main  '$remote_addr - $remote_user [$time_local] &quot;$request&quot; '                       '$status $body_bytes_sent &quot;$http_referer&quot; '                       '&quot;$http_user_agent&quot; &quot;$http_x_forwarded_for&quot;';      log_format avito_json escape=json                      '{'                      '&quot;event_datetime&quot;: &quot;$time_iso8601&quot;, '                      '&quot;server_name&quot;: &quot;$server_name&quot;, '                      '&quot;remote_addr&quot;: &quot;$remote_addr&quot;, '                      '&quot;remote_user&quot;: &quot;$remote_user&quot;, '                      '&quot;http_x_real_ip&quot;: &quot;$http_x_real_ip&quot;, '                      '&quot;status&quot;: &quot;$status&quot;, '                      '&quot;scheme&quot;: &quot;$scheme&quot;, '                      '&quot;request_method&quot;: &quot;$request_method&quot;, '                      '&quot;request_uri&quot;: &quot;$request_uri&quot;, '                      '&quot;server_protocol&quot;: &quot;$server_protocol&quot;, '                      '&quot;body_bytes_sent&quot;: $body_bytes_sent, '                      '&quot;http_referer&quot;: &quot;$http_referer&quot;, '                      '&quot;http_user_agent&quot;: &quot;$http_user_agent&quot;, '                      '&quot;request_bytes&quot;: &quot;$request_length&quot;, '                      '&quot;request_time&quot;: &quot;$request_time&quot;, '                      '&quot;upstream_addr&quot;: &quot;$upstream_addr&quot;, '                      '&quot;upstream_response_time&quot;: &quot;$upstream_response_time&quot;, '                      '&quot;hostname&quot;: &quot;$hostname&quot;, '                      '&quot;host&quot;: &quot;$host&quot;'                      '}';      access_log     syslog:server=unix:\/var\/run\/nginx_log.sock,nohostname,tag=nginx avito_json; #ClickHouse     error_log      syslog:server=unix:\/var\/run\/nginx_log.sock,nohostname,tag=nginx_error; #ClickHouse      #access_log  \/var\/log\/nginx\/access.log  main;      proxy_ignore_client_abort on;     sendfile        on;     keepalive_timeout  65;     include \/etc\/nginx\/conf.d\/*.conf; } <\/code><\/pre>\n<p>  <\/p>\n<p>\u0412\u0438\u0440\u0442\u0443\u0430\u043b\u044c\u043d\u044b\u0439 \u0445\u043e\u0441\u0442 \u043e\u0434\u0438\u043d:<\/p>\n<p>  <\/p>\n<p>vhost1.conf:<\/p>\n<p>  <\/p>\n<pre><code class=\"plaintext\">upstream backend {     server ip-\u0430\u0434\u0440\u0435\u0441-\u0441\u0435\u0440\u0432\u0435\u0440\u0430-\u0441-stub_http_server:8080;     server ip-\u0430\u0434\u0440\u0435\u0441-\u0441\u0435\u0440\u0432\u0435\u0440\u0430-\u0441-stub_http_server:8080;     server ip-\u0430\u0434\u0440\u0435\u0441-\u0441\u0435\u0440\u0432\u0435\u0440\u0430-\u0441-stub_http_server:8080;     server ip-\u0430\u0434\u0440\u0435\u0441-\u0441\u0435\u0440\u0432\u0435\u0440\u0430-\u0441-stub_http_server:8080;     server ip-\u0430\u0434\u0440\u0435\u0441-\u0441\u0435\u0440\u0432\u0435\u0440\u0430-\u0441-stub_http_server:8080; }  server {     listen   80;     server_name vhost1;     location \/ {         proxy_pass http:\/\/backend;     } }<\/code><\/pre>\n<p>  <\/p>\n<p>\u0414\u043e\u0431\u0430\u0432\u043b\u044f\u0435\u043c \u0432 \u0444\u0430\u0439\u043b \/etc\/hosts \u0432\u0438\u0440\u0442\u0443\u0430\u043b\u044c\u043d\u044b\u0435 \u0445\u043e\u0441\u0442\u044b:<\/p>\n<p>  <\/p>\n<pre><code class=\"bash\">ip-\u0430\u0434\u0440\u0435\u0441-\u0441\u0435\u0440\u0432\u0435\u0440\u0430-\u0441-nginx vhost1<\/code><\/pre>\n<p>  <\/p>\n<h3 id=\"emulyator-http-servera\">\u042d\u043c\u0443\u043b\u044f\u0442\u043e\u0440 HTTP \u0441\u0435\u0440\u0432\u0435\u0440\u0430<\/h3>\n<p>  <\/p>\n<p>\u0412 \u043a\u0430\u0447\u0435\u0441\u0442\u0432\u0435 \u044d\u043c\u0443\u043b\u044f\u0442\u043e\u0440\u0430 HTTP \u0441\u0435\u0440\u0432\u0435\u0440\u0430 \u0431\u0443\u0434\u0435\u043c \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u044c <a href=\"https:\/\/github.com\/maxiko\/nodejs-stub-server\" rel=\"nofollow\">nodejs-stub-server<\/a> \u043e\u0442 <a href=\"https:\/\/habr.com\/users\/Anthrax_Beta\/\">Maxim Ignatenko<\/a><\/p>\n<p>  <\/p>\n<p>Nodejs-stub-server \u043d\u0435 \u0438\u043c\u0435\u0435\u0442 rpm. \u0417\u0434\u0435\u0441\u044c <a href=\"https:\/\/github.com\/patsevanton\/nodejs-stub-server\" rel=\"nofollow\">https:\/\/github.com\/patsevanton\/nodejs-stub-server<\/a> \u0441\u043e\u0437\u0434\u0430\u0435\u043c \u0435\u043c\u0443 rpm. \u0421\u043e\u0431\u0438\u0440\u0430\u0442\u044c\u0441\u044f rpm \u0431\u0443\u0434\u0435\u0442 \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e <a href=\"https:\/\/copr.fedorainfracloud.org\/coprs\/antonpatsev\/nodejs-stub-server\/\" rel=\"nofollow\">Fedora Copr<\/a> <\/p>\n<p>  <\/p>\n<p>\u0423\u0441\u0442\u0430\u043d\u0430\u0432\u043b\u0438\u0432\u0430\u0435\u043c \u043d\u0430 upstream nginx rpm \u043f\u0430\u043a\u0435\u0442 nodejs-stub-server<\/p>\n<p>  <\/p>\n<pre><code class=\"bash\">yum -y install yum-plugin-copr yum copr enable antonpatsev\/nodejs-stub-server yum -y install stub_http_server systemctl start stub_http_server<\/code><\/pre>\n<p>  <\/p>\n<h3 id=\"nagruzochnoe-testirovanie\">\u041d\u0430\u0433\u0440\u0443\u0437\u043e\u0447\u043d\u043e\u0435 \u0442\u0435\u0441\u0442\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435<\/h3>\n<p>  <\/p>\n<p>\u0422\u0435\u0441\u0442\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435 \u043f\u0440\u043e\u0432\u043e\u0434\u0438\u043c \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e Apache benchmark.<\/p>\n<p>  <\/p>\n<p>\u0423\u0441\u0442\u0430\u043d\u0430\u0432\u043b\u0438\u0432\u0430\u0435\u043c \u0435\u0433\u043e:<\/p>\n<p>  <\/p>\n<pre><code class=\"bash\">yum install -y httpd-tools<\/code><\/pre>\n<p>  <\/p>\n<p>\u0417\u0430\u043f\u0443\u0441\u043a\u0430\u0435\u043c \u0442\u0435\u0441\u0442\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435 \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e Apache benchmark c 5 \u0440\u0430\u0437\u043d\u044b\u0445 \u0441\u0435\u0440\u0432\u0435\u0440\u043e\u0432:<\/p>\n<p>  <\/p>\n<pre><code class=\"plaintext\">while true; do ab -H &quot;User-Agent: 1server&quot; -c 10 -n 10 -t 10 http:\/\/vhost1\/; sleep 1; done while true; do ab -H &quot;User-Agent: 2server&quot; -c 10 -n 10 -t 10 http:\/\/vhost1\/; sleep 1; done while true; do ab -H &quot;User-Agent: 3server&quot; -c 10 -n 10 -t 10 http:\/\/vhost1\/; sleep 1; done while true; do ab -H &quot;User-Agent: 4server&quot; -c 10 -n 10 -t 10 http:\/\/vhost1\/; sleep 1; done while true; do ab -H &quot;User-Agent: 5server&quot; -c 10 -n 10 -t 10 http:\/\/vhost1\/; sleep 1; done<\/code><\/pre>\n<p>  <\/p>\n<h3 id=\"nastroyka-grafana\">\u041d\u0430\u0441\u0442\u0440\u043e\u0439\u043a\u0430 Grafana<\/h3>\n<p>  <\/p>\n<p>\u041d\u0430 \u043e\u0444\u0438\u0446\u0438\u0430\u043b\u044c\u043d\u043e\u043c \u0441\u0430\u0439\u0442\u0435 Grafana \u0432\u044b \u043d\u0435 \u043d\u0430\u0439\u0434\u0435\u0442\u0435 \u0434\u0430\u0448\u0431\u043e\u0440\u0434. <\/p>\n<p>  <\/p>\n<p>\u041f\u043e\u044d\u0442\u043e\u043c\u0443 \u0431\u0443\u0434\u0435\u043c \u0434\u0435\u043b\u0430\u0442\u044c \u0435\u0433\u043e \u0432\u0440\u0443\u0447\u0443\u044e.<\/p>\n<p>  <\/p>\n<p>\u041c\u043e\u0439 \u0441\u043e\u0445\u0440\u0430\u043d\u0435\u043d\u043d\u044b\u0439 \u0434\u0430\u0448\u0431\u043e\u0440\u0434 \u0432\u044b \u043c\u043e\u0436\u0435\u0442\u0435 \u043d\u0430\u0439\u0442\u0438 <a href=\"https:\/\/gist.github.com\/patsevanton\/e6fb0dfda1dfcf97efb6d762088bda18\" rel=\"nofollow\">\u0442\u0443\u0442<\/a>.<\/p>\n<p>  <\/p>\n<p>\u0422\u0430\u043a \u0436\u0435 \u0432\u0430\u043c \u043d\u0443\u0436\u043d\u043e \u0441\u043e\u0437\u0434\u0430\u0442\u044c \u043f\u0435\u0440\u0435\u043c\u0435\u043d\u043d\u0443\u044e table \u0441 \u0441\u043e\u0434\u0435\u0440\u0436\u0438\u043c\u044b\u043c <code>nginx.access_log<\/code>.<br \/>  <img decoding=\"async\" src=\"https:\/\/habrastorage.org\/webt\/hs\/ta\/jd\/hstajdx3bnaqy07jxy96lj5wpjw.png\"><\/p>\n<p>  <\/p>\n<p>Singlestat Total Requests:<\/p>\n<p>  <\/p>\n<pre><code class=\"sql\">SELECT  1 as t,  count(*) as c  FROM $table  WHERE $timeFilter GROUP BY t<\/code><\/pre>\n<p>  <\/p>\n<p><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/webt\/og\/sg\/ku\/ogsgkulqfaxoiivh3xtmi6nvxd4.png\"><\/p>\n<p>  <\/p>\n<p>Singlestat Failed Requests:<\/p>\n<p>  <\/p>\n<pre><code class=\"sql\">SELECT  1 as t,  count(*) as c  FROM $table  WHERE $timeFilter AND status NOT IN (200, 201, 401) GROUP BY t<\/code><\/pre>\n<p>  <\/p>\n<p><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/webt\/am\/sr\/fs\/amsrfs0u3nyvtx3gohufkzdltfa.png\"><\/p>\n<p>  <\/p>\n<p>Singlestat Failing Percent:<\/p>\n<p>  <\/p>\n<pre><code class=\"sql\">SELECT  1 as t, (sum(status = 500 or status = 499)\/sum(status = 200 or status = 201 or status = 401))*100 FROM $table  WHERE $timeFilter GROUP BY t<\/code><\/pre>\n<p>  <\/p>\n<p><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/webt\/az\/xd\/w7\/azxdw7c3kkguncr5a8ujbtgvdb4.png\"><\/p>\n<p>  <\/p>\n<p>Singlestat Avg Response Time:<\/p>\n<p>  <\/p>\n<pre><code class=\"sql\">SELECT  1, avg(request_time) FROM $table  WHERE $timeFilter GROUP BY 1<\/code><\/pre>\n<p>  <\/p>\n<p><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/webt\/5k\/gu\/l9\/5kgul9tbdow2cnzjwvh5yrhm-rm.png\"><\/p>\n<p>  <\/p>\n<p>Singlestat Max Response Time:<\/p>\n<p>  <\/p>\n<pre><code class=\"sql\">SELECT  1 as t, max(request_time) as c  FROM $table  WHERE $timeFilter GROUP BY t<\/code><\/pre>\n<p>  <\/p>\n<p><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/webt\/wl\/2b\/qd\/wl2bqdydk9osyhxodtlnooet80o.png\"><\/p>\n<p>  <\/p>\n<p>Count Status:<\/p>\n<p>  <\/p>\n<pre><code class=\"sql\">$columns(status, count(*) as c) from $table<\/code><\/pre>\n<p>  <\/p>\n<p><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/webt\/rg\/qg\/s_\/rgqgs_21jdozcvpcvfv6-znexve.png\"><\/p>\n<p>  <\/p>\n<p>\u0414\u043b\u044f \u0432\u044b\u0432\u043e\u0434\u0430 \u0434\u0430\u043d\u043d\u044b\u0445 \u043a\u0430\u043a \u043f\u0438\u0440\u043e\u0433, \u043d\u0443\u0436\u043d\u043e \u0443\u0441\u0442\u0430\u043d\u043e\u0432\u0438\u0442\u044c \u043f\u043b\u0430\u0433\u0438\u043d \u0438 \u043f\u0435\u0440\u0435\u0437\u0430\u0433\u0440\u0443\u0437\u0438\u0442\u044c grafana.<\/p>\n<p>  <\/p>\n<pre><code class=\"bash\">grafana-cli plugins install grafana-piechart-panel service grafana-server restart<\/code><\/pre>\n<p>  <\/p>\n<p>Pie TOP 5 Status:<\/p>\n<p>  <\/p>\n<pre><code class=\"sql\">SELECT     1, \/* fake timestamp value *\/     status,     sum(status) AS Reqs FROM $table WHERE $timeFilter GROUP BY status ORDER BY Reqs desc LIMIT 5<\/code><\/pre>\n<p>  <\/p>\n<p><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/webt\/qo\/3y\/v1\/qo3yv1ll-drlzbhgfr93m1tzwfg.png\"><\/p>\n<p>  <\/p>\n<p>\u0414\u0430\u043b\u044c\u0448\u0435 \u0431\u0443\u0434\u0443 \u043f\u0440\u0438\u0432\u043e\u0434\u0438\u0442\u044c \u0437\u0430\u043f\u0440\u043e\u0441\u044b \u0431\u0435\u0437 \u0441\u043a\u0440\u0438\u043d\u0448\u043e\u0442\u043e\u0432:<\/p>\n<p>  <\/p>\n<p>Count http_user_agent:<\/p>\n<p>  <\/p>\n<pre><code class=\"sql\">$columns(http_user_agent, count(*) c) FROM $table<\/code><\/pre>\n<p>  <\/p>\n<p>GoodRate\/BadRate:<\/p>\n<p>  <\/p>\n<pre><code class=\"sql\">$rate(countIf(status = 200) AS good, countIf(status != 200) AS bad) FROM $table<\/code><\/pre>\n<p>  <\/p>\n<p>Response Timing:<\/p>\n<p>  <\/p>\n<pre><code class=\"sql\">$rate(avg(request_time) as request_time) FROM $table<\/code><\/pre>\n<p>  <\/p>\n<p>Upstream response time (\u0432\u0440\u0435\u043c\u044f \u043e\u0442\u0432\u0435\u0442\u0430 1-\u0433\u043e upstream):<\/p>\n<p>  <\/p>\n<pre><code class=\"sql\">$rate(avg(arrayElement(upstream_response_time,1)) as upstream_response_time) FROM $table<\/code><\/pre>\n<p>  <\/p>\n<p>Table Count Status for all vhost:<\/p>\n<p>  <\/p>\n<pre><code class=\"sql\">$columns(status, count(*) as c) from $table<\/code><\/pre>\n<p>  <\/p>\n<h3 id=\"obschiy-vid-dashborda\">\u041e\u0431\u0449\u0438\u0439 \u0432\u0438\u0434 \u0434\u0430\u0448\u0431\u043e\u0440\u0434\u0430<\/h3>\n<p>  <\/p>\n<p><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/webt\/is\/gw\/qw\/isgwqw9zoo7-xidser8nrguyh2a.png\"><\/p>\n<p>  <\/p>\n<p><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/webt\/el\/7d\/2j\/el7d2jq0ekail_lkuxjwdsnzm5k.png\"><\/p>\n<p>  <\/p>\n<p><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/webt\/ig\/if\/ph\/igifphdfkwhurvzsf8y97xi6sqq.png\"><\/p>\n<p>  <\/p>\n<p>\u0412\u044b\u0432\u043e\u0434:<\/p>\n<p>  <\/p>\n<p>\u041d\u0430\u0434\u0435\u044e\u0441\u044c, \u0441\u043e\u043e\u0431\u0449\u0435\u0441\u0442\u0432\u043e \u043f\u043e\u0434\u043a\u043b\u044e\u0447\u0438\u0442\u0441\u044f \u043a \u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u043a\u0435\/\u0442\u0435\u0441\u0442\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044e \u0438 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u044e nginx-log-collector.<br \/>  \u0418 \u043a\u0442\u043e-\u043d\u0438\u0431\u0443\u0434\u044c \u043a\u043e\u0433\u0434\u0430 \u0432\u043d\u0435\u0434\u0440\u0438\u0442 nginx-log-collector \u0440\u0430\u0441\u0441\u043a\u0430\u0436\u0435\u0442 \u0441\u043a\u043e\u043b\u044c\u043a\u043e \u0441\u044d\u043a\u043e\u043d\u043e\u043c\u0438\u043b \u0434\u0438\u0441\u043a\u0430, \u041e\u0417\u0423, \u0426\u041f\u0423.<\/p>\n<p>  <\/p>\n<p>Telegram \u043a\u0430\u043d\u0430\u043b\u044b:<\/p>\n<p>  <\/p>\n<ul>\n<li><a href=\"https:\/\/t.me\/clickhouse_ru\" rel=\"nofollow\">Clickhouse<\/a> <\/li>\n<li><a href=\"https:\/\/t.me\/nginx_ru\" rel=\"nofollow\">Nginx<\/a><\/li>\n<li><a href=\"https:\/\/t.me\/metrics_ru\" rel=\"nofollow\">\u0426\u0435\u0440\u043a\u043e\u0432\u044c \u043c\u0435\u0442\u0440\u0438\u043a<\/a><\/li>\n<li><a href=\"https:\/\/t.me\/ru_logs\" rel=\"nofollow\">\u0421\u0431\u043e\u0440 \u0438 \u0430\u043d\u0430\u043b\u0438\u0442\u0438\u043a\u0430 \u0441\u0438\u0441\u0442\u0435\u043c\u043d\u044b\u0445 \u0441\u043e\u043e\u0431\u0449\u0435\u043d\u0438\u0439<\/a><\/li>\n<li><a href=\"https:\/\/t.me\/grafana_ru\" rel=\"nofollow\">Grafana<\/a><\/li>\n<\/ul>\n<p>  <\/p>\n<h3 id=\"millisekundy\">\u041c\u0438\u043b\u043b\u0438\u0441\u0435\u043a\u0443\u043d\u0434\u044b:<\/h3>\n<p>  <\/p>\n<p>\u041a\u043e\u043c\u0443 \u0432\u0430\u0436\u043d\u044b \u043c\u0438\u043b\u043b\u0438\u0441\u0435\u043a\u0443\u043d\u0434\u044b, \u043d\u0430\u043f\u0438\u0448\u0438\u0442\u0435 \u0438\u043b\u0438 \u043f\u0440\u043e\u0433\u043e\u043b\u043e\u0441\u0443\u0439\u0442\u0435, \u043f\u043e\u0436\u0430\u043b\u0443\u0439\u0441\u0442\u0430, \u0432 \u044d\u0442\u043e\u043c <a href=\"https:\/\/github.com\/avito-tech\/nginx-log-collector\/issues\/7\" rel=\"nofollow\">issue<\/a>.<\/p>\n<\/div>\n<p> \u0441\u0441\u044b\u043b\u043a\u0430 \u043d\u0430 \u043e\u0440\u0438\u0433\u0438\u043d\u0430\u043b \u0441\u0442\u0430\u0442\u044c\u0438 <a href=\"https:\/\/habr.com\/ru\/post\/484640\/\"> https:\/\/habr.com\/ru\/post\/484640\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"\n<div class=\"post__text post__text-html\" id=\"post-content-body\" data-io-article-url=\"https:\/\/habr.com\/ru\/post\/484640\/\">\n<p>\u0412 \u044d\u0442\u043e\u0439 \u0441\u0442\u0430\u0442\u044c\u0435 \u0431\u0443\u0434\u0435\u0442 \u0440\u0430\u0441\u0441\u043c\u0430\u0442\u0440\u0438\u0432\u0430\u0442\u044c\u0441\u044f \u043f\u0440\u043e\u0435\u043a\u0442 <a href=\"https:\/\/github.com\/avito-tech\/nginx-log-collector\" rel=\"nofollow\">nginx-log-collector<\/a>, \u043a\u043e\u0442\u043e\u0440\u044b\u0439 \u0431\u0443\u0434\u0435\u0442 \u0447\u0438\u0442\u0430\u0442\u044c \u043b\u043e\u0433\u0438 nginx, \u043e\u0442\u043f\u0440\u0430\u0432\u043b\u044f\u0442\u044c \u0438\u0445 \u0432 \u043a\u043b\u0430\u0441\u0442\u0435\u0440 Clickhouse. \u041e\u0431\u044b\u0447\u043d\u043e \u0434\u043b\u044f \u043b\u043e\u0433\u043e\u0432 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u044e\u0442 ElasticSearch. \u0414\u043b\u044f Clickhouse \u0442\u0440\u0435\u0431\u0443\u0435\u0442\u0441\u044f \u043c\u0435\u043d\u044c\u0448\u0435 \u0440\u0435\u0441\u0443\u0440\u0441\u043e\u0432 (\u0434\u0438\u0441\u043a\u043e\u0432\u043e\u0435 \u043f\u0440\u043e\u0441\u0442\u0440\u0430\u043d\u0441\u0442\u0432\u043e, \u041e\u0417\u0423, \u0426\u041f\u0423). Clickhouse \u0431\u044b\u0441\u0442\u0440\u0435\u0435 \u0437\u0430\u043f\u0438\u0441\u044b\u0432\u0430\u0435\u0442 \u0434\u0430\u043d\u043d\u044b\u0435. Clickhouse \u0441\u0436\u0438\u043c\u0430\u0435\u0442 \u0434\u0430\u043d\u043d\u044b\u0435, \u0447\u0442\u043e \u0434\u0435\u043b\u0430\u0435\u0442 \u0434\u0430\u043d\u043d\u044b\u0435 \u043d\u0430 \u0434\u0438\u0441\u043a\u0435 \u0435\u0449\u0435 \u043a\u043e\u043c\u043f\u0430\u043a\u0442\u043d\u0435\u0435. \u041f\u0440\u0435\u0438\u043c\u0443\u0449\u0435\u0441\u0442\u0432\u0430 Clickhouse \u0432\u0438\u0434\u043d\u044b \u043f\u043e 2 \u0441\u043b\u0430\u0439\u0434\u0430\u043c \u0441 \u0434\u043e\u043a\u043b\u0430\u0434\u0430 <a href=\"https:\/\/habr.com\/ru\/company\/ua-hosting\/blog\/483712\/\">\u041a\u0430\u043a VK \u0432\u0441\u0442\u0430\u0432\u043b\u044f\u0435\u0442 \u0434\u0430\u043d\u043d\u044b\u0435 \u0432 ClickHouse \u0441 \u0434\u0435\u0441\u044f\u0442\u043a\u043e\u0432 \u0442\u044b\u0441\u044f\u0447 \u0441\u0435\u0440\u0432\u0435\u0440\u043e\u0432.<\/a><\/p>\n<p>  <\/p>\n<p><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/webt\/2s\/xr\/fw\/2sxrfwyzgj72wfqblvhlu8fphf0.jpeg\"><\/p>\n<p>  <\/p>\n<p><img decoding=\"async\" src=\"https:\/\/habrastorage.org\/webt\/wb\/uv\/ik\/wbuvikg6tm42r0pjhkrtdrcye08.jpeg\"><\/p>\n<p>  <\/p>\n<p>\u0414\u043b\u044f \u043f\u0440\u043e\u0441\u043c\u043e\u0442\u0440\u0430 \u0430\u043d\u0430\u043b\u0438\u0442\u0438\u043a\u0438 \u043f\u043e \u043b\u043e\u0433\u0430\u043c \u0441\u043e\u0437\u0434\u0430\u0434\u0438\u043c \u0434\u0430\u0448\u0431\u043e\u0440\u0434 \u0434\u043b\u044f Grafana.<\/p>\n<p>  <\/p>\n<p>\u041a\u043e\u043c\u0443 \u0438\u043d\u0442\u0435\u0440\u0435\u0441\u043d\u043e, \u0434\u043e\u0431\u0440\u043e \u043f\u043e\u0436\u0430\u043b\u043e\u0432\u0430\u0442\u044c \u043f\u043e\u0434 \u043a\u0430\u0442.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-297376","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/297376","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=297376"}],"version-history":[{"count":0,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/297376\/revisions"}],"wp:attachment":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=297376"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=297376"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=297376"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}