Сломается ли база, если выдернуть сервер из розетки, или потроха DB ORACLE для чайников

Писал для коллег — программистов, далёких от предметной области, которые действительно, искренне не понимали, что такого сложного в базе данных. Они хотели хранить критические данные в простых файлах. Я задавал им каверзные вопросы о надёжности, скорости и одновременном доступе, они пытались «на ходу» придумать хитрые решения. В конце они трезво оценили требуемый объём кода и поняли, что им придётся написать свой маленький ORACLE или, хотя бы, MySQL. Затем я рассказал им, как были решены эти проблемы в DB ORACLE, их поразило изящество некоторых алгоритмов. Лекция понравилась, и я решил выложить её в открытый доступ.


Кто не хочет читать теорию – в конце пара ключевых моментов, которые должен знать каждый разработчик, работающий с ORACLE. Языком SQL он может и не владеть, но особенности COMMIT’а знать обязан.

Для DBA и прочих знатоков (в хорошем смысле) — материал сознательно максимально упрощён, за счёт этого есть некоторые неточности. Цель была – передать основные принципы неподготовленному человеку.

Вначале был файл и были таблицы с записями фиксированной длинны.
Всё просто – новые записи добавлялись в существующие «дырки» в середину файла вместо удалённых, если «дырок» нет – в конец.
Быстро, но много места приходится резервировать на всякий случай, для редких, уникальных данных. Так, один известный испанец имеет имя «Пабло Диего Хозе Франциско де Паула Хуан Непомукено Криспин Криспиано де ла Сантисима Тринидад Руиз и Пикассо» — соответственно, поле ФИО придётся делать с большим запасом.
И никаких длинных описаний или примечаний из нескольких предложений.



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



Когда добавили функционал – скорость сильно упала. На дефрагментацию уходит много ресурсов, её приходится ограничивать. В результате многие записи оказались «размазаны» по блокам, и, что самое плохое, эти блоки могут физически находиться в разных частях жёсткого диска. Очевидное решение — кэширование в буферном КЭШе — спасает ситуацию при чтении. При фиксации изменений все данные должны быть записаны на диск (когда речь идёт о критичных приложениях нельзя надеяться на UPS), причём желательно не один, а на несколько, для резервирования. Причём диски должны быть логическими, резервирование исключительно средствами RAID не подойдёт, ибо, в случае смерти контроллера на свалку может отправиться весь массив. А пользователи, кроме того, не желают дублировать файлы данных на сервере – серверные диски стоят очень дорого.



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

А как же файлы данных? Периодически запускается процесс, находящий в буфером кэше изменённые, так называемые «грязные» блоки и записывающий их на диск. За счёт «оптовой» обработки повышается скорость, и время реакции системы.
Чтобы размер логов повторного выполнения не увеличивался до бесконечности, используется ротация логов. ORACLE следит, чтобы при циклической смене лог файлов все «грязные» блоки попали в файлы данных.



Из этой схемы вытекает неочевидная с первого взгляда особенность, поразившая меня в своё время до глубины души. Что делать с изменёнными блоками, которые не подтверждены COMMIT”ом, но уже не помещаются в буферный кэш? Писать изменения во временный файл? Нет, ORACLE пишет эти изменения сразу в фалы данных. Да, в системе, изначально ориентированной на повышенную надёжность файлы данных могут содержать неконсистентные, не зафиксированные COMMIT”ом данные, и это нормально, это часть рабочего процесса. Вместо этого СУБД делает резервную копию изменённых блоков в специальных UNDO файлах. Здесь этот резерв сохраняется до фиксации транзакции, и позволяет другим транзакциям читать данные во время того, как другая транзакция их меняет. Да, в ORACLE изменения не блокируют чтение. Это основы, но многие прикладные разработчики с удивлением узнают об этой особенности.
И – все UNDO изменения также записываются в лог повторного выполнения.

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

Если перед циклической перезаписью следующего лог-файла повторного выполнения копировать его на другой носитель или сервер, можно получить следующие плюшки.
1. Простой бэкап – достаточно делать полную копию файлов данных, например, раз в сутки, а в остальное время лишь сохранять логи.
2. Резервный сервер – дальнейшее развитие идеи. Логи накатываются на копию БД, развёрнутой на резервном сервере, по мере их поступления. При наступлении часа Х обработка данных переключается на резервный сервер.
3. Запросы в прошлое – можно посмотреть данные таблицы, какие они были, например, полтора часа назад. И это можно делать на рабочей базе, не надо «поднимать» никаких резервных копий.

Возвращась к заголовку: что же произойдёт при внезапном отключении электричества? Ведь данные в файлах останутся в несогласованном состоянии? Ничего страшного.
Рассмотрим пример: после последней успешной записи в файл БД (время 1) система подтвердила 1 танзакцию (время 2), затем начала выполнять 2-ю, прямо во время записи в файл БД выключается питание (время 3).
Итак, сервер снова заработал. Система читает лог повторного выполнения с момента последней успешной записи на диск (со времени 1) и откатывает все изменения. Для этого в логе есть вся необходимая информация. Затем система последовательно повторяет все операции (накат). Находит метку COMMITа (сделанную во время 2) и выполняет фиксацию изменений. Затем продолжает накат. Доходит до конца лога повторного выполнения (на этой временной точке -3 — отключилось питание) и не находит метку COMMITа. Вновь выполнятся откат, но уже к точке (время 2).
Данные вновь в согласованном состоянии!

Да, вся эта информации полезна, но на что должны обратить внимание разработчики?

• Первый неочевидный момент — в отличие от других СУБД в ORACLE частый COMMIT вреден. При поступлении этой команды информация, пусть и в небольшом количестве, должна быть записана на диск. Этот процесс происходит максимально быстро, но если пытаться выполнять его после изменения каждой записи для 10 миллионной таблицы… Система сконструирована таким образом, чтобы делать COMMIT тогда, когда это необходимо с точки зрения бизнес-логики, а не чаще.
Отката же транзакции (ROLLBACK) нужно по мере сил избегать, как правило, врем на откат часто сравнимо со временем изменений. UPDATE выполнялся час, решили откатить, ROLLBACK тоже будет выполняться сравнимое время. А коммит бы «прошёл» за долю секуды.

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

• Третий момент – не бойтесь join’ов, это не ущербный MySQL. Правильно настроенная база позволит поддерживать и целостность данных, и производительность. HASH JOUN, MERGE JOIN, INDEX ORGANISED TABLES, настройка CARDINALITY, QUERY REWRITING и MATRIALISED VIEW UPDATE ON COMMIT позволят знающему админу добиться хорошей скорости. А вот поддерживать неконсистентную денормализованную систему в порядке через пару лет будет на порядок сложнее.

• И напоследок – используйте связанные переменные! Это позволит разом значительно, часто в несколько раз увеличить производительность и, как бесплатный бонус, избежать SQL!



Вывод – ORACLE создал надёжную СУБД, но, насколько она будет быстро работать, зависит от разработчиков. Неоптимальные решения заставят любой сервер просить пощады.

Комментарии 46

    0
    СУБД делает резервную копию изменённых блоков в специальных UNDO файлах.

    То есть перед записью новых данных сначала происходит обязательное копирование старых данных в другой файл?
      0
      Не вдаваясь в подробности ответ — да. Если включить режим зануды — то ответ нет, зануда просит выкинуть из фразы «в другой файл».
        0
        Это ж сколько работы для СУБД (переходя в режим зануды). Предлагаю улучшение — писать новые данные не поверх старых, а где-то в другом месте в файл данных. Тогда не надо старые данные гонять в UNDO-файл, да и читающие транзакции не будут лазить в UNDO-файл за своим снимком данных. Получится такая вот естественная версионность.
          0
          Т.е. при любом апдейте вы предлагаете полностью копировать строку? Индексы, кстати до комита на какое место будут ссылаться? На новое или старое?
            0
            Апдейт создаст новую версию строки. Вот, к примеру, у нас есть запись ИВАНОВ. Мы ее исправляем на ПЕТРОВ. До начала редактирования с диска в память считывается ИВАНОВ, затем в оперативной памяти он меняется на ПЕТРОВ и эта строка пишется на диск. Я предлагаю всего лишь писать новую версию не затирая старую. Некоторое время в базе будут обе версии (и ИВАНОВ и ПЕТРОВ), пока все старые читающие транзакции не завершатся. Ну а потом новые пишущие транзакции затрут Иванова и останется только Петров.
              0
              Во первых, будет очень сильная фрагментация.
              Во вторых, постоянное обновление индексов при смене физического адреса блока в разы увеличит обращения к диску, причем, даже в случае, когда обновляются поля, не входящие в индекс.
              В третьих. Если это действительно критично, необходимо спроектировать приложение таким образом, чтобы минимизировать количество обновлений, заменив их на добавления.
                0
                По фрагментации — пусть версии будут не у строк и не у записей, а у блоков. Все равно СУБД пишет на диск блоками.

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


                  Это тоже бывает нужно. Например IOT фактически представляет собой «голый» индекс. Но при этом по ее записям также можно строить индексы. В этих индексах, по понятным причинам, не может использоваться ROWID. Именно по этой причине был разработан UROWID. Он менее эффективен, но позволяет указывать на строки, физическое расположение которых может измениться
                    0
                    И еще где-то хранить связь этого уникального идентификатора с физическим адресом, поскольку для быстрой выборки малого количества данных из реально большой базы, в конце концов, нужен физический адрес блока.
                      0
                      Записи придется физически упорядочить по идентификатору. А чтобы быстро находить нужную запись, таблица станет древовидной структурой, как индекс. Ведь это всё реализовано — кластерный первичный ключ, Index-Organized Tables из сообщения GlukKazan выше.
                        0
                        А как сочетается необходимость физически упорядочить записи (ну или блоки) и создание новой копии записи в другом месте при редактировании (с чего Вы, собственно, и начали)?
                        Приходим к необходимости постоянной пересортировки.
                          0
                          Сортировка по идентификатору не обязательно требует постоянной физической перестановки записей. Пусть у нас идентификатором будет 64-разрядное целое. СУБД для новой записи инкрементирует это значение, поэтому любую новую запись достаточно разместить в конце таблицы, чтобы сохранить сортировку. Нет проблем и в модификации. Идентификатор — это суррогатный ключ (его значение не зависит от других полей). Поэтому запись остается со своим идентификатором при любых изменениях значений остальных полей. Только при физическом удалении записи нужно «сомкнуть ряды»: оставшиеся на странице элементы переместить ближе к началу, что является обычной операцией в индексах.

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

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

                          Второе преимущество будет когда пишущая транзакция оборвется если «выдернуть сервер из розетки». Проблема будет только у этой конкретной транзакции. Все остальные данные останутся на своих местах. И я не очень понял как при наличии этих технологий (IOT и версионность блоков) процедура сохранения измененных данных такая сложная и длительная — со всеми этими обязательными копированиями в логи.
                            0
                            Еще раз.
                            Быстрый поиск данных предполагает наличия в индексе именно физического, а не логического адреса блока. В описанном Вами подходе при изменении записи будет создаваться копия блока в другом физическом месте. Даже если логический идентификатор останется таким же, это не поможет при поиске блока. Следовательно, появляется деградация производительности по чтению.

                            Все не так просто, чтобы можно было улучшить «нахрапом», и для выбора способа работы с базой необходимо оценивать все возможные сценарии.
                            Я думаю, что в Oracle выбрали способ с отдельным ROLLBACK-сегментом обоснованно.
                              0
                              Быстрый ли будет поиск, попробую описать без ссылок на другие источники. Вероятно, Вы согласитесь, что поиск по ключу в любом индексе выполняется быстро. А теперь представьте индекс, у которого ключ состоит из одного поля в виде целого числа. Поиск будет практически мгновенным!
                              Два слова об организации индексов вообще. Индекс состоит из иерархии страниц. На каждой странице содержатся элементы. В каждом элементе индекса присутствует ключ и ссылка. В верхнем элементе (узле) ссылка указывает на страницу ниже. В самом нижнем элементе (листе) ссылка в классическом варианте содержит физический адрес записи.
                              Теперь берем наш индекс с ключом по целому числу, и меняем структуру листа (самого нижнего элемента). Уберем ссылку на запись, и вместо нее мы будем хранить непосредственно сами данные. Это и будет таблица, организованная по индексу — стандартная вещь в Oracle.
                              Во вторичных индексах ссылками теперь будут не физические адреса, а целые числа — идентификаторы записей. Поиск по вторичному индексу будет дополняться поиском по нашей таблице-индексу. Но в ней поиск будет очень быстрым, т.к. мы изменили структуру только листа, а на верхних уровнях в узлах останутся пары ключ-ссылка. Из-за такого маленького размера узла их количество на странице будет большим, и таблица-дерево будет низким, но широким.

                              Можно прикинуть на примере.
                              Пусть размер страницы — 4 Кбайт, а размер записи (листового элемента) — 200 байт.
                              Количество записей на странице = 4096 / 200 = 20. То есть в дереве с одним уровнем можно будет разместить 20 записей.
                              Размер узла = 8 байт на идентификатор + 8 байт на ссылку = 16 байт;
                              Количество узлов на странице верхнего уровня = 4096 / 16 = 256, округлим до 200.
                              В дереве с двумя уровнями будет 200 * 20 = 4000 записей.
                              Чтобы разместить 32 000 000 000 записей, понадобится 5 уровней. То есть по ссылке из вторичного индекса мы приходим в корневую страницу, а из нее спускаемся по ссылкам 4 раза и получаем наши данные.
                              Это немного дольше, чем хранить во вторичном индексе сразу физический адрес записи. Но нужно учесть, что корневая страница таблицы-индекса и большое количество верхних страниц попадут в кэш и переход по ссылкам будет производиться в памяти.
                                0
                                Ваше «немного дольше» будет из-за того, что количество физически считанных блоков будет больше. Думаю, что именно этот параметр и оптимизировали при выборе способа работы с базой.
                                  0
                                  А Вы смотрели ссылку на IOT от GlukKazan выше? Эта информация по Index-Organized Tables выложена на сайте Oracle, и там, насколько позволяют понять статью мои знания английского, весьма позитивный тон. Несмотря на некоторые издержки, в целом происходит именно ускорение доступа, как это ни странно. Примеры, почему это так, приведены прямо в статье.

                                  Даже автор топика в своем «третьем моменте» упоминает, правда с опечаткой, про INDEX ORGANISED TABLES в списке средств ускорения доступа к данным.

                                  Но вопрос даже не в том, какие таблицы лучше. Oracle дает возможность выбора, как будут храниться наши данные. Не заточен ли описанный в топике механизм логов для старых способов хранения данных и насколько он актуален для IOT и версионных данных?
                                    0
                                    Да знаю я про IOT. Они дают ускорение в определенных случаях. Но ведь у нас речь о «вторичных индексах». Если во всех индексах вместо rowid использовать urowid то все они будут «вторичными» по производительности. Для больших баз это критично. Так что механизм логов не считаю устаревшим.
                    0
                    Решение LevelDB. Append-only база, которая предполагает что индексы будут лежать в оперативной памяти.
                    0
                    0
                    Индексы тоже должны быть версионными. У читающих транзакций своя версия, у пишуших — своя. Должны же таблицы и индексы соответствовать друг другу.
                      0
                      Они и есть версионные. В Oracle реализована версионность на уровне блоков, а данные индексов хранятся в блоках как и данные таблиц. Что касается записи в другое место в сегменте таблицы, а не в сегменте UNDO, если мне не изменяет память, PostgreSQL так и работает?
              +3
              CASHE
              facepalm.jpg
                –5
                Я тоже дальше читать не смог. Интересно, как наезды на госдеп известной страны кореллируют с повальной безграмотностью нового поколения российских «специалистов»? Ну допустим, супостаты завербовали учителей в 90-х (и похоже, что вместе с врачами). Но Английский-то должен на высоте быть тогда!?
                +2
                Галопом по европе друг… сумбурно, много в куче и мягко говоря поверхностно… при рассказе про рекавери всетаки надо было ориентироваться на SCN а не мифические коммиты…
                  0
                  Не нашел в статье упоминания ACID.
                    0
                    В основном про букву D :)
                    +3
                    Почему синего назвали CASHE?
                      0
                      кАшерный
                        0
                        "- Тепло ли тебе девица, тепло ли, синяя?" :))
                      0
                      >При поступлении этой команды информация, пусть и в небольшом количестве, должна быть записана на диск.

                      Не совсем верно.

                      >И напоследок – используйте связанные переменные! Это позволит разом значительно, часто в несколько раз увеличить производительность

                      Или замедлить, на порядки…
                        –1
                        Пример, где связанные переменные замедляют производительность, в студию!
                        in short, cursor sharing is a crutch that MIGHT be useful in some cases to help a
                        poorly written program survive for the period of time the developers are hard at work
                        CORRECTING their bug and putting bind variable support into their application
                        Это дядя Том говорит про параметр cursor_sharing = force, который позволяет насильно вставить связанные переменные вместо констант. Пользовался два раза с тупым покупным софтом. Очень выручило.

                        Связанные переменные позволяют избежать замусоривание кэша и постоянного построения плана запроса.
                        "select a from t where a=1" и "select a from t where a=2" — это два разных запроса. "select a from t where a=:a" — один. При любых :a
                          0
                          Представьте таблицу order. В ней есть state. База оч старая, 97% записей в таблице имеют State = 'Executed', всего 6 статусов.
                          А теперь представьте, мы сутра выполняем дважды запрос
                          select * from t_order where state = :a
                          с a: = 'Executed' и a: = 'InTransit'

                          При выполнении первого запроса оракл создаст для него план, который конечно же сделает фулл скан.
                          А при выполнении второго запроса план будет пере использован. И мы получим фулл скан на ровном месте, т.к. у оракла есть гистограмма, и он знает что select * from t_order where state = 'InTransit' дает где то 0,5% записей.

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

                          Понятно, что отказ от байнд переменных не идеальное решение, в частности оно сильно затрудняет анализ нагрузки на базу. Но говорить что для всех ситуаций с байнд переменным будет быстрее не верно.
                            0
                            Да, действительно, Вы правы. Про весёлую игру «построй оптимальный план» я совершенно не подумал. Давно не DBA`шничал, пора снова в школу на переподготовку.
                            Статейка отличная, спасибо.
                              0
                              Adaptive cursor sharing и cardinality feedback скрасят картину
                              +1
                              Интересная статейка на тему practical-sql-tuning.blogspot.ru/2008/10/bind-peeking.html
                              У нас 10g как раз и я сталкивался с такой ситуацией на боевом. Вероятно, для 11g я не прав.
                              0
                              >>При поступлении этой команды информация, пусть и в небольшом количестве, должна быть записана на диск.

                              >Не совсем верно.

                              Вы про batch и nowait параметры или что-то другое?
                                0
                                Оракл выкидывает редо на диск раз в н секунд, при заплнении буфера на м процентов и ещё когда то. Так что может быть в момент комита реду уже давно на диске.
                                  0
                                  Из своего опыта могу сказать, что системы ввода-вывода кроме ограничения на количество мегабайт в секунду имеют также ограничение на IOPS — количество операций ввода-вывода в секунду. Большое количество commit приводят к серьезной деградации как раз по потолку IOPS (сессии ждут выполнения lgwr-ом log file sync). Можете попробовать на какой-нибудь тестовой системе. Так что один commit — как мимнимум одна операция ввода-вывода.

                                    0
                                    Кажется я понял, в чем Вы заблуждаетесь. Commit — это не только выкидывание redo на диск, а непосредственно запись некой последовательности байтов, обозначающих команду commit в redo-лог. И она не может быть записана на диск раньше, чем commit произойдет — ведь может быть и rollback :)
                                      0
                                      Да, спасибо, я как то этот момент совершенно упустил.

                                      Честно говоря написал так — для демагогии:) Я не выступаю за очень частые комиты.
                                +3
                                А как избежать SQL с помощью bind-переменных?
                                И главное — зачем?
                                  +1
                                  пропущено слово парсинг.
                                  0
                                  > Второй момент, почему-то неочевидный для многих разработчиков — изменение данных никогда не блокируют чтение.
                                  Еще более неочевидный момент, Oracle не поддерживает Serializable уровень изоляции. То есть то, что Oracle называют Serializable на самом деле является Snapshot isolation. Оно и понятно — используя MVCC и не имея честных блокировок на чтение true serializable не добиться.
                                    0
                                    И еще более неочевидный момент — SQL стандарт это позволяет, там есть маааленькая дырка, которая дает возможность ставить не-serializable уровень по SET TRANSACTION ISOLATION LEVEL SERIALIZABLE.
                                    0
                                    Спасибо, интересная статья. Картинки доставляют.
                                      0
                                      Flashback queries has nothing to do with archivelogs. It uses UNDO, not REDO to get block's state at specific point of time / SCN in past.

                                      Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                                      Самое читаемое