Como Monitorar o Banco de Dados? Existem Ferramentas para Isso?

Me mandaram uma dúvida por correio e decidi comentar no blog. Como monitorar o servidor de banco de dados PostgreSQL? Existem ferramentas para isso?

Bom, existem três maneiras de se monitorar o SGBD. Postgresql ou de outro tipo:

1 - Utilizar uma ferramenta de outros;

2 - Empregar uma ferramenta desenvolvida por você;

3 - Utilizar recursos oferecidos pelo SGBD.

Consideremos seus prós e contras:

1 - Utilizar uma ferramenta de outros;

A ferramenta livre que você deseja para monitorar servidores se chama ZABBIX. Não a conheço em profundidade, mas é ótima, bastante utilizada!

Figura 1 - Zabbix em ação

Certamente existem outras proprietárias de software houses, e também de consultorias, que podem ser muito boas!

2 - Empregar uma ferramenta desenvolvida por você;

Você tem capacidade, mas nem sempre o tempo para adotar esta opção. Mas se você puder tentar, é uma experiência  sem igual! 

Vale a pena reutilizar classes e scripts do DBA, códigos de software livre, tudo o que estiver à mão.

Se sua ferramenta for boa e você puder, existe possibilidade de compartilhar com a comunidade.

3 - Utilizar recursos oferecidos pelo SGBD.

O monitoramento do banco sem ferramentas específicas pode ser feito de duas formas principais.

- Configure a geração do log e leia o registro das operações realizadas e dos tempos de resposta. Você vai precisar de conhecimento sobre a configuração e funcionamento do log do Postgresql;

- Consultas utilizando SELECT às tabelas dos metadados do sistema. É possível saber, por exemplo, quantas conexões estão abertas e que comandos estão sendo executados.Você vai precisar de conhecimentos sobre as tabelas de sistema do Postgresql para começar. 

Considerações Finais

Todas estas são boas opções! Adote inicialmente a que for a mais prática e realista, com o maior custo-benefício. Na prática, o que recomendo é adotar mais de uma forma de monitoramento!!!

Edição de SQL e Funções no PSQL

O psql é a principal interface dos desenvolvedores com o PostgreSQL. No entanto, editar códigos no psql pode ser uma tarefa onerosa. As consultas e funções podem ser extensas e o trabalho se tornar cansativo e improdutivo. Existem algumas opções que podem ajudar a trabalhar melhor com os códigos, sem precisar sair do PSQL, que abordamos resumidamente aqui.

- Executando arquivos TXT salvos previamente.

A maneira que mais utilizo para trabalhar com funções e SQL é executar arquivos txt salvos previamente. Gosto de ter scripts para as necessidades básicas em seus respectivos lugares, que possam ser reutilizados, e tem sido bem útil trabalhar desta forma. Edite o seu SQL no editor que achar melhor e salve-o. Para executa-lo, utilize a sintaxe:

\i (nome do arquivo como  código SQL)

- Editar código SQL usando o Editor Padrão

Neste caso, o sistema abre o editor padrão definido para o postgresql. Caso não exista um editor definido, o postgres perguntará, dentre os disponíveis, qual você deseja utilizar. No meu caso, utilizo o nano (http://www.nano-editor.org/). É um editor bem simples e fácil de usar, que apresenta boas teclas de atalho.

Digite:

\e (ou \edit)

O sistema abre a tela do editor para inserir e editar seu texto, permitindo rolar as páginas e manter o SQL sem problemas. É possível salvar o script para reutilização. Para apenas executar, sem salvar, basta sair do editor. No caso do NANO, teclando CONTROL+X.

- Editar nova função no editor.

Ao se digitar \ef, o psql abre o Editor predeterminado, apresentando um "esqueleto de função" para edição. Basta sair teclando CONTROL+X para criar a função.

- Editar função existente


Neste caso, utilize a sintaxe:

\ef (nome da função a editar)

Se você esqueceu o nome das funções que deseja editar, pode adaptar a consulta abaixo para descobrir.

SELECT proname, pronamespace, proowner FROM PG_PROC;

Agora é editar seus scripts, consultas e funções de dentro do psql!

OOPS! Alterando o Editor Padrão!

Já ia me esquecendo! Altere o editor padrão utilizando a sintaxe abaixo:

\set PSQL_EDITOR (caminho do editor)

Visão Geral da Arquitetura do PostgreSQL!

Adorei esta imagem, com a visão geral da arquitetura do PostgreSQL. O sítio original é este aqui.

PGTUNE: Otimize a configuração do PostgreSQL!

Tuning de banco de dados não é exatamente uma tarefa fácil. É necessário conhecer os rudimentos de bancos de dados, o SGBD utilizado em profundidade, linguagem SQL (ou outra, dependendo do SGBD), além de saber informações precisas sobre os bancos de dados em si e as aplicações que os utilizam.

São muitas variáveis em jogo e o processo de otimização raramente possibilita a quantidade de testes realmente necessária em virtude do tempo ser exíguo, e os sistemas não poderem parar. O PGTUNE é um script python, desenvolvido por Gregory Smith, que atua como ferramenta complementar para o tuning de bancos de dados postgresql, mais especificamente, sugerindo alterações no complexo arquivo postgresql.conf visando um melhor desempenho.

Neste post, vamos apresentar o funcionamento básico do pgtune e elencar pontos fortes e limitações.

* Instalação

O PGTUNE demanda a instalação do python, a qual é bastante simples para linux, windows e MAC (além de máquina virtual do java, .NET, etc.).

Assumindo que você já possui o postgres e o python instalados, basta acessar o sítio do PGTUNE, baixar o arquivo compactado e descompactá-lo.

Para este post utilizamos:
- pgtune versão 0.9.3
- postgresql versão 9.2.1
- python versão 2.6.5


* Utilização

Abra o prompt de comando e entre na pasta descompactada do pgtune.

O comando abaixo aciona o pgtune, passando como parâmetros o arquivo postgresql.conf do postgres e o arquivo de saída (no caso, psql.conf), gerado com sugestões para o tuning do banco de dados.

python pgtune -i /etc/postgresql/9.2/main/postgresql.conf -o psql.conf

O resultado da execução aparece na figura abaixo, com a listagem do arquivo de saída:

Caso deseje testar as alterações, altere o postgresql.conf e reinicie o servidor do banco.

Além dos arquivos de entrada e saída, o script apresenta outros recursos realmente interessantes, através de parâmetros (opcionais) de execução:

* -M ou –memory: Utilizar este parâmetro para fornecer a memória do servidor. Caso não esteja especificado, o pgtune tentará detectar a memória da máquina de execução do script, assumindo que é a máquina servidora.
* -T ou –type: Especifica o tipo de base de dados. As opções aceitas são:
- DW - Grandes massas de dados com poucas alterações mas com consultas extremamente complexas
- OLTP - Sistema tradicional de processamento de transações
- Web - Sistema web, com grande número de acessos concorrentes
- Mixed - Sistema com características intermediárias em relação aos demais tipos.
- Desktop - Sistema monousuário
* -c or –connections: Especifica o máximo de conexões desejado. Este é o parâmetro mais relevante em sistemas com grande número de usuários.

Exemplos adicionais com os parâmetros:

python pgtune -i /etc/postgresql/9.2/main/postgresql.conf -o psql.conf -M 500000
 

python pgtune -i /etc/postgresql/9.2/main/postgresql.conf -o psql.conf -c 1000
 

pgtune-0.9.3$ python pgtune -i /etc/postgresql/9.2/main/postgresql.conf -o psql.conf -M 500000 -c1000

* Considerações finais

O pgtune mostrou que pode ser utilizado sem problemas como ferramenta complementar de tuning de banco de dados. Recomendo que antes de qualquer alteração no postgresql.conf, que se faça testes e backup do arquivo antigo.

Abaixo, listo pontos fortes e limitações:

Vantagens:
- Facilidade de instalação e utilização
- Ser multiplataforma, por utilizar python
- Regras de alteração de valores são acessíveis dentro do script
- Facilidade de interpretação de resultados
- Pode ser utilizado sem interromper o funcionamento do servidor de banco de dados
- Não altera o ambiente de produção, deixando para o DBA a opção de aceitar ou não as alterações sugeridas

Limitações:
- Não faz testes aprofundados no ambiente que indiquem a eficácia da otimização
- Funcionar através de cálculos sobre regras relacionadas à configuração, e não realiza testes de simulação comprobatórios
- Não considera outros fatores além da configuração do postgresql.conf
- Não existe uma forma clara de distinguir entre os diversos tipos de sistema, e um servidor pode ter mais de um banco de dados com tipos diferentes, o que invalidaria a configuração com o parâmetro -T.
- O parâmetro -M não aceita abreviações como 4gb, 5000mb.

Teste você mesmo e me diga o que achou!

APGDIFF: Ferramenta Mediana que pode ser Útil!

Basta um momento de descuido para termos várias versões de um mesmo banco de dados em funcionamento. Identificar os pontos de alteração em esquemas de bancos de dados manualmente é mais do que cansativo: é arriscado.

Para solucionar estes problemas, existem várias ferramentas para comparação entre esquemas de banco de dados, dentre elas a "Another PostgreSQL Diff Tool", também chamada (apgdiff). É uma ferramenta livre que apresenta versão gratuita na web e atualmente está em sua versão 2.4. 

Neste post é mostrado o funcionamento básico da versão web da ferramenta, que é bem simples, e são colocadas as primeiras impressões na sua utilização.

* Funcionamento

A operação da ferramenta é bem simples:
- Inicialmente, acesse o sítio da ferramenta;
- Faça o backup dos bancos de dados que se deseja comparar. Com o utilitário pg_dump a sintaxe poderia ser: pg_dump -U postgres -v -f teste_atual.txt postgres (extraindo o banco de dados postgres para o arquivo txt teste_atual.txt)
- Uma vez que tenha extraído o backup dos dois bancos de dados a comparar, acione a opção "Create Diff online" e faça o upload dos arquivos de backup obtidos
- Acione a opção de comparação de esquemas. Abaixo, colocamos um exemplo passo a passo.

Tela inicial

Inclusão de arquivos para comparação

Diferença entre os esquemas

* Primeiras impressões

O site foi bastante rápido em suas análises, mas não detectou todas as poucas alterações realizadas.

No teste foi acrescentado um novo usuário, o que não foi detectado pela ferramenta. A chave primária da tabela incluída também não foi encontrada pela apgdiff. A ferramenta também apresentou como diferente uma chave primária que na verdade estava igual em ambos os esquemas.

A primeira impressão é que a apgdiff é interessante, mas está longe de ser perfeita. A análise dos backups mostrou que podem ser deixados de lado detalhes importantes, o que não tira sua importância como potencial ferramenta auxiliar.

O trabalho do DBA, ajudado por scripts próprios e outras ferramentas, seguramente pode se beneficiar dos recursos da apgdiff. Mas como qualquer ferramenta, esta apresenta limitações, algumas das quais identificadas neste post.

Gere Automaticamente seus Comandos GRANT e REVOKE!

Os comandos GRANT e REVOKE concedem e retiram permissões de acesso dos usuários aos objetos do banco de dados relativas à inserção, exclusão e alteração de dados, entre outras possibilidades. Neste post, vamos gerar automaticamente comandos GRANT e REVOKE utilizando SQL. Este tipo de procedimento não é muito comum porque ambos os comandos apresentam sintaxes simples que permitem a concessão de acessos sem a necessidade de automação.

Para construir scripts para automatizar a concessão e revogação destes acessos, o primeiro passo é saber quais são os usuários cadastrados no servidor.

1. Quais são os usuários cadastrados?

Para conceder ou revogar privilégios aos usuários, é interessante saber quantos e quais usuários estão cadastrados no seu SGBD, e uma consulta a PG_USER .

select * from pg_user;

usename  | usesysid | usecreatedb | usesuper | usecatupd |  passwd  | valuntil | useconfig

----------+----------+-------------+----------+-----------+----------+----------+-----------

postgres |       10 | t           | t        | t         | ******** |          |

gisuser  |    17141 | f           | f        | f         | ******** |          |

A próxima etapa é identificar as tabelas para as quais será concedido acesso.


2. Quais são as tabelas criadas no banco?



Uma consulta aos metadados de PG_TABLES retorna o nome das tabelas utilizadas. Observe que na consulta, selecionamos apenas as  tabelas do schema public, ignorando as tabelas de sistema.

pf=# select tablename from pg_tables where schemaname = 'public';
 tablename
-----------
 pfdet2011
 pf2011
 ns2011
 nsdet2011
 cliente
(5 registros)
3. Concedendo Acessos em Massa

Com o comando GRANT, posso conceder permissões de inclusão, alteração e exclusão nas tabelas do banco para um determinado usuário. Basta executar este select e utilizar o resultado da consulta como entrada para o postgresql:

pf=# select 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || tablename || ' TO 

postgres'  from pg_tables where schemaname = 'public';
                           ?column?                           
---------------------------------------------------------------
 GRANT SELECT, INSERT, UPDATE, DELETE ON pfdet2011 TO postgres
 GRANT SELECT, INSERT, UPDATE, DELETE ON pf2011 TO postgres
 GRANT SELECT, INSERT, UPDATE, DELETE ON ns2011 TO postgres
 GRANT SELECT, INSERT, UPDATE, DELETE ON nsdet2011 TO postgres
 GRANT SELECT, INSERT, UPDATE, DELETE ON cliente TO postgres
(5 registros)

Uma pequena alteração no script faz o produto cartesiano entre tabelas e usuários, gerando todas as combinações:

pf=# select 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || tab.tablename || ' TO ' || usu.usename || ' ; ' as COMANDO  from pg_tables tab, pg_user usu where tab.schemaname = 'public' ;
                             comando                             
------------------------------------------------------------------
 GRANT SELECT, INSERT, UPDATE, DELETE ON pfdet2011 TO postgres ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pf2011 TO postgres ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON ns2011 TO postgres ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON nsdet2011 TO postgres ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON cliente TO postgres ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pfdet2011 TO gisuser ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pf2011 TO gisuser ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON ns2011 TO gisuser ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON nsdet2011 TO gisuser ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON cliente TO gisuser ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pfdet2011 TO teste ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pf2011 TO teste ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON ns2011 TO teste ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON nsdet2011 TO teste ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON cliente TO teste ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pfdet2011 TO hacker ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON pf2011 TO hacker ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON ns2011 TO hacker ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON nsdet2011 TO hacker ;
 GRANT SELECT, INSERT, UPDATE, DELETE ON cliente TO hacker ;
(20 registros)
4. Revogando permissões de acesso


Com o comando REVOKE, as permissões  para todos os usuários podem ser revogadas instantaneamente:



pf=# select 'REVOKE SELECT, INSERT, UPDATE, DELETE ON ' || tab.tablename || ' FROM ' || usu.usename || ' ; ' as COMANDO  from pg_tables tab, pg_user usu where tab.schemaname = 'public' ;
                               comando                              
---------------------------------------------------------------------
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pfdet2011 FROM postgres ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pf2011 FROM postgres ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON ns2011 FROM postgres ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON nsdet2011 FROM postgres ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON cliente FROM postgres ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pfdet2011 FROM gisuser ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pf2011 FROM gisuser ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON ns2011 FROM gisuser ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON nsdet2011 FROM gisuser ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON cliente FROM gisuser ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pfdet2011 FROM teste ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pf2011 FROM teste ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON ns2011 FROM teste ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON nsdet2011 FROM teste ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON cliente FROM teste ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pfdet2011 FROM hacker ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON pf2011 FROM hacker ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON ns2011 FROM hacker ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON nsdet2011 FROM hacker ;
 REVOKE SELECT, INSERT, UPDATE, DELETE ON cliente FROM hacker ;
(20 registros)

5. Considerações Práticas


Como já foi mencionado neste post, a concessão de acesssos com GRANT e REVOKE raramente demanda alguma automação. Sintaxes poderosas e simples resolvem o problema sem maiores problemas, geralmente sendo executadas diretamente pelo DBA:

GRANT ALL ON DATABASE postgres TO hacker;

REVOKE ALL ON DATABASE postgres FROM hacker;


Este post é mais um exercício do que um exemplo prático, mas pode ser útil em situações em que se deseje maior controle.

Consulte as especificações dos comandos GRANT e REVOKE para ver a grande diversidade de opções disponíveis!

Bom Material sobre Otimização de Desempenho de Bancos de Dados PostgreSQL

Ajustes de performance são uma parte importante do trabalho dos DBAs. Este trabalho de conclusão de curso de Qiang Wang mostra diversas opções que podem ser empregadas para melhorar o desempenho do Postgresql.

O texto está em um inglês de fácil compreensão e as soluções sugeridas são bastante simples, o que torna o material bastante prático.

Faça Você Mesmo: Mapas Mentais Gerados Via SQL!

A criação de diagramas é um processo que exige paciência e bastante tempo. No entanto, se estes diagramas são de natureza hierárquica, fica mais fácil se pensar em um certo grau de automatização. Mas o que isso tem a ver com o postgresql? Este post mostra a geração automatizada de um diagrama hierárquico estilo mind map, através de uma consulta ao postgres!

Basicamente, este post apresenta duas utilidades práticas:
- A criação de mapas mentais sobre o banco de dados, importante para DBAs e útil para o gerenciamento dos bancos de dados;
- A geração de mapas mentais baseados nas informações contidas nos bancos de dados, o que pode ser uma alternativa relevante aos tradicionais relatórios tabulares.

Mapas mentais são diagramas que mostram uma hierarquia de conceitos, ideias ou objetos quaisquer. O Freemind é uma das ferramentas livres mais conhecidas para a construção intuitiva de mapas mentais, e será utilizado neste texto. A ferramenta permite a exportação de mapas mentais para vários formatos, pesquisas e várias outras funcionalidades.


Baixe-a e instale a partir deste site.

O formato interno do freemind é uma linguagem de marcação similar ao HTML ou ao XML, então é razoavelmente fácil criar diagramas através de consultas sql.

Os passos são os seguintes:

- Criar uma consulta que retorne o valor no formato do Freemind;
- Salvar o resultado da consulta em um arquivo .mm (mm de Mind Map);
- Abrir o diagrama gerado no Freemind e fazer as eventuais customizações.

A consulta abaixo realiza uma consulta aos metadados do postgresql e apresenta o resultado ao usuário (tive de salvar como imagem por ter caracteres não aceitos pelo Blogger):

Veja abaixo o resultado final obtido:

Agora é a sua vez! Tente executar a consulta no seu banco de dados, salve o resultado em um arquivo .mm e aprimore o script! 


Alguns desafios para você: 

- Aprimore o script deste post e compartilhe fazendo um comentário. Podes organizar de forma distinta, aninhar informações, acrescentar mais dados ou ainda melhorar o aspecto visual;
- Utilize mapas mentais na gestão dos seus BDs;
- Tente gerar diagramas não hierárquicos utilizando as setas. Não é tão fácil, mas você consegue;
- Produza relatórios no mundo real utilizando o Freemind e os dados gravados no postgresql.

Unlogged Tables: Funcionalidade para Aumento de Desempenho!

Todos sempre buscamos melhorar o desempenho das operações de banco de dados. E um dos recursos de performance ainda pouco utilizados da versão 9.1 do postgres são as chamadas unlogged tables.

O que são Unlogged Tables?

Unlogged Tables são tabelas que não apresentam suporte a recuperação pós-falha. Não apresentam portanto log de transações (write-ahead-log - WAL). Essa característica possibilita um grande ganho de desempenho em todas as operações realizadas. O ganho de desempenho obtido se deve ao sacrifício da possibilidade de recuperar os dados em caso de falha de sistema.

Uma unlogged table tem seus dados automaticamente perdidos em caso de falha, pois é truncada automaticamente, o que gera um ganho no tempo de recuperação do banco de dados.

Os dados de uma unlogged table não sofrem replicação dentro do postgresql.

Em unlogged tables não há necessidade de se manter o log e sincronizá-lo com o banco de dados, fator importante para o de ganho de desempenho.

Em que situações é recomendado utilizar este tipo de tabela?

Em situações em que a durabilidade dos dados não seja realmente importante:
- Para parâmetros de aplicações web;
- Cache de dados em geral;
- Tabelas de status de aplicações, entre outras possibilidades.


Acredito que apenas uma pequena parte de sistemas de bancos de dados possa ser armazenada em tabelas unlogged.

As operações de inserção, alteração, alteração e consulta a dados de uma "tabela sem log" são diferentes de uma tabela "normal"?
A forma de fazer e os comandos utilizados permanecem os mesmos. No entanto, internamente, não há write-ahead-log (WAL), o que faz com que os dados da tabela seja perdidos em caso de quedas de sistema. A velocidade das operações tende a ser bem maior.

Como criar Unlogged Tables?

A criação de tabelas sem log é bastante simples. Basta colocar a cláusula "UNLOGGED" no comando de criação da tabela.

teste=> CREATE TABLE LOGADA (cod integer, descricao varchar(50));
CREATE TABLE
teste=> CREATE UNLOGGED TABLE NAO_LOGADA (cod integer, descricao varchar(50));
CREATE TABLE
teste=>
É permitido indexar este tipo de tabela?

Não existem restrições à indexação, exceto para índices GIST em que este recurso não está implementado. É possível inclusive reindexar, se for o caso! Os índices de uma unlogged table também são "unlogged", isto é, são truncados em caso de falha do sistema.

teste=> CREATE INDEX UNLOGT ON NAO_LOGADA(cod);
CREATE INDEX
teste=>
teste=> insert into NAO_LOGADA values (1, 'Teste 1');
INSERT 0 1
teste=> insert into NAO_LOGADA values (2, 'Teste 2');
INSERT 0 1
teste=> insert into NAO_LOGADA values (3, 'Teste 3');
INSERT 0 1
teste=> REINDEX TABLE NAO_LOGADA;
REINDEX
De quanto é o ganho esperado em desempenho?

DEPENDE da operações realizada. Veja o link abaixo e depois faça seus próprios testes:
http://pgsnaga.blogspot.com/2011/10/pgbench-on-unlogged-tables.html

Considerações Finais

Unlogged Tables são um recurso válido para ganho de performance em certos casos específicos. No entanto, a definição de que tabelas devem ser unlogged pode gerar erros graves e impossibilitar a recuperação de dados relevantes. Esta decisão deve ser sempre bastante embasada e levar em conta as necessidades de todos os usuários do banco.

JMeter: Brincando de Testar seu Servidor PostgreSQL!

Testes de performance de servidores de bancos de dados são importantes para se avaliar ambientes de produção antes da implantação de projetos que envolvam muitos usuários e grande tráfego de informações. Uma ferramenta boa e estável para este tipo de teste é o Jakarta Jmeter.

Através do JMeter é possível configurar conexões ao servidor, criar requisições JDBC (comandos, chamadas de funções, etc.), submetê-las ao servidor e analisar o resultado da execução. A ferramenta permite ainda criar múltiplas threads simulando um grande número de usuários simultâneos, e executar para cada thread as requisições mais de uma vez, gerando uma grande carga de acessos que testa os limites de carga aceitos pelos bancos de dados.


Adicionalmente, é possível utilizar ouvintes (listeners) para apresentar os resultados da execução dos comandos na forma de tabelas, árvores e gráficos diversos.

O JMeter é compatível com qualquer banco que aceite conexão JDBC, incluindo o postgres e pode testar ainda outros tipos de requisição como ftp e http. Baixe o jmeter agora!

Vamos mostrar as principais etapas de utilização da ferramenta utilizando um teste feito no postgresql.

1. Instalação

Baixe a ferramenta, descompacte os arquivos em uma pasta. Observe que existem vários subdiretórios. As pastas mais relevantes são a LIB e a BIN.

Copie o arquivo do driver jdbc do postgresql (e o dos demais bancos com os quais trabalhar) para a pasta LIB do JMeter.

2. Execução

No windows pode ser utilizado o arquivo jmeter.bat, na pasta BIN da ferramenta (jakarta-jmeter-2.4/bin no meu caso). No Linux existe o arquivo jmeter.sh.

Existe a opção de rodar diretamente do arquivo .JAR, digitando:

java  -jar ApacheJMeter.jar

A tela inicial apresentada é bastante simples, com uma barra de menu e uma árvore onde são apresentados hierarquicamente os comandos dos planos de testes.

A árvore apresenta duas grandes divisões: plano de testes e área de trabalho. A área de trabalho pode ser utilizada para colocar comandos que não serão executados, enquanto que o plano de testes é a parte da treeview que apresenta os comandos do plano que serão executados, sendo que comandos de teste podem ser arrastados livremente entre área de trabalho e plano de testes.

3. Número de Usuários Simulados no Teste

O primeiro passo é definir o número de usuários que seu teste deseja simular.  Se o seu teste tiver muitos usuários, pode ser caracterizado como teste de carga. Caso tenha apenas um, executando uma vez cada comando, pode ser entendido como um teste funcional. Cada plano de testes pode agregar dezenas de requisições (testes) de banco de dados (JDBC), FTP, HTTP, entre outras possibilidades.


Com o botão direito do mouse sobre o plano de trabalho acione o menu "Plano de Testes\ Adicionar\ Threads (Users)\ Grupos de Usuários". A tela mostrada permite que se defina quantos usuários virtuais serão simulados, o tempo de inicialização de cada usuário e o número de vezes que cada usuário simulado executará os próximos comandos do plano de testes. Caso se deseje 100  usuários, executando 10 vezes cada teste, uma execução do plano de testes testará 10000 execuções do plano.

4. Configurando o JDBC

Para fazer testes de banco de dados, devemos configurar a conexão JDBC. 

Com o botão direito do mouse sobre o plano de trabalho acione o menu "Plano de Testes\ Adicionar\ Elemento de Configuração\  Configuração da Conexão JDBC".

A tela mostrada permite que se defina os parâmetros de conexão com o servidor, tais como limite de conexões, tempo máximo de conexão, intervalo para timeout de conexão, entre outros. A figura abaixo mostra os principais parâmetros utilizados.

5. Criação do Teste de Banco

Para criar os testes de banco de dados em si, basta se definir o SQL a ser submetido. O comando será executando tantas vezes forem definidas na seção "Grupo de Usuários" do plano de testes.

Com o botão direito do mouse sobre o plano de trabalho acione o menu "Plano de Testes\ Adicionar\ Testador\ Requisição JDBC".

A tela mostrada permite que se forneça o comando SQL a ser testado. Não utilize ponto e vírgula ";", pois pode gerar erro de execução.

6. Adicionando Ouvintes (Listeners)

Antes de executar os testes, deve ser definido de que forma o resultado da execução será apresentado. Os ouvintes monitoram os testes e apresentam o resumo das execuções de várias formas.

Um mesmo teste pode ser visualizado de mais de uma forma, o que facilita o entendimento, seja como árvore, tabela, gráfico ou geração de arquivo.


Com o botão direito do mouse sobre o plano de trabalho acione o menu "Plano de Testes\ Adicionar\ Ouvinte\ Árvore de Resultados".  Acrescente outros ouvintes como "Relatório Agregado" e "Ver Resultados em Tabela". As figuras abaixo mostra o resultado de um teste visto por mais de um ouvinte.

7. Executando Testes

Utilize a barra de menu para executar os testes:
- "Executar\ Iniciar" - Executa os testes do plano de trabalho atual
- "Executar\ Limpar Tudo" - Limpa os resultados de testes anteriores para nova rodada de testes
- "Arquivo\ Salvar" - Salva o Plano de Testes


Independentemente de termos boas ferramentas como o JMeter, temos sempre de testar as nossas aplicações, e não podemos descuidar dos bancos de dados. O JMeter tem potencial para automatizar boa parte dos testes feitos com bancos de dados sem grande esforço, o que não elimina a necessidade de bons testadores e de cuidado na hora de se realizar e interpretar os resultados apresentados.

Teste o JMeter e me diga o que achou dele! Não se esqueça que a qualidade do seu teste é consequência de um bom plano de testes!

Qual é a versão do seu Postgres?

Você sabe qual é a versão do seu servidor Postgres? Sabe mesmo? E do cliente (estava pensando que é sempre a mesma?)? Ele é 32 ou 64 bits? Para muitos desenvolvedores a resposta é não, e em vários casos nem se sabe como recuperar estas informações.


A melhor maneira de se saber a versão de um servidor banco de dados é simplesmente consultando-a. E no caso do postgresql a função que retorna estas informações é a VERSION().

Exemplo: 
- Consulta padrão:
SELECT version();


Resultado:
PostgreSQL 9.0.4 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 32-bit
Para recuperar informações de versionamento do cliente do banco deve se utilizar o utilitário psql:

Exemplo:
psql --version

Resultado: 
psql (PostgreSQL) 9.0.4
contém suporte a edição em linha de comando

Agora você pode visualizar facilmente a versão atual do seu postgres. Então é bom passar mais alguma informação sobre como interpretar o número de versionamento.

O postgres é versionado em um sistema de numeração com três números, no formato "A.B.C". A versão de produção atual, seguindo este formato, é a 9.0.4. A sistemática de numeração de versão do postgres pode ser conferida aqui.

- A - Número de versão principal. Quando este número muda significa que aconteceram alterações radicais na estrutura e funcionamento do banco. A versão atual é 9, e não há planejamento para uma versão 10 no momento.

- B - Número de versão secundário. Quando este número muda significa que aconteceram alterações na estrutura e funcionamento do banco que demandam. Os números A e B devem ser considerados em conjunto, e não apenas o primeiro número de versão, pois indicam uma versão em especial. O B da versão atual é 0, mas existe uma versão beta do postgres 9.1 e planejamento da versão 9.2.

- C - Número de atualizações aplicadas à versão "A.B". Na versão 8.4, por exemplo, já foram aplicadas 10 atualizações. O valor inicial de C é 0 e atualmente estamos na versão 9.0.4, indicando que a versão 9.0 já sofreu 4 atualizações. É importante acompanhar os informes de atualização especialmente quando solucionam questões chave de segurança, performance e bugs que afetam de alguma forma o desenvolvimento dos sistemas e a disponibilidade dos servidores de banco.

Que tal conferir agora a versão que está no seu sistema?

Utilize o PSQL como Gerador de Relatórios!

Uma boa maneira de utilizar o utilitário psql é como meio para executar scripts que recuperem informações e as armazenem em arquivos. Desta forma, podemos gerar relatórios de alta relevância e complexidade a um custo mínimo.

A sintaxe abaixo, lê um script de um arquivo de entrada com o programa e o executa no PSQL:

psql -U usuario -d banco_de_dados -f arquivo_de_entrada

Mas como gerar relatórios de forma fácil com o psql? Simplesmente as opções são infinitas, pois podemos mesclar comandos SELECT, funções como a current_timestamp e comandos do psql. A solução depende da sua criatividade.

Abaixo coloco um script simples que recupera informações sobre os objetos do banco. A opção "\o" especifica um arquivo de saída do relatório. Para se executar o script, o mesmo foi gravado no arquivo "entrada.txt" e foi executado no prompt do psql através da chamada "psql -U postgres -d teste -f entrada.txt". O script foi testado no Postgresql 9.0.

\o saida.txt
\echo Cabecalho
SELECT 'Inicio: ' || current_timestamp as Inicio;
\echo Relatorio no Arquivo Saida.txt
SELECT '#####################################################################' AS Titulo UNION ALL
SELECT '### Relatorio de Banco de Dados 1.0 #################################' AS Titulo UNION ALL
SELECT '### Claudio Leopoldino              #################################' AS Titulo UNION ALL
SELECT '### Script para livre distribuição e utilização #####################' AS Titulo UNION ALL
SELECT '### http://postgresqlbr.blogspot.com/ ###############################' AS Titulo UNION ALL
SELECT '#####################################################################' AS Titulo;
\echo Lista de Bancos de Dados
\qecho '#########################################################################################'
\qecho '### Bancos de Dados #####################################################################'
\qecho '#########################################################################################'
\l
\echo Lista de Bancos de Dados com Detalhe
\l+
\echo Lista de Usuarios
\qecho '#########################################################################################'
\qecho '### Lista de Usuarios ###################################################################'
\qecho '#########################################################################################'
\du
\echo Lista de Tabelas
\qecho '#########################################################################################'
\qecho '### TABELAS #############################################################################'
\qecho '#########################################################################################'
\dt
\echo Lista de Tabelas com Detalhe
\dt+
\echo Lista de Tabelas de Sistema
\dtS
\echo Lista de Tabelas de Sistema com Detalhe
\dtS+
\echo Lista de Indices
\qecho '#########################################################################################'
\qecho '### INDICES #############################################################################'
\qecho '#########################################################################################'
\di
\echo Lista de Indices com Detalhe
\di+
\echo Lista de Sequencias
\qecho '#########################################################################################'
\qecho '### SEQUENCIAS###########################################################################'
\qecho '#########################################################################################'
\ds
\echo Lista de Visoes
\qecho '#########################################################################################'
\qecho '### VISOES ##############################################################################'
\qecho '#########################################################################################'
\dv
\echo Lista de Visoes com Detalhe
\dv+
\echo Lista de Visoes de Sistema
\dvS
\echo Lista de Visoes de Sistema com Detalhe
\dvS+
\echo Lista de Privilegios de Acesso
\qecho '#########################################################################################'
\qecho '### PRIVILEGIOS DE ACESSO ###############################################################'
\qecho '#########################################################################################'
\dp
\echo Lista de Large Objects
\qecho '#########################################################################################'
\qecho '### LARGE OBJECTS #######################################################################'
\qecho '#########################################################################################'
\dl
\echo Lista de Funcoes
\qecho '#########################################################################################'
\qecho '### FUNCOES #############################################################################'
\qecho '#########################################################################################'
\df
\echo Lista de Operadores
\qecho '#########################################################################################'
\qecho '### OPERADORES ##########################################################################'
\qecho '#########################################################################################'
\do
\echo Lista de Tipos de Dados
\qecho '#########################################################################################'
\qecho '### TIPOS DE DADOS ######################################################################'
\qecho '#########################################################################################'
\dT
\echo Rodape
SELECT 'Final: ' || current_timestamp as Final; \q

Agora você pode executá-lo e incrementá-lo para extrair e formatar toda informação que você desejar. Pode ainda criar novos e melhores scripts! Explore opções para layouts mais agradáveis, consultas mais específicas e o que mais a sua necessidade exigir e a sua criatividade for capaz de propor!

Como melhorar este relatório? Te convido a compartilhar com a comunidade nos comentários deste post. Sua contribuição é sempre bem vinda!

Automatize o seu Backup do PostgreSQL via Python!

Vi esse script no site do David Goodwin e achei interessante. Mas ao executá-lo posteriormente tive outra boa surpresa: ele executa "de primeira", sem a necessidade de se fazer qualquer correção. A idéia é através de um programa em python chamar os utilitários de backup do postgres para realizar o backup, e o código deste script pode ser alterado para automatizar outras tarefas de modo bastante simples.

As etapas de utilização são simples:

- Instale o python e o postgres. Esta etapa eu não precisei fazer, porque já o tinha instalado aqui. Teste a versão digitando: "python --version". A versão da minha máquina é a 2.6.5 e do postgres é 9.0.
- Crie um arquivo de script com o código. Use a extensão ".py" como padrão para não misturar seus códigos python os de com outras aplicações.
- Edite os campos "usuário", "senha", "caminho do pg_dump", a "lista de bancos que devem sofrer backup" e salve o arquivo. Destaquei abaixo estes parâmetros em vermelho
- Execute o script. Utilizei "python -v py_backup.py". A opção -v significa VERBOSE, isto é, gera uma descrição de tudo o que está sendo feito durante o backup. Existem outras boas opções do pg_dump e do python, mas isso fica como pesquisa paras os interessados.

Abaixo coloco o código do script, mas acesse também o site original:


#!/usr/bin/python

from time import gmtime, strftime

import subprocess

import os

database_list = [ 'database1', 'database2', 'etc' ]

USER = "postgres"

PASS = "postgres-password"

BACKUP_DIR = "e:\\postgresql_backups\\"

# dump using PostgreSQL's custom format, with maximum compression. (-F c, -Z 9)

dumper = """ "c:\\program files\\postgresql\\8.1\\bin\\pg_dump" -U %s -Z 9 -f %s -F c %s  """                  

os.putenv('PGPASSWORD', PASS)

for database_name in database_list :

        print strftime( "%Y-%m-%d-%H-%M-%S" , gmtime()) + ":dump started for %s"%database_name

        time = str (strftime("%Y-%m-%d-%H-%M"))

        file_name = database_name + '_' + time + ".sql.pgdump"

        #Run the pg_dump command to the right directory

        command = dumper % (USER,  BACKUP_DIR + file_name, database_name)

        subprocess.call(command,shell = True)

        print strftime( "%Y-%m-%d-%H-%M-%S" , gmtime()) + ":finished"

Tente utilizar o python para chamar outros utilitários do postgres. Se quiser fazer um backup de todas as bases de dados, pode utilizar por exemplo o PG_DUMPALL.

Tem sugestões de melhoria para este script? Poste aqui no nosso fórum!

PostgreSQL no Ubuntu: Primeiros Passos Após a Instalação

Este post é uma continuação do de instalação do postgresql 9.0 no Ubuntu. Desta vez o referencial veio do site stuartellis.eu.

Uma vez instalado o banco, o serviço está ativo.

Por padrão existe um banco de dados postgres e um usuário postgres. É necessário configurar o acesso para poder cadastrar usuários.

Entre no terminal e siga os seguintes passos:

- sudo -u postgres psql - Abre o utilitário PSQL utilizando o usuário logado na máquina
- ALTER ROLE postgres WITH ENCRYPTED PASSWORD 'senhaforte'; - Dentro do psql, define a senha do role postgres
- \q - Encerra a sessão do psql

Agora altere os arquivos postgresql.conf e pg_hba.conf para habilitar o acesso:

- cd /etc/postgresql/9.0/main
- sudo gedit postgresql.conf - Alterar parâmetro listen_addresses para '*' ou para a lista de endereços desejada
- sudo gedit pg_hba.conf - Alterar configuração de segurança
- sudo postgresql service restart - Reiniciar o serviço atualiza os parâmetros alterados

Agora você pode entrar no psql como usuário postgresql, executar scripts, etc:

- sudo -u postgres psql -d postgres

Minha Instalação do PostgreSQL 9.0

A minha instalação de postgresql 9.0 no Ubuntu Linux, que já está defasada em relação à nova versão de desenvolvimento (9.1 - anúncio, download), eu fiz com base no link do sítio do Vinicius.

Utilizei estes comandos no terminal:
- sudo apt-get update  - atualização das listas de pacotes do apt-get
- sudo apt-get install postgresql-9.0 - instala o pacote do postgresql, versão 9.0
- sudo apt-get install pgadmin3 - instala o pgadmin
- sudo apt-get autoremove - removendo pacotes não mais necessários
- service postgresql status - teste se a instalação está funcionando. A resposta foi: "Running clusters: 9.0/main". O servidor está no ar!

Agora é executar, estudar e fazer testes!

Claro que existem dezenas de sítios com tutoriais e boas dicas sobre como fazer a instalação, e este post não visa aprofundar o tema. Mas é sempre bom lembrar: que versão está na sua máquina?

Catálogo do Postgres: uma imagem vale por mil palavras!

Compreender os metadados de um SGBD é a forma mais direta de saber como encontrar informações sobre o estado dos bancos de dados de um servidor. No caso do PostgreSQL, as várias tabelas e visões do sistema nem sempre são conhecidas pelo desenvolvedor, o que pode gerar problemas.

A imagem abaixo sintetiza o catálogo do Postgres em sua versão 8.3, apresentando uma visão geral das relações entre as tabelas de metadados. O diagrama não está compĺeto, por terem sido omitidos alguns dos atributos das relações e faltam visões de sistema como PG_TABLES e PG_LOCKS, mas me parece uma iniciativa pioneira para abrir estas informações para um público maior.

A imagem foi encontrada neste site peruano. Caso alguém conheça mais sobre a origem desta imagem e seu(s) autor(es) ou saiba de outra que englobe mais informação ou versões mais novas do Postgres, não deixe de me avisar!!!

PostgreSQL 9.0: Quais são as novidades? A visão das "funcionalidades da semana" - Parte 2!

O PostgreSQL está em sua quarta versão alfa, e começam a aparecer indicações das novas funcionalidades e alterações que foram introduzidas. O informe semanal "PostgreSQL Weekly News", organizado por David Fetter apresenta uma seção chamada "Feature of the Week", ou funcionalidade da semana, descrevendo uma alteração em desenvolvimento. Os informes originais podem ser consultados aqui.

Abaixo listo as últimas funcionalidades citadas, agregando algum comentário quando pertinente:

31/05/2010 - Esta semana não teve "funcionalidade da semana". Nas colunas anteriores a coluna era escrita por David Fetter e Devrim GUNDUZ, mas o segundo não participou da coluna desta semana. Pode ser que a feature of the week volte, ou não...

23/05/2010 - Objetos grandes (lo_ *) agora possuem controles de acesso, como os demais objetos do banco de dados. É mais um fator positivo para a utilização de LOBs em geral.

16/05/2010 - O utilitário pg_ctl agora tem uma opçao initdb. Esta opção inicializa um servidor postgres. Mais informações aqui.

09/05/2010 - Você pode criar triggers por coluna, ou seja, que são disparads somente na modificação das colunas especificadas. Recurso utilíssimo!!!

A sintaxe, como descrita no SQL: 2008, é: CREATE TRIGGER trigger_name (BEFORE | AFTER) UPDATE OF col1 [,] ... ON col2 coln tablename FOR EACH ROW trigger_function PROCESSO EXECUTE ();

02/05/2010 - Agora você pode chamar funções com parâmetros nomeados, por exemplo, parameter_bar foo ('valor' AS parameter_foo, o valor 'outro' AS).

Recurso muito prático se você lembra o nome dos os parâmetros, mas não a ordem em que estão na declaração da função.

25/04/2010 - Funções podem agora ter valores padrão para parâmetros. Ótima e simples idéia!!!

18/04/2010 - GUCs agora são reguláveis por função e por banco de dados.

Você sabe o que é GUC? Global User Configuration (GUC) são configurações de servidor do arquivo postgresql.conf. Poder definir parâmetros de servidor para cada função é ter mais flexibilidade. Mas a forma como isso será implementado não parece clara ainda.

11/04/2010 - "You can now GRANT and REVOKE on objects schema-wide in a single command".

Mais recursos para configurar permissões de acesso, desta vez afetando todo o esquema do banco com apenas um comando. Faltam mais detalhes.

04/04/2010 - Existe agora um comando chamado ALTER DEFAULT PRIVILEGES, o qual permite o ajuste de privilégios que serão aplicados a objetos a serem criados no futuro.

É uma opção boa para préconfigurar os objetos do banco de dados utilzando grant e revoke para tabelas, sequências e funções.

01/04/2010 - "Distributed map-reduce functions in Erlang". Desconsiderem todo anúncio da equipe do Postgres feito no primeiro de abril!!!