[firebase-br] Estatistica do BD
Carlos H. Cantu
listas em warmboot.com.br
Ter Maio 4 10:50:08 -03 2010
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
JSJ> ______________________________________________
JSJ> FireBase-BR (www.firebase.com.br) - Hospedado em www.locador.com.br
JSJ> Para saber como gerenciar/excluir seu cadastro na lista, use:
JSJ> http://www.firebase.com.br/fb/artigo.php?id=1107
JSJ> Para consultar mensagens antigas: http://firebase.com.br/pesquisa
Mais detalhes sobre a lista de discussão lista