Tối Ưu Hóa Cơ Sở Dữ Liệu: Kỹ Thuật Tuning Hiệu Suất Chuyên Gia

Giới Thiệu

Việc tuning hiệu suất trên cơ sở dữ liệu của bạn là một trong những hoạt động phổ biến nhất và quan trọng nhất mà mọi database administrator (DBA) phải thực hiện trên cơ sở định kỳ và liên tục. Các chuyên gia khuyên bạn nên tinh chỉnh thường xuyên cơ sở dữ liệu để tối ưu hóa hiệu suất. Tinh chỉnh giúp người dùng chạy báo cáo và truy vấn nhanh hơn và có kết quả nhanh hơn. Bài viết này chia sẻ một số kỹ thuật troubleshooting và tuning cho cơ sở dữ liệu Oracle®.

Các Bước Tuning

Để bắt đầu, bạn cần xác định khu vực nơi vấn đề đang xảy ra. Các khu vực có thể kích hoạt vấn đề bao gồm hệ điều hành, cơ sở dữ liệu, thiếu hụt bộ nhớ, v.v. Sau khi bạn đã xác định khu vực vấn đề, bạn có thể tiến hành tuning khu vực đó để có lợi ích tối đa. Blog này tập trung chỉ vào tuning hiệu suất.

Oracle đã cung cấp cho DBA một số công cụ để giúp chẩn đoán vấn đề và troubleshooting vấn đề hiệu suất. Các công cụ này bao gồm Automatic Database Diagnostic Monitor (ADDM) và báo cáo Automatic Workload Repository (AWR), bạn có thể tạo cho khoảng thời gian gặp vấn đề và sử dụng trong phân tích của bạn. Bạn nên sử dụng các công cụ để tìm kiếm các thành phần skewed hoặc có thể tuning.

Top Timed Events Trong Báo Cáo AWR

AWR là công cụ bạn có thể tạo báo cáo tóm tắt việc sử dụng bộ nhớ dưới dạng thống kê bởi một instance cụ thể. Để tạo báo cáo AWR, chạy file sau trong dấu nhắc sqlplus:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

Điều này tạo báo cáo AWR ở định dạng HTML bằng cách sử dụng template pre-stored. Sau khi bạn đã tạo báo cáo AWR, bạn có thể nhanh chóng thấy phần Top Timed Events trong báo cáo AWR, xác định khu vực gặp vấn đề, như hiển thị trong hình ảnh sau:

Phiên Tuning ADDM

Bình thường, khi vấn đề được báo cáo, DBA có thể troubleshoot chỉ khu vực đó hoặc giải quyết vấn đề cụ thể đó. Tuy nhiên, nếu vấn đề không được xác định và giải quyết đúng cách, nó có thể dẫn đến vấn đề lớn hơn trong tương lai. DBA có thể bỏ lỡ bức tranh lớn hơn ở đây. Để giúp DBA có bức tranh lớn hơn và hoàn chỉnh về các vấn đề cơ sở dữ liệu, Oracle đã cung cấp công cụ khác cho DBA, ADDM. Phiên tuning ADDM tuân thủ quy trình tương tự như phiên tuning thủ công. Hình ảnh sau cho thấy sự so sánh:

Nguồn hình ảnh: Oracle 11G Performance Tuning training manual

Truy xuất báo cáo ADDM bằng cách sử dụng SQL hoặc thông qua Oracle Enterprise Manager (OEM). Hình ảnh sau cho thấy SQL mẫu:

SQL Xấu Và Kế Hoạch Thực Thi

Sau khi báo cáo AWR hoặc ADDM xác định các phần SQL/SQLID xấu, bạn có thể sử dụng DBMS_XPLAN để thu thập thêm thông tin về chúng. Gói DBMS_XPLAN cung cấp các hàm bảng sau mà bạn có thể sử dụng để truy xuất và hiển thị kế hoạch thực thi (exec):

DISPLAY
DISPLAY_AWR
select plan_table_output from table (DBMS_XPLAN.DISPLAY_AWR('fs22b3fgfh8xc'));
DISPLAY_CURSOR

Hình ảnh sau cho thấy đầu ra mẫu từ lệnh EXPLAIN PLAN và xác định xem truy vấn có đang sử dụng full table scan hay thu hẹp dữ liệu bằng cách sử dụng một số indexes:

Báo cáo ADDM phản ánh xem bạn có thể đạt được lợi ích hiệu suất nào bằng cách tạo một số indexes mới. Bạn cũng có thể chạy SQL Tuning Advisor từ OEM để tinh chỉnh truy vấn và có thể sử dụng kế hoạch thực thi tốt hơn. Hầu hết thời gian, sử dụng kế hoạch tốt hơn cho SQL gây vấn đề giải quyết vấn đề hiệu suất chính.

SQL Tuning Advisor

SQL Tuning Advisor giúp phân tích các câu lệnh SQL và lấy khuyến nghị hiệu suất bằng cách sử dụng SQL authorization ID (SQLID) để phân tích và đề xuất giải pháp. SQL Tuning Advisor phân tích các nguồn sau:

• Top activity: Phân tích các câu lệnh SQL hàng đầu hiện đang hoạt động.
• SQL tuning sets: Phân tích một tập hợp các câu lệnh SQL bạn cung cấp.
• Historical SQL (AWR): Phân tích các câu lệnh SQL từ các câu lệnh được thu thập bởi AWR snapshots.

Các screenshot sau cho thấy một số ví dụ:

Yêu Cầu Chạy Dài

Trong cơ sở dữ liệu Oracle eBusiness Suite, vấn đề long running requests chủ yếu nảy sinh ở nơi một vài concurrent requests tiếp tục chạy. Để giải quyết điều này, bạn cần thu thập thêm thông tin về phiên cơ sở dữ liệu liên quan đến concurrent requests. Hình ảnh sau cho thấy các bước thu thập thông tin này:

Tham Số Bộ Nhớ Cơ Sở Dữ Liệu Oracle

Sau khi xem xét báo cáo AWR, DBA có thể dễ dàng xác định cache cần tinh chỉnh vì cache hit ratio thấp hơn cho cache đó so với các cache khác. Hình ảnh sau cho thấy một vài tham số database hàng đầu bạn phải xem xét cho tuning bộ nhớ instance-wide.

Nguồn hình ảnh: https://ora-performance-tuning.blogspot.com/2014/02/automatic-shared-memory-management.html

Các Wait Event Thường Gặp

Bảng sau cho thấy một số wait event thường gặp và nguyên nhân có thể:

(Nguồn bảng: Oracle Performance Tuning 11G OCP training manual, chapter 20, page 24)

Top 10 Vấn Đề Tuning

Khách hàng thường gặp phải 10 vấn đề tuning hàng đầu sau:

• Quản lý kết nối xấu: Developer viết code kết nối đến cơ sở dữ liệu trong ứng dụng hoặc chạy truy vấn lấy dữ liệu từ cơ sở dữ liệu. Sau khi dữ liệu đã được lấy và không cần thêm gì nữa, code nên đóng kết nối đến cơ sở dữ liệu. Tuy nhiên, điều này thường không xảy ra, dẫn đến số lượng session không hoạt động tăng lên trong cơ sở dữ liệu. Các session này sử dụng tài nguyên quý giá có thể đã được dùng cho các kết nối hoạt động khác.
• Sử dụng xấu con trỏ và shared pool: Con trỏ là công cụ trong kho tàng của Developer phần lớn chưa được sử dụng. Không có con trỏ, Oracle phải hard parse code mỗi khi nó chạy. Điều này ảnh hưởng lớn đến hiệu suất của các truy vấn SQL chạy lặp lại. DBA có thể xác định vấn đề này bằng cách nhìn vào phần Instance Efficiency Percentages - Execute to Parse %. của báo cáo AWR.
• SQL xấu: Cách một truy vấn SQL được viết (với điều kiện join để lấy dữ liệu) ảnh hưởng đáng kể đến hiệu suất của SQL đó sau khi nó được thực thi. Bạn nên tránh full table scans cho các bảng lớn. Sau khi SQL đã được chuẩn bị, developer và DBA nên chạy explain plan trên SQL đó để hiểu chi phí chạy nó trong cơ sở dữ liệu. Bằng cách sử dụng con trỏ, bind variable, và indexes, bạn có thể tăng hiệu quả.
• Sử dụng tham số khởi tạo không tiêu chuẩn: DBA nên luôn chỉ sử dụng tham số khởi tạo tiêu chuẩn hoặc được khuyến nghị. Chỉ sử dụng tham số khởi tạo không tiêu chuẩn khi chúng được đề xuất bởi Oracle Service Request (SR).
• Sai I/O cơ sở dữ liệu: Khi DBA chọn phần cứng cơ sở dữ liệu nên cố gắng phân phối cơ sở dữ liệu trên nhiều đĩa và liên quan đến team mạng trong thảo luận về tốc độ dữ liệu sẽ di chuyển đến người dùng cuối từ database server và ngược lại. DBA nên cân nhắc tốc độ switch và router mạng để tránh bottleneck hoặc vấn đề hiệu suất.
• Vấn đề thiết lập redo log: Redo logs cần thiết để lưu trữ dữ liệu từ redo buffer để Oracle có thể redo giao dịch trong trường hợp bất kỳ sự cố nào. Nếu kích thước redo log không đủ, thì nhiều switch có thể xảy ra trong cơ sở dữ liệu gây ra vấn đề hiệu suất. Điều này cũng tăng tải trên archive generation.
• Serialization của block dữ liệu trong buffer cache: Điều này xảy ra do thiếu hụt free list groups hoặc undo segments. Tình huống này nảy sinh cho cơ sở dữ liệu insert-heavy với cơ sở người dùng hoạt động lớn nhưng ít undo segments, cuối cùng dẫn đến vấn đề hiệu suất.
• Full table scans: Kiểm tra full table scans trong truy vấn bằng cách chạy explain plan. Thông thường, truy vấn làm full table scans phản ánh thiết kế SQL xấu, có thể sửa chữa bằng cách sử dụng indexes và thu hẹp dữ liệu cần thiết. Trong một vài trường hợp, full table scans có thể có lợi, đặc biệt trong trường hợp các bảng nhỏ.
• Recursive SQL: Recursive SQL có thể là phước cho developer nếu sử dụng đúng, nhưng nó có thể là con dao hai lưỡi. Làm đúng, nó cung cấp đầu ra hiệu quả. Nếu không, nó ảnh hưởng lớn đến hiệu suất cơ sở dữ liệu.
• Sắp xếp trên đĩa: In-disk sorting là nhiệm vụ rất đắt cho cơ sở dữ liệu. Nó chỉ ra thiết kế SQL kém và tối ưu hóa xấu. Bạn có thể xác định vấn đề trong phần Instance Activity Stats – Sorts (disk) của báo cáo AWR.

Kết Luận

Mặc dù DBA phải xem xét các khu vực khác nhau cho tuning hiệu suất, trong thế giới cơ sở dữ liệu, tuning hiệu suất bắt đầu từ giai đoạn thiết kế cơ sở dữ liệu và ứng dụng. Cơ sở dữ liệu và ứng dụng được thiết kế với quan điểm tuning hiệu suất trong tâm trí có khả năng mở rộng tốt hơn nhiều so với ứng dụng được thiết kế không cân nhắc tuning hiệu suất.

Các điểm tuning hiệu suất được bao gồm trong blog này chỉ là đỉnh của núi băng. Tôi khuyên các DBA đồng nghiệp tiếp tục đọc thêm về chủ đề này để chăm sóc hiệu suất cơ sở dữ liệu tổng thể.

Sử dụng tab Phản hồi để đưa ra bất kỳ nhận xét hoặc đặt câu hỏi nào.

Tìm hiểu thêm về các dịch vụ cơ sở dữ liệu của chúng tôi.

Rackspace có kiến thức rộng về sản phẩm Oracle. Tìm hiểu thêm về cách chúng tôi có thể tối đa hóa đầu tư Oracle của bạn.