Автор: Paul Randal, Important considerations when performance tuning
Что, по вашему мнению, является самым важным аспектом при настройке производительности, если бы пришлось выбрать что-то одно? Суть в том, что в общем случае ответ всегда «зависит», но некоторые вещи оказываются главной проблемой чаще, чем другие. Давайте пройдёмся по каждому возможному ответу и посмотрим, как сильно они могут повлиять на производительность, если что-то не так. Это ни в коем случае не исчерпывающий список того, что может вызвать проблемы, а просто мои размышления в конце воскресенья, как обычно.
Проектирование и настройка подсистемы ввода-вывода, включая RAID
Здесь может быть довольно много проблем. Есть ли нагрузка с интенсивной записью (файлы данных или журналов) на массиве RAID-5 (в RAID-5 есть штраф на запись)? Правильно ли настроены смещение разделов диска, размер полосы RAID или размер единицы распределения NTFS (если у вас старинная система, подробнее см. Правильно ли установлены смещения разделов диска, размеры страйпа RAID и единицы выделения в NTFS?)? Достаточна ли производительность подсистемы ввода-вывода для нагрузки — высокая длина очереди диска, ошибки ожидания page IO latch wait, долгие операции ввода-вывода с ошибками? Является ли tempdb узким местом из-за того, что множество процессов с множеством пользователей пытаются одновременно создавать/удалять множество временных таблиц? (Подробнее см. Заблуждения вокруг флага трассировки TF1118). Используют ли несколько баз данных одну и ту же подсистему ввода-вывода, так что конкуренция за головки жёсткого диска (не SSD) вызывает проблемы с производительностью? Есть ли узкое место в сети для хранилища iSCSI? Не мешают ли фильтрующие драйверы, замедляя работу?
Аппаратное обеспечение сервера (ЦП/память/прочее)
Ладно — я, наверное, буду звучать как заезженная пластинка, говоря «здесь может быть довольно много проблем». Будем считать само собой разумеющимся, что в каждой категории есть много того, что может пойти не так. Высокая нагрузка на одном ЦП не даст хорошей производительности. Однако иногда чрезмерная параллелизация может привести к страшным ожиданиям CXPacket — несколько потоков в обработчике запросов ждут друг друга, потому что сервер перегружен, — или к проблемам блокировок, когда начинают мешать блокировки, но это уже больше проблема приложения — ой, не тот раздел. Наличие большего количества ЦП обычно хорошо, так как параллельные планы обычно работают быстрее однопоточных. Слишком мало памяти может серьёзно ограничить размер буферного пула и вызвать « thrashing» (постоянную замену страниц) буферного пула (низкое значение page life expectancy), а также привести к сбросу больших операций в tempdb, создавая дополнительную нагрузку на ресурсы. Не уверен, что я подразумевал под «прочее» — может быть, беспроводная клавиатура может замедлить передачу команд SQL Server? Ладно — вспомнил: архитектура ЦП. 64-битная против 32-битной. С 64-битной нет реального ограничения на объём адресуемой памяти и нет возни с AWE. Есть некоторые сложные моменты с NUMA, чтобы избежать межсокетных обращений к памяти (точно так же, как инвалидация кэша ЦП может ударить на SMP-архитектурах), но это уже немного углублённо и гиковско. Кстати — будьте внимательны, когда используете Extended Events на многопроцессорных машинах — там есть настройка, где можно указать тип архитектуры ЦП.
Виртуальный против «реального» сервера
Я первый признаю, что не являюсь экспертом в виртуализации, но теперь у меня дома есть три 8-ядерных машины и много дисков для экспериментов, поэтому я хочу им стать. Именно поэтому я сегодня купил книгу по Hyper-V. Что я знаю, так это то, что слышал от людей на конференциях и во время занятий — главное здесь — виртуализация ввода-вывода. Если используется программная виртуализация ввода-вывода, то производительность будет ужасной под нагрузкой, и лучше не делать этого в продакшене. Hyper-V от Microsoft обходит это, как и VMware ESX, позволяя назначать виртуальным машинам реальные физические ресурсы. И это всё, что я знаю по теме, пока книга не приедет и я не вернусь из Хьюстона, чтобы её прочитать. И, без сомнения, я напишу об этом ещё.
Физическое размещение базы данных (файлы/файловые группы)
tempdb — очевидный случай, и я уже упоминал его выше. С пользовательскими базами данных немного сложнее, и это действительно зависит от лежащей в основе подсистемы ввода-вывода. Один монолитный файл данных на одном жёстком диске не будет хорошо работать при высокой параллельной нагрузке, нужно менять на SSD. Но он может прекрасно работать на массиве RAID-10, хитро настроенном дорогим SAN. В современном мире всё больше баз данных размещается на SAN, поэтому производительность пользовательских баз данных в этом отношении обеспечена, и всё, о чём вам нужно беспокоиться, — это tempdb. .
Проектирование таблиц
Оба конца спектра здесь хороши — от пластиковых столов Costco с откидными металлическими ножками до антикварных французских дубовых фермерских столов. Оба прослужат много лет без ухудшения производительности. Как и обычные таблицы, таблицы SQL Server бывают всех форм и размеров, и вы легко можете выбрать неправильную для своих нужд, что приведёт к плохой производительности. Это огромный ящик Пандоры — на самом деле Кимберли тратит на это два изнурительных дня в одном из своих курсов, и она могла бы легко потратить неделю по 10 часов в день (о да, это называется программа SQL Masters). От выбора правильных типов столбцов до решения, как хранить LOB-значения (см. мой пост Importance of choosing the right LOB storage technique) и выбора хорошего первичного ключа. Только не выбирайте случайный GUID, а если он у вас есть, замените его. Предпочтительно, чтобы ключи кластерного индекса были уникальными, узкими, статичными и постоянно возрастающими. Проектирование таблиц охватывает так много аспектов, что я не могу должным образом осветить их в одном вечернем абзаце. Но вы поняли идею.
Кучи против кластерных индексов
Кимберли любит объединять это с некластерными индексами, но я хотел разделить их, чтобы иметь возможность сослаться на документацию Майкрософт, которая настоятельно рекомендует использовать кластерные индексы. По сути, с учётом возможности записей-пересылок (forwarding records) в кучах и того, что большая часть верхних уровней кластерного индекса находится в памяти, дополнительные случайные физические операции ввода-вывода для доступа к записи в куче перевешивают совокупную стоимость двоичного поиска в памяти на каждом уровне навигации по дереву кластерного индекса. Уверен, некоторые будут спорить — я готов к этому. Есть очень особые случаи, когда кучи лучше, но в общем случае — нет.
Стратегия некластерного индексирования
Главный пункт №1. С чего начать и что сказать? В большинстве сценариев, если у вас нет хорошей стратегии некластерного индексирования, вы не получите хорошей производительности. Причина проста: без правильных некластерных индексов, которые оптимизатор запросов мог бы выбрать, вы получите просмотр таблицы. Вашей таблицы на 124 миллиарда строк. Насколько быстро это будет? Опять же, у Кимберли есть курс, который она преподаёт, где она подробно рассказывает о том, как создавать индексы для OR, AND, JOIN и всевозможных других «извращений». Мне нужно как-нибудь сесть и послушать её, чтобы казаться знающим в этой области. Я просто знаю, что вам нужны индексы с правильной избирательностью. Ладно — я только что позвал Кимберли вниз в офис, чтобы убедиться, что я прав, прежде чем напишу что-то в блоге и выставлю себя идиотом, и она начала с «хм, ну, это зависит». Я сдаюсь. Я занимаюсь повреждениями, высокой доступностью и обслуживанием, а не настройкой производительности. Это чертовски сложно. К счастью, я женат на одном из лучших специалистов на планете по настройке индексов SQL — ничего себе, какие у вас шансы? (На настройку индексов, а не на женитьбу на Кимберли :-) Серьёзно, настройка индексов не так уж плоха — у меня просто случился момент слабости. Вам нужно убедиться, что у вас есть правильные индексы и нет совсем неиспользуемых, иначе вы тратите ресурсы на их поддержку.
Статистика
Если статистика устарела, оптимизатор не сможет выбрать хороший план, и ваша производительность будет ужасной. Всё просто. Включите AUTO_UPDATE_STATISTICS и сделайте обновление статистики частью регулярного обслуживания. Не забывайте также обновлять статистику для индексов и для столбцов без индексов.
Приложение (проектирование и код)
Главный пункт №2. Иногда никакая хитроумная настройка не может выжать хорошую производительность из извращённой логики неадекватного программиста. Приложение, написанное с абсолютным пренебрежением к тому, как работает SQL Server, скорее всего, будет работать плохо. Приложение, написанное со слишком большим знанием того, как работает SQL Server, скорее всего, попадёт в ловушку, полагаясь на «случайное» поведение или поведение, ограниченное определёнными объёмами данных или нагрузками. ORM. Приложение, которое будет интенсивно использовать SQL Server, должно учитывать, как SQL Server будет вести себя при различных нагрузках, на различных серверах и на различных подсистемах ввода-вывода. Обычный тестовый полигон приложения? Таблица клиентов из десяти строк с одним подключением. «Отлично — мой JOIN 16 таблиц с агрегациями на основе CLR выполняется менее чем за 2 секунды». Для 10 строк. Шесть месяцев спустя: «Теперь у нас 3 миллиона клиентов, почему производительность такая ужасная?» Догадайтесь сами.
Обслуживание базы данных
Ладно — начинаю уставать, а мне ещё нужно подготовить опрос на эту неделю. Может быть, я просто скажу «делайте обслуживание» и покончу с этим? Нет? Если вы не обслуживаете свои индексы, они, скорее всего, будут фрагментироваться и влиять на производительность тем или иным способом, и вам придётся играть с FILLFACTOR (см. Database Maintenance Best Practices Part II — Setting FILLFACTOR от Кимберли). Если вы не заботитесь о фрагментации VLF журнала транзакций, это повлияет на операции, связанные с журналом (снова Кимберли в Transaction Log VLFs — too many or too few?). Серьёзно — она говорит, что я много пишу в блог, — сколько ссылок в этом посте на её материалы? Она тоже много пишет!. Если у вас есть повреждения, они могут проявляться как долгие операции ввода-вывода. Если я не поставлю свою еду готовиться, я снова поем слишком поздно. Это была вставка «обслуживание Пола», которая просочилась — и может иметь катастрофические последствия для производительности блогинга. Серьёзно, вы не можете просто ввести базу данных в эксплуатацию и уйти. Она будет постепенно деградировать со временем. Как красный желе, тающий в жаркий летний день, но, вероятно, не так быстро, если только вы не используете случайные GUID в качестве первичных ключей, или если на улице действительно жарко. Что?!?
Настройка производительности — это искусство и наука. Но за ней стоит огромный объём науки, прежде чем вам придётся вникать в художественную сторону. Я лишь поверхностно коснулся этого в статье, на написание которого у меня ушло больше часа, — я стучал по клавишам без остановки. И я не занимаюсь художественной стороной. Я оставляю это Кимберли — она только что сократила время выполнения одного из пакетных процессов нашего клиента с 72 часов до 6.5 часов. Они были очень довольны. Не все мы можем быть Богинями настройки производительности.
Комментариев нет:
Отправить комментарий