45 cách để tăng tốc cơ sở dữ liệu Firebird

Tại đây bạn có thể tìm thấy danh sách các mẹo tối ưu hiệu năng cho cơ sở dữ liệu Firebird trong nhiều lĩnh vực khác nhau - từ phần cứng/hệ điều hành và tinh chỉnh cấu hình Firebird đến các khuyến nghị tối ưu hóa SQL. Danh sách này không phải là tài liệu tham khảo đầy đủ về cách tối ưu hóa Firebird, và nó giả định rằng bạn đã hiểu những kiến thức cơ bản về cách Firebird hoạt động, chẳng hạn như kế hoạch thực thi, quản lý giao dịch và thống kê hiệu năng truy vấn.

Vui lòng áp dụng các mẹo này một cách thận trọng và xác minh hiệu quả của chúng trước khi đưa vào môi trường production.

Công ty chúng tôi (IBSurgeon) cung cấp dịch vụ tối ưu hóa hiệu năng cơ sở dữ liệu toàn diện.

1. Đặt cơ sở dữ liệu trên SSD

Đặt cơ sở dữ liệu của bạn trên SSD. Ổ SSD cung cấp khả năng IO ngẫu nhiên tốt hơn nhiều so với các ổ đĩa truyền thống. IO ngẫu nhiên rất quan trọng đối với việc đọc và ghi dữ liệu phân bố khắp tệp cơ sở dữ liệu lớn - phần lớn các thao tác cơ sở dữ liệu yêu cầu IO ngẫu nhiên song song chuyên sâu.

2. Sử dụng RAID 10

Nếu bạn đang dùng RAID1 hoặc RAID5, hãy cân nhắc RAID10 - nó nhanh hơn 15-25%.

3. Kiểm tra BBU

Nếu bạn đang sử dụng bộ điều khiển RAID, hãy kiểm tra rằng nó đã được lắp đặt và hoạt động với Bộ pin dự phòng (BBU) - một số nhà cung cấp không cung cấp BBU theo mặc định. Không có BBU, bộ điều khiển sẽ vô hiệu hóa bộ nhớ đệm, và RAID hoạt động rất chậm, thậm chí chậm hơn cả các ổ SATA thông thường. Thông thường, bạn có thể kiểm tra trạng thái BBU trong công cụ cấu hình RAID.

4. Đặt bộ nhớ đệm ghi thành write-back

Nếu bạn đang sử dụng bộ điều khiển RAID có lắp BBU (và máy chủ có UPS), hãy kiểm tra rằng bộ nhớ đệm của nó được đặt thành write-back (không phải write-through). «Write-back» kích hoạt bộ nhớ đệm ghi của bộ điều khiển.

5. Kích hoạt bộ nhớ đệm đọc

Nếu bạn sử dụng bộ điều khiển RAID, hãy kiểm tra rằng nó đã kích hoạt bộ nhớ đệm đọc.

6. Kiểm tra hệ thống con ổ đĩa

Kiểm tra các ổ đĩa của bạn để phát hiện bad block và các vấn đề phần cứng khác (bao gồm cả quá nhiệt). Các vấn đề phần cứng có thể làm giảm đáng kể hiệu năng IO và dẫn đến hỏng cơ sở dữ liệu.

7. Sử dụng SuperClassic hoặc Classic trong Firebird 2.5

Nếu bạn sử dụng Firebird 2.5 SuperServer với nhiều kết nối, hãy thử dùng SuperClassic hoặc Classic, chúng có thể mở rộng tốt hơn bằng cách sử dụng tất cả các lõi CPU.

8. Sử dụng SuperServer 3.0 trong Firebird 3.

Nếu bạn sử dụng Classic hoặc SuperClassic trong 2.5, hãy cân nhắc chuyển sang Firebird 3.0 SuperServer, giờ đây nó có thể sử dụng nhiều lõi và kết hợp với những lợi thế của bộ nhớ đệm chia sẻ.

9. Tăng bộ nhớ đệm page buffers

Tăng kích thước bộ nhớ đệm page buffers (tham số DefaultDBCachePages) so với giá trị mặc định. Đối với 2.5 SuperServer, chúng tôi khuyến nghị 10000 trang, đối với 3.0 SuperServer - 50000 trang, đối với Classic và SuperClassic - từ 256 đến 2048 trang. Tuy nhiên, đừng đặt giá trị bộ nhớ đệm page buffers quá cao - việc đồng bộ bộ nhớ đệm có chi phí của nó, và ý tưởng đưa toàn bộ cơ sở dữ liệu vào RAM bằng cách tinh chỉnh giá trị này sẽ không hiệu quả. Sử dụng các tệp cấu hình Firebird đã được tối ưu hóa sẵn tại đây: /vi/optimized-firebird-configuration/

10. Tăng kích thước bộ nhớ cho các thao tác sắp xếp

Tăng giá trị của tham số TempCacheLimit trong firebird.conf - nó chỉ định kích thước bộ nhớ đệm của không gian tạm thời cho việc sắp xếp. Các giá trị mặc định quá thấp (8Mb cho Classic và 64Mb cho SuperServer), hãy sử dụng ít nhất 64Mb cho Classic và 1Gb cho SuperServer và SuperClassic. Một lần nữa, hãy sử dụng các tệp cấu hình đã được tối ưu hóa từ mục #9.

11. Đặt Forced Writes thành Off (cẩn thận!)

Nếu bạn có hoạt động chèn hoặc cập nhật chuyên sâu (bạn có thể kiểm tra bằng HQbird MonLogger, để biết chi tiết xem trang 60 của HQbird User Guide), và nếu bạn đã cài đặt UPS và replication để bảo vệ khỏi lỗi phần cứng, hãy cân nhắc đặt cài đặt Forced Writes thành OFF, nó có thể tăng tốc độ các thao tác ghi lên đến 3 lần.

12. Tăng số lượng hash slots cho Classic/SuperClassic

Tăng giá trị của tham số LockHashSlots cho Classic và SuperClassic từ giá trị mặc định 1009 lên một số nguyên tố lớn (ví dụ 30011), nó sẽ giảm hàng đợi trong cơ chế khóa nội bộ.

13. Sử dụng CPU Affinity cho Super Server 2.5

Nếu bạn sử dụng SuperServer 2.5, hãy đặt tham số CPUAffinity thành giá trị bằng với số lượng cơ sở dữ liệu đang sử dụng: SuperServer trong 2.5 có thể sử dụng các lõi CPU khác nhau để xử lý các yêu cầu cho các cơ sở dữ liệu nhất định.

14. Sử dụng ổ đĩa nhanh cho không gian tạm

Đặt phần đầu tiên của tham số TempDirectory trong firebird.conf vào ổ đĩa nhanh - SSD hoặc RAM drive. Nó sẽ giảm thời gian của các thao tác sắp xếp lớn - ví dụ khi cơ sở dữ liệu đang được khôi phục.

15. Lưu trữ bản sao lưu cơ sở dữ liệu trên một ổ đĩa khác

Lưu trữ bản sao lưu cơ sở dữ liệu trên ổ đĩa vật lý (RAID) chuyên dụng. Nó sẽ tách biệt IO đọc và ghi trong quá trình sao lưu, và tăng tốc độ sao lưu và giảm tải cho ổ đĩa chính. Điều này đặc biệt quan trọng khi bản sao lưu được thực hiện trong khi người dùng đang làm việc với cơ sở dữ liệu. Thông tin chi tiết hơn về cấu hình phần cứng cho Firebird có thể được tìm thấy trong " Firebird Hardware Guide".

16. Vô hiệu hóa các chỉ mục cho việc chèn hàng loạt

Nếu bạn chèn hoặc cập nhật nhiều bản ghi (hơn 25% của bảng), hãy vô hiệu hóa các chỉ mục cho bảng nơi các bản ghi được chèn và kích hoạt lại chúng sau khi chèn hoặc cập nhật. Thao tác xây dựng lại chỉ mục có thể nhanh hơn nhiều lần cập nhật chỉ mục.

17. Sử dụng Global Temporary Tables cho việc chèn nhanh

Để tăng tốc độ chèn và cập nhật, hãy sử dụng Global Temporary Tables cho việc chèn hàng loạt các bộ bản ghi lớn, sau đó chuyển các bản ghi vào bảng vĩnh viễn. Việc chèn bản ghi vào GTT, xử lý trước chúng và sau đó chuyển sang bảng lưu trữ vĩnh viễn có thể rất hiệu quả.

18. Tránh các chỉ mục không cần thiết

Sử dụng ít chỉ mục hơn cho các bảng có hoạt động chèn và cập nhật chuyên sâu. Mỗi chỉ mục thêm chi phí đáng kể cho các thao tác chèn, cập nhật, xóa và thu gom rác - có thể có thêm 3-4 lần đọc và ghi trang khi một bản ghi đơn lẻ được chèn/cập nhật/xóa/dọn dẹp cho mỗi chỉ mục.

19. Thay thế UDF bằng các lời gọi hàm tích hợp

Thay thế các lời gọi UDF bằng các lời gọi hàm tích hợp. Nhiều hàm tích hợp đã được thêm vào trong các phiên bản gần đây của Firebird, cung cấp chức năng trước đây chỉ có trong các thư viện UDF. Thay thế các hàm như vậy khi có thể, vì các hàm tích hợp hoạt động nhanh hơn UDF tới 3 lần.

20. Sử dụng giao dịch chỉ đọc cho các thao tác đọc

Sử dụng giao dịch chỉ đọc cho các thao tác không thay đổi bản ghi (tức là SELECT) với chế độ cô lập = read committed. Các giao dịch như vậy không giữ lại các phiên bản bản ghi khỏi việc thu gom rác, và có thể chạy vô thời hạn: chúng không ảnh hưởng đến hiệu năng cơ sở dữ liệu.

21. Sử dụng các giao dịch ghi ngắn và loại bỏ TẤT CẢ các giao dịch chạy dài

Sử dụng các giao dịch ghi ngắn (cho các thao tác INSERT/UPDATE/DELETE).

Giao dịch ghi càng ngắn càng tốt. Các giao dịch ngắn giữ lại số lượng phiên bản bản ghi khỏi việc thu gom rác ít hơn theo tỷ lệ so với các giao dịch chạy dài. Thật không may, ngay cả một giao dịch chạy dài duy nhất (ví dụ từ công cụ phát triển bị bỏ ngỏ) cũng có thể phá hỏng hiệu quả tốt của tất cả các giao dịch ghi ngắn khác. Đó là lý do tại sao bạn cần theo dõi các giao dịch chạy dài và sửa các vị trí thích hợp trong mã nguồn. Sử dụng công cụ HQbird DataGuard để nhận cảnh báo về giao dịch hoạt động lâu nhất trong cơ sở dữ liệu Firebird (ứng dụng nào đã khởi tạo nó, địa chỉ IP nào, dấu thời gian bắt đầu của nó), và công cụ HQbird MonLogger để xem danh sách đầy đủ các giao dịch hoạt động chạy dài và thống kê IO của chúng. Ngoài ra, nếu bạn đang sử dụng các thành phần/thư viện truy cập cơ sở dữ liệu có thể lưu trữ bộ bản ghi, hãy sử dụng cached updates.

22. Tránh các chuỗi bản ghi dài

Tránh các tình huống khi một bản ghi có nhiều phiên bản bản ghi - Firebird hoạt động chậm hơn nhiều với các chuỗi bản ghi dài. (để xem một số bảng có bao nhiêu phiên bản bản ghi, và chuỗi bản ghi dài nhất là bao nhiêu, bạn có thể sử dụng công cụ HQbird IBAnalyst, tab Tables, sắp xếp theo “Max Version”). Sử dụng kết hợp chèn và xóa theo lịch các bản ghi cũ thay vì nhiều lần cập nhật cùng một bản ghi.

23. Sử dụng PREPARE đúng cách

Sử dụng prepared statements để chạy các truy vấn SQL nơi chỉ các tham số thay đổi - ví dụ, thực hiện prepare trước vòng lặp của các truy vấn như vậy. Prepare có thể mất thời gian đáng kể (đặc biệt đối với các bảng lớn), và việc chuẩn bị truy vấn chỉ một lần sẽ tăng đáng kể hiệu năng tổng thể.

24. Không COMMIT quá thường xuyên trong thao tác chèn/cập nhật hàng loạt

Trong trường hợp thao tác INSERT/UPDATE/DELETE hàng loạt, không commit giao dịch sau mỗi thay đổi (điều này có thể xảy ra nếu bạn đang sử dụng tùy chọn auto commit trong driver cơ sở dữ liệu của mình) - commit giao dịch ít nhất sau 1000 thao tác hoặc nhiều hơn. Mỗi lần commit giao dịch chạy một số thao tác IO đọc/ghi đối với cơ sở dữ liệu, đó là lý do tại sao commit thường xuyên làm giảm hiệu năng cơ sở dữ liệu.

25. “Tắt” các chỉ mục nếu bạn đang sử dụng IN với nhiều hằng số

Nếu bạn đang sử dụng cấu trúc WHERE fieldX IN (Constant1, Constant2,… ConstantN), và có một chỉ mục trên fieldX, Firebird sẽ sử dụng chỉ mục nhiều lần bằng số hằng số trong danh sách IN. Vô hiệu hóa tìm kiếm chỉ mục bằng cách biến fieldX thành biểu thức +0: WHERE fieldX+0 IN (Constant1, Constant2,… ConstantN), hoặc, đối với chuỗi, sử dụng fieldX||''

26. Thay thế IN bằng JOIN

Tránh sử dụng các truy vấn với WHERE IN lồng nhau (SELECT… WHERE IN (SELECT.. WHERE IN() )), nó có thể gây nhầm lẫn cho bộ tối ưu hóa Firebird. Chuyển đổi các IN lồng nhau thành các join.

27. Sử dụng LEFT JOIN đúng cách

Nếu bạn đang sử dụng LEFT OUTER join, hãy đặt rõ ràng các bảng trong join từ bảng nhỏ nhất đến bảng lớn nhất.

28. Giới hạn việc lấy dữ liệu của các truy vấn SELECT

Luôn cố gắng giới hạn đầu ra lớn cho các truy vấn SELECT bằng các mệnh đề FIRST… SKIP hoặc ROWS. Nếu truy vấn không được thiết kế đặc biệt như một báo cáo (yêu cầu tất cả các bản ghi phải được in/xuất), thông thường chỉ cần hiển thị 10-100 bản ghi hàng đầu là đủ. Chỉ lấy các bản ghi cần thiết.

29. Chỉ định ít cột hơn trong SELECT với ORDER BY/GROUP BY

Giảm số lượng cột và tổng chiều rộng của chúng trong các truy vấn với ORDER BY/GROUP BY cả trong phần SELECT (tức là các trường được hiển thị) và trong mệnh đề ORDER BY. Firebird hợp nhất các cột từ mệnh đề SELECT và ORDER BY/GROUP BY và sắp xếp chúng trong bộ nhớ (hoặc, nếu không đủ bộ nhớ, trên ổ đĩa). Vì vậy, nếu có một VARCHAR dài trong SELECT, kích thước của các tệp sắp xếp có thể thực sự lớn (nhiều gigabyte). Giảm số lượng trường chỉ cho những trường phải được sắp xếp và join muộn với các trường lớn để hiển thị có thể tăng đáng kể (x3-x10) tốc độ của một truy vấn với ORDER BY/GROUP BY.

30. Sử dụng derived tables để tối ưu hóa SELECT với ORDER BY/GROUP BY

Một cách khác để tối ưu hóa truy vấn SQL với việc sắp xếp là sử dụng derived tables để tránh các thao tác sắp xếp không cần thiết. Thay vì

SELECT FIELD_KEY, FIELD1, FIELD2, ... FIELD_NFROM TORDER BY FIELD2

sử dụng sửa đổi sau:

SELECT T.FIELD_KEY, T.FIELD1, T.FIELD2, ... T.FIELD_NFROM (SELECT FIELD_KEY FROM T ORDER BY FIELD2) T2JOIN T ON T.FIELD_KEY = T2.FIELD_KEY

31. Lưu trữ chuỗi ngắn trong VARCHAR, chuỗi lớn trong BLOB

Để lưu trữ dữ liệu ký tự ngắn, hãy sử dụng VARCHAR, để lưu trữ văn bản dài, hãy sử dụng BLOB. Varchar nhanh hơn cho các phần dữ liệu nhỏ vì chúng được lưu trữ trong bản ghi, và toàn bộ bản ghi được đọc trong cùng một chu kỳ IO, và nếu kích thước bản ghi nhỏ hơn 2/3 kích thước trang cơ sở dữ liệu, toàn bộ bản ghi được lưu trữ trên cùng một trang cơ sở dữ liệu. BLOB được lưu trữ bên ngoài bản ghi, và yêu cầu thêm một vòng IO để đọc nó, và chúng thể hiện lợi thế với việc đọc và ghi chuỗi dài.

32. Loại trừ các cột BLOB khỏi các SELECT lớn

Loại trừ các cột BLOB khỏi các SELECT lớn. Sử dụng một loại liên kết muộn với các sub-select để hiển thị có chọn lọc thông tin từ BLOB (ví dụ, hiển thị nội dung của tài liệu).

33. Sử dụng BIGINT cho khóa chính và khóa duy nhất

Sử dụng kiểu BIGINT cho khóa chính và khóa duy nhất tự động tăng và cho các định danh của tất cả các loại. Các thao tác với BIGINT là nhanh nhất, và BIGINT có đủ dung lượng để lưu trữ hầu hết tất cả các phạm vi dữ liệu.

34. Không sử dụng VARCHAR cho khóa

Không sử dụng VARCHAR cho các định danh trừ khi thực sự cần thiết - các thao tác với chúng kém hiệu quả hơn nhiều so với cột số nguyên. Đặc biệt tránh sử dụng GUID làm định danh - do phân bố ngẫu nhiên của các giá trị GUID, các thao tác INSERT/UPDATE với Khóa chính/Khóa duy nhất dạng GUID có thể chậm hơn 20 lần so với số nguyên.

35. Tính toán lại thống kê chỉ mục

Tính toán lại thống kê chỉ mục thường xuyên. Cập nhật thống kê chỉ mục cho các bảng có thay đổi thường xuyên hoặc lớn bằng lệnh SET STATISTICS, điều này cho phép bộ tối ưu hóa Firebird chọn kế hoạch SQL tốt hơn. HQbird Firebird DataGuard có thể thực hiện tính toán lại thống kê chỉ mục tự động theo lịch mong muốn (thường là mỗi tuần một lần).

36. Sử dụng connection pool

Nếu các kết nối đến cơ sở dữ liệu Firebird ngắn (điển hình cho các trang web), hãy sử dụng connection pool - ví dụ, trong PHP sử dụng hàm ibase_pconnect thay vì ibase_connect

37. Sử dụng tùy chọn LINGER trong Firebird 3.0

Nếu các kết nối cơ sở dữ liệu ngắn và bạn đang sử dụng Firebird 3+, hãy sử dụng tùy chọn LINGER để giữ cache hoạt động trong khoảng thời gian được chỉ định, nó sẽ giữ các trang thường dùng trong cache ngay cả khi không có kết nối nào khác. Ví dụ, ALTER DATABASE SET LINGER TO 60 sẽ giữ cache trong 60 giây sau khi kết nối cuối cùng kết thúc.

38. Sử dụng HASH JOIN

Trong Firebird 3.0, trong trường hợp kết nối bảng lớn và bảng nhỏ, HASH JOIN có thể nhanh hơn nhiều so với join thông thường sử dụng «nested loop» với chỉ mục. Để bộ tối ưu hóa Firebird sử dụng HASH join, hãy sử dụng +0 trong điều kiện join: T1 JOIN T2 ON T1.FIELD1+0 = T2.FIELD2+0. Kiểm tra kết quả tối ưu hóa trước khi đưa vào môi trường production!

39. Đánh dấu các hàm PSQL phù hợp là DETERMINISTIC

Đánh dấu các hàm PSQL của bạn (trong Firebird 3+) không có tham số và trả về giá trị hằng bằng từ khóa DETERMINISTIC. Các hàm deterministic được tính toán và lưu vào cache trong phạm vi của truy vấn hiện tại.

40. Sử dụng hàm phân tích (window) trong Firebird 3.0

Nếu bạn đang chạy SELECT với việc xuất đồng thời một cột nào đó và hàm tổng hợp cho nó, hãy sử dụng hàm window (phân tích) - nó nhanh hơn truy vấn con hoặc 2 truy vấn. Ví dụ:

Select id, department, salary, salary / (select sum(salary) from employee) percentagefrom employee

thay thế bằng

Select id, department, salary, salary / sum(salary) OVER () percentage from employee

41. Sử dụng switch -se cho gbak

Sử dụng switch -se để tăng tốc độ sao lưu và/hoặc phục hồi của gbak lên đến 20%, ví dụ

gbak -b -g -se service_mgr c:\db\data.fdb e:\backup\data.fbk

42. WHERE CURRENT OF

Cách nhanh nhất để xử lý các bản ghi được lấy bởi con trỏ trong PSQL là mệnh đề ‘where current of <>’. Nó nhanh hơn ‘where rb$db_key = :v_db_key’ và nhanh hơn nhiều so với tìm kiếm bằng khóa chính hoặc khóa duy nhất.

43. Tránh truy vấn thường xuyên đến bảng giám sát

Không chạy truy vấn đến các bảng giám sát Firebird (MON$) quá thường xuyên - các truy vấn như vậy tiêu tốn tài nguyên đáng kể và có thể làm giảm đáng kể hiệu suất của logic nghiệp vụ chính. Chúng tôi khuyến nghị chạy truy vấn MON$ không thường xuyên hơn một lần mỗi phút. Để giám sát liên tục các truy vấn/giao dịch/kết nối Firebird, hãy sử dụng công cụ HQbird PerfMon hỗ trợ Trace API (xem trang 66 của HQbird User Guide để biết chi tiết) .

44. Sử dụng tùy chọn NO_AUTO_UNDO cho chèn/cập nhật hàng loạt

Nếu bạn đang chạy nhiều lệnh DML (Update/Insert/Delete) trong khuôn khổ của cùng một giao dịch, Firebird hợp nhất undo-log của mỗi lệnh với undo-log của giao dịch. Để tăng tốc các thao tác DML hàng loạt, hãy bắt đầu giao dịch với tùy chọn «NO AUTO UNDO», để không hợp nhất undo-log của mỗi lệnh với undo-log của giao dịch.

45. Không sử dụng xác thực SRP trong Firebird 3 nếu bạn không cần

Không sử dụng xác thực người dùng SRP (Firebird 3.0+) nếu bạn không thực sự cần - kết nối với xác thực SRP được thiết lập chậm hơn so với kết nối thông thường.

Thay cho phần tóm tắt

Tối ưu hóa hiệu suất đòi hỏi phải xem xét nhiều yếu tố và có thể thực sự phức tạp. Nếu bạn đã thử tất cả những điều trên, hãy cân nhắc thuê dịch vụ tối ưu hóa hiệu suất cơ sở dữ liệu chuyên nghiệp.

Liên hệ với chúng tôi

Bạn có bất kỳ câu hỏi nào? Đừng ngần ngại liên hệ với chúng tôi qua email!