Database performance

Фрагментация таблиц в бд Oracle 12c, которой нет в 19с. Часть 1.

Krotov KonstantinОставьте комментарий

Окружение:

OS: OEL 7.8

DB: 12.1.0.2.190716 BP,  2. 19.7.0.0.200414 RU

При выполнений пакетных заданий и не только было замечено интересное — фрагментация таблиц, где казалось бы её не должно быть(или должно?).

Для проверки влияния был составлен тест из 3 циклов:

 1 цикл — главный.

2 цикл находится внутри цикла 1, выполняет вставку.

3 цикл находится внутри цикла 1, выполняет удаление.

Количество проходов циклов 2 и 3 одинаково и задаётся при старте, циклы выполняются друг за другом, commit выполняется после каждой удалённой или вставленной строки.

Тест проводится с участим трёх таблиц(KKS$FRAG_512B, KKS$FRAG_1024B, KKS$FRAG_2048B), в которых длина вставляемой строки заранее известна, это 512байт, 1024байт и 2048байта. Размер сегмента берётся из dba_segments.

Исходный код теста

Для таблиц KKS$FRAG_512B, KKS$FRAG_1024B, KKS$FRAG_2048B было выполнено:

Количество цикловКоличество операций вставок и удалений в каждом цикле
3032003
6061006
606706
606506
606106
Таб.1
  1. Результат для KKS$FRAG_512B:
12.1.0.2.190716 BP, занято байт в таблице KKS$FRAG_512B33554432
12.1.0.2.190716 BP, занято блоков в таблице KKS$FRAG_512B4096
19.7.0.0.200414 RU, занято байт в таблице KKS$FRAG_512B7340032
19.7.0.0.200414 RU, занято блоков в таблице KKS$FRAG_512B896
Таб.2 KKS$FRAG_512B

Различие в размере сегмента составляет 4,5 раза.

2. Результат для KKS$FRAG_1024B:

12.1.0.2.190716 BP, занято байт в таблице KKS$FRAG_1024B33554432
12.1.0.2.190716 BP, занято блоков в таблице KKS$FRAG_1024B4096
19.7.0.0.200414 RU, занято байт в таблице KKS$FRAG_1024B9437184
19.7.0.0.200414 RU, занято блоков в таблице KKS$FRAG_1024B1152
Таб.3 KKS$FRAG_1024B

Отличие скромнее, но оно по прежнему существенно — в 3,5 раза.

3. Результат для KKS$FRAG_2048B:

12.1.0.2.190716 BP, занято байт в таблице KKS$FRAG_2048B19922944
12.1.0.2.190716 BP, занято блоков в таблице KKS$FRAG_2048B2432
19.7.0.0.200414 RU, занято байт в таблице KKS$FRAG_2048B16777216
19.7.0.0.200414 RU, занято блоков в таблице KKS$FRAG_2048B2048
Таб.4 KKS$FRAG_2048B

Разница не велика, но она есть: 1,18 раза

4. Другой профиль нагрузки для KKS$FRAG_512B.

Количество цикловКоличество операций вставок и удалений в каждом цикле
20021002
Таб.5 KKS$FRAG_512B
12.1.0.2.190716 BP, занято байт в таблице KKS$FRAG_512B46137344
12.1.0.2.190716 BP, занято блоков в таблице KKS$FRAG_512B5632
19.7.0.0.200414 RU, занято байт в таблице KKS$FRAG_512B720896
19.7.0.0.200414 RU, занято блоков в таблице KKS$FRAG_512B88
Таб.6 KKS$FRAG_2048B

Разница: в 64 раза

5. Другой профиль нагрузки для KKS$FRAG_1024B.

Количество цикловКоличество операций вставок и удалений в каждом цикле
20021002
Таб.7 KKS$FRAG_1024B

Результат:

12.1.0.2.190716 BP, занято байт в таблице KKS$FRAG_1024B262144
12.1.0.2.190716 BP, занято блоков в таблице KKS$FRAG_1024B32
19.7.0.0.200414 RU, занято байт в таблице KKS$FRAG_1024B196608
19.7.0.0.200414 RU, занято блоков в таблице KKS$FRAG_1024B24
Таб.6 KKS$FRAG_1024B

Разница: в 1.3 раза

Исходя из результатов тестов можно сделать вывод: при переходе на версию 19с, рост бд возможно замедлиться при одинаковой нагрузке. Самое время понять, что приводит к такому различию размеров таблиц.

Резкий рост log file sync и buffer busy wait при незначительном повышении нагрузки.

Krotov KonstantinОставьте комментарий

Для начала приведу первоисточник, благодаря которому, проблема была локализована и устранена:

  1. High log file sync waits? Check log parallelism!

2.  Finding the root cause of “CPU waits” using stack profiling

В первоисточнике вы можете подробнее ознакомится с механизмами и параметрами, которые будут упомянутs здесь, я же только опишу конечный результат, который получился у меня.

Описание проблемы: после того как бд переехала на сервер с 4мя сокетами, по достижению определённой нагрузки, резко вырастали ожидания  log file sync и buffer busy wait.

Окружение: RHEL 7, Oracle 12.1.0.2 + BP, ASM 12.1.0.2 + BP

Как это выглядит:

AWR:

Lab128:

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

1.      Первым делом решено было бороться с log file sync(LFS).

Из ссылки 1 я узнаю про параметр _log_parallelism_max.

Критерии, по которым можно попробовать оценить, есть ли смыл в изменении его значения по умолчанию:

1.      Ожидание log file sync гораздо выше(более чем в 2 раза) log file parallel write

Например, log file sync= 1мс, а log file parallel write=2мс и выше.

2.      Оценить значении log file parallel write P3(Number of I/O requests) из v$active_session_history/dba_active_session_history в нагруженный и не нагруженный период. P3 должен изменяться, от 1 до _log_parallelism_max т.е. быть 2,3,4,5…

3.      При повышении значения log file parallel write P3(Number of I/O requests), log file sync должен расти, но возможно не обязан.

4.      Количество потоков CPU больше, либо равно 64.

5.      Log File Parallel Writes Take Longer with a Higher CPU Count (Doc ID 1980199.1)

Я применил значение _log_parallelism_max=2

Результат:

AWR:

log file sync снизился с 5.68мс до 1.15мс(по AWR), казалось бы победа, но нет, присутствует ожидание buffer busy wait, которое оказывает сильное влияние на работу приложения, да и само значение log file sync всё ещё выше, чем на старом сервере, даже после улучшения.

2.   Здесь переходим ко второй части.

Ожидания buffer busy wait с работой приложения никак не связано, профиль нагрузки практически не менялся. Kоличественные характеристики полезной работы бд: dml выполненных в секунду, количество коммитов в секунду, LIO в секунду в целом выросли на 5%-10% и после переезда на 4 сокета, buffer busy wait раскрылся во всей красе, опять таки только по достижению определённой нагрузки(превышение на 5%-10%), если её не превышать, то всё хорошо.

Всё хорошо — это означает, что ожидания buffer busy wait стремятся к нулю, а log file sync значительно меньше 1мс (0.3-0.6мс) для работы под нужной нагрузкой.

Вышеописанное должно привести на мысль, что что-то не так с железом, либо проблема на уровне OS. Здесь на помощь приходит вторая ссылка, из которой становится понятно, что вероятно дело в kernel.numa_balancing. И действительно, если поискать на MOS используя kernel.numa_balancing, то получим:

  1. Requirements for Installing Oracle Database 19c on OL7 or RHEL7 64-bit (x86-64) (Doc ID 2551169.1)
  2. (EX39) NUMA-enabled database servers experience continuously high load or reduced performance after updating to Exadata 12.2.1.1.0 or higher. (Doc ID 2319324.1)

А также из общедоступных источников: Optimal Configuration of Memory System

https://support.huawei.com/enterprise/en/doc/EDOC1000117211/1395f4a4/optimal-configuration-of-memory-system

Откуда следует вывод, что без kernel.numa_balancing=0 никуда. И действительно, после применения данного параметра, производительность бд значительно выросла:

В итоге, конфигурация имеет значение _log_parallelism_max=2 на уровне бд и kernel.numa_balancing=0 на уровне ОС.

Также эксперимент был проведён на Solaris Sparc:

 Solaris Sparc : 11.4+patch

Oracle 12.1.0.2 + BP

ASM 19.7

И _log_parallelism_max=2 VS_log_parallelism_max=1:

Нагрузка тут уже в 3 раза выше, чем на Linux, а снижение LFS c 0.77 мс до 0.60 мс дало прирост быстродействия на стороне приложения до 30%.

Также _log_parallelism_max=1 снизил потребление ЦПУ на 7%.

Выводов нет, делайте их сами, а использование скрытых параметров должно быть обоснованным и обдуманным решением.

Enq: SS- Contention

Krotov KonstantinОставьте комментарий

На эту тему есть хорошее описание от Riyaj Shamsudeen( https://orainternals.wordpress.com/2012/02/13/temporary-tablespaces-in-rac/). Откуда берётся это ожидание и почему так. Также есть ещё статья https://ebs12blog.wordpress.com/2017/09/24/enq-ss-contention-and-enq-ts-contention-waits-in-a-rac-database-in-ebs-environment , есть и другие статьи.

И есть нота на MOS: EVENT: DROP_SEGMENTS — Forcing cleanup of TEMPORARY segments (Doc ID 47400.1). Но всё выше описанное может ‘показаться’ не рабочим или ресурсоёмким.

Я пройдусь по возможным вариантам(а может есть ещё варианты ?), как избежать enq: ss- contention:

1.      Выполнить ‘какой либо’ resize.

Это может быть, как добавление темп файлов, так и выполнение shrink табличного пространства или coalesce.

Данный метод неэффективен (сколько можно добавлять файлов?) и ресурсоёмким, шринк/coalesce темпа создаёт нагрузку на ввод-вывод (регулярно это делать- сомнительное дело).

2.      Выполнить рекомендацию из ноты 47400.1

Если выполнить так как описано, то с виду будет выглядеть так, как будто решение alter session set events ‘immediate trace name DROP_SEGMENTS level TS#+1 не работает, эффект будет не заметен.

Первый вариант выглядит нерациональным, но если выбора нет, то и он сойдёт)))

А вот второй вариант выглядит более интересным, и он описан в блоге ebs12blog.wordpress.com(ссылка выше).

И тут главное не упустить две важные детали:

1.      Выполнять alter session set events ‘immediate trace name DROP_SEGMENTS level TS#+1 нужно в цикле, т.к. за одно выполнение освобождается определённое количество (или % ?) экстентов.

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

После выполнения скрипта на каждом инстансе бд, выглядит это так (gv$sort_segment):

Нет занятых и свободных. 🙂

Регулярное выполнение DROP_SEGMENTS level TS#+1 на нодах, побочных эффектов не выявило. Версия бд 12.1.0.2

Длительное ожидание latch: row cache objects

Krotov KonstantinОставьте комментарий

Я с коллегой разбирали интересный случай, связанный с ожиданием ‘latch: row cache objects’ при выполнении запроса.

По ASH на ожидание ‘latch: row cache objects’ уходило значительная часть времени (до 48%):

Большое количество времени теряется на ожидании ‘latch: row cache objects’ Дополнительно, проанализировав трассировку 10046, выявлена большая ‘не учтённая’ часть времени:

Данные приведённые на рис 1. и рис. 2 взяты от разных сессий, но сути дела это не меняет. Далее выяснилось, что конкуренция ведётся за области словаря данных dc_users и dc_tablespace.

Исходя из доступных описаний dc_users и dc_tablespace, непонятно, для чего понадобилась блокировка(latch) при выполнении оператора select. Далее, была использована утилита perf для анализа вызовов на уровне OS и получена следующая картина:

На основе имеющихся данных была найдена статья ‘12c: эффекты Automatic Dynamic Sampling’.

Где указывалось, что защёлки ‘dc_users’ и ‘dc_tablespace’ могут быть связаны с HJ и багом 13902396. В баге 13902396, в разделе DIAGNOSTIC ANALYSIS есть ‘qerhjFetch’ как на рисунке, и решение на основе HJ.

Смотрим ASH ещё раз:

Теперь проблема выглядит не такой сложной…

Проблема воспроизводится в 11.2.0.4 и 12.1.0.2, обе версии имеют нужные PSU. 

Теперь мы можем попробовать сделать что ни будь с HJ. Я принял решение использовать параметр “_gby_hash_aggregation_enabled = false” на уровне сессии(это задание по расписанию). После применения параметра “_gby_hash_aggregation_enabled = false”, ожидание защёлки исчезло, производительность определённого SQL выражения выросло в 4 раза, с 5тыс выполнений в секунду до 20тыс выполнений в секунду.

latch: row cache objects. dc_rollback_segments

Krotov KonstantinОставьте комментарий

Версия Oracle 12.1.0.2.170117.

Входные данные: сессии находятся в ожидании latch: row cache objects, раздел dc_rollback_segments, что очень замедляет работу базы данных. dc_rollback_segments легко виден в AWR за проблемный период в разделе Dictionary Cache Stats.

При таких входных данных, на MOS десятки багов, которые исправлены в 12.1.0.2, а то и в 11.2 или ещё раньше.

Собираем информацию далее.

Смотрим в v$undostat.tuned_undoretention, затюненое retention плавно добралось до 90000 сек, поднимали это значение два запроса, время выполнения которых было гораздо, гораздо ниже 90000сек.

Далее смотри dba_undo_extents.status, и выясняется, что почти все экстенты UNEXPIRED, очень мало ACTIVE, и почти нет EXPIRED.

И того имеем:

 undo_retention=20000 в файле параметров(spfile).

Затюненое tuned_undoretention=90000

Свободных undo экстентов нет.

Проверяем новые данные на MOS, в уме держим мысль: “как уменьшить tuned_undoretention?”. И находим, очень похожий баг, который исправлен в 11.2.0.1,10.2.0.5 и т.д, про 12.1 ни слова.

Bug 7291739 — Contention with auto-tuned undo retention or high TUNED_UNDORETENTION (Doc ID 7291739.8)

В качестве решения проблемы, включаем два скрытых параметра, первый, отключение undo автотюненга, второй выставление наивысшего порога undoretention.