Oracle mechanics

19.04.2014

Практический тест параллельной последовательной (многоблочной) записи на SSD

Filed under: Диагностика системы (instance),Oracle,PX,temp segment — Игорь Усольцев @ 23:32
Tags: ,

Некоторое время назад с Дмитрием Якубеней использовали возможность протестировать SSD на предмет массированной/параллельной многоблочной записи (что обычно считается слабым местом SSD против обычных дисков), в роли временного/temporary tablespace на бд с преобладающими операциями direct path write temp / direct path read temp в профиле нагрузки

Кратко можно отметить, что один SSD почти успешно заменил семь SATA (7,200 rpm) дисков в части записи-чтения временных сегментов на умеренно нагруженной бд DWH профиля. При этом на протяжении тестов все остальные файлы бд оставались на дисковой группе SATA, однако временная запись составляла заметно более 50% всей записи, а чтение >= 35-40% от общего объёма чтения бд. Почти успешно потому, что замедление временной записи всё-таки было заметно для отдельных запросов в относительно высоким DOP

Некоторые детали и прочая статистика

Из общей исторической статистики интересующего ожидания direct path write temp:

SQL> @dba_hist_system_event "event_name = 'direct path write temp' and snap_id between 52545 and 52752"
 
SNAP_ID BEGIN_INTERVAL_TIME EVENT_NAME              WAIT_COUNT AVG_WAIT_TIME_NS
------- ------------------- ----------------------- ---------- ----------------
  52545 11.02.2014 16:00    direct path write temp       90556            75997 -- использовался SATA TEMP
  52546 11.02.2014 17:00    direct path write temp       15429             6693
  52547 11.02.2014 18:00    direct path write temp       37971            13783
  52548 11.02.2014 19:00    direct path write temp       15814             7237
  52549 11.02.2014 20:00    direct path write temp       24231            16748
  52551 11.02.2014 22:00    direct path write temp       58024            29364 -- переключились на SSD TEMP
  52552 11.02.2014 23:00    direct path write temp        7717             4539
  52553 12.02.2014 00:00    direct path write temp        9315             4219
...
  52607 14.02.2014 06:00    direct path write temp       46925             7153
  52608 14.02.2014 07:00    direct path write temp      570698            21294
  52609 14.02.2014 08:00    direct path write temp      184164             8642
...
  52727 19.02.2014 06:00    direct path write temp       48636            16249
  52728 19.02.2014 07:00    direct path write temp      242981            27030
  52729 19.02.2014 08:00    direct path write temp      366523            21332 -- AWR SSD (*)
  52730 19.02.2014 09:00    direct path write temp      135915             2745
  52731 19.02.2014 10:00    direct path write temp       49294            14561
  52732 19.02.2014 11:00    direct path write temp       12256             6017
  52733 19.02.2014 12:00    direct path write temp       34807             5100
  52734 19.02.2014 13:00    direct path write temp       26608            17918
  52735 19.02.2014 14:00    direct path write temp       39440             8593
  52736 19.02.2014 15:00    direct path write temp       13331             5096
  52737 19.02.2014 16:00    direct path write temp        3186             3342
  52740 19.02.2014 19:00    direct path write temp       28492            12496 -- далее вернулись на SATA TEMP
  52741 19.02.2014 20:01    direct path write temp       22868            14474
...
  52751 20.02.2014 06:00    direct path write temp       50082            28341
  52752 20.02.2014 07:00    direct path write temp      331446            20369
  52753 20.02.2014 08:00    direct path write temp      343749            24104 -- AWR SATA (**)

— можно заключить, что в среднем SSD не показал ухудшения при сравнимых нагрузках, отмеченные звёздочками снапшоты характеризуются достаточно высокой нагрузкой и далее будут более детально рассмотрены

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

SQL> select decode(session_type,'BACKGROUND',REGEXP_SUBSTR(program, '\([^\)]+\)'), nvl2(qc_session_id, 'PX', 'FOREGROUND')) as SESSION_TYPE,
  2         count(*),
  3         p3text,
  4         round(avg(p3)) as AVG_BLOCK_COUNT
  5  from dba_hist_active_sess_history
  6       where snap_id in (52754, 52730)
  7         and event = 'direct path write temp' and session_state = 'WAITING'
  8  group by decode(session_type,'BACKGROUND',REGEXP_SUBSTR(program, '\([^\)]+\)'), nvl2(qc_session_id, 'PX', 'FOREGROUND')), p3text
  9  /
 
SESSION_TYPE          COUNT(*) P3TEXT       AVG_BLOCK_COUNT
------------------- ---------- ------------ ---------------
PX                        1471 block cnt                 13 -- случайно красиво
FOREGROUND                 147 block cnt                 14 -- ровно 1:10

— т.е. более 90% приходится на PX-slave процессы, пишущие в среднем по 13 x 16КБ блоков = более 200КБ за одну операцию записи, что вполне соответствует параллельной последовательной (многоблочной) записи, имхо

*) Отмеченный AWR при использовании SSD в качестве TEMP:

11.2.0.3.@ SQL> select output from table( DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(  1241709836,  1, 52729, 52730));

...
Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                           Avg                  
                                                          wait   % DB           
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
direct path read temp             1,846,767      14,818      8   32.6 User I/O  -- профиль нагрузки системы
direct path write temp              366,523       7,819     21   17.2 User I/O  -- --//--
...
                                                    % of Waits                  
                                 -----------------------------------------------
                           Total                                                
Event                      Waits  <1ms  <2ms  <4ms  <8ms <16ms <32ms  <=1s   >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
direct path read temp      1845.  60.8   4.2   2.7   4.4  10.6  10.1   7.1    .0
direct path write temp     366.1  14.0  22.8  19.4   9.6   8.8   9.8  15.5    .0 -- чуть худшая гистограмма против SATA ***
...

IOStat by Filetype summary              DB/Inst: XXXX/xxxx  Snaps: 52729-52730

                Reads:   Reqs   Data    Writes:  Reqs   Data      Small   Large
Filetype Name   Data    per sec per sec Data    per sec per sec    Read    Read
--------------- ------- ------- ------- ------- ------- ------- ------- -------
Temp File          161G   608.1 45.6127  143.3G   215.4 40.6135     7.8     0.5 -- на TEMP приходится почти 42% от общего чтения
Data File        159.2G   153.6 45.1083   14.2G    82.5 4.01884    36.8     1.7 -- и 71% от общего объёма записи
Archive Log       45.4G    12.9 12.8772   13.8G     3.9 3.89902    23.4    12.4
Log File          13.8G     4.1 3.89902   27.3G    13.6 7.73717    19.2     0.0
Control File       5.3G    52.9 1.51588    102M     1.8 .028225     6.5    58.4
TOTAL:           384.7G   831.6 109.013  198.7G   317.3 56.2967    10.7     1.9
          -------------------------------------------------------------

**) Отмеченный AWR при использовании SATA в качестве TEMP и схожим профилем нагрузки:

SQL> select output from table( DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(  1241709836,  1, 52753, 52754));

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                           Avg                  
                                                          wait   % DB           
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
direct path read temp             1,776,141      15,181      9   30.3 User I/O  
DB CPU                                            8,832          17.6           
direct path write temp              343,749       8,286     24   16.5 User I/O  
...
                                                     % of Waits                  
                                  -----------------------------------------------
                            Total                                                
 Event                      Waits  <1ms  <2ms  <4ms  <8ms <16ms <32ms  <=1s   >1s
 -------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
direct path read temp      1775,  56.5   5.7   3.4   4.6  11.3  11.3   7.1    .0
direct path write temp     343,7  14.8  26.1  15.4   7.6   9.1  10.3  16.7    .0
...
IOStat by Filetype summary              DB/Inst: XXXX/xxxx  Snaps: 52753-52754

                Reads:   Reqs   Data    Writes:  Reqs   Data      Small   Large
Filetype Name   Data    per sec per sec Data    per sec per sec    Read    Read
--------------- ------- ------- ------- ------- ------- ------- ------- -------
Temp File        148,1G   569.4 41,9684  124,2G   181.6 35,2132     7.8     0.3 -- на TEMP приходится почти 36% от общего чтения
Data File          166G   218.4 47,0594   30,3G   150.5 8,59016    25.3     1.2 -- и 56% от общего объёма записи
Archive Log       66,7G    19.0 18,9181   22,3G     6.3 6,32947    28.3    11.4
Log File          22,3G     9.5 6,33086   44,2G    22.6 12,5321    11.0     0.0
Control File      10,5G   101.6 2,96294    118M     2.1 ,032663     5.9    65.1
TOTAL:           413,6G   917.9 117,239  221,2G   363.2 62,6976    10.7     2.1
          -------------------------------------------------------------

Если выбрать конкретный запрос с большим кол-вом операций временной записи:

SQL> select snap_id, sql_id, count(*)
  2    from dba_hist_active_sess_history
  3   where snap_id in (52754, 52730)
  4     and event = 'direct path write temp'
  5   group by snap_id, sql_id
  6   order by count(*) desc
  7  /
 
   SNAP_ID SQL_ID          COUNT(*)
---------- ------------- ----------
     52754 40wnpfbaguvyx        426 -- вот
     52730 40wnpfbaguvyx        424 -- этот
     52730 fjyr084uqdpj0        161
     52754 gchrdmcww5ruh        144
     52754 9vtmathtdk8f5        103
     52754 98gkngsqj0aay         85
...

в статистике выполнения можно видеть минимальные отличия между использованием SATA и SSD в качестве TEMP:


SQL> @dba_hist_sqlstat "sql_id = '40wnpfbaguvyx' and snap_id in (52754,52730)"
 
BEGIN_SNAP_TIME EXECS SQL_ID              PLAN       COST ELA_PER_EXEC DIRECT_WRITES_PER_EXEC ROWS_PER_EXEC IOWAITS_PER_EXEC PX_PER_EXEC
--------------- ----- ------------- ---------- ---------- ------------ ---------------------- ------------- ---------------- -----------
20.02 08:00         1 40wnpfbaguvyx  422671096    1237132  15508578699                4357381        621795      14291009117          20 -- SATA
19.02 08:00         1 40wnpfbaguvyx  422671096    1236208  15550779802                4353223        621426      14338744098          20 -- SSD

— DIRECT_WRITES_PER_EXEC (direct_writes_delta/executions_delta) в статистике может включать direct path write, характерные, например, для операций прямой вставки, однако к этом запросе на INSERT прямая вставка не использовалась согласно плану:

SQL> SELECT * FROM TABLE(dbms_xplan.display_awr('40wnpfbaguvyx',422671096));

PLAN_TABLE_OUTPUT
--------------------
SQL_ID 40wnpfbaguvyx
--------------------

Plan hash value: 422671096

--------------------------------------------------------------------------------------
| Id  | Operation                       | Name               | Rows  | Bytes |TempSpc|
--------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                |                    |       |       |       |
|   1 |  LOAD TABLE CONVENTIONAL        |                    |       |       |       | -- non direct-path insert
|   2 |   PX COORDINATOR                |                    |       |       |       |
|   3 |    PX SEND QC (RANDOM)          | :TQ10001           |    57M|   129G|       |
|   4 |     VIEW                        |                    |    57M|   129G|       |
|   5 |      WINDOW SORT                |                    |    57M|   127G|   146G| -- с ожидаемым значительным потреблением TEMP
|   6 |       PX RECEIVE                |                    |    57M|   127G|       |
|   7 |        PX SEND HASH             | :TQ10000           |    57M|   127G|       |
|   8 |         VIEW                    | MV_TOTAL_SALES     |    57M|   127G|       |
|   9 |          UNION-ALL              |                    |       |       |       |
|  10 |           FILTER                |                    |       |       |       |
|  11 |            PX BLOCK ITERATOR    |                    |    32M|    71G|       |
|  12 |             MAT_VIEW ACCESS FULL| MV_PART_SALES_1    |    32M|    71G|       |
|  13 |           FILTER                |                    |       |       |       |
|  14 |            PX BLOCK ITERATOR    |                    |    25M|    55G|       |
|  15 |             MAT_VIEW ACCESS FULL| MV_PART_SALES_2    |    25M|    55G|       |
--------------------------------------------------------------------------------------

***) Выше я отмечал, что гистограмма ожидания direct path write temp для SSD чуть хуже, чем у SATA, что было замечено субъективно, например, при запусках тяжёлых отчётных запросов с DOP 16-32 и объёмными hash join-ами наблюдалось увеличение среднего времени ожидания до 40 мс (против 20 у SATA), что значительно влияло на общее время выполнения запросов

Т.е., один SSD не всегда заменяет без потерь семь обычных SATA дисков, имеет смысл замена в более щадящем соотношении и после тестов, что естественно

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

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

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 такие блоггеры, как: