วิธีกัน memory หมดตอน export Excel ไฟล์ใหญ่จาก Laravel

ปุ่ม export ที่ใช้ได้ดีมาหลายเดือนมักมาล่มตอนสิ้นเดือนหรือสิ้นปี วันที่ผู้ใช้เลือกช่วงข้อมูลยาวที่สุด แล้วได้หน้า error หรือ log แบบนี้

Allowed memory size of 2147483648 bytes exhausted (tried to allocate 20480 bytes)

ที่ผ่านมาเราก็มักแก้ด้วยการเพิ่ม memory_limit ขึ้นไปอีก ซึ่งได้ผลจนกว่าข้อมูลจะโตอีกรอบ ทางที่ยั่งยืนกว่าคือเข้าใจว่า memory ไปอยู่ที่ไหน แล้วคำนวณได้ตั้งแต่ก่อนเขียนโค้ด

PhpSpreadsheet เก็บทุกช่องไว้ใน memory จนกว่าจะบันทึก#

PhpSpreadsheet สร้างไฟล์ทั้งไฟล์เป็น object ใน memory ทุกช่องมีค่า ชนิดข้อมูล และสไตล์ของตัวเอง แล้วค่อยเขียนเป็นไฟล์ .xlsx ตอนจบ เอกสารของ PhpSpreadsheet ประมาณไว้ราว 1 KB ต่อช่อง หรือ 1.6 KB บน PHP 64 บิต

ตัวเลขที่ควรดูจึงเป็นจำนวนช่อง คือจำนวนแถวคูณจำนวนคอลัมน์ ไม่ใช่จำนวนแถวอย่างเดียว

ไฟล์	จำนวนช่อง	memory ที่ใช้
5,000 แถว × 20 คอลัมน์	100,000	ราว 100–160 MB ตามตัวเลขในเอกสาร
100,000 แถว × 10 คอลัมน์	1,000,000	683 MB และ 11.9 วินาที (เราวัดเอง)
90,000 แถว × 42 คอลัมน์	3,800,000	เกิน 2 GB เขียนไม่เสร็จ

แถวที่สองมาจากที่เราลองเขียนไฟล์ข้อความไทยปนตัวเลขด้วย PhpSpreadsheet 5.10 บน PHP 8.4 หนึ่งรอบ ส่วนแถวสุดท้ายเป็นรายงานจริงตัวหนึ่ง ซึ่งเพิ่ม memory เท่าไหร่ก็ไม่จบสวย

chunk ช่วยแค่ตอนดึงข้อมูล ไม่ได้ทำให้ไฟล์เล็กลง#

Laravel Excel แบบ FromQuery ดึงข้อมูลทีละ 1,000 แถว (ค่า exports.chunk_size) ทำให้ไม่ต้องโหลด model ทั้งหมดพร้อมกัน แต่ทุกแถวที่ดึงมายังไปต่อท้ายชีทใน memory อยู่ดี ไฟล์ 3.8 ล้านช่องก็ยังเป็น 3.8 ล้านช่อง

มีสองแบบที่ดูเหมือน chunk แต่ไม่ได้ผล

• ->get()->chunk(100) get() โหลดทุกแถวขึ้นมาก่อนแล้ว chunk() แค่แบ่ง collection ที่อยู่ใน memory ครบแล้วออกเป็นกลุ่ม ถ้าจะดึงทีละช่วงจริงต้องใช้ ->chunk(), ->lazy() หรือ ->cursor() บน query
• WithChunkReading บน export concern ตัวนี้เป็นของ import ฝั่ง export Laravel Excel อ่านแค่ WithCustomChunkSize เมธอด chunkSize() ที่เขียนไว้จึงไม่มีผล

// เดิม ใส่ concern ของ import ไว้ chunkSize() ไม่มีผล ใช้ค่าเริ่มต้น 1000
class OrdersSheet implements FromQuery, WithChunkReading

// ใช้ concern ของ export
class OrdersSheet implements FromQuery, WithCustomChunkSize

ถ้าส่ง export เข้า queue Laravel Excel จะแบ่งเป็น job ละ chunk แต่ละ job ต้องเปิดไฟล์ชั่วคราวที่เขียนไว้ขึ้นมาใหม่ทั้งไฟล์ก่อนต่อท้าย job หลัง ๆ จึงช้าลงและกิน memory มากขึ้นตามขนาดไฟล์ queue ช่วยให้ผู้ใช้ไม่ต้องรอหน้าเว็บ แต่ไม่ได้แก้เรื่อง memory

FromView สร้างตาราง HTML ขึ้นมาก่อนอีกชั้น#

FromView สะดวกตรงที่ใช้ Blade เดียวกับหน้าเว็บหรือ PDF ได้ แต่ Laravel Excel ต้อง render HTML ทั้งตารางเป็น string ก่อน แล้วให้ PhpSpreadsheet อ่าน HTML นั้นกลับเป็นชีทอีกรอบ memory จึงต้องรับทั้งก้อน HTML และตัวชีทพร้อมกัน ในระบบสินเชื่อที่เราดูแล รายงานสัญญาราว 10,000 สัญญากับงวดชำระ 89,000 งวดหมด memory ที่ขั้นนี้

Streaming writer เขียนทีละแถวแล้วปล่อยแถวนั้นไป#

writer แบบ streaming ไม่เก็บไฟล์ทั้งไฟล์ไว้ เขียนแถวลงไฟล์แล้วปล่อยแถวนั้นออกจาก memory ทันที memory จึงแทบคงที่ไม่ว่าไฟล์จะยาวแค่ไหน ตัวเลือกที่ใช้ใน Laravel ได้

• OpenSpout เขียน .xlsx ได้ ทั้งสไตล์ รูปแบบตัวเลข merge และตรึงแถว (วิธีใช้อยู่ในโพสต์นี้)
• league/csv สำหรับ CSV ซึ่งเร็วที่สุด แต่ไม่มีสไตล์ใด ๆ
• fast-excel และ spatie/simple-excel ที่ห่อ OpenSpout ไว้ให้ใช้ง่ายขึ้น

ทุกตัวต้องป้อนข้อมูลด้วย ->lazy(), ->cursor() หรือ generator ไม่อย่างนั้น memory ก็ไปหมดที่ collection แทน ผลจากการวัดหนึ่งรอบของเรา ไฟล์ 100,000 แถว × 10 คอลัมน์

เขียนด้วย	เวลา	memory สูงสุด
league/csv	0.27 วินาที	2 MB
OpenSpout 5.13	2.5 วินาที	2 MB
PhpSpreadsheet 5.10	11.9 วินาที	683 MB

ถ้ายังต้องการความสามารถที่มีแต่ PhpSpreadsheet เช่น conditional formatting หรือ chart ทางเลือกคือย้ายที่เก็บช่องไปไว้ใน cache ผ่าน Settings::setCache() (Laravel Excel ตั้งได้ที่ cache.driver ใน config/excel.php) memory ลดลงแลกกับความเร็ว และ cache ที่ใช้ต้องไม่ทิ้งข้อมูลกลางทาง

เลือกวิธีตามจำนวนช่อง#

จำนวนช่อง (แถว × คอลัมน์)	วิธีที่เหมาะ
ไม่ถึง 100,000	Laravel Excel หรือ PhpSpreadsheet ตามปกติ ดาวน์โหลดทันทีได้
100,000 ถึงราว 1,000,000	ยังใช้ PhpSpreadsheet ได้ แต่ส่งเข้า queue และตั้ง memory ของ job ไว้ราว 1 GB
เกินราว 1,000,000 หรือ memory สูงสุดเกิน 1 GB	เขียนแบบ streaming ด้วย OpenSpout หรือ CSV

ตัวเลขนี้เป็นจุดเริ่มต้น สไตล์ที่ตั้งเยอะ สูตร และข้อความยาวทำให้แต่ละช่องกินมากขึ้น วิธีที่แม่นกว่าคือลองกับข้อมูลช่วงที่ยาวที่สุดที่ผู้ใช้เลือกได้ แล้วดู memory_get_peak_usage()

ตั้ง memory_limit ให้แต่ละ job แทนการตั้ง -1#

การตั้ง memory_limit เป็น -1 ดูเหมือนแก้ปัญหาจบ แต่ job ที่หลุดจะกิน RAM ได้จนเครื่องหมด แล้ว OOM killer ของระบบปฏิบัติการจะฆ่า process ทิ้ง PHP ไม่มีโอกาสเขียน log ไม่มี exception และ failed() ของ job ไม่ทำงาน สถานะในระบบค้างอยู่ที่ "กำลังทำ" โดยไม่มีใครรู้ว่าเกิดอะไรขึ้น

ตั้งเพดานให้ job ที่สร้างรายงานแต่ละตัวแทน PHP จะหยุดเองพร้อม error ที่บอกชัดว่า memory หมด ไปขึ้นใน log หรือ error tracker ได้

public function handle(): void
{
    ini_set('memory_limit', '1024M');

    // สร้างไฟล์
}

รายงานไหนต้องใช้มากกว่าก็เพิ่มเฉพาะตัวนั้น ส่วนการส่ง export เข้า queue แล้วแจ้งผู้ใช้เมื่อไฟล์เสร็จอยู่ในโพสต์เรื่องส่งไฟล์ให้ผู้ใช้

เวลามักหมดไปกับการจัดสไตล์มากกว่า query#

เมื่อรายงานช้า เรามักไปดู query ก่อน แต่ในไฟล์ใหญ่ เวลาส่วนใหญ่อาจอยู่ที่โค้ดจัดหน้าตา ลูปที่ตั้งความสูงทีละแถวในรายงาน 45,000 แถวใช้เวลาราวหนึ่งนาทีอย่างเดียว การตีเส้นขอบทุกช่องและแปลงวันที่ทีละช่องหลังเขียนชีทเสร็จก็เป็นแบบเดียวกัน ตั้งเป็นช่วงหรือเป็นค่าเริ่มต้นของชีททีเดียวแทน (ตัวอย่างอยู่ในโพสต์เรื่องจัดหน้าตารายงาน)

Excel รับได้ไม่เกิน 1,048,576 แถวต่อชีท#

ถึง memory จะพอ Excel ก็มีเพดาน 1,048,576 แถวและ 16,384 คอลัมน์ต่อชีท OpenSpout ขึ้นชีทใหม่ให้เองเมื่อถึงเพดาน แต่รายงานล้านแถวก็ไม่มีใครอ่านจบอยู่ดี ถ้าข้อมูลใหญ่ขนาดนั้น CSV หรือการให้ผู้ใช้ดึงข้อมูลผ่าน API น่าจะเหมาะกว่า

อีกทางที่ได้ผลและทำง่ายคือจำกัดช่วงวันที่ที่ผู้ใช้เลือกได้ในฟอร์ม เช่นไม่เกินหนึ่งปีต่อครั้ง ผู้ใช้ที่ต้องการข้อมูลหลายปีก็ export ทีละปีได้ ระบบไม่ต้องเผื่อกรณีที่ใครเลือกย้อนหลังสิบปี

รายงานที่โตจนเกินวิธีเดิม#

ตอนที่เราทำระบบรายงานจาก SAP ที่ส่ง Excel ให้แต่ละฝ่ายตามเวลา รายงานส่วนใหญ่ยังเขียนด้วย Laravel Excel เพราะเขียนง่ายและจัดสไตล์ได้ครบ เราตั้งเกณฑ์ไว้ว่ารายงานที่เกินราว 10,000 แถว หรือวัดแล้ว memory ใกล้ 1 GB ให้ย้ายไปเขียนด้วย OpenSpout รายงานหนึ่งที่มีราว 34,000 แถว × 109 คอลัมน์ สองชีท เคยใช้ memory 2.6 GB และ 210 วินาที หลังย้ายเหลือราว 380 MB และ 38 วินาที

ส่วนในระบบสินเชื่อโฉนดที่ดิน นอกจากย้ายรายงานใหญ่ไปเขียนแบบ streaming แล้ว เราก็จำกัดรายงานสรุปกระแสเงินสดไว้ไม่เกินหนึ่งปีต่อครั้ง หลังจากมีคนเลือกช่วงย้อนหลังหลายปีจน memory หมด