Hàm và thủ tục

Đưa logic vào cơ sở dữ liệu là một quyết định thiết kế, nhưng nó cũng là một quyết định về hiệu năng. Phần này đo cái giá của việc gọi hàm, và tìm ra một mặc định làm chậm truy vấn gấp đôi mà không ai được cảnh báo.

Gọi hàm hai triệu lần

Cùng một phép nhân, viết bốn cách, mỗi cách đo ba lần:

PL/pgSQL đắt gấp gần ba lần hàm SQL, và gấp bảy lần so với viết thẳng. Mỗi lần gọi phải dựng một ngữ cảnh thực thi, cấp phát biến, chạy, rồi dọn.

IMMUTABLE không giúp gì ở đây, và điều đó đúng như thiết kế: IMMUTABLE chỉ cho phép PostgreSQL tính một lần khi tham số là hằng số. Ở đây tham số là giá trị của từng dòng, khác nhau mỗi lần, nên không có gì để tái sử dụng.

Nhưng đừng bỏ IMMUTABLE vì thế — phần 14 đã đo rằng nó là điều kiện bắt buộc để dựng chỉ mục biểu thức. Nó chỉ không phải công cụ tăng tốc gọi hàm.

Hàm SQL đơn giản được nội tuyến hoá

create function gia_co_vat(g numeric) returns numeric
as $$ select g * 1.1 $$ language sql immutable;

Kế hoạch của hai câu truy vấn:

where gia * 1.1 > 1000          Filter: ((gia * 1.1) > '1000'::numeric)
where gia_co_vat(gia) > 1000    Filter: ((gia * 1.1) > '1000'::numeric)

Giống hệt nhau. PostgreSQL thay lời gọi hàm bằng chính thân hàm, và từ đó bộ tối ưu làm việc với biểu thức trần.

Điều này chỉ áp cho hàm SQL có thân là một câu SELECT duy nhất. Hàm PL/pgSQL không bao giờ được nội tuyến — đó là lý do nó đắt gấp 7 lần trong bảng trên.

Vậy nếu được nội tuyến thì sao vẫn chậm hơn?

Mặc định PARALLEL UNSAFE làm chậm gấp đôi

Đây là phần tôi không lường trước.

Cùng một biểu thức sau khi nội tuyến, cùng một kết quả, chậm gấp đôi — chỉ vì một thuộc tính khai báo.

CREATE FUNCTION mặc định gán PARALLEL UNSAFE cho mọi hàm mới. PostgreSQL không thể tự biết hàm của bạn có an toàn khi chạy trong tiến trình phụ hay không, nên nó chọn giả định thận trọng nhất.

Hậu quả: chỉ cần một hàm như vậy xuất hiện ở bất kỳ đâu trong truy vấn là toàn bộ truy vấn mất khả năng chạy song song. Không có cảnh báo, không có dòng nào trong EXPLAIN nói ra lý do — bạn chỉ thấy Seq Scan thay vì Parallel Seq Scan.

Trên bảng vài trăm triệu dòng với máy chủ 16 lõi, khác biệt không phải gấp đôi mà là gấp mười.

Cách sửa là một câu:

alter function gia_co_vat(numeric) parallel safe;

Hoặc khai ngay lúc tạo. Tìm những hàm đang bị mặc định:

select n.nspname, p.proname, p.proparallel
from pg_proc p join pg_namespace n on n.oid = p.pronamespace
where n.nspname not in ('pg_catalog','information_schema')
  and p.proparallel = 'u';

Chỉ khai PARALLEL SAFE khi hàm thật sự an toàn: không ghi vào bảng, không đọc trạng thái phiên, không dùng chuỗi, không gọi hàm không an toàn khác. Hàm tính toán thuần thì luôn an toàn.

Vòng lặp so với xử lý theo tập

Cập nhật 200.000 dòng:

Hai điều đọc ra được.

Bọc một câu lệnh vào hàm gần như miễn phí. 7% cho một lần gọi hàm bao quanh một lệnh chạm 200.000 dòng — chi phí gọi hàm chia đều ra thì bằng không. Nếu bạn muốn đóng gói logic vào hàm để tái sử dụng, đây không phải lý do để ngần ngại.

Chuyển sang vòng lặp mới là chỗ mất tiền. Ba lần chậm hơn, và đó là khi vòng lặp chạy bên trong cơ sở dữ liệu, không có vòng đi–về mạng nào. Nếu vòng lặp đó nằm ở tầng ứng dụng, phần 37 đã đo con số tương ứng: 7.278 dòng mỗi giây, tức khoảng 27 giây cho 200.000 dòng — chậm hơn 47 lần.

Quy tắc: đưa câu lệnh vào hàm, đừng đưa vòng lặp vào hàm.

Hàm và thủ tục khác nhau ở một chỗ

create function f() returns void as $$
begin insert into r values (...); commit; end $$ language plpgsql;

select f();
--  ERROR:  invalid transaction termination

create procedure p() as $$
begin insert into r values (...); commit; end $$ language plpgsql;

call p();
--  chạy được, dòng đã được ghi

Hàm luôn chạy bên trong giao dịch của người gọi — nó là một phần của một biểu thức, và một biểu thức không thể tự kết thúc giao dịch chứa nó. Thủ tục thì được gọi bằng CALL như một câu lệnh độc lập, nên nó điều khiển được giao dịch.

Điều này quan trọng cho công việc theo lô. Một thủ tục xử lý mười triệu dòng có thể COMMIT sau mỗi 10.000 dòng:

create procedure xu_ly_lo() as $$
declare i int := 0;
begin
  loop
    update viec set tt='xong' where id in (
      select id from viec where tt='cho' limit 10000 for update skip locked);
    exit when not found;
    commit;                  -- chốt từng lô
    i := i + 1;
  end loop;
end $$ language plpgsql;

Không có COMMIT giữa chừng, cả mười triệu dòng nằm trong một giao dịch — và phần 21 đã đo hậu quả của giao dịch dài: VACUUM không dọn được gì, bảng phình gấp năm lần.

Khi nào đưa logic vào cơ sở dữ liệu

Nên:

• Logic phải đúng bất kể ai gọi — kể cả người chạy SQL bằng tay. CHECK và trigger đã bàn ở phần 39 và 40; hàm là mức tiếp theo.
• Thao tác cần nhiều vòng đi–về nếu làm ở ứng dụng. Một hàm đọc, tính, rồi ghi trong một lần gọi thay cho ba vòng mạng.
• Công việc theo lô lớn — thủ tục với COMMIT giữa chừng là công cụ đúng.

Không nên:

• Logic nghiệp vụ phức tạp. Khó kiểm thử, khó theo dõi phiên bản, khó gỡ lỗi — và người bảo trì sau bạn sẽ không nghĩ tới việc tìm ở đó.
• Hàm gọi trên từng dòng của bảng lớn. 7,2 lần là con số thật, và nó nhân lên theo số dòng.
• Bất cứ thứ gì gọi ra hệ thống ngoài. Cùng lý do đã nói ở phần 40: nó nằm trong giao dịch.

Thử ba mươi giây

Tìm hàm đang tắt chạy song song trên hệ thống của bạn bằng câu truy vấn ở trên. Với mỗi hàm là phép tính thuần — không đọc bảng, không ghi gì — hãy đặt parallel safe.

Và kiểm xem hàm nào đang được gọi nhiều nhất:

select funcname, calls, round(total_time::numeric) as tong_ms,
       round(self_time::numeric / nullif(calls,0), 4) as tb_moi_lan_ms
from pg_stat_user_functions
order by total_time desc limit 10;

Cần bật track_functions = 'pl' hoặc 'all'. Hàm nào có calls lên tới hàng triệu và tb_moi_lan_ms rất nhỏ chính là loại nên viết thẳng vào truy vấn — cái giá không nằm ở mỗi lần gọi, mà ở số lần gọi.

Phần sau đo kết nối và connection pool: max_connections thật sự chịu được bao nhiêu, và vì sao con số đó nhỏ hơn nhiều so với người ta nghĩ.