Xuất báo cáo số lượng bản ghi lớn với PHP (Laravel)
Hi anh em. Mình là Nam, 1 Web Developer, đang sử dụng Laravel & Vuejs cho dự án của công ty. Hôm nay mới vỡ cái ngu 1 chút và thấy cái này khá hay nên muốn chia sẻ kinh nghiệm này với anh em :)) Mong mọi người góp ý thêm và đừng gạch đá mình.
Chả là mình phải thực hiện công việc xuất báo cáo dữ liệu cho KH ở công ty, công việc cũng khá suôn sẻ và không có gì đáng nói cho tới ngày nó có vấn đề :))
Phần 1: Những yêu cầu cơ bản
Khách hàng bên mình có yêu cầu xuất báo cáo dữ liệu ra file Excel, là log của họ gọi sang API công ty mình. Việc này khá thốn vì nếu là file CSV dạng không cấu trúc, thì sẽ chỉ việc select ra và trả về theo dạng stream cho client. Tuy nhiên việc xuất Excel hơi a đuồi do bạn phải viết file xong mới trả về cho khách hàng được.
Thời gian đầu thì khá ok ae ạ. Mình xử dụng thằng
Nhiều người cùng vào export 1 lúc & cùng phải chờ => sv oẳng
Phải chờ export xong 1 file mới có thể chọn file tiếp theo => chờ export xong mới được tạo tiếp => ux tệ
sv lag, web lag => KH buồn => sếp buồn => Nam oẳng
Do đó mình xử dụng cách như sau:
Không bắt đợi khi export nữa mà sẽ lưu tham số, yêu cầu export vào DB
Định kỳ quét các yêu cầu, tham số đó và ném nó vào Queue cho chạy lần lượt và lưu file
Cập nhật link download file và gửi cho KH
Sau khi đổi qua cách này thì việc Export đã mượt mà hơn rất nhiều, mọi người có thể chọn 1 lúc 10 file , 20 file ,… vứt đó và đi ăn cơm. Ăn xong về là có file, không phải đợi nữa. Việc export tuần tự cũng giúp cho Server đỡ thốn hơn, CPU & Mem đỡ bị quá tải khi có quá nhiều connection & request để export => passed. +1 respect :))
Phần 2: Nhiều vấn đề hơn
Sau 1 thời gian chạy ngon nghẻ, KH vui và sử dụng dịch vụ của mình nhiều hơn=> tra cứu nhiều hơn => nhiều log hơn. Vấn đề xảy ra là khi export 1 file excel lớn ( tầm 400k row với khoảng 10 cột text) , server 8GB ram, 4CPU thì mình bị xảy ra vấn đề là bị Mem & CPU overload
Mày mò 1 lúc mình thấy thằng Laravel có hỗ trợ chức năng Chunk( bạn có thể đọc thêm
Tuy nhiên được 1 quãng thì anh Dev Ops bên mình lại claim rằng DB thường xuyên bị cảnh báo về Đọc database liên tục 5-10p. Ối. Thế là mình lại lọ mọ ngồi xem sao thì hóa ra là thằng Chunk nó giảm việc cao tải bằng cách tách ra thành các câu lệnh limit/offset. Ví dụ với 400k records như trên, với chunk(1000) thì mình sẽ gọi 400 câu lệnh vào DB 1 lần. và mỗi lần gọi như vậy DB sẽ phải loop qua các records để lấy theo limit, offset đã chọn => bảo sao bị quá tải về Đọc ổ cứn
Lại phải mày mò 1 lúc, thử tìm cách đọc ghi stream thẳng từ DB về ( thay vì vừa đọc vừa xử lý) nhưng mãi không được. Khoai. Search search thì mình mò ra trên docs của PHP nó có ghi thế này:
Queries are using the buffered mode by default. This means that query results are immediately transferred from the MySQL Server to PHP and then are kept in the memory of the PHP process. This allows additional operations like counting the number of rows, and moving (seeking) the current result pointer. It also allows issuing further queries on the same connection while working on the result set. The downside of the buffered mode is that larger result sets might require quite a lot memory. The memory will be kept occupied till all references to the result set are unset or the result set was explicitly freed, which will automatically happen during request end the latest. The terminology “store result” is also used for buffered mode, as the whole result set is stored at once.
Đại khái là ông PHP mặc định lấy 1 cục dữ liệu về lưu trên mem & xử lý tiếp. Oh shiet. Thế là mình phải code ngay 1 đoạn PHP thuần xử lý mấy việc sau
Query dữ liệu về và sử dụng hàm fetch_assoc để lấy từng bản ghi và xử lý thôi. Đọc tới đâu xử lý tới đó => với mức 400k records này thì đọc DB cũng ảnh hưởng 1 chút nhé các bác. nhưng sẽ đỡ hơn rất nhiều so với Chunk
=> note là không nên lưu dữ liệu ra biến dạng mảng khi làm việc với đống records này vì không thì sẽ lại lưu trên mem thì coi như công cốc
$query = " ... ";
$mysqli->set_charset('utf8'); // trong truong hop text co tieng viet nhe
$uresult = $mysqli->query($query, MYSQLI_USE_RESULT);
if ($uresult) {
while ($result = $uresult->fetch_assoc()) {
// xu ly du lieu tai day
}
}
Sau khi update sang dạng read mysql unbuffered thì mình đã giải quyết được kha khá các vấn đề về mem, cpu, đọc dữ liệu DB.
Tạm thời đến đây đang chạy ngon và chưa có vấn đề gì tiếp :)) cảm ơn ae đã đọc tới đây. Bài viết có gì sai sót mong ae bỏ qua và góp ý cho mình. Many thanks <3
Phần 3: Update
Sau khi tham khảo thêm các bác trong group Laravel Việt Nam e thấy thêm 1 số cách suggest khá hay là dùng Larvel Cursor ( thằng này giống con trỏ) & Lazy Collection. Mọi người có thể thử thêm nhé. Em chưa test kỹ hết được các case :))
