ĐĂNG KÝ ĐỂ NHẬN THÔNG TIN MỚI NHẤT

Làm thế nào tối ưu bảng bị phân mảnh trong các hệ thống giao dịch trực tuyến 24×7? | Defragment Table | Shrink Table | Production OLTP 24×7

Trong những dự án tối ưu cơ sở dữ liệu mà Wecommit trực tiếp triển khai, chúng tôi nhận thấy nhiều anh em lập trình chưa có thói quen và chiến lược đánh giá mức độ phân mảnh dữ liệu của bảng. Những lợi ích mà bạn có được trong bài viết này:

  • Biết chính có phải tất cả các trường hợp phân mảnh đều ảnh hưởng nghiêm trọng đến hiệu năng hay không?
  • Các bước triển khai chi tiết, step by step
  • Phân tích về nguyên lý rất ĐƠN GIẢN, DỄ HIỂU, DỄ NHỚ
  • Nếu bạn cần giải pháp chi tiết có thể áp dụng vào hệ thống PRODUCTION trong thực tế (áp dụng được cả với những hệ thống có giao dịch 24×7), bạn cũng có thể tìm thấy giải pháp chi tiết, step by step ở cuối bài viết

1. Tạo bảng dữ liệu phục vụ demo 

  • Thực hiện tạo bảng tên là WECOMMIT_FRAGMENT_TABLE có 7 cột và 2 triệu bản ghi thời điểm ban đầu.
  • Thực hiện xóa phần lớn dữ liệu trong bảng, chỉ để lại 1 bản ghi duy nhất

1.1. Scripts tạo bảng và khởi tạo dữ liệu

SQL> create sequence wecommit_seq_test start with 1 increment by 1;
Sequence created.

SQL> create table wecommit_fragment_table(id number, col1 varchar2(30), col2 varchar2(30), col3 varchar2(30),col4 varchar2(30),col5 varchar2(30),col6 varchar2(30));
Table created.

SQL>  BEGIN
FOR i IN 1 ..2000000 LOOP
insert into WECOMMIT_FRAGMENT_TABLE values(wecommit_seq_test.nextval, 'TRAN QUOC HUY','TRAN QUOC HUY','TRAN QUOC HUY','WECOMMIT','WECOMMIT','WECOMMIT');
commit;
END LOOP;
END;

PL/SQL procedure successfully completed.

SQL> commit;
Commit complete.

# Kiểm tra thông tin của bảng sau khi đã tạo 

SQL> select count(*) from WECOMMIT_FRAGMENT_TABLE;
  COUNT(*)
----------
   2000000
SQL> select owner, segment_name, bytes/1024/1024 "MB" from dba_segments where segment_name='WECOMMIT_FRAGMENT_TABLE'

1

Lúc này table đang có 2,000,000 bản ghi và chiếm 176MB dung lượng trên ổ cứng.

1.2. Script xóa dữ liệu

SQL> delete from WECOMMIT_FRAGMENT_TABLE whereid between 1 and 1999999;
1999999 rows deleted.

SQL> commit;
Commit complete.

1.3.  Kiểm tra trạng thái của bảng sau khi đã xóa dữ liệu

SQL>  select count(*) from WECOMMIT_FRAGMENT_TABLE;
  COUNT(*)
----------
 1

SQL> select owner, segment_name, bytes/1024/1024 "MB" from dba_segments where segment_name='WECOMMIT_FRAGMENT_TABLE'

1

Như vậy: 

  • Mặc dù số lượng bản ghi của Table đã giảm từ 2.000.000 xuống còn 1 bản ghi (giảm 99.9999%), tuy nhiên số block dữ liệu của bảng vẫn chiếm 176MB (không thay đổi bất kỳ chút nào).
  • Trường hợp này bảng đã có hiện tượng phân mảnh cực kỳ nhiều

1.4. Script dưới đây cho ta biết chính xác mức độ phân mảnh của bảng

Bước 1: Thực hiện cập nhật lại thông tin thống kê của bảng

SQL> EXEC DBMS_STATS.gather_table_stats('HUYTQ', 'WECOMMIT_FRAGMENT_TABLE', estimate_percent => 100, cascade => TRUE);
PL/SQL procedure successfully completed.

 

Bước 2:  Câu lệnh kiểm tra chi tiết tình trạng phân mảnh 

  SELECT owner,
         table_name,
         ROUND ((blocks * 8) / 1024, 2)
             "Fragmented size (mb)",
         ROUND ((num_rows * avg_row_len / 1024 / 1024), 2)
             "Actual size (mb)",
           ROUND ((blocks * 8) / 1024, 2)
         - ROUND ((num_rows * avg_row_len / 1024 / 1024), 2)
             "Reclaim size (mb)",
             (  (  ROUND ((blocks * 8 / 1024), 2)
                 - ROUND ((num_rows * avg_row_len / 1024 / 1024), 2))
              / ROUND ((blocks * 8 / 1024), 2))
           * 100
         - 10
             "Reclaimable space % "
    FROM dba_tables
   WHERE     table_name = '&TABLE_NAME'
         AND ROUND ((blocks * 8 / 1024), 2) > 0
         AND     (  (  ROUND ((blocks * 8 / 1024), 2)
                     - ROUND ((num_rows * avg_row_len / 1024 / 1024), 2))
                  / ROUND ((blocks * 8 / 1024), 2))
               * 100
             - 10 >
             10
         AND   ROUND ((blocks * 8) / 1024, 2)
             - ROUND ((num_rows * avg_row_len / 1024 / 1024), 2) >
             100
         AND owner NOT IN ('SYS')
ORDER BY 1, 6 DESC;

Enter value for table_name: WECOMMIT_FRAGMENT_TABLE
2
Kết quả cho thấy bảng đã bị phân mảnh hơn 90%

2. Nếu bảng bị phân mảnh thì ảnh hưởng thế nào đến hiệu năng của các câu lệnh? Có phải cứ Table bị phân mảnh thì hệ thống sẽ cực kỳ chậm không?

Các bạn sẽ tìm được câu trả lời thông qua Demo và bài phân tích dưới đây

2.1. Chi tiết câu lệnh thực hiện đánh giá

Chúng ta hãy cùng xem câu lệnh tìm kiếm theo ID (hiện tại cột này không được đánh Index).

select * from WECOMMIT_FRAGMENT_TABLE where id=2000000;

2.2. Trường hợp không có Index trên cột ID thì chiến lược thực thi của câu lệnh thế nào?

3
Để thực hiện lấy ra 1 bản ghi và trả về cho người dùng, hệ thống cần thực hiện TABLE ACCESS FULL, bản chất của công việc này là QUÉT TOÀN BỘ CÁC BLOCK DỮ LIỆU của bảng.
Chúng ta có thể thấy rằng, công việc này đang tiêu tốn dự kiến 1 phút 13s để thực hiện, và chi phí của công việc này là 6055 (theo đơn vị tính chi phí tài nguyên thực hiện câu lệnh của cơ sở dữ liệu).       

2.3. Nếu cột ID được đánh Index thì hiệu năng của câu lệnh sẽ thay đổi thế nào?

Tôi sẽ tạo index trên cột ID cho công việc Test này 

create index IDX_ID on WECOMMIT_FRAGMENT_TABLE(ID);

Trong trường hợp này, chiến lược thực thi của câu lệnh như sau:

chiến lược thực thi khi bảng bị phân mảnh
chiến lược thực thi khi bảng bị phân mảnh

Như vậy, nếu cột ID được đánh Index, hệ thống có thể biết chính xác bản ghi có giá trị ID=2000.000 đang ở vị trí nào (block nào). Và vì xác định được chính xác block cần vào để lấy thông tin, hệ thống chỉ cần ước tính chưa tới 1s để thực hiện công việc này.
Lúc này các bạn có thể thay việc hiệu năng được cải thiện TỤT HUYẾT ÁP:

  • Về thời gian: từ 1 phút 13s (73s) cho việc tìm kiếm tất cả các block dữ liệu, giảm xuống còn 1s cho việc tìm kiếm chính xác thông tin của block dữ liệu (thông tin nhận được từ Index – giá trị ROWID). Thời gian đã cải thiện hơn 98.64%
  • Về chi phí thực hiện của câu lệnh: giảm từ 6055 xuống còn 72 – tương đương với giảm hơn 98.8%.
  • Tổng số bản ghi trả ra của cả 2 trường hợp vẫn là: 01 bản ghi. 

2.4 Như vậy việc một bảng bị phân mảnh không phải lúc nào cũng dẫn tới việc hệ thống bị CHẬM, TREO

Nếu hiểu rõ bản chất, bạn sẽ thấy rằng: việc tối ưu không tuân thủ theo một nguyên tắc cứng nhắc nào hết.

Nếu một bảng bị phân mảnh rất nhiều, một số câu lệnh trong hệ thống của bạn sẽ bị giảm hiệu năng thậm tệ, một số câu lệnh thì gần như không ảnh hưởng. Trong ví dụ trên của tôi đã chứng minh được điều này.

Tại phần này tôi sẽ lấy một ví dụ đời thường để bạn hiểu được “bản chất” của vấn đề bên trên.

Các bạn có thể hình dùng Table của bạn giống như một tòa chung cư. Các dữ liệu của bạn giống như những người dân sống trong chung cư đó.

Lúc đầu bạn đưa dữ liệu vào bảng, giống như việc những người dân ồ ạt kéo đến ở trong tòa chung cư.

Lúc ban đầu chung cư rất đông vui nhộn nhịp, rất cả các phòng đều có người ở (dữ liệu được đưa vào các block). Hình ảnh dưới đây chính là chung cư của các bạn: có 14 tầng, mỗi tầng có 18 căn hộ (mỗi căn hộ chính là 1 block chứa dữ liệu đó).

Tầng 14 P1401 P1402 P1403 P1404 P1405 P1406 P1407 P1408
Tầng 13 P1301 P1302 P1303 P1304 P1305 P1306 P1307 P1308
Tầng 12 P1201 P1202 P1203 P1204 P1205 P1206 P1207 P1208
Tầng 11 P1101 P1102 P1103 P1104 P1105 P1106 P1107 P1108
Tầng 10 P1001 P1002 P1003 P1004 P1005 P1006 P1007 P1008
Tầng 9 P901 P902 P903 P904 P905 P906 P907 P908
Tầng 8 P801 P802 P803 P804 P805 P806 P807 P808
Tầng 7 P701 P702 P703 P704 P705 P706 P707 P708
Tầng 6 P601 P602 P603 P604 P605 P606 P607 P608
Tầng 5 P501 P502 P503 P504 P505 P506 P507 P508
Tầng 4 P401 P402 P403 P404 P405 P406 P407 P408
Tầng 3 P301 P302 P303 P304 P305 P306 P307 P308
Tầng 2 P201 P202 P203 P204 P205 P206 P207 P208
Tầng 1 P101 P102 P103 P104 P105 P106 P107 P108

Một ngày nọ, chung cư buộc phải di dời người dân ra địa điểm khác (vì lý do quy hoạch), tất cả mọi người đều rời khỏi căn hộ của mình, trừ ông Trần Quốc Huy vẫn bám trụ ở lại. 

Hình ảnh căn hộ của bạn lúc này như sau: 

Tầng 14 P1401 P1402 P1403 P1404 P1405 P1406 P1407 P1408
Tầng 13 P1301 P1302 P1303 P1304 P1305 P1306 P1307 P1308
Tầng 12 P1201 P1202 P1203 P1204 P1205 P1206 P1207 P1208
Tầng 11 P1101 P1102 P1103 P1104 P1105 P1106 P1107 P1108
Tầng 10 P1001 P1002 P1003 P1004 P1005 P1006 P1007 P1008
Tầng 9 P901 P902 P903 P904 P905 P906 P907 P908
Tầng 8 P801 P802 P803 P804 P805 P806 P807 P808
Tầng 7 P701 P702 P703 P704 P705 P706 P707 P708
Tầng 6 P601 P602 P603 P604 P605 P606 P607 P608
Tầng 5 P501 P502 P503 P504 P505 P506 P507 P508
Tầng 4 P401 P402 P403 P404 P405 P406 P407 P408
Tầng 3 P301 P302 P303 P304 P305 P306 P307 P308
Tầng 2 P201 P202 P203 P204 P205 P206 P207 P208
Tầng 1 P101 P102 P103 P104 P105 P106 P107 P108

Các căn hộ đánh dấu màu xanh là những căn hộ trống.

Căn hộ được bôi màu đỏ (phòng 1106) là căn hộ có người ở (ông Trần Quốc Huy vẫn ở đó).

Hiện tượng này tương đương với việc rất nhiều block dữ liệu đang không được sử dung, nhưng không được thu hồi (hiện tượng phân mảnh của bảng).

Bây giờ, chúng ta xét 2 trường hợp

Trường hợp 1: Chung cư này không có danh sách thông tin từng người dân đang ở căn hộ số bao nhiêu. Nếu bạn cần đi tìm ông Trần Quốc Huy để yêu cầu ông ấy di dời khỏi tòa chung cư thì phải làm thế nào?

  • Do bạn không biết chính xác ông ấy đang ở phòng số bao nhiêu, do đó bạn sẽ buộc phải đi từng tầng, đối với mỗi tầng sẽ ghé qua từng phòng để tìm. Việc này chính là giải thuật FULL TABLE SCAN (quét toàn bộ block dữ liệu).

Trường hợp 2: Nếu ban quản lý của chung cư ấy có một danh sách cho biết thông tin của người dân và căn hộ tương ứng thì sao. 

  • Để cần tìm ông Trần Quốc Huy, bạn sẽ phải mở danh sách đó ra và tra xem anh ta đang ở phòng số bao nhiêu. Tại đây bạn biết được anh ấy ở tầng 11, phòng 1106. Bước này tương ứng với giải thuật INDEX RANGE SCAN để quét thông tin ROWID- số phòng)
  • Bước số 2: Đi tới phòng 1106 để gặp ông Trần Quốc Huy. Bước này chính là bước TABLE ACCES BY INDEX ROWID 

5

3. Tôi có một chương trình có phí – Từ điển tối ưu 100x hiệu năng – chương trình này sẽ giúp bạn HOÀN TOÀN TỰ TIN và KHÁC BIỆT so với các đồng nghiệp về năng lực tối ưu

Trong chương trình này bạn sẽ nhận được rất nhiều các KIẾN THỨC, KINH NGHIỆM ĐỘC QUYỀN, có nhiều thứ các anh em DEV còn không biết về sự tồn tại. 

Tôi sẽ đồng hành 1 năm cùng anh em, để anh em hoàn toàn tự tin khi áp dụng các kiến thức vào dự án thực tế trên công ty.

Đây là một chương trình có phí, anh em có thể tham gia chương trình 01 năm ngay từ mức phí 8.000.000 VNĐ.

Các anh em có thể đăng ký trải nghiệm để hiểu rõ mọi thứ trong chương trình để tự đánh giá được chương trình có phù hợp với mục tiêu của bản thân hay không.

Buổi trải nghiệm này diễn ra qua Zoom Online, cách thức đăng ký tham gia trải nghiệm như sau:

  • Cách 1: Đăng ký trực tiếp trên Website wecommit.com.vn qua Form đăng ký
  • Cách 2: Inbox Zalo 0888549190

4. Một số kiến thức khác về tối ưu SQL, tối ưu Database mà bạn cần biết

5. Nếu ban muốn liên hệ với tôi

Tác giả: Trần Quốc Huy – Founder & CEO Wecommit.

Các bạn có thể liên hệ, thảo luận các kiến thức về tối ưu cùng tôi qua kênh FB cá nhân của tôi

Facebook: https://www.facebook.com/tranquochuy.toiuu/

Views: 999





    Câu hỏi bảo mật

    Trả lời

    Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *