Измерение скорости массовых UPDATE.

В одной из прикладных задач возникла проблема - с течением времени (1, 2, 3, 4 часа) сильно замедлялась работа с базой данных (так, что приходилось рестартовать сервер). При этом никаких конфигурационных или других причин обнаружено не было (в т.ч. таких как авто-sweep, transaction gap и др.). Со слов авторов прикладной задачи им удалось определить причину "торможения", и они предоставили тестовый запрос для воспроизведения проблемы.

описание механизма возникновения проблемы и ее решение при помощи no_savepoint читайте в журнале ibdeveloper.com N2 -
ibdeveloper.com/issues/issue-2-oct-17-2005/testing-the-no-savepoint-feature-in-interbase-751/

Тестируемые серверы

Firebird 1.0.2, Firebird 1.5 (билд 4290), Yaffil 884, InterBase 7.0.1.1, InterBase 7.1.0.189 (SP2).

Операционная система - Windows 2000 Professional, SP 4.

Тестировались только сервера SuperServer.

Тестируемые данные и запросы

В качестве теста использовалась таблица, содержащая 100 тысяч записей.
Тестовые запросы:

1. update tst set val=val+0
   where val >90000
2. update tst set val=val+0

по столбцу val построен индекс.

бэкап тестовой БД, результаты статистики из IBExpert и сводные таблицы и графики в Excel находятся в файле updtest.zip (274k).

Процедура теста

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

1. устанавливался сервер конкретной версии, его gds32.dll, gbak.exe. сервер запускался как приложение (например ibserver -a)
2. в IBExpert производился restore базы данных, восстановление шло через tcp (localhost:h:\db\updtest.ib), без commit after each table, с указанием размера страницы 4096 байт.
3. открывался алиас updtest (тоже через localhost)
4. в окне SQLEditor выполнялся запрос (1 или 2) 4 раза подряд с сохранением результатов статистики (пункты 1 exec, 2 exec и т.д. в файлах updtest_*.txt). При этом commit не производился (т.е. количество версий после четвертого update оставалось равным 10 или 100 тысяч соответственно).

Запросы 1 и 2 выполнялись каждый раз на заново восстановленной из backup базе данных.

Смысл теста

• UPDATE 1
  обновляет 10 тысяч (запрос 1) или 100 тысяч (запрос 2) записей, т.е. создает соответствующее число версий на диске. Статистика сообщает о скорости чтения данных с диска, количестве обращения к страницам.
• UPDATE 2
  производит обновление информации в версиях, созданных предыдущим update. Статистика сообщает об эффективности использования кэша, количестве изменяемых страниц и обращений к страницам.
• UPDATE 3/4
  то же самое, что и UPDATE 2

(возможно, что наличие индекса по столбцу VAL не обязательно, т.к. повторная проверка теста при отсутствии этого индекса на IB 7.0.1.1 показала идентичность времени первого update, 22 секунды на втором update (против 8), 90 секунд на третьем, и 98 секунд на четвертом. т.е. отличался по времени только второй update).

Результаты теста

10К

По скорости на первом месте Yaffil, и с мизерным отставанием - Firebird 1.5. InterBase 7.0 показал результаты чуть лучше, чем Firebird 1.0.2. InterBase 7.1 SP2 оказался на последнем месте.

100K

Разрыв результатов виден еще сильнее. Yaffil все равно быстрее, несмотря на то, что у него не "приходит к нулю" количество Reads/Writes из кэша на 3 и 4-ом update. Firebird очень близок по результатам, как и в тесте 10К.
InterBase 7.0 на графике кажется близок по результатам к этим серверам, однако если сравнить время в числовом выражении - то это 2.5 секунд на 3 и 4 update у FB1.5/YA, и ~90 секунд у IB 7.0. Т.е. время выполнения хуже в 30 раз.
InterBase 7.1 и Firebird 1.0.2 (т.е. можно считать что IB 6.0) показали худшие результаты. У FB 1.0 замедление в 500 раз, у IB 7.1 - 750 раз.

По загрузке процессора только Yaffil и Firebird 1.5 показали среднюю загрузку на update 2 в 50%, остальные сервера - 100%.

О проблеме с производительностью InterBase 7.1 для этого случая (100К) было сообщено в b.p.i. На что последовал ответ от Quinn Wildman:

While I didn't test your problem, I talked to R&D about this problem. In InterBase 7.1 there was change in how the engine handled the condition of more than 10,000 rows being updated more than once which introduced this condition. I think we'll fix this problem with the next version of InterBase. With InterBase 7.1, the only solution is to make sure the number of multiply updated rows within a transaction is <10,000.

Т.е. действительно, ускорение сборки мусора в индексах и вообще изменение механизма сборки мусора, введенное в 7.1, привело к ухудшению производительности в данном тесте.

В InterBase 7.5.1 ситуация с данной проблемой исправлена. Кроме того, введен параметр dbp isc_dpb_no_savepoint, который слегка ускоряет массовую вставку, удаление или обновление записей.

Общие

• Было отмечено, что Yaffil и Firebird по крайней мере в статистике сообщают о размере кэша, точно соответствующему заданному в ibconfig (yaconfig, firebird.conf) - 2048. InterBase вопреки установленному явно значению почему-то выдавал 2038 (IB 7.0) или 2051 (IB 7.1) страниц.

Примечания

Для данного теста контроль времени сборки мусора (select count(*) from tst в очередном коннекте) не проверялся, по причине отсутствия индексов с неуникальными значениями на таблице TST. Например, Firebird 1.5 выполнил запрос select count(*) from tst за 0,381 секунду. По статистике и времени сборки мусора данным запросом все сервера дают очень близкие результаты, за исключением Yaffil:

------ Performance info ------
   Prepare time 	= 0ms
   Execute time 	= 1s 913ms
   Avg fetch time 	= 1913,00 ms
   Current memory 	= 8392704
   Max memory 		= 8397824
   Memory buffers 	= 2048
   Reads from disk to cache 	=   2219
   Writes from cache to disk 	=   2672
   Fetches from cache 			= 809430
 

В Yaffil сборка мусора не производится отдельным thread, а также видна та же самая ситуация с кэшем, аналогичная результатам в тесте 100K/