Задача перехвата DML-запросов и как она решается разными DB

[oMoses]

Эх давно мы не говорили о неоспоримых преимуществах Oracle над всякими там МэСэ Си-Ку-Элами и Ай-Би-Эм Ди-Би Два!

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

Задача: сформировать полный список всех успешных (after commit) INSERT/DELETE/UPDATE с сохранением полного синтаксиса и изменяемых величин. Т.е., результат "наката" перехваченных таким образом DML's должен совпадать с теми изменениями, которые генерируются приложением. Каждый запрос должен сопровождаться дополнительной служебной информацией, как-то именем пользователя, точным временем, SCN, наименованием схемы и объекта и т.д.

Вопрос: как такая задача решается средствами MS SQL и IBM DB2? Будет-ли такое решение работать unattended и обеспечивать гарантированный перехват сотен тысяч запросов на протяжении недель?

[katit]

MS SQL:

Traces. (SQL Profiler using them)

Нащет времени работы так тут много соствляющих.

[oMoses]

Расскажу, как это может быть сделано средствами Oracle9i. Возможны несколько путей, как-то:
- auditing
- triggers
- streams
- CDC
- tracing
- logminer

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

Triggers, увы, позволяют сравнительно легко отслеживать изменения в самих данных, но не позволяют перехватывать полные DML's. К тому-же, они способны запросто "посадить" производительность целой базы и имеют серьезные ограничения.

Streams (в 8i - репликация) и CDC (Capture Data Changing) базируются на технологии logminer и в данном случае будут явным overkill. Более того, опять следует упомянуть про существенные ограничения.

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

Logminer, кажется, наиболее подходящим для решения поставленной задачи, поскольку позволяет читать и анализировать абсолютно все запросы сделанные к базе через архивные логи, генерируемые последней. Однако, и эта технология не лишена своих ограничений и неудобств. Во-первых, база должна быть в archivelog mode. Более того, этот режим должен быть включенным c опцией Force, дабы гарантированно защититься от direct-INSERTs (SQL*Loader, DIRECT=Y). Далее, каждый лог может иметь сотни тысяч транзакций, из которых интерес для нас может представлять лишь десяток. Logminer требует исключительных прав, которыми простого пользователя не наделишь. Процесс чтения/анализа/складирования нужной информации требует дополнительно "автоматизации" через PL/SQL процедуры, shell-scripting, cron или Oracle jobs... Вывод: даже технология logminer вряд-ли будет удобна и адекватна при продолжительно использовании и значительной нагрузке.

Что осталось??? А ничего...

P.S. Только не спрашивайте меня зачем это может понадобиться!

[oMoses]

MS SQL:
Traces. (SQL Profiler using them)

Боюсь, что этот механизм не позволит Вам отловить DML's "закомитченных" транзакций, и отсеить - все остальные. Или я не прав?

[Dmitry67]

Да, жто так
Выловить commit в принципе можно
Но commit в процедуре, и она commit-unbalanced, то да
А так можно проанализировать commit/rollback
Задача не тривиальная

А зачем ?

[tengiz]

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

Поэтому самыми точными оказываются методы, основанные на проигрывании реального журнала транзакций. В SQL Server это тот же log shipping, например, или log-based репликация.

[Michael Popov]

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

На клиенте включать ничего не надо. Tracing включается на сервере. В полученных логах есть вся необходимая информация, включая commits/rollbacks, SQL syntax, bind vars, etc. Анализировать логи - для этого надо или самому скрипты писать или тулы соответствующие. Две (но очень большие) проблемы с tracing: performance overhead (10-20%) and security (all your data in plain text files).

Можно еще это посмотреть:
http://www.quest.com/foglight/foglight_SQLtrans.asp

[Dmitry67]

Не думаю что все это можно корректно проиграть во всех случаях
Пример: триггеры реагируют на время

[oMoses]

Спасибо всем откликнувшимся! Жаль, что про DB2 пока еще никто не рассказал, однако сильно подозреваю, что и там ситауция схожая - ни одна база не дает надежного и вполне рабочего механизма решения подобной задачи. Потому как идея сама по себе - бредовая и является порождением чуждого индусского разума. К счастью, мне удалось своих коллег отговорить от попытки ее реализации. Более того, сам смысл и реальная необходимость в подобном была поставлена под сомнение.

[Dmitry67]

Кстати о DB2
а куда делся zVlad ?

[oMoses]

Как. разве он вам не сказал?
Окончательно разочаровался в IBM и мигрировал под крышу Oracle!

[zVlad]

Кстати о DB2
а куда делся zVlad ?

Как. разве он вам не сказал?
Окончательно разочаровался в IBM и мигрировал под крышу Oracle!

Никуда я не девался и повода разочаровываться в DB2 и IBM у меня не было.
Наоборот, oMoses своими рассуждениями в этом топике добавил повод лишний раз убедиться что DB2 в этом разрезе ни чем не уступает Ораклу и более того ощутить преимущество DB2, хотя бы в вопросе возможности доступа к записям журнала транзакций. Никаких ограничений и неудобств в этом у DB2 нет. Читать можно не только архивный журнал, но и активный никак не влияя на работу самого DB2. На этом как раз, как правило, базируются средства репликаций. Хотя для последнего имеются и другие возможности, как то DataCapture, который перехватывает все DML и пишит в специальные для этого созданные таблицы DB2 всю необходимую для воспроизведения изменений информацию.

Мигрировать же под крышу Оракла у меня нет, никогда не было и не будет ни малейшего желания и необходимости. И слава богу.

[Dmitry67]

Хорошо что появились а то я уже беспокоился

Несколько вопросов по DB2 навеянных Yukon

А как в DB2 c хранением XML в базе ? Не как длинный текст, а с индексацией XML полей ?

stored procedures, которые пишутся в DB2 представляют с точки зрения Microsoft, 'unmanaged' code ? то есть не котролируются утечки памяти, 'run aways' по ресурсам... Как построена конкуренция за память кода процедур и самого ядра DB2 (с кешем)

[zVlad]

......

1. А как в DB2 c хранением XML в базе ? Не как длинный текст, а с индексацией XML полей ?

2. stored procedures, которые пишутся в DB2 представляют с точки зрения Microsoft, 'unmanaged' code ? то есть не котролируются утечки памяти, 'run aways' по ресурсам... Как построена конкуренция за память кода процедур и самого ядра DB2 (с кешем)

1. К сожалению приложение, для которого я DBA, не использует ничего кроме голых таблиц с индексами. Недавно стали использоваться триггеры. В своих приложениях (автоматизации задач DBA) я еще использую первичные и внешние ключи и другие мелочи. Поэтому сказать что-либо о XML, базирующеся на моем личном опыте и знаниях я не могу. Из статей и аннонсов у меня сложилось впечатление что у ИБМ в этом вопросе все в порядке и они едва ли не лидеры в стандарте xSQL или как это там называется. Спекулировать не буду.

2. Первый раз слышу о таких недостатках. У Вас есть примеры?
Такие проблемы как "утечка памяти", "не контролируемое использование ресурсов" были открыты, изучены и "сняты" на МФ ИБМ-ом годах так в 70х. На ЕС ЭВМ это еще проявлялось в 80х, то есть до того как появился Мicrosoft (не в обиду будет сказано). Не думаю что на других платформах ИБМ забыл про этот опыт.

Мне кажется ни один DB2 DBA не сможет рассказать как работает механизм конкуренции за память в DB2 по той простой причине что они никогда не испытывают проблем с этим. А раз так - зачем это знать. Правильно?
Я пропустил несколько дискуссий на эту тему, просто потому что мне было ничего не известно о подобных проблемах в ДБ2. Если я не прав прошу других ДБ2-шников меня поправить.

А вообще, уже давно мне не приходится разрешать проблем ДБ2 как такового. За последние 4 года это было всего может быть два раза и каждый раз оказывалось что ИБМ уже имеет решение - просто мы были не в курсе.
Главной моей проблемой, как DB2 DBA, на сегодня являются люди окружающие DB2. В двух словах: за последние месяцы я обнаружил больше чем два десятка мест в приложении работающих не эффективно и предложил меры по исправлению, практически без изменения кода. И не могу "продавить" это через людей, кто принимает решение о внедрении. Большинство предложений протестированно, доказана их эффективность, каким то чудом два-три были внедренны, были восторги СOOL!!!, но вот с основными предложениями - вилы. Людской фактор.
Другая проблема - это приложения написаные либо для Оракл, либо для Windows и желающие соединяться с нашей базой на DB2. Это тоже ночной кошмар. Решения на уровне 2-ого класса, никакого понятия о реальных нагрузках и проблемах. Розовые очки, и удивленные глаза.
Наверное месяц объяснять пришлось, что когда ресурс занят, то запрос ставится в очередь, а когда ожидание в очереди превышает определенное время, то запрос терминируется и его просто нужно повторить. Нет говорят там у вас в ДБ2 что-то не так. Да я согласен, что что-то не так, но не в ДБ2, а опять же в том как приложение для этого ДБ2 написано. Но в свою очередь попробуйте докажите что-нибудь тем кто пишет это приложение.
Другой пример. Создатели приложения используют кодировку представленную кодовой страницей 1034, а говорят в документации что у них страница номер 37. Никакие доказательства (официальные документы) так и не были признаны. А проблема "всплыла" когда приложение из-под Windows начало соединяться и брать информацию из ДБ2. Поскольку мы, на основании документации приложения, установили номер 37 в ДБ2, а на самом деле несколько символов кодировались иначе, то и получился "сыр-бор". И так почти всегда. Люди - это самая большая проблема в моей практике.

[Dmitry67]

Да, людской фактор в большиз фирмах вещь еще та
Что касается памяти то я вот что имел ввиду конкретно
Пусть stored procedure делает ref=malloc(1024*1024*1024), то есть аллокирует доЯя чего то один гиг памяти. Откуда он возьмется ?

Для примера допустим что у вас всего полтора гига. Естественно база, точнее ее кеш занимает почти всю оперативку. В случае managed code SQL server поймет что ситуация конфликтная и будет пытаться поджаться, это лучше чем page faults от него и stored procedures. Как жто построено в IBM ?

[zVlad]

Да, людской фактор в большиз фирмах вещь еще та
Что касается памяти то я вот что имел ввиду конкретно
Пусть stored procedure делает ref=malloc(1024*1024*1024), то есть аллокирует доЯя чего то один гиг памяти. Откуда он возьмется ?

Для примера допустим что у вас всего полтора гига. Естественно база, точнее ее кеш занимает почти всю оперативку. В случае managed code SQL server поймет что ситуация конфликтная и будет пытаться поджаться, это лучше чем page faults от него и stored procedures. Как жто построено в IBM ?

Это видимо будет зависить от платформы. На МФ такими вопросами ведает не сервер базы данных (хранимые процедуры вообще не в адресном пространстве сервера БД выполняются), а ОС. Считается нормальным что на МФ одновременно выполняются много серверов, сервисов, приложений, пакетов и т.д.. Если бы было возможным, что один жадный процесс захватил память, то работать вообще было бы не возможно.
В системе VM, например, еще в 80-е существовало такое понятие как рабочий набор - количество реальной памяти, которое может окупировать отдельная ВМ, если она окупирует больше, то система начинает "свапать" ее же страницы.

А что в случае MS SQL хранимые процедуры выполняются в том же адрессном пространстве (или как там это называется) что и сам сервер? Это должно быть не безопасно.

[Dmitry67]

zVlad, обычные stored procedures написанные на TSQL выполняются в адресном пространстве сервера, но это не C, а TSQL, то есть только сервер их и может выполнять.

Тем не менее Ваше беспокойство совершенно справедливо, новая технология CLR предполагает исполнение кода написанного пользователем как stored procedure. Правда код жто является managed, то есть safe, так как все операции проверяются системой .Net, но тем не менее. Юконовцы убеждали меня что все безопастно, но полностью до конца я не поверил

Я понял что sp в DB2 выполняются в другом адресном пространстве; если отведение памяти в них работает на общих основаниях то если процедура будет неожиданно много отводать памяти начнутся page faults, который можно было бы избежать если SQL server 'aware' относительно выделений памяти. Так построено в Yukon.

Другое дело что выделение больших объемов памяти наверное редкая задача

[dzher]

А как в ДБ2 ц хранением ХМЛ в базе ? Не как длинный текст, а с индексацией ХМЛ полей ?

ХМЛ ехтендер

сторед процедурес, которые пишутся в ДБ2 представляют с точки зрения Мицрософт, ьунманагедь цоде ?

Дмитрий, Мицрософт здесь несколько слукавила - партия (ИБМ) рекомендует ДБ2 СП писАть на Ява - что таки является [managed code] (Java/JVM vs .C#/NET)

[oMoses]

Наоборот, oMoses своими рассуждениями в этом топике добавил повод лишний раз убедиться что DB2 в этом разрезе ни чем не уступает Ораклу и более того ощутить преимущество DB2, хотя бы в вопросе возможности доступа к записям журнала транзакций. Никаких ограничений и неудобств в этом у DB2 нет. Читать можно не только архивный журнал, но и активный никак не влияя на работу самого DB2. На этом как раз, как правило, базируются средства репликаций. Хотя для последнего имеются и другие возможности, как то DataCapture, который перехватывает все DML и пишит в специальные для этого созданные таблицы DB2 всю необходимую для воспроизведения изменений информацию.

Влад, привет! Знаете, вот если в этом Вашем пассаже заменить DB2 на Oracle, а Oracle - на DB2, то справедливость этих высказываний - не изменится. Это сродне тому, как кто-то в наполненом наполовину стакане склонен видеть наполовину полный стакан, а кто-то - наполовину пустой. Нюансы начинают проявляться лишь при непосредственной и продолжительной работе по решению конкретных задач. Думаю, что как в Oracle, как и в DB2 (не говоря уж и про MS SQL) достаточно мест, сделанных через ж....

[zVlad]

.........

Влад, привет! Знаете, вот если в этом Вашем пассаже заменить DB2 на Oracle, а Oracle - на DB2, то справедливость этих высказываний - не изменится. Это сродне тому, как кто-то в наполненом наполовину стакане склонен видеть наполовину полный стакан, а кто-то - наполовину пустой. Нюансы начинают проявляться лишь при непосредственной и продолжительной работе по решению конкретных задач. Думаю, что как в Oracle, как и в DB2 (не говоря уж и про MS SQL) достаточно мест, сделанных через ж....

Если это так, то что же означает это:

.....Logminer, кажется, наиболее подходящим для решения поставленной задачи, поскольку позволяет читать и анализировать абсолютно все запросы сделанные к базе через архивные логи, генерируемые последней. Однако, и эта технология не лишена своих ограничений и неудобств. Во-первых, база должна быть в archivelog mode. Более того, этот режим должен быть включенным c опцией Force, дабы гарантированно защититься от direct-INSERTs (SQL*Loader, DIRECT=Y)......"

В чем неудобство нахождения Оракле в archivelog mode? Значит ли это что обычно Оракл не архивирует журнал? Что ж тогда происходит, когда активный журанл заполняется?

[zVlad]

.........
Я понял что sp в DB2 выполняются в другом адресном пространстве; если отведение памяти в них работает на общих основаниях то если процедура будет неожиданно много отводать памяти начнутся page faults, который можно было бы избежать если SQL server 'aware' относительно выделений памяти. Так построено в Yukon.

Другое дело что выделение больших объемов памяти наверное редкая задача

Управление памятью завсегда считалось функцией операционной системы. И это почти закон. Что, Microsoft открыл новый закон и теперь это функция сервера баз данных? Или все таки ОС тоже ведает памятью?
Видимо, в случае с Yukon, ОС ведает памятью Yukon-а, в то время как последний дублирует эту функцию для выполняющихся в его рамках хранимых процедур. Я не прав?

В ДБ2 на МФ хр. процедура выполняется в отдельном адресном пространстве, каждая в своем. Выделение им ресурсов - задача ОС.
Таким образом, хр. процедура есть ничто иное как приложение, запускаемое в той же системе, где находится сервер. И отличие хр. процедур от классических приложений лишь в том, что хр. процедуры определенным образом готовятся, инициируются через оператор SQL СALL, и выполняются как за пределами вызвавшего ХП-дуру приложения так за пределами сервера баз данных.
Все механизмы аутентификации и авторизации остаются теми же что и для приложений запущенных любым другим способом.
Следовательно, хр. процедура может быть написана на каком угодно языке (хоть на русском матершинном) это никоим образом не уменьшит как безопасности сервера баз данных, так и контролируемости использования ресурсов. Что и наблюдается на практике.
В ДБ2 для Unix, Window имелись понятия "fenced SP" и "not-fenced SP", последние выполняются в том же адрессном пространстве что и сервер баз данных. Но эта информация у меня очень устаревшая, как там сейчас дела с этим обстоят не знаю

Интересно, а как с этой точки зрения выглядят ХП-дуры в Оракл?

[Lazy44]

Управление памятью завсегда считалось функцией операционной системы. И это почти закон. Что, Microsoft открыл новый закон и теперь это функция сервера баз данных?

Позвольте с вами не согласится. Оракл дает полную свободу или не-свободу по управлению ресурсами вне зависимости от ограничений на уровне ОС. Я не берусь судить об управлении памятью под зОС, но при работе ХП написанных на SQL PL под UDB 7.1 under AIX у меня были проблемы при аллокировании больших кусков памяти под массивы. При этом памяти было больше чем достаточно в разы. Было такое чувство, что база хватает первый кусок памяти из heap и не смотрит на его размер. Проблема была не повторяемая. Видать, эту проблему для SQL PL в ИБМ еще не решили. В оракле можно динамически менять размеры почти всех пулов, и , боюсь соврать, но тут поправят, по моему можно отдать все на откуп базе для ленивых

[zVlad]

Управление памятью завсегда считалось функцией операционной системы. И это почти закон. Что, Microsoft открыл новый закон и теперь это функция сервера баз данных?

1. Позвольте с вами не согласится. Оракл дает полную свободу или не-свободу по управлению ресурсами вне зависимости от ограничений на уровне ОС.
2. Я не берусь судить об управлении памятью под зОС, но при работе ХП написанных на SQL PL под UDB 7.1 under AIX у меня были проблемы при аллокировании больших кусков памяти под массивы. При этом памяти было больше чем достаточно в разы. Было такое чувство, что база хватает первый кусок памяти из heap и не смотрит на его размер. Проблема была не повторяемая. Видать, эту проблему для SQL PL в ИБМ еще не решили.
3. В оракле можно динамически менять размеры почти всех пулов, и , боюсь соврать, но тут поправят, по моему можно отдать все на откуп базе для ленивых

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

2. Если можно приведите больше деталей. Какой вид процедуры, каким образом происходит распределение памяти, какое сообщение (код) получает процедура. Догабываюсь, что Вы работаете на уровне написания прикладных программ и Вам просто нужна помощь DBA в этом вопросе. Сама UDB здесь скорее всего не причем - у ИБМ достаточно было времени и опыта, чтобы увидеть и устранить проблему, которую Вы полагаете UDB имеет. Я имею в виду: "...Было такое чувство, что база хватает первый кусок памяти из heap и не смотрит на его размер." Ну не может этого быть.

3. Руководство DBA для UDB содержит множество параметров которые можно менять, имеются также автоматически настраиваемые параметры. Чтобы что-то утверждать в этой части конкретно нужно перелопатить очень много информации, или иметь одинаково интенсивную практику в обеих системах. Боюсь не Вы ни я не отвечаем ни тому ни другому требованию.

[Dmitry67]

zVlad, Вы все время говорите о том как классно что сама ОС ведает памятью и для sp, и для сервера. Однако это очень неоптимальное решение c точки зрения performance. Потмоу что возникает авто(само)конкуренция за ресурс

Пример. У меня есть таблица 1G. памяти на сервере 1G. База берет 1G для кеша (само ядро маленькое и его не считаем). Есть sp которая делает очень сложную обработку и для этого временно аллокирует 512M.

Последовательность действий: table scan, вызов процедуры, table scan.
Разбираем по шагам. 1 table scan - при люой архитиектуре чиатется таблица последовательным чтением и помещается в кеш. Я обозначу это так: seqread(1024);

Далее вызывается sp; она делает malloc(512*1024) и чтото то там делает. ОС вынуждена вытолкнуть половину кеша сервера в виде страниц, то есть pagewrite(512).

Далее снова делаем table scan. Сервер думает что вся таблица у него в кеше. На самом деле при обращении к половине страниц кеша произойдет page faults: randomread(512). Вот здесь мы сильно попадаем потому что чтение идет не большими кусками а по страничке и возможно вразнобой

Итого: seqread(1024) + pagewrite(512) + randomread(512)

Теперь ситуация если server 'aware' отнсоительно sp. В момент malloc он поджимается, сознательно разрушая половину кеша и отдает память процедуре. Никакого IO не происходит

Далее, при table scan ему надо будет взять половину из кеша а половину снова прочитать с диска. Итого seqread(512)

Получили seqread(1024) + seqread(512) - значительно меньше !

Я сильно упростил, однако принцип именно такой

[verzlo]

Расскажу, как это может быть сделано средствами Oracle9i. Возможны несколько путей, как-то:
- auditing
- triggers
- streams
- CDC
- tracing
- logminer

Вы пропустили logical standby - по моему он должен удовлетворить условиям задачи...