Phân tích Slow query của Postgresql

alt text

Slow query là gì?

Khi các câu query chậm hơn một thời gian nhất định tùy theo bạn định nghĩa, ví dụ chậm hơn 50ms, thì các câu query đó được xem là slow query.
Và tùy theo ứng dụng mà sẽ có định nghĩa khác nhau, một vài ví dụ:

  • Bạn viết một API authentication cho cái app nho nhỏ và bạn hy vọng nó chạy càng nhanh càng tốt, tối đa là 30ms/request => Query phải < 30ms.
  • Bạn viết một ứng dụng liên quan đến tiền bạc, thanh toán online. Đòi hỏi bắt buộc là phải chính xác và an toàn, chậm một chút cũng không sao => Query có thể nới ra thành < 100 ms.

Tùy theo mục đích mà có tiêu chuẩn thời gian khác nhau

Slow query xảy ra khi nào?

Có rất nhiều trường hợp sẽ xảy ra slow query như:

  • Query condition trên column thiếu index
  • Query bị lock bởi các câu query, transaction,...
  • Câu query không được optimize nên dẫn đến query thừa hoặc không tối ưu, sequence scan thay vì index scan,...
  • Cấu hình máy không đáp ứng được nhu cầu query hiện tại.
  • ...

Có khá nhiều nguyên nhân có thể xảy ra và để có thể giải quyết thì việc đầu tiên là phải biết nó có xảy ra :smile:

Log slow query

Postgresql cho phép bạn log các câu query, statement,... và nhiều thứ khác ra.
Để log ra thì bạn có thể edit file postgresql.config và thay đổi theo nhu cầu, mình liệt kê một vài thông số cơ bản phục vụ cho bài viết, bạn có thể xem thêm ở đây
https://www.postgresql.org/docs/current/static/runtime-config-logging.html

  • log_directory: Nơi lưu log, mặc định thường là pg_log
  • log_filename: format name của file log
  • log_min_duration_statement: Nếu lớn hơn thời gian này query sẽ được cho là slow query (đơn vị milisecond)

Save lại và reload (hoặc restart) PostgreSQL. Khi có slow query PostgreSQL sẽ giúp bạn ném vào log.

Note nhỏ: Khi bạn restart PostgreSQL, các connection của client đều sẽ bị disconnect

          (╯°□°)╯︵ ┻━┻

nhưng nếu bạn reload thì không sao cả, mọi thứ vẫn bình thường

          (ノ◕ヮ◕)ノ*:・゚✧

sudo service postgresql reload

Khi log ra bạn sẽ thấy nhiều thông tin dạng thế này và fix dễ dàng hơn

593821c2.21a2 2017-06-08 00:00:41 ICT LOG:  duration: 439.218 ms  execute <unnamed>: SELECT * FROM "users"  WHERE (username = $1) LIMIT 1
593821c2.21a2 2017-06-08 00:00:41 ICT DETAIL:  parameters: $1 = 'trietphm'

pgBadger

Vì sao lại cần log analyzer?

Sau một ngày đẹp trời bạn mở file log lên và chợt nhận ra nó nặng cả GB với hằng hà sa số log query slow, với cơ man nào là các câu query dài loằng ngoằng hay những câu query ngắn ngủn như SELECT * FROM users WHERE id = $1 mà bạn chắc chắn không thể nào chậm mà vẫn xuất hiện trong đây? Rồi có những câu query bạn cũng không biết nó đến từ server nào hay hành tinh nào? Không biết câu query nào chậm nhất để mà optimize? Không biết và không biết.

Một đống hỗn độn và gần như vô dụng!

pgBadger là gì?

pgBadger là một PostgreSQL log analyzer nhỏ gọn được viết bằng Perl script giúp bạn phân tích và report từ PostgreSQL log file.
Sẽ giúp bạn khai thác được file hỗn độn kia, đập đá ra vàng. Sau khi xử lý pgBadge sẽ xuất ra cho bạn một file HTML (hoặc JSON) report về file log đó.

Cài đặt

        tar xzf pgbadger-9.x.tar.gz
        cd pgbadger-9.x/
        perl Makefile.PL
        make && sudo make install
  • Trong quá trình cài đặt có thể bị lỗi và bạn cần cài thêm perl-devel. Cài đặt trong Centos qua rpm sudo yum install perl-devel

Update format log file

Để pgBadger có thể đọc được tốt file log, bạn cần update lại một chút trong config:

  • log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ' log ra các thông tin user, database name, application và client ip address
  • Enable các option log khác để lấy được nhiều thông tin hơn:
        log_checkpoints = on
        log_connections = on
        log_disconnections = on
        log_lock_waits = on
        log_temp_files = 0
        log_autovacuum_min_duration = 0
        log_error_verbosity = default

Parse log & export report

Cách đơn giản nhất:

pgbadger <file_log> <output.html>

Mặc định pgBadger sẽ để output là out.html nếu không được định nghĩa

Report

Khi mở file output html lên sẽ có rất nhiều thông tin phân tích từ log file đó, một số hình ảnh:
alt text
alt text
alt text
alt text


Bài viết được đăng lại từ blog của mình

Bình luận


White
{{ comment.user.name }}
Bỏ hay Hay
{{comment.like_count}}
Male avatar
{{ comment_error }}
Hủy
   

Hiển thị thử

Chỉnh sửa

White

Triet Pham

7 bài viết.
92 người follow
Kipalog
{{userFollowed ? 'Following' : 'Follow'}}
Cùng một tác giả
White
116 45
Bắt đầu chuyển sang dùng Vim làm editor chính một cách nghiêm túc sau nhiều lần thử, bỏ cuộc và quay trở về Sublime Text. Còn về nguyên nhân bỏ cuộ...
Triet Pham viết hơn 1 năm trước
116 45
White
34 7
(Ảnh) Data system Ngày nay do sự phát triển rất nhanh về phần cứng nên hầu hết các ứng dụng không còn phát triển theo hướng tối ưu hóa về tốc đ...
Triet Pham viết 1 năm trước
34 7
White
23 3
Các thuật toán sắp xếp newsfeed Một trong những tính năng thường gặp của những trang tin tức/mạng xã hội là newsfeed. Thông thường các trang nà...
Triet Pham viết 7 tháng trước
23 3
Bài viết liên quan
White
4 4
Sáng đọc tin trên HackerNews thấy có một tin khá thú vị nên copy về đây Nội dung thay đổi mới nhất được tóm tắt ở (Link). Cụ thể, các commiters c...
cpplover viết hơn 3 năm trước
4 4
{{like_count}}

kipalog

{{ comment_count }}

bình luận

{{liked ? "Đã kipalog" : "Kipalog"}}


White
{{userFollowed ? 'Following' : 'Follow'}}
7 bài viết.
92 người follow

 Đầu mục bài viết

Vẫn còn nữa! x

Kipalog vẫn còn rất nhiều bài viết hay và chủ đề thú vị chờ bạn khám phá!