Перед вами заключительная из серии статей, посвященных базе данных, которой необходимо, образно выражаясь, «сбросить вес к Новому году».
В первой статье (опубликованной в Windows IT Pro/RE № 3 за 2016 год) мы говорили о том, почему размер так важен и для самой базы данных, и для всех ее копий, что применимо для большинства организаций. Затем мы рассмотрели вопрос об оценке дополнительного объема дискового пространства, получаемого при использовании сжатия по методу ROW. Этот метод позволил сжать базу данных нашего клиента, изначально занимавшую 3,8 Тбайт дискового пространства, до 2,6 Тбайт без каких-либо модификаций кода. Более того, сжатие таблиц и индексов дало возможность значительно повысить производительность приложения, активно использующего подсистему ввода-вывода.
Во второй статье я показал, каким образом применение алгоритмов ROW и PAGE приводит к изменению внутренней структуры страниц базы данных, и объяснил, почему сжатие по алгоритму PAGE позволяет столь эффективно сокращать объем данных.
В третьей статье мы выяснили, как принимать решение по поводу того, следует ли в процессе сжатия использовать алгоритм ROW или PAGE, переходя от таблицы к таблице, от индекса к индексу и от раздела к разделу. После применения рекомендаций, изложенных в этой статье, объем базы данных сократился до 1,4 Тбайт, а скорость обработки данных стала еще выше, чем прежде.
В заключительной статье я рассмотрю еще два обстоятельства, активно способствующие разрастанию базы данных, и расскажу о том, что было сделано для их нейтрализации.
Большие строковые значения
Описывая процедуру сжатия таблиц, я упомянул о том, что механизм хранения строковых значений усовершенствовался лишь в двух базовых отношениях.
- При выполнении сжатия по алгоритму ROW данные char и nchar хранились в формате variable length. Таким образом, если значение hello сохранялось как char (40), SQL Server не выделял для его хранения 40 байт. Однако в процессе извлечения данных система возвращала 40 байт, как и ожидалось.
- В версии SQL Server 2008 R2 картина была дополнена компрессией данных Unicode. В конечном итоге значения многих двухбайтовых символов стали занимать лишь половину выделенного для них пространства.
Проблема здесь состоит в том, что в системах нередко хранятся большие объемы символьных данных, допускающих высокую степень компрессии, но, в сущности, они записываются в несжатом формате.
В рассматриваемой клиентской системе я обнаружил, что большой массив данных XML (порядка 700 Гбайт) хранится в столбцах с данными типа varchar. Эти данные могли бы храниться как данные xml, но нужды в том, чтобы допускать появление возникающих в таком случае непроизводительных затрат, не было, поскольку внутри базы данных функции XQuery для их опроса не использовались. Поэтому клиент попросту решил хранить эти данные в формате varchar. Если же в каких-то редких случаях администраторам все-таки приходилось применять к упомянутым данным функции XQuery, они ничего не имели против того, чтобы перед использованием этих данных преобразовывать их из CAST в формат xml.
Если вам когда-либо доводилось применять к данным этого типа компрессию в стиле ZIP, вы знаете, как хорошо они сжимаются. Так что я решил сжать соответствующие столбцы по алгоритму ZIP. Правда, надо отметить, что подобная процедура предполагает интенсивное использование ресурсов процессора. В целом я не сторонник того, чтобы компрессия выполнялась таким приложением, как SQL Server.
Декомпрессия существующих данных предполагает привлечение значительных ресурсов, и я хотел организовать дело так, чтобы она выполнялась не сразу, а в течение некоторого времени. Для реализации этой идеи в каждую таблицу, где данные должны храниться либо в сжатом, либо в несжатом формате, я включил дополнительные столбцы. Рассмотрим для примера таблицу в листинге 1.
Затем я изменил приложение таким образом, чтобы оно могло нормально работать независимо от того, были возвращены столбцы LogData или LogDataCompressed. Изменения коснулись и следующего обстоятельства: перед тем, как данные, включенные во все новые строки, передаются на SQL Server, они сжимаются по алгоритму GZip.
Наконец, мы написали небольшую утилиту, которая в периоды низкой активности системы последовательно сжимает все существующие данные. В результате массив данных на 700 Гбайт сократился до 180 Гбайт, а общий объем базы данных снизился до 880 Гбайт.
Сжатие в базе данных?
Если бы я, как это часто бывает, не смог модифицировать приложение, мне пришлось бы заменить таблицу представлением с тем же именем и выполнить компрессию и декомпрессию данных внутри базы данных. Однако не забывайте, что в большинстве случаев такую работу лучше выполнять вне базы данных. Однако, если по каким-то причинам это нужно делать внутри базы данных, руководствуйтесь настоящим разделом.
Одна из примечательных новых особенностей версии SQL Server 2016 состоит в том, что в нее включены собственные функции COMPRESS () и DECOMPRESS (). Подробное описание оператора утверждения COMPRESS можно найти в руководстве по адресу: msdn.microsoft.com/en-us/library/mt622775.aspx. Подробное описание утверждения DECOMPRESS содержится в руководстве по адресу: msdn.microsoft.com/en-us/library/mt622776.aspx. Если бы наш клиент к тому времени использовал версию SQL Server 2016, это было бы просто, но, увы, в его распоряжении была более старая версия системы.
В версиях, которые поддерживают технологию SQLCLR, функции для выполнения компрессии создаются относительно просто. Пример кода для решения этой задачи приведен в листинге 2.
Файлы изображений в PDF
Итак, объем данных, хранящихся в базе данных, сократился до 880 Гбайт. Из них 350 Гбайт использовалось для хранения файлов PDF в столбцах формата varchar (max). Эти файлы тоже заслуживали внимания.
Разумеется, работа с файлами PDF не входит в круг тем, связанных с эксплуатацией SQL Server, и все же есть смысл уделить внимание модификациям, которым мы подвергли упомянутые файлы.
- Разрешение текста было сокращено с 1200×1200 до 150×150 точек на дюйм. Даже при использовании разрешения 75×75 точек на дюйм я не встречал ни одного пользователя, который был бы способен увидеть разницу.
- Существующие в новых файлах изображения TIFF были заменены более миниатюрными (и высококачественными) изображениями PNG. Это не только привело к уменьшению размеров, но и позволило повысить качество увеличенных картинок. Такое изменение нельзя было осуществить в старых файлах. В них я понижал качество хранимых изображений с помощью функций из библиотеки PDF до тех пор, пока не достигал приемлемого баланса размера и качества.
- Ненужные шрифты, которые хранились буквально в каждом файле PDF, были удалены.
В результате объем данных в PDF, изначально составлявший 350 Гбайт, сократился до 120 Гбайт.
В большинстве случаев у нас имеется ряд возможностей для сокращения общего объема баз данных, и выше я уже писал о том, почему достижение этой цели имеет столь важное значение. Но вернемся к нашей клиентской базе данных. Сокращение общего объема базы данных с 3,8 Тбайт до 550 Гбайт обеспечило выполнение следующих модификаций:
- реализация сбалансированного подхода к выбору алгоритмов ROW и PAGE при сжатии данных;
- сжатие объемных строковых данных;
- сокращение размеров изображений в формате PDF.
С точки зрения нашего клиента это было значительным достижением.
Наконец, финальная (и, можно сказать, неожиданная) выгода состояла в том, что теперь база данных почти полностью умещалась в памяти. Система клиента была оснащена оперативной памятью объемом 512 Гбайт. Так что вы можете себе представить, насколько повысились в результате такие показатели, как общая производительность и степень управляемости.
CREATE TABLE dbo.SystemEvents ( SystemEventID bigint IDENTITY(1,1) CONSTRAINT PK_dbo_SystemEvents PRIMARY KEY, LoggedWhen datetime NOT NULL, LogData varchar(max) NOT NULL ); GO Я бы модифицировал эту таблицу следующим образом: CREATE TABLE dbo.SystemEvents ( SystemEventID bigint IDENTITY(1,1) CONSTRAINT PK_dbo_SystemEvents PRIMARY KEY, LoggedWhen datetime NOT NULL, LogData varchar(max) NULL, IsCompressed bit NOT NULL CONSTRAINT DF_dbo_SystemEvents_IsCompressed DEFAULT(0), LogDataCompressed varbinary(max) NULL, CONSTRAINT CK_dbo_SystemEvents_Either_LogData_Or_LogDataCompressed_Is_Required CHECK (LogData IS NOT NULL OR LogDataCompressed IS NOT NULL) ); GO
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.IO.Compression;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(Name = "CompressData",
DataAccess = DataAccessKind.None,
SystemDataAccess = SystemDataAccessKind.None,
IsDeterministic = true,
IsPrecise = true)]
public static SqlBytes CompressData(SqlBytes DataToCompress)
{
SqlBytes returnValue = SqlBytes.Null;
if (!DataToCompress.IsNull)
{
using (var compressedData = new MemoryStream())
using (var gZipOutputStream = new GZipStream(compressedData,
CompressionMode.Compress,
true))
{
gZipOutputStream.Write(DataToCompress.Buffer, 0, DataToCompress.Buffer.Length);
gZipOutputStream.Flush();
gZipOutputStream.Close();
returnValue = new SqlBytes(compressedData.ToArray());
}
}
return returnValue;
}
[Microsoft.SqlServer.Server.SqlFunction(Name = "DecompressData",
DataAccess = DataAccessKind.None,
SystemDataAccess = SystemDataAccessKind.None,
IsDeterministic = true,
IsPrecise = true)]
public static SqlBytes DecompressData(SqlBytes DataToDecompress)
{
SqlBytes returnValue = SqlBytes.Null;
if (!DataToDecompress.IsNull)
{
var copyBuffer = new byte[4096];
using (var decompressedData = new MemoryStream())
using (var gZipInputStream = new GZipStream(DataToDecompress.Stream,
CompressionMode.Decompress,
true))
{
int bytesReadFromInputStream;
while ((bytesReadFromInputStream
= gZipInputStream.Read(copyBuffer, 0, copyBuffer.Length)) > 0)
{
decompressedData.Write(copyBuffer, 0, bytesReadFromInputStream);
}
returnValue = new SqlBytes(decompressedData.ToArray());
}
}
return returnValue;
}
}