Главная » Хабрахабр » [Из песочницы] PHP, YII2 и формирование больших excel-файлов

[Из песочницы] PHP, YII2 и формирование больших excel-файлов

Начало

Одна поддерживаемая нашей компанией учетно-отчетная система начала очень быстро разрастаться в количестве хранимых данных. Система написана на PHP с использованием фреймворка Yii2. Изначально отчеты строились через библиотеку PhpSpreadsheet, которая пришла на смену, уже давно ставшему deprecated, PhpExcel.

На начальном этапе проблем не возникало, но когда объем стал превышать многие сотни тысяч записей, то скрипт формирования выгрузки стал отваливаться в timeout limit. Среди разного вида отчетности был один очень крупный – фактически полный набор всех хранящихся в БД данных должен выгружаться в одну excel-таблицу. Но врЕменного решения хватило ненадолго – проблема с лимитом времени превратилась в проблему с лимитом памяти. Для начала повысили этот самый лимит и начали искать пути решения проблемы. Очень скоро пользователи снова начали жаловаться на ошибки по времени выполнения. Серверу накинули «оперативки» и вообще сняли memory_limit для данной отдельно взятой операции. Но сидеть и смотреть десяток минут на экран с индикатором загрузки – мало удовольствия. Пришлось убрать и временной лимит для полного отчета. Эксперименты с настройками окружения прекратили, почесали затылок и приступили к оптимизации кода. К тому же иногда отчет нужен был «здесь и сейчас», и каждая потраченная минута на его формирование оказывалась критичной.

Поиск решения

Первое, что было сделано – скрипт отчетности вынесен в фоновый процесс, а пользователь наблюдает за ходом через «прогрессбар». Фоновое выполнение заданий реализовали через механизм очередей с использованием Redis для хранения. Работа в системе не останавливается, можно заниматься другими задачами и периодически возвращаться на страницу с отчетом – посмотреть, а не готов ли файл. Как только файл формируется, пользователю предлагается ссылка на скачивание. Но, как уже упоминалось выше, иногда файл требовался «немедленно», а повышение юзабилити никак не решало эту проблему. Тем временем количество данных продолжало расти и время построения файла дошло до 79 минут! Это совершенно не приемлемо, особенно учитывая, что отчетность — одна из основ функционала данной системы. Нет, все остальные части работали как часы, но эта ложка дегтя портила общее впечатление.

Первые результаты

Снова сели за анализ кода. Первое, что было протестировано – процесс выбора данных из БД. Но запросы уже были оптимизированы максимально возможным способом. Хоть самый долгий запрос и представлял собой страшную выборку с пятью-шестью обращениями к монструозному ФИАСу, но отрабатывал за 2-5 секунд. Слабым местом был не он, а формирование файла-«эксельника». Начались попытки оптимизации этого процесса. Начиная от кеширования в redis, до извращений вроде формирования отдельных небольших «эксельников» в параллельных потоках с последующим склеиванием в один файл. Но результат был всегда один: проблема со временем превращалась в проблему с памятью и наоборот. Золотой середины не было, только перетекание из крайности в крайность. После определенного количества данных потребление ресурсов библиотекой начинало расти экспоненциально и победить это не представлялось возможным. PhpSpreadsheet – не подходит для больших файлов. В итоге было принято решение сменить библиотеку. Как вариант – написание своего аналога для формирования эксель-файлов.

Анализ и выбор инструмента

Спешить с написанием велосипедов не стали, а для начала провели аналитику существующих решений. Из возможных вариантов заинтересовал только box/spout. Быстро переписали модуль с использованием этой библиотеки. В итоге, полный отчет получился за 145 секунд. Напомню, что последние тесты с PhpSpreadsheet — 79 минут, а тут 2,5 минуты! Провели тестирование: увеличили объем данных в 2 раза. Отчет сформировался за 172 секунды. Разница потрясающая. Конечно, библиотека не обладает всеми теми же функциями, что и PhpSpreadsheet, но в данном случае хватает и минимального набора инструментов, так как критичным является скорость работы.

Расширение для Yii2

Итоговое решение оформили в виде расширения для Yii2. Может быть, кому-то пригодится. Расширение позволяет выгрузить любой набор данных из GridView в excel с сохранением фильтрации и сортировки. В качестве зависимостей использует yii/queue и box/spout. Применять расширение имеет смысл для формирования действительно больших файлов, ну, хотя бы 50 000 строк =) В данный момент модуль, ставший основой для расширения, лихо справляется с нагрузкой почти в 600 000 строк.

Ссылка на github: Yii2 ExcelReport Extension

Спасибо за внимание!


Оставить комментарий

Ваш email нигде не будет показан
Обязательные для заполнения поля помечены *

*

x

Ещё Hi-Tech Интересное!

[Перевод] Китайская панель биомаркеров старения

Молекулярные и фенотипические биомаркеры старения. Введение. Для чего нужны биомаркеры старения? И этот процесс напрямую связан с молекулярными изменениями. Старение представляет из себя зависящий от времени физиологический функциональный спад, который поражает большинство живых организмов. С одной стороны, выявление биомаркеров старения ...

Как работает stack trace на ARM

Добрый день! Несколько дней назад столкнулся с небольшой проблемой в нашем проекте — в обработчике прерывания gdb неправильно выводил stack trace для Cortex-M. Поэтому в очередной раз полез выяснять, а какими способами можно получать stack trace для ARM? Какие флаги ...