Kiểm soát sự phát triển của cơ sở dữ liệu:Các chiến lược để giữ kích thước bảng nhỏ và tránh phồng dữ liệu

Hầu hết các ứng dụng web đều sử dụng một loại kho dữ liệu nào đó, thường là cơ sở dữ liệu quan hệ. Khi một ứng dụng web thành công, việc bắt đầu "tích trữ" dữ liệu trong cơ sở dữ liệu có thể trở nên quá dễ dàng. Nhưng việc tích trữ dữ liệu dẫn đến sự tăng trưởng không giới hạn của các bảng cơ sở dữ liệu (cả số lượng hàng và kích thước dữ liệu được lưu trữ).

Mặc dù cách này hoạt động tốt ở một mức độ nhất định, nhưng nó rất hữu ích để ngăn chặn một số dữ liệu phình to — hoặc, nếu bạn không thể ngăn chặn điều đó, hãy lập kế hoạch trước cho cơ sở hạ tầng của mình để quản lý sự tăng trưởng một cách thỏa đáng.

Trước khi đi sâu vào, hãy xem chúng ta có thể xử lý các ứng dụng cồng kềnh như thế nào.

Thêm dữ liệu không phải lúc nào cũng tốt

Hầu hết các ứng dụng chúng tôi làm việc đều có xu hướng lớn hơn theo thời gian.

Nếu bạn sử dụng nhà cung cấp đám mây cho cơ sở dữ liệu của mình, bạn có thể đạt đến giới hạn bộ nhớ được phân bổ. Khi điều đó xảy ra, bạn sẽ cần nâng cấp lên loại phiên bản khác. Cơ sở dữ liệu Heroku PostgreSQL có giới hạn, ví dụ:phiên bản cấp "sở thích" bị giới hạn ở 1GB dữ liệu.

Việc có nhiều dữ liệu hơn cũng có ý nghĩa đối với tốc độ truy vấn. Những gì từng có thể thực hiện được nếu không có chỉ mục sẽ trở nên không thể thực hiện được với các bảng lớn hơn. Một số lần quét phạm vi hàng sẽ chậm hơn. Sẽ có thêm nhiều khóa để thực hiện cơ sở dữ liệu UPDATE và DELETE hoạt động.

Bảng cơ sở dữ liệu phát triển như thế nào

Việc tích trữ dữ liệu diễn ra dần dần. Những gì không phải là vấn đề hôm nay có thể dễ dàng trở thành vấn đề trong một tháng hoặc sáu tháng. Điều nguy hiểm nhất về việc tích trữ dữ liệu là nó rất dễ bị bỏ sót. Hãy xem xét một số tình huống rất cổ điển:

• Vì lý do tuân thủ, bạn sử dụng đá quý như paper_trail và nhận được audit_log_entries cái bàn. Mọi thao tác có chế độ quan trọng trong ứng dụng của bạn sẽ tạo một hàng trong audit_log_entries cái bàn. Những mục nhật ký kiểm tra đó không bao giờ được lưu trữ.
• Bạn chấp nhận tải lên và đang sử dụng ActiveStorage. Bạn không bao giờ xóa các video tải lên, vì vậy activestorage_blobs của bạn cái bàn ngày càng lớn hơn.
• Bạn chạy một CMS dùng chung để xuất bản trên Web và bạn cho phép lưu trữ các phân đoạn bài viết trong cơ sở dữ liệu của mình. Nền tảng của bạn trở nên thành công nhưng hầu hết các tác giả của bạn đều viết những bài báo có kích thước như một cuốn sách. pages bảng trở nên rất lớn mặc dù nó chỉ chứa vài nghìn trang.
• Bạn cho phép nội dung do người dùng tải lên nhưng bạn không xóa dữ liệu vì lý do tuân thủ và thay vào đó, hãy sử dụng nội dung như paranoia để xóa thông qua một lá cờ. user_items của bạn bảng phát triển vô hạn và vượt qua 10 triệu hàng mà bạn không hề biết.

Những mô hình này có ý nghĩa nếu không nắm bắt kịp thời. Nếu bạn có cái nhìn rõ ràng về kích thước bàn của mình, bạn có thể dự đoán khi nào bạn sẽ phải nâng cấp và lên lịch bảo trì vào thời gian thấp điểm để thực hiện nâng cấp mà ít tác động đến người dùng hơn.

Việc thực hiện các phép chiếu cũng trở nên dễ dàng hơn nhiều. Ví dụ:

• Hôm nay events của chúng tôi bảng phù hợp với bộ nhớ. Với tốc độ phát triển hiện tại, nó sẽ không còn vừa trong bộ nhớ trong bảy tháng nữa.
• Chúng tôi đang sử dụng 30% dung lượng lưu trữ cho loại phiên bản RDS của mình. Chúng tôi sẽ đạt 90% vào tháng 1 năm sau.
• Chúng tôi có truy vấn quét toàn bộ bảng trên payments để tính toán một hàm trên mỗi hàng trong đó đầu vào phụ thuộc vào truy vấn. Chúng tôi biết rằng payments bảng sẽ vượt quá hai triệu hàng trong ba tuần và 20 triệu vào tháng 1 năm sau.

Tất cả đều có khả năng xuất hiện dưới dạng sự cố hoặc mất điện. Nhưng nếu bạn tấn công đủ sớm, bạn có thể giảm thiểu điều này khá dễ dàng. Ví dụ:

• Thiết lập lưu trữ cho tất cả dữ liệu cũ hơn 30 ngày trong events bàn.
• Nâng cấp phiên bản RDS của bạn sau bốn tháng.
• Để hạn chế việc quét toàn bộ bảng, hãy thêm WHERE bổ sung thay vào đó, hãy điều kiện cho truy vấn của chúng tôi để tính toán hàm trên một tập hợp con các hàng nhỏ hơn nhiều.

Tăng cường khả năng hiển thị qua sự phát triển cơ sở dữ liệu của bạn

Để theo dõi sự phát triển của cơ sở dữ liệu, bạn có hai khả năng:

• Cài đặt công cụ đặc biệt (như số liệu thống kê cho MySQL) và kết nối công cụ đó với công cụ thu thập số liệu của bạn thông qua Prometheus, Telegraph hoặc các công cụ khác.
• Sử dụng AppSignal, đặc biệt nếu bạn đang sử dụng ứng dụng này cho ứng dụng của mình.

AppSignal có thể lưu trữ một số loại chỉ số và một trong những loại chỉ số mà nó hỗ trợ được gọi là gauge . Một gauge là một chuỗi thời gian duy nhất cho mỗi môi trường (như production , staging , hoặc development ) mà bạn có thể cập nhật thường xuyên. Các số liệu của AppSignal cũng cho phép gắn thẻ, vì vậy chúng tôi có thể tự động tạo một số gauge được gắn thẻ số liệu cho các bảng cơ sở dữ liệu của chúng tôi. Hãy làm điều đó:

• db.row_count để biết số lượng hàng gần đúng trong các bảng của chúng tôi, trên mỗi bảng (chúng tôi sẽ tính gần đúng sau)
• db.data_size_bytes về số byte mà bảng đang sử dụng
• db.index_size_bytes về số byte mà chỉ mục của bảng đang sử dụng

Lưu ý rằng tôi đang thêm loại giá trị vào tên chỉ số - điều này sẽ giúp ích sau này khi chúng ta xác định cách hiển thị chỉ số. Các số liệu "dữ liệu" và "chỉ số" riêng biệt cũng rất quan trọng. Nếu một bảng chứa nhiều hàng và nhiều hơn một vài chỉ mục thì kích thước của các chỉ mục đó có thể gấp hai hoặc ba lần kích thước của dữ liệu được lưu trữ (vì mọi chỉ mục đều lưu trữ dữ liệu dẫn xuất cho chính nó, tạo ra một số chi phí lưu trữ).

Đếm nhanh các hàng trong bảng cơ sở dữ liệu

Phần "gần đúng" của số hàng là cần thiết. Thông thường, nếu bạn muốn có số hàng chính xác trong một bảng, bạn có thể thực hiện truy vấn như SELECT COUNT(1) FROM my_table . Tuy nhiên, nó có thể chậm hơn chúng ta mong muốn.

Khi bạn COUNT , cơ sở dữ liệu đảm bảo rằng số hàng trong bảng không thay đổi trong khi truy vấn — và do đó sẽ khóa bảng hoặc tạo một giao dịch khác nhau trong khi truy vấn chạy. Bảng càng lớn thì truy vấn càng chậm — nhiều hàng sẽ được quét hơn và sẽ tích lũy nhiều khóa hơn.

Do đó, khi tất cả những gì chúng ta muốn là một con số gần đúng "đủ gần" về số lượng hàng (để ước tính hiệu suất, độ chính xác từ 10-30 nghìn hàng trở lên là đủ tốt), chúng ta có thể sử dụng số liệu thống kê của công cụ cơ sở dữ liệu nội bộ để truy vấn loại dữ liệu này.

Hầu hết các cơ sở dữ liệu đều có quyền truy cập được tối ưu hóa vào dữ liệu bảng vì các hàng được viết thành "trang". Công cụ cơ sở dữ liệu theo dõi hàng nào được gán cho trang nào, gần như theo thứ tự chèn:

• Hàng 1-100 ở trang 1
• Hàng 101-200 ở trang 2
• Hàng 201-300 ở trang 3

vân vân.

Vì công cụ biết nó có bao nhiêu trang trên mỗi bảng và số lượng hàng thô trên mỗi trang mà nó sử dụng, nên nó có thể đếm số trang mà nó đã phân bổ rồi nhân số này với kích thước trang (số hàng trên mỗi trang) để đưa ra ước tính đó. Điều này có một số ưu điểm:tốc độ đếm rất nhanh và không cần khóa bảng khi chạy truy vấn.

Ghi kích thước bảng cho MySQL

Những gì chúng ta cần làm sau đó sẽ khác nhau tùy theo cơ sở dữ liệu vì chúng ta cần truy vấn công cụ cơ sở dữ liệu để biết số liệu thống kê về bảng. Hãy bắt đầu với MySQL. Đây là truy vấn chúng ta cần chạy:

Sau đó, lấy một vài cột từ đầu ra của nó. Những thứ chúng tôi quan tâm là Data_length , Index_length , và Rows . Kích thước của bảng được xác định bằng tổng của Data_length + Index_length và số hàng gần đúng dựa trên các trang là Rows .

Hãy gói nó thành một khối mã để thu thập dữ liệu này cho toàn bộ cơ sở dữ liệu của bạn. Vì chúng tôi không làm việc với bất kỳ ActiveModel nào các lớp, chúng ta sẽ sử dụng các phương thức truy vấn do ActiveRecord cung cấp trực tiếp:

Ghi kích thước bảng cho PostgreSQL

Chúng tôi cần một truy vấn phức tạp hơn cho PostgreSQL vì chúng tôi không có truy vấn phím tắt như SHOW TABLE STATUS . Chúng ta phải truy vấn các bảng PostgreSQL nội bộ:

Lưu ý rằng điều này chỉ chiếm public lược đồ (lược đồ mặc định mà bạn có thể đang sử dụng). Nếu bạn muốn bao gồm các lược đồ khác, bạn sẽ cần xóa WHERE t.table_schema = 'public' điều kiện và thay thế table_info.fetch('name') với table_info.fetch('full_table_name') .

Đảm bảo cập nhật số liệu thường xuyên

Khối này phải chạy đều đặn, vì vậy bạn nên đặt nó vào bộ lập lịch Sidekiq hoặc tác vụ Rake chạy từ cron. Ví dụ:nếu bạn sử dụng good_job, bạn có thể thêm nó vào phần "cron" của mình như sau:

Tạo Trang tổng quan

Sau khi bạn có dữ liệu, hãy xây dựng trang tổng quan. Bạn có thể sao chép trang tổng quan sau theo nhu cầu của mình:

Nhấp vào Add dashboard và sau đó là Import dashboard trong hộp thoại phương thức xuất hiện. Trang tổng quan sẽ cung cấp cho bạn các biểu đồ như thế này:

Và biểu đồ kích thước bảng này:

Lưu ý cách chúng tôi sử dụng thẻ ký tự đại diện để tự động tạo biểu đồ cho mọi bảng trong cơ sở dữ liệu.

Diễn giải dữ liệu

Khi xem dữ liệu của bạn, hãy chú ý đến sự tăng trưởng theo cấp số nhân hoặc tuyến tính về số lượng hoặc kích thước hàng — nói cách khác, bảng ngày càng lớn hơn.

Nếu bạn thấy điều này, bạn có một vài lựa chọn. Một là lên kiến ​​trúc cho sự phát triển không giới hạn này — biết khi nào nên nâng cấp và liệu bạn có thể nâng cấp lên quy mô tốt nhất tiếp theo hay không. Một cách khác là thiết lập một nhiệm vụ xóa thường xuyên - đồng nghiệp cũ của tôi, Wander Hillen đã viết một bài viết hay về chủ đề này.

Ví dụ:trong ảnh chụp màn hình ở trên, một số bảng thường xuyên co lại — đây là lúc các tác vụ dọn dẹp thông thường chạy. Bạn có thể thấy rằng dữ liệu tích lũy cho đến một thời điểm nhất định, nhưng mặc dù số lượng hàng vào khá ổn định nhưng kích thước và số lượng hàng lại giảm xuống.

Miễn là có những sự sụt giảm này và lượng dữ liệu trong bảng của bạn tăng với tốc độ ổn định thì cơ sở dữ liệu của bạn sẽ không làm bạn ngạc nhiên khi bị phá sản giới hạn đột ngột.

Tóm tắt:Tránh phồng dữ liệu từ các bảng cơ sở dữ liệu

Trong bài đăng này, chúng tôi đã xem xét cách bạn có thể theo dõi sự phát triển của cơ sở dữ liệu và giảm tải dữ liệu.

Sự phình to dữ liệu là một rủi ro thực sự đối với các ứng dụng thành công trên thị trường. Bằng cách thiết lập số liệu cho các bảng cơ sở dữ liệu, bạn có thể dự đoán tốt hơn thời điểm mở rộng quy mô cơ sở dữ liệu theo chiều dọc và liệu bạn có cần cài đặt chương trình dọn dẹp dữ liệu cũ thường xuyên hay không. Máy đo AppSignal có thẻ, kết hợp với một chút SQL, có thể cung cấp cho bạn dữ liệu này ở định dạng thuận tiện và dễ chịu.

Đừng để cơ sở dữ liệu của bạn làm bạn ngạc nhiên!

Julik Tarkhanov

Tác giả khách mời Julik Tarkhanov là kỹ sư phần mềm nhân viên tại Cheddar Payments và là tác giả của nhiều thư viện mã nguồn mở Ruby.

Tất cả bài viết của Julik Tarkhanov