Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3 4 5   вперед  Ctrl      все
 Падение производительности при checkpoint  [new]
white_satan
Member

Откуда:
Сообщений: 61
Есть на 2000 SQL-сервере SP4 build 2055 есть несколько таблиц:
1. _table1 = ~2 млн записей
2. _table2 = ~1 млн записей
3. _table3 = ~0.5 млн записей
4. _table4 = ~1.5 млн записей
5. _table5 = ~3 млн записей

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

В каждой таблице есть кластерные индексы и индексы для select

Есть web-скрипт, который вносит в данные в _table1 со скоростью 10 записей/сек
в зависимость от параметров, переданных скриптом, происходят также UPDATE и INSERT во все остальные таблицы

Средняя скорость обработки одного http-запроса 10-200мс, НО
периодически, регулярно, через некоторое время скорость обработки http-запроса падает и и составляет ОТ 2000 до 10000мс (бывает и дольше) и длинся это около 10-15 сек

в PerfomanceMonitor в этот момент график Checkpoint pages/sec увеличивается с 0 до 300-600.
Скорость записи на диск в этот момент около 5000000 байт/сек
ProcessExplorer показывает что идет только запись в файлы БД

Я так понимаю что сервер сбрасывает "грязные" страницы на диск, и дисковая подсистема просто "проседает" в момент chekpoint но:
1. почему так долго длится chekpoint?
2. как сделать так, чтобы запросы продолжали выполняться в момент сброса данных

Имеющиеся данные:
Windows 2003 Server
Sql server 2000 SP4 build 2055
два Raid1. (80Гб+80Гб Raid1 (7200rpm) под систему и лог транзакций и 600Гб+600Гб Raid1 (10000rpm) под файлы БД).


FillFactor везде 80%

recovery model :full

Как с этим бороться?
7 фев 12, 18:04    [12049358]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при checkpoint  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
white_satan,

1. делайть чекпоинт чаще будет быстрее проходить
2. увеличить скороть диска под лог транзакций
7 фев 12, 18:13    [12049449]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при checkpoint  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
white_satan,

5 мбайт/сек скорость записи, эт както совсем мало для серверного железа, у вас там винт не умирает случаем?
7 фев 12, 18:15    [12049468]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при checkpoint  [new]
Crimean
Member

Откуда:
Сообщений: 13147
филлфактор 80 говорите?
хотите сказать что у вас каждый день индексация?
и вам реально есть приход по сравнению с FF = 100?

а вообще диск дохлый на запись
поставить явно небольшой рекавери интервал - вариант и посмотреть, что получится
но сервер сам неплохо выбирает интервал для чекпоинтов, так что диск дохлый или политика кеширования никуда не годится
7 фев 12, 18:27    [12049554]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при checkpoint  [new]
white_satan
Member

Откуда:
Сообщений: 61
WarAnt
white_satan,

1. делайть чекпоинт чаще будет быстрее проходить
2. увеличить скороть диска под лог транзакций


1. recovery interval 0
Судя по счетчикам делается раз в 1-3 минуты
7 фев 12, 19:49    [12049902]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при checkpoint  [new]
white_satan
Member

Откуда:
Сообщений: 61
WarAnt
white_satan,

5 мбайт/сек скорость записи, эт както совсем мало для серверного железа, у вас там винт не умирает случаем?


Во-во, но тесты говорят что random write 45MB/sec
7 фев 12, 19:50    [12049905]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при checkpoint  [new]
white_satan
Member

Откуда:
Сообщений: 61
WarAnt
white_satan,
2. увеличить скороть диска под лог транзакций


Что даст скорость для логов транзакций?
уменьшит продолжительность checkpoint?
7 фев 12, 19:52    [12049917]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при checkpoint  [new]
white_satan
Member

Откуда:
Сообщений: 61
Crimean
филлфактор 80 говорите?
хотите сказать что у вас каждый день индексация?
и вам реально есть приход по сравнению с FF = 100?


Мне казалось что при частых UPDATE INSERT сервер должен перестраивать индексы сам.
Как узнать как часто у меня индексация?
7 фев 12, 19:54    [12049924]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при checkpoint  [new]
iljy
Member

Откуда:
Сообщений: 8711
white_satan
Crimean
филлфактор 80 говорите?
хотите сказать что у вас каждый день индексация?
и вам реально есть приход по сравнению с FF = 100?


Мне казалось что при частых UPDATE INSERT сервер должен перестраивать индексы сам.
Как узнать как часто у меня индексация?

Сервер сам ничего делать не будет. Настройка планов обслуживания (в том числе дефрагментации и реиндексации) - задача администратора.
7 фев 12, 19:58    [12049941]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при checkpoint  [new]
white_satan
Member

Откуда:
Сообщений: 61
iljy
white_satan
пропущено...


Мне казалось что при частых UPDATE INSERT сервер должен перестраивать индексы сам.
Как узнать как часто у меня индексация?

Сервер сам ничего делать не будет. Настройка планов обслуживания (в том числе дефрагментации и реиндексации) - задача администратора.

Т.е я сам должен настраивать деврагментацию, переиндексацию, обновление статистики?
Может что-то забыл...
помогите в этом плане. Какие задачи и как часто выполнять?
7 фев 12, 20:01    [12049951]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при checkpoint  [new]
iljy
Member

Откуда:
Сообщений: 8711
white_satan,

http://msdn.microsoft.com/ru-ru/library/ms189858.aspx
7 фев 12, 20:04    [12049966]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при checkpoint  [new]
white_satan
Member

Откуда:
Сообщений: 61
И вернемся к вопросу. Почему при checkpoint тупят остальные запросы? Почему скорость записи при checkpoint 5MB, хотя тесты дают 45MB?
7 фев 12, 20:06    [12049975]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при checkpoint  [new]
iljy
Member

Откуда:
Сообщений: 8711
white_satan
И вернемся к вопросу. Почему при checkpoint тупят остальные запросы? Почему скорость записи при checkpoint 5MB, хотя тесты дают 45MB?

Вернемся.
1. Что с фрагментацией таблиц и индексов?
2. Что с фрагментацией файлов на диске?
3. Что с кешированием записи?
7 фев 12, 20:11    [12049994]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при checkpoint  [new]
white_satan
Member

Откуда:
Сообщений: 61
iljy
white_satan
И вернемся к вопросу. Почему при checkpoint тупят остальные запросы? Почему скорость записи при checkpoint 5MB, хотя тесты дают 45MB?

Вернемся.
1. Что с фрагментацией таблиц и индексов?
2. Что с фрагментацией файлов на диске?
3. Что с кешированием записи?


1. например для _table1
индекс1
W (9): - Logical Scan Fragmentation ..................: 32.21%
W (10): - Extent Scan Fragmentation ...................: 98.95%
индекс2
W (9): - Logical Scan Fragmentation ..................: 3.04%
W (10): - Extent Scan Fragmentation ...................: 99.23%
индекс3
W (9): - Logical Scan Fragmentation ..................: 29.44%
W (10): - Extent Scan Fragmentation ...................: 99.47%
2. Фрагментация тома
Всего фрагментировано = 42 %
Фрагментация файлов = 85 %
Фрагментация свободного места = 0 %

Фрагментация файлов
Всего файлов = 10
Средний размер файла = 5,41 ГБ
Всего фрагментировано файлов = 3
Всего лишних фрагментов = 26
В среднем фрагментов на файл = 3,60
3. Это как?
7 фев 12, 20:25    [12050052]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при checkpoint  [new]
andsm
Member

Откуда: Москва
Сообщений: 1320
Блог
white_satan
И вернемся к вопросу. Почему при checkpoint тупят остальные запросы?

Обычно такое поведение наблюдается на слабом железе если и файлы данных и лог лежат на одном диске. Когда идет чекпойнт, тормозится запись в лог транзакций и из-за этого запросы идут дольше. Запись в лог транзакций явлется синхронной операцией, чекпойнт - асинхронная операция.
white_satan
Почему скорость записи при checkpoint 5MB, хотя тесты дают 45MB?

45 Мб - вероятно при копировании файла? Характер нагрузки разный. При копировании файла запись последовательная, при чекпойнте используется scatter gather writes.
А если еще и лог на том же диске где и данные - то замедляется из-за операций записи и лишних передвижений головок.

2iljy:
Фрагментация таблиц и индексов на такое поведение сказываться может, но очень слабо и на фоне других факторов для типичной системы будет просто не видно. Фрагментация файлов существенно повлиять может лишь при сильной фрагментации.
Кеширование - для ответа на заданный вопрос знание как там что настроено не нужно. Так что это вопросы ни о чем.
7 фев 12, 20:25    [12050053]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при checkpoint  [new]
white_satan
Member

Откуда:
Сообщений: 61
Когда идет чекпойнт, тормозится запись в лог транзакций и из-за этого запросы идут дольше. Запись в лог транзакций явлется синхронной операцией, чекпойнт - асинхронная операция.

Но лог транзакций на другом диске!!!
7 фев 12, 20:28    [12050067]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при checkpoint  [new]
andsm
Member

Откуда: Москва
Сообщений: 1320
Блог
white_satan
Но лог транзакций на другом диске!!!

На другом физическом диске или на другом логическом диске?

Есть ли на том диске где лог транзакций какие-то другие файлы? Может tempdb там же?
7 фев 12, 20:31    [12050079]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при checkpoint  [new]
white_satan
Member

Откуда:
Сообщений: 61
andsm
white_satan
Но лог транзакций на другом диске!!!

На другом физическом диске или на другом логическом диске?

Есть ли на том диске где лог транзакций какие-то другие файлы? Может tempdb там же?

- На другом raid1
- да, tempdb тамже. для чего он нужен? как это влияет на операцию checkpoint?
7 фев 12, 20:33    [12050091]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при checkpoint  [new]
iljy
Member

Откуда:
Сообщений: 8711
andsm
2iljy:
Фрагментация таблиц и индексов на такое поведение сказываться может, но очень слабо и на фоне других факторов для типичной системы будет просто не видно. Фрагментация файлов существенно повлиять может лишь при сильной фрагментации.
Кеширование - для ответа на заданный вопрос знание как там что настроено не нужно. Так что это вопросы ни о чем.

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

white_satan
- да, tempdb тамже. для чего он нужен? как это влияет на операцию checkpoint?

При интенсивном использовании tempdb может давать существенную нагрузку, причем случайного доступа. Что может сильно конфликтовать с последовательным доступом к лог-файлу. Опять же - при отключенном кешировании записи падение производительности может быть очень серьезным.
7 фев 12, 20:42    [12050126]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при checkpoint  [new]
andsm
Member

Откуда: Москва
Сообщений: 1320
Блог
white_satan
- На другом raid1
- да, tempdb тамже. для чего он нужен? как это влияет на операцию checkpoint?

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

Если убрать из рассмотрения первое типичное объяснение замедления при чекпойнте, то тогда надо смотреть на второе. Вторая типичная проблема - в запросах не используются индексы, требуется просмотр полностью таблиц, а БД не помещается целиком в памяти. Тогда замедление при чекпойнте вызвано тем что запросы ждут когда же будут с диска прочитаны данные. А данные читаются медленно из за работы чекпойнта.
Такой вариант легко проверяется по счетчикам производительности.
7 фев 12, 20:44    [12050135]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при checkpoint  [new]
white_satan
Member

Откуда:
Сообщений: 61
При интенсивном использовании tempdb может давать существенную нагрузку, причем случайного доступа. Что может сильно конфликтовать с последовательным доступом к лог-файлу. Опять же - при отключенном кешировании записи падение производительности может быть очень серьезным.

1. Как узнать что tempdb интенсивно используется?
2. как включить/выключить кеширование записи. Если это в настройках RAID, то там стоит WRITE BACK, CACHED
7 фев 12, 20:45    [12050141]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при checkpoint  [new]
white_satan
Member

Откуда:
Сообщений: 61
Вторая типичная проблема - в запросах не используются индексы, требуется просмотр полностью таблиц, а БД не помещается целиком в памяти. Тогда замедление при чекпойнте вызвано тем что запросы ждут когда же будут с диска прочитаны данные. А данные читаются медленно из за работы чекпойнта.


Вот это ближе к делу. Но...есть пару вопросов....
1 БД не помещается целиком в памяти. я выделил серверу динамическую память от 2 до 4 Г, но от забирает 1.7 и больше не берет?
2 У нас много разных запросов к одной(нескольким) таблицам. не будем мы же создавать для каждого запроса индексы. все равно есть запросы где нужно просматривать таблицу. как найти компромис? Много запросов с between по дате.
7 фев 12, 20:53    [12050178]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при checkpoint  [new]
white_satan
Member

Откуда:
Сообщений: 61
Тогда замедление при чекпойнте вызвано тем что запросы ждут когда же будут с диска прочитаны данные. А данные читаются медленно из за работы чекпойнта.

Нет ли механизма, при котором бы checkpoint не загружал диск на 100%..ну например часть данных записал, дал возможность прочитать данные...
7 фев 12, 20:56    [12050192]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при checkpoint  [new]
white_satan
Member

Откуда:
Сообщений: 61
и еще вопрос. sql server держит индексы в памяти?
7 фев 12, 20:58    [12050210]     Ответить | Цитировать Сообщить модератору
 Re: Падение производительности при checkpoint  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
white_satan
и еще вопрос. sql server держит индексы в памяти?
Страницы индексов в этом плане ничем не отличаются от страниц данных. Имеет смысл - держит.
7 фев 12, 21:01    [12050233]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3 4 5   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить