Отдавна не съм споделял oracle-related приключения. Пусти мързел…
Днес ще разкажа за следната случка. На всеки час едно приложение „замръзва“ за няколко секунди. Всъщност идентифицирането на причините отне доста усилия. Видимите симптоми се появяват на ниво application server. Ровихме из настройките там, ровихме из мрежата, ОС, базите…
На края успяхме да идентифицираме следната зависимост: това приложение (покрай другите неща) използва и една супер натоварена таблица. Информацията в тази таблица се пази няколко часа, след това се изтрива; за това пък за тези няколко часа се натрупват доста данни – гигабайти. Операциите са предимно Insert и Select. Таблицата е разделена на partitions по часове, като на всеки час един job минава и затрива данните от най-стария partition с
alter table ... truncate partition ... UPDATE GLOBAL INDEXES
Това UPDATE GLOBAL INDEXES се налага, защото първичния ключ на таблицата има глобален индекс. Това е така, защото на partitioned таблици, уникалния индекс или трябва да е глобален, или да съдържа partition key колоните (логично).
Оказа се, обаче, че в тази схема има голям проблем. Странното „замръзване“ на приложението съвпада точно с времето, когато се стартира този този TRUNCATE job.
По-внимателно вглеждане в базата, съдържаща описаната таблица, показа, че в продължение на няколко секунди (почти) всички сесии, които се опитват да INSER-ват в тази натоварена таблица, чакат cursor: pin S wait on X. Има и малко library cache lock. Въпреки че говорим за времена от рода на 4-5-6 секунди, се натрупват един забележим брой сесии, които „висят“.
* * *
Какво се случва всъщност?
Disclaimer: следва обяснението, което аз приемам за вярно. Може и да бъркам нещо. Ако греша някъде, моля, поправете ме.
truncate (или в случая, alter table ... truncate partition) e DDL заявка. Като такава тя инвалидира всички курсори, които работят със съответната таблица. И когато дойде време за поредния insert (а такива има по десетки в секунда), първата сесия, която види невалидния курсор, тръгва да parse-ва. Тук говорим за parse на един елементарен insert на един ред в една таблица – би трябвало да стане за милисекунди. Обаче за да се случи parse, сесията трябва да „заключи“ за момент дефиницията на таблицата в library cache. В противен случай има опасност да parse-не, да измисли някакъв план, а някой друг да промени структурата на таблицата и плана да излезе неверен. Това заключване се случва с library cache lock.
Да, обаче в това време truncate-a си върви. insert сесията трябва да изчака truncate-a да свърши. Чак тогава може да получи заключваницата върху структурата на таблицата и да си направи parse-а. Цялата тази дандания е на пръв поглед ненужна при truncate – нали не променя по никакъв начин структурата на таблицата. Обаче е DDL. За това първия insert започва да чака.
Няколко милисекунди след това идва друга сесия, която също иска да insert-ва. Тази сесия иска да види дали има готов parse-нат план за заявката или не. Изчислява си hash на заявката, поглежда в library cache и за да е сигурна, удря един адски бърз lock на този hash. Във 10g/11g това става с използването на мутекси. Да, обаче в това време предишния insert още се мъчи да parse-не (всъщност чака). За това имаме невероятния шанс да видим заключване на мутекс – една операция, която трябва да става за микросекунди. Това чакане е известно като cursor: pin S wait on X.
Същото става и със следващата сесия, която опита insert. И със следващата. И със следващата…
* * *
По принцип truncate е много бърза операция. В крайна сметка тя е само промяна в data dictionary и не трябва да зависи от обема данни. Обаче в случая имаме един изроден truncate, който, освен нулирането на HWM, се занимава да оправя и разни индекси.
След като tuncate приключи, кълбото моментално се разплита в обратен ред. Но вече сме имали няколко секунди downtime на приложението. И това става на всеки час.
От Oracle са идентифицирали това положение като проблемно в bug 7640597. Даже са направили и пач. Идеята на пача е гениално проста и ефективна: инвалидирането на курсорите се случва в самия край на truncate операцията. Така те могат веднага да бъдат re-parse-нати.
За съжаление при пускането на този patch се оказва, че той има нежелани „странични ефекти“. За това да го оттеглили. Проблема е решен генерално в бъг 8441239, който е оправен в 11.2. Обаче няма backport за по-стари версии.
* * *
В този случай имаме няколко опции:
1. Да се откажем от TRUNCATE (и други алтернативни варианти, включващи DDL – примерно alter table exchange partition). Вместо това да използваме DELETE. Това ще доведе до много по-голямо натоварване на тази критична база; ще генерира огромно количество redo; и ще работи много по-бавно. Но понеже е прост DML, няма да инвалидира курсори.
2. Може да направим TRUNCATE по-бърз като направим partitions на половин час, или даже по-малко. Така всеки TRUNCATE ще има по-малко редове за оправяне в индекса, респективно – ще минава по-бързо. Това, обаче, само отлага проблема. При нарастване на обемите, или в някой по-натоварен момент, ще се озовем пред същата драма, а може и по-голяма.
3. Може да добавим колоната, по която се прави partitionig, като част от първичния ключ. В този случай индекса може да стане локален и TRUNCATE ще минава с обичайната си страшна скорост. Но това решение не е винаги възможно – зависи от самите данни и начина на използването им.
4. Може да upgrade-нем към 11.2. Oracle обещават, че там проблема е отстранен. Това, обаче, не е решение от днес за утре.
5. Може да настояваме за backport на patch-а. За целта трябва „a serious business justification“. Това, обаче, може да отнеме седмици или месеци; а и ще бъдем първите, които да изпитат новия patch в production.
Кой е верния път зависи от конкретната бизнес ситуация. За нуждите на блога няма да изпадам в подробности за нашия избор. Ще покажа, обаче, как може да тествате възможните решения, ако се натъкнете на този проблем.
Stay tuned!