Справочник Бухгалтера

По материалам рассылки comp.soft.winsoft.sqlhelpyouself на subscribe.ru

Часть 1) Сокращение размера Transaction Log в SQL Server 2000 с помощью DBCC (По материалам Q272318)

Команда DBCC SHRINKFILE для урезания log в SQL Server 2000 больше не является отложенной операцией. DBCC SHRINKFILE пытается сокращать файл немедленно. Однако, в некоторых случаях, необходимы дополнительные действия для того, что бы журнал был сокращён до ужного размера. При выполнении DBCC SHRINKFILE, SQL Server 2000 удаляет виртуальные журналы, чтобы в целом получить заданный размер журнала. Если заданный размер не достигнут, SQL Server размещает фиктивные входы журнала в последний виртуальный журнал, пока виртуальный журнал не будет заполнен, а потом перемещает заголовок журнала в начало файла. В таком случае, чтобы завершить сокращение размера журнала:

1. Выполнить инструкцию BACKUP LOG, чтобы удалить неактивную часть журнала.
2. Выполнить DBCC SHRINKFILE снова, задав желательный размер.

Например:

DBCC SHRINKFILE(pubs_log, 2)
(Если нужный размер не достигнут)
BACKUP LOG pubs WITH TRUNCATE_ONLY
DBCC SHRINKFILE(pubs_log,2)

Часть 3) Метод быстрого усечения журнала транзакций и перевода БД в offline По материалам статьи Krishnan М. Kaniappan на swynk.com «Quickly Shrinking the Transaction Log»

В этой статье Кришнан обсуждает два следующих аспекта:
— Усечение журнала транзакций.
— Метод перевода базы данных в автономный режим (offline).

Усечение журнала транзакций

Кришнан предлагает следующую уловку для быстрого усечения журнала транзакций, который у Вас может стать слишком большим. Вы можете сократить журнал транзакций, используя системные хранимые процедуры sp_detach_db и sp_attach_db. Для получения дополнительной информации об отсоединении и присоединении баз данных обратитесь к BOL. Когда Вы отсоединяете базу данных, используя sp_detach_db, SQL сервер будет знать, что работа с базой данных была корректно завершена, и для последующего прикрепления базы данных, журнал транзакций не нужен и может быть не доступен. Его можно удалить. Когда Вы прикрепляете базу данных, SQL сервер создаёт новый журнал, который будут иметь минимальный размер. Для этого необходимо выполнить следующие шаги:

— Отсоедините базу данных, используя процедуру sp_detach_db (предварительно убедитесь, что никакие процессы не используют файлы базы данных);
— Удалите журнал;
— Прикрепите базу данных заново, используя процедур sp_attach_db у.

Так как Вы отсоединяете и прикрепляете базу данных к тому же самому серверу, Вы не будете иметь проблем с нарушением логинов. Для получения дополнительной информации по усечению файлов журналов транзакций, состоящих из одного или более журналов, Вы можете обратиться к статьям Базы Знаний Микрософт: Q256650 (для SQL 7.0) и Q272318 (для SQL 2000).

Часть 3) Как усекается журнал транзакций SQL Server 7.0 По материалам статьи из Microsoft Knowledge Base

Есть несколько причин, из-за которых журнал транзакций не усекается при использовании DBCC SHRINKFILE или DBCC SHRINKDATABASE. В Books Online DBCC SHRINKFILE и DBCC SHRINKDATABASE описаны достаточно хорошо, но слишком кратко. В Микрософте SQL Server 7.0, команды SHRINKFILE и SHRINKDATABASE устанавливают желаемый размер, до которого необходимо усекать журнал. Эти команды могут быть применены для каждого журнала, но это, фактически, только заявка, которую сервер попытается выполнить. Поэтому, после выполнения команды SHRINKFILE или SHRINKDATABASE, Вам придётся дополнительно выполнить команду, которая усекает журнал и у Вас есть шанс, что это произойдёт. Вы не можете уменьшить журнал транзакций до размера меньше установленного критериями, которые представлены ниже:

— Чтобы усечь журнал транзакций до размера меньшее первоначального, Вы должны уменьшить соответствующие файлы с помощью DBCC SHRINKFILE. Вы не можете использовать DBCC SHRINKDATABASE, чтобы усечь журнал транзакций до размера меньшего его оригинала или явно заданного размера. Первоначальный размер определяется, как размер файла журнала в команде CREATE DATABASE плюс любые использованные после этого команды ALTER DATABASE. Первоначальный размер определяется без учёта автоматического роста файла журнала.

— Физический размер журнала никогда не может быть меньше используемой сервером в момент усечения его части. Вы можете использовать команду DBCC SQLPERF (LOGSPACE), чтобы увидеть количество используемого в журнале места.

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

— Поскольку журнал транзакций может быть сокращен только до границы виртуального журнала virtual log file (VLF), не возможно сократить журнал к размеру меньше чем VLF, даже если это место реально не используется. Аналогично, если часть VLF используется, Вы не можете сократить занимаемое этим VLF место. Для получения дополнительной информации, см. разделы «Virtual Log Files» и «Transaction Log Physical Architecture» в Books Online.

Журнал транзакций, это wrap-around (с запашком) журнал. Это означает, что в любое время могут существовать свободные (free) или многократно используемые (reusable) VLF, которые могут находиться в начале, в середине, и/или в конце журнала транзакций. Сократить можно только свободное место в конце журнала транзакций, а не всё свободное пространство журнала. Также, Вы можете сократить только целые VLF. Сокращаемые в конце журнала VLF должны быть неактивны. Для более детальной информации обратитесь к разделу Truncating the Transaction Log в Books Online.

Обратите внимание на следующее:

— Всегда резервируйте системные и пользовательские базы данных до и после того, как Вы вносите изменения, затрагивающие систему. DBCC SHRINKFILE и DBCC SHRINKDATABASE — не регистрируемые операции, и исполнение их нарушает последовательность отписывания транзакций из журнала в резервную копию. После выполнения этих команд, Вы должны будете сделать полное резервное копирование базы данных.

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

— Удостоверьтесь, что отсутствуют старые, долго выполняющиеся или не копируемые транзакции. Чтобы сделать это, используйте:

DBCC OPENTRAN (database_name)

— Выполните DBCC SHRINKFILE или DBCC SHRINKDATABASE, чтобы определить возможную границу усечения (shrinkpoint). DBCC SHRINKFILE и DBCC SHRINKDATABASE, по умолчанию, разрешены членам серверной роли sysadmin или роли базы данных db_owner. Для информации о различиях между этими командами, обратитесь к Books Online (обратите внимание на отличие в параметрах):

DBCC SHRINKFILE (file_name, target_size)
DBCC SHRINKDATABASE (database_name, target_percent)

— Создайте несколько фиктивных транзакции, чтобы имитировать журналирование транзакций и затем, выполните команду BACKUP, чтобы осуществить усечение журнала транзакций. Инструкция BACKUP фактически попытается усечь журнал транзакций к заданному целевому размеру (target_size или target_percent).

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

SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes int,
@NewSize int
— *** MAKE SURE TO CHANGE THE NEXT 3 LINES WITH YOUR CRITERIA. ***
— This is the name of the database for which the log will be shrunk.
USE Your_Database_Name
SELECT @LogicalFileName = ‘Your_log’ — Use sp_helpfile to identify the logical file name
that you want TO shrink.
@MaxMinutes = 10, — Limit on time allowed to wrap log.
@NewSize = 100 — in MB
— Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size — in 8K pages
FROM sysfiles
WHERE NAME = @LogicalFileName
SELECT ‘Original Size of ‘ + DB_NAME() + ‘ LOG is ‘ +
CONVERT(varchar(30),@OriginalSize) + ‘ 8K pages or ‘ +
CONVERT(varchar(30),(@OriginalSize*8/1024)) + ‘MB’
FROM sysfiles
WHERE NAME = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) NOT NULL)
— Wrap log and truncate it.
DECLARE @Counter int,
@StartTime datetime,
@TruncLog varchar(255)
SELECT @StartTime = GETDATE(),
@TruncLog = ‘BACKUP LOG ‘ + DB_NAME() + ‘ WITH TRUNCATE_ONLY’
— Try an initial shrink.
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
— Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) — time has not
expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE NAME =
@LogicalFileName) — the log has not shrunk
— The value passed in for new size is smaller than the current size.
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN — Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN — update
— Because it is a char field it inserts 8000 bytes.
INSERT DummyTrans VALUES (‘Fill Log’)
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END — update
EXEC (@TruncLog) — See if a trunc of the log shrinks it.
END — outer loop
SELECT ‘Final Size of ‘ + DB_NAME() + ‘ LOG is ‘ +
CONVERT(varchar(30),size) + ‘ 8K pages or ‘ +
CONVERT(varchar(30),(size*8/1024)) + ‘MB’
FROM sysfiles
WHERE NAME = @LogicalFileName
DROP TABLE DummyTrans
PRINT ‘*** Perform a full database backup ***’
SET NOCOUNT OFF

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

После того, когда журнал транзакций усечён:

1. Выполните полное резервное копирование базы данных master.
2. Выполните полное резервное копирование пользовательской базы данных. Это необходимо, потому что команда SHRINK не регистрируется в журнале и нарушается порядок отписывания транзакций.

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

Дополнительная информация в Microsoft Knowledge Base:

Разделы Books Online: Transaction Log Physical Architecture; Optimizing Transaction Log Performance.

Часть 4) Причины заполнения журнала транзакций SQL серверов 4.2x, 6.0, 6.5, 7.0 По материалам статьи Микрософт: Q110139 — INF Causes of SQL Transaction Log Filling Up

Журнал транзакций может полностью заполнится, что сделает невозможным операции UPDATE, DELETE или INSERT, включая CHECKPOINT. Обычно это проявляется, как ошибка 1105, имеющая следующее содержание:

От этого может пострадать любая база данных, включая master и tempdb. Эта статья рассматривает возможные причины и решение проблем, приводящих к ошибке 1105. Если Ваш журнал транзакций заполнен полностью, и Вы получили сообщение об ошибке 1105, Вы должны очистить журнал, используя команду DUMP TRANSACTION. Для получения дополнительной информации об использовании DUMP TRANSACTION, см. документацию SQL сервера.

Фундаментальной характеристикой реляционных баз данных, таких как Microsoft SQL Server, является поддержка целостности. Любая транзакция должна быть абсолютно неделима и все вносимые ей изменения должны быть применены полностью или не применены вообще, даже в случае отказа системы. В определяемой пользователем транзакции, транзакционным блоком считаются все инструкции между BEGIN TRANSACTION и COMMIT TRANSACTION. В неявной транзакции, каждая отдельная инструкция SQL рассматривается неделимым модулем. Это дает возможность серверу баз данных выдерживать сбои питания, аварийные отказы операционной системы, и т.д., когда, после перезапуска, происходит автоматическое (без участия обслуживающего персонала) восстановление баз данных к непротиворечивому состоянию. Эту возможность обеспечивает механизм журнала транзакций. Так как целостность данных является фундаментальным свойством SQL сервера, регистрация транзакций не может быть заблокирована. Некоторые утилиты или операции, типа BCP или SELECT INTO, имеют минимальную регистрацию в журнале, но даже этого достаточно, чтобы обратный откат таких операций был возможен. Требования к количеству дискового пространства для поддержки регистрации транзакций могут быть весьма высокими. Например, в большинстве случаев, модификация каждой строки данных должна быть зарегистрирована, а также регистрируются и все модификации затронутых индексов. Так как запись в журнале может содержать фиксированную часть данных изменяемой строки, количество занимаемого журналом места зависит от ширины строки. Для коротких строк, количество занимаемого в журнале места, при операциях UPDATE, DELETE или INSERT, может в десятки раз увеличивать занимаемое базой место на диске. При использовании достаточно широких строк, размер базы данных будет пропорционально больше величины журнала транзакций. Поэтому, старайтесь тщательно отслеживать потребление журналом транзакций дискового пространства, что поможет Вам гарантировать целостность данных. Обеспечение нормальной работы механизма регистрации транзакций является одной из первейших обязанностей DBA.

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

Несколько факторов могут влиять на размер журнала транзакций. Первый из таких факторов — оптимизатор запросов (query optimizer). Для одного и того же SQL запроса, модифицирующего данные, через какое-то время план выполнения может измениться в зависимости от статистики распределения данных. Разные планы выполнения могут по разному использовать место в журнале. Следующий фактор — неизбежная внутренняя фрагментация базы данных, которая может привести к разному количеству имеющихся разбитых страницы. Не существует стандартных методов отслеживания подобных процессов, поскольку SQL сервер управляет пользовательскими данными автоматически. Самым простым методом такой оценки является выполнение команды DBCC CHECKTABLE (syslogs), которая возвращает число 2048-байтовых страниц данных в журнале, до и после выполнения тестовых или реальных, модифицирующих данные запросов. Это может дать приблизительную оценку требующегося места в журнале для таких запросов. Обычно, лучше допустить ошибку в сторону избыточности размера журнала, при определении его размера или дискового пространства для него.

У SQL Server 7.0, журнал транзакций имеет возможность расшириться автоматически. Дискретность прироста может задаваться пользователем или может быть разрешено использование всего доступного дискового пространства. Журнал состоит из виртуальных журналов (Virtual Log files, далее VLF). Количество и размер этих виртуальных журналов определяет SQL сервер и это не может быть изменено конфигурационными параметрами. После создания новой базы данных, каждый её физический журнал имеет не менее двух VLF. Иногда администраторы базы данных включают опцию базы данных «truncate log on checkpoint», чтобы избежать переполнения журнала. Назначение этой опции состоит в том, чтобы обеспечить автоматическое усечения журнала транзакций, главным образом для разрабатываемых или тестовых баз данных, которым не обязательна регистрация транзакций для последующего их резервирования. Эта опция не отключает регистрацию транзакций или поддержку целостности, а только заставляет обработчик контрольной точки делать попытку усечения журнала приблизительно каждые 60 секунд. Обратите внимание, что журнал транзакций не будет усекаться при ручной установке команды «truncate log on checkpoint», которая автоматически запускает контрольную точку в базе данных. Эта опция всегда включена для базы данных tempdb, хотя Вы не увидите этого с помощью хранимой процедуры sp_help.Но даже при включённой опцией «truncate log on checkpoint», не исключены случаи, когда журнал транзакций может переполниться. Ниже эти случаи рассмотрены подробно:

1. При исполнении большой, неделимой транзакции, особенно при массовых операциях UPDATE, DELETE или INSERT: Каждый отдельная SQL инструкция будет рассматриваться как самостоятельный, неделимый модуль, который должен применяться или не применяться целиком. По этой причине, все изменения строк должны быть зарегистрированы, и транзакция не может быть усечена на всём протяжении её исполнения. Например, если происходит массовая операция с INSERT, которая продолжается пять минут, записи журнала транзакций, относящиеся к этой транзакции, не могут быть усечены в течении этого времени. Администратор базы данных должен обеспечить достаточно места в журнале для самой большой, массовой операции или должен разбить массовую операцию на меньшие блоки.

2. При наличии в журнале незавершённых транзакций: журнал транзакций может быть усечен только до самой старой незавершённой транзакции. Есть несколько возможных причин появления незавершённых транзакций, большинство из которых относятся к ошибкам прикладного программного обеспечения. Они включают:

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

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

— Ошибки прикладного программного обеспечения, вследствие которых транзакция остаётся не завершённой: обычная причина этого — неправильная обработка вызова dbcancel() DB-Library в пределах определяемой пользователем транзакции. Это наблюдается, когда запрос отменен с помощью dbcancel(), причём, исполняющаяся в это время SQL инструкция прерывается и откатывается назад, а внешняя транзакция не отменяется. Приложение должно гарантировать выполнение ROLLBACK TRANSACTION или инструкции COMMIT TRANSACTION, чтобы полностью отработать и закрыть транзакцию. Не выполнение этого требования, часто приводит к появлению ошибки 3902: The commit transaction has no corresponding BEGIN TRANSACTION. Это можно использовать в приложении, чтобы в ответ выполнить SELECT @@TRANCOUNT, и определить, какая вложенная транзакция существует на этом уровне. Однако, приложение не должно делать это вслепую, что бы всё-таки выполнить COMMIT/ROLLBACK и получить @@TRANCOUNT=0. Это не желательно потому, что @@TRANCOUNT может указывать не на ту транзакцию, которую ожидалось определить. Т.е. приложение может не увидеть запись о транзакции вложенного уровня, относительно той транзакции, которая стала причиной ошибки приложения. И тогда, инициация COMMIT/ROLLBACK может привести к завершению или откату не завершённой транзакций, так как приложение не может знать, какие транзакции завершены на вложенном уровне. Для разрешения таких проблем, программист должен так отладить приложение и возможные хранимые процедуры, что бы полностью исключить возможностьљ непреднамеренного прерывания вложенных транзакций.

— Сетевые ошибки, из-за которых SQL сервер не получает уведомление о потере сетевого подключения. Если клиентская рабочая станция зависает, перезагружается, или будет выключена во время исполнения определяемой пользователем транзакции, средства сетевого уровня должны сообщить об этом SQL серверу. Если сеть не отреагирует на это должным образом, SQL сервер будет считать, что клиент продолжает работу, и выполняющаяся клиентская транзакция будет по-прежнему активна. Эта сетевая проблема должна контролироваться администратором, который может с помощью sp_who, sp_lock, или сетевых утилит определить такие оборванные сетевые соединения и уничтожать их вручную.

— Транзакции, не завершенные из-за блокировок. В многопользовательской среде исполняющаяся транзакция может обратиться к блокированным другим процессом данным. В этом случае, транзакция останется открытой и не позволит выполнить усечение журнала. Чтобы это обнаружить, программист или администратор базы данных должен использовать sp_who, sp_lock, или другие инструментальные средства, для соответствующего анализа. В большинстве случаев, проблемы блокировок могут быть снижены или устранены через изменение запроса, индекса, и изменение дизайна базы данных.

— Неудачные попытки отмены запросов на модификацию данных. Если приложение исполняет dbcancel(), но запрос не отменен из-за проблем в сети или SQL сервера, запрос продолжает выполняться, и транзакция останется открытой. Если Вы подозреваете наличие такой проблемы, используете sp_who, чтобы увидеть, отменён ли запрос. При попытке такой отмены для клиента, работающего через TCP/IP сокет, пробуйте проверить подключение клиента по named pipes, или запустите клиентское приложение на сервере, используя local pipes. Это поможет определить, вызвана ли проблема сетью или SQL сервером.

3. Чрезмерная утилизация ресурсов сервера при усечении журнала во время отработки Checkpoint: Хотя журнал транзакций усекается каждые 60 секунд, количество усекаемых за это время транзакций ограничено. Вероятность того, что контрольная точка не успеет пробежать за отведённое ей время весь журнал, чрезвычайно мала, и Вы должны предварительно рассмотреть все другие возможные причины возникновения проблемы. Однако, превысить максимальную норму усечения всё-таки возможно, если большое количествољ клиентов будет одновременно выполнять модификацию большого количества данных. Подобные проблемы решаются путём пересмотра структуры приложения, с целью уменьшения числа модифицируемых клиентом строк, что всегда должно быть одной из первостепенных задач дизайна для любой базы данных. Если это не выполнимо, система может быть масштабирована в целях увеличения пропускной способности дисковой подсистемы (I/O), например striping, дополнительные диски или дисковые контроллеры, и т.д. Обычно, это видно потому, что процесс отработки контрольной точки потребляет больше времени, чем положено при исполнении команды DUMP TRANSACTION, поскольку он пытается не отставать от усечения журнала. Как только порог усечения превышен (см. ниже) Вы увидите, что обработчик контрольной точки перестал делать попытки усечения журнала базы данных, пока не произойдёт очистка журнал транзакций.

4. Превышение порога усечения. Обработчик контрольной точки по существу делает DUMP TRANSACTION WITH TRUNCATE_ONLY. Так же, как это делается вручную. Но контрольная точка не всегда будет успевать появится до того, как журнал будет заполнен до некоторого критического значения. Например, чрезмерный объём модификаций данных, выполненный за очень короткое время, может заполнить журнал транзакций на 95% между двумя очередными контрольными точками. Когда обработчик контрольной точки сделает попытку усечения почти заполненного журнала транзакций, это может привести к невозможности самого усечения. Такое случается потому, что усечение журнала само является регистрируемой операцией. Единственное решение в этом случае состоит в том, чтобы использовать DUMP TRANSACTION WITH NO_LOG, т.е. вручную запустить усечение журнала транзакций. Не рекомендуется использование опции NO_LOG кроме случаев, когда это абсолютно необходимо, поскольку операция не будет регистрироваться в журнале и произошедший во время выполнения такой операцииљ отказ системы, может породить ошибки в базе данных.

5. Комбинации представленных выше четырёх причин. Например, при нормальных условиях в интенсивно модифицируемой среде, норма усечения обработчика контрольной точки может препятствовать заполнению журнала. Если временно открытая транзакция, вызванная любой из вышеупомянутых причин (например, блокировки) приведёт к заполнению журнала, к примеру, до 50 %, останется гораздо меньшее возможностей для обработки других возможных модификаций данных, что делает более вероятным достижение порога усечения, после которого автоматическое усечение журнала будет невозможно. Транзакции в tempdb регистрируются так же, как и в любой другой базе данных. Опция TRUNCATE LOG ON CHECKPOINT, в большинстве случаев,љ остаётся включённой для tempdb. Из-за этого, журнал транзакций постоянно усекается и не переполняется. Однако, любая из вышеупомянутых причин может заставить журнал базы tempdb переполниться. Конфигурация Tempdb обычно подразумевает размещение базы и журнала в одном файле (sysusages.segmap=7), вследствие чего данные и регистрационные операции будут конкурировать за одно и то же доступное дисковое пространство. Некоторые конструкции Transact-SQL, такие, как GROUP BY, ORDER BY DESC и т.д., будут автоматически требовать место в tempdb для своей работы. Это порождает неявные записи BEGIN TRANSACTION в журнале tempdb, отвлекая на это дополнительное место. Такаяљтранзакция по базе tempdb будет продолжаться до завершения породившей её транзакции в пользовательской базе, что может задержать усечение журнала tempdb в течение этого времени. Если транзакция в пользовательской базе приостановлена по какой-либо причине, (включая блокировки или приложение, не обрабатывающее dbnextrow() для завершения), транзакция в tempdb, как и в предыдущем случае, останется открытой, мешая усечению журнала tempdb. Для решения такой проблемы, программист должен соответствующим образом отладить приложение и/или разрешать проблемы параллельного исполнения транзакций, которые порождают эту ситуацию.

Усечение журнала транзакций SQL Server 7.0 происходит путём усечения виртуальных журналов Virtual Log Files (VLF) из которых, как из кирпичиков, состоит журнал транзакций. Если в журнале существует активная транзакция, расположенная резидентно в одном из VLF, этот виртуальный журнал не может быть усечен. Если активные транзакции есть во всех виртуальных журналах, журнал транзакций не может быть усечен. Если включена опция автоматического роста журнала и есть достаточно места на диске, где находится журнал, и максимальный размер файла журнала ещё не достигнут, журнал транзакций будет увеличен на величину, указанную в свойствах журнала.

Нижеследующие замечания рассматривают тот случай, когда происходит усечение журнала при исполнении SQL запроса, в зависимости от того, включена ли опция TRUNCATE LOG ON CHECKPOINT:

— Если опция TRUNCATE LOG ON CHECKPOINT включена, и будет установлено во время запуска сервера, что журнал транзакций переполнен — его содержимое будет автоматически уничтожено

В MS SQL очистка журнала транзакций необходима в том случае, если настроена полная модель восстановления базы данных. Если журнал транзакций переполнился, то ваша база данных откажется работать и будет выдавать ошибку: «журнал транзакций для базы данных заполнен». Почему такое происходит и как этого избежать? Рассмотрим два решения, которые помогут быстро устранить ошибку и продолжить работу с базой.

Увеличиваем размер журнала транзакций.

Запускаем SQL Server Management Studio, заходим в свойства базы и выбираем пункт .

Для типа файла «Журнал» увеличиваем максимальный размера файла для авторасширения.

Сжимаем файл журнала транзакций.

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

Запускаем SQL Server Management Studio, заходим в свойства базы и выбираем пункт . Модель восстановления выбираем «Простая» и нажимаем ОК.

Далее правой клавишей мышки по базе и выбираем из контекстного меню — —

Тип сжатия: Журнал
Операция сжатия: Реорганизовать файлы, перед тем как освободить неиспользуемое место
И указываем размер до которого необходимо сжать, например 0.

Теперь нужно вернуться в свойства базы к пункту и переключить модель восстановления на «Полная».

Очень часто это связано с тем, что очень быстро увеличивается LOG-файл базы данных MS SQL.
Для того, чтобы понять причины быстрого роста необходимо понять вообще зачем нужен этот LOG-файл. Давайте рассмотрим структуру файлов, попытаемся сделать это упрощено.
Любая база данных MS SQL содержит файлы с двумя расширениями *.mdf и *.log.
MDF-файл — это файл с данными. Содержит сведения, необходимые для запуска базы данных, и ссылки на другие файлы в базе данных. Их может быть несколько.
LOG-файл — это файл журнала транзакций. Файлы журнала транзакций содержат сведения, используемые для восстановления базы данных. Для файлов журнала транзакций рекомендуется расширение LDF. LOG-файлов может быть несколько.
Справедливости ради, еще выделяют и NDF-файлы, но при работе в 1С они не используются (вторичные файлы данных, являются не обязательными).

Как это работает

Теперь рассмотрим как работает запись в БД MS SQL.
Сервер 1С:Предприятия записывает данные в mdf-файл(ы) и параллельно все транзакции связанные с изменением данных в mdf фиксируются в журнал транзакций или log-файл(ы). Причем если по какой-то причине в базе данных произойдет сбой, log-файл поможет нам восстановить данные практически на любой момент времени.
Важно понимать, что в самих log-файлах нет данных, там фиксируются ТОЛЬКО транзакции (действия). Грубо говоря запросы, которые изменяют данные в MDF. Упрощенно, это «тетрадь» куда записываются все изменения (добавления, изменения, удаления) в таблицах базы данных.
Все, конечно, гораздо сложнее, но тут мы приводим упрощенную схему.
Имея такую «тетрадочку» с логом транзакций можно «листать» изменения и добиться того, что система может «откатиться» на нужное время.

Почему растет LOG-файл (ldf)?

Понятное дело, что если записываются все изменения то лог-файл просто обязан расти. Всякие фоновые задания, которые пишут по одной записи в какой-нибудь регистр в 1С делают изменения в данных, а следовательно, растет размер лога. Причем, чем больше изменений, тем больше растет ldf-файл. А такая операция, как обновление информационной базы часто ведет вообще к огромному росту, так как при обновлении информационной базы происходит много изменений в данных и это все фиксируется.
Так же на размер файла транзакций влияет и интенсивность работы пользователей. Если мы открываем один и тот же документ и каждый раз меняем один реквизит и записываем документ, то в mdf-файле ничего изменяться не будет, а вот в файле транзакций, будет 10 записей с транзакциями, каждая из которых что-то меняет.
В MS SQL возможно использование нескольких моделей восстановления данных. Это, собственно, механизм, который и отвечает за журнал транзакций.
Полная модель восстановления (Full) — фиксируются ВСЕ транзакции. При этой модели будет максимальный рост журнала транзакций, но при этом риска данных журналов практически нет.
С неполным протоколированием — похожа на полную модель восстановления, но уменьшает место, занимаемое журналами, за счет неполного протоколирования большинства массовых операций. Возможно восстановление до конца любой резервной копии.
Простая модель (Simple) — данные по журналам практически не фиксируются.
Посмотреть на вашу модель можно открыв Microsoft SQL Server Managment Studio, щелкнув на нашу БД правой кнопкой:

Методы борьбы с размерами файла транзакций MS SQL

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

SHRINK (сжатие) лога транзакций

В простонародье это «шринк» файла. Это «обрезка» файла и удаление оттуда данных транзакций. Действительно, если журнал транзакций нам нужен только при возникновении сбоев, может имеет смысл настроить частое резервное копирование, а лог транзакций вообще отключить? Это возможно.

Шаг 1. Сжатие log-файла

Откроем Microsoft SQL Server Managment Studio и «сожмем» log-файл.
После этого откроется окно:
Тут можно подобрать нужные параметры сжатия и освободить используемое место журнала транзакций. При этом ваши данные в базе данных никак не пострадают. Здесь мы имеем дело исключительно с журналом транзакций и как мы сказали выше, база данных вообще может обойтись без журнала в простой модели протоколирования, а значит очистка это вполне нормально.

Шаг 2. Переключение на простую модель восстановления

Если вы хотите на корню решить вопрос с ростом логов, то вы можете переключить модель восстановления на простую (Simple). На самом первом скриншоте выше, переключите модель на простую и нажмите OK.
Так же возможно выполнения вот такого запроса:
USE BACKUP LOG TO DISK=’NULL’ GO DBCC SHRINKFILE (, 1) GO
Этот способ наладить работу с размером логов имеет как плюсы (быстро и навсегда решает проблему роста логов), так и минусы. Например, вы теряете возможность оперативно откатывать изменения.
Важно, что речь не о полных бэкапах, а именно когда речь идет о восстановлении по данным журнала транзакций. Как пример, бэкап был вчера, а сегодня после обеда, когда было внесено 50 документов, вы случайно очистили важный документ и хотите вернуть его. Это можно сделать с помощью логов.
Некоторые считают такой метод, не верным и отчасти это так, но если вы и раньше не пользовались восстановлением данных по данным журнала транзакций, то я не думаю, что это вообще проблема для вас 🙂

Создание резервных копий журнала транзакций

Кроме способа описанного выше в MS SQL есть возможность создавать резервные копии журнала транзакций. Это можно сделать из Microsoft SQL Server Managment Studio:
А следующим шагом:
Важно! Делая бэкап журнала транзакций мы усекаем его. MS SQL понимает, что копия журнала сделана, а значит можно уменьшить размер log-файла.
Это же самое можно выполнить запросом:
BACKUP LOG TestUIT TO DISK = ‘C:\Backups\Logs\TestUIT.TRN’ WITH STATS GO Плюсы второго вариант очевидны, вы всегда можете восстановить данные (надо вам сделать эксперименты самостоятельно с этим), написать скрипт, который может это сделать автоматически. При этом после каждого бэкапа размер журнала транзакций будем сокращен. Если вам не нужно, вы всегда можете использовать первый вариант и простую модель восстановления БД.
Вот такие дела, друзья.
Всем удачи и берегите ваш MS SQL!

Решение

Создание меньшего файла журнала должно быть зарезервировано для сценариев, в которых он столкнулся с неожиданным ростом, которого вы не ожидаете повторения. Если файл журнала снова вырастет до того же размера, не так уж много делается, сократив его временно. Теперь, в зависимости от целей восстановления вашей базы данных, это действия, которые вы должны предпринять.

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

Если вы заботитесь о своевременном восстановлении

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

Предположительно, ваша база данных находится в FULLрежиме восстановления. Если нет, убедитесь, что это:

ALTER DATABASE testdb SET RECOVERY FULL;

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

DECLARE @path NVARCHAR(255) = N’\\backup_share\log\testdb_’ + CONVERT(CHAR(8), GETDATE(), 112) + ‘_’ + REPLACE(CONVERT(CHAR(8), GETDATE(), 108),’:’,») + ‘.trn’; BACKUP LOG foo TO DISK = @path WITH INIT, COMPRESSION;

Обратите внимание, что это \\backup_share\должно быть на другой машине, которая представляет собой другое базовое устройство хранения. Резервное копирование этих данных на один и тот же компьютер (или на другую машину, использующую одни и те же базовые диски или другую виртуальную машину, находящуюся на одном физическом узле) на самом деле не поможет вам, поскольку, если машина взорвалась, вы потеряли свою базу данных и его резервные копии. В зависимости от вашей сетевой инфраструктуры может возникнуть больше смысла резервировать локально, а затем передавать их в другое место за кулисами; в любом случае вы хотите как можно быстрее получить их с основной машины базы данных.

Теперь, когда у вас есть регулярные резервные копии журналов, должно быть разумным сжать файл журнала на что-то более разумное, чем то, что оно взорвалось до сих пор. Это не означает, что запуск SHRINKFILEснова и снова до тех пор, пока файл журнала не станет 1 МБ — даже если вы часто выполняете резервное копирование журнала, ему все же необходимо разместить сумму любых одновременных транзакций, которые могут произойти. События автосогласования файлов журнала дороги, потому что SQL Server должен обнулить файлы (в отличие от файлов данных, когда включена инициализация мгновенных файлов), и пользовательские транзакции должны ждать, пока это произойдет. Вы хотите как можно меньше сделать эту процедуру сокращения роста и сокращения, и вы, конечно же, не хотите, чтобы ваши пользователи платили за нее.

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

Итак, вам нужно придумать практический размер для вашего файла журнала. Никто здесь не может сказать вам, что это такое, не зная намного больше о вашей системе, но если вы часто сокращаете файл журнала, и он снова растет, хороший водяной знак, вероятно, на 10-50% выше, чем самый большой из них , Предположим, что это достигает 200 МБ, и вы хотите, чтобы любые последующие события автоматического разгона составляли 50 МБ, тогда вы можете настроить размер файла журнала таким образом:

USE ; GO ALTER DATABASE Test1 MODIFY FILE (NAME = yourdb_log, SIZE = 200MB, FILEGROWTH = 50MB); GO

Обратите внимание, что если файл журнала в настоящее время> 200 МБ, вам может потребоваться выполнить это сначала:

USE yourdb; GO DBCC SHRINKFILE(yourdb_log, 200); GO

Если вы не заботитесь о своевременном восстановлении

Если это тестовая база данных, и вы не заботитесь о восстановлении в определенный момент времени, вы должны убедиться, что ваша база данных находится в SIMPLEрежиме восстановления.

ALTER DATABASE testdb SET RECOVERY SIMPLE;

Постановка базы данных в SIMPLEрежим восстановления гарантирует, что SQL Server повторно использует части файла журнала (по сути, поэтапный отказ от неактивных транзакций) вместо того, чтобы расти, чтобы вести запись обо всех транзакциях (например, FULLвосстановление происходит до тех пор, пока вы не создадите резервную копию журнала). CHECKPOINTсобытия помогут контролировать журнал и убедиться, что он не должен расти, если вы не генерируете много активности t-log между CHECKPOINTs.

Затем вы должны сделать абсолютно уверенным, что этот рост журналов был действительно вызван аномальным событием (скажем, ежегодной уборкой весны или восстановлением ваших самых больших индексов), а не из-за обычного повседневного использования. Если вы сократите файл журнала до смехотворно малого размера, и SQL Server просто должен его снова вырастить, чтобы приспособить вашу нормальную деятельность, что вы набрали? Могли ли вы использовать это дисковое пространство, которое вы освободили только временно? Если вам требуется немедленное исправление, вы можете запустить следующее:

USE yourdb; GO CHECKPOINT; GO CHECKPOINT; — run twice to ensure file wrap-around GO DBCC SHRINKFILE(yourdb_log, 200); — unit is set in MBs GO

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

Некоторые вещи, которые вы не хотите делать

• Создайте резервную копию журнала с помощью TRUNCATE_ONLYопции и затемSHRINKFILE . Во-первых, этот TRUNCATE_ONLYпараметр устарел и больше не доступен в текущих версиях SQL Server. Во-вторых, если вы находитесь в FULLмодели восстановления, это разрушит цепочку журналов и потребует новой полной резервной копии.

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

• Используйте параметр «Усадочная база данных» . DBCC SHRINKDATABASEи вариант плана технического обслуживания сделать то же самое — плохие идеи, особенно если вам действительно нужно только решить проблему с журналом. Направьте файл, который вы хотите настроить, и настройте его самостоятельно, используя DBCC SHRINKFILEили ALTER DATABASE … MODIFY FILE(примеры выше).

• Сократите файл журнала до 1 МБ . Это выглядит заманчиво, потому что, эй, SQL Server позволит мне делать это в определенных сценариях и смотреть на все пространство, которое он освобождает! Если ваша база данных не предназначена только для чтения (и это значит, вы должны ее пометить как таковое ALTER DATABASE), это абсолютно просто приведет ко многим ненужным событиям роста, поскольку журнал должен учитывать текущие транзакции независимо от модели восстановления. Какой смысл временно освобождать это пространство, так что SQL Server может вернуть его медленно и мучительно?

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

Быть инициативным

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

Наихудшие возможные настройки здесь — рост 1 МБ или 10% роста. Довольно забавно, что это стандартные по умолчанию для SQL Server (о которых я жаловался и просил об изменениях безрезультатно ) — 1 МБ для файлов данных и 10% для файлов журналов. Первый из них слишком мал в этот день и в возрасте, а последний приводит к более длительным и продолжительным событиям каждый раз (скажем, ваш файл журнала 500 МБ, первый рост — 50 МБ, следующий рост — 55 МБ, следующий рост — 60,5 МБ , и т. д. — и на медленном вводе-выводе, поверьте, вы действительно заметите эту кривую).

дальнейшее чтение

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

Сообщение в блоге, которое я написал в 2009 году, когда я увидел несколько сообщений «вот как сжимать файл журнала» .

Сообщение в блоге Брент Озар написал четыре года назад, указывая на несколько ресурсов в ответ на статью журнала SQL Server Magazine, которая не должна была публиковаться .

Сообщение блога Пола Рэндала, объясняющее, почему важно обслуживание t-журнала, и почему вы также не должны сокращать свои файлы данных .

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

Сегодня в блоге мы публикуем третью, заключительную часть статьи о новых возможностях СУБД Oracle. Мы уже рассказали, как новая мультиарендная архитектура позволяет упростить администрирование и повысить эффективность использования оборудования, а также как ускоряется выполнение аналитических и других типов запросов и повышается надежность базы данных с помощью In-Memory Database и шардинг. В финальной части статьи мы уделим внимание механизмам повышения производительности и автономности, в том числе автоматической индексации, AutoML, блокчейн-таблицам и др.

В каждой новой версии Oracle Database происходит оптимизация алгоритмов, ускоряются многие операции, повышается производительность выполнения SQL и PL/SQL кода. Мы предлагаем обзор наиболее ценных и важных механизмов.

Статистика реального времени. Скорость выполнения SQL-запроса зависит от плана его выполнения. Оптимизатор запросов (cost-based optimizer) строит планы, в том числе, на основе собранной статистики (количество записей, наличие индексов, кардинальность и т.д.). При устаревшей, неверной статистике план будет плохим, поэтому администраторы баз данных должны постоянно обновлять статистику. Сейчас, например, это можно делать автоматически, в окне обслуживания (например, ночью). Но операция сбора статистики влияет на производительность базы даных, а в период между двумя ее сборами статистика быстро устаревает.

Для решения этой проблемы был реализовал механизм сбора статистики в реальном времени – Real Time Statistic. Теперь часть наиболее важной статистики («базовая статистика») хранится в оперативной памяти, автоматически обновляется при выполнении каждого оператора языка манипулирования данными (Data Manipulation Language, DML) и периодически сбрасывается на диск. Каждый новый запрос использует свежую статистику (она формируется на основе статистики из памяти и из словаря базы данных) и выполняется с оптимальным планом запроса.

Статистика собирается на лету, поэтому, чтобы не тормозить DML, собирается только базовая. Полную статистику можно собирать реже, так как она теперь меньше влияет на качество выполнения запросов.

Дополнительным бонусом от статистики реального времени является то, что многие агрегационные функции (min, max, count), если в запросе нет предложения where, не читают данные с диска, а берут их из базовой статистики в памяти. Это резко ускоряет выполнение запроса.

Автоматическое создание индексов. Это наиболее интересный новый механизм. Индекс в базе данных служит для ускорения выполнения SQL запросов. Считается, что основную массу индексов строят разработчики приложения на этапе создания приложения, а администраторы должны лишь поддерживать эти индексы. Однако, на практике мы видим, что администраторы и разработчики, ответственные за обеспечение высокой производительности приложения, постоянно создают все новые индексы на этапе эксплуатации. В результате некоторые индексы дублируют друг друга, занимая место в базе данных. Но, главное, они замедляют операции вставки, удаления и изменения данных, которые требуют изменения индексов.

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

1. Захват (Capture). Во время работы приложения СУБД периодически (каждые 15 мин) захватывает выполняемые SQL-запросы и помещает их в специальный репозиторий (Automatic SQL Tuning Set, ASTS). Захватываются не только тексты SQL, но и планы выполнения, переменные привязки, статистика выполнения и т д.
2. Идентификация кандидатов (Identify). Новые захваченные запросы анализируются с помощью алгоритмов искусственного интеллекта (ИИ) для определения индексов-кандидатов, которые могут улучшить выполнение этих запросов. Кандидаты создаются в базе данных как неиспользуемые индексы (Unusable) – по сути это лишь описания индексов в словаре базы данных (метаданные). Кроме кандидатов на добавление выявляются кандидаты на удаление, так как новые индексы-кандидаты могут перекрывать существующие индексы.
3. Верификация (Verify). Оптимизатор запросов проверяет, что новые индексы действительно влияют на планы выполнения запросов. Оказывающие влияние кандидаты реально создаются в базе данных, но помечаются как невидимые (сессиям они пока не видны). Захваченные запросы (Select) выполняются в отдельной сессии с учетом новых индексов, и по ним собирается статистика.
4. Принятие решения (Decide). Если производительность всех запросов, использующих новый индекс, улучшилась, то индекс помечается как видимый для всех сессий и далее используется как обычно при построении планов запросов. Если производительность всех запросов, использующих новый индекс, ухудшилась, то индекс удаляется. Если же производительность одной части запросов улучшилась, а другой — ухудшилась, то индекс становится видимым только для тех запросов, которые он улучшает. Остальные запросы его не видят и не используют.
5. Онлайн проверка (Online validation). При дальнейшем использовании приложений меняется статистика и нагрузка, появляются новые запросы, поэтому процедура выявления и построения новых автоматических индексов периодически повторяется, чтобы учесть изменения.
6. Мониторинг. СУБД постоянно отслеживает использование автоматических индексов и удаляет давно не используемые (политику хранения задает администратор). Кроме того, в базе данных могут сосуществовать автоматические и ручные индексы, а политика удаления для них может различаться.

Администратор может включить/выключить автоматическое создание индексов; указать схемы базы данных, для которых этот режим не работает; задать, в каком табличном пространстве следует создавать эти индексы; построить отчет о полезных рекомендуемых индексах без их создания.

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

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

Карантин для ресурсоемких запросов. Сложные запросы сильно нагружают систему и мешают работать другим пользователям. При изменении плана выполнения или объема данных запрос, который раньше работал быстро, может стать помехой для других. Поэтому в менеджере ресурсов СУБД реализована возможность отправлять в карантин запросы, когда они потребляют больше ресурсов, чем позволено. Такой запрос прерывается и при последующих запусках не выполняется. При изменении ситуации, например, когда планка ограничения ресурсов будет поднята, карантин будет снят и запрос сможет выполняться. В качестве пороговых значений для помещения запроса в карантин могут использоваться процессорное время, общее время выполнения, объем ввода/вывода, число физических или логических операций ввода/вывода.

Работа с энергонезависимой памятью

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

Появление энергонезависимой памяти (Persistent Memory, PMEM) революционно меняет правила игры и, очевидно, приведет к изменению архитектуры СУБД. PMEM по стоимости дешевле оперативной памяти, но по скорости близка к ней. И она не теряет данные после выключения питания. Таким образом, в будущем можно будет всю базу данных поместить в PMEM и отказаться от дорогостоящих операций ввода/вывода, кэшей в памяти, подкачки блоков и т д.

Oracle Database, начиная с версии 20с, умеет работать с PMEM. Она использует ее двояко. Во-первых PMEM Intel Optain помещается в ячейки хранения машины баз данных Oracle Exadata (рис. 1) .

Рисунок 1. Уровни хранения данных в Exadata

Таким образом, теперь в Exadata реализовано четыре уровня хранения данных:

• диски (холодные данные);
• флеш-память (кэширование теплых данных);
• PMEM (кэширование горячих данных);
• оперативная память.

При этом обращение экземпляра СУБД к данным PMEM происходит не по стандартному протоколу ROCE (замена Infiniband), а по специальному протоколу RDMA напрямую. Это позволяет обойти сетевой стек и стек ввода/вывода и снизить задержки в десятки раз. Кроме того, Exadata записывает журналы транзакций (redolog) на PMEM. Все это значительно ускоряет операции ввода/вывода.

Но пользователи Oracle Database 20с могут и на обычном (не Exadata) оборудовании использовать преимущества PMEM. В энергонезависимой памяти можно размещать журналы транзакций (redo logs) и файлы данных. SQL-запросы выполняются напрямую над данными файловой системы PMEM по специальным алгоритмам. Поскольку данные из PMEM не считываются в буферы оперативной памяти, соответствующие накладные расходы исключаются. Это позволяет значительно ускорить выполнение важных запросов и повысить производительность всей СУБД.

Новые возможности Oracle Database 20c

Несмотря на то, что версия Oracle Database 20с является промежуточной, она обладает множеством новых возможностей. Выделим лишь самые интересные из них (рис. 2). (DB Nest, In-Memory Vector Join и Persistent memory рассматривались во второй части.)

Рисунок 2. Инновации в Oracle Database 20c

Блокчейн-таблицы. Это обычные таблицы базы данных, открытые только на чтение и добавление строк – изменить данные в этих таблицах нельзя. Удалить строки и всю таблицу можно только через заданный срок (или никогда), который определяет администратор. Строки таблицы связаны в цепочки (как в обычном блокчейне) – в каждой строке хранится хэш-значение предыдущей строки цепочки. Тем самым гарантируется, что строки цепочки не изменились, так как для изменения одной строки придется перестроить всю цепочку. Пользователи могут сохранить копии хэш-значений для проверки, что интересующие их значения не были изменены. Это простой и удобный способ организовать централизованный блокчейн в базе данных для приложений/пользователей, не доверяющих друг другу.

Native JSON в базе данных. Таблицы могут содержать колонки с документами JSON. Элементы данных в документах JSON можно обновлять. Теперь документы хранятся не в текстовом, а в бинарном представлении, что сильно ускоряет работу с ними. Кроме того, и опция In-memory, и ячейки Exadata умеют работать с колонками, содержащими документы JSON.

SQL-макросы в запросе. SQL-макросы позволяют упростить написание SQL-запроса. SQL-макрос – это функция, которая на выходе возвращает текст, который подставляется в текст исходного SQL-запроса. Например, это может быть динамически сформированный текст подзапроса или предикат условия для WHERE. Макросы делятся на табличные (подставляются в выражение FROM и формируют имя таблицы/таблиц или подзапрос для генерации таблицы) и скалярные (подставляются в WHERE/HAVING, GROUP/ORDER BY и формируют имя колонки/группы колонок или целого предиката для WHERE). SQL-макросы позволяют проще писать то, что раньше реализовывалось с помощью динамического SQL.

Автоматизация машинного обучения (AutoML в OML4Py). Опция Advanced Analytics теперь является бесплатной компонентой СУБД Oracle. В ее состав входят средства создания, обучения и использования моделей для машинного обучения (Machine Learning, ML). В СУБД имеется большая библиотека таких моделей (преимущественно на языке Python) и средства построения и настройки моделей (рис. 3). Однако выбор, построение и обучение таких моделей – сложная задача и требует высокой квалификации в области машинного обучения.

AutoML позволяет упростить создание моделей машинного обучения. Она помогает сделать следующее:

• выбрать правильную и наиболее подходящую модель для конкретной задачи (выдает рекомендации и список подходящих моделей);
• выбрать необходимые атрибуты (колонки таблицы) в качестве входных данных для модели (рекомендует, какие атрибуты и как сильно влияют на результат – чем меньше значимых атрибутов, тем быстрее и проще модель)
• выбрать параметры настройки для моделей (features).

Сегодня алгоритмы машинного обучения очень широко используются в приложениях. Механизмы AutoML в OML4Py (Oracle Machine Learning for Python) помогут неспециалистам в области машинного обучения создавать и использовать модели.

Рисунок 3. Алгоритмы машинного обучения СУБД Oracle

Заключение

Это только часть новых возможностей СУБД Oracle 12.2, 18c, 19c и 20c. Полный список можно найти в документации по конкретной версии СУБД. Некоторые из рассмотренных новых возможностей требуют больших дополнительных вычислительных ресурсов (например, автоматические индексы, онлайн статистика, memoptimized таблицы и т. д.), поэтому они реализованы на платформе Exadata. Список возможностей для конкретной платформы также можно найти в документации на СУБД Oracle.

Новые версии СУБД работают в корпоративном ЦОД (on-premise), в публичном облаке Oracle Cloud и также доступны на Exadata Cloud@Customer и в частном регионе публичного облака Oracle Dedicated Region Cloud@Customer, которые размещаются в клиентском дата-центре, но полностью обслуживаются Oracle. На базе этих СУБД (начиная с 18с) также реализованы автономные базы данных Oracle Autonomous Database. Они доступны как в публичном облаке Oracle, так и на Exadata Cloud@Customer в дата-центрах клиентов по модели «Oблако Oracle у клиента». Каждая автономная база данных – это PDB.

Новая инициатива Oracle – облачные сервисы Always Free – позволяет каждому желающему бесплатно заказать на неограниченный срок две автономные БД. На них удобно изучать новые возможности, вести разработку, тестировать свои приложения.