Кэширование SQL-запросов

OpenCart, как и любая CMS, осуществляет немалое количество sql-запросов к БД, часть из которых являются однотипными (то есть для разных пользователей будет один и тот же результат).

И если товаров много, то sql-запросы легко могут стать основной причиной тормозов интернет-магазина (если у вас 5000+ товаров, то об этом вы, вероятно, хорошо знаете).

Однако, этого можно избежать за счет кэширования sql-запросов модулем IMDBOptimizer.

Возможности:

1. Гибридная система кэширования SQL-запросов (БД + файлы), позволяющая увеличить скорость генерации HTML-страницы (тестировалось на стандартном OpenCart с 5500 товаров — прирост производительности от 30% до 70-80%) и частично сбалансировать нагрузку между диском и БД.
2. Поддерживается фильтр «по словам» для исключения SQL-запросов из процесса кэширования (регистронезависимо).
3. Поддерживается фильтр «по URL» для исключения отдельных страниц из процесса кэширования  SQL-запросов (регистронезависимо).
4. Так как кэшируются только SQL-запросы, то такой модуль можно успешно применять совместно с другими модулями кэширования (например, v2pagecache). Однако, совместимость лучше проверять на тестовом сервере.
5. Установили модуль? Ничего не нужно настраивать для кэширования. SQL-запросы автоматически начинают кэшироваться (с учетом фильтров), без необходимости что-то еще настраивать.
6. Еще одной отличительной особенностью кэширования именно SQL-запросов является то, что если один и тот же запрос используется при генерации разных веб-страниц или же просто выполняется повторно, то используется всего один кэш. Простой пример, открыли один и тот же товар из разных категорий — опции будут закэшированы всего 1 раз.
7. Можно применять как с созданием индексов, так и без.
8. При установке, модуль сразу создает типовую настройку, нужно лишь включить кэш.
9. Легко включается и легко отключается.

Ограничения и нюансы:

1. Так как это модуль кэширования уровня БД, то необходимо учитывать, что такие возможности, как отображение реального остатка товара или текущей цены в карточке, не поддерживаются (данные же кэшированы).
2. Кэшируются только SQL-запросы, начинающиеся с select.
3. Заменяется ядровой файл registry.php
4. Кэширование применяется только к клиентской части, в админской части все запросы выполняются как обычно.
5. Учитывайте, что кэширование это дополнительная нагрузка. Например, при первом открытии страницы товара, она может дольше загружаться (создается кэш).

Кэширование SQL-запросов — включение и очистка

Как включить кэширование:

1. Перейдите во вкладку «Кэш SQL-запросов».
2. Укажите, что кэширование «Включено» и сохраните настройки.
3. Кэш включен
   Для отключения нужно сделать все то же самое, только в шаге 2 установить «Отключено»

Как очистить кэш:

1. Перейдите во вкладку «Кэш SQL-запросов»
2. Нажмите кнопку «Удалить кэш»
3. Дождитесь соответствующего сообщения

Кэширование SQL-запросов — фильтры

Фильтр запросов «по словам»:

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

При фильтрации, символ «#» заменятся на префикс базы данных, что позволяет создавать собственные конфигурации, которые легко переносить в другие интернет-магазины.

Важно! Пробелы так же учитываются – сделано для того, чтобы можно было исключать отдельные таблицы. Например, строка «#order» без пробела после order исключает все таблицы, связанные с заказом. А с пробелом после исключает только oc_order (рекомендуется так же дублировать правило и указывать апостроф после order, так как SQL-запросы формируются по-разному).

Пустые строки игнорируются. Так же игнорируются пробелы вначале и в конце SQL-запроса.

Фильтр запросов «по URL»:

В данном фильтре построчно указываются комбинации «[тип поиска]: [часть URL адреса]» (или же просто «[часть URL адреса]») для отключения кэширования при генерации конкретных страниц (таких как корзина или же оформление заказа). Все комбинации приводятся к нижнему регистру (регистронезависимо).

1. [часть URL адреса] – это какая-то часть URL адреса, например, «#/cart/» или «=checkout/».
2. [тип поиска] – имеет три значения: «l» (сравнивать часть адреса сначала URL; учитываются параметры запроса), «i» (искать часть адреса внутри URL; учитываются параметры запроса), «r» (искать часть адреса справа; параметры запроса не учитываются).

При фильтрации, символ «#» заменятся на домен сайта, что позволяет создавать собственные конфигурации, которые легко переносить в другие интернет-магазины.

По умолчанию, указан список фильтров для стандартных настроек, а так же корзины с модулем Simple.

Так же учитывайте, что префиксы «http://» и «https://» обрезаются, и что если фрагмент «[тип поиска]:» не указан, то поиск происходит слева (чтобы можно было просто URL вставлять).

Примеры:

r: #/cart
Подходит «site.ru/cart», «site.ru/cart?asd=1». Но, не подходит «site,ru/cartini»

i: =checkout/
Подходит «site.ru/index.php?route=checkout/checkout&1=2». Но, не подходит «site.ru/index.php?route=check/some».

Кэширование SQL-запросов — дополнительные настройки

Максимальный размер вставки в БД. Данный параметр указывает свыше какого размера данных, информацию необходимо кэшировать в файл. Максимальное значение 65000. Рекомендуется использовать в диапазоне 20000 — 30000.

Время жизни кэша (сек). Здесь указывается время в секундах, в течение которое будет актуален созданный кэш.

Минимальное время для кэша (мс). Здесь задается время в мс, определяющее минимальное время выполнения sql-запроса, выше которого запрос кэшируется. Например, если стоит 20 мс, а запрос выполнился за 10 мс, то он не кэшируется. Если же запрос выполнился за 21 мс, то он кэшируется.

Таблицы sql-кэширования: тип MyISAM и InnoDB

Существует возможность выбирать какой тип таблицы будет использоваться для хранения sql-кэша в БД: тип MyISAM или InnoDB.

Учтите, что чисто физически это две разные таблицы в БД и что кнопка удаления кэша очищает обе таблицы.

Если вы не знаете что это и зачем нужно, то используйте тип MyISAM (по умолчанию в модуле используется).

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

Для тех, кому нужна только базовая оптимизация

Если вы не планируете сами вносить дополнительные индексы (или же оставите это тем, кто в этом разбирается), то, как уже говорил, модуль содержит настройки по умолчанию для базовой оптимизации БД.

Важно! Учтите, что операция весьма длительная и что требуется минимальная нагрузка в базе данных (то есть, желательно, чтобы пользователей сайта вообще не было или их было немного). В крайнем случае, вы можете последовательно и отдельно создавать индексы для каждой из таблиц, а не все скопом.

1. Сделайте бэкап всей базы данных (а лучше сайта в целом)! Это важно!

2. Откройте модуль

3. Выберите все таблицы (можно щелкнуть по ссылке «Выделить всё»)

4. Чуть ниже, щелкните по кнопке «Генерировать!»

5. Откиньтесь на спинку кресла и наблюдайте как модуль создает индексы

Несколько щелчков мыши и у вас проведена базовая оптимизация базы данных!

Учитывайте, что ajax-запросы с созданием индексов для таблиц (для каждой таблицы свой ajax-запрос) подразумевают определенное максимальное время выполнения (техническая особенность: ограничение времени для выполнения php в веб-сервере). Обычно, это значение в диапазоне 60-300 секунд (чаще всего 300, но зависит от хостинга или если у вас VPS, то от ваших настроек).

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

Дело в том, что несмотря на то, что ajax-запрос не выполнился в выделенное время веб-сервера, sql-запрос для создания индекса продолжает выполняться (индекс в БД продолжает создаваться).

Это может быть актуально для ситуаций, когда, например, в таблице порядка 1 000 000 записей и более (объемные интернет-магазины). В ситуациях с количеством товаров 10к+ такое вряд ли встретится, разве что у вас достаточно много опций или атрибутов.

Для понимания. В рамках тестов, для небольшого интернет-магазина в небольшом тестовом сервере с 11 000 товарами (88 000 связанных товаров, 10 000 опций, 5 000 атрибутов, 22 000 скидок, 25 000 акций), 20 000 заказов (110 000 товаров, 220 000 опций, 110 000 записей тотал), 29 000 клиентов, с таблицами до 200 000 записей, полное создание индексов заняло порядка 35 секунд.

Еще один тест. Для копии таблицы oc_product с 1 400 000 записей в том же небольшом тестовом сервере генерация 5 индексов заняла порядка 90 секунд.

Профилактика — создание индексов

Для профилактики, необходимо запускать генерацию после установки любых других модулей, которые создают свои таблицы (не всегда в них имеются все необходимые индексы).

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

Профилактика — оптимизация таблиц

Базы данных это сложные механизмы, где часть вещей остается на ответственность пользователей. Одной из них является снижение производительности, в связи с частым изменением и модификацией набора записей таблиц.

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

Поэтому для повышения производительности БД рекомендуется периодически выполнять оптимизацию таблиц. В среднем, для OpenCart это, примерно, раз в неделю.

Чтобы провести оптимизацию, достаточно лишь открыть вкладку «Сервис», выбрать таблицы и нажать кнопку «Оптимизировать!».

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

 

Починка таблиц

Как же неприятно открыть страницу товара и увидеть вместо карточки сообщение вида «PHP … Table is currupted … try to repair it…».

Редко, но такое бывает, что таблицы в БД MySql повреждаются. К счастью, чаще всего для их починки достаточно лишь запустить специальный запрос. Однако, чтобы это сделать необходимо зайти в хостинг, открыть панель phpMyAdmin, найти в интернете как составлять запрос и запустить его (а до этого всего, еще понять что делать с этой ошибкой). Для обычных пользователей или тех, кто только начинает, это весьма непростая задача.

Модуль же позволяет существенно упростить этот процесс. Нужно лишь указать поврежденную таблицу и нажать кнопку «Починить!» во вкладке «Сервис».

Учтите, что ошибки при подсчете не всегда означают ошибки восстановления. Так, например, таблица oc_cart попросту не поддерживает данный вид команды.

Блок с именами

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

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

Например:

Конкретные поля:
product_id, language_id

Префиксы:
stat, col

Окончания:
_id

При таких настройках, индекс для поля «product_option_value_id» будет создан (если его нет в таблице), так как поле заканчивается на «_id»

Карта индексов

В данном блоке можно составлять конкретные индексы, которые необходимо создать. Важно, что таблица каждого правила должна быть выбрана в списке таблиц. Иначе индекс не будет проверен или создан.

Правила составляются по следующему принципу:

Имя таблицы – Поле (, Поле)

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

Например:

#product_description – language_id, product_id

В данном случае будет создан индекс (language_id, product_id) для таблицы oc_product_description (если такового индекса не было в таблице).

Удаление индексов

В закладке «Индексы» есть возможность удалять из таблиц индексы, созданные модулем. А именно те, которые имеют префикс «imdbo_».

Сделано это для того, чтобы исходные индексы или созданные вручную индексы не были случайным образом удалены из базы данных.

Подход к генерации имен индексов

Чтобы упростить использование созданных индексов для других авторов и создателей сайтов, в модуле был введен специальный алгоритм генерации имен создаваемых индексов.

Так что если вы будете планировать использовать данные индексы при построении своих sql-запросов (например, с возможностью повторного внедрения в другие интернет-магазины), то сделать это будет весьма просто.

Сам алгоритм подбора

Шаг 1. Составляется имя «imdbo_» + «[поле 1]» + «[поле 2]» + … + «[поле N]». Если у таблицы индекса с таким именем нет и его длина не превышает 64 символа (требование БД), то индексу присваивается это название. В противном случае, алгоритм переходит к следующему шагу.

Например, для индекса (product_id) будет использовано имя «imdbo_product_id», а для индекса (product_id, order_id) будет использовано имя «imdbo_product_id_order_id». И так далее.

Шаг 2. Составляется имя «imdbo_» + «[поле 1 — первые буквы слов в столбце]» + «_[поле 2 — первые буквы слов в столбце]» + …  Если индекса с таким именем нет и его длина не превышает 64 символа (требование БД), то индексу присваивается это название. В противном случае, переход к следующему шагу.

Например, для индекса (order_id, customer_id, store_id, payment_zone_id, currency_id, marketing_id), чья длина больше 64 символов в шаге 1, будет использовано имя «imdbo_oi_ci_si_pzi_ci_mi».

Шаг 3. Практически нереальная ситуация, но сделана для унификации. Составленное имя из шага 2 обрезается до 61 символа (если требуется) и к нему прибавляется приставка «_[номер]», где номер от 1 до 99.

Например, «imdbo_oi_ci_si_pzi_ci_mi_1», …, «imdbo_oi_ci_si_pzi_ci_mi_25», «imdbo_oi_ci_si_pzi_ci_mi_99».

Если же и это недостижимо, то выполняется следующий шаг.

Шаг 4. Аналогично шагу 3, практически нереальная ситуация, но сделана для 100% унификации. Имя стоится как «imdbo_» + «[время UTC]»  + «_[номер]». Например, «imdbo_1508711578_3».

А теперь, по-простому. При базовой оптимизации будут созданы индексы только шага 1. Шаг 2 это уже настройки из карты индексов (если вручную были указаны сложные составные индексы). Шаг 3  встретится очень редко, но если и встретится такое, то имена с постфиксами будут аналогичными от интернет-магазина к интернет-магазину. Шаг 4 сделан просто для безопасности, но в реальности невозможен (если, конечно, кто-то специально вручную не создал более 100 одинаковых индексов для таблицы).

Лог медленных запросов

Модуль IMDBOptimizer позволяет сохранять в специальный лог-файл медленные sql запросы, что нередко удобно (не нужно тревожить поддержку / не нужно самому настраивать mysql / не всегда вообще существует такая возможность / …).

Модуль позволяет логировать не только sql запросы в клиентской части сайта, но и в админке.

Важно! Если у вас нет в корне сайта файла ‘.htaccess’ (не используете стандартный файл опенкарта для поддержки ЧПУ), то настоятельно рекомендуется создать такой файл и добавить в него строку:

Options -Indexes

Нюансы:

1. Для большей безопасности к названию файла добавляется случайно сгенерированная строка (создается во время установки/переустановки модуля).

2. Технические запросы самого модуля IMDBOptimizer не сохраняются в логе (включая закэшированные запросы). Простыми словами, если вам необходимо узнать какие sql-запросы медленно выполняются, то нужно либо отключить sql кэш, либо дожидаться момента, когда запросы будут перекэшированы.

3. Файл лога медленных запросов находится в /system/IMDBOptimizer/Log/imdbo_slow_[случайная строка].log

4. Логируются только те запросы, которые запрашиваются с помощью общего объекта в опенкарте. Поэтому если, например, какой-либо модуль использует собственное подключение, то его запросы не будут логироваться.

5. Модуль не может логировать повисшие запросы в БД, так как запись в лог добавляется после завершения выполнения sql-запроса.

6. В логе так же могут сохраняться URL (для удобства; если выставлена соответствующая настройка). Это крайне важно учитывать при включенном логе админки, так как в URL админки содержится токен безопасности. Так же этот момент может касаться запросов в клиентской части. Например, специального вида ссылки с ключами или токенами безопасности в параметрах.

7. Реальный размер файла лога в кнопке скачать отображается только после полной перезагрузки модуля.

8. Если размер файла с логом большой, то перед его скачиванием настоятельно рекомендуется отключать логирование медленных запросов. Так же советуется использовать ftp или хостинг-панель (если такая возможность существует).

9. Если размер файла с логом более 1 Гб, то его необходимо скачивать не через интерфейс, а через ftp или хостинг-панель (если такая возможность существует).

10. Помните, что если в sql-запросах содержится «деликатная» информация, то, при включенном логе медленных запросов, она так же будет сохранена в файле с логом. Поэтому к логу стоит относится с аккуратностью и разумной безопасностью. Например, включили лог, собрали данные о медленных запросах, скачали файл с логом, а затем выключили и удалили лог.

Пример лога:

---

2020-04-07 03:14:54

Exec time (s): 0.0863, IsAdmin = 0, URL = site.ru/index.php?route=product/category&path=20

SELECT COUNT(DISTINCT p.product_id) AS total FROM oc_product_to_category p2c LEFT JOIN oc_product p ON (p2c.product_id = p.product_id) LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pd.language_id = ‘1’ AND p.status = ‘1’ AND p.date_available <= NOW() AND p2s.store_id = ‘0’ AND p2c.category_id = ’20’

---

Настройка генерации по Cron

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

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

Доступные действия:

1. EnableSQLCache — включает SQL кэширование.
2. ClearSQLCache — очищает SQL кэш.
3. DisableSQLCache — отключает SQL кэширование.
4. EnableSQLLog — включает лог медленных запросов.
5. ClearSQLLog — очищает лог медленных запросов.
6. DisableSQLLog — отключает лог медленных запросов.

Примечание: Название действий регистронезависимы.

Как настроить генерацию по Cron:

1. Откройте модуль.
2. Перейдите во вкладку «Настройки».
3. Укажите секретный ключ в соответствующем поле (желательно цифро-буквенную комбинацию минимум из 20-30 символов). Тут важно понимать, что ключ не может быть пустым. В таком случае действия попросту не будут выполняться.
4. Сохраните настройки. Если вы использовали сохранение с перезагрукой, то снова откройте вкладку «Настройки».
5. Укажите список необходимых действий через запятую. Так же можете воспользоваться кнопками, расположенными прямо под полем со списком действий. Учитывайте, что действия выполняются в порядке их следования.
6. Сохраните ссылку и укажите в cron вызов через wget.
7. Повторяйте пункт 5-6 для всех нужных вам наборов действий.

Более подробно о том, как корректно вызывать cron написано у каждого провайдера (иногда бывают отличительные особенности), но обычно это выглядит так:

wget -q -O- [ссылка] > /dev/null 2>&1

где [ссылка] – это как раз та ссылка, которая генерируется во вкладке «Настройки».

Помните, что в один момент времени может быть запущен всего один список действий (иначе это может приводить к проблемам). Поэтому, настраиваете задания в cron с запасом по времени.

Секретный ключ можно менять сколько угодно раз, но важно помнить, что хранится только актуальный ключ. Поэтому если вы изменили секретный ключ, то ранее сохраненные ссылки (или указанные в cron) не будут приводить к запуску действий.

Помните, что скрипт выполняется при помощи обычного запроса wget (т. е. аналогично тому, как открыть страницу в браузере). Так что, если при выполнении действий скрипту не хватает времени, то необходимо увеличить время выполнения клиентского запроса в php. Например, если у вас SQL кэш занимает десятки Гигабайт, то очистка кэша может выполняться не очень быстро.

Особенности и ограничения

• Важно учитывать, что генерация индексов происходит для каждой таблицы отдельно. То есть для каждой таблицы отдельно посылается AJAX-запрос. Это связано с тем, что для больших таблиц создание одного индекса может быть весьма длительной операцией. Поэтому, если вы запустили генерацию, то просто дождитесь, когда рядом с кнопками появится сообщение, что генерация завершена.
• Создаются только обычные индексы.
• Удаляются только индексы, созданные модулем. А именно те, которые имеют префикс «imdbo_». Сделано для того, чтобы не сломать исходные настройки базы данных и вручную созданные индексы.
• Если не существует указанных таблиц или полей, то указанные данные будут просто игнорироваться.
• В БД проверяются только те таблицы, которые начинаются с префикса таблиц опенкарта (Сделано для тех, кто использует в одной БД несколько сайтов)
• Пользователь должен иметь полные права для получения доступа к БД (или достаточные для получения метаданных и создания индексов)
• Оптимизация производится для БД MySQL
• Имена создаваемых индексов имеют технические названия (техническое ограничение автоматизации)
• Если существует идентичный по полям индекс, то ничего не будет происходить.
• Заменяется ядровой файл registry.php
• Ссылку для cron (та что в настройках динамически создается) необходимо дополнительно проверять (могут быть некорректности в части до «index.php»; например, если вы скрыли админку в другой каталог; к сожалению, ядро опенкарта в этом смысле не очень удобное).
• Требуется, чтобы в настройках сайта было выставлено mbstring.func_overload 0. Если вы не знаете как это сделать , то уточните в вашем хостинге. В большинстве случаев по умолчанию это значение 0 (вам, как клиенту, ничего не нужно делать), но в некоторых хостингах выставляют значение 2. Это актуально в тех случаях, если у вас возникли проблемы с ключами и активацией модуля.
• Требуется boostrap и jquery

Установка, следующие версии и использование

1. Распакуйте в корень сайта содержимое (каталоги admin, catalog и system).

2. Откройте админку и установите модуль (если это следующая версия, то переустановите)

3. Обновите модификаторы

4. Возможно потребуется почистить кэш твига

5. Откройте в админке модуль (редактирование)

6. Пользуйтесь.

https://feofan.club/imdboptimizer-oc-3-optimizaciya-bazy-dannyh-v-1-4-1-key.html

 

Купить у Автора