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
Вот статистика после бэкапа:
Код: Выделить всё
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
Было
Data pages: 3824, data page slots: 3824, average fill: 63%
а стало
Data pages: 2828, data page slots: 2828, average fill: 90%
Что ещё посмотреть?