Сильное падение производительности сервера

Дмитрий

Доброго времени суток!
Есть проблема: сервер стал медленно работать.
Исходные данные:
- MS Windows Server 2003 Standard Edition SP1;
- процессор Intel Xeon 2x3.4 GHz;
- 2 GB RAM;
- Interbase Server WI-V7.5.1.162;
- размер страницы БД - 16384;
- размер файла БД - 25.5 GB (92 таблицы и 83 процедуры);
Суть в том, что те запросы, которые выполнялись за доли секунды, теперь выполняются за 30-40 минут. Не могу понять, в чем проблема? Сделал backup/restore - не помогло. Что делать - то?
P.S. Файл ibconfig не трогал. Он без изменений, с момента установки.

stix-s · Сообщение **stix-s** » 15 ноя 2007, 06:56

Дмитрий писал(а):Доброго времени суток!
Есть проблема: сервер стал медленно работать.
Исходные данные:
- MS Windows Server 2003 Standard Edition SP1;
- процессор Intel Xeon 2x3.4 GHz;
- 2 GB RAM;
- Interbase Server WI-V7.5.1.162;
- размер страницы БД - 16384;
- размер файла БД - 25.5 GB (92 таблицы и 83 процедуры);
Суть в том, что те запросы, которые выполнялись за доли секунды, теперь выполняются за 30-40 минут. Не могу понять, в чем проблема? Сделал backup/restore - не помогло. Что делать - то?
P.S. Файл ibconfig не трогал. Он без изменений, с момента установки.

мусор?
хм

Сделал backup/restore - не помогло

вроде не он
что говорит такой замечательный инструментарий, как IBAnalyst?
планы запросов ручками прописаны?

Дмитрий

IBAnalyst ничего страшного не сообщил. Все, как обычно. Мол есть таблицы, где много версий, несколько плохих индексов и т.п.
Планы и сам писал, и убирал, где явно указаны - без результатов.

Сообщение **kdv** » 15 ноя 2007, 10:27

планы надо смотреть. такое сильное замедление может быть разве что из-за сильного изменения плана, или из-за какой-нибудь фигни с диском или контроллером. файлы на диске быстро копируются?
медленно выполняется только часть запросов?

WildSery · Сообщение **WildSery** » 15 ноя 2007, 10:30

Индексы активны?

Дмитрий

Файлы копируются быстро. Backup делается около часа, restore - около семи часов. Индексы активны. Началось все это после того, как размер БД перевалил за 25 ГБ.
И что интересно, памяти на сервере больше 300 МБ не используется.

Сообщение **kdv** » 15 ноя 2007, 11:43

явно тормоза с размером файла связаны быть не могут. я имею в виду, вот вчера было 20, а сегодня 25 гиг, и вдруг "запросы которые выполнялись доли секунды выполняются по 30-40 минут".
Хотя, натурально надо планы смотреть.

Дмитрий

Имеем таблицу:

Код: Выделить всё

CREATE TABLE DOC_HISTORY (
    CUSTOMER_ID    INTEGER NOT NULL,
    DOC_ID         INTEGER NOT NULL,
    DOC_TYP        INTEGER NOT NULL,
    DOC_DATE       DATE NOT NULL,
    REAL_DOC_DATE  DATE NOT NULL,
    DOC_NOTE       VARCHAR(255),
    ACTUALITY      SMALLINT DEFAULT 1 NOT NULL,
    TABLE_NAME     VARCHAR(32) NOT NULL,
    CHANGER_NAME   VARCHAR(50) NOT NULL
);


CREATE UNIQUE INDEX DOC_HIS1 ON DOC_HISTORY (DOC_ID, CUSTOMER_ID);
CREATE INDEX DOC_HIS2 ON DOC_HISTORY (CUSTOMER_ID);
CREATE INDEX DOC_HIS3 ON DOC_HISTORY (ACTUALITY, TABLE_NAME);
CREATE INDEX DOC_HIS4 ON DOC_HISTORY (CUSTOMER_ID, ACTUALITY);
CREATE INDEX DOC_HIS5 ON DOC_HISTORY (CUSTOMER_ID, DOC_ID, ACTUALITY);

Статистика:

DOC_HISTORY (168)
Primary pointer page: 246, Index root page: 247
Average record length: 109.92, total records: 1098443
Average version length: 120.64, total versions: 7263, max versions: 1
Data pages: 10173, data page slots: 10173, average fill: 84%
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 27
80 - 99% = 10145

Index DOC_HIS1 (0)
Depth: 2, leaf buckets: 548, nodes: 1098443
Average data length: 1.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 146
60 - 79% = 4
80 - 99% = 398

Index DOC_HIS2 (1)
Depth: 2, leaf buckets: 502, nodes: 1098443
Average data length: 0.00, total dup: 586753, max dup: 69
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 135
60 - 79% = 0
80 - 99% = 367

Index DOC_HIS3 (2)
Depth: 2, leaf buckets: 432, nodes: 1098770
Average data length: 0.00, total dup: 1098762, max dup: 395931
Fill distribution:
0 - 19% = 9
20 - 39% = 6
40 - 59% = 23
60 - 79% = 10
80 - 99% = 384

Index DOC_HIS4 (3)
Depth: 2, leaf buckets: 621, nodes: 1098770
Average data length: 2.00, total dup: 278638, max dup: 68
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 153
60 - 79% = 1
80 - 99% = 467

Выполняем следующую процедуру для поиска дубликатов:

Код: Выделить всё

CREATE PROCEDURE CLEAR_DOC_HISTORY 
RETURNS (
    CUSTOMER_ID_DEL INTEGER,
    DOC_ID_DEL INTEGER,
    SCH_DEP VARCHAR(20))
AS
DECLARE VARIABLE CUSTOMER_ID INTEGER;
DECLARE VARIABLE DOC_ID INTEGER;
DECLARE VARIABLE DOC_TYP INTEGER;
DECLARE VARIABLE DOC_DATE DATE;
DECLARE VARIABLE REAL_DOC_DATE DATE;
DECLARE VARIABLE DOC_NOTE VARCHAR(255);
DECLARE VARIABLE TABLE_NAME VARCHAR(32);
DECLARE VARIABLE CHANGER_NAME VARCHAR(50);
BEGIN
 FOR SELECT CUSTOMER_ID, DOC_ID, DOC_TYP, DOC_DATE, REAL_DOC_DATE, DOC_NOTE, TABLE_NAME, CHANGER_NAME
     FROM DOC_HISTORY
     WHERE ACTUALITY = 0
     PLAN (DOC_HISTORY INDEX (DOC_HIS3))
     INTO :CUSTOMER_ID, :DOC_ID, :DOC_TYP, :DOC_DATE, :REAL_DOC_DATE, :DOC_NOTE, :TABLE_NAME, :CHANGER_NAME
 DO
  BEGIN
   CUSTOMER_ID_DEL = NULL;
   FOR SELECT CUSTOMER_ID, DOC_ID
       FROM DOC_HISTORY
       WHERE (CUSTOMER_ID = :CUSTOMER_ID) AND (DOC_TYP = :DOC_TYP) AND (DOC_DATE = :DOC_DATE) AND
             (REAL_DOC_DATE = :REAL_DOC_DATE) AND (DOC_NOTE = :DOC_NOTE) AND (ACTUALITY = 0) AND
             (TABLE_NAME = :TABLE_NAME) AND (CHANGER_NAME = :CHANGER_NAME) AND (DOC_ID > :DOC_ID)
       PLAN (DOC_HISTORY INDEX (DOC_HIS1))
       INTO :CUSTOMER_ID_DEL, :DOC_ID_DEL
   DO
    BEGIN
     IF (NOT (CUSTOMER_ID_DEL IS NULL)) THEN
      SUSPEND;
    END
  END
END

Так эта процедура выполнется уже 17 часов 25 минут!

WildSery · Сообщение **WildSery** » 15 ноя 2007, 11:53

Дмитрий писал(а):Backup делается около часа, restore - около семи часов.

Чего так долго-то? Или для InterBase это нормально?
У меня бэкап-рестор на Firebird такой базы раз в 5 быстрее. А может и больше, с ходу не помню.

Merlin · Сообщение **Merlin** » 15 ноя 2007, 12:56

1. По первому запросу в процедуре. Результат

Select Actuality, Count(*) From Doc_History

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

2. По второму запросу в процедуре. Индекс DOC_HIS1 крайне для него нееффективен, испольуется только первый сегмент, поле Customer_ID - нет. Тут не помешал бы композит на все условия поиска или на их группу, заметно усекающую выборку, а сегмент, на который накладывается условие не на равенство либо вообще не нужен, либо долженн быть последним, ибо сегменты, идущие после него не используются при поиске.

Дмитрий

Согласен. DOC_HIS3 - не эффективен. Но если его нет, то будет NATURAL. Статистика по нему плохая - 0.125.
Можно, конечно, создать индекс по группе сравнения, но! Раньше эта процедура выполнялясь за несколько минут. А сейчас уже 19-ый час пошел.

WildSery · Сообщение **WildSery** » 15 ноя 2007, 13:23

Код: Выделить всё

CREATE PROCEDURE CLEAR_DOC_HISTORY 
RETURNS ( 
    CUSTOMER_ID_DEL INTEGER, 
    DOC_ID_DEL INTEGER, 
    SCH_DEP VARCHAR(20)) 
as
declare variable doc_typ integer;
declare variable doc_date date; 
declare variable real_doc_date date; 
declare variable doc_note varchar(255); 
declare variable table_name varchar(32); 
declare variable changer_name varchar(50); 

declare variable p_customer_id integer;
declare variable p_doc_typ integer;
declare variable p_doc_date date; 
declare variable p_real_doc_date date; 
declare variable p_doc_note varchar(255); 
declare variable p_table_name varchar(32); 
declare variable p_changer_name varchar(50); 
begin 
  customer_id = null; doc_typ = null; doc_date = null; real_doc_date = null;
  doc_note = null; table_name = null; changer_name = null; doc_id = null;

  for select customer_id, doc_id, doc_typ, doc_date, real_doc_date, doc_note, table_name, changer_name
    from doc_history
    where actuality+0 = 0
    order by customer_id+0, doc_typ, doc_date, real_doc_date, doc_note, table_name, changer_name, doc_id
    into customer_id_del, doc_id_del, doc_typ, doc_date, real_doc_date, doc_note, table_name, changer_name
  do
    if (customer_id_del = p_customer_id and doc_typ = p_doc_typ and doc_date = p_doc_date and
        real_doc_date = p_real_doc_date and doc_note = p_doc_note and
        table_name = p_table_name and changer_name = p_changer_name) then
      suspend;
    else begin
      p_customer_id = customer_id_del; p_doc_typ = doc_typ; p_doc_date = doc_date;
      p_real_doc_date = real_doc_date; p_doc_note = doc_note;
      p_table_name = table_name; p_changer_name = changer_name;
    end
end

Обращаю внимание на недопустимость полей NULL в логе. Я специально не анализирую нулы, потому что в твоей процедуре они также будут проигнорированы.
Если нулы всё же присутствуют - добавить COALESCE.

Сообщение **kdv** » 15 ноя 2007, 13:36

CREATE UNIQUE INDEX DOC_HIS1 ON DOC_HISTORY (DOC_ID, CUSTOMER_ID);

опять... почему не Primary Key?

Так эта процедура выполнется уже 17 часов 25 минут!

дык. по индексу, без индекса...
1098443 раз произвести поиск.... причем наверняка могут лишние индексы цепляться.

Дмитрий

kdv писал(а):
CREATE UNIQUE INDEX DOC_HIS1 ON DOC_HISTORY (DOC_ID, CUSTOMER_ID);
опять... почему не Primary Key?

Так эта процедура выполнется уже 17 часов 25 минут!
дык. по индексу, без индекса...
1098443 раз произвести поиск.... причем наверняка могут лишние индексы цепляться.

Уже переделываю на Primary Key. Все равно надо связи установить.
Лишние индексы цепляются, если оптимизатору поверить. Я же явно план указываю.
Выполнять процедуру прекратил. Не выполнилась за 19 часов.

NMX · Сообщение **NMX** » 15 ноя 2007, 14:16

Дмитрий писал(а):Файлы копируются быстро. Backup делается около часа, restore - около семи часов. Индексы активны. Началось все это после того, как размер БД перевалил за 25 ГБ.
И что интересно, памяти на сервере больше 300 МБ не используется.

У нас был случай когда WIN 2000 Server отобрал память у сервера Firebird в пользу системного кеша, куда пытался "затолкать" разрошийся файл базы данных. Выличилось насторйкой в системном реестре, которая отвечает за распеределение физической памяти между файловым кешем операционной системы и приложениями. Как называется ключ реестра, к сожалению забыл...

Сообщение **kdv** » 15 ноя 2007, 14:29

unique на пк - это концептуально, к производительности отношения не имеет.

Дмитрий

kdv писал(а):unique на пк - это концептуально, к производительности отношения не имеет.

Это я знаю. Просто начинаю кое-какие связи устанавливать, а с ПК красивше. Но это все лирика.
А чё с производительностью делать?

WildSery · Сообщение **WildSery** » 15 ноя 2007, 15:38

WildSery писал(а):
Код: Выделить всё
    where actuality+0 = 0

Да, "+0" отсюда следует убрать, если значиний равных нулю относительно мало.

hvlad · Сообщение **hvlad** » 15 ноя 2007, 15:46

Дмитрий писал(а):Лишние индексы цепляются, если оптимизатору поверить. Я же явно план указываю.

А он (IB) на него ложит (или кладёт

)

Merlin · Сообщение **Merlin** » 15 ноя 2007, 16:42

hvlad писал(а):
Дмитрий писал(а):Лишние индексы цепляются, если оптимизатору поверить. Я же явно план указываю.
А он (IB) на него ложит (или кладёт )

Это не трудно и посмотреть. А насчёт того, что раньше работало - поди база прыжком увеличилась с 2Гб до 25, поигрались и залили дофига данных. Или в начале ордера по ID появились много таких, которых раньше не было. Не может такое быстро работать на больших объёмах и равномерном распределении. Насчёт первого запроса - в общем-то не так много проигрываем натуралу даже если в таблице записей с Actuality=0 процентов 80%, фих с ним. А вот после каженной записи, отсекая по индексу только меньшие по ID, выполнять фактически натуральный перебор до упора, даже хуже, ибо индекс всё равно сканируется - это фигня-с. Начиная с мильённой записи оно, конешно, терпимо, но до ней таки надо добраться.

iBase.ru Forum

Сильное падение производительности сервера

Сильное падение производительности сервера

Re: Сильное падение производительности сервера