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 hiệu suất cho cơ sở dữ liệu Firebird trong nhiều lĩnh vực khác nhau - từ phần cứng/HĐH 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à giả định rằng bạn hiểu các kiến thức cơ bản về hoạt động của Firebird, chẳng hạn như kế hoạch thực thi, quản lý giao dịch và số liệu thống kê hiệu suất 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 sản xuất.

Công ty chúng tôi (IBSurgeon) cung cấp dịch vụ tối ưu hóa hiệu suất 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 IO ngẫu nhiên tốt hơn nhiều so với ổ đĩa truyền thống. IO ngẫu nhiên rất quan trọng cho việc đọc và ghi dữ liệu phân bố trong 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 sử dụng RAID1 hoặc RAID5, hãy cân nhắc RAID10 - nhanh hơn 15-25%.

3. Kiểm tra BBU

Nếu bạn sử dụng bộ điều khiển RAID, hãy kiểm tra xem nó có Bộ pin dự phòng (BBU) được lắp đặt và hoạt động hay không - một số nhà cung cấp không cung cấp BBU theo mặc định. Nếu không có BBU, bộ điều khiển sẽ vô hiệu hóa bộ nhớ cache và RAID hoạt động rất chậm, thậm chí chậm hơn 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ớ cache ghi ở chế độ write-back

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

5. Bật bộ nhớ cache đọc

Nếu bạn sử dụng bộ điều khiển RAID, hãy kiểm tra xem nó đã bật bộ nhớ cache đọc hay chưa.

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

Kiểm tra ổ đĩa của bạn để phát hiện các bad block và các sự cố phần cứng khác (bao gồm cả quá nhiệt). Các sự cố phần cứng có thể làm giảm đáng kể hiệu suất 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ử sử 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 nâng cấp lên Firebird 3.0 SuperServer, hiện tại nó có thể sử dụng nhiều lõi và kết hợp với các ưu điểm của bộ nhớ cache dùng chung.

9. Tăng bộ nhớ cache page buffers

Tăng kích thước bộ nhớ cache page buffers (tham số DefaultDBCachePages) so với các 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ớ cache page buffers quá cao - việc đồng bộ hóa bộ nhớ cache 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 điều 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ị tham số TempCacheLimit trong firebird.conf - tham số này chỉ định kích thước bộ nhớ cache của không gian tạm thời để 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ừ #9.

11. Tắt Forced Writes (thận trọng!)

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, chi tiết xem trang 60 của Hướng dẫn sử dụng HQbird), và nếu bạn có UPS và bản sao lưu được cài đặt để bảo vệ khỏi sự cố phần cứng, hãy cân nhắc đặt cài đặt Forced Writes thành OFF, điều này có thể tăng tố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ị tham số LockHashSlots cho Classic và SuperClassic từ mặc định 1009 lên một số nguyên tố lớn (ví dụ: 30011), điều này 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 thời

Đặt phần đầu tiên của tham số TempDirectory trong firebird.conf vào ổ đĩa nhanh - SSD hoặc ổ RAM. Điều này sẽ giảm thời gian của các thao tác sắp xếp lớn - ví dụ như 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ữ các bản sao lưu cơ sở dữ liệu trên ổ đĩa vật lý chuyên dụng (RAID). Điều này sẽ tách biệt IO đọc và ghi trong quá trình sao lưu, 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 các 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 " Hướng dẫn phần cứng Firebird".

16. Vô hiệu hóa chỉ mục cho các thao tá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% 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 Bảng tạm thời toàn cục để chèn nhanh

Để tăng tốc độ chèn và cập nhật, hãy sử dụng Bảng tạm thời toàn cục để chèn hàng loạt các tập 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 các bản ghi vào GTT, xử lý trước chúng rồi chuyển sang bảng cố định 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à dọn dẹp rác - có thể có 3-4 lần đọc và ghi trang bổ sung khi một bản ghi duy nhất được chèn/cập nhật/xóa/làm sạch cho mỗi chỉ mục.

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

Thay thế các lệnh gọi UDF bằng các lệnh 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. Hãy 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 tới 3 lần so với UDF.

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 quá trình dọn dẹp rác và có thể chạy vô thời hạn: chúng không ảnh hưởng đến hiệu suất cơ sở dữ liệu.

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

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 thì càng tốt. Các giao dịch ngắn giữ lại số lượng phiên bản bản ghi từ quá trình dọn dẹp rác ít hơn theo tỷ lệ so với các giao dịch chạy lâu. Thật không may, ngay cả một giao dịch chạy lâu duy nhất (ví dụ: từ công cụ phát triển để mở) 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 giám sát các giao dịch chạy lâu 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 đã bắt đầu 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 lâu và số liệu 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 cập nhật được lưu trữ.

22. Tránh 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à gì, 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 trình 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 các câu lệnh đã chuẩn bị để chạy các truy vấn SQL trong đó chỉ có các tham số thay đổi - ví dụ: thực hiện prepare trước vòng lặp 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ẽ làm tăng đáng kể hiệu suất 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, đừ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 tự động commit trong trình điều khiển cơ sở dữ liệu của mình) - hãy 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 việc commit thường xuyên làm giảm hiệu suất 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ó chỉ mục trên fieldX, Firebird sẽ sử dụng chỉ mục nhiều lần bằng số lượng 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 có WHERE IN lồng nhau (SELECT… WHERE IN (SELECT.. WHERE IN() )), điều này có thể làm bối rối trình 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 joins, hãy đặt các bảng trong join một cách rõ ràng từ nhỏ nhất đến lớn nhất.

28. Giới hạn việc tìm nạp 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ụ thể như một báo cáo (yêu cầu tất cả các bản ghi được in/xuất), thông thường chỉ cần hiển thị 10-100 bản ghi hàng đầu là đủ. Chỉ tìm nạp 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 có ORDER BY/GROUP BY cả trong phần SELECT (tức là các trường cần hiển thị) và trong mệnh đề ORDER BY. Firebird hợp nhất các cột từ SELECT và các mệnh đề 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). Việc 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 cần hiển thị có thể làm tăng đáng kể (x3-x10) tốc độ của truy vấn có ORDER BY/GROUP BY.

30. Sử dụng các bảng dẫn xuất để 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 có sắp xếp là sử dụng các bảng dẫn xuất để 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 biến thể 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 BLOBs

Để 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 BLOBs. 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. BLOBs được lưu trữ bên ngoài bản ghi và yêu cầu thêm một vòng IO để đọc chúng, và chúng thể hiện lợi thế khi đọc và ghi các 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ị thông tin từ BLOBs một cách có chọn lọc (ví dụ: hiển thị nội dung của tài liệu).

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

Sử dụng loại BIGINT cho các khóa chính và khóa duy nhất tự 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ữ gần như tất cả các phạm vi dữ liệu.

34. Đừng dùng VARCHAR cho khóa

Đừng 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ác cột số nguyên. Đặc biệt tránh dùng GUID làm định danh - do sự 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 GUID có thể chậm hơn tới 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 các kế hoạch SQL tốt hơn. HQbird Firebird DataGuard có thể tự động thực hiện việc tính toán lại thống kê chỉ mục theo lịch trình 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 cơ sở dữ liệu đến Firebird ngắn (điển hình cho các trang web), hãy sử dụng connection pool - ví dụ, trong PHP 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 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 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 thúc kết nối cuối cùng.

38. Sử dụng HASH JOIN

Trong Firebird 3.0, khi join các bảng lớn và 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 dùng HASH join, hãy thêm +0 vào đ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 sản xuất!

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 số bằng từ khóa DETERMINISTIC. Các hàm xác định (deterministic) được tính toán và lưu cache trong phạm vi 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 đồng thời xuất ra một cột và hàm tổng hợp cho cột đó, hãy sử dụng hàm window (phân tích) - nó nhanh hơn subquery hoặc 2 truy vấn. Ví dụ:

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

thay 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 gbak lên tới 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 các bảng giám sát

Đừ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 hiệu suất của logic nghiệp vụ chính. Chúng tôi khuyến nghị chạy các 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 các thao tác 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 cùng một giao dịch, Firebird sẽ 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 phải 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

Đừ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ó câu hỏi nào không? Đừng ngần ngại liên hệ với chúng tôi qua email!