Оптимизация работы с БД

[Blakkky]

День добрый, комюнити.

Есть ряд наработок по оптимизации количества запросов к БД в ocStore 0.2.0 (проделывалось на магазине в 200+ категорий и 2000+ товарных позиций), в частности:

- Все категории грузятся одним запросом;

- Исправлены запросы с участием языка (убран один JOIN);

- Добавлено множество индексов на таблицы;

- Ряд мелких правок в коде для отладки и оптимизации работы;

По итогам, вместо более 1000 запросов к базе на генерации морды получил 140+, и ускорение генерации страниц с 1200мс до 200мс (при отключенном кешировании запросов).

Во вложении - diff от ocStore v0.2.0 (базовой) и скрипт генерации запросов для создания индексов (на всякий случай, на все "..._id"-поля, где-то, может, и переборщил, но все-равно лучше стало, чем было).

Также есть правки для дополнения SeoUrl_ocStore_0.2.0.zip (не помню, входит оно в базовую сборку или нет), тоже на уменьшение количества запросов.

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

PS: убедительная просьба ставить diff аккуратно и внимательно, предварительно забекапив скрипты и базу.

PPS: в будущем готов проделать тоже самое и для ocStore 1.х (по крайней мере посмотреть, что там и как), когда доберусь до переноса магазина с 0.2 на 1.0.

db_index_hotfix.php

preformance.diff.txt

[RGB]

Попробую на досуге воспользоваться вашим решением.

[Blakkky]

Что именно смущает?

diff содержит изменения в файлах, все можно посмотреть глазками, при наличии знаний по PHP.

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

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

Если будут какие-то проблемы - готов подсказать.

[RGB]

Что именно смущает?

diff содержит изменения в файлах, все можно посмотреть глазками, при наличии знаний по PHP.

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

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

Если будут какие-то проблемы - готов подсказать.

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

[RGB]

Не знаю почему, но у меня оптимизировать не только не получилось, но и совсем наоборот. Не знаю КАК у вас получилось 1000 запросов на главной, у меня все немного иначе.

В базе около 2500 товаров, главная до оптимизации:

Memory Usage: 1.573029 MB
Execution Time: 0.354942 seconds
75 sql queries executed:

После:

Memory Usage: 1.584435 MB
Execution Time: 0.656461 seconds
75 sql queries executed:

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

[Blakkky]

В базе у меня порядка 2300 товаров и 210 категорий, включен СЕО, поставлен модуль "deadcow seo", на главной показывается 10 последних новинок и 30 "лучших товаров".

75 запросов - это похоже на работу кеша резалтсетов (mysql_cached подключен как драйвер базы).

В моем патче я запросы мерюю непосредственно в методе DB->query();, т.е. до решения о том, где данные, в кеше или в базе.

Реально запросов на много больше, и вот почему:

Главная страница, это:

- Загрузить конфиг, настройки модулей и т.д. - 10-30 запросов в базу;

- Загрузить 30 категорий - 30-50 запросов в базу;

- Загрузить 10 позиций для "новинок" - 10 запросов в базу за загрузку + обновить кол-во просмотров (еще 10 запросов);

- Загрузить 30 позиций (по модулю "Последние") - 30 запросов в базу за загрузку + обновить кол-во просмотров (еще 30 запросов);

- Загрузить 10 статей - 3-5 запросов;

- Загрузить ссылки для SEF/ЧПУ - по 2 запроса на элемент страницы (т.е. (30 категорий + 40 позиций товара + 10 статей + 210 категорий, чтоб полный путь в ЧПУ построить) * 2 итого ~ 400 запросов.

Без ЧПУ выходит порядка 150-200 запросов в базу, с ЧПУ - к 600 подбирается. И это совсем скромные оценки :)

У меня сейчас стабильно 0.15 - 0.2 сек генерация любой страницы у магазина.

Если есть желание, готов помочь прикрутить патч к Вашему магазину.

[RGB]

В базе у меня порядка 2300 товаров и 210 категорий, включен СЕО, поставлен модуль "deadcow seo", на главной показывается 10 последних новинок и 30 "лучших товаров".

75 запросов - это похоже на работу кеша резалтсетов (mysql_cached подключен как драйвер базы).

В моем патче я запросы мерюю непосредственно в методе DB->query();, т.е. до решения о том, где данные, в кеше или в базе.

Реально запросов на много больше, и вот почему:

Главная страница, это:

- Загрузить конфиг, настройки модулей и т.д. - 10-30 запросов в базу;

- Загрузить 30 категорий - 30-50 запросов в базу;

- Загрузить 10 позиций для "новинок" - 10 запросов в базу за загрузку + обновить кол-во просмотров (еще 10 запросов);

- Загрузить 30 позиций (по модулю "Последние") - 30 запросов в базу за загрузку + обновить кол-во просмотров (еще 30 запросов);

- Загрузить 10 статей - 3-5 запросов;

- Загрузить ссылки для SEF/ЧПУ - по 2 запроса на элемент страницы (т.е. (30 категорий + 40 позиций товара + 10 статей + 210 категорий, чтоб полный путь в ЧПУ построить) * 2 итого ~ 400 запросов.

Без ЧПУ выходит порядка 150-200 запросов в базу, с ЧПУ - к 600 подбирается. И это совсем скромные оценки :)

У меня сейчас стабильно 0.15 - 0.2 сек генерация любой страницы у магазина.

Если есть желание, готов помочь прикрутить патч к Вашему магазину.

Видимо, вы как раз залезли в те дебри, куда я пока не могу попасть - я оценивал скорость работы по этому модулю: http://forum.opencart.com/viewtopic.php?t=27918

Вы не пробовали измерять результаты работы именно через него?

[costas]

Респект автору, как на счёт логирования медленных запросов MySQL с последующей оптимизацией?

[Blakkky]

Респект автору, как на счёт логирования медленных запросов MySQL с последующей оптимизацией?

Ну для себя я так и делал, просто в подвал сайта ссыпал запросы, медленнее 0.01 сек и их explain-ы (в diff-е есть этот код) чтоб при отладке посмотреть, что к чему и подправить.

Честно говоря, было огромное желание нормализовать базу по-человечески (в честности, избавиться от вложенных запросов для выбора рейтинга) и дописать целую кучу BaseТипНадстройкиController-ов (BaseShipping, BasePayment, BaseModuleController и т.д.) чтоб уменьшить копипаст кода при создании модулей, типов доставок и т.д. Остановило то, что это все будет абсолютно не совместимо с opencart-ом и поддерживать это будет намного труднее.

[rb2]

Честно говоря, было огромное желание нормализовать базу по-человечески (в честности, избавиться от вложенных запросов для выбора рейтинга) и дописать целую кучу BaseТипНадстройкиController-ов (BaseShipping, BasePayment, BaseModuleController и т.д.) чтоб уменьшить копипаст кода при создании модулей, типов доставок и т.д. Остановило то, что это все будет абсолютно не совместимо с opencart-ом и поддерживать это будет намного труднее.

Та боже ж мой. Автор опенкарта - живой человек, держит код в гуглокоде и принимает патчи.