IBAnalyst: Hiểu về Cơ sở dữ liệu của Bạn

Dmitri Kuzmenko, [email protected], cập nhật lần cuối 31-Tháng 3-2014

Tôi đã làm việc với InterBase từ năm 1994. Hồi đó, hầu hết các cơ sở dữ liệu đều nhỏ và không cần tinh chỉnh gì. Tất nhiên, có những lúc tôi phải thay đổi ibconfig trên máy chủ, cấu hình lại phần cứng hoặc hệ điều hành, nhưng đó gần như là tất cả những gì tôi có thể làm để tối ưu hiệu suất.

Bốn năm trước, công ty chúng tôi bắt đầu cung cấp dịch vụ hỗ trợ kỹ thuật và đào tạo cho người dùng InterBase. Làm việc với nhiều cơ sở dữ liệu sản xuất cũng dạy tôi rất nhiều điều khác nhau. Tuy nhiên, hầu hết những gì tôi học được đều liên quan đến ứng dụng - cách sử dụng tham số giao dịch, tối ưu hóa truy vấn và tập kết quả.

Tất nhiên, tôi đã biết từ khá lâu về gstat - công cụ cung cấp thông tin thống kê cơ sở dữ liệu. Nếu bạn từng xem đầu ra của gstat hoặc đọc opguide.pdf về nó, bạn sẽ biết rằng đầu ra thống kê trông giống như một loạt các con số và không có gì khác. Được rồi, bạn có thể phát hiện thông tin phân mảnh cho một bảng hoặc chỉ mục cụ thể, nhưng còn thông tin hữu ích nào khác có thể thu được?

May mắn thay, trước khi làm việc với InterBase, tôi đã quan tâm đến các cấu trúc dữ liệu khác nhau, cách chúng được lưu trữ và thuật toán chúng sử dụng. Điều này giúp tôi diễn giải đầu ra của gstat. Vào thời điểm đó, tôi quyết định viết một công cụ có thể phân tích đầu ra gstat để hỗ trợ tinh chỉnh cơ sở dữ liệu hoặc ít nhất là xác định nguyên nhân của các vấn đề hiệu suất.

Câu chuyện dài, nhưng kết quả là IBAnalyst đã được tạo ra. Bất chấp kinh nghiệm của tôi, nó vẫn cho phép tôi tìm thấy những điều rất thú vị hoặc các vấn đề hiệu suất trong các cơ sở dữ liệu khác nhau.

Các hệ thống thực tế có hiệu suất chạy dao động như một làn sóng. Biên độ của những ’làn sóng’ này có thể thấp hoặc cao, vì vậy bạn có thể thấy hiệu suất khác nhau từ ngày này sang ngày khác (hoặc từng giờ). Hiệu suất thực tế phụ thuộc vào nhiều yếu tố, bao gồm thiết kế ứng dụng, cấu hình máy chủ, độ đồng thời của giao dịch, rác phiên bản trong cơ sở dữ liệu, v.v. Để tìm hiểu điều gì đang xảy ra trong cơ sở dữ liệu (cả khía cạnh tích cực và tiêu cực của hiệu suất), bạn ít nhất nên thỉnh thoảng xem qua thống kê cơ sở dữ liệu.

Hãy xem xét các khả năng của IBAnalyst. IBAnalyst có thể lấy thống kê từ gstat hoặc Services API và biên dịch chúng thành một báo cáo cung cấp cho bạn thông tin đầy đủ về cơ sở dữ liệu, các bảng và chỉ mục của nó. Nó có các cảnh báo tại chỗ có sẵn khi duyệt thống kê; nó cũng bao gồm các nhận xét gợi ý và báo cáo khuyến nghị.

Thông tin cơ sở dữ liệu

Hình 1 Tóm tắt thống kê cơ sở dữ liệu

Bản tóm tắt trong Hình 1 cung cấp thông tin chung về cơ sở dữ liệu của bạn. Các cảnh báo hoặc nhận xét được hiển thị dựa trên kiến thức được thu thập cẩn thận từ một số lượng lớn các cơ sở dữ liệu sản xuất thực tế.

Lưu ý: Tất cả các hình trong bài viết này chứa thống kê gstat được lấy từ một cơ sở dữ liệu sản xuất thực tế (với sự cho phép của chủ sở hữu).

Như tôi đã nói trước đó, thống kê cơ sở dữ liệu thô trông khó hiểu và khó diễn giải. IBAnalyst làm nổi bật mọi vấn đề tiềm ẩn rõ ràng bằng màu vàng hoặc đỏ và chi tiết của vấn đề có thể được đọc chỉ bằng cách đặt con trỏ lên mục liên quan và đọc gợi ý được hiển thị.

Tiếp theo, chúng ta có thể thấy rằng tham số Forced Write được đặt thành OFF và được đánh dấu màu đỏ. InterBase 4.x và 5.x theo mặc định có tham số này là ON. Forced Writes tự nó là một phương pháp bộ nhớ đệm ghi: khi ON, nó ghi dữ liệu đã thay đổi ngay lập tức vào đĩa, nhưng OFF có nghĩa là các ghi sẽ được lưu trữ trong một khoảng thời gian không xác định bởi hệ điều hành trong bộ nhớ đệm tệp của nó. InterBase 6 tạo cơ sở dữ liệu với Forced Writes OFF.

Tại sao điều này được đánh dấu màu đỏ trong báo cáo IBAnalyst? Câu trả lời rất đơn giản - sử dụng ghi không đồng bộ có thể gây hỏng cơ sở dữ liệu trong trường hợp mất điện, lỗi hệ điều hành hoặc máy chủ.

Mẹo: Thật thú vị khi các giao diện HDD hiện đại (ATA, SATA, SCSI) không cho thấy sự khác biệt lớn nào về hiệu suất khi Forced Write được đặt On hoặc Off(1).

Tiếp theo trên báo cáo là “khoảng quét” (sweep interval) bí ẩn. Nếu dương, nó đặt kích thước khoảng cách giữa giao dịch cũ nhất (2) và giao dịch snapshot cũ nhất, tại đó engine được cảnh báo về nhu cầu bắt đầu thu gom rác tự động. Trên một số hệ thống, việc chạm ngưỡng này sẽ gây ra hiệu ứng “mất hiệu suất đột ngột”, và do đó đôi khi người ta khuyên nên đặt khoảng quét thành 0 (vô hiệu hóa hoàn toàn việc quét tự động). Ở đây, khoảng quét được đánh dấu màu vàng, vì giá trị của khoảng cách quét là âm, điều này có thể xảy ra trong thống kê InterBase 6.0, Firebird và Yaffil nhưng không phải trong InterBase 7.x. Khi giá trị của khoảng cách quét lớn hơn khoảng quét (nếu khoảng quét không phải là 0), mục báo cáo cho khoảng quét sẽ được đánh dấu màu đỏ với gợi ý phù hợp.

Chúng ta sẽ xem xét 8 hàng tiếp theo như một nhóm, vì tất cả chúng hiển thị các khía cạnh của trạng thái giao dịch của cơ sở dữ liệu:

Giao dịch cũ nhất là giao dịch chưa cam kết (non-committed) cũ nhất. Bất kỳ số giao dịch thấp hơn nào đều là cho các giao dịch đã cam kết và không có phiên bản bản ghi nào có sẵn cho các giao dịch đó. Số giao dịch cao hơn giao dịch cũ nhất là cho các giao dịch có thể ở bất kỳ trạng thái nào. Điều này còn được gọi là “giao dịch thú vị cũ nhất” (oldest interesting transaction), vì nó bị đóng băng khi một giao dịch kết thúc bằng rollback và máy chủ không thể hoàn tác các thay đổi của nó tại thời điểm đó.
Snapshot cũ nhất - giao dịch đang hoạt động (tức là chưa cam kết) cũ nhất tồn tại tại thời điểm bắt đầu của giao dịch hiện là Giao dịch “Thú vị” cũ nhất. Cho biết số giao dịch snapshot thấp nhất quan tâm đến các phiên bản bản ghi.
Hoạt động cũ nhất - giao dịch đang hoạt động cũ nhất hiện tại (3).
Giao dịch tiếp theo - số giao dịch sẽ được gán cho giao dịch mới.
Giao dịch đang hoạt động - IBAnalyst sẽ đưa ra cảnh báo nếu số giao dịch hoạt động cũ nhất thấp hơn 30% so với số lượng giao dịch hàng ngày. Thống kê không cho biết liệu có bất kỳ giao dịch hoạt động nào khác giữa giao dịch hoạt động cũ nhất và giao dịch tiếp theo hay không, nhưng có thể có các giao dịch như vậy. Thông thường, nếu giao dịch hoạt động cũ nhất bị kẹt, có hai nguyên nhân có thể: a) một số giao dịch đang hoạt động trong một thời gian dài hoặc b) thiết kế ứng dụng cho phép các giao dịch chạy trong một thời gian dài. Cả hai nguyên nhân đều ngăn cản việc thu gom rác và tiêu tốn tài nguyên máy chủ.
Giao dịch mỗi ngày - điều này được tính từ giao dịch tiếp theo, chia cho số ngày đã trôi qua kể từ khi tạo cơ sở dữ liệu đến thời điểm thống kê được lấy. Điều này chỉ có thể chính xác cho các cơ sở dữ liệu sản xuất hoặc cho các cơ sở dữ liệu được khôi phục định kỳ từ bản sao lưu, khiến việc đánh số giao dịch được đặt lại.

Như bạn đã học, nếu có bất kỳ cảnh báo nào, chúng được hiển thị dưới dạng các dòng màu, với các gợi ý rõ ràng, mô tả về cách khắc phục hoặc ngăn chặn vấn đề.

Cần lưu ý rằng thống kê cơ sở dữ liệu không phải lúc nào cũng hữu ích. Thống kê được thu thập trong quá trình làm việc và các hoạt động bảo trì có thể vô nghĩa.

Đừng thu thập thống kê nếu bạn:

Vừa khôi phục cơ sở dữ liệu của mình
Thực hiện sao lưu (gbak -b db.gdb) mà không có công tắc -g
Gần đây đã thực hiện quét thủ công (gfix -sweep)

Thống kê bạn nhận được trong những trường hợp như vậy sẽ thực tế là vô dụng. Cũng đúng rằng trong quá trình làm việc bình thường có thể có những lúc cơ sở dữ liệu ở trạng thái hoàn hảo, ví dụ, khi các ứng dụng tạo ra tải cơ sở dữ liệu ít hơn bình thường (người dùng đang ăn trưa hoặc đó là thời điểm yên tĩnh trong ngày kinh doanh).

Làm thế nào bạn có thể biết khi nào có điều gì đó không ổn với cơ sở dữ liệu?

Các ứng dụng của bạn có thể được thiết kế tốt đến mức chúng sẽ luôn hoạt động với các giao dịch và dữ liệu một cách chính xác, không tạo ra khoảng cách quét, không tích lũy nhiều giao dịch đang hoạt động, không giữ các snapshot chạy dài, v.v. Thông thường điều đó không xảy ra (xin lỗi, các đồng nghiệp).

Lý do phổ biến nhất là các nhà phát triển kiểm tra ứng dụng của họ chỉ chạy với hai hoặc ba người dùng đồng thời. Khi ứng dụng sau đó được sử dụng trong môi trường sản xuất với mười lăm người dùng đồng thời trở lên, cơ sở dữ liệu có thể hoạt động không thể đoán trước. Tất nhiên, chế độ đa người dùng có thể hoạt động tốt vì hầu hết các xung đột đa người dùng có thể được kiểm tra với hai hoặc ba ứng dụng chạy đồng thời. Tuy nhiên, với số lượng người dùng lớn hơn, các vấn đề thu gom rác có thể phát sinh. Những vấn đề tiềm ẩn như vậy có thể được phát hiện nếu bạn thu thập thống kê cơ sở dữ liệu vào những thời điểm chính xác.

Thông tin bảng

Hãy xem một đầu ra mẫu khác từ IBAnalyst.

![](/images/article_IBAnalyst (1).jpg)

Hình 2 Thống kê bảng

Chế độ xem thống kê Bảng của IBAnalyst cũng rất hữu ích. Nó có thể hiển thị những bảng nào có nhiều phiên bản bản ghi, nơi có một số lượng lớn các cập nhật/xóa được thực hiện, các bảng bị phân mảnh, với phân mảnh do cập nhật/xóa hoặc do blob, v.v. Bạn có thể thấy những bảng nào đang được cập nhật thường xuyên và kích thước bảng tính bằng megabyte. Hầu hết các cảnh báo này đều có thể tùy chỉnh.

Trong ví dụ cơ sở dữ liệu này có một số vấn đề. Trước hết, màu vàng trong cột VerLen cảnh báo rằng không gian chiếm bởi các phiên bản bản ghi lớn hơn không gian chiếm bởi chính các bản ghi. Điều này có thể là kết quả của việc cập nhật nhiều trường trong một bản ghi hoặc do xóa hàng loạt. Xem các hàng trong đó cột MaxVers được đánh dấu màu xanh lam. Điều này cho thấy chỉ có một phiên bản trên mỗi bản ghi được lưu trữ và do đó vấn đề là do xóa hàng loạt. Giá trị trong cột Versions cho thấy có bao nhiêu bản ghi đã bị xóa.

Các giao dịch hoạt động tồn tại lâu ngăn cản việc thu gom rác là lý do chính gây suy giảm hiệu suất. Đối với một số bảng, có thể có nhiều phiên bản vẫn “đang được sử dụng”. Máy chủ không thể quyết định liệu chúng có thực sự đang được sử dụng hay không, vì các giao dịch đang hoạt động có khả năng cần bất kỳ một hoặc tất cả các phiên bản này. Do đó, máy chủ không coi các phiên bản này là rác và việc xây dựng một bản ghi chính xác từ nhiều phiên bản ngày càng mất nhiều thời gian hơn bất cứ khi nào một giao dịch tình cờ đọc nó. Trong Hình 2, bạn có thể thấy hai bảng có số phiên bản cao gấp ba lần số bản ghi. Sử dụng thông tin này, bạn cũng có thể kiểm tra xem việc các ứng dụng của bạn cập nhật các bảng này thường xuyên như vậy là do thiết kế hay do lỗi.

Chế độ xem chỉ mục

Các chỉ mục được engine cơ sở dữ liệu sử dụng để thực thi các ràng buộc khóa chính, khóa ngoại và duy nhất. Chúng cũng tăng tốc độ truy xuất dữ liệu. Các chỉ mục duy nhất là tốt nhất để truy xuất dữ liệu, nhưng mức độ lợi ích từ các chỉ mục không duy nhất phụ thuộc vào sự đa dạng của dữ liệu được lập chỉ mục.

Ví dụ, hãy nhìn vào ADDR_ADDRESS_IDX6. Trước hết, chính tên chỉ mục gợi ý rằng nó được tạo thủ công. Nếu thống kê được lấy bởi Services API với thông tin siêu dữ liệu, bạn có thể thấy những cột nào được lập chỉ mục (trong IBAnalyst 1.83 trở lên). Đối với chỉ mục đang xem xét, bạn có thể thấy nó có 34999 khóa, TotalDup là 34995 và MaxDup là 25056. Cả hai cột trùng lặp đều được đánh dấu màu đỏ. Điều này là do chỉ có 4 giá trị khóa duy nhất trong tất cả các khóa của chỉ mục này, như có thể thấy từ cột Uniques. Hơn nữa, chuỗi trùng lặp lớn nhất (khóa trỏ đến các bản ghi có cùng giá trị cột) là 25056 - tức là gần như tất cả các khóa lưu trữ một trong bốn giá trị duy nhất. Kết quả là, chỉ mục này có thể:

Giảm tốc độ của quá trình khôi phục. Được rồi, ba mươi lăm nghìn khóa không phải là vấn đề lớn đối với cơ sở dữ liệu và phần cứng hiện đại, nhưng tác động vẫn nên được ghi nhận.
Làm chậm quá trình dọn dẹp rác. Các chỉ mục có số lượng giá trị duy nhất thấp có thể cản trở quá trình dọn dẹp rác lên đến mười lần so với một chỉ mục hoàn toàn duy nhất. Vấn đề này đã được giải quyết trong InterBase 7.1/7.5 và Firebird 2.0.
Tạo ra các lần đọc trang không cần thiết khi trình tối ưu hóa đọc chỉ mục. Điều này phụ thuộc vào giá trị đang được tìm kiếm trong một truy vấn cụ thể - tìm kiếm bằng một chỉ mục có giá trị MaxDup lớn hơn sẽ chậm hơn. Tìm kiếm theo giá trị trên một cột có ít giá trị trùng lặp hơn sẽ nhanh hơn, nhưng chỉ bạn biết rằng cột đó được lập chỉ mục.

Đó là lý do tại sao IBAnalyst thu hút sự chú ý của bạn đến các chỉ mục như vậy, đánh dấu chúng bằng màu đỏ và vàng, và đưa chúng vào báo cáo Recommendations. Thật không may, hầu hết các chỉ mục “xấu” được tạo tự động để thực thi các ràng buộc khóa ngoại. Trong một số trường hợp, vấn đề này có thể được giải quyết bằng cách ngăn chặn, sử dụng trigger, các thao tác xóa hoặc cập nhật khóa chính trong các bảng tra cứu. Nhưng nếu không thể thực hiện các thay đổi như vậy, IBAnalyst sẽ hiển thị cho bạn các chỉ mục “xấu” trên Khóa ngoại mỗi khi bạn xem thống kê.

Báo cáo

Không cần phải xem toàn bộ báo cáo mỗi lần, phát hiện màu ô và đọc gợi ý cho các cảnh báo mới. Thông tin trực tiếp và chi tiết hơn có thể được lấy bằng cách sử dụng tính năng Recommendations của IBAnalyst. Chỉ cần tải thống kê và đi đến menu Reports/View Recommendations. Báo cáo này cung cấp phân tích từng bước, bao gồm các cảnh báo mô tả chi tiết hơn về forced writes, sweep interval, hoạt động cơ sở dữ liệu, trạng thái giao dịch, kích thước trang cơ sở dữ liệu, sweeping, các trang kiểm kê giao dịch, các bảng phân mảnh, các bảng có nhiều phiên bản bản ghi, các thao tác xóa/cập nhật hàng loạt, các chỉ mục sâu, các chỉ mục không thân thiện với trình tối ưu hóa, các chỉ mục vô dụng và thậm chí cả các bảng trống. Tất cả thông tin này và các đề xuất đi kèm được tạo động dựa trên thống kê đang được tải.

Như một ví dụ về đầu ra của báo cáo, hãy xem một báo cáo được tạo cho thống kê cơ sở dữ liệu mà bạn đã thấy trước đó trong bài viết này:

“Tổng kích thước của các trang kiểm kê giao dịch (TIP) là lớn - 94 kilobyte hoặc 23 trang. Giao dịch Read_committed sử dụng TIP toàn cục, nhưng các giao dịch snapshot tạo bản sao TIP riêng trong bộ nhớ. Kích thước TIP lớn có thể làm chậm hiệu suất. Hãy thử chạy sweep thủ công (gfix -sweep) để giảm kích thước TIP.”

Đây là một trích dẫn khác từ phần bảng/chỉ mục của báo cáo:

“Số lượng bảng có phiên bản: 8. Số lượng lớn phiên bản bản ghi thường làm chậm hiệu suất. Nếu có nhiều phiên bản bản ghi trong bảng, thì quá trình dọn dẹp rác không hoạt động, hoặc các bản ghi không được đọc bởi bất kỳ câu lệnh select nào. Bạn có thể thử select count(*) trên các bảng đó để thực thi quá trình dọn dẹp rác, nhưng điều này có thể mất nhiều thời gian (nếu có nhiều phiên bản và các chỉ mục không duy nhất tồn tại) và có thể không thành công nếu có ít nhất một giao dịch quan tâm đến các phiên bản này.

Đây là danh sách các bảng có tỷ lệ phiên bản/bản ghi lớn hơn 3:


Bảng	Bản ghi	Phiên bản	Kích thước Bản ghi/Phiên bản
CLIENTS_PR	3388	10944	92%
DICT_PRICE	30	1992	45%
DOCS	9	2225	64%
N_PART	13835	72594	83%
REGISTR_NC	241	4085	56%
SKL_NC	1640	7736	170%
STAT_QUICK	17649	85062	110%
UO_LOCK	283	8490	144%

Tóm tắt

IBAnalyst là một công cụ vô giá hỗ trợ người dùng thực hiện phân tích chi tiết thống kê cơ sở dữ liệu Firebird hoặc InterBase và xác định các vấn đề tiềm ẩn với cơ sở dữ liệu về hiệu suất, bảo trì và cách ứng dụng tương tác với cơ sở dữ liệu. Nó tiếp nhận các thống kê cơ sở dữ liệu khó hiểu và hiển thị chúng một cách trực quan, dễ hiểu và sẽ tự động đưa ra các đề xuất hợp lý về việc cải thiện hiệu suất cơ sở dữ liệu và giảm nhẹ việc bảo trì cơ sở dữ liệu.

1 InterBase 7.5 và Firebird 1.5 có các tính năng đặc biệt có thể định kỳ xóa các trang chưa lưu nếu Forced Writes được Tắt.

2 Giao dịch cũ nhất cũng là giao dịch quan tâm cũ nhất, được đề cập ở mọi nơi. Đầu ra của gstat không hiển thị giao dịch này là “quan tâm”.

3 Ann Harrison nói rằng Oldest active là giao dịch cũ nhất đang hoạt động khi giao dịch đang hoạt động cũ nhất hiện tại bắt đầu. Đối với các ứng dụng, đây không phải là sự khác biệt lớn ở đây.