Oracle mechanics

08.03.2010

FIRST_ROWS для веб-приложений при ограниченных процессорных ресурсах

Filed under: CBO,heuristics,Oracle,persistent connection,web — Игорь Усольцев @ 17:34
Tags: , ,

Конфигурация: нагруженный 2-х серверный (серверы веб и бд) веб-сайт клиента. Первоначальная проблема проявлялась в периодических отказах в обслуживании (HTTP Error 502 Bad gateway), сопровождающимися сообщениями в nginx error.log

upstream timed out

Сотрудники клиента не мудрствуя и по-своему логично увеличивают количество [дочерних] процессов apache c 30 до 70

StartServers 70
MinSpareServers 70
MaxSpareServers 70
MaxClients 70

Сервер бд отреагировал достаточно грустно

top - 10:55:57 up 7 days, 22:56,  1 user,  load average: 39.55, 41.54, 49.93
Tasks: 208 total,   6 running, 204 sleeping,   0 stopped,   0 zombie
Cpu(s): 13.8%us,  2.2%sy,  0.0%ni, 37.3%id, 46.2%wa,  0.1%hi,  0.3%si,  0.0%st
Mem:   8179324k total,  8122028k used,    57296k free,    14964k buffers
Swap:  8393920k total,   819192k used,  7574728k free,  6215620k cached

Отказы в обслуживании учащаются, сайт работает через раз :(Судя по данным с сервера бд, повышенная нагрузка (load average) вызвана значительным использованием swap, что в свою очередь является следствием увеличившегося количества сессий веб-сервера (до 70), потребляющими значительные ресурсы памяти — инициализационные параметры Oracle SGA были подобраны для 30-40 пользовательских процессов — со стороны веб-сервера используются постоянные соединения [permanent connections]

Очевидным быстрым решением было уменьшить на веб-сервере максимальное количество процессов апача до нормального уровня 30:

StartServers         30
MinSpareServers      30
MaxSpareServers      30
MaxClients           30

и разобраться с действительными причинами возникновения проблем, которые нашлись и на стороне веб-сервера (большое количество ~50,000 устаревших файлов в кэше приложения значительно замедляли работу PHP кода с диском), и на стороне сервера бд, где были заметны очевидно неэффективные запросы типа

SELECT ...
 FROM forum_message FM
 LEFT JOIN forum_user FU ON FM.AUTHOR_ID = FU.USER_ID
 LEFT JOIN user U ON FM.AUTHOR_ID = U.ID
 WHERE ((FM.FORUM_ID = :"SYS_B_5"))
 AND ((FM.TOPIC_ID <= :"SYS_B_6"))
 AND ((FM.APPROVED = :"SYS_B_7"))
 ORDER BY FM.ID DESC

на план выполнения которых никак не влияло создание хорошего (с высокой избирательностью) индекса

SQL> create index IX_FORUM_MESSAGE_4 on FORUM_MESSAGE("FORUM_ID", "TOPIC_ID", "APPROVED");
Index created.

Оптимизатор упорно выбирает план с использованием полного сканирования индекса (INDEX FULL SCAN DESCENDING) по первичному ключу (SYS_C006830)

SQL> select * from TABLE(dbms_xplan.display_cursor('','','ALLSTATS  LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
 | Id  | Operation                      | Name                | Starts |  E-Rows | A-Rows |   A-Time   | Buffers | Reads      |
 -------------------------------------------------------------------------------------------------------------------------
 |*  1 |  FILTER                        |                     |      1  |        |      0 |00:02:51.98 |    1073K|    128K|
 |   2 |   NESTED LOOPS OUTER           |                     |      1  |      1 |      0 |00:02:51.98 |    1073K|    128K|
 |   3 |    NESTED LOOPS OUTER          |                     |      1  |      1 |      0 |00:02:51.98 |    1073K|    128K|
 |*  4 |     TABLE ACCESS BY INDEX ROWID|   FORUM_MESSAGE     |      1  |      1 |      0 |00:02:51.98 |    1073K|    128K|
 |   5 |      INDEX FULL SCAN DESCENDING| SYS_C006830         |      1  |    910K|    910K|00:00:00.01 |    2344 |    662 |
 |   6 |     TABLE ACCESS BY INDEX ROWID|   USER              |      0  |      1 |      0 |00:00:00.01 |       0 |      0 |
 |*  7 |      INDEX UNIQUE SCAN         | SYS_C007495         |      0  |      1 |      0 |00:00:00.01 |       0 |      0 |
 |   8 |    TABLE ACCESS BY INDEX ROWID |   FORUM_USER        |      0  |      1 |      0 |00:00:00.01 |       0 |      0 |
 |*  9 |     INDEX UNIQUE SCAN          | IX_FORUM_USER_USER6 |      0  |      1 |      0 |00:00:00.01 |       0 |      0 |
 -------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
 ---------------------------------------------------
 1 - filter(:SYS_B_3=:SYS_B_4)
 4 - filter(("FM"."TOPIC_ID"<=:SYS_B_6 AND "FM"."FORUM_ID"=:SYS_B_5  AND "FM"."APPROVED"=:SYS_B_7))
 7 - access("FM"."AUTHOR_ID"="U"."ID")
 9 - access("FM"."AUTHOR_ID"="FU"."USER_ID")

Причина такого поведения CBO — в использовании значения параметра OPTIMIZER_MODE = first_rows на уровне инстанса, что хорошо показывает 10053 трейс

SINGLE TABLE ACCESS PATH
...
Best:: AccessPath: IndexRange  Index: IX_FORUM_MESSAGE_4
Cost: 4.00 Degree: 1  Resp: 4.00  Card: 0.02  Bytes: 0
...
****** Recost for ORDER BY (using index) ************
***************************************
SINGLE TABLE ACCESS PATH
...
Best:: AccessPath: IndexRange  Index: SYS_C006830
Cost: 748769.86 Degree: 1  Resp: 748769.86  Card: 0.02  Bytes: 326

даже несмотря на проигрыш в стоимости доступа более, чем на 5 порядков!

Изменение значения параметра OPTIMIZER_MODE на более «стоимостные» значения first_rows(1) или all_rows на уровне текста запроса невозможно, а изменение на уровне инстанса увеличивает нагрузку на процессор сервера бд (из-за массового изменения планов выполнения [других] запросов от сканирования по индексам к использованию hash join) и на диск — опять же из-за увеличения количества hash join и, соответственно, ожиданий direct path read/direct path write

На уровне инстанса было оставлено значение OPTIMIZER_MODE = first_rows, а для проблемных запросов — использована технология SQL Profiles

DECLARE
my_sql_id VARCHAR2(30) := 'my_sql_id';
my_task_name VARCHAR2(30);
BEGIN
begin
DBMS_SQLTUNE.DROP_TUNING_TASK(my_sql_id);
exception when others then NULL;
end;
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => my_sql_id,
scope       => 'COMPREHENSIVE',
time_limit  => 600,
task_name   => my_sql_id,
description => 'SQL analysis for SQL_ID='||my_sql_id);
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => my_task_name);
END;
begin
dbms_sqltune.accept_sql_profile(task_name => 'my_sql_id'
, replace => TRUE
,force_match => true);
end;

что ожидаемым образом влияет на план выполнения проблемного запроса

select plan_table_output from table(dbms_xplan.display_cursor('my_sql_id','0'))
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                     |       |       |     7 (100)|          |
|   1 |  SORT ORDER BY                  |                     |     1 |   413 |     7  (15)| 00:00:01 |
|*  2 |   FILTER                        |                     |       |       |            |          |
|   3 |    NESTED LOOPS OUTER           |                     |     1 |   413 |     6   (0)| 00:00:01 |
|   4 |     NESTED LOOPS OUTER          |                     |     1 |   367 |     5   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID|   FORUM_MESSAGE     |     1 |   326 |     4   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN          | IX_FORUM_MESSAGE_4  |     1 |       |     3   (0)| 00:00:01 |
|   7 |      TABLE ACCESS BY INDEX ROWID|   FORUM_USER        |     1 |    41 |     1   (0)| 00:00:01 |
|*  8 |       INDEX UNIQUE SCAN         | IX_FORUM_USER_USER6 |     1 |       |     0   (0)|          |
|   9 |     TABLE ACCESS BY INDEX ROWID |   USER              |     1 |    46 |     1   (0)| 00:00:01 |
|* 10 |      INDEX UNIQUE SCAN          | SYS_C007495         |     1 |       |     0   (0)|          |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:SYS_B_3=:SYS_B_4)
6 - access("FM"."FORUM_ID"=:SYS_B_5 AND "FM"."APPROVED"=:SYS_B_7 AND
"FM"."TOPIC_ID"<=:SYS_B_6)
filter("FM"."APPROVED"=:SYS_B_7)
8 - access("FM"."AUTHOR_ID"="FU"."USER_ID")
10 - access("FM"."AUTHOR_ID"="U"."ID")
Note
-----
- SQL profile "SYS_SQLPROF_0149e58ae8474001" used for this statement

и благотворно — на общее состояние сервера бд

top - 18:24:32 up 8 days,  6:25,  1 user,  load average: 2.10, 2.76, 3.49
Tasks: 170 total,   5 running, 165 sleeping,   0 stopped,   0 zombie
Cpu(s): 12.7%us,  1.2%sy,  0.0%ni, 79.6%id,  6.1%wa,  0.1%hi,  0.4%si,  0.0%st
Mem:   8179324k total,  8131108k used,    48216k free,    10684k buffers
Swap:  8393920k total,    37364k used,  8356556k free,  6768780k cached

Добавить комментарий »

Комментариев нет.

RSS feed for comments on this post. TrackBack URI

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Блог на WordPress.com.

%d такие блоггеры, как: