Чеклист для оценки работы с СУБД

14 комментариев
Чеклист для оценки работы с СУБД
Оговорюсь сразу, что я не DBA. И я даже не напишу триггер без мануала. Однако мне посчастливилось приобрести неплохой опыт в разработке и оптимизации баз данных. Этим опытом я и хочу поделиться. Я хочу привести простой список, который, я надеюсь, поможет вам выявить узкие места в работе с СУБД на ваших проектах. Все это мои личные наработки, поэтому на какую либо полноту, упорядоченность, уникальность и безоговорочность они претендовать не могут. Итак, начнем. Структура БД - Созданы ли домены для всех необходимых столбцов? - Созданы ли все необходимые fk(foreign key)? - Созданы ли индексы для fk указывающие на таблицы с большим объемом данных(в некоторых СУБД индексы создаются вместе с fk)? - Правильный ли порядок полей в составных индексах? - Есть ли таблицы где размер id(pk) поля(домена) занимает более 30% от объема строки(кортежа) и есть вторичный pk(primary key)? - Можно ли заменить триггеры на fk? Работа с СУБД - Используете ли вы Connection pool? - Используете ли вы серверные курсоры? - Используете ли вы read-only транзакции и курсоры где возможно? Однонаправленные курсоры? - Используете ли вы параметризированные и подготовленные запросы (особенно при многократной вставке информации)? - Используете ли вы явное управление транзакциями? - Минимизирована ли длина транзакции? - Есть ли очень длинные или объемные транзакции, которые можно разбить на несколько? - Есть ли у вас после IUD запросов select в той же транзакции? - Выставлен ли таймаут для запроса? - выставлен ли минимально возможный Isolation Level? Меняете ли вы его в зависимости от нужд транзакции? - Знаете ли вы что, когда и на сколько блокируется на запись? А на чтение? - Блокируются ли любая ваша таблица более чем на секунду? - Знаете ли вы TOP 5 запросов в вашей системе по кол-ву вызовов? А по времени выполнения? - Можно ли часть часто используемой информации закэшировать? - Есть ли у вас выборки, которые по условию возвращают более 30% данных из таблицы? - Есть ли у вас выборки, которые возвращают информации в 2 раза больше чем необходимо? - Есть ли запросы, которые сканируют всю таблицу не по ключу(natural scan)? Вот вкратце и все что вспомнилось. Почти обо все вопросы я в свое время спотыкался. Практически каждый может выиграть вам кучу времени у сервера без всяких хитрых махинаций и подстроек СУБД и БД в частности. Не обвиняйте во всех проблемах других – попробуйте сначала найти их в себе. P.S. Если причина упоминания какого-либо пункта не ясна - спрашивайте.

Хотите сообщить важную новость?

Пишите в наш Телеграм

Читайте также

Самые популярные фреймворки и языки программирования среди стартапов-единорогов
Самые популярные фреймворки и языки программирования среди стартапов-единорогов

Самые популярные фреймворки и языки программирования среди стартапов-единорогов

2 комментария
Huawei запустила ИИ-базу данных для корпоративных клиентов
Huawei запустила ИИ-базу данных для корпоративных клиентов

Huawei запустила ИИ-базу данных для корпоративных клиентов

Разработчик открытых баз данных Redis ограничил использование части своих продуктов
Разработчик открытых баз данных Redis ограничил использование части своих продуктов

Разработчик открытых баз данных Redis ограничил использование части своих продуктов

2 комментария
СМИ: доступ к базе данных миллиарда индийцев стоит дешевле $10
СМИ: доступ к базе данных миллиарда индийцев стоит дешевле $10

СМИ: доступ к базе данных миллиарда индийцев стоит дешевле $10

1 комментарий

Обсуждение

Anonymous
Anonymous Software Engineer в EPAM
0

Для начала я бы посоветовал пронумеровать ваши "пункты", чтобы к ним было удобно ссылаться, по id так сказать =)
А вообще, интересует:
"- Используете ли вы явное управление транзакциями?" - не всегда в этом есть необходимость. Конечно все зависит от того, что вы подразумеваете под явным, если ручками открывать, комитить, откатывать и закрывать, то уж простите нет, далеко не всегда.
"- Блокируются ли любая ваша таблица более чем на секунду?" - я конечно прошу прощения, но устанавливать четкий порог в 1-ну секунду - это как-то не правильно, всегда есть исключиьтельные ситуации, когда можно позволить себе и больше, и, в то же время, некоторые ситуации не потерпят и такого долгого блокирования.
"- Знаете ли вы что, когда и на сколько блокируется на запись? А на чтение?" - вы знаете, не всегда мне нужно это знать, не считаю необходимостью отслеживать это всегда.
"- Есть ли у вас выборки, которые возвращают информации в 2 раза больше чем необходимо?" - опять же, что за хардкод у вас - в два раза и не больше...

так что вот, я далеко не со всем согласен.

Anonymous
Anonymous Team Lead / Project Manager в Qulix Systems
1

>> Для начала я бы посоветовал пронумеровать ваши "пункты", чтобы к ним было удобно ссылаться, по id так сказать =)
Я специально не нумеровал по 2 причинам: 1. нумерация задает порядок. Этот список нельзя назвать упорядоченным. 2. Неудобно читать и отвечать на коменты по номерам(особенно если потом пост апдейтится).Поэтому, если вы не возражаете, я оставлю as is.

Про конкретные цифры в списке. Естественно они взяты с потолка. Они индивидуальны для каждой системы, а описывать кучу нюансов нет смысла. Просто подставляйте туда то значение которая вам необходимо по требованиям. Я не могу найти для вас ответы. Я хочу всего лишь помочь вам поставить правильные вопросы.
А вообще я пользуюсь правилом 5 секунд - пользователь должен получить ответ не более чем через 5 секунд для десктоп и 1 секунду для веб.

>>вы подразумеваете под явным, если ручками открывать, комитить, откатывать и закрывать, то уж простите нет, далеко не всегда.
А зря. Открытие и закрытие транзакции тоже требует ресурсов. На 50 select запросов к примеру у вас будет 50 транзакций. Я думаю это не самое лучшее применение для ресурсов системы.

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

Anonymous
Anonymous
-1

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

- Используете ли вы Connection pool?
Да. У меня 10000 соединений в пуле!

- Минимизирована ли длина транзакции?
Да. У меня почти каждый инсерт идет в отдельной транзакции. (Открытие и закрытие транзакции тоже требует ресурсов. (с))

- Выставлен ли таймаут для запроса?
Конечно. 160 минут.

- Знаете ли вы TOP 5 запросов в вашей системе по кол-ву вызовов? А по времени выполнения?
Конечно. По от 2 до 5 часов. И чо?

И так далее. :)

Anonymous
Anonymous Team Lead / Project Manager в Qulix Systems
1

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

1

--Можно ли заменить триггеры на fk?
Не могли бы Вы поконкретнее пояснить этот пункт? Что Вы имеете ввиду здесь?

-- Есть ли очень длинные или объемные транзакции, которые можно разбить на несколько?
В разных СУБД по разному. Поэтому указывайте пожалуйста конкретные СУБД к которым применимо это правило. С точки зрения согласованности даных разбивать не стоит.

Anonymous
Anonymous Team Lead / Project Manager в Qulix Systems
1

--Можно ли заменить триггеры на fk?
Для MSSQL 2000 версии существовало кучу ограничений для поддержания целостности БД только на основе FK. поэтому достаточно часто для решения проблем с circular fk reference или же при необходимости обнулить поле создавались триггеры которые выполняли роль с которой вполне мог справиться и fk. В 2005 данные проблемы уже не возникали. Но на долгих проектах почти всегда остаются такие триггеры хоть поддержка 2000 сервера уже и не нужна.
А вообще триггер очень дорогое удовольствие в плане производительности. Если вы не реализуете БЛ на уровне БД (я ярый противник такого решения) то желательно поддерживать целостность БД на уровне приложения. Хотя иногда конечно без них никак не обойтись.

-- Есть ли очень длинные или объемные транзакции, которые можно разбить на несколько?
При объемных транзакциях очень сильно уменьшается перфоманс сервера БД. Я не могу однозначно ответить почему. Из размышлений я могу только отнести их к проблемам с ведением лога транзакции и неправильным перестроением индексов.
Конкретно относится к миграции и импортам данных из других систем. Если вам необходимо смигрировать, к примеру, 1 гигабайт данных, то быстрее всего несколькими транзакциями по 10 мегабайт(опять же цифра с потолка) заллить их в БД в вспомогательную таблицу, а потом одной процедурой применить изменения. И если в процессе заливки данных в БД в n-ой транзакции возникла ошибка то данные очищаются с помощью delete.
Такое решение работает быстрее чем если заливать все данные сразу в рабочие таблицы, при ошибке данные очищаются намного быстрее, ваши рабочие таблицы не залочены во время добавления данных в таблицу.
Проблема большой транзации 100% есть на 2000 сервере, но не думаю что она была полностью решена в боолее поздних версиях. Проблема с долгими откатами больших вопрос существуют во всех СУБД.
Решение с вспомогательными таблицами будет работать на любых СУБД.
Согласованность в данном случае никак не страдает.

0

"- Есть ли у вас выборки, которые возвращают информации в 2 раза больше чем необходимо?"
) а зачем вообще выборки, которые возвращают больше данных чем нужно?

для mysql можно добавить
- тюнинг параметров памяти
- оптимизация выбора типа таблиц (inmemory myisam innodb)
- избежать по возможности сортировок
- при "листании" длинных результатов вытягивать набор id а потом по id вытягивать каждую запись отдельно

Anonymous
Anonymous Team Lead / Project Manager в Qulix Systems
1

>>) а зачем вообще выборки, которые возвращают больше данных чем нужно?
К сожалению это достаточно часто встречающееся явления в бизнес приложениях, и особенно при использовании ORM. Эта так называемая практика reusable code.
Встречал случаи когда только для проверки существуют ли записи с определенным свойством вытягивались все записи удовлетворяющие этому условию для того что бы вызвать потом count.

За дополнительные пункты спасибо. Однако у меня нет большой практики работы конкретно с Mysql, поэтому в полной степени оценить я их не могу.

-1

это практика диктуется ленью - благодаря таким программистам - консультанты-тюнеры-оптимизаторы имеют и хлеб и к хлебу )))

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

0

1. По пункту: "- Созданы ли все необходимые fk(foreign key)? " хотелось бы отметить следующее, что при веб разработке зачастую внешние ключи не создаются в принципе, чтобы не увеличивать время вставки новых записей в таблицу (редактирование записей) . Естественно, там где это надо.
Это только в теории полностью нормализованная база является идеальным решением. При разработке под веб многие аспекты нормализации опускаются.
2. не вижу смысла явно открывать транзакции там, где это не нужно.

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

Anonymous
Anonymous Team Lead / Project Manager в Qulix Systems
1

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

А неявное открытие транзакций можно применять только на статических страницах или очень маленьких проектах. Но опять таки можно.

А по поводу стадий. Мои вопросы как раз относятся к более зрелым проектам. Никто не будет ломать голову о ускорении того что еще не написано. И конечно же у каждого проекта своя архитектура, свой путь, своя культура.

-1

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

>>А неявное открытие транзакций можно применять только на статических страницах или очень маленьких проектах. Но опять таки можно.
Вот это я не понимаю. Если я делаю обычный select к базе зачем мне открывать явно транзакцию?

Anonymous
Anonymous Team Lead / Project Manager в Qulix Systems
1

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

Для читающих транзакций можно использовать Isolation level read commited, а для изменяющих транзакций его вполне может не хватить для решения таких проблем как repeatable read и т.д.

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

-1

>>> Например за время генерауии страницы вы вызвали 10 select запросов. В итоге у вас 10 раз создалась и закрылась транзакция, хотя можно обойтись только 1 транзакцией.

Ну и что? Создание транзакции не такая тяжелая оперция как получение коннекта к базе. И кто сказал, что это плохо?

>>> Для читающих транзакций можно использовать Isolation level read commited, а для изменяющих транзакций его вполне может не хватить для решения таких проблем как repeatable read и т.д.

Всё это какие-то частные случаи. Это только для select запросов можно использовать read only транзакции. А как же быть со вставкой записей и редактированием, коих запросов обычно основная масса.

>> Плюс возможна проблема с неактуальностью данных. Например ту запись которую вы прочитали уже удалилиа другая транзакция. А значения из той записи вы используете, например, для добавления новой записи с fk полями.

Ну и что? Значит будет ошибка при вставке записи.
Зачастую записи вообще не удаляют из таблиц , потому как очень неприятная долгая операция. Часто заводят cnjk.tw с флажком, а если надо удалить эти записи, то пишут специальный сервис.

Спасибо! 

Получать рассылки dev.by про белорусское ИТ

Что-то пошло не так. Попробуйте позже