Мониторинг баз данных Firebird

Firebird 2.0 на полную катушку

Мониторинг баз данных Firebird.

0.Преамбула

  1. Автор не претендует на роль истины в последней инстанции. Задачи и методы их решения, изглагаемые в статье, являются следствием опыта, полученного автором при написании программ, не являются панацеей и если кто-нибудь может предложить более оптимальное решение и поделиться им с нами, автор будет только рад :)
  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), отладка сложных запросов.