Read-only archive. Login and posting are unavailable.
View Full Version : Hỏi cách xóa 1 tỉ dòng dữ liệu trong MS_SQL
Hiện mình có 1 reporting database bị đầy, nên cần xóa bớt dữ liệu, chỉ giữ lại data của 2 năm cuối. Cứ mỗi ngày lưu một lát cắt (có log number riêng cho từng ngày và date) vào table khoảng 500 ngàn dòng mà không để ý nó bị đầy, tính ra khoảng 1,5 tỉ dòng dữ liệu.
Hiện tại chức năng count đã không còn tác dụng do dữ liệu quá nhiều nên không biết cụ thể tổng số là bao nhiêu. Chạy delete theo từng ngày một thì khá là lâu. Nên chắc phải dùng tới truncate, nhưng mà chỉ biết tới nó mà chưa từng dùng đến.
Có cao thủ MS_SQL nào đã từng dùng đến truncate để xóa một phần dữ liệu cho mình xin ít kinh nghiệm được không ? Xóa toàn bộ dữ liệu trước 2018.
dreamnight
21-02-2020, 22:30
Hiện mình có 1 reporting database bị đầy, nên cần xóa bớt dữ liệu, chỉ giữ lại data của 2 năm cuối. Cứ mỗi ngày lưu một lát cắt (có log number riêng cho từng ngày và date) vào table khoảng 500 ngàn dòng mà không để ý nó bị đầy, tính ra khoảng 1,5 tỉ dòng dữ liệu.
Hiện tại chức năng count đã không còn tác dụng do dữ liệu quá nhiều nên không biết cụ thể tổng số là bao nhiêu. Chạy delete theo từng ngày một thì khá là lâu. Nên chắc phải dùng tới truncate, nhưng mà chỉ biết tới nó mà chưa từng dùng đến.
Có cao thủ MS_SQL nào đã từng dùng đến truncate để xóa một phần dữ liệu cho mình xin ít kinh nghiệm được không ? Xóa toàn bộ dữ liệu trước 2018.
Solution simple nhất là bạn dùng SELECT INTO dữ liệu cần giữ vào 1 table khác xong truncate cái table cũ đi rồi rename table mới :look_down:. Tuy nhiên phải chia trường hợp ra phụ thuộc vào tình trạng table của bạn:
1) Có index cột nào ko? Vd CreatedDate desc, thì dùng SELECT INTO nhanh vì nó đã được index rồi.
2) Xui chả có index gì. Đây là trường hợp dễ dính nhất cho nhiều hệ thống mà dev ko phải DBA. Lúc này cũng là solution trên nhưng bạn phải viết store procedure để chạy batch theo từng tháng để ko bị out memory. Vd chạy 1/2018 rồi 2/2018.
3) Trường hợp xui nhất hệ mặt trời là có quá nhiều foreign keys (3 trở lên). Trường hợp này khiến việc truncate hay delete trở nên cực hình. Lời khuyên là remove key ra hết rồi chấp nhận hi sinh table đó mất reference keys. Rồi cũng SELECT INTO qua table mới.
Cả 3 trường hợp bạn phải check log size thì lúc này dễ ăn đạn vì ko kiểu soát được số dòng được kéo qua table mới. Tốt nhất hãy switch qua chế độ Simple Recovery rồi kéo qua.
Cách phức tạp hơn là dựa vào log file để recovery. Tuy nhiên cách này performance sẽ chậm, đổi lại an toàn cho hệ thống database server bạn ko bị out memory hay crash vì foreign keys. Keyword: Log shipping, cái này để bạn tự google để cho thêm kiến thức DBA nhé
Solution simple nhất là bạn dùng SELECT INTO dữ liệu cần giữ vào 1 table khác xong truncate cái table cũ đi rồi rename table mới :look_down:. Tuy nhiên phải chia trường hợp ra phụ thuộc vào tình trạng table của bạn:
1) Có index cột nào ko? Vd CreatedDate desc, thì dùng SELECT INTO nhanh vì nó đã được index rồi.
2) Xui chả có index gì. Đây là trường hợp dễ dính nhất cho nhiều hệ thống mà dev ko phải DBA. Lúc này cũng là solution trên nhưng bạn phải viết store procedure để chạy batch theo từng tháng để ko bị out memory. Vd chạy 1/2018 rồi 2/2018.
3) Trường hợp xui nhất hệ mặt trời là có quá nhiều foreign keys (3 trở lên). Trường hợp này khiến việc truncate hay delete trở nên cực hình. Lời khuyên là remove key ra hết rồi chấp nhận hi sinh table đó mất reference keys. Rồi cũng SELECT INTO qua table mới.
Cả 3 trường hợp bạn phải check log size thì lúc này dễ ăn đạn vì ko kiểu soát được số dòng được kéo qua table mới. Tốt nhất hãy switch qua chế độ Simple Recovery rồi kéo qua.
Cách phức tạp hơn là dựa vào log file để recovery. Tuy nhiên cách này performance sẽ chậm, đổi lại an toàn cho hệ thống database server bạn ko bị out memory hay crash vì foreign keys. Keyword: Log shipping, cái này để bạn tự google để cho thêm kiến thức DBA nhé
Mình đang chạy cái solution đó, cơ mà select into cũng từng ngày nó mới chịu, vì hết thì chắc khoảng 360 mil dòng dữ liệu, quá nhiều nó cũng không chịu.
dreamnight
21-02-2020, 22:43
Mình đang chạy cái solution đó, cơ mà select into cũng từng ngày nó mới chịu, vì hết thì chắc khoảng 360 mil dòng dữ liệu, quá nhiều nó cũng không chịu.
Thế thì chấp nhận đi ko có cách nào nhanh hơn đâu. 360m records là con số nhiều cần tốn time để tính toán sự toàn vẹn dữ liệu mà. Muốn speed up thì scale up cái server lên thôi chứ ko cũng ko nhanh hơn được. Ngày trước mình phục hồi 500m cũng cả ngày trời, phải cách ly server production để nó chạy full load đó. Database ko nhanh được đâu vì nhanh nó sai là hỏng data ngay :sosad:
Thế thì chấp nhận đi ko có cách nào nhanh hơn đâu. 360m records là con số nhiều cần tốn time để tính toán sự toàn vẹn dữ liệu mà. Muốn speed up thì scale up cái server lên thôi chứ ko cũng ko nhanh hơn được. Ngày trước mình phục hồi 500m cũng cả ngày trời, phải cách ly server production để nó chạy full load đó. Database ko nhanh được đâu vì nhanh nó sai là hỏng data ngay :sosad:
Thanks thím, đang cách ly, cắt toàn bộ import, mai thứ 7 mà chắc cũng phải làm việc rồi, để thứ 2 hi vọng còn kịp đưa nó vào lại hoạt động.
Mà truncate cả cái table 1,5 tỉ dòng dữ liệu - khoảng 1 Tb dữ liệu thì liệu nó có cho phép không thím ? vừa chạy vừa lo vì nếu nó không cho phép truncate là thành công cốc.
dreamnight
22-02-2020, 00:13
Mà truncate cả cái table 1,5 tỉ dòng dữ liệu - khoảng 1 Tb dữ liệu thì liệu nó có cho phép không thím ? vừa chạy vừa lo vì nếu nó không cho phép truncate là thành công cốc.
Truncate là delete force đó, bao đi hết, chưa bao giờ gặp trường hợp truncate ko được trong đời :beauty:
Hiện mình có 1 reporting database bị đầy, nên cần xóa bớt dữ liệu, chỉ giữ lại data của 2 năm cuối. Cứ mỗi ngày lưu một lát cắt (có log number riêng cho từng ngày và date) vào table khoảng 500 ngàn dòng mà không để ý nó bị đầy, tính ra khoảng 1,5 tỉ dòng dữ liệu.
Hiện tại chức năng count đã không còn tác dụng do dữ liệu quá nhiều nên không biết cụ thể tổng số là bao nhiêu. Chạy delete theo từng ngày một thì khá là lâu. Nên chắc phải dùng tới truncate, nhưng mà chỉ biết tới nó mà chưa từng dùng đến.
Có cao thủ MS_SQL nào đã từng dùng đến truncate để xóa một phần dữ liệu cho mình xin ít kinh nghiệm được không ? Xóa toàn bộ dữ liệu trước 2018.
Mình không chắc mình hiểu đúng không, nhưng mà nếu mình nghĩ giải pháp table partition có thể giúp bạn.
Với table partition, ví dụ bạn đánh partition theo năm. Rồi bạn có thể truncate từng mảnh của partition. (Ví dụ truncate các partion của năm <= 2018) Bạn xem thử xem.
B. Truncate Table Partitions
Applies to: SQL Server ( SQL Server 2016 (13.x) through current version)
The following example truncates specified partitions of a partitioned table. The WITH (PARTITIONS (2, 4, 6 TO 8)) syntax causes partition numbers 2, 4, 6, 7, and 8 to be truncated.
TRUNCATE TABLE PartitionTable1
WITH (PARTITIONS (2, 4, 6 TO 8));
GO
https://docs.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql?view=sql-server-ver15
Theo mình biết thì partition sinh ra là chủ yếu để cho mấy việc maintenance kiểu này. Chứ để tăng tốc query chỉ là phụ thôi (Partition làm chưa biết nhanh hơn ra sao nhưng tác dụng phụ tràn ngập, vd như Index Seek trên bảng có nhiều partition thì tăng query cost vl luôn). Bên mình dữ liệu không lớn như nên mình nghiên cứu với thử toàn để cho biết chứ ít không có cơ hội xài.
Mà mình nghĩ 1,5 tỉ dòng, bạn đang xài SQL phiên bản bao nhiêu, hay đang xài Azure SQL cloud. Bạn có nghĩ đến giải pháp dùng columnstore để tiết kiệm bộ nhớ + index scan độ thần thánh không (cực kì phù hợp với các hệ thống data warehouse nếu không có yêu cầu đặc biệt, như update liên miên chẳng hạn). Chứ trong mắt mình 1 tỉ rưỡi dòng mà xài columnstore thì không lớn lắm đâu. 1355308
Mình không chắc mình hiểu đúng không, nhưng mà nếu mình nghĩ giải pháp table partition có thể giúp bạn.
Với table partition, ví dụ bạn đánh partition theo năm. Rồi bạn có thể truncate từng mảnh của partition. (Ví dụ truncate các partion của năm <= 2018) Bạn xem thử xem.
https://docs.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql?view=sql-server-ver15
Theo mình biết thì partition sinh ra là chủ yếu để cho mấy việc maintenance kiểu này. Chứ để tăng tốc query chỉ là phụ thôi (Partition làm chưa biết nhanh hơn ra sao nhưng tác dụng phụ tràn ngập, vd như Index Seek trên bảng có nhiều partition thì tăng query cost vl luôn). Bên mình dữ liệu không lớn như nên mình nghiên cứu với thử toàn để cho biết chứ ít không có cơ hội xài.
Mà mình nghĩ 1,5 tỉ dòng, bạn đang xài SQL phiên bản bao nhiêu, hay đang xài Azure SQL cloud. Bạn có nghĩ đến giải pháp dùng columnstore để tiết kiệm bộ nhớ + index scan độ thần thánh không (cực kì phù hợp với các hệ thống data warehouse nếu không có yêu cầu đặc biệt, như update liên miên chẳng hạn). Chứ trong mắt mình 1 tỉ rưỡi dòng mà xài columnstore thì không lớn lắm đâu. 1355308
Đúng rồi, trước đó mình tìm kiếm các giải pháp thì biết cái truncate có thể dùng được, và partition thì có thể xóa một phần, nhưng partition thế nào thì lại khá ít tư liệu.
Nó là MS_SQL và mới update lên bản 2014. Server được dùng cho việc reporting nên chỉ có import không có update. Được build từ hơn 10 năm trước nên việc chuyển đổi chắc không thể. Thực sự đến lúc mình kiểm tra thấy gần 1 Tbs cho dữ liệu SQL cũng giật cả mình, hiện free space còn có gần 100 Gb, bọn quản lý server làm full backup hàng ngày mà cũng đéo kêu ca gì cả.
Đúng rồi, trước đó mình tìm kiếm các giải pháp thì biết cái truncate có thể dùng được, và partition thì có thể xóa một phần, nhưng partition thế nào thì lại khá ít tư liệu.
Nó là MS_SQL và mới update lên bản 2014. Server được dùng cho việc reporting nên chỉ có import không có update. Được build từ hơn 10 năm trước nên việc chuyển đổi chắc không thể. Thực sự đến lúc mình kiểm tra thấy gần 1 Tbs cho dữ liệu SQL cũng giật cả mình, hiện free space còn có gần 100 Gb, bọn quản lý server làm full backup hàng ngày mà cũng đéo kêu ca gì cả.
Mình làm với Azure SQL cloud, toàn version mới nhất nên ko biết nhiều với mấy version cũ, nhưng 2014 thì gay đấy, mình thấy cái hướng dẫn truncate partition trên nó ghi dành cho SQL Server 2016. Trên cloud thì vụ truncate partition mình từng test thử rồi, làm OK. Partition sinh ra để chuyên trị ba cái trò rebuild, reorganize 1 phần mà.
Cả columnstore mà xài tốt thì theo mình biết cũng phải từ 2016 trở lên. :stick: Nghe vậy thôi chứ mình chưa có trải nghiệm qua columnstore của SQL các version thấp.
Bảng 1.5 tỉ row thì chắc chắn phải là bảng fact rồi, mà nếu đã là bảng fact thì đâu ai link với nó đâu, sợ gì vụ foreign key nhỉ.
Cái vụ phải select ngày báo lỗi là chính xác báo lỗi gì vậy bác. Ko biết server mấy bạn ra sao chứ mình chơi trên cloud, plan cỡ trung thôi mà quất select into bảng tạm mấy chục triệu row còn được mà. Ít nhất cũng phải select được theo năm chứ nhỉ.
1355308
dreamnight
22-02-2020, 10:42
Mình làm với Azure SQL cloud, toàn version mới nhất nên ko biết nhiều với mấy version cũ, nhưng 2014 thì gay đấy, mình thấy cái hướng dẫn truncate partition trên nó ghi dành cho SQL Server 2016. Trên cloud thì vụ truncate partition mình từng test thử rồi, làm OK. Partition sinh ra để chuyên trị ba cái trò rebuild, reorganize 1 phần mà.
Cả columnstore mà xài tốt thì theo mình biết cũng phải từ 2016 trở lên. :stick: Nghe vậy thôi chứ mình chưa có trải nghiệm qua columnstore của SQL các version thấp.
Bảng 1.5 tỉ row thì chắc chắn phải là bảng fact rồi, mà nếu đã là bảng fact thì đâu ai link với nó đâu, sợ gì vụ foreign key nhỉ.
Cái vụ phải select ngày báo lỗi là chính xác báo lỗi gì vậy bác. Ko biết server mấy bạn ra sao chứ mình chơi trên cloud, plan cỡ trung thôi mà quất select into bảng tạm mấy chục triệu row còn được mà. Ít nhất cũng phải select được theo năm chứ nhỉ.
1355308
Azure SQL là nguyên 1 hệ thống to đùng nó nuôi thì lấy gì mà ko xử lý được 1.5 tỉ rows.
Chức năng partition hay columnstore có thể apply được trên database (sql server 2012 đã có), tuy nhiên vấn đề lớn phát sinh thêm đó là phải đợi nó scan full 1.5 tỉ row nhé. Mà vì con database server trên bị xiềng về hardware rồi (theo mình nghĩ cấu hình nó tầm 16Gb là cùng) thì chạy 1.5 tỉ row để apply partition ko nổi vì out memory ngay, nếu siết memory thì phải đợi cả hàng chục tiếng.
Azure SQL là nguyên 1 hệ thống to đùng nó nuôi thì lấy gì mà ko xử lý được 1.5 tỉ rows.
Chức năng partition hay columnstore có thể apply được trên database (sql server 2012 đã có), tuy nhiên vấn đề lớn phát sinh thêm đó là phải đợi nó scan full 1.5 tỉ row nhé. Mà vì con database server trên bị xiềng về hardware rồi (theo mình nghĩ cấu hình nó tầm 16Gb là cùng) thì chạy 1.5 tỉ row để apply partition ko nổi vì out memory ngay, nếu siết memory thì phải đợi cả hàng chục tiếng.
thím trả lời hoàn toàn chính xác, nó bị xiềng về hardware nên bị lỗi out of memory.
alohomora
22-02-2020, 13:45
Tạo job cho nó xóa từ từ vài trăm k rows mỗi 10p thì từ từ cũng xong thôi chứ nhỉ? Có gấp gáp không thím?
Tạo job cho nó xóa từ từ vài trăm k rows mỗi 10p thì từ từ cũng xong thôi chứ nhỉ? Có gấp gáp không thím?
thanks thím, sau khi reduce xong thì chắc chắn sẽ phải có job reduce từ từ để tránh tình trang trong bài bị lặp lại. Đéo hiểu sao lúc build thì chả ai nghĩ đến cái việc này. Nó còn mấy cái bảng như thế nữa, đang phải dọn dần :sexy:
dreamnight
22-02-2020, 14:40
thanks thím, sau khi reduce xong thì chắc chắn sẽ phải có job reduce từ từ để tránh tình trang trong bài bị lặp lại. Đéo hiểu sao lúc build thì chả ai nghĩ đến cái việc này. Nó còn mấy cái bảng như thế nữa, đang phải dọn dần :sexy:
Đơn giản mà, thằng viết ra toàn Dev có phải DBA đâu mà biết ba cái vụ này. Dev giờ chỉ thấy CRUD dưới DB được là mừng rồi, còn DB nó growing thế nào ko care đâu. Nói thật cho tới bây giờ mình vẫn thấy nhiều hệ thống coi nhẹ DBA lắm, toàn code cho run trước đã rồi tính tới vụ án maintainance sau, mà DB lên PROD rồi khó fix lắm :chaymau:
alohomora
22-02-2020, 14:46
Đơn giản mà, thằng viết ra toàn Dev có phải DBA đâu mà biết ba cái vụ này. Dev giờ chỉ thấy CRUD dưới DB được là mừng rồi, còn DB nó growing thế nào ko care đâu. Nói thật cho tới bây giờ mình vẫn thấy nhiều hệ thống coi nhẹ DBA lắm, toàn code cho run trước đã rồi tính tới vụ án maintainance sau, mà DB lên PROD rồi khó fix lắm :chaymau:
dev giờ dùng Entity Framework thì chả cần quan tâm đến indexing luôn ấy chứ, khi nào scale lớn lớn tí lại tính sau :adore:
alohomora
24-02-2020, 08:10
Rốt cục là thớt dùng cách gì vậy? Chia sẻ lên anh em còn học hỏi nào.
1. Một ngày insert tầm 500k row là tương đối nhiều. Có nhất thiết phải dùng SQL database để lưu không? Vì bạn bảo đây là bản lưu report. Mình cũng không biết report về gì nhưng từ con số 500k row/ngày thì nghe vẻ con số khá tương tự việc logging. Nếu report của bạn mà chỉ dùng ở mức hạn chế, ví dụ query theo ngày hay theo tháng, thì theo mình lưu vào blob store trên clould cho rẻ. Vì SQL tính giá thành đắt, dùng nó cho dữ liệu có index thôi.
2. Có một solution khác #2 một chút. Là tạo một bảng mới cấu trúc y hệt bảng cũ. Yeu cầu cần sửa code một chút. Khi write thì write vào bảng mới, khi read thì read từ bảng mới, nếu không thấy thì read từ bảng cũ. Chạy job đến ngày hết hạn thì xoá bớt dữ liệu, hoặc canh đến ngày hết hạn thì truncate bảng cũ đi
3. Cái job chạy lập lịch để xoá bớt dữ liệu: mình thấy rất phí resource, mỗi ngày insert 500k row và expect là cũng sẽ có 500k row của một ngày nào đó trong quá khứ bị xoá. Cảm giác database của thím đang bị stress vì cái hoạt động insert/delete report, performance của cả engine bị drag down vì thằng này - mặc dù nó có thể ko phải là business chính. Theo mình thì nên cô lập cái chức năng report hoặc re-design lại cái thiết kế reporting. Nhiều writes dư thừa quá...
Lạm bàn một chút về mấy vụ database này. Theo mình thấy 1 ngày 500.000 dòng thì nghe nhiều đấy, nhưng kinh nghiệm của mình quy ra columnstore thì không nhiều đâu, mới chỉ bằng 1 nửa của 1 full rowgroup à. Mình ước tính cả database 10 năm 1,5 tỉ dòng kia mà dã columnstore index, không có rowstore index nào khác thì chỉ ~100 -> 200 GB là cùng.
Cỡ này không đến mức phang cả cái giải pháp data lake trên blob/data lake storage đâu. 500.000 dòng 1 ngày theo mình là quá nhỏ so với cái hệ này. Mình thấy chỉ nên nghĩ đến data lake khi data dự tính có chừng > 500GB dữ liệu file parquet. (Mình ước tính phải khoảng tương đương vài chục tỉ dòng).
Không biết là do mình có làm sai, không biết cách dùng hay gì ko nhưng theo kinh nghiệm của mình thì trên 1 dataset nhỏ vài triệu row đến vài chục triệu row, SQL Server thực hiện aggregate bằng columnstore index scan sẽ nhanh hơn dùng Spark aggregate trên 1 file parquet. https://i.imgur.com/2y9npcU.png.
Khi mà data lớn lên hẳn nữa rồi, bắt buộc phải tìm giải pháp mới, mà chơi trên cloud Azure thì có thể nghĩ đến kiểu giải pháp kiểu như Azure Synapse Analytic. Mấy cái này nó kiểu chả khác gì SQL bình thường nhưng có siêu scale khổng lồ. Tuy nhiên thì nó đắt đỏ khủng khiếp. Còn ba cái quỷ chơi theo kiểu data lake này, storage thì siêu rẻ, nhưng còn phải kéo theo 1 tầng query, xử lý kiểu Hadoop Hive, Spark hoặc mấy cái như Azure Data Lake Analytic. Được cái là scale gần như vô hạn. Mà cái này hơi bị hiếm với khó xài, kiếm được analyst biết xài để còn mine được data hơi bị khó, còn engineer thì cũng chẳng mấy người rành để mà thiết kế điều khiển. https://i.imgur.com/JGdqgzY.png 1355308
Tất nhiên ta có thể mix như các data mart quan trọng, chính yếu, đã được cô đọng lên, analyst chơi nhiều thì giữ trên 1 cái RDBMS. Còn đối với kiểu mấy cái nho nhỏ, kiểu event, log...số lượng cực lớn thì giữ trong data lake cũng khá đẹp, khi cần thì dùng mấy cái như Spark ad-hoc query vào.
vBulletin® v3.8.0, Copyright ©2000-2026, Jelsoft Enterprises Ltd.