Начало
Одна поддерживаемая нашей компанией учетно-отчетная система начала очень быстро разрастаться в количестве хранимых данных. Система написана на PHP с использованием фреймворка Yii2. Изначально отчеты строились через библиотеку PhpSpreadsheet, которая пришла на смену, уже давно ставшему deprecated, PhpExcel.
Среди разного вида отчетности был один очень крупный – фактически полный набор всех хранящихся в БД данных должен выгружаться в одну excel-таблицу. На начальном этапе проблем не возникало, но когда объем стал превышать многие сотни тысяч записей, то скрипт формирования выгрузки стал отваливаться в timeout limit. Для начала повысили этот самый лимит и начали искать пути решения проблемы. Но врЕменного решения хватило ненадолго – проблема с лимитом времени превратилась в проблему с лимитом памяти. Серверу накинули «оперативки» и вообще сняли 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
Спасибо за внимание!
Комментарии (34)
nvv
16.08.2018 18:07Zodiak_smr, пришлось ли отказаться от функционала исходной библиотеки или все необходимое затащили в свою?
Zodiak_smr Автор
16.08.2018 18:18К сожалению, отказаться полностью от PhpSpreadsheet не удалось. Например, в некоторых отчетах требуется сохранить html-разметку (цвет текта, размер и т.д.) в формируемом эксельнике. В этих случаях и некоторых других оставили старую библиотеку. Свое решение применили точечно к крупным отчетам.
nvv
16.08.2018 20:07Форматирование корректно работает у вас? Текст, чтобы ведущие нули не терялись, не конвертировались в числа/деньги значения, количество нолей после запятой и др.
Zodiak_smr Автор
16.08.2018 20:50Форматирование — корректно с этим проблем нет. Это один из важных моментов, на который обращали внимание. Если бы можно было убрать форматирование, то время построения файла сократилось бы ~ 30%, проводили такие тесты.
wertex15
16.08.2018 19:12А какого рода данные постоянно нужны сотрудникам в виде отчетов? Да еще и в экселе… Намекните хотя бы)
Zodiak_smr Автор
16.08.2018 20:07Увы. Заказчик — гос структура, и данные весьма… специфичны. А у нас соглашение о неразглашении
alexhott
16.08.2018 19:25потом каждая операция в таком файле выпооняется несколько минут, или у юзеров машины хорошие
Zodiak_smr Автор
16.08.2018 20:09Во время тестирования объем данных искусственно увеличивали для оценки потенциала. Так при этих тестах несколько одновременно просто открытых файлов иногда подвешивали систему. Но заказчик пока не жалуется)
nvv
16.08.2018 20:10Для некоторых отчётов эффективно сделать xlsx, который тянет данные напрямую из базы (лучше из реплики) и строит срезы или сводные.
Zodiak_smr Автор
16.08.2018 20:58Держали в уме подобный вариант на случай, если не удастся достичь успеха внутри системы. Еще был резервный вариант с подключением к реплике Power BI и построение этого отчета оттуда. Но оказалось, что Power BI имеет ограничение: максимальное количество строк, которое можно экспортировать в XLSX-файл — 150 000
demimurych
16.08.2018 21:25+1А почему не банальный csv? который влет открывается тем же Excel?
Zodiak_smr Автор
17.08.2018 05:55Это было первое, что предлагали в качестве решения. Но заказчика вариант не устроил, хотели только xlsx на выходе. Переубедить не удалось. Пришлось «плакать, колоться, но продолжать жрать кактус»
Hellcore
17.08.2018 05:54А чем csv плох? Если конечно нет требований к заранее подготовленным формулам и не нужно цветами играть.
Используя очереди на редис, можно сделать сервис асинхронным и многопоточным, сохраняя результаты в промежуточные временные файлы, а потом просто пересобрать результатирующий файл в нужном порядке.
Данные можно лить в csv используя генераторы (yield) и поток с блокировкой напрямую в файл.
Проблема с совместимостью обычно решается двумя кнопками «скачать для windows (cp1251, ';')» и «скачать для unix (utf8, ',')
Csv насколько я знаю идеально открывается во всех существующих экселях, опендокументах, спредшитах и в чем угодно вообще.Zodiak_smr Автор
17.08.2018 05:58В отдельно взятом проблемном отчете никаких требований к формулам и цветам не было, csv строился очень быстро и без дополнительных манипуляций, но заказчик хотел только xlsx на выходе, никаких других вариантов.
nvv
17.08.2018 07:26CSV надо импортировать, что требует лишних действий пользователей, а часто ещё необходимо типы данных вручную указывать (текст и др.).
nikolayv81
17.08.2018 22:40Не открывается он идеально (из личного опыта) выше писал комментарий, если импортировать в эксель из csv, то нужно правильно задавать форматы полей, ну и самое главнре в csv нельзя залить поля текста с переводами строки.
serginhold
18.08.2018 09:33Все там можно в csv с переводом строки, в двойные кавычки многострочный текст и экранирование внутри
nikolayv81
18.08.2018 10:06Формально да на практике:
"
Эксель убивает ведующие нули и приводит типы даже тогда, когда значение указано в кавычках. Делать так не надо, это ошибка. Но чтобы обойти эту проблему экселя, можно сделать небольшой «хак» — значение начать со знака «равно», после чего поставить в кавычках то, что необходимо передать без изменения формата.
У экселя есть спецсимвол «равно», который в CSV рассматривается как идентификатор формулы. То есть, если в CSV встретится =2+3, он сложит два и три и результат впишет в ячейку. По стандарту он это делать не должен.
“
И
и ещё много чего, статья — https://m.habr.com/company/mailru/blog/129476/
К сожалению приходилось ломать голову как экспортировать и импортировать, реально если подразумевать импорт в excel то универсального варианта не зависящего от настроек у пользователя просто нет.
CrazyNiger
17.08.2018 10:48У нас в системе тоже был подобный опыт, когда клиенту требовались большие отчеты со сложно-форматированной шапкой и разметкой. Мы тоже вынесли это в фоновые задачи, которые обрабатывались не php, а nodejs-скриптом с библиотекой exceljs.
Но это померкло в сравнении с задачей от другого клиента, которому потребовались фото-отчеты оформленные в формате PowerPoint'a. Вот тут пришлось подумать, но в итоге сделали по томуже принципу: nodejs с библиотекой officegen успешно смог осилить формирование pptx-файлов на 2-5 Gb.
tushev
17.08.2018 14:38Сталкивался с подобной задачей. Формирование больших экселей на PhpExcel приводило к кошмарному расходу RAM и занимало часы времени. Я изучил известные на то время альтернативы, и в итоге был вынужден создать свой велосипед.
Для этого был изучен формат .xlsx. Оказалось что это обычный архив с кучей xml файлов (данные, стили, настройки...), картинок и прочих вспомогательных файлов. Используя эти файлы как примеры, был создан собственный велосипед с набором необходимых функций, которые работал в сотни (!) раз быстрее и практически не расходовал память. Объем потребляемой памяти не зависил от объема данных.
Правильным выбором оказалось формирование xml файлов без всяких библиотек и готовых решений, а просто путем дозаписи строчек в файл. Именно это позволило не расходовать RAM как большинство других решений и не хранить сложные структуры данных в памяти. После формирования всех xml файлов и добавления вспомогательных статических файлов и изображений, они архивировались и получался готовый .xlsx файл.springimport
17.08.2018 16:40Ничего удивительного. Вы создали частное решение которые быстрее универсального. Сам тоже так делал когда-то с docx, там тоже xml в архиве :)
Совсем другое дело doc…tushev
17.08.2018 22:35В принципе получилось не так уж и специализированно. Там было и форматирование и формулы. Но было одно важное требование — листы и ряды можно было создавать только последовательно. Собственно за счет этого и удалось добиться огромной скорости и экономии ресурсов.
Fragster
17.08.2018 15:31Помню, когда делали выгрузку XML для яндекс маркета на стандартном шаред хостинге (ограничение 30 секунд и 32 мегабайта памяти) каталога с 15к номенклатуры, просто делали кусками по 500 номенклатур, а потом склеивали. Учитывая, что xlsx — это зип архив с XML, тут можно было бы применить тот же подход.
PQR
Интересно, что потом пользователи делают с отчётом из 600К строк? Глазами, очевидно, все тысячи строк не смотрят…
Singrana
Скорее, они могут использовать этот же эксель для импорта в другую софтину. Хотя, выгрузить тот же прайс с 100к позиций, которые разложены по вкладкам — тоже возможный сценарий :)
stalevar1990
Действительно. Скорее всего анализируется какой-то срез данных из всех этих 600К строк. Может нужно было посмотреть в строну формирования не полностью отчета, а именно тех данных которые нужны.
Zodiak_smr Автор
Передают людям, которые не имеют доступа в систему. Система располагается в закрытой корпоративной сети, а часть пользователей — удаленно, и они могут не иметь доступа. Кто-то ходит через vipnet, а кто-то получает файлы в 600К строк
polearnik
а экспортировать в csv а потом конвертнуть какойто консольной утилиткой в эксель файл?
Gudson
Я думаю, если бы документ состоял чисто из таблицы с данными — то скорее всего просто использовали бы CSV формат.А так — данные в документе дополнительно можно форматировать (шрифты, цвета, фильтры, дропдауны), да и разбиение по листам — тоже нередко нужная вещь для тех, кто потом работает с excel.
nikolayv81
Из личного опыта — найти утилиту правильно конвертирующую таблицу в соиню полей и пару сотен тысяч строк — не так просто, это ещё не учитывая того что в поле название организации вполне могут быть любые символы в т.ч. перенос строки во всех трёх известных вариантах.
p.s. а у Oracle ещё number(38 цифр) — один из наиболее распоспостранённых типов, да и номер счёта (20 цифр) excel очень любит сохранить как число (с округлением ;)).
nikolayv81
"Пучеглазят"
Но по факту обычно фильтры/формулы/суммы/сводные таблицы/сравнения с другими данными/системы построенные на ссылках на другие файлы (это то что видел лично).
Причём если вам покажется что эти процессы легко автоматизировать — это не совсем так, т.к. процесс может слишком часто меняться ввиду внешних причин/часто присутствует "экпертное мнение"/банально для многих ручных вещей может не существовать более удобного и гибкого инструмента чем эксель :)