Como está a segurança do seu banco de dados?

Você se preocupa com seus dados? Então, leia o artigo.

Nas consultorias que tenho realizado país afora é comum encontrar nas empresas uma equipe de desenvolvimento, uma equipe de infraestrutura, uma equipe de gestores e ... cadê a equipe de Banco de dados? (Entenda que essa equipe é composta por um ou mais profissionais nas áreas de Administração de Banco de dados, Administração de Dados, Engenharia de Dados, Analistas de Dados, BI dentre outros).

Na maioria das empresas de pequeno porte, e algumas de médio e grande porte, o líder de desenvolvimento é o “cara” do Banco de dados. É a pessoa que mais conhece do negócio e dos dados da sua empresa. Algumas vezes essa pessoa que toma conta dos dados é o parente mais próximo ou a pessoa de confiança do gestor da empresa.

Enfim, independente do profissional responsável pelo banco de dados é importante que o gestor tenha um profissional com qualificação acadêmica específica, consciência e maturidade profissional e, se possível, algumas certificações na ferramenta que é utilizada.

Cuidados que não podem ser dispensados e são necessários para que seu profissional possa garantir a boa saúde e qualidade do seu banco de dados, visando o melhor desempenho do conjunto: Sistema Operacional, Software de Banco de Dados, Modelo de Dados e Performance do Servidor.

Um verdadeiro DBA com um mínimo de experiência sabe definir corretamente os níveis de segurança de acesso dos usuários ao banco de dados, como por exemplo, não passando para a equipe de desenvolvimento um usuário de aplicação com permissão de administrador do banco de dados. Ainda, define corretamente as opções de GRANT para o usuário, sem a necessidade de ceder a permissão como OWNER do banco de dados. Seus dados são sigilosos e/ou preciosos para você? Então, tome cuidado, pois a invasão e roubo de informações quase sempre ocorrem de dentro para fora da empresa por profissionais insatisfeitos.

Em quase todas as empresas que visitei nos últimos anos encontrei falhas básicas na segurança e administração dos usuários de acesso ao banco. Por exemplo: usuários de aplicação com permissão de SYSADMIN, ou ainda, o usuário administrador do banco (sa, root, postgres, SYSADMIN...) como usuário de aplicação, GRANTs desnecessários, as integrações com o LDAP em que os colaboradores que não fazem mais parte do quadro da empresa com seus usuários ativos e com permissão no banco de dados. Loucura total!

O gestor da empresa deve ficar atento as mudanças da empresa e ter cuidado especial com a área de Banco de Dados. Elenco alguns itens que podem auxiliar na sua gestão:

• Faça uma triagem rigorosa de profissionais durante a contratação.
• Realize um processo seletivo interno/externo (Caso seja possível, contrate uma consultoria externa para não haver "tendencionismos" na contratação).
• Pague salários compatíveis com o mercado (gestor, não economize com esse profissional).
• Exija experiência do profissional.
• Realize pontos de controle com essa área.
• Treine seu profissional/equipe.
• Como pré-requisito solicite a assinatura do Termo de Confidencialidade (Documento deve ser assinado pelo profissional).
• Faça um documento de Compliance para sua empresa.

Finalizo comentando que os dados da sua empresa ficam sob a responsabilidade dessa área, cuide para que os dados sejam guardados por profissionais qualificados e éticos.

Agradeço ao amigo Fabiano que ajudo na construção da imagem.

“A tarefa não é tanto ver aquilo que ninguém viu, mas pensar o

que ninguém ainda pensou sobre aquilo que todo mundo vê.”

Arthur Schopenha

A história do Postgres

Dadas as suas características poderosas e avançadas, você pode se perguntar como uma peça valiosa de software passou a ser livre e de código aberto. Tal como acontece com muitos outros projectos de código aberto, a resposta começa na Universidade Berkeley da Califórnia (UCB).

PostgreSQL, originalmente chamado de Postgres, foi criado na UCB por um professor de ciência da computação chamado Michael Stonebraker, que passou a se tornar o CTO da Informix Corporation. Stonebraker começou o desenvolvimento do Postgres em 1986 como um projeto de acompanhamento ao seu antecessor, o Ingres, agora de propriedade da Computer Associates. O nome Postgres, deriva de seu antecessor ("post + Ingres"). Ingres, desenvolvido entre 1977 e 1985, tinha sido um exercício de criação de um sistema de banco de dados de acordo com a teoria clássica de um RDBMS (Relational DataBase Management System). Postgres, desenvolvido entre 1986 e 1994, foi um projeto destinado a abrir novos caminhos em conceitos de banco de dados, tais como a exploração de tecnologias "objeto relacional".

Stonebraker e seus alunos de pós-graduação desenvolveram ativamente o Postgres por oito anos. Durante esse tempo, o Postgres introduziu regras, procedimentos, tipos extensíveis com índices e conceitos objeto-relacional. O Postgres foi posteriormente comercializado para pela Illustra que mais tarde foi comprada pela Informix e integrado no seu Servidor Universal. A Informix foi comprada pela IBM em 2001, por um bilhão de dólares.

Em 1995, dois alunos de Ph.D. do laboratório de Stonebraker, Andrew Yu e Jolly Chen, substituiram um subconjunto  da linguagem de consulta SQL do Postgres. Assim, o Postgres passou a ser chamado de Postgres95.

Em 1996, o Postgres95 foi lançado da academia para uma nova vida no mundo open source, quando um grupo dedicado de desenvolvedores, de fora da UCB viu a grandiosidade do sistema, e se dedicaram ao seu desenvolvimento contínuo. Contribuiram com grande parte de seu tempo, habilidades de desenvolvimento, trabalho e conhecimento técnico; este grupo global de desenvolvimento transformou radicalmente o Postgres. Ao longo dos próximos oito anos, trouxeram consistência e uniformidade ao código, criaram testes de regressão detalhados para a garantia de qualidade, criaram listas de discussão para bug reposrts, foram corrigidos inúmeros bugs, acrescentaram incríveis funcionalidades e, ainda, preencheram várias lacunas tais como documentação para desenvolvedores e usuários.

O resultado desse trabalho resultou em um novo banco de dados que ganhou uma reputação sólida, confiável e estável. Com o início de sua nova vida no mundo open source, com muitos novos recursos e melhorias, o sistema de banco de dados recebeu o nome atual: PostgreSQL. ( "Postgres" ainda é usado como um apelido e fácil de pronunciar.)

O PostgreSQL começou na versão 6.0. Com a ajuda de centenas de desenvolvedores em torno de todo o mundo, o sistema foi modificado e melhorado em quase todas as áreas. Ao longo dos próximos quatro anos (versões 6.0 e 7.0), grandes melhorias e novos recursos foram feitas, tais como:

- Multiversion Concurrency Control (MVCC). Bloqueio em nível de tabela foi substituído por um sistema de controle de concorrência multiversionado sofisticado, que permite que a atividade de leitura continue lendo dados consistentes durante a atividade de escrita e permite backups on-line (quentes), enquanto o banco de dados está em execução.

- Recursos do SQL importantes. Muitas melhorias foram feitas no SQL incluindo subconsultas, padrões, restrições, chaves primárias, chaves estrangeiras, identificadores entre aspas, coerção literal do tipo string, conversão de tipos e gravação de binário e hexadecimal entre outros.

- Melhorias nos tipos internos. Novos tipos nativos foram adicionados, incluindo uma ampla gama de tipos de date/time e tipos geométricos.

- Velocidade. Grandes aumentos de velocidade e desempenho na ordem dos 20-40% foram feitas, e backend tempo de arranque foi reduzida em 80%.

Os quatro anos seguintes (versões 7.0 a 7.4) trouxe o Write-Ahead Log (WAL), esquemas SQL, prepared queries, outer joins, consultas complexas,  associação de sintaxe SQL92, toast, suporte IPv6, full-text indexação, auto-vacuum, linguagens procedurais Perl / Python / TCL, melhor suporte SSL, revisão no otimizador, informações de estatísticas do banco de dados, mais segurança, funções de tabela e os aperfeiçoamentos de logs e melhorias significativas de velocidade, entre outras coisas. 

Hoje, a base de usuários do PostgreSQL é maior do que nunca e inclui um grupo considerável de grandes empresas que o utilizam em ambientes críticos. Algumas dessas empresas como a Afilias e Fujitsu fizeram contribuições significativas para o desenvolvimento do PostgreSQL.

Muitas organizações, agências governamentais e empresas utilizam o PostgreSQL. Você vai encontrar instalações em ADP, CISCO, NTT Data, NOAA, Research In Motion, O Serviço Florestal dos EUA e The American Chemical Society. Hoje, é raro encontrar uma grande empresa ou agência governamental que não está usando PostgreSQL em pelo menos um departamento.

Se alguma vez houve um tempo para você considerar seriamente o uso do PostgreSQL para alimentar a sua aplicação ou negócios, seria agora.

Adaptação: Anderson Abreu

Fonte: https://www.postgresql.org/about/history/

A Importância de Planejar e Gerenciar

Meu objetivo neste artigo é explanar um pouco sobre a importância do planejamento e gerenciamento de todas as atividades que executamos no dia-a-dia profissional/familiar e desenvolver a consciência de elaboração de planejamentos com maior atenção e com base em informações, utilizando para isso técnicas e meios que auxiliem no sucesso e consequentemente em um maior índice de acerto.

Inovar está diretamente ligado ao conceito de planejar, ou seja, criar algo novo ou melhorar algo já produzido deveria envolver um planejamento, um projeto.

Cada vez mais, o volume de informações disponíveis tem crescido de forma exponencial fazendo com que a a sua gestão seja uma tarefa mais complicada e imprescindível. Quando não há planejamento torna-se difícil a tomada de decisão. Imagine um barramento de serviço coletando informações de mídias sociais e extraindo informações relativas aos atletas dos jogos olímpicos e comparando com o perfil pessoal de cada atleta, exportando um gráfico com o nome dos atletas e a quantidade de mensagens relacionadas.

Diante de um século que está cedento por informação e necessita cada vez mais de agilidade na troca de dados, o governo e as empresas públicas e privadas são o alvo de empresas fornecedoras de serviços e produtos que precisam planejar com qualidade, cuidando para que não haja alterações de escopo, prazo e custo e que sejam cobrados valores justos. Afinal, quem paga a conta somos nós!

Não obstante, é comum observarmos alterações de escopo e prazo nos projetos de TI por diversos motivos como:

- falta de gestão;

- falta de preparo dos profissionais envolvidos;

- falta de clareza no entendimento do negócio;

- projetos mal escritos, "assassinos da língua portuguesa e das regras gramaticais" e

- dentre outras...

Alguns projetos na área de TI não alcançam seus objetivos e consequentemente o sucesso por causa de problemas relacionados ao seu gerenciamento.

Precisamos Planejar e gerenciar, conforme o PMBOK, gerenciamento de projetos:

                                 "é a aplicação de conhecimento, habilidades, ferramentas e

                                   técnicas às atividades do projeto a fim de atender aos seus 

                                   requisitos" e escopo do projeto é o "trabalho a ser realizado 

                                   para entregar um produto, serviço ou resultado com as 

                                  características e funções especificadas".

Fica um pergunta: Será que estamos fazendo o nosso trabalho com qualidade, planejamento e gerenciamento, respeitando normas e regras?

Portanto, planejar é imprescindível e, segundo Vieira:

                                  "o processo de planejamento define e refina os objetivos e as 

                                   escolhas das melhores alternativas de ação para atingir os 

                                   objetivos propostos pelo projeto".

Com a prática rotineira e sistêmica do planejamento em projetos de TI podemos identificar benefícios significativos, criar uma série de vantagens competitivas agregadas ao negócio e identificar novas oportunidades de negócio.

Alguns benefícios são:

- Soluções com tecnologia mais atual e maior qualidade;

- Redução de gastos desnecessários, duração do projeto e qualidade superiores;

- Maior rentabilidade;

- Reutilização de módulos em outros projetos e

- Clientes satisfeitos.

Lembre-se: Execute sua atividade com qualidade pois o retrabalho torna a atividade muito mais cara.

Um conjunto de metodologias, normas e ferramentas foram pensadas para nos auxiliar e minimizar as dificuldades na hora do planejamento, como por exemplo: Gestão da Informação, Sistemas de Informação, Pesquisas, Questionários, Opinião Especializada, Modelagem de Negócios, Benchmarking, ITIL, Análise de Pontos de Função e Lições Aprendidas.

Gestão da informação é uma das primeiras atividades a ser executada quando não há controle na elaboração de planejamento. Veja: 

                                              "é o processo que consiste nas atividades de busca, 

                                                classificação, processamento, armazenamento e

                                                disseminação de informações independentemente do 

                                                formato ou meio que se encontra."

                                                                                                                       CRUZ (2002)

Planejar e gerenciar não é uma tarefa do outro mundo, se você não tem conhecimento profundo sobre as questões explanadas anteriormente, utilize uma planilha eletrônica e registre suas atividades, mensure tempo, pense nos custos envolvidos.

A prática fará, com o tempo, a excelência!

Não se esqueça, a base de tudo é a disposição em registrar, armazenar e distribuir tais informações. A partir desse momento é possível criar uma base histórica e através de estatísticas, comparações e análises chegar a previsões mais fundamentadas.

REFERÊNCIAS

Project Management Body of Knowledge - PMOBK Guide. 3ª Edição 2004.

VIEIRA, Marconi Fábio. Gerenciamento de Projetos de Tecnologia da Informação. 2ª Edição.

HARRINGTON, H. J. Aperfeiçoando Processos Empresariais. Makron Books. 1ª Edição 1993.

TURBAN, Efraim, RAINER JR., R. Kelly. Administração de Tecnologia da Informação. 3ª Edição.

Indexação MS SQL Server

Olá Pessoal,

Hoje tive a necessidade de criar um procedimento para reindexar todos os índices de todas as tabelas de um determinado banco de dados, de forma automática, ou seja, quando uma tabela for criada, o procedimento inclui automaticamente a nova estrutura para reindexação, caso existam índices.

Criei um JOB para garantir o processamento diário.

Segue a procedure:

CREATE PROCEDURE SP_REINDEXARTABELAS ( @Database_input VARCHAR(255) )
AS
BEGIN
DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT 

SET @fillfactor = 90

DECLARE DatabaseCursor CURSOR FOR  
SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN  ('master','msdb','tempdb','model','distribution') and name = @database_input ORDER BY 1  
OPEN DatabaseCursor  

FETCH NEXT FROM DatabaseCursor INTO @Database  

WHILE @@FETCH_STATUS = 0  
BEGIN  
  SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT '''' + table_name + '''' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''   
EXEC (@cmd)  
OPEN TableCursor   

FETCH NEXT FROM TableCursor INTO @Table   
WHILE @@FETCH_STATUS = 0   
BEGIN   
IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9) 
BEGIN
SET @cmd = 'use '+@database_input +';
ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ');'
EXEC (@cmd) 
END
ELSE
BEGIN
SET @cmd = 'use '+@database_input +';
DBCC DBREINDEX('+@Table+','' '','+ convert(char(2),@fillfactor)+'); '
EXEC (@cmd)
END
FETCH NEXT FROM TableCursor INTO @Table   
END   
CLOSE TableCursor   
DEALLOCATE TableCursor  
FETCH NEXT FROM DatabaseCursor INTO @Database  
END  
CLOSE DatabaseCursor   
DEALLOCATE DatabaseCursor
END

Para executar o procedimento:

EXEC SP_REINDEXARTABELAS 'nome_banco'

Problemas com o xp_sendmail

Hoje, deparei com um problema corriqueiro em um dos servidores SQL Server 2005 de um cliente.

xp_sendmail: failed with mail error 0x80040115

xp_sendmail: failed with mail error 0x80040000

Vamos lá!

Como ninguém sabe tudo precisei recorrer ao Books online da Microsoft e encontrei as referências [1] [2] que auxiliaram no entendimento e consequentemente na solução do problema.

[1] https://support.microsoft.com/en-us/kb/555180

[2] https://support.microsoft.com/en-us/kb/833045

Funcionamento

O SQL Server implementa o serviço de SQL Mail. E para cada sessão aberta do cliente os componentes MAPI façam o logon no servidor de email, conforme a configuração estipulada no profile do SQL Mail.

Quando são identificadas falhas no envio das mensagens é comum que os DBAs menos atualizados reiniciem o serviço do SQL Server, sem necessidade. É claro que após a reinicialização do serviço vai funcionar. Como ninguém sabe tudo precisei recorrer ao Books online da Microsoft e encontrei as referências [1] [2] que auxiliaram no entendimento e consequentemente na solução do problema.

Esse recurso que utilizei será removido em uma versão futura do Microsoft SQL Server. Evite usar esse recurso em desenvolvimentos novos e planeje modificar os aplicativos que atualmente o utilizam para os novos recursos implementados pela Microsoft no SQL Server.

Solução simples:

exec master.dbo.xp_stopmail GO EXEC master.dbo.xp_sendmail @recipients=’andersonabreu@gmail.com’, @message=’Corpo do email teste’, @subject=’Assunto: teste de envio de email’

Ao realizar o teste, houve um momento que foi necessário iniciar o serviço.

Se você usar xp_startmail para iniciar as sessões de e-mail, poderá fornecer opcionalmente seu nome de login e senha para que não tenha de digitá-los no prompt de comando. Entretanto, o SQL Mail não será executado em uma sessão MAPI existente se houver uma em execução.

exec master.dbo.xp_startmail GO EXEC master.dbo.xp_sendmail @recipients=’andersonabreu@gmail.com’, @message=’Corpo do email teste’, @subject=’Assunto: teste de envio de email’

Indexação e Fragmentação (SQLServer)

Índices

Hoje, abordarei sobre um tema que para muitos parece ser um mito e algo temoroso. Contudo, tentarei abordar de forma simples

Não é difícil nos depararmos com estruturas de tabelas mal definidas, com tipos de dados que podem ser melhor definidos, tabelas sem índices e consultas SQLs mal escritas.

Um bom time de DBAs tem como uma de suas premissas a execução de procedimentos rotineiras, a maioria deles agendado, para uma boa saúde do banco de dados. Um destes procedimentos é a indexação/desfragmentação de índices.

Os índices são utilizados para potencializar as operações de buscas em banco de dados. Índices bem definidos podem melhorar de forma exponencial o acesso aos dados gravados em disco, reduzindo drasticamente a varredura desnecessária dentro do disco e da estrutura de arquivos e páginas dos blocos de índice.

Recentemente, fui solicitado em uma consultoria para melhorar o desempenho de um GED.

Vamos lá!

As atividades foram executadas em um servidor com Microsoft SQL Server 2008 R2.

Procedimentos

Resolvi analisar o percentual de fragmentação das tabelas.

Observei diversos índices com um alto percentual de fragmentação. Para analisar a fragmentação criei uma stored procedure para auxiliar nos dados, conforme estrutura a seguir:

Stored Procedure utilizada para analisar o percentual de fragmentação:

CREATE PROCEDURE SP_ESTATISTICA_FRAGMENTACAO AS BEGIN                 CREATE TABLE #fragmentacao(                                object_id integer,                                tabela VARCHAR(128),                                index_id integer,                                nome_indice VARCHAR(500),                                percentual_fragmentacao numeric(18,6))                 DECLARE @Tabela VARCHAR(128)                 DECLARE @catalogo VARCHAR(128)                 DECLARE Tabelas CURSOR FAST_FORWARD FOR                                SELECT TABLE_CATALOG, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'                 OPEN Tabelas                 FETCH NEXT FROM Tabelas INTO @catalogo, @Tabela                 WHILE @@FETCH_STATUS = 0                 BEGIN                                INSERT INTO #fragmentacao                                SELECT b.object_id, @tabela, a.index_id, b.name nome_indice, avg_fragmentation_in_percent               FROM sys.dm_db_index_physical_stats (DB_ID(@catalogo), OBJECT_ID(@tabela), NULL, NULL, NULL) AS a                                 JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id                                                               FETCH NEXT FROM Tabelas INTO @catalogo, @Tabela                    END                 CLOSE Tabelas                 DEALLOCATE Tabelas        SELECT * FROM #fragmentacao        DROP TABLE #fragmentacao END exec SP_ESTATISTICA_FRAGMENTACAO

Resolvi aprofundar um pouco mais nas consultas e recuperei para cada tabela o espaço consumido por Dados, Índices, Reservado, Não utilizado e o Número de tuplas.

Para isso fiz a seguinte view:

View para análise de dados das tabelas:

CREATE VIEW VW_ESTATISTICA_TABELA AS SELECT OBJECT_NAME(object_id) As Tabela, Rows As Linhas,     SUM(Total_Pages * 8) As Reservado,     SUM(CASE WHEN Index_ID > 1 THEN 0 ELSE Data_Pages * 8 END) As Dados,         SUM(Used_Pages * 8) -         SUM(CASE WHEN Index_ID > 1 THEN 0 ELSE Data_Pages * 8 END) As Indice,     SUM((Total_Pages - Used_Pages) * 8) As NaoUtilizado FROM     sys.partitions As P     INNER JOIN sys.allocation_units As A ON P.hobt_id = A.container_id where OBJECT_NAME(object_id) not like 'sys%' and OBJECT_NAME(object_id) not like 'ifts%' and OBJECT_NAME(object_id) not like 'queue%' and OBJECT_NAME(object_id) not like 'fulltext%' and OBJECT_NAME(object_id) not like 'frag%' and OBJECT_NAME(object_id) not like 'file%' GROUP BY OBJECT_NAME(object_id), Rows SELECT * FROM VW_ESTATISTICA_TABELA ORDER BY linhas desc

Feitas as devidas verificações passei para o passo seguinte que foi gerar os dados do antes da desfragmentação e reindexação das tabelas.

Em algumas tabelas só a desfragmentação não foi suficiente e foi necessária a reindexação.

Veja os códigos utilizados:

-- COMANDO UTILIZADO PARA REINDEXAR A TABELA DBCC DBREINDEX ('nome_tabela'); -- COMANDO PARA RECONSTRUIR O INDICIE ALTER INDEX nome_indice ON nome_tabela REBUILD; -- COMANDO PARA DESFRAGMENTAR TODOS OS INDICES DA TABELA ALTER INDEX ALL ON ACS_CIDADE REORGANIZE ;   -- COMANDO PARA DESFRAGMENTAR INDIVIDUALMENTE CADA ÍNDICE ALTER INDEX idx_acsCidade_idEstado ON ACS_CIDADE REORGANIZE ;

Monitoramento PostgreSQL

Monitoramento PostgreSQL

Sabemos que existem diferentes formas de monitorar as atividades no SGBD PostgreSQL.

Recentemente, fui solicitado em uma consultoria de PostgreSQL, para uma aplicação escrita em java utilizando hibernate (com muuuiiiittttooooos HQLs), foi solicitado que verificasse os possíveis pontos de gargalo no servidor de banco de dados.

Afinal, “o problema sempre (ou quase) é no banco de dados” (Pensamento popular quando o banco de dados “aparentemente” está lento).

Vamos lá!

Verifiquei os parâmetros de configuração do postgresql.conf em conjunto com os parâmetros de configuração do Sistema Operacional (Redhat 7).

“Excelentes” DBAs já haviam feito um “excelente” trabalho de tuning e comprovei que os parâmetros de configuração estavam ajustados para a melhor performance de utilização do SGBD, pelo menos para aquela realidade.

Procedimentos

Investigando mais a fundo o motivo, identificamos que algumas transações, oriundas da aplicação, estavam dentro de transações que aguardavam muito tempo por uma ação do usuário, isso quando não perdiam a sessão e a aplicação simplesmente não fechava a conexão. Com isso, foi inevitável o aumento de requisições para novas conexões ativas no SGBD.

Bingo! Problema na aplicação que foi mal escrita.

Os procedimentos a seguir foram realizados em um servidor Postgres 9.3.10, em Julho/2016, em dois dias de monitoramento.

É claro que existem outras formas de realizar esse monitoramento!!!

Ao consultar a view pg_stat_activity, observei que haviam muitas requisições cujo estado era idle_in_transaction.

O que precisava ser feito? Capturar essas queries e reportar para que a equipe de desenvolvimento pudesse agir pontualmente e buscar uma solução definitiva para o problema.

Consulta utilizada para buscar atividades no SGBD com o estado idle_in_transaction:

SELECT pid, datname, usename, client_addr, query_start, state_change, state, query  FROM pg_stat_activity  WHERE state = 'idle_in_transaction';

Criação de estrutura no banco de dados para coleta dos dados:

-bash-4.1$ psql Password: psql.bin (9.2.4) Type "help" for help. [?1034hpostgres=# postgres=# postgres=# postgres=# postgres=# CREATE ROLE usr_monitoramento LOGIN ENCRYPTED PASSWORD 'md53fed9f02a9f95c16b62bf2f718ce74b0' VALID UNTIL 'infinity'; CREATE ROLE postgres=# postgres=# postgres=# postgres=# CREATE DATABASE bd_monitoramento WITH ENCODING='UTF8' OWNER=usr_monitoramento CONNECTION LIMIT=-1; CREATE DATABASE postgres=# postgres=# postgres=# postgres=# CREATE TABLE atividades ( postgres=#  pid integer, postgres=#  datname name, postgres=#  usename name, postgres=#  client_addr inet, postgres=#  query_start timestamp with time zone, postgres=#  state_change timestamp with time zone, postgres=#  state text, postgres=#  query text postgres=#); CREATE TABLE postgres=# postgres=# postgres=# postgres=# ALTER TABLE atividades OWNER TO usr_monitoramento; ALTER TABLE postgres=# postgres=# postgres=# postgres=# CREATE TABLE bloqueio ( postgres=#   hora_atual timestamp with time zone, postgres=#   pid_bloqueado integer, postgres=#   hora_inicio_bloqueada timestamp with time zone, postgres=#   hora_mudanca_estado_bloqueada timestamp with time zone, postgres=#   usuario_bloqueado name, postgres=#   querie_bloqueada text, postgres=#   aplicacao_bloqueada text, postgres=#   pid_bloqueio integer, postgres=#   hora_inicio_bloqueio timestamp with time zone, postgres=#   hora_mudanca_estado_bloqueio timestamp with time zone, postgres=#   usuario_bloqueio name, postgres=#   querie_bloqueio text, postgres=#   aplicacao_bloqueio text postgres=#); CREATE TABLE postgres=# postgres=# postgres=# postgres=# ALTER TABLE bloqueio  OWNER TO usr_monitoramento; ALTER TABLE

Com a consulta a seguir, pude extrair as queries mais frequentes com estado idle_in_transaction:

SELECT pid, query, COUNT(0) qtde INTO atividades_agrupadas FROM atividades GROUP BY pid, query ORDER BY 3 DESC;

Consulta para capturar as possíveis requisições que estão bloqueando e que são bloqueadas:

SELECT now() hora_atual, atividade_bloqueada.pid pid_bloqueado, atividade_bloqueada.query_start hora_incio_bloqueada, atividade_bloqueada.state_change hora_mudanca_estado_bloqueada, atividade_bloqueada.usename usuario_bloqueado, atividade_bloqueada.query querie_bloqueada, atividade_bloqueada.application_name aplicacao_bloqueada, bloqueio.pid pid_bloqueio, atividade_bloqueio.query_start hora_incio_bloqueio, atividade_bloqueio.state_change hora_mudanca_estado_bloqueio, atividade_bloqueio.usename usuario_bloqueio, atividade_bloqueio.query querie_bloqueio, atividade_bloqueio.application_name aplicacao_bloqueio FROM  pg_catalog.pg_locks bloqueados JOIN pg_catalog.pg_stat_activity atividade_bloqueada  ON atividade_bloqueada.pid = bloqueados.pid JOIN pg_catalog.pg_locks bloqueio ON bloqueio.locktype = bloqueados.locktype AND bloqueio.DATABASE IS NOT DISTINCT FROM bloqueados.DATABASE AND bloqueio.relation IS NOT DISTINCT FROM bloqueados.relation AND bloqueio.page IS NOT DISTINCT FROM bloqueados.page AND bloqueio.tuple IS NOT DISTINCT FROM bloqueados.tuple AND bloqueio.virtualxid IS NOT DISTINCT FROM bloqueados.virtualxid AND bloqueio.transactionid IS NOT DISTINCT FROM bloqueados.transactionid AND bloqueio.classid IS NOT DISTINCT FROM bloqueados.classid AND bloqueio.objid IS NOT DISTINCT FROM bloqueados.objid AND bloqueio.objsubid IS NOT DISTINCT FROM bloqueados.objsubid AND bloqueio.pid != bloqueados.pid JOIN pg_catalog.pg_stat_activity atividade_bloqueio ON atividade_bloqueio.pid = bloqueio.pid WHERE NOT bloqueados.granted;

Criação do arquivo monitoramento.sh:

#!/bin/sh # Coleta de dados PG_STAT_ACTIVITY e BLOQUEIO # Data de criacao: 2016-07-01 # Criado por: Anderson Abreu # E-mail: andersonabreu@gmail.com export PGPASSWORD="edb2013" INICIO="Inicio: " FIM="Final: " D=$(date '+%d/%m/%Y %R:%S') echo $INICIO$D >> /tmp/log_atividades.txt /opt/PostgreSQL/9.2/bin/psql -U postgres -w -d bd_monitoramento -c "insert into atividades select pid, datname, usename, client_addr, query_start, state_change, state, query from pg_stat_activity where state = 'idle_in_transaction';" >> /tmp/log_atividades.txt /opt/PostgreSQL/9.2/bin/psql -U postgres -w -d bd_monitoramento -c "insert into bloqueio SELECT now() hora_atual, atividade_bloqueada.pid pid_bloqueado, atividade_bloqueada.query_start hora_incio_bloqueada, atividade_bloqueada.state_change hora_mudanca_estado_bloqueada, atividade_bloqueada.usename usuario_bloqueado, atividade_bloqueada.query querie_bloqueada, atividade_bloqueada.application_name aplicacao_bloqueada, bloqueio.pid pid_bloqueio, atividade_bloqueio.query_start hora_incio_bloqueio, atividade_bloqueio.state_change hora_mudanca_estado_bloqueio, atividade_bloqueio.usename usuario_bloqueio, atividade_bloqueio.query querie_bloqueio, atividade_bloqueio.application_name aplicacao_bloqueio FROM  pg_catalog.pg_locks bloqueados JOIN pg_catalog.pg_stat_activity atividade_bloqueada  ON atividade_bloqueada.pid = bloqueados.pid JOIN pg_catalog.pg_locks bloqueio         ON bloqueio.locktype = bloqueados.locktype         AND bloqueio.DATABASE IS NOT DISTINCT FROM bloqueados.DATABASE         AND bloqueio.relation IS NOT DISTINCT FROM bloqueados.relation         AND bloqueio.page IS NOT DISTINCT FROM bloqueados.page         AND bloqueio.tuple IS NOT DISTINCT FROM bloqueados.tuple         AND bloqueio.virtualxid IS NOT DISTINCT FROM bloqueados.virtualxid         AND bloqueio.transactionid IS NOT DISTINCT FROM bloqueados.transactionid         AND bloqueio.classid IS NOT DISTINCT FROM bloqueados.classid         AND bloqueio.objid IS NOT DISTINCT FROM bloqueados.objid         AND bloqueio.objsubid IS NOT DISTINCT FROM bloqueados.objsubid         AND bloqueio.pid != bloqueados.pid JOIN pg_catalog.pg_stat_activity atividade_bloqueio ON atividade_bloqueio.pid = bloqueio.pid WHERE NOT bloqueados.granted;" >> /tmp/log_atividades.txt D=$(date '+%d/%m/%Y %R:%S') echo $FIM$D >> /tmp/log_atividades.txt

Agendamento da rotina:

Agendei a execução para ocorrer a cada minuto para termos o máximo de dados e suas possíveis repetições.

# crontab -e *       *       *       *       *       /tmp/monitoramento.sh

Report para equipe de desenvolvimento

Feita a coleta de dados, foi realizada a exportação dos dados para um arquivo no formato CSV para que fosse possível a análise da equipe de desenvolvimento.

copy atividades_agrupadas to '/tmp/atividades_agrupadas_idle_in_transaction.csv' DELIMITER ';' CSV HEADER; copy bloqueio to '/tmp/bloqueio.csv' DELIMITER ';' CSV HEADER;