Как использовать методы наименьших квадратов для оценки ресурсов и мониторинга баз Oracle

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



    Возьмем Oracle Cloud Control. В схеме SYSMAN в представлении MGMT_METRICS_1DAY у нас хранится значение метрики «Filesystem Space Available (MB)» для некоего хоста с TARGET_GUID = '6B1E3AFA92B3EA29AD73BB87432C084C'.
     
    SELECT TO_CHAR (CAST (ROLLUP_TIMESTAMP AS DATE), 'YYYY-MM-DD"T"HH24:MI:SS'),
             ROUND (VALUE_AVERAGE, 2) AVG_FREE_SPACE_INM
        FROM SYSMAN.MGMT_METRICS_1DAY
       WHERE 	
    TARGET_GUID = '6B1E3AFA92B3EA29AD73BB87432C084C'
             AND METRIC_GUID = HEXTORAW ('E8838C71E687BF0A9E02FFACC0C9AC80')
             AND KEY_VALUE = '/u10'
    ORDER BY ROLLUP_TIMESTAMP;
    

    График ниже показывает, что за три месяца, с 14 августа 2018 года по 15 ноября 2018 года, свободное место в файловой системе /u01 уменьшилось  на 1.5TB — с 4,1 ТБ до 2,6 ТБ



    Для определения времени, на которое нам хватит оставшихся 2,6 ТБ, обратимся к функциям линейной регрессии.

    В Oracle для вычисления наклона линии регрессии используется функция REGR_SLOPE(x,y). Функция наклона линии регрессии REGR_SLOPE определяется отношением ковариации множеств x и y к дисперсии множества y:

    REGR_SLOPE(x,y) = COVAR_POP(x,y) / VAR_POP(y)

    Для вычисления перехвата оси Y используется функция REGR_INTERCEPT(x,y). Функция перехвата оси Y REGR_INTERCEPT определяется разницей среднего значения множества x и произведения наклона линии регрессии и среднего значения множества y:

    REGR_INTERCEPT(x,y) = AVG(x) – REGR_SLOPE(x, y) * AVG(y)

    Для вычисления R-квадрата или коэффициента детерминации используется функция REGR_R2(x,y), которая не определена при нулевой дисперсии y, равна единице при нулевой дисперсии x и ненулевой дисперсии y. А в случае положительной дисперсии x и ненулевой дисперсии y равна квадрату корреляции x и y:

    NULL if VAR_POP(y)  = 0
     
    1 if VAR_POP(x)  = 0 and
    VAR_POP(y) != 0
     
    POWER(CORR(expr1,expr),2) if VAR_POP(x)  > 0 and
    VAR_POP(y)  != 0

    Мы бы описали коэффициент детерминации как величину, характеризующую процент описанных вариаций моделью. Если коэффициент детерминации равен 1, то наша модель описывает 100% вариаций и наш прогноз будет максимально точным.

    Предлагаем в качестве x взять разницу времени, в которое получена метрика и текущим временем —  ROLLUP_TIMESTAMP-SYSDATE. В качестве y возьмем среднее значение свободного оставшегося места на диске /u01 в МБ — «Filesystem Space Available (MB)». В таком случае, используя функцию REGR_INTERCEPT(ROLLUP_TIMESTAMP-SYSDATE, VALUE_AVERAGE) в качестве групповой, мы можем оценить, через сколько суток закончится место на диске /u01.

    В развернутом виде значение функции наклона линии регрессии, перехвата оси и коэффициента детерминации получаем запросом:

    SELECT   COVAR_POP (ROLLUP_TIMESTAMP - SYSDATE, NULLIF (ROUND (VALUE_AVERAGE, 2), 0)) / VAR_POP (NULLIF (ROUND (VALUE_AVERAGE, 2), 0)) REGR_SLOPE,
             AVG (ROLLUP_TIMESTAMP)- (COVAR_POP (SYSDATE - ROLLUP_TIMESTAMP, NULLIF (ROUND (VALUE_AVERAGE, 2), 0))/VAR_POP(NULLIF(ROUND(VALUE_AVERAGE, 2), 0)))*AVG (NULLIF (ROUND(VALUE_AVERAGE, 2), 0))
                REGR_INTERCEPT,
             AVG (SYSDATE - ROLLUP_TIMESTAMP)-(COVAR_POP(SYSDATE - ROLLUP_TIMESTAMP,NULLIF(ROUND(VALUE_AVERAGE,2),0))/VAR_POP(NULLIF(ROUND(VALUE_AVERAGE,2),0)))*AVG(NULLIF(ROUND(VALUE_AVERAGE,2),0)))      REGR_INTERCEPT_ABS,
             CASE
                WHEN VAR_POP (NULLIF (ROUND (VALUE_AVERAGE, 2), 0)) = 0
                THEN
                   NULL
                WHEN     VAR_POP (SYSDATE - ROLLUP_TIMESTAMP) = 0
                     AND VAR_POP (NULLIF (ROUND (VALUE_AVERAGE, 2), 0)) != 0
                THEN
                   1
                WHEN     VAR_POP (SYSDATE - ROLLUP_TIMESTAMP) > 0
                     AND VAR_POP (NULLIF (ROUND (VALUE_AVERAGE, 2), 0)) != 0
                THEN
                   POWER (CORR (SYSDATE - ROLLUP_TIMESTAMP, NULLIF (ROUND (VALUE_AVERAGE, 2), 0)),2)
             END
                REGR_R2
        FROM MGMT_METRICS_1DAY
       WHERE 	
    TARGET_GUID = '6B1E3AFA92B3EA29AD73BB87432C084C'
             AND METRIC_GUID = HEXTORAW ('E8838C71E687BF0A9E02FFACC0C9AC80')
             AND KEY_VALUE = '/u10'
    ORDER BY ROLLUP_TIMESTAMP;

    Или уже с использованием функций REGR_SLOPE, REGR_INTERCEPT и REGR_R2:

    SELECT REGR_SLOPE(SYSDATE-ROLLUP_TIMESTAMP, NULLIF(ROUND(VALUE_AVERAGE,2),0)) RSLP,
           REGR_INTERCEPT(SYSDATE-ROLLUP_TIMESTAMP, NULLIF(ROUND(VALUE_AVERAGE,2),0)) RINSP,  
           REGR_R2(SYSDATE-ROLLUP_TIMESTAMP, NULLIF(ROUND(VALUE_AVERAGE,2),0)) RSQR
           FROM MGMT_METRICS_1DAY
           WHERE TARGET_GUID='6B1E3AFA92B3EA29AD73BB87432C084C'
           AND METRIC_GUID=HEXTORAW('E8838C71E687BF0A9E02FFACC0C9AC80')
     	
      AND KEY_VALUE='/u10'
           ORDER BY ROLLUP_TIMESTAMP;

    REGR_SLOPE = -0.00005
    REGR_INTERCEPT = 149.46
    REGR_R2 = 0.97

    В нашем случае коэффициент детерминации близок к 1, и мы видим, что примерно через 149 cуток место на файловой системе /u10 закончится.

    Мы можем использовать данный метод для оценки времени, на которое нам хватит имеющегося места на дисках для хостов продуктивных баз. В нашем случае хосты продуктивных баз включены в группу с METRIC_GUID='E8838C71E687BF0A9E02FFACC0C9AC80'.

    Создаем User Definded Type T_TYPE c полями, которые нам нужны для выводимых данных: имя хоста, имя файловой системы, наклон линии регрессии, число дней, через которое закончится место на файловой системе и коэффициент детерминации.

    CREATE OR REPLACE TYPE T_TYPE AS OBJECT(TARGET_NAME VARCHAR2(256), KEY_VALUE VARCHAR2(256), RSLP NUMBER,RINSP NUMBER,RSQR NUMBER);

    Создаем Nested Table Type R_TYPE на базе T_TYPE:

    CREATE OR REPLACE TYPE R_TYPE AS TABLE OF T_TYPE;

    Создаем пакет, включающий функцию GET_VALUES для извлечения данных из MGMT_METRICS_1DAY  и процедуру отправки полученных данных по почте SEND_VALUES.

    CREATE OR REPLACE PACKAGE EST_FS_EXHAUST IS

    Для функции GET_VALUES входными параметрами будут переменная V_GN с именем группы хостов и значением коэффициента детерминации V_RSQ.

    FUNCTION GET_VALUES(V_GN VARCHAR2, V_RSQ NUMBER) RETURN R_TYPE;

    Для процедуры SEND_VALUES  входными параметрами будут переменная V_GN  и V_RSQ, аналогичные переменным функции GET_VALUES, плюс email, на который мы планируем отправить наш мини-отчет.

    PROCEDURE SEND_VALUES(V_GN VARCHAR2, V_RSQ NUMBER, V_MAIL VARCHAR2);
    END EST_FS_EXHAUST;
    

    В теле пакета определяем функцию GET_VALUES и процедуру SEND_VALUES
         
    CREATE OR REPLACE PACKAGE BODY EST_FS_EXHAUST IS
      
    FUNCTION GET_VALUES(V_GN VARCHAR2, V_RSQ NUMBER) RETURN R_TYPE AS
       	V_REC R_TYPE;
    BEGIN
    SELECT
    T_TYPE(
    M.TARGET_NAME,
    	D.KEY_VALUE,
    ROUND(REGR_SLOPE(D.ROLLUP_TIMESTAMP-SYSDATE, NULLIF(ROUND(D.VALUE_AVERAGE,2),0)),0),
    ROUND((ABS(REGR_INTERCEPT(D.ROLLUP_TIMESTAMP-SYSDATE, NULLIF(ROUND(D.VALUE_AVERAGE,2),0)))),0),  
    ROUND(REGR_R2(D.ROLLUP_TIMESTAMP-SYSDATE, NULLIF(ROUND(D.VALUE_AVERAGE,2),0)),0))
    BULK COLLECT INTO V_REC
    FROM MGMT_METRICS_1DAY D, MGMT_TARGETS M, MGMT_TARGET_MEMBERSHIPS G
    WHERE M.TARGET_GUID=G.MEMBER_TARGET_GUID
    AND M.TARGET_GUID=D.TARGET_GUID
    AND G.COMPOSITE_TARGET_NAME=V_GN
    AND METRIC_GUID=HEXTORAW('E8838C71E687BF0A9E02FFACC0C9AC80')
    GROUP BY M.TARGET_NAME, D.KEY_VALUE
    HAVING REGR_R2(D.ROLLUP_TIMESTAMP-SYSDATE, NULLIF(ROUND(D.VALUE_AVERAGE,2),0)) > V_RSQ
           ;
    RETURN V_REC;
    END GET_VALUES;
     PROCEDURE SEND_VALUES(V_GN VARCHAR2, V_RSQ NUMBER, V_MAIL VARCHAR2) IS
    V_REC R_TYPE;
    	MSG VARCHAR2(2048):='';
    BEGIN
    	V_REC:= GET_VALUES(V_GN,V_RSQ);
    FOR I IN V_REC.FIRST..V_REC.LAST
    LOOP
    	MSG:=CHR(10)||MSG||' Host '||V_REC(I).TARGET_NAME||' filesystem '||V_REC(I).KEY_VALUE||' will be exhausted in '||V_REC(I).RINSP||' days'|| CHR(9)||CHR(10);
    END LOOP;
    EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''mail_server''';
    UTL_MAIL.SEND(SENDER => 'monitoring@yourmail.com',
            	RECIPIENTS => V_MAIL,
               	SUBJECT => 'Test Mail',
               	MESSAGE => MSG,
             	MIME_TYPE => 'text; charset=us-ascii');
    END;
     
    END EST_FS_EXHAUST;
    /
    

    Допустим, мы хотим получить время, через которое закончится место в файловых системах группы продуктивных хостов ‘prod_hosts’ при коэффициенте детерминации больше 0,5:

    begin
    EST_FS_EXHAUST.SEND_VALUES('prod_hosts',0.5,'operator@yourdomain.com');
    end;
    /
    PL/SQL procedure successfully completed.
    

    Как итог, в почту приходит сообщение:

    Host host1 filesystem /u51 will be exhausted in 342 days          
    Host host2 filesystem /u40 will be exhausted in 236 days          
    Host host3 filesystem /u20/redo01 will be exhausted in 1100310 days             
    Host host4 filesystem /u10 will be exhausted in 150 days          
    Host host4 filesystem /u01/integration will be exhausted in 75080 days     
    Host host4 filesystem /u01/app will be exhausted in 135 days   
    Host host5  filesystem /u30/redo01 will be exhausted in 62252596 days          
    Host host6 filesystem /u01 will be exhausted in 260 days          
    Host host7  filesystem /u99 will be exhausted in 1038 days

    Обратите внимание на файловые системы /u20/redo01 и /u30/redo01 — здесь расположены REDO LOGS и место не расходуется. Наша модель спрогнозировала, что место в /u20/redo01 на хосте host3 закончится через 2990 лет, а в /u30/redo01 на хосте host5 — через 169164 года. В обоих случаях наклон линии регрессии меньше –1.   

    Использование этого метода можно расширить — например, оценивать время до исчерпания места в FAST RECOVERY AREA, в табличных пространствах базы и других областях.
    • +12
    • 2,1k
    • 3
    ВТБ
    90,85
    Компания
    Поделиться публикацией

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

      0
      Так и подмывает спросить — какую же чудесную систему мониторинга Вы используете, что приходиться писать такие милые процедуры?
        +1
        Понимаете, мониторинг и предсказание – разные вещи, требующие разных подходов и алгоритмов. Если у вас вся СХД однородна и добавить пространство хосту можно одним кликом, то мониторинга достаточно. Если же добавление СХД требует длительных процедур закупки и установки оборудования, то мониторингом уже не обойдёшься, нужно предсказывать проблемы сильно заранее. SAS, например, предлагает готовое решение, которое, базируясь на данных мониторинга, предсказывает сбои. И самое интересное – его покупают…
        +2
        Статья выглядит как шпаргалка сисадмина, которая нечаянно утекла в публичный доступ. Даже GUID-ы наверное не поменяли на фэйковые.
        Ну, хоть зато в REDO-логах у вас вроде бы место закончится еще не скоро )

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

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