Firebird 2.0 на полную катушку
Мониторинг баз данных Firebird.
0.Преамбула
1. Автор не претендует на роль истины в последней инстанции. Задачи и методы их решения,
изглагаемые в статье, являются следствием опыта, полученного автором при написании программ, не являются панацеей и если
кто-нибудь может предложить более оптимальное решение и поделиться им с нами, автор будет только рад :)
2. Автор является сторонником движения OpenSource и исповедует один из основных лозунгов OpenSource "Знания человека принадлежат миру", и поэтому Вы вправе свободно копировать, цитировать и использовать данный текст по своему усмотрению. Никаких ограничений на данный текст, изложенные в нем материалы, алгоритмы и методики, не накладывается.
2. Автор является сторонником движения OpenSource и исповедует один из основных лозунгов OpenSource "Знания человека принадлежат миру", и поэтому Вы вправе свободно копировать, цитировать и использовать данный текст по своему усмотрению. Никаких ограничений на данный текст, изложенные в нем материалы, алгоритмы и методики, не накладывается.
1.Введение
По администрированию баз даннных InterBase/Firebird написано достаточно много публикаций в основном касающихся Service API (API-функций по управлению базами данных и SQL-сервером), мы не будем останавливаться на них. Целью данной статьи является рассмотрение новых функциональных особенностей Firebird, которые появились в этом SQL-сервере в версии 2.1. Это так называемые "Виртуальные таблицы" или системные таблицы с префиксом MON$ в имени. "Виртуальными", согласно документации, их назвали потому, что они не существуют в базе данных, просто API-функции доступа к базе данных эмулируют присутствие этих таблиц, а служебная информация о текущих процессах в базе данных, располагается в соотвествующих столбцах этих таблиц. На мой взгляд такой подход со стороны разработчиков к реализации мониторинга баз данных является самым удобным, так как не требует разработки дополнительного API-интерфейса (и, соответственно, переработки клиентских VCL-библиотек доступа), а доступ к функциональности можно осуществлять при помощи старых VCL-библиотек (IBX, IBO, FIBPlus), для этого надо лишь поменять gds32.dll(fbclient.dll) на соответствующую текущей версии сервера.
Многие разработчики сталкивались с отладкой SQL-скриптов, выполняющих какие-либо продолжительные действия или строящих сложные отчеты. И не всегда, к сожалению, написать скрипт получается с первого раза, в некоторых случаях он приводит к зависанию процесса и "торможению" остальных процессов SQL-сервера (имеется в виду архитектура Classic Server). Казалось, нет ничего проще: узнать зависший процесс в диспетчере задач в Windows или вызвав ps ax в LINUX и прибить зависший процесс. Но если в этот момент к базе данных еще подключено 50-70 пользователей (причем у всех работа начинает дико тормозить, шеф уже полез в сейф за пистолетом :), определить кто есть кто в этом списке достаточно проблематично. Нужно лезть в монитор файерволла, смотреть какой PID процесса подключенного от твоего компьютера, потом убивать этот процесс. Не спешите кидать в автора камни с криком "Кто же отлаживает скрипты на реальной базе ?". Отлаживают, причем все, только не признаются в этом. :) А если серьезно, то существует иногда необходимость при разработке систем реального времени или отчетов реального времени, обновляющихся с определенным интервалом, производить отладку на реальных данных потому, что требуется обеспечить ввод этих самых данных с нужной частотой. И обеспечить его лучше, чем это могут сделать сами пользователи системы, вряд ли получиться.
Или, в конце концов, выяснить, какой из клиентов "завис" и принудительно завершить его запрос. Особенно это полезно в тех случаях, когда главный бухгалтер вдруг решит составить годовой отчет в конце рабочего дня, а не в положенное ей штатным расписанием утреннее время, в то время как идет массовая "набивка" накладных и "подгрузка" данных из распределенных филиалов.
Давней мечтой программистов баз данных InterBase/Firebird был функционал мониторинга активных транзакций и запросов с возможностью принудительного завершения последних. И вот наконец дождались: разработчики Firebird реализовали это возможность в Firebird v2.1. Об этой возможности далее и пойдет речь в статье.
2.Постановка учебной задачи
В качестве учебной задачи мы поставим написание программы для мониторинга базы данных при помощи системных таблиц мониторинга, а также управление "зависшими" запросами, в среде Borland Delphi 7.
3.Ограничения учебной задачи
При решении учебной задачи использовались:
1. Firebird SQL Server v2.1.0.15972 Classic Edition
2. Утилита администрирования базами данных InterBase/Firebird IBExpert v2007.05.03
3. Компилятор языка Паскаль Delphi v7
4. Библиотека доступа к базам данных InterBase/Firebird InterbaseExpress, входящая в поставку компилятора Delphi 7.
5. Библиотека EhLib, компонент TDBGridEh для отображения данных. Хотя, в принципе, подойдет и любой другой DBGrid.
С целью упрощения кода для восприятия, все действия и методы размещены в обработчиках событий OnClick, но это не является основанием для того, чтобы преждевременно записывать автора в ряды "баттоно-кидателей" :)
4.Небольшое отступление
Прежде, чем приступить к рассмотрению данной функциональности Firebird, стоит обратить внимание на то,что функциональные особенности, описанные в статье, являются нововведением Firebird v2.1, то есть Вам следует обновить версию сервера до указанной. Более того, как замечено на практике, таблицы мониторинга MON$* не совсем виртуальные, и напрямую зависят от версии ODS (On Disk Structure) базы данных (требуется ODS 11.1), а это в свою очередь значит, что придется перед установкой новой версии сервера зарезервировать базу данных под старой версией и восстановить из бэкапа под новой.
В этом месте Вас ожидают еще одни "грабли". Если Вы используете UDF, скомпилированные при помощи M$ Visual C++ (в часности, популярная rFunc http://sourceforge.net/projects/rfunc), то с радостью сообщаю Вам: разработчики перешли на новую версию компилятора, теперь и fbclient.dll, и процессу сервера требуются библиотеки времени выполнения msvcp80.dll и msvcr80.dll, в то время, как rFunc у Вас собрана скорее всего под старыми msvcp71.dll и msvcr71.dll. А это значит, что даже если у Вас получиться восстановить базу данных из бэкапа, то все равно возможны ошибки при вызове прецедур или триггеров, использующих UDF из библиотек, написанных при помощи младших версий компилятора M$ Visual C++. То есть перед переходом на новую версию сервера, необходимо пересобрать UDF-библиотеки под новой версией компилятора.
UDF, написанных при помощи FreePascal (Lazarus), Borland Delphi или Borland CBuilder, данное отступление не касается. Опять же, если Вы не используете в Ваших библиотеках линковку с msvcp71.dll и msvcr71.dll
И последнее. Для того, чтобы использовать данную функциональность Firebird, необходимо заменить клиентские библиотеки Вашего ПО на fbclient.dll версии 2.1, а также для программы учебной задачи, чтобы иметь возможность "убивать" интересующие нас запросы.
5.Описание таблиц мониторинга
Ниже представлена логическая схема таблиц мониторинга.
Логической мы ее назовем потому, что связи таблиц, предсталенные на схеме, не являются физическими (не существует соответствующих RELATION CONSTRAINTS), в данном случае эти связи отображают логическое отношение данных, допускающее, например, наличие записей в таблице MON$STATEMENTS, имеющих MON$TRANSACTION_ID = NULL, появляющихся при подключении клиентских приложений, ипользующих fbclient.dll (gds32.dll) версии ниже, чем 2.1. Рассмотрим подробнее эти таблицы:
MON$DATABASE (база данных) имеет одну запись (например, как RDB$DATABASE), хранящую служебную информацию о текущей базе данных. В текущий версии сервера ТОЛЬКО ЧТЕНИЕ
MON$DATABASE_NAME физическое имя файла базы данных в файловой системе
MON$PAGE_SIZE размер страницы базы данных (1024, 2048, 4096, 8192, 16384).
MON$ODS_MAJOR старшая версия ODS базы данных, например 11
MON$ODS_MINOR младшая версия ODS базы данных, например 1
MON$OLDEST_TRANSACTION (OIT number)
MON$OLDEST_ACTIVE (OAT number)
MON$OLDEST_SNAPSHOT (OST number)
MON$NEXT_TRANSACTION ID следующей транзакции (значение "генератора транзакций")
MON$PAGE_BUFFERSколичество страниц, расположенных в кэше
MON$SQL_DIALECT диалект языка SQL
MON$SHUTDOWN_MODE текущий режим остановки
0: online
1: multi-user shutdown
2: single-user shutdown
3: full shutdown
принимает одно из значений поля RDB$TYPE набора данных, возвращаемых SQL-скриптом
SELECT T.RDB$TYPE, T.RDB$TYPE_NAME FROM RDB$TYPES T WHERE (T.RDB$FIELD_NAME ='MON$SHUTDOWN_MODE')
MON$SWEEP_INTERVAL интервал перед сборкой мусора
MON$READ_ONLY признак доступности базы данных только для чтения
MON$FORCED_WRITES
MON$RESERVE_SPACE
MON$CREATION_DATEдата создания (восстановления из бэкапа) файла базы данных
MON$PAGES суммарное количество страниц базы данных
MON$ATTACHMENTS (активные подключения) хранит информацию об активных в текущий момент подключениях к базе данных. В текущий версии сервера ТОЛЬКО ЧТЕНИЕ
MON$ATTACHMENT_ID идентификатор подключения
MON$SERVER_PID идентификатор процесса сервера (PID). Можно увидеть в диспетчере задач M$ Windows или в списке #ps ax LINUX
MON$STATEсостояние подключения
0: idle
1: active
принимает одно из значений поля RDB$TYPE набора данных, возвращаемых SQL-скриптом
SELECT T.RDB$TYPE, T.RDB$TYPE_NAME FROM RDB$TYPES T WHERE (T.RDB$FIELD_NAME ='MON$STATE')
MON$ATTACHMENT_NAME имя базы, укзанное при подключении (строка подключения). Содержит или физическое имя файла базы данных, или имя псевдонима из файла ($firebird)/aliases.conf, смотря как была задана строка подключения.
MON$USER имя пользователя, под которым подключились к базе
MON$ROLE роль пользователя
MON$REMOTE_PROTOCOL протокол подключения, например TCPv4
MON$REMOTE_ADDRESS удаленный адрес подлючения, например 192.168.0.101
MON$REMOTE_PID идентификатор процесса (PID) клиентского приложения на удаленной ЭВМ, вызвавшего подключение. Если клиентское приложение использует библиотеку fbclient.dll младше версии 2.1, то поле содержит NULL
MON$REMOTE_PROCESS полный путь к исполняемому файлу клиентского приложения на удаленной ЭВМ, вызвавшего подключение. Если клиентское приложение использует библиотеку fbclient.dll младше версии 2.1, то поле содержит NULL
MON$CHARACTER_SET_ID идентификатор кодировки, указанной при подключении, например 52
принимает одно из значений поля RDB$CHARACTER_SET_ID набора данных, возвращаемых SQL-скриптом
SELECT C.RDB$CHARACTER_SET_ID, C.RDB$CHARACTER_SET_NAME FROM RDB$CHARACTER_SETS C
MON$TIMESTAMP дата-время старта подключения
MON$GARBAGE_COLLECTION флаг сборки мусора
MON$TRANSACTIONS (активные транзакции) хранит информацию об активных в текущий момент транзакциях. В текущий версии сервера ТОЛЬКО ЧТЕНИЕ
MON$TRANSACTION_ID идентификатор транзакции
MON$ATTACHMENT_ID идентификатор подключения из таблицы MON$ATTACHMENTS
MON$STATE состояние транзакции
0: idle
1: active
принимает одно из значений поля RDB$TYPE набора данных, возвращаемых SQL-скриптом
SELECT T.RDB$TYPE, T.RDB$TYPE_NAME FROM RDB$TYPES T WHERE (T.RDB$FIELD_NAME ='MON$STATE')
MON$TIMESTAMP время старта транзакции
MON$TOP_TRANSACTION (top transaction)
MON$OLDEST_TRANSACTION (local OIT number)
MON$OLDEST_ACTIVE (local OAT number)
MON$ISOLATION_MODE уровень изоляции транзакции
0: consistency
1: concurrency
2: read committed record version
3: read committed no record version
принимает одно из значений поля RDB$TYPE набора данных, возвращаемых SQL-скриптом
SELECT T.RDB$TYPE, T.RDB$TYPE_NAME FROM RDB$TYPES T WHERE (T.RDB$FIELD_NAME ='MON$ISOLATION_MODE')
MON$LOCK_TIMEOUT таймаут блокировки записей
-1: infinite wait (бесконечно)
0: no wait (не ожидать разрешения конфликта)
N: timeout N (ожидать разрешение конфликта)
MON$READ_ONLY транзакция открыла набор данных "только для чтения"
MON$AUTO_COMMIT признак автоматического подтверждения транзакции
MON$AUTO_UNDO признак автоматического отката транзакции
MON$STATEMENTS (активные запросы) хранит информацию об активных в текущий момент запросах к базе данных. В текущий версии сервера ТОЛЬКО ЧТЕНИЕ для записей, вызванных подключением при помощи старых версий клиентских библиотек, и ЧТЕНИЕ-УДАЛЕНИЕ для подключений, использующих клиентскую библиотеку версии 2.1 и старше.
MON$STATEMENT_ID идентификатор запроса к базе данных.
MON$ATTACHMENT_ID идентификатор подключения к базе данных из таблицы MON$ATTACHMENTS
MON$TRANSACTION_ID идентификатор транзакции. Если клиентское приложение использует библиотеку fbclient.dll младше версии 2.1, то поле содержит NULL
MON$STATE состояние запроса
0: idle
1: active
принимает одно из значений поля RDB$TYPE набора данных, возвращаемых SQL-скриптом
SELECT T.RDB$TYPE, T.RDB$TYPE_NAME FROM RDB$TYPES T WHERE (T.RDB$FIELD_NAME ='MON$STATE')
MON$TIMESTAMP дата-время старта запроса
MON$SQL_TEXT текст запроса (если доступен). для операторов DDL (CREATE TABLE, VIEW, TRIGGER) не доступен.
5. Написание клиентского приложения.
Создадим новый проект в Delphi и разместим компоненты как показано на рисунке.
Назначение компонентов: IBDataBase1 создает подключение к базе данных, trAttachments :TIBTransaction - транзакция для наборов данных, qrAttachments :TIBQuery для выборки данных о текущих подключениях. qrTransactions :TIBQuery выбирает активные транзакции Firebird2.1-клиентов. qrTransactionsStatements :TIBQuery выбирает активные запросы Firebird2.1-клиентов, qsStatements :TIBQuery выбирает запросы ранних клиентов, для которых MON$TRANSACTION_ID = NULL.
Для класса формы объявим такие методы в секции private.
//Обновляет список подключений к базе данных
procedure TFormMain.RefreshAttachments;
begin
if trAttachments.InTransaction then
trAttachments.Rollback;
qrAttachments.SQL.Text :=
'SELECT MA.MON$ATTACHMENT_ID, MA.MON$SERVER_PID, MA.MON$STATE, '+#13#10+
' (SELECT FIRST 1 T.RDB$TYPE_NAME FROM RDB$TYPES T '+#13#10+
' WHERE (T.RDB$FIELD_NAME = ''MON$STATE'') '+#13#10+
' AND (T.RDB$TYPE = MA.MON$STATE)) AS MON$STATE_TYPE_NAME, '+#13#10+
' MA.MON$ATTACHMENT_NAME, MA.MON$USER, MA.MON$ROLE, MA.MON$REMOTE_PROTOCOL, '+#13#10+
' MA.MON$REMOTE_ADDRESS, MA.MON$REMOTE_PID, MA.MON$CHARACTER_SET_ID, '+#13#10+
' (SELECT FIRST 1 CH.RDB$CHARACTER_SET_NAME FROM RDB$CHARACTER_SETS CH '+#13#10+
' WHERE (CH.RDB$CHARACTER_SET_ID = MA.MON$CHARACTER_SET_ID)) AS CHARACTER_SET_NAME, '+#13#10+
' MA.MON$TIMESTAMP, MA.MON$GARBAGE_COLLECTION '+#13#10+
'FROM MON$ATTACHMENTS MA '+#13#10;
// если отмечена "Не показывать текущее подключение ...", то исключаем из набора данных текущее подключение к базе данных
if cbxHideCurrentConnection.Checked then
qrAttachments.SQL.Text := qrAttachments.SQL.Text + 'WHERE (MA.MON$ATTACHMENT_ID <> CURRENT_CONNECTION) ';
try
qrAttachments.Open;
except
end;
end;
//Обновляет список активных транзакций для указанного подключения AttachmentId
procedure TFormMain.RefreshTransactions(const AttachmentId:string);
begin
if qrTransactions.Active then qrTransactions.Close;
qrTransactions.SQL.Text :=
'SELECT TR.MON$TRANSACTION_ID, TR.MON$ATTACHMENT_ID, TR.MON$STATE, '+#13#10+
' (SELECT FIRST 1 T.RDB$TYPE_NAME FROM RDB$TYPES T '+#13#10+
' WHERE (T.RDB$FIELD_NAME = ''MON$STATE'') '+#13#10+
' AND (T.RDB$TYPE = tr.MON$STATE)) AS MON$STATE_TYPE_NAME, '+#13#10+
' TR.MON$TIMESTAMP, TR.MON$TOP_TRANSACTION, TR.MON$OLDEST_TRANSACTION, '+#13#10+
' TR.MON$OLDEST_ACTIVE, TR.MON$ISOLATION_MODE, '+#13#10+
' (SELECT FIRST 1 T.RDB$TYPE_NAME FROM RDB$TYPES T '+#13#10+
' WHERE (T.RDB$FIELD_NAME = ''MON$ISOLATION_MODE'') '+#13#10+
' AND (T.RDB$TYPE = tr.MON$ISOLATION_MODE)) AS MON$ISOLATION_MODE_NAME, '+#13#10+
' TR.MON$LOCK_TIMEOUT, TR.MON$READ_ONLY, TR.MON$AUTO_COMMIT, TR.MON$AUTO_UNDO '+#13#10+
'FROM MON$TRANSACTIONS TR '+#13#10+
'WHERE (TR.MON$ATTACHMENT_ID = '''+AttachmentId+''') '+#13#10+
' AND (TR.MON$TRANSACTION_ID <> CURRENT_TRANSACTION)'; // hidding current transaction
try
qrTransactions.Open;
except
end;
end;
// Обновляет список активных запросов Firebird2.1-клиентов для транзакции TransactionId подключения AttachmentId
procedure TFormMain.RefreshTransactionsStatments(const AttachmentId, TransactionId:string);
begin
if qrTransactionsStatments.Active then
qrTransactionsStatments.Close;
qrTransactionsStatments.SQL.Text :=
'SELECT ST.MON$STATEMENT_ID, ST.MON$ATTACHMENT_ID, ST.MON$TRANSACTION_ID, '+#13#10+
' ST.MON$STATE, ST.MON$TIMESTAMP, ST.MON$SQL_TEXT, '+#13#10+
' (SELECT FIRST 1 T.RDB$TYPE_NAME FROM RDB$TYPES T '+#13#10+
' WHERE (T.RDB$FIELD_NAME = ''MON$STATE'') '+#13#10+
' AND (T.RDB$TYPE = ST.MON$STATE)) AS MON$STATE_TYPE_NAME '+#13#10+
'FROM MON$STATEMENTS ST '+#13#10+
'WHERE (ST.MON$ATTACHMENT_ID = '''+AttachmentId+''') '+#13#10+
' AND (ST.MON$TRANSACTION_ID = '''+TransactionId+''')';
try
qrTransactionsStatments.Open;
except
end;
end;
Обновляет список активных запросов ранних Firebird-клиентов для подключения AttachmentId
procedure TFormMain.RefreshStatments(const AttachmentId:string);
begin
if qrStatements.Active then
qrStatements.Close;
qrStatements.SQL.Text :=
'SELECT ST.MON$STATEMENT_ID, ST.MON$ATTACHMENT_ID, ST.MON$TRANSACTION_ID, '+#13#10+
' ST.MON$STATE, ST.MON$TIMESTAMP, ST.MON$SQL_TEXT, '+#13#10+
' (SELECT FIRST 1 T.RDB$TYPE_NAME FROM RDB$TYPES T '+#13#10+
' WHERE (T.RDB$FIELD_NAME = ''MON$STATE'') '+#13#10+
' AND (T.RDB$TYPE = ST.MON$STATE)) AS MON$STATE_TYPE_NAME '+#13#10+
'FROM MON$STATEMENTS ST '+#13#10+
'WHERE (ST.MON$ATTACHMENT_ID = '''+AttachmentId+''') '+#13#10+
' AND (ST.MON$TRANSACTION_ID IS NULL)';
try
qrStatements.Open;
except
end;
end;
// Убивает активный запрос по его коду StatementId
procedure TFormMain.KillStatement(const StatementId:string);
var
lk_tr :TIBTransaction;
lk_qr :TIBQuery;
lk_flag :Boolean;
begin
if trAttachments.InTransaction then
trAttachments.Rollback;
lk_tr := TIBTransaction.Create(nil);
lk_tr.DefaultDatabase := IBDataBase1;
lk_qr := TIBQuery.Create(lk_tr);
lk_qr.Database := IBDataBase1;
lk_qr.Transaction := lk_tr;
lk_qr.SQL.Text := 'DELETE FROM MON$STATEMENTS MS WHERE (MS.MON$STATEMENT_ID = '+StatementId+')';
lk_flag := false;
lk_tr.StartTransaction;
try
lk_qr.ExecSQL;
finally
lk_flag := true;
end;
if lk_tr.InTransaction then
begin
if lk_flag then
lk_tr.Commit
else
lk_tr.Rollback;
end;
lk_tr.Free;
RefreshAttachments;
end;
Соответственно, определим следующие обработчики событий:
procedure TFormMain.ButtonRefreshClick(Sender: TObject);
begin
RefreshAttachments;
end;
procedure TFormMain.qrAttachmentsBeforeClose(DataSet: TDataSet);
begin
if qrTransactions.Active then
qrTransactions.Close;
if qrStatements.Active then
qrStatements.Close;
end;
procedure TFormMain.qrAttachmentsAfterScroll(DataSet: TDataSet);
begin
RefreshTransactions(DataSet.FieldByName('MON$ATTACHMENT_ID').AsString);
RefreshStatments (DataSet.FieldByName('MON$ATTACHMENT_ID').AsString);
end;
procedure TFormMain.qrTransactionsBeforeClose(DataSet: TDataSet);
begin
if qrTransactionsStatments.Active then
qrTransactionsStatments.Close;
end;
procedure TFormMain.qrTransactionsAfterScroll(DataSet: TDataSet);
begin
RefreshTransactionsStatments(DataSet.FieldByName('MON$ATTACHMENT_ID').AsString,
DataSet.FieldByName('MON$TRANSACTION_ID').AsString);
end;
procedure TFormMain.ButtonKillClick(Sender: TObject);
begin
KillStatement(qrTransactionsStatments.FieldByName('MON$STATEMENT_ID').AsString);
end;
Запустив программу на выполнение, мы можем увидеть состояние базы данных.
6.Заключение
Данная статья была нацелена на то, чтобы показать как пользоваться таблицами мониторинга в базах данных Firebird v2.1, поэтому автор старался сделать все "по-примитивней". Конечно, автор надеется, что у читателя найдется более широкая область применения данной функциональности для своих нужд, например как: использование мониторинга в связке с потоковыми запросами (представлеными в стандартных примерах $DELPHI\Demos\DelphiWin32\VCLWin32\Db\IBX\ThreadedIBX), отслеживание атак на Web-системы (Apache->PHP->Firebird), отладка сложных запросов.
