Dmitry Yemanov wrote:
Alexey Voytsehovich wrote:
таблица на 107 миллионов записей, каждый день 12 лимонов удаляем,
столько же добавляем, через некоторое время дикие тормоза на сборе
статистики и удалении. :(
А при отключенных юзерах тормоза тоже имеют место?
вот информация снятая запросами mon$attacments mon$statements
"##################################################################################"
"OPC"
"##################################################################################"
MON$ATTACHMENT_ID MON$SERVER_PID MON$STATE MON$ATTACHMENT_NAME
MON$USER
MON$ROLE
MON$REMOTE_PROTOCOL MON$REMOTE_ADDRESS
MON$REMOTE_PID
MON$CHARACTER_SET_ID MON$TIMESTAMP MON$GARBAGE_COLLECTION
MON$REMOTE_PROCESS
MON$STAT_ID
================= ============== =========
===============================================================================
===============================================================================
===============================================================================
===================
===============================================================================
============== ==================== =========================
======================
===============================================================================
============
122471 4892 1 opc
SYSDBA
NONE
TCPv4 127.0.0.1
4004
0 2008-09-10 08:00:09.5460 1 c:\Program
Files\Firebird\Firebird_2_5\bin\isql.exe
2
122458 3956 1 opc
SYSDBA
NONE
TCPv4 127.0.0.1
3024
0 2008-09-09 21:00:03.8430 1 C:\Program
Files\OPCServer\demoserver1_0.exe
8
MON$STATEMENT_ID MON$ATTACHMENT_ID MON$TRANSACTION_ID MON$STATE
MON$TIMESTAMP MON$SQL_TEXT MON$STAT_ID
================ ================= ================== =========
========================= ================= ============
5 122471 2652436 1 2008-09-10
08:00:09.9060 0:1 6
==============================================================================
MON$SQL_TEXT:
select * from MON$ATTACHMENTS
==============================================================================
10 122458 2652367 1 2008-09-09
21:00:46.2810 0:2 11
==============================================================================
MON$SQL_TEXT:
delete from MomData where ARDTimeStamp < (current_date - 7)
==============================================================================
вот последняя статистика на momdata (которую смогли посчитать, состояние на
06-09-08 01-19
MOMDATA (154)
Primary pointer page: 200, Index root page: 201
Average record length: 25.36, total records: 105694679
Average version length: 0.00, total versions: 0, max versions: 0
Data pages: 434068, data page slots: 917632, average fill: 63%
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 434067
80 - 99% = 0
Index IDX_MOMDATA1_NEW1 (0)
Depth: 3, leaf buckets: 72877, nodes: 105730152
Average data length: 0.13, total dup: 98687506, max dup: 86963
Fill distribution:
0 - 19% = 329
20 - 39% = 40
40 - 59% = 62317
60 - 79% = 7875
80 - 99% = 2316
Index IDX_MOMDATA2_NEW1 (1)
Depth: 3, leaf buckets: 104131, nodes: 144318020
Average data length: 0.91, total dup: 98687204, max dup: 87015
Fill distribution:
0 - 19% = 794
20 - 39% = 218
40 - 59% = 77941
60 - 79% = 7260
80 - 99% = 17918
Index IDX_MOMDATA4_NEW1 (2)
Depth: 3, leaf buckets: 82926, nodes: 105818657
Average data length: 3.00, total dup: 178400, max dup: 21754
Fill distribution:
0 - 19% = 2316
20 - 39% = 603
40 - 59% = 32118
60 - 79% = 1134
80 - 99% = 46755
вот заголовок бд (на то же время)
Database "D:\Program Files\Firebird\Firebird_2_1\opc.ib"
Database header page information:
Flags 0
Checksum 12345
Generation 2234021
Page size 16384
ODS version 11.2
Oldest transaction 2217472
Oldest active 2217473
Oldest snapshot 2217473
Next transaction 2217474
Bumped transaction 1
Sequence number 0
Next attachment ID 103360
Implementation ID 16
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Aug 18, 2008 11:37:13
Attributes force write
Variable header data:
Sweep interval: 0
*END*
ЗЫ. Какой размер базы, если хорошенько заархивировать? :-) Я мог бы
выкачать и взглянуть...
хз. тут надо админов дернуть чтобы проект оттормозили. сейчас размер бд 24 547
409 920 OPC.IB
ЗюЫю
сервер 4-х головый, 4 гига памяти, винты скорость до 90 мб\с, две сетевые карты
в разные подсети, все операции идут на Localhost:opc (алиас орс существует)
