분명 간단한 일이었다
지난 3월 2일, 출근 3일째 되는 날에 회의실에 불려갔다. 이제 막 출근을 시작한 0년차 꼬꼬마 PHP 개발자로서는 보통 떨리는 일이 아니었는데, 사수도, 일 시키는 기획자도, 업무 관련자로서 참가하는 DB팀 팀장도 전혀 긴장의 내색이 없었다. 그럴 수밖에. 너무나 간단한 일이었으니까.
지금 우리 회사 각종 지표 통계를 DB에 적재해 놓은 게 있어요. 이걸 지금 있는 CMS 화면에서 띄워주려고 해요. 그냥 DB 테이블 보여주고, 다운받게 해주는 게 다에요. 할 수 있겠지요?
당연히 할 수 있다. 애초에 CMS라는 게 DB 보여주는 툴이다. 자신만만하게 시작을 했고, 사용 중인 October CMS의 기본을 익히며 기존 코드들의 컨벤션을 따르며 테이블 만들고 다운로드를 구현했었다. AJAX로.
이하의 기록은 2019년 오늘에 와서야 드디어 이 건에 기술적 종결을 지은 사실을 기념하고자 남겨두는, 대용량 DB 데이터 출력에 관한 온갖 삽질의 이력이다.
1차 시도: AJAX
기본 아이디어는 간단하다. “SELECT * FROM 어쩌구” 쳐서 나오는 모든 결과를 “어쩌구.csv”에 저장해 내리면 된다. 하지만 테이블에 너무 많은 자료가 있으면 한번에 “SELECT *” 걸면 안 되고, limit과 offset을 적절히 활용해서 결과들을 “토막”(chunk)낸 다음 순차적으로 가져와야 한다.
1단계: 파일 경로와 파일명을 확정짓는다
2단계: 1단계에서 확정된 경로에 확정된 파일명으로 빈 파일을 만든다
3단계: SELECT * FROM 어쩌구 LIMIT 1000 OFFSET (1000*n)을 실행해서 그 결과를 계속 2단계에 만든 파일에 꽂는다
4단계: 3단계가 끝나면 해당 파일 URL을 보여준다그리고 여기서 3단계와 4단계를 AJAX로 처리했다.
// 함수: CSV 최초 생성
var readyProgress = function (tableName, callback) {
$.request('onReadyProgress', {
data: {
tableName: tableName
},
success: function (data) {
callback(data);
},
error: function () {
alert('서버 오류! 나중에 다시 시도해 주세요.');
return false;
}
});
};
// 함수: CSV 추가 작업
var doProgress = function (progressData, callback) {
var totalPages = Math.ceil(progressData.total/progressData.chunk);
// onDoProgress 는 PHP 메소드. total, chunk, page, table 등을 넘기면 offset limit 을 걸어서 파일에 append한 뒤 progress 값을 업데이트한 배열을 돌려준다.
$.request('onDoProgress', {
data: {
table: progressData.table,
filename: progressData.filename,
file: progressData.file,
total: progressData.total,
chunk: progressData.chunk,
page: progressData.page,
},
success: function (returnData) {
showProgress(returnData);
if (returnData.page > totalPages) {
callback(returnData);
} else {
doProgress(returnData, callback);
}
},
error: function () {
doProgress(progressData);
}
});
};
// 함수: 팝업 내 진행바 조작
var showProgress = function (data) {
var currentProgress = data.progress;
var total = data.total;
modal.find('.progress-bar').css('width', currentProgress+'%');
modal.find('.progress-text').text(currentProgress+'% 완료');
};
// 함수: 팝업 내 다운로드 링크 출력
var shareProgress = function (data) {
modal.find('.download-dialogue').html('<p class="text-center"><a class="btn btn-lg btn-primary oc-icon-download" href="results/download/'+data.filename+'" target="_blank">'+data.filename+'.csv</a></p>');
modal.find('.modal-title').append(' 준비 완료!');
};
// 테이블명 받아와서
var theTable = button.data('tableName');
// 함수들 콜백
readyProgress(theTable, function(tableData){
doProgress(tableData, function(returnData){
shareProgress(returnData);
});
});
그렇다. 한 번의 chunk가 성공했는지를 JS 콜백이 한 번 받고, 성공했다고 받으면 그 다음 chunk를 JS 콜백이 요청 보내서 다시 그 chunk가 성공했는지를 체크한다. (그리고 서버가 돌려주는 progress 값을 가지고 진행바를 보여준다.) 이론상 문제될 것이 전혀 없다. 실제로도 커밋로그를 보면, 3월 19일에 이 소스로 실사용이 시작된 이후 5월 초까지는 이 소스가 별탈 없이 잘 사용이 되었다.
뭐가 문제였냐고?
- shareProgress() 함수가 콜 되어야만 사용자는 파일을 받을 수 있었으므로, 그때까지 사용자는 팝업 진행 바를 하염없이 바라보고 있어야 했다.
- doProgress()는 순전히 PHP 스크립트가 넘겨주는 배열의 page, progress 값 등에만 의존하고 있었다. 서버 장애 발생 같은 상황에서 이 방식은 반드시 문제를 일으킨다.
- 무엇보다, (10만 줄이 임계점이었던 걸로 기억하는데) 테이블 크기가 조금만 커져도 처리 속도가 exponentially하게 느려졌다. 사실 이 문제 때문에 별 삽질을 다 해보다가 결국 2차 시기를 시도해야 했다.
“아니 CSV면 무슨 동영상 인코딩도 아니고 그냥 텍스트 바이너리잖아 그거 내리는 게 뭐가 문제가 되는데?” 싶으실 텐데, 여기서는 해당 PHP 소스를 좀 봐야 한다.
// onDoProgress() 메소드 어딘가에서...
foreach ($rows as $row) {
$file = $download->appendCSV($file, $row);
}
// onDoProgress() 가 사용하는 헬퍼 클래스에서...
public function appendCSV($fileName, $rows, $method = 'a') {
$csv = fopen($fileName, $method);
if ($method == 'w') : fputs($csv, "\xEF\xBB\xBF"); endif;
fputcsv($csv, $rows);
fclose($csv);
return $fileName;
}
절로 이마를 탁! 치게 되지 않는가?
2차 시도: 커맨드 명령
위의 PHP 소스를 보고 아직 이마를 못 짚으신 분들을 위해 저기서 CSV 파일 다루는 방법이 잘못된 부분을 설명드리면…
- PHP 내장함수 fputcsv()는 사실 별로 많지 않은 자료들을 내릴 때만 써야 한다. 이 함수 자체가 하는 작업이 꽤 많다. (따옴표 묶기, 적당히 escape 처리하기, implode, 줄바꿈 넣기 등)
- 자료를 한 줄 한 줄 읽을 때마다 번번이 fopen()과 fclose()를 반복하면 안 된다. 리소스를 만들어 포인터를 위치시키는 일은 생각보다 시간과 비용이 많이 드는 작업이고 가급적 한꺼번에 처리해야 한다.
이 교훈을 얻어서 소스를 고쳤다.
// 헬퍼클래스에서...
public function appendFile($table, $fileName, $currentStep = 0, $totalSteps) {
$chunk = Model::getSomeModelsFromDB($this->database, $table, $this->limit*$currentStep, $this->limit);
// 일단 빈 문자열을 만들고
$chunkTxt = '';
// 이번 chunk에서 퍼온 자료들을 문자열에 이어붙인 다음
foreach ($chunk as $row) {
$chunkTxt .= implode(',', (array) $row);
$chunkTxt .= "\r\n";
}
// 한꺼번에 파일에 쓰기
$file = fopen($fileName, 'a');
$appended = fputs($file, $chunkTxt);
fclose($file);
return ($appended !== FALSE);
}
그리고 이 수정을 하는 와중에 새로 들어온 요구사항이 있었다.
한 달에 한 번씩 매달 26일 아침쯤에 A테이블이 싹 업데이트되거든요. 이게 진짜 좀 대용량인데 이걸 좀 미리 서버에서 파일 만들어서 떨궈놓으면 어떨까요? 출근해서 그냥 다운로드만 받을 수 있게
음… 그러면 어차피 라라벨 아티즌 콘솔 써서 시스템 커맨드를 돌려야 하니까… 안 그래도 다운로드 버튼이 맘대로 동작을 안 하는데 다 시스템 커맨드로 묶어 버리자!! 싶어서 다운로드 버튼 작동 방식을 수정했다. 좀 많이 바뀌긴 했지만 아무튼 기본 아이디어는 개 간단하다.
1단계. 버튼을 누르면 테이블 이름을 담아서 PHP에 요청을 보낸다.
2단계. PHP는 시스템 커맨드를 실행하고 쭉 일을 한 뒤 결과를 반환한다.
3단계. 브라우저는 그 반환받은 결과를 가지고 사용자에게 메시지를 띄운다.1~2단계에 해당하는 PHP 처리 메소드 역시 당연히 super straightforward해진다. 그냥 커맨드를 하나 실행하고 그 결과를 리턴할 뿐이다.
public function onRequestDump() {
ini_set('max_execution_time', 600); // 10분
try {
Artisan::call('stat:dump', [
'table' => input('tableName'),
'--extension' => 'csv',
]);
Flash::success(e(trans(':tableTitle 추출 완료. "대용량" 메뉴에서 찾아가세요.', ['tableTitle' => input('tableTitle')])));
} catch (Exception $e) {
Flash::error($e->getMessage());
}
}
하지만 아까 이마를 탁! 치셨던 분이라면 이번에도 다시 이마를 딱! 치실 수밖에 없을 것.
- PHP 스크립트 실행 시간을 10분으로 늘려 봐야 소용없다. 웹서버(NGINX 등)의 응답 제한시간이 30초일 경우 이 요청은 30초만에 연결 시간 초과로 연결이 끊긴다.
- 따라서, 전체 파일 생성에 30초 이상이 소요될 것이 분명한 tableName을 요청 넣었을 경우, 웹서버에 의해 408이 떨어지므로, 사용자는 실패 메시지도, 성공 메시지도 보지 못한다.
이 문제를 어떻게 해결했느냐고? 위 소스에서 파일 생성 완료를 알리는 Flash::success() 라인을 지워버리고(그러니까… onRequestDump() 메소드는 더 이상 아무것도 반환하지 않는다!!), 커맨드가 CSV 파일을 떨구는 폴더 내용을 볼 수 있는 ‘대용량’ 메뉴를 만든 다음… 다운로드 버튼을 누를 때마다 이런 메시지를 띄워 버렸다.
서버에 ":tableTitle" DB 추출을 요청합니다. 추출된 파일은 "대용량" 메뉴에서 받아가실 수 있습니다.
서버가 별도로 추출 과정을 알려드리지는 않으므로, 파일을 받기 전에는 해당 파일의 크기가 더 이상 변경되지 않는지 확인 후 받으시기 바랍니다. 여러 사람이 같은 자료를 동시에 요청하지 않도록 주의하세요.
그러면 불쌍한 우리 회사 통계자료 열람자들은… 자기가 누르려는 다운로드 버튼을 먼저 누른 사람이 없는지 주변에 물어본 다음에… 그 버튼을 누르고… 대용량 메뉴에 가서 파일 크기가 커지고 있는 동안(= 커맨드 잡이 파일을 만드는 동안)은 기다리고 있다가… 파일이 더 이상 커지지 않으면 그제서야 그걸 다운받았다!!! ㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋ
이게 무려 지난 7월 중순의 일인데, 오늘 할일이 없어서 문득 생각해 보자니 그때 이후로 별다른 업데이트가 없었던고로, 세상에, 이날 이때까지 이분들이 이걸 이대로 계속 쓰고 계실 것 같더란 말이지… ㅎㅎㅎ;;;;
그래서 드디어 streamed response 방식으로 변경했다. 정말 먼 길을 돌아온 셈이다.
마지막 시도: StreamedResponse
원래는 지난 11월쯤에 또 잠깐 할일이 없어서 ‘PHP로는 스트리밍을 어떻게 구현하지?’ 궁금해서 테스트 코드를 만들어 연습하다가 알게 된 것이었다. 라라벨에서 어떤 바이너리를 스트리밍 응답으로 내리려면, Symfony에 포함된 StreamedResponse 클래스를 쓰면 된다.
사실 라라벨 5.6 이상에는 streamDownload()라는 메소드가 주어져 있다. 파일 내용이 될 바이너리를 출력하는 콜백과 파일명(아마도 그리고 커스텀 헤더)을 지정해서 콜하면 된다.
return response()->streamDownload(function () {
echo GitHub::api('repo')->contents()->readme('laravel', 'laravel')['contents'];
}, 'laravel-readme.md');
하지만 과연 이걸 October CMS에 주어진 response() 헬퍼로도 쓸 수 있을까? 그걸 확인하고 구현하는 과정도 이제는 싫어서 그냥 이미 누군가가 이루어 놓은 성취를 적용해 코드를 고쳤다. 사실 저 블로그 글을 예전에도 보기는 했었다. 그땐 도통 뭐가 뭔지 이해가 안 돼서 덮어놨는데, 스트림 응답 자체를 한번 경험해 보니 이젠 그래도 짬 좀 먹었다고 이해가 되는 것이다.
use Symfony\Component\HttpFoundation\StreamedResponse;
public function download($tableName) {
ini_set('max_execution_time', 600); // 10분
return response()->stream(function () use ($tableName) {
// 최초 파일 생성은 일단 BOM부터 넣고 본다.
$csv = fopen('php://output', 'w');
fputs($csv, "\xEF\xBB\xBF");
// CSV의 첫번째 행은 컬럼명을 구해서 채우고 한 줄 띄운다.
$columns = ResultsModel::getColumnsFromDB($this->connection, $tableName);
fputs($csv, implode(',', $columns)."\r\n");
// 맨 처음의 chunk를 퍼온다.
$skip = 0;
$chunkRows = ResultsModel::getSomeModelsFromDB($this->connection, $tableName, $skip, 1000);
// 과감하게 무한루프를 돈다.
while ($chunkRows->count() > 0) {
foreach ($chunkRows as $row) fputs($csv, implode(',', (array) $row)."\r\n");
$skip += 1000;
$chunkRows = ResultsModel::getSomeModelsFromDB($this->connection, $tableName, $skip, 1000);
}
// 다 됐으면 파일 핸들을 끄고 반환한다.
fclose($csv);
}, 200, [
// 무조건 다운로드가 되어야 하므로 헤더를 설정해 준다.
'Content-Type' => 'text/csv',
'Content-Disposition' => 'attachment;filename='.$tableName.'_'.date('ymdhi').'.csv',
]);
}
이 방법은 상기 모든 과정이 겪었던 고질적인 문제들을 그냥 다 해결해 버린다.
- 쿼리 효율의 문제. 불행히도 다운받아야 하는 테이블들 대다수가 PK가 없어 라라벨 ORM의 chunk() 메소드를 쓸 수 없다. 하지만 그러거나 말거나 old fashioned하게 limit-offset만 적절히 지정해 줘도, 충분히 원하는 것을 달성하면서, 심지어 메모리도 안 잡아먹는다.
- 파일 생성 효율의 문제. 지금까지는 물리적 파일을 만든 다음 그걸 계속해서 열고 닫고를 반복했고 이건 필연적으로 파일 크기가 커질 때 메모리 초과를 일으킨다. 하지만 php://output에 결과를 넣어 HTTP 응답으로 바로 내려 버리면, 용량이니 메모리니 하는 것은 순전히 그 응답을 받는 클라이언트의 문제가 된다. (그리고 그게 자연스럽다.)
- 응답 시간의 문제. download() 메소드는 새 창에서 열리는 새 요청이고 이 요청의 Content-Disposition 헤더가 attachment를 갖고 있으므로 이 요청은 웹서버 응답 시간과 상관없이 전체 파일이 다 내려오기를 꼼짝없이 기다리게 된다. (심지어 브라우저에 의해 다운로드가 처리된다.)
- 안정성과 사용성. 이제 사용자들은 그냥 평범하게 csv 파일을 다운받으면 된다. 그동안 보았던 이상한 팝업이나, “대용량 메뉴로 이동하여 뭘 기다렸다가” 어쩌구 하는 안내를 받을 필요가 없다. 무엇보다, 서버의 임의 위치에 파일들을 쓸데없이 짱박아둘 필요가 없어졌다.
맨 위의 쿼리 효율에 있어서 하나만 더 적고 넘어가자면… 원래는 파일 append를 반복하기 위해 while 루프를 쓰지 않았다. 무한루프에 대한 경험 부족도 있었고, 왠지 무한루프는 무조건 무섭다는 느낌이었다. 그래서 어떻게 했었느냐고? 공연히 복잡하게 재귀를 시켰다.
public function outputFile($table, $fileName, $currentStep, $totalSteps, $rows) {
// 파일 열고 쓰고 닫는 처리
$appended = $this->appendFile($table, $fileName, $currentStep, $totalSteps);
if (!$appended) {
// 중간 실패 결과 반환 로직
} else {
if ($totalSteps > $currentStep) {
$currentStep++;
$this->outputFile($table, $fileName, $currentStep, $totalSteps, $rows);
} else {
// 전체 완료 결과 반환 로직
}
}
}
정신없다. (그리고 행여나 $appended 변수가 적절히 메모리에서 비워지지 않거나 지나치게 많이 쌓이는 순간 100% 뻗을 것이다.) 이제는 안 쓰는 이 메소드를 작성한 보람이 그나마 있다면, 재귀함수를 돌릴 때 메모리가 터지지 않도록 인자를 (안) 넘기는 요령을 알게 된 정도?
간단한 일이지만 간단하지 않았다
알고 보니 생각보다 challenges들이 많은 일이었다. 족히 300만 줄이 넘어가는 DB 테이블/뷰가 있고, 이 테이블/뷰에는 PK가 걸려 있지 않아 chunk를 돌리기가 어려우며, 그렇다고 해서 일반 사용자들이 그 전체 내용을 CSV 파일로 다운로드 받는 데 별다른 어려움이 있어서는 안 된다는 것까지도 꽤 골치 아픈 문제였다.
이 태스크 하나 처리하면서 chunk를 수행하는 법을 최소 세 가지는 봤던 것 같다. 하나는 while, 다른 하나는 성공 시 재귀, 다른 하나는 전체 steps 수와 지금 처리할 step 수를 번번이 갱신해 가며 전체 steps 수만큼 호출하기. Eloquent의 chunk() 메소드를 처음 까봤을 때는 그게 세 번째 방법인 줄 알았는데, 지금 다시 생각해 보니 그건 while이었다.
지금은 불행인지 다행인지 시스템 크론잡으로 매달 26일 아침에 데이터를 내려야 하는 일은 없어졌다. 그래서 커맨드를 쓸 필요가 없어졌고, 그래서 다운로드 버튼은 순전히 웹 응답으로 처리할 수 있었다. 아무에게도 보고하지 않고 CMS를 업데이트했다. 아마 아무도 몰라주겠지. 다만 ‘어 이젠 엄청 금방 편하게 잘 되네’ 정도로 생각하고 넘어갈 것이다.
하긴 뭐 몰라주면 또 어떤가? 필요한 레벨업 잘 해서 다음 스텝으로 넘어가면 되지. 인생이란 이름의 무한루프도 while true가 가장 속 편할 테니까.
PS. 그렇다고 이거 하나에 장장 10개월을 버렸다는 건 아니고… 다른 일 하면서 띄엄띄엄 개선하다 보니 이 태스크 자체는 진척이 이렇게 됐다. 사실은 아무도 관심 없으면서 겉으로는 모두가 다 관심을 주는 듯한 그런 일을 하고 있다. PHP가 그렇지 뭐.
