Выгрузка отчётов: как лучше организовать хранение файлов?

Question

[Remmi]

Добрый день!

Сделал у себя на сайте PHP-скрипт генерации отчётов в Excel-формате (XLS), CSV и текстовом виде для программы-просмотрщика.

Скрипт запускается каждый день в 00:05 и создаёт отчёты за прошедший день для всех объектов в базе. Например, для 29 апреля получается что-то вроде:
объект-1/отчёт-2014-04-29.xls
объект-1/отчёт-2014-04-29.csv
объект-1/отчёт-2014-04-29.txt
объект-2/отчёт-2014-04-29.xls
объект-2/отчёт-2014-04-29.csv
объект-2/отчёт-2014-04-29.txt
объект-3/... и так далее

Проблема следующая. Каждый отчёт весит до 1.2 Мб (36000 строк), а с учётом того, что их делается три вида, общий вес получается порядка 3.6-3.8 Мб за день. Пространства на диске у меня всего 10 Гб, на соседнем разделе 20, но туда я делаю дампы БД, так что это не вариант :)

Какие варианты решения пришли в голову:
1) Хранить файлы только за последний месяц
Плюсы: ест меньше места
Минусы: история не сохраняется, и остаётся ограничение в количестве объектов: если их будет больше 100, то даже месяц не получится хранить
2) Грузить файлы на сторонний файлообменник (Яндекс.Диск, например) и работать через него
Плюсы: много места (на ЯДиске 200 Гб)
Минусы: непонятно, как это сделать
3) Генерировать отчёты на лету по запросу пользователя, хранить их один день и потом удалять все сгенерированные файлы
Плюсы: не требует много места и решает задачу
Минусы: долгая выгрузка по времени, работа с БД в любое время и возможные утечки памяти (для Excel использую либу PHPExcel)

Какой вариант лучше подойдёт? И как бороться с минусами подходящего варианта?

Answer 1

[Станислав]

А чем собственно данные в xls, csv & txt отличаются? Почему бы не генерировать по крону, например только csv, а остальное на лету?

Comments

[Remmi]

Отличаются форматом:
1. XLS - полноценный Excel5-файл, созданный через PHPExcel
2. CSV - стандартный файл с разделителем "," и кодировкой UTF-8
3. TXT - файл с табами и специфическими заголовками

Вариант с чтением из готового CSV хороший, надо будет производительность замерить на своём объёме - что быстрее выберется: 36000 строк из базы или из CSV. Но проблема свободного места не решается - его просто становится в три раза больше.)

[Vlad]

Ну так, а куда вы денетесь, если отчеты надо хранить в любом случае? Что в базе они у вас будут, что на диске в формате csv. В вашем случае такой вопрос целесобразно задать? У вас отчеты постоянно выгружают и действительно они всем нужны и всегда в таком объеме? Просто если вы в этом не уверены - я бы просто так файлы не складывал на диск, а генерировал по запросу.

[Remmi]

@DaFive проверил генерацию XLS на лету через PHPExcel: до 4 минут идёт формирование файла, этот вариант не подходит

[Remmi]

Теперь либо заранее генерировать (дальше сжимать или закачивать на ЯДиск), либо отказываться и отдавать фиктивный csv-файл с разделителями ";" и кодировкой под Excel.

[Vlad]

@Remmi пробовали искать альтернативные варианты библиотек (не PHPExcel) ? Да, и почему просто не брать csv с разделителями ";" ? Любой Excel их читает. даже по-умолчанию в винде открывает csv-файлы через себя.

[Remmi]

@DaFive тут уже заморочка заказчика, для него csv-формат "некошерен". Попробую ещё PHP Spreadsheet_WriteExcel и хардкод: habrahabr.ru/post/18726

[Arris]

@DaFive не любой. Непонятным образом иногда он не понимает кодировку, точнее решает, что файл идет в кодировке win1251 и не предлагает её выбирать. Приходится морочиться с импортом (на что например, мои заказчики интеллектуально неспособны).

[Remmi]

@Arris @DaFive отлично себя показал вариант с прямым выводом файла из поста habrahabr.ru/post/18726
С небольшим нюансом для русского текста в функции xlsWriteLabel:
> $Value = iconv('UTF-8', 'CP1251', $Value);
И всё генерируется быстро и как надо

Answer 2

[kfuntov]

Просто чтение csv быстрее выборки из базы.
+ можно гзиповать раз в неделю csv, которые старше месяца. Это сильно сократит размер.

Comments

[Remmi]

Попробовал - файл сжался до 75 килобайт! Если хранить только CSV и гзиповать их, то места получается более, чем достаточно. А для генерации TXT и Excel использовать CSV. Похоже, что с отчётами буду использовать именно такой вариант.

[kfuntov]

А Excel после gzip сколько вест?

[kfuntov]

Ещё вопрос - как вы Excel на лету генерили? Возможно лучше весь файл фигачить в память, а потом делать fromArray .

[Remmi]

@kfuntov Excel сжимается где-то в 4-5 раз всего. Excel делал через библиотеку PHPExcel, считывал исходный файл в массив:
$lines = gzfile($file);
И по очереди вписывал все элементы в генерируемый файл:
foreach ($lines as $line) {
$page->setCellValue(...)
}

[kfuntov]

Попробуйте всё-таки fromArray

$data = array(
array ('Name', 'Surname'),
array('Schwarz', 'Oliver'),
array('Test', 'Peter')
);
$objPHPExcel->getActiveSheet()->fromArray($data, null, 'A1');

Answer 3

[Vlad]

Мы в проекте храним отчеты в архивных таблицах (старше 2 месяцев). По запросу пользователя выбираются нужные данные, на лету создается xls и csv и собирается в архив с уникальным именем и отдается пользователю. Затем файлы (архивы) старше определенного периода (не скажу сколько точно) удаляются с сервера. Если пользователь не найдет отчета - всегда может создать заново.

Comments

[Remmi]

А какой размер у отчёта? Данные из базы собираются напрямую?

[Vlad]

По вашему пункту 3. Мы так и делаем. Почему архивные таблицы? У нас количество записей в таблице отчетов увеличивалось на ~15-20 млн строк за месяц. Без разделения на месяца мы бы действительно столкнулись с долгой выгрузкой данных и тормозной работой. Данные выбираются из базы (архивных таблиц) напрямую, естественно, за прошлые месяцы. За текущий - из основной таблицы данных.

[Vlad]

По размеру отчета - может быть до миллиона строк в текстовых файлах. Каждый месяц у нас записывается в один файл. Как правило - достаточно.

[Remmi]

@DaFive я выбрал MyISAM - ARCHIVE, конечно, хороший вариант, но выборка идёт медленно. В моём случае надо быстро выбирать данные за нужный период и отрисовывать графики в браузере клиента. Данные в БД кладёт единственный скрипт-сборщик.

[Vlad]

@Remmi тут уже вопрос в другом. Насколько четко у вас структурированы данные, какие индексы на полях по которым ищутся данные и т.п. Еще раз повторю, таблицы у нас немаленькие (~20 млн записей в каждой) - выборка у нас занимает очень мало времени. Больше времени скрипты генерируют файлы и складывают их в архив.

[Remmi]

@DaFive два индекса: ИД объекта и DATETIME записи, структура постоянная.

В день 36000 записей с одного объекта, сейчас объектов 5, за месяц около 5 миллионов записей.

Что посоветуете: делить данные на отдельные таблицы для каждого объекта? Или лучше делить по объектам и месяцам?

[Remmi]

Вспомнил: деление по месяцам не совсем подходит, для выборки используется интервал 30 дней, а такая штука почти обязательно попадёт на два разных месяца.

[Vlad]

Дык не пойму, причем интервал. Вы знаете, что выборка попадает от такого-то число до другого. Соответственно в курсе тех дат, которые должны присутствовать в запросе. Смотрите что за начальная дата и в какую таблицу попадает и смотрите, что за конечная. Просто пока у вас нет четкой структуры данных архивов отчетов - возникают непонимания. Дата выборки у нас на устоявшейся структуре служит для того, чтобы обратиться к нужной таблице по нужным ключам (у нас - ID партнера и ID проекта). Даты мы не выбираем. Просто в архивной таблице у нас исключена возможность попадания данных из другого месяца. Нужен период - смотрим какие месяца входят. Дергаем по очереди таблицы с именем (год, месяц) с данными по ID, складываем в отчет и запихиваем в архив.

[Vlad]

Вот пример. Тут данные меньше, ибо год старый и localhost, но ориентировочно - структура такая. clip2net.com/s/i3xBMu

[Remmi]

@DaFive то есть при выборке задействуются две (несколько) таблиц? И всё нормально проходит?

[Vlad]

@Remmi в вашем случае будут максимум задействованы 2 таблицы. У нас же можно выгрузить отчет за полгода (ограничено функционалом сайта), а то и больше. Не вижу ничего страшного, чтобы дернуть данные из двух таблиц. У вас же не реалтайм онлайн-игра. А разницу в полсекунды в запросах к MySQL вы не заметите. Еще раз повторюсь, выборка занимает мало времени. Больше времени у нас занимает генерация файлов, поэтому стоит специальный "отслеживальщик" его готовности в разделе сайта (то есть пока он не сформируется - пользователь видит лоадер).

Но, возможно не уточнил. У нас можно смотреть отчеты за текущий и прошлый месяц в онлайн. То есть выводится все детально со всеми вытекающими в разделе "Отчеты" на сайте. Если пользователь хочет более ранние отчеты - тогда мы уже собираем ему файлы.

[Remmi]

@DaFive судя по скриншоту, размер одной записи в вашей таблице примерно в 4 раза меньше, чем в моей. )

[Remmi]

@DaFive спасибо за ответ - пожалуй, попробую переделать БД из одной таблицы на несколько. И ARCHIVE, похоже, не так уж и плох, как мне сначала показалось.

[Vlad]

@Remmi записи там сильно порезаны и не далеко не все. Мне для дебага на локалхосте много не нужно. Возможно, у вас больше информации в одной записи, у нас же проекты разные. Я бы сходу в карьер не пробовал на вашем месте. А, к примеру, рассмотрел 2 варианта хранения. В нашем случае - другие нам не подошли.

Answer 4

[EugeneOZ]

Храните на Amazon S3 или Google Cloud Storage. Для них есть API, SDK - сделаете за пару часов работу с ними у себя в скрипте. И место там стоит дёшево и доступность со всей планеты быстрая.

Answer 5

[Arris]

if (function_exists('gzcompress')) {
    $gz = gzopen($path.$fname.".gz", "wb9");
    gzwrite($gz, file_get_contents($path.$fname.".sql"));
    gzclose($gz);
    unlink($path.$fname.".sql");
}

Comments

[Remmi]

Не рискнул бы делать file_get_contents на мегабайтном файле по запросу пользователя. Плюс unlink нужно юзать с определённой опаской.

[Arris]

Ну это намек - как можно сделать. Точнее ЧТО. А что опасного в unlink ?

[Remmi]

@Arris вот, например, примеры из мана, целая куча нюансов в его использовании:
ru2.php.net/manual/ru/function.unlink.php#101637

А по GZIPу, на мой взгляд, лучше делать через BAT-файл поиск всех файлов с маской *.csv и делать на них gzip, благо он идёт в комплекте с виндой. Быстро и удобно, плюс файл удалять не надо.

[Arris]

@Remmi gzip? В комплекте с виндой? Боюсь, вы что-то путаете. И вообще, причем тут винда? Мы, кажется, говорим о сайте и ПХП скрипте.

Если у вас не работает unlink() (у меня все прекрасно работает) - вы можете сделать truncate файла до нуля и потом стирание :) Думаю, написать код не составит для вас труда.

[Remmi]

@Arris понял :) Короче, не оправдало себя сжатие CSV-файла и последующая генерация на лету: PHPExcel делает файлы до 4 минут (до 2 минут в консольном режиме), так что придётся делать заготовки заранее или совсем отказываться от XLS-файлов.

[Arris]

@Remmi поищите библиотеки генерации в OpenOffice формат. Мне это тоже светит, хотя экспортировать надо базу поменьше, до 5000 строк.

[Remmi]

@Arris сейчас допёр, что gzip у меня с git'ом поставился, а саму папку с bin'арниками в системные переменные записал. Поэтому спокойно из консоли вызывал его )))