Как уменьшить размер базы данных в 3 раза, ускорить сайт и уменьшить нагрузку на хостинг с помощью 3 модулей для Drupal 7

В этой статье можно будет узнать как настроить файловое кеширование на Drupal 7 сайте, увеличить максимальное время кеширования, удалить ненужные данные из базы данных и в результате - сократить размер базы данных в 3 раза.

В одном из проектов мы столкнулись с тем, что размер базы данных был довольно большим, база весила порядка 6.6 гб, но сам проект не был популярным, строился на будущее и встала задача - как на текущем виртуальном хостинг аккаунте с лимитом в 12 гб и другими проектами на аккаунте, разместить и этот проект не выходя за рамки лимитов.

Отметим, что хостинг был щедрым и в отличие от многих других хостингов, где база данных, файлы и бэкапы находятся в рамках одного общего лимита, на хостинге Beget на тарифе Start было: под файлы отдельно 12 гб на SSD, под базы данных 12 гб, бэкапы не тарифицируются.

Если на Вашем хостинге есть сайты под управлением Drupal 7 и есть необходимость уменьшить размер базы данных, улучшить быстродействие сайта, читайте дальше.

Выносим кеш таблиц базы данных в файлы

Из-за особенностей проектов под файлы было задействовано мало места, а под базы данных много, необходимо было по возможности вначале перераспределить занятое место в пользу файлов, решено это было установкой и настройкой модуля filecache, таким образом, чтобы весь кеш сайта по умолчанию хранился в файлах, а не в базе данных, уменьшая таким образом размер базы данных.

Чтобы после установки и включения модуля filecache он начал работать, необходимо в самый низ файла настроек: /sites/default/settings.php добавить следующий код (заменив пути до временной папки на свои): 

$conf['file_temporary_path'] = 'абсолютный_путь_к_временному_каталогу';

$conf['cache_backends'] = array('sites/all/modules/filecache/filecache.inc');
$conf['cache_default_class'] = 'DrupalFileCache';
$conf['filecache_directory']['default'] = 'абсолютный_путь_к_временному_каталогу/filecache';

$conf['page_cache_without_database'] = TRUE;
$conf['page_cache_invoke_hooks'] = FALSE;

Примечания:

Первую строку можно не добавлять в файле настроек (settings.php), а ввести через веб-интерфейс в админке по адресу: /admin/config/media/file-system (Временный каталог).

Чтобы узнать абсолютный путь на хостинге, можно в админке перейти по адресу /admin/reports/status/php и найти значение переменной: $_SERVER['DOCUMENT_ROOT'], это значение будет часть абсолютного пути до папки, в которой расположен сайт и в зависимости от настроек, временная папка может быть расположена в этой папке (чаще всего), например: /home/d/ddd_account_name/site.ru/public_html/tmp или вынесена на уровень выше, чтобы не быть доступной через интернет: /home/d/ddd_account_name/site.ru/tmp.

Последние 2 строчки позволяют загружать страницу из кеша максимально быстро, по возможности не дёргая базу данных или значительно уменьшая на неё нагрузку, по сравнению с тем, если эти 2 строчки не прописывать.

Кеширование уменьшает нагрузку на потребление ресурсов хостинга - процессорное время, затрачиваемое на сайт и базу данных. А в нашем случае ещё и уменьшает размер базы данных, т.к. по умолчанию в Drupal кеш хранится в базе данных, а спомощью модуля filecache, теперь кеш будет храниться в папке временных файлов. Т.к. база данных и файлы на хостинге Beget работают на SSD дисках, то на быстродействии файловый кеш не уступает кешу в базе данных.

Вынос кеша из базы данных в файлы позволил сократить размер базы на 2 гб (когда все страницы сайта закешированы). 

Увеличиваем время кеширования

Так как на нашем проекте обновление информации из открытых источников происходит раз в месяц, то есть смысл увеличить срок кеширования. Снизив максимально нагрузку на базу данных, ведь чем чаще очищается кеш, тем чаще страницы нужно заного генерировать, напрягая базу данных и процессор, а если кеш хранится долго, то и нагрузка на потребляемые ресурсы ниже.

По умолчанию в Drupal 7 максимальное время кеширования составляет 1 день:

Максимальное время кеширования в Drupal 7 составляет: 1 день
Максимальное время кеширования в Drupal 7 составляет: 1 день

Чтобы увеличить максимальное время кеширования на сайте под управлением Drupal 7, скачиваем, устанавливаем и активируем модуль Cache Lifetime Options , а в настройках по адресу /admin/config/development/performance теперь указываем желаемое время кеширования, вплоть до 12 месяцев. Так как на нашем проекте после импорта данных раз в месяц в ручную проверяется, что после импорта всё работает как надо, то и в ручную после проверки сбрасывается кеш, поэтому время кеширования выставлено максимальное:

Указываем желаемое время кеширования, вплоть до 12 месяцев
Указываем желаемое время кеширования, вплоть до 12 месяцев

*Объединение файлов JavaScript на скриншоте отключено, так как при включении портит отображение Яндекс.карт, если на Вашем проекте включение объединения скриптов не портит отображение страниц, то лучше оставить включенным.

Доступные варианты для выбора времени кеша:

Варианты выбора времени кеширования после установки модуля Cache Lifetime Options
Варианты выбора времени кеширования после установки модуля Cache Lifetime Options

Корректный подсчёт размера базы данных на хостинге

Здесь есть нюанс, забегая вперёд скажу, что сократив размер базы в 2 раза по данным PHPMyAdmin, в панели хостинга всё ещё отображался прежний размер и выполнение операции Оптимизации таблиц с помощью PHPMyAdmin, модуля optimizeDB или через SSH не приведёт к желаемому результату, а именно корректной калькуляции размера базы данных хостингом. Из-за ограничений прав на хостинге.

На хостинге Beget решить эту задачку можно либо превысив квоту на аккаунте под занимаемый размер для баз данных, в этом случае у них автоматически запускается процедура оптимизации таблиц и соответственно происходит пересчёт размера баз данных или необходимо написать тикет в тех.поддержку, чтобы они в ручную запустили эту процедуру. 

После этого размер базы уменьшается, но до следующего превышения, поэтому чтобы по возможности снизить случаи превышения из-за растущего кеша, а на рассматриваемом проекте было более 30 000 страниц и проход по ним роботом мог генерировать несколько гигабайт данных в кеше, необходимо было настроить файловый кеш.

Также стоит отметить, что раз в месяц, когда выполняется импорт данных, то база всё равно по данным хостинга растёт, но в PHPMyAdmin её размер адекватный, так что проблем по лимитам не должно быть, по крайней мере с этим проектом при превышении лимита, как правило на автомате, на следующий день происходит понижение. А если хостинг и напишет о превышении, Вы всегда сможете показать что PHPMyAdmin размер меньше и попросить запустить процедуру оптимизации таблиц баз данных на аккаунте.

Boost или Filecache - какой модуль лучше?

Кстати среди основных легко настраиваемых на уровне модулей систем кеширования для Drupal 7 рассматривались Boost и Filecache, по тестам времени отклика сособой разницы замечено не было, а по занимаемому размеру на жёстком диске, данные кеша всех страниц сайта от Boost весили в среднем на 30% больше, чем данные кеша от Filecache, поэтому выбран был именно модуль Filecache.

Как дополнительно уменьшить размер базы данных?

Следующей задачей было по возможности дополнительно сократить размер данных, хранящихся в таблицах базы данных.

Особенностью проекта был регулярный раз в месяц импорт данных из источника открытых данных, поэтому сами данные не обладали высокой ценностью, т.к. ежемесячно обновлялись. Ну и на самом хостинге автоматически создаются и хранятся до 10 последних бэкапов + можно бесплатно создать 1 вечный бэкап.

Drupal автоматически создаёт для каждого поля материала основную таблицу поля и копию этой таблицы с префиксом revision_, в которой хранится копия данных основной таблицы, по сути это удваивает количество таблиц и соответственно размер занимаемых ими данных. А сами таблицы с префиксом revision_ являются для проекта не нужными и избыточными. Сами таблицы с префиксом revision_ удалить не получится, их Drupal будет создавать автоматом каждый раз при записи данных в основные и здесь на помощь приходит модуль Field SQL norevisions

Модуль Field SQL norevisions позволяет очистить содержимое таблиц с префиксом revision_ и в дальнейшем контролировать эти таблицы, не допуская запись в них данных.

После установки модуля в админке по адресу /admin/config/system/field_sql_norevisions/field_sql_norevisions_entity_settings необходимо вначале выбрать какие данные нужно будет контролировать, отметив чекбоксы и нажав Сохранить:

Необходимо вначале выбрать какие данные нужно будет контролировать, отметив чекбоксы и нажав Сохранить
Необходимо вначале выбрать какие данные нужно будет контролировать, отметив чекбоксы и нажав Сохранить

Затем по адресу /admin/config/system/field_sql_norevisions/field_sql_norevisions_batch_delete нужно отметить все чекбоксы и нажать Удалить для удаления данных из таблиц с ревизиями:

Нужно отметить все чекбоксы и нажать Удалить для удаления данных из таблиц с ревизиями
Нужно отметить все чекбоксы и нажать Удалить для удаления данных из таблиц с ревизиями

После удаления данных, в базе таблицы в префиксом revision_ будут иметь только структуру, без данных, размером в 112-128 кбайт каждая такая таблица:

Удаление данных из таблиц с ревизиями сократило размер базы данных ещё на 2 гб.

Результаты

В итоге с помощью установки и настройки 2 модулей: Filecache и Field SQL norevisions, нам удалось уменьшить размер базы данных в 3 раза (с 6.6 гб до 2.2 гб) и ускорить загрузку страниц.

Благодаря модулю Cache Lifetime Options максимальное время кеширования на сайте увеличено до 12 месяцев и кеш сейчас сбрасывается в ручную, при необходимости, когда данные были обновлены. Благодаря этому снизилась нагрузка на хостинг.

Всё это позволило вместить текущий проект на виртуальном хостинге Beget без увеличения лимитов за те же деньги и попутно ускорить загрузку страниц и снизить нагрузку.

 

Если у кого-то есть идеи простых решений на уровне установки модуля, как ещё можно уменьшить размер базы данных на виртуальном хостинге, пишите в комментариях.

Также интересно будет узнать, если кто-то использует виртуальный хостинг с большими, чем у Beget лимитами по диску/базе данных/нагрузке на базу/нагрузке на процессор за те же или меньшие деньги, пишите в комментариях.

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