EnТест вставки записей
Кузьменко Дмитрий, 22 октября 1998. Исправлено 26 сентября 2000.
За последние три недели пронесся буквально шквал телефонных вопросов по абсолютно одной и той же теме: почему IB медленно работает при вставке записей свыше 200 тысяч, и почему индекс по такой таблице создается 2 часа, и т. д.
Причем самое интересное, что ответить "почему медленно" я не могу. Просто не знаю. А для того, чтобы убрать все сомнения насчет "медленности" IB, мне пришлось потратить полдня на эмуляцию предложенных ситуаций.
Итак,
Исходные данные
(по телефонным звонкам):- БД размером 35-80 мегабайт.
- Кол-во записей в основной таблице – 200-300 тысяч.
- Операционная система, где установлен IB – ... Windows 95.
Наверняка кто-то из читающих эти строки уже начал улыбаться: "Ну какая же работа может быть под 95-ыми! Разве что клиентские приложения можно запускать..."
Абсолютно согласен. У меня даже и мысли не возникало заняться решением проблемы именно под Win95. Хотя бы потому, что в нашей компании на всех компьютерах без исключения уже давно работает Windows NT 4.0 (где server, а где – workstation), даже на компьютерах в отделе продаж.
Так что весь последующий тест происходил под Windows NT. Его описание наверняка будет интересно даже тем, кто не испытывает вышеупомянутых проблем, поскольку можно будет сравнить собственное аппаратное обеспечение с тем, на котором был сделан тест.
Аппаратное обеспечение
- Pentium Pro 150MHz, 64MB RAM, HDD1 – Quantum Fireball ST6 (6.4Gb), HDD2 – IBM DALA 3540 (540Mb).
- Оба винчестера – IDE, находятся на одном шлейфе. Quantum работает в PIO Mode 4, IBM – в PIO Mode 3. На винчестере IBM располагается файл виртуальной памяти NT. На Quantum – операционная система, базы данных и т. п. Файловая система – только FAT.
Программное обеспечение
- Windows NT 4.0, ServicePack 3, все основные HotFixes связанные с аппаратным обеспечением или протоколами (tcp/ip и т. п.)
- IB Database 5.1.1 for Windows 95/NT. Все тесты производились без Guardian в режиме Application.
Вставка записей и просмотр данных производились на той же машине, где и находился сервер IB.
Конфигурация IB Database
(содержимое файла IBCONFIG):SERVER_WORKING_SIZE_MAX 33000
SERVER_WORKING_SIZE_MIN 10000
DATABASE_CACHE_PAGES 3750
SERVER_CLIENT_MAPPING 8192
LOCK_HASH_SLOTS 513
SERVER_PRIORITY_CLASS 1
TMP_DIRECTORY 40000000 "C:\TEMP"
TMP_DIRECTORY 100000000 "E:\"
TMP_DIRECTORY 100000000 "F:\"
SERVER_WORKING_SIZE_MIN 10000
DATABASE_CACHE_PAGES 3750
SERVER_CLIENT_MAPPING 8192
LOCK_HASH_SLOTS 513
SERVER_PRIORITY_CLASS 1
TMP_DIRECTORY 40000000 "C:\TEMP"
TMP_DIRECTORY 100000000 "E:\"
TMP_DIRECTORY 100000000 "F:\"
Параметры с символом # в начале удалены как умолчательные. Описание параметров и их значений приведено в конце текста.
База данных
Поскольку исходная проблема состояла в скорости вставки и индексирования одной таблицы, была создана база данных именно с одной таблицей.CREATE DATABASE "E:\TESTV.GDB" PAGE_SIZE 8192 DEFAULT CHARACTER SET WIN1251;
/* Table: CLIENTS, Owner: SYSDBA */
CREATE TABLE CLIENTS (ID INTEGER NOT NULL,
LNAME CHAR(30),
FNAME CHAR(30),
MNAME CHAR(30),
EMAIL CHAR(20),
ADDRESS CHAR(80),
PASSPORT CHAR(20),
BOXID INTEGER,
SALARY NUMERIC(15, 2),
CONSTRAINT PK_CLIENTS PRIMARY KEY (ID));
CREATE GENERATOR GENCLIENT;
SET TERM ^ ;
/* Triggers only will work for SQL triggers */
CREATE TRIGGER NEWCLIENT FOR CLIENTS
ACTIVE BEFORE INSERT POSITION 0
as
begin
new.ID=GEN_ID(genclient, 1);
end
^
COMMIT WORK ^
SET TERM ; ^
/* Table: CLIENTS, Owner: SYSDBA */
CREATE TABLE CLIENTS (ID INTEGER NOT NULL,
LNAME CHAR(30),
FNAME CHAR(30),
MNAME CHAR(30),
EMAIL CHAR(20),
ADDRESS CHAR(80),
PASSPORT CHAR(20),
BOXID INTEGER,
SALARY NUMERIC(15, 2),
CONSTRAINT PK_CLIENTS PRIMARY KEY (ID));
CREATE GENERATOR GENCLIENT;
SET TERM ^ ;
/* Triggers only will work for SQL triggers */
CREATE TRIGGER NEWCLIENT FOR CLIENTS
ACTIVE BEFORE INSERT POSITION 0
as
begin
new.ID=GEN_ID(genclient, 1);
end
^
COMMIT WORK ^
SET TERM ; ^
Максимальный размер данных в записи таблицы CLIENTS составляет 4+30+30+30+20+80+20+4+8 = 226 байт.
Максимальный физический размер записи составляет 4+32+32+32+22+82+22+4+8 = 238 байт.
Минимальный физический размер записи составляет 16+4+4+4+4+4+4+4+8 = 52 байта.
Тестовые данные
Проще всего для генерации данных было использовать какой-нибудь готовый инструмент. К примеру, PowerDesigner DataArchitect 6.1 фирмы Sybase. Он позволяет генерировать тестовые данные либо в скрипт, либо вставлять их в БД через ODBC-драйвер. Изначально таблица CLIENTS содержала поле типа DATE (BIRTHDAY), но оказалось что DataArchitect в скрипте использует формат даты YYYY-mm-dd, который не распознается IB. (Удивительные люди писали DataArchitect – откуда они взяли такой формат, и как его поменять???) Поэтому поле типа DATE пришлось удалить.Размер строковых значений для символьных полей был выбран случайным от 5 до максимальной длины соответствующего поля.
Триггер NEWCLIENT был введен специально для того, чтобы один и тот же скрипт с данными можно было выполнять несколько раз для достижения необходимого количества записей.
Весь скрипт состоял из 10 тысяч строк вроде
insert into CLIENTS
(ID, LNAME, FNAME, MNAME, EMAIL, ADDRESS, PASSPORT, BOXID, SALARY)
values (1, 'JiUbL3vQu4QRhv4JZvuOgZ1uyiGuK', 'xoEEnRG01', 'rrjX2O5DNIj5xI',
'2y3zU', 'CPuVjOon9ciy5xW', 'SpUc8I1r', 1382428, 430673.94);
и занимал на диске 2.6 мегабайт. (Скрипт большего размера создать в DataArchitect было проблематично, поскольку даже скрипт из 10 тысяч записей генерировался в течение 20-ти минут).(ID, LNAME, FNAME, MNAME, EMAIL, ADDRESS, PASSPORT, BOXID, SALARY)
values (1, 'JiUbL3vQu4QRhv4JZvuOgZ1uyiGuK', 'xoEEnRG01', 'rrjX2O5DNIj5xI',
'2y3zU', 'CPuVjOon9ciy5xW', 'SpUc8I1r', 1382428, 430673.94);
В начале скрипта была добавлена строка
CONNECT "E:\TESTV.GDB" USER ...
т. е. соединение с IB производилось через локальный интерфейс (tcp/ip см. дальше). Результаты теста
Результаты будут описаны в виде последовательности операций, которые производились над базой данных:- Был взят WISQL32.EXE, и в нем выполнялась операция RUN ISQL Script. На выполнение скрипта (вставка 10 тысяч записей) уходило около минуты, т. е. 60-70 секунд.
- Периодически для того, чтобы убедиться в успешном добавлении записей, в WISQL выполнялся запрос SELECT COUNT(*) FROM CLIENTS. В один из таких моментов, когда кол-во записей было около 170 тысяч, зависла NT. В чем была причина зависания – неясно. После перезагрузки БД оказалась в совершенно неповрежденном состоянии, и вставка записей была продолжена.
- Однако управляться с WISQL в плане частого выполнения одних и тех же операций оказалось тяжеловато, и для этого с расширением .SQL было ассоциировано два действия – Open (при помощи Notepad) и Run (isql.exe "%1").
- После "перехода" с WISQL на isql оказалось что тот же скрипт выполняется в два раза быстрее! Т. е. 10 тысяч записей вставляются не за минуту, а за 35 секунд!!! (размышления на эту тему см. в конце текста)
- Как только в таблице CLIENTS набралось около 270 тысяч записей, было создано два индекса для того, чтобы оценить скорость добавления записей при наличии большего числа индексов (чем 1).
CREATE INDEX BYNAME ON CLIENTS(LNAME, FNAME);
CREATE INDEX BYBOX ON CLIENTS(BOXID);
CREATE INDEX BYBOX ON CLIENTS(BOXID);
Создание этих индексов заняло не более 10 минут (всего). Более подробные данные по времени создания индексов см. в конце описания хода теста.
- С двумя дополнительными индексами (кроме индекса первичного ключа) добавление пошло помедленнее где-то в полтора раза. Поэтому после того, как было добавлено еще 80 тысяч записей, индексы были выключены командами
ALTER INDEX BYNAME INACTIVE и
ALTER INDEX BYBOX INACTIVE
ALTER INDEX BYBOX INACTIVE
Примечание. Подобная деактивация может выполняться достаточно долго. В этом случае более выгодно сделать drop index. См. статью по проблемам удаления большого количества данных. В данном случае деактивация индексов занимала всего 5-6 секунд (т. к. дубликатов значений в индексах было очень мало).
- Вплоть до 1 миллиона 200 тысяч записей разницы в скорости добавления пачек из 10 тысяч записей каждая обнаружено практически не было. В результате среднее время добавления 10 тысяч записей, независимо от количества уже существующих записей в таблице, составило около 37 секунд.
Теперь
Немного дополнительной статистики
для оценки скорости работы IB:- Окончательно таблица CLIENTS содержала 1279961 записей (миллион двести семьдесят девять тысяч девятьсот шестьдесят одна)
- Размер базы данных – 291мегабайт.
Например, статистика по запросу:
SELECT COUNT(*) FROM CLIENTS
PLAN (CLIENTS NATURAL)
COUNT
===========
1279981
Current memory = 31933436
Delta memory = 0
Max memory = 32065620
Elapsed time= 59.50 sec
Buffers = 3750
Reads = 31325
Writes 0
Fetches = 2622595
PLAN (CLIENTS NATURAL)
COUNT
===========
1279981
Current memory = 31933436
Delta memory = 0
Max memory = 32065620
Elapsed time= 59.50 sec
Buffers = 3750
Reads = 31325
Writes 0
Fetches = 2622595
Причем при количестве записей около 750 тысяч время выполнения такого запроса составляет около 45 секунд.
По окончании заполнения таблицы индексы BYNAME и BYBOX были созданы опять командами
ALTER INDEX BYNAME ACTIVE и
ALTER INDEX BYBOX ACTIVE
Команда активизации индекса работает (с точки зрения IB) абсолютно так же, как и CREATE INDEX, за исключением того, что информация о таблице и индексируемых полях берется из системных таблиц. Т. е. индекс создается "с нуля".
Создание индекса состоит из двух фаз: 1 – создание сортированного массива данных, 2 – перенос данных в страницы индекса.
Первая фаза требует достаточного свободного пространства, которое определяется системной переменной TEMP или параметром TMP_DIRECTORY в файле конфигурации IB.
Индекс temp-пространство время сортировки время создания полное время*
BYNAME 113Mb 7 минут 6 минут 13 минут
BYBOX 22Mb 3 минуты 1 минута 4 минуты
BYBOX 22Mb 3 минуты 1 минута 4 минуты
*Полное время – время от начала выполнения команды create index/alter index active до ее окончания.
Итого, для создания двух индексов по 1 млн 280 тыс записей потребовалось в сумме 17 минут.
Разумеется, после создания индексов была еще раз проверена скорость вставки записей (3 раза по те же 10 тысяч). Она оказалась в 2.2 раза большей, чем без индексов. Т. е. около 1.5 минут.
Еще 1 тест
select count(*) from clients
where id > 10000 and id <50000
PLAN (CLIENTS INDEX (RDB$PRIMARY1))
where id > 10000 and id <50000
PLAN (CLIENTS INDEX (RDB$PRIMARY1))
Таких записей – 39999.
Current memory = 32006144
Delta memory = 361472
Max memory = 32007256
Elapsed time= 2.92 sec
Buffers = 3750
Reads = 1062
Fetches = 80469
Delta memory = 361472
Max memory = 32007256
Elapsed time= 2.92 sec
Buffers = 3750
Reads = 1062
Fetches = 80469
После этого
delete from clients
where id > 10000 and id <50000
PLAN (CLIENTS INDEX (RDB$PRIMARY1))
Current memory = 32153600
Delta memory = 147456
Max memory = 32153600
Elapsed time= 4.47 sec
Buffers = 3750
Reads = 1
Writes 0
Fetches = 200326
where id > 10000 and id <50000
PLAN (CLIENTS INDEX (RDB$PRIMARY1))
Current memory = 32153600
Delta memory = 147456
Max memory = 32153600
Elapsed time= 4.47 sec
Buffers = 3750
Reads = 1
Writes 0
Fetches = 200326
И затем Disconnect/Connect и
select count(*) from clients
PLAN (CLIENTS NATURAL)
COUNT
===========
1239982
Current memory = 31933440
Delta memory = 288768
Max memory = 32007256
Elapsed time= 555.35 sec
Buffers = 3750
Reads = 40387
Writes 28723
Fetches = 3267043
PLAN (CLIENTS NATURAL)
COUNT
===========
1239982
Current memory = 31933440
Delta memory = 288768
Max memory = 32007256
Elapsed time= 555.35 sec
Buffers = 3750
Reads = 40387
Writes 28723
Fetches = 3267043
Count подсчитывается долго (в 10 раз дольше чем раньше) из-за сборки мусора, т. е. сборки "удаленных" предыдущим запросом данных. По сборке мусора и др. см. соответствующие статьи в разделе "для разработчика".
Далее, второй вариант (с указанием диапазона where в выборке select count()):
delete from clients
where id >= 50000 and id <90000
PLAN (CLIENTS INDEX (RDB$PRIMARY1))
Current memory = 32154624
Delta memory = 221184
Max memory = 32154624
Elapsed time= 5.83 sec
Buffers = 3750
Reads = 1037
Writes 0
Fetches = 200334
select count(*) from clients
where id >= 50000 and id <90000
PLAN (CLIENTS INDEX (RDB$PRIMARY1))
COUNT
===========
0
Current memory = 32007168
Delta memory = 362496
Max memory = 32007256
Elapsed time= 459.24 sec
Buffers = 3750
Reads = 10144
Writes 26039
Fetches = 727377
select count(*) from clients
PLAN (CLIENTS NATURAL)
COUNT
===========
1199982
Current memory = 31936512
Delta memory = -70656
Max memory = 32139352
Elapsed time= 90.24 sec
Buffers = 3750
Reads = 29368
Writes 2812
Fetches = 2458687
select count(*) from clients
PLAN (CLIENTS NATURAL)
COUNT
===========
1199982
Current memory = 31936512
Delta memory = 0
Max memory = 32139352
Elapsed time= 55.33 sec
Buffers = 3750
Reads = 29370
Writes 0
Fetches = 2458687
where id >= 50000 and id <90000
PLAN (CLIENTS INDEX (RDB$PRIMARY1))
Current memory = 32154624
Delta memory = 221184
Max memory = 32154624
Elapsed time= 5.83 sec
Buffers = 3750
Reads = 1037
Writes 0
Fetches = 200334
select count(*) from clients
where id >= 50000 and id <90000
PLAN (CLIENTS INDEX (RDB$PRIMARY1))
COUNT
===========
0
Current memory = 32007168
Delta memory = 362496
Max memory = 32007256
Elapsed time= 459.24 sec
Buffers = 3750
Reads = 10144
Writes 26039
Fetches = 727377
select count(*) from clients
PLAN (CLIENTS NATURAL)
COUNT
===========
1199982
Current memory = 31936512
Delta memory = -70656
Max memory = 32139352
Elapsed time= 90.24 sec
Buffers = 3750
Reads = 29368
Writes 2812
Fetches = 2458687
select count(*) from clients
PLAN (CLIENTS NATURAL)
COUNT
===========
1199982
Current memory = 31936512
Delta memory = 0
Max memory = 32139352
Elapsed time= 55.33 sec
Buffers = 3750
Reads = 29370
Writes 0
Fetches = 2458687
Выводы
Самый главный вывод, наверное, в этом тесте – не используйте в качестве сервера Windows 95/98. :-)Второе – для получения максимальной скорости вставки записей необходимо пользоваться либо вставкой из скрипта, либо писать программу на IB API, FreeIBComponents или IBX. В случае программы скорость вставки может быть даже выше, если использовать подготовленные (prepared) запросы (в этом случае передаются только данные, и оптимизатор не разбирает или подготавливает запрос при каждой вставке записей). Кроме этого, вставка записей через BDE будет минимум в 1.5 раз медленнее, чем например через FreeIBComponents/IBX.
При этом ни в коем случае нельзя использовать компоненты, которые кэшируют данные – FIBDataSet, IBTable, IBQuery, IBDataSet. Пользоваться можно только компонентами, работающими без буферизации – FIBQuery или IBSQL.
WISQL, предположительно, использует нечто вроде Application.ProcessMessages в Delphi, что замедляет вставку записей почти в два раза по сравнению с ISQL.
Наличие посторонних коннектов на скорость вставки не влияет, если только в таких коннектах не производится никаких операций с БД.
Количество записей в таблице не влияет на скорость вставки. Ухудшение производительности под Windows95/98 может быть вызвано плохой работой с виртуальной памятью. На NT, как видите, таких проблем нет.
Наличие индексов замедляет скорость вставки данных. Более оптимальным по времени может оказаться деактивация индексов, вставка данных, и активация индексов – в сумме эти операции могут занять меньшее время, чем вставка данных с активными индексами.
При удалении данных физически записи не удаляются, а только помечаются как "мусор". Следующая транзакция с запросом select может обнаружить, что эти записи уже никому не нужны, и "мусорные" записи будут принудительно вычищены. Это приводит к значительному замедлению выборок из базы данных, как в последнем примере с серией delete/select count. Следует стремиться к тому, чтобы подобные удаления или модификации больших групп записей производились как "регламентные". Возможно даже лучшим вариантом окажется разделение основной таблицы на более мелкие (например информация по месяцам), и удаление всей таблицы (DROP TABLE) вместо удаления групп записей (скажем, за месяц). Кроме того, на таком тесте нельзя основывать предположение о "медленности" IB.
Вот и все. Чтобы и у вас так же работало! (и даже еще лучше).
Приложение: stat_v.txt – файл анализа статистики по тестовой БД.