Исследование алгоритма пропорционального заполнения

Автор: Paul Randal, Investigating the proportional fill algorithm

Это тема, которая недавно всплыла в списке рассылки Microsoft Certified Master, и которую я продвигаю из-за её последствий для производительности, так что я подумал, что из неё получится интересная статья.

Алгоритмы выделения

Движок хранения SQL Server (Storage Engine, SE) использует два алгоритма при выделении экстентов из файлов в файловой группе: круговой обход (round robin) и пропорциональное заполнение (proportional fill).

Круговой обход означает, что SE будет пытаться выделять место из каждого файла в файловой группе поочерёдно. Например, для базы данных с двумя файлами в первичной файловой группе (с идентификаторами файлов 1 и 3, поскольку 2 — это всегда файл журнала), SE будет пытаться выделить из файла 1, затем из файла 3, затем из файла 1, затем из файла 3, и так далее.

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

Пропорциональное заполнение работает путём присвоения каждому файлу в файловой группе числа, называемого «целью пропуска» (skip target). Вы можете думать об этом как об обратном взвешивании, где чем выше значение 1, тем больше раз этот файл будет пропущен при обходе цикла кругового выделения. Во время кругового обхода цель пропуска для файла проверяется, и если она равна 1, происходит выделение. Если цель пропуска выше 1, она уменьшается на 1 (до минимального значения 1), выделение не происходит, и рассмотрение переходит к следующему файлу в файловой группе.

(Обратите внимание, что здесь есть дополнительная изюминка: когда используется параметр запуска -E, каждый файл с целью пропуска 1 будет использоваться для 64 последовательных выделений экстентов, прежде чем цикл кругового обхода продвинется дальше. Это задокументировано в Books Online здесь и полезно для увеличения смежности листовых уровней индекса для очень больших сканирований — вспомните хранилища данных.)

Цель пропуска для каждого файла — это целочисленный результат от (количество свободных экстентов в файле с наибольшим свободным пространством) / (количество свободных экстентов в этом файле). Файлы в файловой группе с наименьшим количеством свободного пространства, следовательно, будут иметь самые высокие цели пропуска, и в файловой группе должен быть по крайней мере один файл с целью пропуска 1, что гарантирует, что каждый раз при обходе цикла кругового выделения произойдёт хотя бы одно выделение экстента.

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

Исследование целей пропуска

Существует недокументированный флаг трассировки 1165, который позволяет нам видеть цели пропуска всякий раз, когда они пересчитываются, и я полагаю, что этот флаг был добавлен в SQL Server 2008. Он также требует включения флага трассировки 3605, чтобы позволить выводить отладочную информацию.

Давайте попробуем!

Сначала я включу флаги трассировки, выполню циклическую смену журнала ошибок, создам небольшую базу данных и посмотрю в журнале ошибок соответствующую информацию:

DBCC TRACEON (1165, 3605);
GO
 
EXEC sp_cycle_errorlog;
GO
 
USE [master];
GO
 
IF DATABASEPROPERTYEX (N'Company', N'Version') > 0
BEGIN
    ALTER DATABASE [Company] SET SINGLE_USER
        WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [Company];
END
GO
 
CREATE DATABASE [Company] ON PRIMARY (
    NAME = N'Company_data',
    FILENAME = N'D:\SQLskills\Company_data.mdf',
    SIZE = 5MB,
    FILEGROWTH = 1MB)
LOG ON (
    NAME = N'Company_log',
    FILENAME = N'D:\SQLskills\Company_log.ldf'
);
 
EXEC xp_readerrorlog;
GO

Вывод:

2016-10-04 11:38:33.830 spid56       Proportional Fill Recalculation Starting for DB Company with m_cAllocs -856331000.
2016-10-04 11:38:33.830 spid56       Proportional Fill Recalculation Completed for DB Company new m_cAllocs 8192, most free file is file 1.
2016-10-04 11:38:33.830 spid56       File [Company_data] (1) has 44 free extents and skip target of 1. 

m_cAllocs — это порог, при котором цели пропуска будут пересчитаны. В первой строке вывода у него случайное число, так как база данных только что создана и счётчик ещё не инициализирован. Это имя члена класса C++ внутри SE, который реализует управление файловыми группами.

Теперь я добавлю ещё один файл такого же размера:

ALTER DATABASE [Company] ADD FILE (
    NAME = N'SecondFile',
    FILENAME = N'D:\SQLskills\SecondFile.ndf',
    SIZE = 5MB,
    FILEGROWTH = 1MB);
GO
 
EXEC xp_readerrorlog;
GO

Вывод:

2016-10-04 11:41:27.880 spid56       Proportional Fill Recalculation Starting for DB Company with m_cAllocs 8192.
2016-10-04 11:41:27.880 spid56       Proportional Fill Recalculation Completed for DB Company new m_cAllocs 8192, most free file is file 3.
2016-10-04 11:41:27.880 spid56       File [Company_data] (1) has 44 free extents and skip target of 1. 
2016-10-04 11:41:27.880 spid56       File [SecondFile] (3) has 79 free extents and skip target of 1. 

Обратите внимание, что хотя у двух файлов разное количество экстентов, целочисленный результат от 79 / 44 равен 1, поэтому обе цели пропуска установлены в 1.

Теперь я добавлю гораздо больший файл:

ALTER DATABASE [Company] ADD FILE (
    NAME = N'ThirdFile',
    FILENAME = N'D:\SQLskills\ThirdFile.ndf',
    SIZE = 250MB,
    FILEGROWTH = 1MB);
GO
 
EXEC xp_readerrorlog;
GO

Вывод:

2016-10-04 11:44:20.310 spid56       Proportional Fill Recalculation Starting for DB Company with m_cAllocs 8192.
2016-10-04 11:44:20.310 spid56       Proportional Fill Recalculation Completed for DB Company new m_cAllocs 8192, most free file is file 4.
2016-10-04 11:44:20.310 spid56       File [Company_data] (1) has 44 free extents and skip target of 90. 
2016-10-04 11:44:20.310 spid56       File [ThirdFile] (4) has 3995 free extents and skip target of 1. 
2016-10-04 11:44:20.310 spid56       File [SecondFile] (3) has 79 free extents and skip target of 50. 

Файл с наибольшим свободным пространством — это файл с идентификатором 4, поэтому цели пропуска других файлов устанавливаются в (свободные экстенты файла 4) / (свободные экстенты в файле). Например, цель пропуска для файла 1 становится целочисленным результатом от 3995 / 44 = 90.

Теперь я создам таблицу, которая может иметь только одну строку на страницу, и заставлю произойти более 8192 выделений экстентов (вставив более 8192 × 8 строк, заставляя выделить столько страниц). Это также означает, что файлы автоматически вырастут и будут иметь примерно равное количество свободных экстентов.

USE [Company];
GO
 
CREATE TABLE [BigRows] (
    [c1] INT IDENTITY,
    [c2] CHAR (8000) DEFAULT 'a');
GO
 
SET NOCOUNT ON;
GO
 
INSERT INTO [BigRows] DEFAULT VALUES;
GO 70000
 
EXEC xp_readerrorlog;
GO

Вывод:

2016-10-04 11:55:28.840 spid56       Proportional Fill Recalculation Starting for DB Company with m_cAllocs 8192.
2016-10-04 11:55:28.840 spid56       Proportional Fill Recalculation Completed for DB Company new m_cAllocs 8192, most free file is file 3.
2016-10-04 11:55:28.840 spid56       File [Company_data] (1) has 0 free extents and skip target of 74. 
2016-10-04 11:55:28.840 spid56       File [ThirdFile] (4) has 0 free extents and skip target of 74. 
2016-10-04 11:55:28.840 spid56       File [SecondFile] (3) has 74 free extents and skip target of 1. 

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

Конкуренция за спинлок

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

Это означает, что если вы создаёте файловую группу с разными размерами файлов, скорее всего, они будут автоматически расти в разное время, и цели пропуска не все будут равны 1, а значит, спинлок придётся захватывать для каждого выделения экстента. Не огромная проблема, но это всё же дополнительные циклы CPU и возможность возникновения конкуренции за спинлок (для базы данных с интенсивной вставкой), которых вы могли бы избежать, сделав все файлы в файловой группе изначально одинакового размера.

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

Последствия для производительности

Итак, когда нужно заботиться о пропорциональном заполнении?

Один пример — при попытке смягчить конкуренцию за битовые карты выделения в tempdb. Если у вас один файл данных tempdb и огромная конкуренция PAGELATCH_UP на первой странице PFS в этом файле (из-за рабочей нагрузки с множеством параллельных соединений, создающих и удаляющих небольшие временные таблицы), вы можете решить добавить в tempdb всего один дополнительный файл данных (что не является правильным решением). Если существующий файл очень заполнен, а новый — нет, цель пропуска для старого файла будет большой, а цель пропуска для нового файла будет равна 1. Это означает, что последующие выделения в tempdb будут происходить из нового файла, перенося всю конкуренцию PFS на новый файл и не обеспечивая никакого облегчения конкуренции вообще! Я обсуждаю этот случай в своей статье о правильном добавлении файла данных в tempdb.

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

Резюме

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