[firebase-br] Estatistica do BD
Josauro S.J.
josauro em casasoft.inf.br
Ter Maio 4 12:51:47 -03 2010
Obrigado Cantu,
Existe uma forma de identificar hoje qual a transação mais antiga o que
estava fazendo, talvez pelas tabelas do sistema, ou que registros foi usada
assim ficaria mais facil localizar em que parte do sistema pode ter ficado
pendurado. (FB 2.0.5)
Respondendo outra pergunta: Foi tirado vai IBExpert, Service, Data Base
Properties, mas pode ser tirado pelo IBConsole -> Maintenace / DataBase
Statistics
Grato
Josauro S.J.
----------------------------------------------------------------------
Message: 1
Date: Tue, 4 May 2010 10:50:08 -0300
From: "Carlos H. Cantu" <listas em warmboot.com.br>
To: FireBase <lista em firebase.com.br>
Subject: Re: [firebase-br] Estatistica do BD
Message-ID: <942858906.20100504105008 em warmboot.com.br>
Content-Type: text/plain; charset=iso-8859-1
Oldest active 11573098
Next transaction 11638337
Pelos números, existe uma transação que está ativa há 65.239
transações "atrás". Reveja seu controle transacional, pois isso impede
a coleta de lixo e geralmente prejudica a performance do servidor.
[]s
Carlos H. Cantu
www.FireBase.com.br - www.firebirdnews.org
www.warmboot.com.br - blog.firebase.com.br
JSJ> Prezados
JSJ> Nunca analizei nesse nível e gostaria de uma breve dica, sobre a
estatistica
JSJ> abaixo tirada de um banco de dados de um cliente, o qual tem +- 150 a
200
JSJ> terminais.
JSJ> É possível identificar algo que precise ser melhorado, temos sentido
uma
JSJ> queda de performan-se semanalmente, o sweep estava com 20000 setei para
0
JSJ> hoje e tirei o relatorio, entao o mesmo não foi feito ainda, sera feito
JSJ> manualmente.
JSJ> Cortei o log de tabelas para não estender o email.
JSJ> Database header page information:
JSJ> Flags 0
JSJ> Checksum 12345
JSJ> Generation 11655165
JSJ> Page size 4096
JSJ> ODS version 11.0
JSJ> Oldest transaction 11573097
JSJ> Oldest active 11573098
JSJ> Oldest snapshot 11573096
JSJ> Next transaction 11638337
JSJ> Bumped transaction 1
JSJ> Sequence number 0
JSJ> Next attachment ID 40124
JSJ> Implementation ID 19
JSJ> Shadow count 0
JSJ> Page buffers 0
JSJ> Next header page 0
JSJ> Database dialect 3
JSJ> Creation date Mar 3, 2010 10:25:46
JSJ> Attributes force write
JSJ> Variable header data:
JSJ> Sweep interval: 0
JSJ> *END*
JSJ> Database file sequence:
JSJ> File /dados/firebird/data/casasoft.fdb is the only file
JSJ> Analyzing database pages ...
JSJ> FICHA_IMOVEL (302)
JSJ> Primary pointer page: 1225, Index root page: 1226
JSJ> Data pages: 1286, data page slots: 1286, average fill: 93%
JSJ> Fill distribution:
JSJ> 0 - 19% = 0
JSJ> 20 - 39% = 1
JSJ> 40 - 59% = 0
JSJ> 60 - 79% = 72
JSJ> 80 - 99% = 1213
JSJ> Index IDX_FIC_IMOVEL (0)
JSJ> Depth: 2, leaf buckets: 35, nodes: 10894
JSJ> Average data length: 6.72, total dup: 0, max dup: 0
JSJ> Fill distribution:
JSJ> 0 - 19% = 0
JSJ> 20 - 39% = 0
JSJ> 40 - 59% = 4
JSJ> 60 - 79% = 3
JSJ> 80 - 99% = 28
JSJ> Index PK_FICHA_IMOVEL (1)
JSJ> Depth: 2, leaf buckets: 35, nodes: 10894
JSJ> Average data length: 6.71, total dup: 0, max dup: 0
JSJ> Fill distribution:
JSJ> 0 - 19% = 0
JSJ> 20 - 39% = 0
JSJ> 40 - 59% = 4
JSJ> 60 - 79% = 4
JSJ> 80 - 99% = 27
JSJ> FILIAL_CONEXAO (142)
JSJ> Primary pointer page: 901, Index root page: 902
JSJ> Data pages: 8486, data page slots: 8535, average fill: 56%
JSJ> Fill distribution:
JSJ> 0 - 19% = 43
JSJ> 20 - 39% = 265
JSJ> 40 - 59% = 8097
JSJ> 60 - 79% = 79
JSJ> 80 - 99% = 2
JSJ> Index PK_FILIAL_CONEXAO (0)
JSJ> Depth: 3, leaf buckets: 3303, nodes: 572436
JSJ> Average data length: 9.84, total dup: 2505, max dup: 22
JSJ> Fill distribution:
JSJ> 0 - 19% = 7
JSJ> 20 - 39% = 736
JSJ> 40 - 59% = 944
JSJ> 60 - 79% = 149
JSJ> 80 - 99% = 1467
JSJ> CLIENTE (150)
JSJ> Primary pointer page: 917, Index root page: 918
JSJ> Data pages: 4127, data page slots: 4127, average fill: 84%
JSJ> Fill distribution:
JSJ> 0 - 19% = 0
JSJ> 20 - 39% = 1
JSJ> 40 - 59% = 0
JSJ> 60 - 79% = 394
JSJ> 80 - 99% = 3732
JSJ> Index IDX_CLI_CNPJ_CPF (8)
JSJ> Depth: 3, leaf buckets: 360, nodes: 108147
JSJ> Average data length: 2.85, total dup: 64290, max dup: 46734
JSJ> Fill distribution:
JSJ> 0 - 19% = 1
JSJ> 20 - 39% = 0
JSJ> 40 - 59% = 301
JSJ> 60 - 79% = 3
JSJ> 80 - 99% = 55
JSJ> Index IDX_CLI_ESTADO_CIVIL (0)
JSJ> Depth: 2, leaf buckets: 132, nodes: 108146
JSJ> Average data length: 0.01, total dup: 108139, max dup: 50924
JSJ> Fill distribution:
JSJ> 0 - 19% = 0
JSJ> 20 - 39% = 0
JSJ> 40 - 59% = 45
JSJ> 60 - 79% = 2
JSJ> 80 - 99% = 85
JSJ> Index IDX_CLI_FILIAL (7)
JSJ> Depth: 2, leaf buckets: 138, nodes: 108143
JSJ> Average data length: 0.01, total dup: 108139, max dup: 60904
JSJ> Fill distribution:
JSJ> 0 - 19% = 1
JSJ> 20 - 39% = 0
JSJ> 40 - 59% = 30
JSJ> 60 - 79% = 0
JSJ> 80 - 99% = 107
JSJ> Index IDX_CLI_NACIONALIDADE (1)
JSJ> Depth: 2, leaf buckets: 144, nodes: 108143
JSJ> Average data length: 0.02, total dup: 108039, max dup: 65649
JSJ> Fill distribution:
JSJ> 0 - 19% = 0
JSJ> 20 - 39% = 0
JSJ> 40 - 59% = 61
JSJ> 60 - 79% = 1
JSJ> 80 - 99% = 82
JSJ> Index IDX_CLI_PROFISSAO (2)
JSJ> Depth: 2, leaf buckets: 168, nodes: 108146
JSJ> Average data length: 0.10, total dup: 106681, max dup: 38091
JSJ> Fill distribution:
JSJ> 0 - 19% = 0
JSJ> 20 - 39% = 0
JSJ> 40 - 59% = 98
JSJ> 60 - 79% = 8
JSJ> 80 - 99% = 62
JSJ> Index IDX_CLI_REFERENCIA (3)
JSJ> Depth: 3, leaf buckets: 287, nodes: 108143
JSJ> Average data length: 2.37, total dup: 3410, max dup: 17
JSJ> Fill distribution:
JSJ> 0 - 19% = 1
JSJ> 20 - 39% = 0
JSJ> 40 - 59% = 174
JSJ> 60 - 79% = 5
JSJ> 80 - 99% = 107
JSJ> Index IDX_SOUNDEX_NOME (5)
JSJ> Depth: 2, leaf buckets: 122, nodes: 108143
JSJ> Average data length: 0.12, total dup: 106766, max dup: 106656
JSJ> Fill distribution:
JSJ> 0 - 19% = 1
JSJ> 20 - 39% = 0
JSJ> 40 - 59% = 45
JSJ> 60 - 79% = 0
JSJ> 80 - 99% = 76
JSJ> Index IDX_SOUNDEX_NOME_ALTERNATIVO (6)
JSJ> Depth: 2, leaf buckets: 121, nodes: 108143
JSJ> Average data length: 0.11, total dup: 106833, max dup: 106656
JSJ> Fill distribution:
JSJ> 0 - 19% = 0
JSJ> 20 - 39% = 0
JSJ> 40 - 59% = 45
JSJ> 60 - 79% = 0
JSJ> 80 - 99% = 76
JSJ> Index PK_CLIENTE (4)
JSJ> Depth: 3, leaf buckets: 319, nodes: 108143
JSJ> Average data length: 6.12, total dup: 0, max dup: 0
JSJ> Fill distribution:
JSJ> 0 - 19% = 0
JSJ> 20 - 39% = 0
JSJ> 40 - 59% = 7
JSJ> 60 - 79% = 2
JSJ> 80 - 99% = 310
JSJ> FOTO_CLIENTE (249)
JSJ> Primary pointer page: 1118, Index root page: 1119
JSJ> Data pages: 0, data page slots: 0, average fill: 0%
JSJ> Fill distribution:
JSJ> 0 - 19% = 0
JSJ> 20 - 39% = 0
JSJ> 40 - 59% = 0
JSJ> 60 - 79% = 0
JSJ> 80 - 99% = 0
JSJ> Index IDX_FTC_CLIENTE (0)
JSJ> Depth: 1, leaf buckets: 1, nodes: 0
JSJ> Average data length: 0.00, total dup: 0, max dup: 0
JSJ> Fill distribution:
JSJ> 0 - 19% = 1
JSJ> 20 - 39% = 0
JSJ> 40 - 59% = 0
JSJ> 60 - 79% = 0
JSJ> 80 - 99% = 0
JSJ> Index PK_FOTO_CLIENTE (1)
JSJ> Depth: 1, leaf buckets: 1, nodes: 0
JSJ> Average data length: 0.00, total dup: 0, max dup: 0
JSJ> Fill distribution:
JSJ> 0 - 19% = 1
JSJ> 20 - 39% = 0
JSJ> 40 - 59% = 0
JSJ> 60 - 79% = 0
JSJ> 80 - 99% = 0
JSJ> FOTO_IMOVEL (282)
JSJ> Primary pointer page: 1185, Index root page: 1186
JSJ> Data pages: 1207, data page slots: 1207, average fill: 79%
JSJ> Fill distribution:
JSJ> 0 - 19% = 1
JSJ> 20 - 39% = 3
JSJ> 40 - 59% = 10
JSJ> 60 - 79% = 877
JSJ> 80 - 99% = 316
JSJ> Index IDX_FTO_IMOVEL (0)
JSJ> Depth: 2, leaf buckets: 87, nodes: 41174
JSJ> Average data length: 0.64, total dup: 37440, max dup: 51
JSJ> Fill distribution:
JSJ> 0 - 19% = 1
JSJ> 20 - 39% = 7
JSJ> 40 - 59% = 54
JSJ> 60 - 79% = 14
JSJ> 80 - 99% = 11
JSJ> Index PK_FOTO_IMOVEL (1)
JSJ> Depth: 2, leaf buckets: 131, nodes: 41174
JSJ> Average data length: 6.30, total dup: 0, max dup: 0
JSJ> Fill distribution:
JSJ> 0 - 19% = 0
JSJ> 20 - 39% = 0
JSJ> 40 - 59% = 7
JSJ> 60 - 79% = 15
JSJ> 80 - 99% = 109
JSJ> ACESSO_REGISTRO (330)
JSJ> Primary pointer page: 1282, Index root page: 1283
JSJ> Data pages: 1, data page slots: 1, average fill: 15%
JSJ> Fill distribution:
JSJ> 0 - 19% = 1
JSJ> 20 - 39% = 0
JSJ> 40 - 59% = 0
JSJ> 60 - 79% = 0
JSJ> 80 - 99% = 0
JSJ> Index IDX_ACR_REGISTRO (0)
JSJ> Depth: 1, leaf buckets: 1, nodes: 10
JSJ> Average data length: 35.90, total dup: 0, max dup: 0
JSJ> Fill distribution:
JSJ> 0 - 19% = 1
JSJ> 20 - 39% = 0
JSJ> 40 - 59% = 0
JSJ> 60 - 79% = 0
JSJ> 80 - 99% = 0
JSJ> Index PK_ACESSO_REGISTRO (1)
JSJ> Depth: 1, leaf buckets: 1, nodes: 10
JSJ> Average data length: 7.80, total dup: 0, max dup: 0
JSJ> Fill distribution:
JSJ> 0 - 19% = 1
JSJ> 20 - 39% = 0
JSJ> 40 - 59% = 0
JSJ> 60 - 79% = 0
JSJ> 80 - 99% = 0
JSJ> AGENCIA (137)
JSJ> Primary pointer page: 891, Index root page: 892
JSJ> Data pages: 397, data page slots: 397, average fill: 71%
JSJ> Fill distribution:
JSJ> 0 - 19% = 0
JSJ> 20 - 39% = 0
JSJ> 40 - 59% = 0
JSJ> 60 - 79% = 397
JSJ> 80 - 99% = 0
JSJ> Index IDX_AGE_BANCO (0)
JSJ> Depth: 2, leaf buckets: 25, nodes: 19170
JSJ> Average data length: 0.03, total dup: 19117, max dup: 5200
JSJ> Fill distribution:
JSJ> 0 - 19% = 1
JSJ> 20 - 39% = 0
JSJ> 40 - 59% = 10
JSJ> 60 - 79% = 0
JSJ> 80 - 99% = 14
JSJ> Index PK_AGENCIA (1)
JSJ> Depth: 2, leaf buckets: 57, nodes: 19170
JSJ> Average data length: 6.67, total dup: 0, max dup: 0
JSJ> Fill distribution:
JSJ> 0 - 19% = 0
JSJ> 20 - 39% = 0
JSJ> 40 - 59% = 2
JSJ> 60 - 79% = 1
JSJ> 80 - 99% = 54
JSJ> AGENDA (217)
JSJ> Primary pointer page: 1053, Index root page: 1054
JSJ> Data pages: 23, data page slots: 23, average fill: 72%
JSJ> Fill distribution:
JSJ> 0 - 19% = 0
JSJ> 20 - 39% = 0
JSJ> 40 - 59% = 0
JSJ> 60 - 79% = 23
JSJ> 80 - 99% = 0
JSJ> Index IDX_AGE_AGENDADO_PARA (0)
JSJ> Depth: 1, leaf buckets: 1, nodes: 1072
JSJ> Average data length: 0.00, total dup: 1071, max dup: 1071
JSJ> Fill distribution:
JSJ> 0 - 19% = 0
JSJ> 20 - 39% = 0
JSJ> 40 - 59% = 1
JSJ> 60 - 79% = 0
JSJ> 80 - 99% = 0
JSJ> Index IDX_AGE_CONCLUIDO (4)
JSJ> Depth: 1, leaf buckets: 1, nodes: 1072
JSJ> Average data length: 0.01, total dup: 1068, max dup: 745
JSJ> Fill distribution:
JSJ> 0 - 19% = 0
JSJ> 20 - 39% = 0
JSJ> 40 - 59% = 0
JSJ> 60 - 79% = 0
JSJ> 80 - 99% = 1
JSJ> Index IDX_AGE_RESPONSAVEL (1)
JSJ> Depth: 1, leaf buckets: 1, nodes: 1072
JSJ> Average data length: 0.00, total dup: 1071, max dup: 1071
JSJ> Fill distribution:
JSJ> 0 - 19% = 0
JSJ> 20 - 39% = 0
JSJ> 40 - 59% = 1
JSJ> 60 - 79% = 0
JSJ> 80 - 99% = 0
JSJ> Index IDX_AGE_USUARIO (2)
JSJ> Depth: 1, leaf buckets: 1, nodes: 1072
JSJ> Average data length: 0.00, total dup: 1071, max dup: 1071
JSJ> Fill distribution:
JSJ> 0 - 19% = 0
JSJ> 20 - 39% = 0
JSJ> 40 - 59% = 1
JSJ> 60 - 79% = 0
JSJ> 80 - 99% = 0
JSJ> Index PK_AGENDA (3)
JSJ> Depth: 2, leaf buckets: 4, nodes: 1072
JSJ> Average data length: 7.11, total dup: 0, max dup: 0
JSJ> Fill distribution:
JSJ> 0 - 19% = 1
JSJ> 20 - 39% = 0
JSJ> 40 - 59% = 0
JSJ> 60 - 79% = 0
JSJ> 80 - 99% = 3
JSJ> AGENDA_RESPONSAVEL (218)
JSJ> Primary pointer page: 1055, Index root page: 1056
JSJ> Data pages: 23, data page slots: 23, average fill: 68%
JSJ> Fill distribution:
JSJ> 0 - 19% = 0
JSJ> 20 - 39% = 1
JSJ> 40 - 59% = 0
JSJ> 60 - 79% = 22
JSJ> 80 - 99% = 0
JSJ> Index IDX_AGR_AGENDA (0)
JSJ> Depth: 2, leaf buckets: 4, nodes: 1122
JSJ> Average data length: 6.79, total dup: 50, max dup: 3
JSJ> Fill distribution:
JSJ> 0 - 19% = 1
JSJ> 20 - 39% = 0
JSJ> 40 - 59% = 0
JSJ> 60 - 79% = 0
JSJ> 80 - 99% = 3
JSJ> Index PK_AGENDA_RESPONSAVEL (1)
JSJ> Depth: 2, leaf buckets: 4, nodes: 1122
JSJ> Average data length: 7.10, total dup: 0, max dup: 0
JSJ> Fill distribution:
JSJ> 0 - 19% = 1
JSJ> 20 - 39% = 0
JSJ> 40 - 59% = 0
JSJ> 60 - 79% = 0
JSJ> 80 - 99% = 3
Mais detalhes sobre a lista de discussão lista