Распухла БД

Ремонт и восстановление баз данных InterBase, Firebird, Yaffil

Модераторы: kdv, Alexey Kovyazin

Ответить
thor
Сообщения: 1
Зарегистрирован: 15 апр 2011, 12:10

Распухла БД

Сообщение thor » 15 апр 2011, 12:35

Доброго времени суток.
FB 2.1 В базу в таблицу Trends в блоб-поле Trend добавляются посредством UDF из клиента. При превышении определённого размера блоб-поля (10K), ХП вставляет запись в таблицу LogTrends (и с ней больше не работает на запись/изменение - только чтение), а затем обнуляет в Trends это блоб-поле. Остальные таблицы - справочники, которые обновляются только при старте клиента.
В один прекрасный вечер размер БД увеличился в 10 раз и составил 1,5 ГБайта.

Вот статистка "распухшей" БД:

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


Database "E:\ABase\ARCHIVE1big.FDB" 
Database header page information: 
	Flags			0 
	Checksum		12345 
	Generation		3056362 
	Page size		16384 
	ODS version		11.1 
	Oldest transaction	3056351 
	Oldest active		3056352 
	Oldest snapshot		3056352 
	Next transaction	3056353 
	Bumped transaction	1 
	Sequence number		0 
	Next attachment ID	2706 
	Implementation ID	16 
	Shadow count		0 
	Page buffers		0 
	Next header page	0 
	Database dialect	3 
	Creation date		Apr 9, 2011 14:06:48 
	Attributes		force write 
 
    Variable header data: 
	Sweep interval:		20000 
	*END* 
 
Database file sequence: 
File E:\ABase\ARCHIVE1big.FDB is the only file 
 
Analyzing database pages ... 
 
IBE$PARAMS_HISTORY (130) 
    Primary pointer page: 141, Index root page: 142 
    Average record length: 0.00, total records: 0 
    Average version length: 0.00, total versions: 0, max versions: 0 
    Data pages: 0, data page slots: 0, average fill: 0% 
    Fill distribution: 
	 0 - 19% = 0 
	20 - 39% = 0 
	40 - 59% = 0 
	60 - 79% = 0 
	80 - 99% = 0 
 
    Index RDB$PRIMARY1 (0) 
	Depth: 1, leaf buckets: 1, nodes: 0 
	Average data length: 0.00, total dup: 0, max dup: 0 
	Fill distribution: 
	     0 - 19% = 1 
	    20 - 39% = 0 
	    40 - 59% = 0 
	    60 - 79% = 0 
	    80 - 99% = 0 
 
ITEMS (128) 
    Primary pointer page: 138, Index root page: 139 
    Average record length: 63.31, total records: 38510 
    Average version length: 0.00, total versions: 0, max versions: 0 
    Data pages: 253, data page slots: 262, average fill: 76% 
    Fill distribution: 
	 0 - 19% = 7 
	20 - 39% = 1 
	40 - 59% = 2 
	60 - 79% = 222 
	80 - 99% = 21 
 
    Index ITEMS_IDX1 (1) 
	Depth: 2, leaf buckets: 23, nodes: 38510 
	Average data length: 2.00, total dup: 2, max dup: 1 
	Fill distribution: 
	     0 - 19% = 0 
	    20 - 39% = 0 
	    40 - 59% = 16 
	    60 - 79% = 0 
	    80 - 99% = 7 
 
    Index PK_ITEMS (0) 
	Depth: 2, leaf buckets: 12, nodes: 38510 
	Average data length: 1.00, total dup: 0, max dup: 0 
	Fill distribution: 
	     0 - 19% = 0 
	    20 - 39% = 0 
	    40 - 59% = 0 
	    60 - 79% = 0 
	    80 - 99% = 12 
 
ITEMSDESCRIPTION (134) 
    Primary pointer page: 149, Index root page: 150 
    Average record length: 50.38, total records: 38281 
    Average version length: 0.00, total versions: 0, max versions: 0 
    Data pages: 214, data page slots: 226, average fill: 74% 
    Fill distribution: 
	 0 - 19% = 1 
	20 - 39% = 0 
	40 - 59% = 1 
	60 - 79% = 212 
	80 - 99% = 0 
 
    Index FK_ITEMSDESCRIPTION_1 (2) 
	Depth: 2, leaf buckets: 12, nodes: 38281 
	Average data length: 1.00, total dup: 0, max dup: 0 
	Fill distribution: 
	     0 - 19% = 0 
	    20 - 39% = 0 
	    40 - 59% = 0 
	    60 - 79% = 1 
	    80 - 99% = 11 
 
    Index FK_ITEMSDESCRIPTION_2 (1) 
	Depth: 2, leaf buckets: 15, nodes: 38281 
	Average data length: 0.00, total dup: 38255, max dup: 24047 
	Fill distribution: 
	     0 - 19% = 1 
	    20 - 39% = 0 
	    40 - 59% = 9 
	    60 - 79% = 1 
	    80 - 99% = 4 
 
    Index PK_ITEMSDESCRIPTION (0) 
	Depth: 2, leaf buckets: 12, nodes: 38281 
	Average data length: 1.00, total dup: 0, max dup: 0 
	Fill distribution: 
	     0 - 19% = 0 
	    20 - 39% = 0 
	    40 - 59% = 0 
	    60 - 79% = 1 
	    80 - 99% = 11 
 
ITEMTYPES (133) 
    Primary pointer page: 147, Index root page: 148 
    Average record length: 39.60, total records: 10 
    Average version length: 0.00, total versions: 0, max versions: 0 
    Data pages: 1, data page slots: 1, average fill: 3% 
    Fill distribution: 
	 0 - 19% = 1 
	20 - 39% = 0 
	40 - 59% = 0 
	60 - 79% = 0 
	80 - 99% = 0 
 
    Index PK_ITEMTYPES (0) 
	Depth: 1, leaf buckets: 1, nodes: 10 
	Average data length: 1.20, total dup: 0, max dup: 0 
	Fill distribution: 
	     0 - 19% = 1 
	    20 - 39% = 0 
	    40 - 59% = 0 
	    60 - 79% = 0 
	    80 - 99% = 0 
 
[color=#0000BF]LOGTRENDS (131) 
    Primary pointer page: 143, Index root page: 144 
    Average record length: 45.92, total records: 3824 
    Average version length: 0.00, total versions: 0, max versions: 0 
    Data pages: 3824, data page slots: 3824, average fill: 63% 
    Fill distribution: 
	 0 - 19% = 0 
	20 - 39% = 0 
	40 - 59% = 0 
	60 - 79% = 3824 
	80 - 99% = 0 
 
    Index PK_LOGTRENDS (0) 
	Depth: 2, leaf buckets: 4, nodes: 3824 
	Average data length: 7.04, total dup: 0, max dup: 0 
	Fill distribution: 
	     0 - 19% = 1 
	    20 - 39% = 0 
	    40 - 59% = 0 
	    60 - 79% = 0 
	    80 - 99% = 3 
 
TRENDS (132) 
    Primary pointer page: 145, Index root page: 146 
    Average record length: 35.26, total records: 38510 
    Average version length: 0.00, total versions: 0, max versions: 0 
    Data pages: 3694, data page slots: 16724, average fill: 70% 
    Fill distribution: 
	 0 - 19% = 443 
	20 - 39% = 419 
	40 - 59% = 368 
	60 - 79% = 320 
	80 - 99% = 2144 
 
    Index FK_TRENDS_1 (1) 
	Depth: 2, leaf buckets: 12, nodes: 38510 
	Average data length: 1.00, total dup: 0, max dup: 0 
	Fill distribution: 
	     0 - 19% = 0 
	    20 - 39% = 0 
	    40 - 59% = 0 
	    60 - 79% = 0 
	    80 - 99% = 12 
 
    Index PK_TRENDS (0) 
	Depth: 2, leaf buckets: 12, nodes: 38510 
	Average data length: 1.00, total dup: 0, max dup: 0 
	Fill distribution: 
	     0 - 19% = 0 
	    20 - 39% = 0 
	    40 - 59% = 0 
	    60 - 79% = 0 
	    80 - 99% = 12 
[/color] 
UNITS (135) 
    Primary pointer page: 151, Index root page: 152 
    Average record length: 17.12, total records: 26 
    Average version length: 0.00, total versions: 0, max versions: 0 
    Data pages: 1, data page slots: 1, average fill: 5% 
    Fill distribution: 
	 0 - 19% = 1 
	20 - 39% = 0 
	40 - 59% = 0 
	60 - 79% = 0 
	80 - 99% = 0 
 
    Index PK_UNITS (0) 
	Depth: 1, leaf buckets: 1, nodes: 26 
	Average data length: 1.04, total dup: 0, max dup: 0 
	Fill distribution: 
	     0 - 19% = 1 
	    20 - 39% = 0 
	    40 - 59% = 0 
	    60 - 79% = 0 
	    80 - 99% = 0 
 
    Index UNITS_IDX1 (1) 
	Depth: 1, leaf buckets: 1, nodes: 26 
	Average data length: 3.00, total dup: 0, max dup: 0 
	Fill distribution: 
	     0 - 19% = 1 
	    20 - 39% = 0 
	    40 - 59% = 0 
	    60 - 79% = 0 
	    80 - 99% = 0 

Сделал бэкап со сборкой мусора, и размер базы вернулся к нормальному размеру (примерно 110 МБайт)
Вот статистика после бэкапа:

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

 
Database "E:\ABase\ARCHIVE1big.FDB" 
Database header page information: 
	Flags			0 
	Checksum		12345 
	Generation		70 
	Page size		16384 
	ODS version		11.1 
	Oldest transaction	60 
	Oldest active		61 
	Oldest snapshot		61 
	Next transaction	62 
	Bumped transaction	1 
	Sequence number		0 
	Next attachment ID	5 
	Implementation ID	16 
	Shadow count		0 
	Page buffers		0 
	Next header page	0 
	Database dialect	3 
	Creation date		Apr 15, 2011 11:16:15 
	Attributes		force write 
 
    Variable header data: 
	Sweep interval:		20000 
	*END* 
 
 
Database file sequence: 
File E:\ABase\ARCHIVE1big.FDB is the only file 
 
Analyzing database pages ... 
ITEMS (128) 
    Primary pointer page: 138, Index root page: 139 
    Average record length: 64.47, total records: 38510 
    Average version length: 0.00, total versions: 0, max versions: 0 
    Data pages: 248, data page slots: 248, average fill: 77% 
    Fill distribution: 
	 0 - 19% = 0 
	20 - 39% = 1 
	40 - 59% = 0 
	60 - 79% = 237 
	80 - 99% = 10 
 
    Index ITEMS_IDX1 (1) 
	Depth: 2, leaf buckets: 16, nodes: 38510 
	Average data length: 2.00, total dup: 2, max dup: 1 
	Fill distribution: 
	     0 - 19% = 1 
	    20 - 39% = 0 
	    40 - 59% = 0 
	    60 - 79% = 0 
	    80 - 99% = 15 
 
    Index PK_ITEMS (0) 
	Depth: 2, leaf buckets: 12, nodes: 38510 
	Average data length: 1.00, total dup: 0, max dup: 0 
	Fill distribution: 
	     0 - 19% = 0 
	    20 - 39% = 0 
	    40 - 59% = 0 
	    60 - 79% = 0 
	    80 - 99% = 12 
 
ITEMSDESCRIPTION (134) 
    Primary pointer page: 149, Index root page: 150 
    Average record length: 48.81, total records: 38281 
    Average version length: 0.00, total versions: 0, max versions: 0 
    Data pages: 208, data page slots: 208, average fill: 74% 
    Fill distribution: 
	 0 - 19% = 0 
	20 - 39% = 0 
	40 - 59% = 0 
	60 - 79% = 208 
	80 - 99% = 0 
 
    Index FK_ITEMSDESCRIPTION_1 (2) 
	Depth: 2, leaf buckets: 12, nodes: 38281 
	Average data length: 1.00, total dup: 0, max dup: 0 
	Fill distribution: 
	     0 - 19% = 0 
	    20 - 39% = 0 
	    40 - 59% = 0 
	    60 - 79% = 1 
	    80 - 99% = 11 
 
    Index FK_ITEMSDESCRIPTION_2 (1) 
	Depth: 2, leaf buckets: 10, nodes: 38281 
	Average data length: 0.00, total dup: 38255, max dup: 24047 
	Fill distribution: 
	     0 - 19% = 0 
	    20 - 39% = 1 
	    40 - 59% = 0 
	    60 - 79% = 0 
	    80 - 99% = 9 
 
    Index PK_ITEMSDESCRIPTION (0) 
	Depth: 2, leaf buckets: 12, nodes: 38281 
	Average data length: 1.00, total dup: 0, max dup: 0 
	Fill distribution: 
	     0 - 19% = 0 
	    20 - 39% = 0 
	    40 - 59% = 0 
	    60 - 79% = 1 
	    80 - 99% = 11 
 
ITEMTYPES (133) 
    Primary pointer page: 147, Index root page: 148 
    Average record length: 37.60, total records: 10 
    Average version length: 0.00, total versions: 0, max versions: 0 
    Data pages: 1, data page slots: 1, average fill: 3% 
    Fill distribution: 
	 0 - 19% = 1 
	20 - 39% = 0 
	40 - 59% = 0 
	60 - 79% = 0 
	80 - 99% = 0 
 
    Index PK_ITEMTYPES (0) 
	Depth: 1, leaf buckets: 1, nodes: 10 
	Average data length: 1.20, total dup: 0, max dup: 0 
	Fill distribution: 
	     0 - 19% = 1 
	    20 - 39% = 0 
	    40 - 59% = 0 
	    60 - 79% = 0 
	    80 - 99% = 0 
 
LOGTRENDS (131) 
    Primary pointer page: 143, Index root page: 144 
    Average record length: 45.92, total records: 3824 
    Average version length: 0.00, total versions: 0, max versions: 0 
    Data pages: 3824, data page slots: 3824, average fill: 63% 
    Fill distribution: 
	 0 - 19% = 0 
	20 - 39% = 0 
	40 - 59% = 0 
	60 - 79% = 3824 
	80 - 99% = 0 
 
    Index PK_LOGTRENDS (0) 
	Depth: 2, leaf buckets: 4, nodes: 3824 
	Average data length: 7.04, total dup: 0, max dup: 0 
	Fill distribution: 
	     0 - 19% = 1 
	    20 - 39% = 0 
	    40 - 59% = 0 
	    60 - 79% = 0 
	    80 - 99% = 3 
 
TRENDS (132) 
    Primary pointer page: 145, Index root page: 146 
    Average record length: 33.96, total records: 38510 
    Average version length: 0.00, total versions: 0, max versions: 0 
    Data pages: 2828, data page slots: 2828, average fill: 90% 
    Fill distribution: 
	 0 - 19% = 1 
	20 - 39% = 0 
	40 - 59% = 87 
	60 - 79% = 273 
	80 - 99% = 2467 
 
    Index FK_TRENDS_1 (1) 
	Depth: 2, leaf buckets: 13, nodes: 38510 
	Average data length: 1.00, total dup: 0, max dup: 0 
	Fill distribution: 
	     0 - 19% = 0 
	    20 - 39% = 0 
	    40 - 59% = 0 
	    60 - 79% = 0 
	    80 - 99% = 13 
 
    Index PK_TRENDS (0) 
	Depth: 2, leaf buckets: 13, nodes: 38510 
	Average data length: 1.00, total dup: 0, max dup: 0 
	Fill distribution: 
	     0 - 19% = 0 
	    20 - 39% = 0 
	    40 - 59% = 0 
	    60 - 79% = 0 
	    80 - 99% = 13 
 
UNITS (135) 
    Primary pointer page: 151, Index root page: 152 
    Average record length: 15.12, total records: 26 
    Average version length: 0.00, total versions: 0, max versions: 0 
    Data pages: 1, data page slots: 1, average fill: 5% 
    Fill distribution: 
	 0 - 19% = 1 
	20 - 39% = 0 
	40 - 59% = 0 
	60 - 79% = 0 
	80 - 99% = 0 
 
    Index PK_UNITS (0) 
	Depth: 1, leaf buckets: 1, nodes: 26 
	Average data length: 1.04, total dup: 0, max dup: 0 
	Fill distribution: 
	     0 - 19% = 1 
	    20 - 39% = 0 
	    40 - 59% = 0 
	    60 - 79% = 0 
	    80 - 99% = 0 
 
    Index UNITS_IDX1 (1) 
	Depth: 1, leaf buckets: 1, nodes: 26 
	Average data length: 3.00, total dup: 0, max dup: 0 
	Fill distribution: 
	     0 - 19% = 1 
	    20 - 39% = 0 
	    40 - 59% = 0 
	    60 - 79% = 0 
	    80 - 99% = 0 
Может кто подскажет в чём причина? Версий записей в обоих случайх нет, зависших транзакций тоже (в статистике я их не вижу ?). Изменились вот параметры в Trends.
Было
Data pages: 3824, data page slots: 3824, average fill: 63%
а стало
Data pages: 2828, data page slots: 2828, average fill: 90%
Что ещё посмотреть?

dimitr
Разработчик Firebird
Сообщения: 888
Зарегистрирован: 26 окт 2004, 16:20

Re: Распухла БД

Сообщение dimitr » 15 апр 2011, 20:56

мусорные блобы копились?

kdv
Forum Admin
Сообщения: 6595
Зарегистрирован: 25 окт 2004, 18:07

Re: Распухла БД

Сообщение kdv » 16 апр 2011, 01:18

Сделал бэкап со сборкой мусора, и размер базы вернулся к нормальному размеру (примерно 110 МБайт)
не говорите ерунду (про "бэкап со сборкой мусора")
www.ibase.ru/devinfo/gbak.htm

Ответить