Làm cách nào để tăng gấp 10 lần số lượng truy vấn vào cơ sở dữ liệu mà không cần chuyển sang máy chủ hiệu quả hơn và duy trì chức năng hệ thống? Tôi sẽ cho bạn biết cách chúng tôi giải quyết sự suy giảm hiệu suất của cơ sở dữ liệu, cách chúng tôi tối ưu hóa các truy vấn SQL để phục vụ nhiều người dùng nhất có thể và không làm tăng chi phí tài nguyên máy tính.
Tôi làm dịch vụ quản lý quy trình kinh doanh trong các công ty xây dựng. Khoảng 3 nghìn công ty làm việc với chúng tôi. Hơn 10 nghìn người làm việc với hệ thống của chúng tôi mỗi ngày trong 4-10 giờ. Nó giải quyết nhiều vấn đề khác nhau về lập kế hoạch, thông báo, cảnh báo, xác thực... Chúng tôi sử dụng PostgreSQL 9.6. Chúng tôi có khoảng 300 bảng trong cơ sở dữ liệu và nhận được tới 200 triệu truy vấn (10 nghìn bảng khác nhau) mỗi ngày. Trung bình chúng tôi có 3-4 nghìn yêu cầu mỗi giây, vào những thời điểm tích cực nhất là hơn 10 nghìn yêu cầu mỗi giây. Hầu hết các truy vấn là OLAP. Có ít bổ sung, sửa đổi và xóa hơn nhiều, nghĩa là tải OLTP tương đối nhẹ. Tôi đã cung cấp tất cả những con số này để bạn có thể đánh giá quy mô dự án của chúng tôi và hiểu trải nghiệm của chúng tôi có thể hữu ích như thế nào đối với bạn.
Hình một. trữ tình
Khi bắt đầu phát triển, chúng tôi không thực sự nghĩ đến loại tải nào sẽ xảy ra với cơ sở dữ liệu và chúng tôi sẽ làm gì nếu máy chủ ngừng hoạt động. Khi thiết kế cơ sở dữ liệu, chúng tôi đã làm theo các khuyến nghị chung và cố gắng không tự bắn vào chân mình mà còn vượt xa những lời khuyên chung chung như “đừng sử dụng mẫu chúng tôi đã không đi vào. Chúng tôi thiết kế dựa trên nguyên tắc chuẩn hóa, tránh dư thừa dữ liệu và không quan tâm đến việc tăng tốc một số truy vấn nhất định. Ngay khi những người dùng đầu tiên đến, chúng tôi đã gặp phải vấn đề về hiệu suất. Như thường lệ, chúng tôi hoàn toàn không chuẩn bị cho việc này. Những vấn đề đầu tiên hóa ra lại đơn giản. Theo quy định, mọi thứ đã được giải quyết bằng cách thêm một chỉ mục mới. Nhưng đã đến lúc các bản vá lỗi đơn giản ngừng hoạt động. Nhận thấy rằng chúng tôi thiếu kinh nghiệm và ngày càng khó hiểu nguyên nhân gây ra sự cố, chúng tôi đã thuê các chuyên gia giúp chúng tôi thiết lập máy chủ chính xác, giám sát kết nối và chỉ cho chúng tôi nơi cần tìm .
Hình hai. Thống kê
Vì vậy, chúng tôi có khoảng 10 nghìn truy vấn khác nhau được thực hiện trên cơ sở dữ liệu của mình mỗi ngày. Trong số 10 nghìn truy vấn này, có những quái vật được thực thi 2-3 triệu lần với thời gian thực hiện trung bình là 0.1-0.3 ms và có những truy vấn có thời gian thực hiện trung bình là 30 giây được gọi 100 lần một ngày.
Không thể tối ưu hóa tất cả 10 nghìn truy vấn, vì vậy chúng tôi quyết định tìm ra nơi cần hướng nỗ lực của mình để cải thiện hiệu suất của cơ sở dữ liệu một cách chính xác. Sau vài lần lặp lại, chúng tôi bắt đầu chia yêu cầu thành các loại.
Yêu cầu HÀNG ĐẦU
Đây là những truy vấn nặng nhất và tốn nhiều thời gian nhất (tổng thời gian). Đây là những truy vấn được gọi rất thường xuyên hoặc các truy vấn mất rất nhiều thời gian để thực thi (các truy vấn dài và thường xuyên đã được tối ưu hóa trong những lần lặp đầu tiên của cuộc chiến về tốc độ). Kết quả là máy chủ dành nhiều thời gian nhất cho việc thực thi chúng. Hơn nữa, điều quan trọng là phải phân tách các yêu cầu hàng đầu theo tổng thời gian thực hiện và riêng biệt theo thời gian IO. Các phương pháp tối ưu hóa các truy vấn như vậy hơi khác nhau.
Thông lệ thông thường của tất cả các công ty là làm việc với các yêu cầu TOP. Có rất ít trong số đó; tối ưu hóa ngay cả một truy vấn có thể giải phóng 5-10% tài nguyên. Tuy nhiên, khi dự án hoàn thiện, việc tối ưu hóa các truy vấn TOP trở thành một nhiệm vụ ngày càng không hề đơn giản. Tất cả các phương pháp đơn giản đều đã được xử lý và yêu cầu “nặng” nhất “chỉ” chiếm 3-5% tài nguyên. Nếu tổng số truy vấn TOP chiếm ít hơn 30-40% thời gian thì rất có thể bạn đã nỗ lực để chúng hoạt động nhanh chóng và đã đến lúc chuyển sang tối ưu hóa các truy vấn từ nhóm tiếp theo.
Vẫn còn phải trả lời câu hỏi có bao nhiêu truy vấn hàng đầu nên được đưa vào nhóm này. Tôi thường lấy ít nhất 10, nhưng không quá 20. Tôi cố gắng đảm bảo rằng thời gian của lần đầu tiên và lần cuối cùng trong nhóm TOP chênh lệch không quá 10 lần. Tức là nếu thời gian thực hiện truy vấn giảm mạnh từ vị trí thứ 1 xuống vị trí thứ 10 thì tôi lấy TOP-10, nếu giảm dần hơn thì tôi tăng quy mô nhóm lên 15 hoặc 20.
trung nông
Đây là tất cả các yêu cầu đến ngay sau TOP, ngoại trừ 5-10% cuối cùng. Thông thường, việc tối ưu hóa các truy vấn này có cơ hội tăng hiệu suất máy chủ lên đáng kể. Những yêu cầu này có thể nặng tới 80%. Nhưng ngay cả khi thị phần của họ đã vượt quá 50% thì cũng đã đến lúc bạn phải xem xét chúng kỹ hơn.
Đuôi
Như đã đề cập, những truy vấn này xuất hiện ở cuối và chiếm 5-10% thời gian. Bạn chỉ có thể quên chúng nếu không sử dụng các công cụ phân tích truy vấn tự động, khi đó việc tối ưu hóa chúng cũng có thể không tốn kém.
Đánh giá từng nhóm như thế nào?
Tôi sử dụng truy vấn SQL giúp đưa ra đánh giá như vậy cho PostgreSQL (Tôi chắc chắn rằng một truy vấn tương tự có thể được viết cho nhiều DBMS khác)
Truy vấn SQL để ước tính kích thước của nhóm TOP-MEDIUM-TAIL
SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) AS sum_tail
FROM
(
SELECT CASE WHEN rn <= 20 THEN tt_percent ELSE 0 END AS time_top,
CASE WHEN rn > 20 AND rn <= 800 THEN tt_percent ELSE 0 END AS time_medium,
CASE WHEN rn > 800 THEN tt_percent ELSE 0 END AS time_tail
FROM (
SELECT total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query,
ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn
FROM pg_stat_statements
ORDER BY total_time DESC
) AS t
)
AS ts
Kết quả của truy vấn là ba cột, mỗi cột chứa phần trăm thời gian cần thiết để xử lý các truy vấn từ nhóm này. Bên trong yêu cầu có hai số (trong trường hợp của tôi là 20 và 800) để phân tách các yêu cầu từ nhóm này với nhóm khác.
Đây là cách so sánh đại khái tỷ lệ chia sẻ yêu cầu tại thời điểm công việc tối ưu hóa bắt đầu và hiện tại.
Biểu đồ cho thấy tỷ lệ yêu cầu TOP đã giảm mạnh, nhưng “trung nông” lại tăng lên.
Lúc đầu, các yêu cầu TOP bao gồm những sai lầm trắng trợn. Theo thời gian, những căn bệnh thời thơ ấu biến mất, tỷ lệ yêu cầu TOP giảm dần và ngày càng phải nỗ lực nhiều hơn để tăng tốc những yêu cầu khó.
Để có được văn bản của yêu cầu, chúng tôi sử dụng yêu cầu sau
SELECT * FROM (
SELECT ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn, total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query
FROM pg_stat_statements
ORDER BY total_time DESC
) AS T
WHERE
rn <= 20 -- TOP
-- rn > 20 AND rn <= 800 -- MEDIUM
-- rn > 800 -- TAIL
Dưới đây là danh sách các kỹ thuật được sử dụng phổ biến nhất đã giúp chúng tôi tăng tốc các truy vấn TOP:
- Ví dụ: thiết kế lại hệ thống, xử lý lại logic thông báo bằng cách sử dụng trình trung chuyển tin nhắn thay vì truy vấn định kỳ tới cơ sở dữ liệu
- Thêm hoặc thay đổi chỉ mục
- Viết lại các truy vấn ORM thành SQL thuần túy
- Viết lại logic tải dữ liệu lười biếng
- Bộ nhớ đệm thông qua việc không chuẩn hóa dữ liệu. Ví dụ: chúng ta có kết nối bảng Giao hàng -> Hóa đơn -> Yêu cầu -> Ứng dụng. Nghĩa là, mỗi lần phân phối được liên kết với một ứng dụng thông qua các bảng khác. Để không liên kết tất cả các bảng trong mỗi yêu cầu, chúng tôi nhân đôi liên kết đến yêu cầu trong bảng Phân phối.
- Lưu các bảng tĩnh bằng sách tham khảo và hiếm khi thay đổi bảng trong bộ nhớ chương trình.
Đôi khi những thay đổi tương đương với một thiết kế lại ấn tượng, nhưng chúng cung cấp 5-10% tải hệ thống và hợp lý. Theo thời gian, ống xả ngày càng nhỏ hơn và yêu cầu thiết kế lại ngày càng nghiêm túc hơn.
Sau đó, chúng tôi chuyển sự chú ý sang nhóm yêu cầu thứ hai - nhóm trung nông. Còn rất nhiều truy vấn trong đó và có vẻ như sẽ mất rất nhiều thời gian để phân tích toàn bộ nhóm. Tuy nhiên, hầu hết các truy vấn hóa ra lại rất đơn giản để tối ưu hóa và nhiều vấn đề được lặp lại hàng chục lần với nhiều biến thể khác nhau. Dưới đây là ví dụ về một số cách tối ưu hóa điển hình mà chúng tôi đã áp dụng cho hàng chục truy vấn tương tự và mỗi nhóm truy vấn được tối ưu hóa đã giảm tải cơ sở dữ liệu từ 3-5%.
- Thay vì kiểm tra sự hiện diện của các bản ghi bằng COUNT và quét toàn bộ bảng, EXISTS bắt đầu được sử dụng
- Loại bỏ DISTINCT (không có công thức chung, nhưng đôi khi bạn có thể dễ dàng loại bỏ nó bằng cách tăng tốc yêu cầu lên 10 - 100 lần).
Ví dụ: thay vì truy vấn để chọn tất cả trình điều khiển từ một bảng phân phối lớn (DELIVERY)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.IDđã thực hiện một truy vấn trên một bảng tương đối nhỏ PERSON
SELECT P.ID, P.FIRST_NAME, P.LAST_NAME FROM PERSON WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)Có vẻ như chúng tôi đã sử dụng truy vấn con tương quan nhưng nó giúp tăng tốc hơn 10 lần.
- Trong nhiều trường hợp, COUNT bị bỏ hoàn toàn và
- thay vì
UPPER(s) LIKE JOHN%’sử dụng
s ILIKE “John%”
Mỗi yêu cầu cụ thể đôi khi được tăng tốc lên 3-1000 lần. Mặc dù có hiệu suất ấn tượng, nhưng lúc đầu, chúng tôi thấy rằng chẳng ích gì khi tối ưu hóa một truy vấn mất 10 mili giây để hoàn thành, là một trong 3 truy vấn nặng nhất và chiếm một phần trăm của một phần trăm thời gian tải cơ sở dữ liệu tổng thể. Nhưng bằng cách áp dụng cùng một công thức cho một nhóm truy vấn cùng loại, chúng tôi đã thu lại được một vài phần trăm. Để không lãng phí thời gian xem xét thủ công tất cả hàng trăm truy vấn, chúng tôi đã viết một số tập lệnh đơn giản sử dụng biểu thức chính quy để tìm các truy vấn cùng loại. Kết quả là, việc tự động tìm kiếm các nhóm truy vấn đã cho phép chúng tôi cải thiện hơn nữa hiệu suất của mình với nỗ lực khiêm tốn.
Kết quả là chúng tôi đã làm việc trên cùng một phần cứng được ba năm rồi. Tải trung bình hàng ngày là khoảng 30%, cao điểm đạt tới 70%. Số lượng yêu cầu cũng như số lượng người dùng đã tăng khoảng 10 lần. Và tất cả điều này là nhờ vào việc giám sát liên tục các nhóm yêu cầu TOP-MEDIUM này. Ngay khi một yêu cầu mới xuất hiện trong nhóm TOP, chúng tôi ngay lập tức phân tích nó và cố gắng tăng tốc. Chúng tôi xem xét nhóm TRUNG BÌNH mỗi tuần một lần bằng cách sử dụng các tập lệnh phân tích truy vấn. Nếu chúng tôi gặp các truy vấn mới mà chúng tôi đã biết cách tối ưu hóa, chúng tôi sẽ nhanh chóng thay đổi chúng. Đôi khi chúng tôi tìm thấy các phương pháp tối ưu hóa mới có thể áp dụng cho nhiều truy vấn cùng một lúc.
Theo dự báo của chúng tôi, máy chủ hiện tại sẽ chịu được sự gia tăng số lượng người dùng thêm 3-5 lần nữa. Đúng vậy, chúng tôi có thêm một con át chủ bài nữa - chúng tôi vẫn chưa chuyển các truy vấn CHỌN sang máy nhân bản, như được khuyến nghị. Nhưng chúng tôi không làm điều này một cách có ý thức, bởi vì trước tiên chúng tôi muốn sử dụng hết khả năng tối ưu hóa “thông minh” trước khi bật “pháo hạng nặng”.
Một cái nhìn quan trọng về công việc đã thực hiện có thể gợi ý việc sử dụng tỷ lệ dọc. Mua một máy chủ mạnh hơn thay vì lãng phí thời gian của các chuyên gia. Máy chủ có thể không tốn nhiều tiền như vậy, đặc biệt là khi chúng tôi chưa sử dụng hết giới hạn của việc mở rộng quy mô theo chiều dọc. Tuy nhiên, chỉ có số lượng yêu cầu tăng gấp 10 lần. Trong vài năm, chức năng của hệ thống đã tăng lên và hiện có nhiều loại yêu cầu hơn. Nhờ bộ nhớ đệm, chức năng tồn tại được thực hiện với ít yêu cầu hơn và yêu cầu hiệu quả hơn. Điều này có nghĩa là bạn có thể nhân thêm 5 một cách an toàn để có được hệ số gia tốc thực. Vì vậy, theo những ước tính thận trọng nhất, chúng ta có thể nói rằng gia tốc là từ 50 lần trở lên. Việc xoay máy chủ theo chiều dọc sẽ tốn kém hơn 50 lần. Đặc biệt là khi việc tối ưu hóa được thực hiện thì nó sẽ hoạt động mọi lúc và hóa đơn thuê máy chủ sẽ đến hàng tháng.
Nguồn: www.habr.com