Baixe o app para aproveitar ainda mais
Prévia do material em texto
Sumário Conteúdo sobre Novidades 25 – Oracle 12c: Conheça a cláusula pattern matching [ Tércio Costa ] 14 – Trabalhando com o PostgreSQL: implantação do banco de dados [ Bruno de Oliveira Machado ] Conteúdo sobre Novidades 06 – SQL Server 2016: Conheça as novidades [ Felipe de Assis ] Artigo no estilo Mentoring 32 – Atualização de schemas no Oracle 11gR2 com o Oracle Data Pump [ Marcelo Thomaz Cicala ] 145ª Edição - 2017 - ISSN 1677918-5 - Impresso no Brasil Editor Rodrigo Oliveira Spínola (rodrigo.devmedia@gmail.com) Subeditor Eduardo Oliveira Spínola Consultora Técnica Anny Caroline (annycarolinegnr@gmail.com) Jornalista Responsável Kaline Dolabella - JP24185 Capa e Diagramação Romulo Araujo Fale com o Editor! É muito importante para a equipe saber o que você está achando da revista: que tipo de artigo você gostaria de ler, que artigo você mais gostou e qual artigo você menos gostou. Fique a vontade para entrar em contato com os editores e dar a sua sugestão! Se você estiver interessado em publicar um artigo na revista ou no site SQL Magazine, entre em contato com os editores, informando o título e mini-resumo do tema que você gostaria de publicar: Rodrigo Oliveira Spínola - Editor da Revista rodrigo.devmedia@gmail.com RODRIGO OLIVEIRA SPÍNOLA Editor Chefe da SQL Magazine, Mobile e Engenharia de Software Magazine. Professor da Faculdade Ruy Barbosa, uma instituição parte do Grupo DeVry. Doutor e Mestre em Engenharia de Software pela COPPE/UFRJ. Assine agora e tenha acesso a todo o conteúdo da DevMedia: www.devmedia.com.br/mvp Atendimento ao leitor A DevMedia possui uma Central de Atendimento on-line, onde você pode tirar suas dúvidas sobre serviços, enviar críticas e sugestões e falar com um de nossos atendentes. Através da nossa central também é possível alterar dados cadastrais, consultar o status de assinaturas e conferir a data de envio de suas revistas. Acesse www.devmedia .com.br/central, ou se preferir entre em contato conosco através do telefone 21 3382-5038. Publicidade publicidade@devmedia.com.br – 21 3382-5038 Anúncios – Anunciando nas publicações e nos sites do Grupo DevMedia, você divulga sua marca ou produto para mais de 100 mil desenvolvedores de todo o Brasil, em mais de 200 cidades. Solicite nossos Media Kits, com detalhes sobre preços e formatos de anúncios. EXPEDIENTE Guia HTML 5Guia HTML 5 DEVMEDIA http://www.devmedia.com.br/guias/guia-html/3 Um verdadeiro manual de referência com tudo que você precisa sobre HTML! 6 SQL Magazine • Edição 145 Este artigo é destinado a profissionais que lidam com bancos de dados SQL Server diretamente e/ou tomam decisões técnicas relacionadas à implementação de soluções usando esta plataforma de gerencia- mento de dados. O conteúdo abordará as novidades disponibilizadas na versão 2016 do produto com o intuito de expor suas melhorias e seus novos recursos. Fique por dentro SQL Server 2016: Conheça as novidades Veja neste artigo uma análise sobre as funcionalidades apresentadas para a nova versão As duas últimas versões do SQL Server (2012 e 2014) trouxeram algumas funcionalidades que mudaram bastante o cenário de implantação do produto, principalmente quando são consideradas características como performance e alta disponibi- lidade. Nesse contexto, recursos como Always On Availability Groups, In-Memory Optimized Tables/ Procedures e Columnstore Indexes se tornaram, desde seus respectivos lançamentos, essenciais para garantir a ambientes críticos de bancos de dados os mais altos níveis de performance, disponibilidade e capacidade de gerenciamento. Dando sequência à evolução do produto, o time res- ponsável pelo desenvolvimento da plataforma trabalhou para melhorar ainda mais os mecanismos já implemen- tados e, claro, adicionar novas funcionalidades para que o SQL Server possa atender eficientemente diversos ce- nários e casos de uso onde performance, confiabilidade e segurança são necessários junto ao gerenciamento de dados, não importando sua volumetria. Temporal Tables, Query Store e Stretch Databases são apenas três das novas funcionalidades presentes no SQL Server 2016. Além dessas, pode-se mencionar as melhorias feitas junto ao recurso In-Memory Optimized Tables/Procedures, cuja lista de pré-requisitos para implantação foi drasticamente reduzida, e também os ajustes nos Columnstore Indexes, que serão compatíveis com índices tradicionais do tipo Row Store a partir da versão 2016, limitação que bloqueava a adoção desse recurso em alguns cenários. Outra novidade que se pode notar na versão 2016 do SQL Server é a abordagem de integração entre On-Premi- ses e Cloud Computing. Essa ideologia, que já vem sendo aplicada há alguns anos pela Microsoft, tornou-se ainda mais forte, trazendo ao principal software de gerencia- mento de dados novas funcionalidades que permitirão melhor aproveitamento dos recursos disponibilizados na plataforma de nuvem Windows Azure. Devido à amplitude do assunto em relação a cada uma das fun- cionalidades aqui apresentadas, o objetivo desse artigo não será adentrar nos detalhes técnicos acerca de como as novas funciona- lidades irão se comportar quando utilizadas, mas sim apresentar uma visão geral do que cada uma possibilitará, trazendo ao leitor um esboço expressivo da nova versão do SQL Server. Stretch Databases O primeiro recurso a ser apresentado neste artigo é denominado Stretch Databases. Seu objetivo é permitir o armazenamento de tabelas específicas no Azure SQL Database, fazendo com que objetos do mesmo banco de dados fiquem distribuídos entre o ambiente tradicional e a plataforma de nuvem. Basicamente, com a funcionalidade Stretch Databases será possível selecionar uma ou mais tabelas de um banco de dados armazenado localmente e indicar ao SGBD que esses objetos devem ser mantidos agora no Windows Azure, removendo-os do ambiente On-Premises e mantendo apenas uma referência lógica entre os dois locais. Para as aplicações, essa divisão lógica do banco de dados é completamente transparente, visto que o próprio SQL Server é responsável por enviar e recuperar os registros do Azure SQL Database quando houver manipulações e/ou consultas sobre as tabelas armazenadas em nuvem. Na camada de banco de dados, o funcionamento será relativamente simples: as tabelas selecionadas para usarem esse recurso ainda existirão no servidor local e ainda serão capazes de receber comandos DML normalmente, mas todos os registros serão Edição 145 • SQL Magazine 7 assincronamente transferidos para o Azure SQL Database quando inseridos no objeto, diferente do que acontecerá quando comandos UPDATE, DELETE e SELECT forem executados, pois nesses casos os dados serão movimentados “em tempo real” para atender as requisições de forma transparente às aplicações, agregando à solicitação apenas o tempo necessário para a transferência na rede. A transferência dos dados entre os ambientes On-Premises e Cloud Computing acontece totalmente em background, através de tarefas internas do SQL Server que são as principais responsáveis por direcionar os registros para o Azure SQL Database e/ou solicitá- los novamente quando uma query faz requisições de leitura. A Figura 1 ilustra a arquitetura básica do recurso Stretch Databases. Figura 1. Arquitetura básica da funcionalidade Stretch Databases O principal benefício dessa funcionalidade é a possibilidade de usar os recursos locais com maior eficiência, pois será significativamente mais fácil remover dados históricos e/ou raramente acessados do mesmo servidor onde a porção de dados mais ativa e importante ao negócio está localizada, permitindo assim melhor utilização dos discos, por exemplo. Dados históricos costumamfazer com que os backups dos bancos de dados sejam maiores e, consequentemente, a janela de execução aumente drasticamente ao longo da vida útil da solução, além de prejudicarem a performance da seleção e manipulação de dados por parte das aplicações, pois na maioria das vezes, apenas uma pequena porcentagem dos registros armazenados em uma tabela é constantemente utilizada. Outro exemplo de dados acessados ocasionalmente são tabelas de auditoria, cuja funcionalidade geralmente é rastrear e armazenar ações de usuários ou aplicações para uma possível consulta depois. Normalmente, os dados de histórico mantidos por questões de auditoria são destinados a ajudar caso haja algum tipo de investigação a respeito de uma falha ou erro, ou mesmo para serem entregues a órgãos do governo conforme possíveis regulamentações existentes para aquele segmento de negócio. Os dois cenários mencionados anteriormente são apenas alguns dos exemplos que poderão ser beneficiados com a Stretch Databases, pois ambos lidam com uma porção dos dados que é acessada apenas ocasionalmente. Desse modo, armazená-los em um servidor de alta performance, com discos extremamente rápidos e que trazem consigo um custo elevado talvez não seja o melhor dos cenários quando o assunto é rentabilidade. Outra vantagem ganha destaque quando consideramos questões financeiras, visto que o custo de armazenamento em nuvem é significativamente inferior ao valor exigido para a aquisição e manutenção de equipamentos de alta performance. É importante destacar que o custo, em ambientes tradicionais, não se refere apenas à aquisição de equipamentos, mas também à manutenção, contratação ou treinamento de pessoal qualificado para administrar, infraestrutura para instalação, entre outras coisas. Além disso, a implantação desse recurso é bastante simples. Basta possuir em mãos uma conta do Windows Azure, habilitar o banco de dados para Stretch e selecionar as tabelas desejadas, que precisam atender uma pequena lista de critérios, como não possuir constraints unique ou default. Depois disso, basta deixar que o próprio SQL Server provisione o ambiente em nuvem, caso ele não exista, e faça os demais ajustes. A principal desvantagem é a latência de rede existente entre os ambientes On-Premises e Cloud Computing, fator que deve ser considerado quando performance ainda é importante para consultas e manipulações dos dados armazenados no Azure SQL Database. Devido ao armazenamento das tabelas selecionadas ser realizado remotamente, em um Datacenter da Microsoft, consultas sobre os dados desses objetos tendem a necessitar um tempo superior ao que seria necessário caso estivessem localmente no servidor. Devido a essa desvantagem, é importante avaliar cuidadosamente quais tabelas indicar para o armazenamento remoto. Não é recomendado, por exemplo, aplicar esse recurso sobre uma tabela que seja crítica a uma aplicação LOB (Linha de Negócio, em inglês), pois cada transação efetuada precisará do tempo normalmente exigido e também do tempo agregado pela latência de rede, e esse aumento no tempo de execução pode não ser tolerável. Query Store O objetivo desse novo recurso é prover ao administrador de banco de dados a capacidade de analisar planos de execução retroativamente, assim possibilitando, por exemplo, a comparação de como uma determinada rotina tem sido executada pelo SQL Server ao longo de um determinado período, o que facilita a identificação de alguns tipos de problemas de performance. Basicamente, o Query Store armazena planos de execução gerados pelo Query Optimizer e os disponibiliza para consulta através de uma nova interface gráfica adicionada ao Management Studio. Para isso, ele captura os planos de execução depois que o processo é executado e armazena no mesmo banco de dados onde a query foi disparada. Assim como o recurso Stretch Databases, o Query Store é invisível às aplicações e pode ser considerado uma ferramenta de apoio a profissionais responsáveis pela identificação e resolução de problemas de performance mais voltados a rotinas e comandos T-SQL. SQL Server 2016: Conheça as novidades 8 SQL Magazine • Edição 145 A configuração do Query Store é muito simples. É necessário apenas habilitar a funcionalidade no banco de dados desejado e ajustar algumas configurações como, por exemplo, a quantidade de espaço em disco que será destinado ao armazenamento de planos de execução e o período de retenção dos planos dentro do banco de dados. Uma vez habilitado, o SQL Server passa a inserir uma cópia dos planos de execução na base de dados do Query Store e disponibilizá-los para consulta conforme a periodicidade configurada inicialmente. Esse ajuste e os mencionados anteriormente podem ser visualizados na janela de configuração do recurso, apresentada na Figura 2. Depois de coletados e disponibilizados para consulta, os planos de execução podem ser acessados através de alguns relatórios presentes no SQL Server Management Studio, sob a aba Query Store, dentro do diretório do próprio banco de dados em questão. Por padrão, existem quatro relatórios, cada um permitindo que os dados sejam visualizados sob uma perspectiva diferente. A Figura 3 apresenta um dos relatórios disponíveis, onde é possível conferir, por exemplo, as 25 queries que mais consumiram recursos na última hora (canto superior esquerdo), além de permitir a visualização do plano de execução (canto inferior) e também de como diferentes planos de execução (caso exista mais de um) da mesma procedure têm se comportado no ambiente no mesmo período mencionado anteriormente (canto superior direito). Os relatór ios apresentados pelo Query Store ainda possibilitam que o administrador de banco de dados selecione um plano de execução que possui melhor desempenho dentre aquelas diferentes execuções de uma determinada procedure e solicitar ao SQL Server que fixe essa estratégia como caminho padrão que o produto deve utilizar para todas as chamadas da rotina. Essa ferramenta facilitará, ainda, a aplicação de outro recurso já existente nas versões anteriores da plataforma e conhecido como Plan Guides, que pode ser definido justamente como a fixação de um único plano de execução para um código T-SQL em específico, técnica que remove a autonomia do produto em definir a melhor estratégia durante a execução propriamente dita do processo e transfere ao administrador de banco de dados essa responsabilidade. Temporal Tables As tabelas temporais são um novo tipo de objeto no SQL Server 2016 cujo objetivo é rastrear e armazenar as modificações feitas sobre os registros de uma determinada Figura 2. Configuração do recurso Query Store Figura 3. Relatório de visualização do Query Store Edição 145 • SQL Magazine 9 tabela, possibilitando que os dados sejam consultados como se a query tivesse sido disparada em outro ponto específico no tempo, como uma semana atrás. É válido destacar que esse recurso, diferentemente do SQL Server Audit, por exemplo, não tem como foco a identificação de quem fez uma ou outra modificação nos dados, mas sim o que mudou propriamente dito nos registros. O foco é totalmente direcionado ao histórico dos dados, não considerando questões de auditoria de segurança e/ou assuntos correlacionados. Seu funcionamento é bem simples: a tabela temporal é criada e referenciada junto à tabela que deverá ser rastreada. Depois disso, qualquer operação que altere ou exclua dados do objeto principal provocará uma inserção na tabela temporal, onde serão mantidas as versões anteriores dos registros que foram modificados, mantendo assim um histórico de todas as versões geradas para cada registro depois de sua implementação. Além disso, toda a implementação,assim como o funcionamento, é invisível às aplicações e usuários, não exigindo nenhuma modificação em códigos-fonte ou rotinas presentes em qualquer uma das camadas, mesmo nos bancos de dados. Isso acontece porque a aplicação desse recurso no banco de dados não exige modificações significativas nos objetos tradicionais, onde os dados são normalmente manipulados. Basicamente, ao criar uma tabela temporal, comandos de manipulação de dados como UPDATEs e DELETEs (deve-se também considerar o MERGE, pois, na prática, ele atualiza ou exclui registros) efetuados sobre a tabela principal provocam um comando adicional, responsável por registrar a versão anterior do registro na tabela temporal, que acumulará todas as versões já existentes e não mais válidas dos dados que sofreram alterações ou exclusões. Comandos INSERT não geram operação alguma na tabela temporal, visto que não criam uma versão anterior para ser considerada como histórico, mas sim incluem um novo registro no banco de dados. Já em relação aos comandos SELECT, a Microsoft adicionou quatro novos comandos para permitir a seleção dos dados históricos como se a query tivesse sido executada em outro horário/dia. Caso nenhuma dessas novas cláusulas seja usada, uma consulta convencional é realizada pelo SQL Server, retornando apenas os dados da tabela tradicional. A Figura 4 ilustra o conceito aplicado nas tabelas temporais. Armazenar dados históricos da forma que as Temporal Tables provisionarão era, até então, uma tarefa árdua para o administrador, pois exigia a aplicação de outros recursos, complexos de implementar e, principalmente, de administrar, ou até mesmo dependiam de aquisição de ferramentas de terceiros, adaptações no banco de dados ou nas aplicações, o que tornava a necessidade de se manter esses dados um enorme desafio técnico. O principal ponto de atenção em relação a esse recurso é o volume de dados que pode ser gerado na tabela temporal depois de um período com essa funcionalidade habilitada. Como cada comando UPDATE e DELETE gera um novo registro no histórico, deve-se atentar ao tamanho do objeto por questões de administração e performance, pois uma tabela temporal associada a um objeto que sofra uma considerável quantidade de alterações pode gerar um volume de versões extremamente grande. Deve-se ressaltar que o volume de dados armazenados em uma tabela temporal não trará impactos na performance e no funcionamento convencional do ambiente em relação a operações de leitura na tabela principal, visto que nada muda para as tabelas onde os dados de produção estão armazenados. Contudo, operações que geram alteração de dados, como UPDATE e DELETE, causarão um comando adicional de INSERT no servidor, justamente para manter a versão histórica do registro que foi modificado. Figura 4. Arquitetura básica das Temporal Tables Adicionalmente, tarefas administrativas como backup, manutenção de índices, transferências de dados feitas por recursos como Always On Availability Groups tendem a considerar esses registros em suas operações, aumentando assim a quantidade de dados a serem processados por cada uma delas. Para mitigar as dificuldades mencionadas, estratégias como particionamento de dados, combinadas com operações de Switch Partition, por exemplo, podem ser aplicadas e usadas constantemente para movimentar os dados históricos para tabelas separadas e/ou outros filegroups do banco de dados. FOR JSON O SQL Server 2016 traz agora suporte à cláusula FOR JSON, o que faz a nova versão do produto ser capaz de retornar dados nesse formato, facilitando a integração de dados entre a plataforma de banco de dados e as aplicações que consomem e transferem dados usando o padrão JSON (BOX 1). É uma linguagem de comunicação similar ao XML que tem sido largamente adotada por web services para responder a requisições através da internet. É baseada em JavaScript, porém possui suas particularidades adaptadas justamente para possibilitar transferências de dados através da internet com menor tráfego de dados possível. BOX 1. JSON Basicamente, será possível consultar os dados usando a linguagem T-SQL e formatar os resultados em JSON, tornando viável o consumo e transmissão direto da informação sem necessidade de uma nova conversão por parte da aplicação, através de web services. SQL Server 2016: Conheça as novidades 10 SQL Magazine • Edição 145 No enta nto, a i nda não haverá a possibilidade de armazenar os dados no formato JSON, nem haverá uma grande variedade de comandos específicos para manipular dados nesse modelo, como é possível fazer quando se trabalha com dados armazenados dentro do SQL Server em XML. Os comandos já divulgados pela Microsoft incluem conversões automáticas de dados tabulares para o formato JSON, como pode ser visto na Figura 5, além da possibilidade de se realizar o inverso, ou seja, ler dados provindos desse modelo e manipulá-los como registros tabulares, como é comumente feito no SQL Server. Live Query Statistics Mais uma novidade presente no SQL Server 2016 será a possibilidade de visualizar um plano de execução em tempo real. Na prática, esse recurso permitirá que um administrador de banco de dados acompanhe como o SQL Server está conduzindo uma determinada query durante seu processamento em tempo real. Nas versões anteriores, haviam duas possibilidades quando o assunto era visualizar um plano de execução de uma query: estimado e real. O primeiro deles é baseado em uma estimativa do SQL Server de como seria a melhor forma de se atender à requisição feita pelo usuário sem mesmo processá-la, enquanto o segundo corresponde ao plano de execução efetivamente utilizado para concluir aquela solicitação. Naturalmente, o plano de execução estimado é mais rápido para ser obtido, visto que não há necessidade de o SQL Server processar a consulta. Em contrapartida, o plano estimado pode não ser fidedigno à estratégia que seria escolhida pelo produto em uma situação onde a query fosse realmente processada. O plano de execução real, por sua vez, é totalmente confiável em relação ao que foi escolhido pelo SQL Server no processamento da query. Adicionalmente, retorna informações complementares que podem ser obtidas apenas quando a consulta é processada de fato. Sua desvantagem é a necessidade de aguardar a conclusão da consulta, o que muitas vezes demanda muito tempo ou em alguns ambientes não é possível devido a i números fatores ou situações adversas. É na questão tempo exigido para se obter o plano de execução real que esse novo recurso se encaixa perfeitamente, pois será possível analisar as etapas que o SQL Server está conduzindo para entregar os dados solicitados antes mesmo do processo ser concluído, inclusive acompanhando como o volume de dados está sendo processado por cada um dos operadores. A Figura 6 ilustra o resultado obtido através do uso desta nova funcionalidade: um plano de execução “vivo”, ainda em processamento, onde o responsável pela análise pode visualizar não apenas a estratégia de execução do SQL Server, mas também o percentual de conclusão de cada operador, entre outras informações adicionais. Backup to Azure Não é segredo que o assunto backup seja uma das principais responsabilidades de qualquer profissional cuja posição inclua cuidar de um ambiente de banco de dados. Essa questão é tão importante que são inúmeros os cuidados que devem ser tomados com os arquivos resultantes desse processo. Uma vez que serão requisitados, na maioria das vezes, em momentos críticos, de grande tensão e preocupação de todos, principalmente das pessoas responsáveis pelo negócio da empresa, como coordenadores, diretores, presidente, etc., é imensurável a importânciade tais arquivos. Backups são normalmente gerados em disco e depois copiados para um dispositivo de armazenamento a longo prazo, como uma fita. Em alguns ambientes, são feitos diretamente para a fita ou mesmo para um servidor munido de um software gerenciador de backups, que automatiza boa parte do trabalho, como o módulo Data Protection Manager do System Center, conjunto de soluções de administração de ambientes corporativos da Microsoft. Armazenar arquivos de backup fora de um ambiente On-Premises, onde Figura 6. Visualização do plano de execução em tempo real Figura 5. Suporte nativo a JSON Edição 145 • SQL Magazine 11 são necessários equipamentos adequados, sala cofre, proteção, segurança de acesso, entre outros requisitos, não é um conceito totalmente novo, visto que alguns fornecedores de soluções voltadas para Cloud Computing disponibilizam serviços dedicados ao armazenamento de dados críticos. Para tal, oferecem todos os requisitos mencionados anteriormente com um custo inferior ao normalmente investido pelas empresas no provisionamento dessa infraestrutura. Uma das novas funcionalidades do SQL Server 2016 endereça justamente esse novo cenário: a realização de backups e restores de forma integrada ao armazenamento de dados em nuvem. Essa nova capacidade introduzirá maior facilidade em questões como segurança dos backups, garantia de disponibilidade e até mesmo maior flexibilidade em projetos de migração. A partir da versão 2016 é possível realizar backups e armazenar os arquivos diretamente em um espaço de armazenamento do Windows Azure, assim como restaurar bancos de dados usando a mesma premissa. Isso facilita consideravelmente atividades de recuperação de desastres, pois os arquivos de backup estarão salvos e acessíveis independentemente do tipo de problema que venha a ocorrer, mesmo que um dos datacenters da Microsoft fique indisponível, pois cada arquivo é replicado para mais duas localizações visando garantir total proteção dos dados. Outra atividade que se beneficiará desse novo recurso é a migração de bancos de dados entre dois ambientes On-Premises que estejam conectados por uma rede de baixa performance, mas possuam uma conexão mais rápida com a internet, por exemplo. Nesse cenário, será possível realizar o backup diretamente para o ambiente Cloud Computing e depois restaurar o banco de dados para o segundo servidor usando o mesmo arquivo, provindo do Windows Azure. Outro cenário onde esse recurso trará benefícios será a migração entre um ambiente On-Premises e outro hospedado na plataforma Windows Azure, visto que o mesmo processo apresentado no cenário anterior poderá ser aplicado. Para efeitos de comparação, essa estratégia de cópia e migração de bancos de dados através de backups possui uma pequena diferença em relação às versões anteriores, onde é necessário realizar os backups localmente ou em um diretório de rede, e depois fazer o upgrade para o Windows Azure. Com o SQL Server 2016, o tempo poderá ser poupado, visto que apenas uma tarefa, a execução do backup propriamente dita, será necessária para atingir o mesmo objetivo. Always Encrypted Pensando na segurança dos dados, a Microsoft introduziu no SQL Server 2016 uma funcionalidade que permitirá que aplicações se comuniquem com a plataforma de banco de dados e estabeleçam uma transmissão de dados entre eles onde apenas informações criptografadas poderão ser enviadas e recebidas. Essa funcionalidade permitirá que a aplicação manipule os dados em formato legível e no momento do envio deles para o banco de dados, providencie sua criptografia, transferindo-os na rede já protegidos por algoritmos avançados de segurança. O banco de dados, por sua vez, receberá os dados criptografados e os armazenará da mesma forma, ilegíveis. Por fim, quando a aplicação solicitar ao SQL Server que responda a uma requisição de dados, a plataforma de banco de dados enviará os dados ainda criptografados para a camada de apresentação, que será responsável por exibi-los em seu formato tradicional. Essa arquitetura pode ser conferida na Figura 7. Figura 7. Arquitetura básica de funcionamento do Always Encrypted O objetivo principal desse recurso é evitar que dados sejam acessados, roubados e interpretados por pessoas mal- intencionadas que consigam acesso à rede de dados da empresa. Caso haja uma violação de segurança e o atacante consiga capturar qualquer informação que esteja sendo transferida entre a aplicação e o banco de dados, ele não conseguirá compreendê-los, visto que somente a aplicação, através de sua camada de acesso ADO, pode remover a criptografia e disponibilizar os dados no formato “clear text”. Dynamic Data Masking Essa nova funcionalidade do SQL Server 2016 tem a capacidade de proteger dados armazenados em colunas específicas mascarando os valores para que usuários sem o privilégio necessário não tenham acesso a determinadas informações mesmo que ainda possam executar comandos sobre aquele objeto, tudo isso de forma totalmente transparente para as aplicações. O Dynamic Data Masking pode ser aplicado tanto em uma nova tabela quanto sobre uma já existente, através de novas cláusulas T-SQL adicionadas ao produto para configurar como os dados serão mascarados. Cada coluna recebe sua própria configuração, ou seja, uma tabela com 10 colunas pode ter algumas com essa funcionalidade e outras sem. Embora a conf iguração seja feita sobre cada coluna individualmente, para visualizar os dados legivelmente é necessário possuir a permissão UNMASK sobre aquela tabela. SQL Server 2016: Conheça as novidades 12 SQL Magazine • Edição 145 Todos os usuários que não possuírem esse direito, ao selecionar os dados do objeto, receberão os valores das colunas onde há o Dynamic Data Masking configurado como valores mascarados, conforme a especificação feita na implantação da funcionalidade. É importante destacar que os dados permanecem armazenados sem máscara na tabela e nos índices existentes, sendo mascarados apenas no momento da entrega do result set ao usuário solicitante que não possuir a permissão necessária. A Figura 8 ilustra a arquitetura geral dessa funcionalidade, onde é possível visualizar a diferença entre os resultados obtidos para dois diferentes grupos de usuários. O primeiro deles com permissão de SELECT sobre o objeto, mas sem direito a visualizar o valor contido naqueles campos, e o segundo, com direito total a consultar e ver os dados de toda a tabela. Figura 8. Arquitetura da funcionalidade Dynamic Data Masking Row-Level Security Pode-se definir como objetivo desse recurso a capacidade de conceder permissões de segurança aos usuários conforme o resultado de uma função, que atuará como critério de validação para liberar ou não consultas e operações de manipulação de dados a quem estiver tentando alguma dessas operações. Tudo isso dinamicamente, sem que aplicações ou os próprios usuários notem alguma diferença nas queries executadas por eles. Na prática, essa funcionalidade foi projetada para permitir aos administradores de banco de dados a implementação de segurança baseada em predicados personalizados, que podem inclusive ser dinâmicos conforme a lógica inserida dentro da função responsável por essa classificação, em uma arquitetura bem similar a outro recurso: o Resource Governor. Sua arquitetura é constituída por dois objetos: uma função de classificação e uma política de segurança (denominada security policy na arquitetura oficial da funcionalidade), objeto novo disponibilizado no SQL Server 2016 para associar uma função a uma ou mais tabelas, podendo inclusive ser considerada como um container de associações. A função de qualificação é responsável por receber um ou mais parâmetros, provindos da tabelaassociada na política de segurança, e avaliar, a cada comando SELECT efetuado, quais serão os dados que aquele usuário poderá visualizar. Com base nisso, o próprio SQL Server adicionará o filtro equivalente nos operadores do plano de execução e assim retornar apenas os registros classificados como acessíveis para o devido usuário. A Figura 9 apresenta a arquitetura básica da funcionalidade Row-Level Security simulando um cenário de vendas onde dois grupos de colaboradores (gerentes e diretores) possuem diferentes permissões em relação a quais dados podem ou não serem visualizados. Figura 9. Arquitetura básica da funcionalidade Row-Level Security No exemplo em questão, os diretores possuem permissão total de visualização. Assim, quando disparam uma consulta à tabela de vendas, receberão como resultado todos os dados existentes naquela tabela. Por outro lado, os gerentes, ao executarem a mesma consulta, poderão ver apenas as vendas relativas ao território correspondente à sua unidade. A lógica por trás da classificação de quais dados um usuário específico pode visualizar fica dentro da função, que é desenvolvida pelo profissional responsável pelo ambiente e associada a uma tabela através do container Security Policy. Dentro desse objeto as possibilidades são praticamente incontáveis. Um exemplo é a possibilidade de determinar a permissão baseada em diversos critérios, como o ID do departamento onde aquele colaborador está alocado, restringindo-o, nesse caso, a visualizar apenas os dados do setor em questão. Cada uma das novidades do SQL Server 2016, tanto as novas funcionalidades quanto as melhorias feitas pela Microsoft junto a alguns dos recursos já existentes, reforçam o comprometimento da empresa em tornar o produto melhor, atendendo a cenários com a maior robustez e eficiência possíveis, independente da criticidade e volumetria de dados a ser processada. Além de todas as funcionalidades apresentadas neste artigo, vale destacar que diversos recursos já existentes no produto desde versões anteriores receberam melhorias, algumas delas significativas, como no caso do In-Memory Optimized Tables Edição 145 • SQL Magazine 13 and Procedures, mencionado no começo deste artigo, além de outras alterações que foram aplicadas na versão 2016 que superficialmente passam aos olhos, mas em cenários específicos podem fazer total diferença. Os investimentos feitos pela Microsoft na plataforma de dados baseada em SQL Server (tanto On-Premises quanto Cloud Computing, através do Azure SQL Database) colocaram recentemente o produto como líder no quesito segurança, por exemplo, entre todos os fornecedores de sistemas gerenciadores de bancos de dados segundo o Gartner, instituto responsável por diversos testes e benchmarks, entre outras coisas relacionadas à tecnologia da informação. Essa marca apenas comprova a evolução que o SQL Server vem tendo em suas versões mais recentes. Novas funcionalidades, novos conceitos, inúmeras melhorias e novas possibilidades Felipe de Assis felipe.assis@outlook.com / http://felipedba.wordpress.com É especialista SQL Server na DataEX, empresa Gold Partner Microsoft especializada na prestação de serviços para a plata- forma de dados Microsoft. É certificado desde a versão 2005 do produto e atualmente é MCSE Data Platform nas versões 2012/2014. Colabora eventualmente com artigos para a revista SQL Magazine e é um dos palestrantes do SQL Server PASS no Brasil, tendo participado do último SQL Saturday, realizado no mês de novembro de 2015 em Brasília, onde falou sobre SQL Server 2016: as Temporal Tables. Autor destacaram o produto como uma solução corporativa de dados completa e muito abrangente, capaz de ser implementada nos mais diferentes cenários onde a gestão de dados é primordial. 14 SQL Magazine • Edição 145 São inúmeras as situações em que o administrador de dados ou sys- admin não dispõe de métricas eficazes para obter a melhor implemen- tação e performance do banco de dados. Algumas vezes isso decorre até da falta de informação sobre as potencialidades oferecidas pelo sistema. Para os que optaram pelo PostgreSQL em sua organização, apresentamos nesse artigo diversos aspectos técnicos, do hardware ao software, da instalação ao tuning, do sistema gerenciador de banco de dados ao sistema operacional, das formas de instalação à configuração do cluster, visando auxiliar na tarefa de implantação para que o leitor obtenha o melhor resultado em sua instalação. Fique por dentro Trabalhando com o PostgreSQL: implementação do banco de dados Domine os aspectos de uma implementação em ambiente de produção A produção e interpretação de dados relacionados às atividades humanas sempre fomentou a evolução da tecnologia que atualmente cons- titui como um dos pilares da civilização moderna. Na década de 70, no séc. XX, não era diferente. Os setores comercial e industrial da época já manifestavam o in- teresse em obter acesso mais rápido aos seus registros em decorrência do armazenamento convencional de arquivos, bem como reduzir a redundância e inconsis- tência dos dados até que fosse possível compartilhá-los, garantindo certo nível de segurança. Apostando nesse mercado, a IBM (International Business Machines) iniciou suas pesquisas determinada a oferecer uma ferramenta que atendesse à nova demanda. Durante as pesquisas, alguns modelos de bancos de dados foram explorados, dentre eles, o hierárquico, de rede e relacional. Nesse contexto, o modelo de dados deve ser entendido como o conjunto de ferramentas conceituais para a descrição de dados, seus relacionamentos, semântica e restrições que mantenham a consistência. Esses modelos se sub- dividem em três grupos: lógicos baseados em objetos, lógicos baseados em registros e modelo físicos de dados. Retornando à pesquisa da IBM, ela batizou o resultado do projeto inicial com o nome System R, visionário na implementação de sistemas de bancos de dados rela- cionais e na implementação da SQL (Structured Query Language), embora não fosse integralmente fiel ao pro- posto por Codd em suas regras para o modelo relacional. Na época seus autores divulgaram generosamente os detalhes de seu objeto de estudos à comunidade por meio de artigos, o que encorajou dois pesquisadores de Berkley, os cientistas Michael Stonebraker e Eugene Wong, a desenvolverem o próprio projeto de banco de dados relacional. Ambos já haviam arrecadado fun- dos com o projeto INGRES (Interective Graphics Retrieval System) - um banco de dados geográfico que fora desenvolvido para um grupo econômico, implementado por um grupo de estudantes e financiado por órgãos de pesquisa do governo norte-americano - o que garantiria subsídio ao início do projeto. Do projeto "INGRES" derivaram outros sistemas de bancos de dados, tais como SQL Server, Sybase e o próprio PostgreSQL. O projeto Postgres foi iniciado em 1986 na Universidade de Berkley, e sua proposta era suprir as dificuldades impostas pelo Edição 145 • SQL Magazine 15 INGRES, como a manipulação de tipos definidos pelo usuário (hoje conhecidos como objetos) pelo sistema relacional e a de gran- de volume de armazenamento. Através dessa lógica, o nome Post- greSQL veio da aglutinação de "post-INGRES" (após o INGRES). Em 1994 o PostQUEL, que era a linguagem nativa para interpre- tação de consultas implementada pelo Postgres, foi substituída pelo interpretador SQL. Esse foi um marco importante na história do projeto, que após isso passou a ser chamado PostgreSQL. PostgreSQL O PostgreSQL utiliza o modelo lógico baseado em registros atra- vés do modelo relacional. Adicionalmente, o projeto atual permite manipulações diretas análogas ao conceito da orientação a objetos, como herança, polimorfismo e o uso de objetos.É distribuído sob a licença BSD (Berkley Software Distribution), que caracteriza o software como pertencendo ao domínio público e, portanto, livremente personalizável, exigindo somente o reconhecimento autoral para a distribuição do binário, versão original ou alterada. Essa licença é muito permissível e viabiliza o funcionamento de softwares em diferentes formatos de licença trabalhando juntos. Não se pode dizer que a licença BSD é necessariamente melhor que a licença "concorrente", GPL (Gnu General Public License). A questão é que a primeira é menos restritiva. Note que tanto a licença BSD quanto o Postgres tiveram origem no mesmo local, a Universidade de Berkley. A norma ACID, acrônimo para os conceitos de Atomicidade, Consistência, Isolamento e Durabilidade, que representam os critérios básicos para transações e recuperações a falhas, é inte- gralmente implementada pelo PostgreSQL. Essas propriedades asseguram aspectos essenciais para a qualidade das operações, tais como controle de integridade referencial e de concorrência de multi-versão, ou MVCC (Multiversion Concurrency Control), além de ser premissa do modelo relacional. Existem muitas implementações maduras no PostgreSQL, tais como replicação síncrona e assíncrona, segurança SSL(Secure So- cket Layer) e criptografia nativos, utilização de clusters de dados, operação com multithreads, multiplataforma, suporte a 24 idiomas, backups PITR (Point-In-Time Recovery), áreas de arma- zenamento (tablespaces), pontos de salvamento (save- poins), subconsultas e visões atualizáveis, controle de locking, suporte à definição de funções em PL/ PgSQL, Perl, Python, Ruby, dentre outras lingua- gens de programação, foreign data-wrappers, alerta e failover automatizado para ambientes de missão crítica, além de inúmeras características vantajosas. Existe também o interesse crescente da comunidade em apoiar o desenvolvimento do software através de contribuições e patrocínios, como é o caso da Hewlett-Packard, VMware, EnterpriseDB, Fujitsu, Google, Skype e RedHat, para citar algumas. Por isso, o PostgreSQL é frequentemente apontado como ótima opção em projetos que demandem estabi- lidade e facilidade de uso conjugada a boa curva de aprendizado, visto que ostenta a reputação de ser o sistema gerenciador de banco de dados de código livre mais sofisticado disponível. Como dito anteriormente, o PostgreSQL é um projeto comunitário, open source, coordenado pelo PostgreSQL Global De- velopment Group, e que não limita o escopo de suas capacidades, recursos e funcionalidades aos usuários, ou seja, está totalmente à disposição para qualquer um, a qualquer momento. Um aspecto que corrobora o alinhamento às demandas mais atuais, como a disponibilidade de serviços em nuvem e a escala- bilidade vertical, é o fato de que o PostgreSQL não é somente re- lacional: é multi-paradigma, agregando também funcionalidades No-SQL (Not Only SQL). Aqui temos um bom exemplo de como o uso de extensões pode ampliar a gama de possibilidades do sistema. O PGXN (PostgreSQL Extension Network) é um sistema de distribuição central para bibliotecas open source de extensão para o PostgreSQL. De forma similar, também existe o pgFoundry, que reúne diversos projetos relativos ao PostgreSQL. A maioria das linguagens de programação modernas proveem interface nativa para conexões com o PostgreSQL. Como alterna- tiva em caso de ausência, há disponibilidade de acesso através da interface ODBC (Open Database Connectivity), que permitiriam acesso por produtos da Microsoft como Access e Excel. Aplicações desenvolvidas em Java contam com o suporte oferecido pelo driver JDBC (Java Database Connectivity). Já para o PHP, teríamos o PDO (PHP Data Objects) como opção. O acesso aos dados ocorre de vá- rias formas, podendo ser através de linha de comando (psql), pelo código SQL embutido na aplicação/framework, por cliente gráfico (pgAdmin III) ou via navegador web (phpPgAdmin), por exemplo. Arquitetura e versionamento Outro ponto forte do projeto é a sua arquitetura cliente-servidor (ver Figura 1), fazendo com que o acesso aos dados seja distribuído a clientes que não os acessam diretamente mas via processo no servidor (postmaster), mesmo que o cliente esteja acessando na mes- ma máquina onde o serviço está instalado. A seguir, uma forma simplificada de como esse acesso se dá, através de TCP/IP (Transfer Control Protocol/Internet Protocol) ou Internet, via tunelamento. Figura 1. Arquitetura cliente-servidor implementada no PostgreSQL Trabalhando com o PostgreSQL: implementação do banco de dados 16 SQL Magazine • Edição 145 A Tabela 1 indica algumas das limitações para as capacidades de armazenamento. Antes de iniciarmos nossas considerações sobre a instalação do PostgreSQL, explicaremos o controle de versão praticado em suas distribuições. Suas atualizações encontram-se disponíveis no site oficial do PostgreSQL, e o número da versão já nos indica, de imediato, o que podemos encontrar. Esse número deve ser decomposto em duas partes: a primeira delas nos dará a versão majoritária e a segunda, a versão minoritária. A versão majoritária representa um marco evolutivo do sistema, composto por novas funcionalidades e revisões e é identificada pelos dois primeiros números da versão. Já a versão minoritária se refere a atualizações de segurança e correções de bugs, sinalizada pelo último número da versão. O clico de vida da versão majoritária é de cerca de cinco anos, com versões lançadas a cada ano, enquanto isso, disponibili- zações de atualizações de versões minoritárias são lançadas quase que mensalmente. A inclusão de novas funcionalidades costuma ser amplamente discutida em listas internacionais, como a wiki do Roadmap, debatidas e aprovadas durante a PgCon, evento que ocorre no Canadá anualmente. Por exemplo, à época da publicação desse artigo, a versão estável mais atual disponível era a 9.5.3. Isso significa que tínhamos à dis- posição todas as funcionalidades da versão 9.5 e também estávamos à terceira revisão de segurança e correções de erros para a série. Atualizações minoritárias do cluster nunca mudam o formato de armazenamento interno e são sempre compatíveis. É possível apenas substituir os executáveis enquanto o sistema estiver pa- rado e reiniciar o cluster após isso. Por exemplo, a versão 8.42 é compatível com a 8.4, 8.4.1 e 8.4.6. As atualizações minoritárias não alteram o diretório de dados e são simples assim. Contu- do, atualizações majoritárias sujeitam o PGDATA, diretório de dados anteriormente citado, a alterações. Isso torna o processo mais sensível. O método tradicional para esse procedimento é a realização do dump, utilizando o pg_dump ou pg_dumpall a partir da versão mais recente, a fim de compatibilizar os recursos, na versão anterior, com consequente restore na versão mais atual. No entanto, essas não são as únicas opções seguras de atualização A partir da série 8.4.x foi possível migrar um cluster da série 8.3.x para a mais recente, de forma direta, sem a necessidade de dump-restore, através do aplicativo pg_migrator, que mais tarde teve seu nome alterado para pg_upgrade e atualmente encontra- -se estável. Sua performance é notadamente mais rápida que a forma tradicional, além de evitar que espaço adicional em disco seja necessário para a migração. Também é possível usar certos métodos de replicação, como Slony, para criar um servidor de espera (stand by) com a versão atualizada do PostgreSQL. Isso é possível porque Slony suporta replicação entre diferentes versões majoritárias do PostgreSQL. A espera pode estar no mesmo computador ou em um computador diferente. Uma vez que tenha sincronizado com o servidor master (executando a versão mais antiga do PostgreSQL), você pode alter- nar mestres e encerrar a instância de banco de dadosmais antigo. De forma geral, os itens atualizados são os relativos à adminis- tração, SQL, API da biblioteca, catálogos do sistema e o servidor API (Application Programming Interface) da linguagem C. É altamente aconselhável que sejam consultadas as notas de lançamento da versão antes de proceder as atualizações. Mesmo que o processo seja crítico, é melhor realizá-lo do que expor seus dados a vulnerabilidades já conhecidas. Caso o fornecedor do seu sistema operacional não forneça atualizações de segurança da série utilizada por você, considere utilizar os pacotes de atualização disponíveis no site oficial do PostgreSQL. No entanto, se não existir pacote disponível de qual- quer forma, considere compilar o PostgreSQL diretamente no seu ambiente com as suas preferências e otimizações. Recursos de hardware Até esse ponto já abordamos vários aspectos que justificariam a escolha do PostgreSQL como sistema gerenciador de bancos de dados como opção. Agora iremos abordar questões relacionadas ao hardware. Esse é um aspecto central que pode conduzir ao su- cesso ou fracasso da implementação. Prioritariamente, devemos conhecer os discos e controladoras, memória RAM (Random Access Memory), CPU (Central Processing Unit) e rede. Nos adiantando um pouco mais, inclusive a opção pelo sistema de arquivos é deter- minante. Torna-se obrigatório avaliar informações técnicas tais como estatísticas, relatórios de benchmarks e consultar a opinião de pessoas cuja experiência poderia contribuir. Alta performance em bancos de dados só é obtida com domínio sobre o hardware. Hard Disk Por ser o recurso mais consumido pelo SGBD, iniciaremos nossa discussão pelos discos e controladoras. Ao escolher um HD (Hard Disk), você certamente se atenta à sua capacidade de armazena- mento, à sua interface e, provavelmente, ao tamanho do cache, afinal, essas são as informações que acompanham a descrição do produto. Mas há outros parâmetros ligados ao desempenho do dispositivo que também devem ser observados. Os mais conhe- cidos são: seek time, latency time e access time. O seek time, em tradução livre, tempo de busca, normalmente indica o tempo que a cabeça de leitura e gravação leva para se deslocar até uma trilha do disco ou mesmo de uma trilha a ou- tra. Quanto menor esse tempo, melhor o desempenho, é claro. Esse parâmetro pode ter algumas diferenciações, sendo que sua divulgação varia de fabricante para fabricante. Latency time é a Tamanho máximo de um banco Ilimitado Tamanho máximo de uma tabela 32 TB Tamanho máximo de uma linha 1,6 TB Tamanho máximo de um campo 1 GB Tamanho máximo de linhas por tabela Ilimitado Tamanho máximo de colunas por tabela De 250 a 1.600, dependendo dos tipos de dados utilizados Tamanho máximo de índices por tabela Ilimitado Tabela 1. Capacidade de armazenamento Edição 145 • SQL Magazine 17 medida que indica o tempo necessário para que a cabeça de lei- tura e gravação se posicione no setor do disco que deve ser lido ou mesmo gravado. Esse parâmetro sofre influência do tempo de rotação dos discos (atualmente de 5.400, 7.200 e 10.000 RPM / Rotações Por Minuto) e também é informado em milissegundos. O access time, corresponde a um cálculo que combina os parâme- tros de latency time e seek time. Em termos práticos, o access time indica o tempo necessário para se obter uma informação do HD. Novamente, quanto menor esse tempo, melhor. RAID Além de meramente armazenar dados, soluções de armazena- mento têm que prover acesso à informação de maneira eficiente, em tempo hábil e, dependendo do caso, oferecendo algum tipo de proteção contra falhas. É nesse ponto que os sistemas RAID (Redundant Array of Independent Disks) apresentam sua utilidade: segurança, capacidade e desempenho. Dessa forma, um arranjo ou sistema RAID é a aglomeração física de HDs representados por uma unidade lógica de armazenamento. Podemos entender a unidade lógica como o dispositivo que o sistema operacional identifica como apto ao armazenamento secundário, independen- temente da quantidade de dispositivos em uso e da tecnologia do disco. Os benefícios são muitos. Para mencionar alguns, temos: • Em caso de falha de um dos discos, os dados existentes nele não serão perdidos, pois podem ser replicados em outra unidade (redundância); • É possível elevar o volume de armazenamento a qualquer mo- mento com a adição de mais HD; • O acesso à informação pode se tornar mais rápido, pois os dados são distribuídos em todos os discos; • Dependendo do caso, há maior tolerância a falhas, pois o sistema não é paralisado se uma unidade parar de funcionar; • Um sistema RAID pode ser mais barato que um dispositivo de armazenamento mais sofisticado e, ao mesmo tempo, oferecer praticamente os mesmos resultados. Múltiplos discos RAID separados por tabelas e índices muito acessados são importantes. Outro ponto importante é optar por controladoras RAID que disponham de baterias. Isso viabiliza o uso de seu cache de forma mais eficiente e segura. Há disponi- bilidade de diferentes tipos de arranjos que visam atender com flexibilidade as necessidades dos administradores de dados e sysadmins em RAID. Não serão citadas aqui as opções porque fogem ao escopo do artigo. Memória RAM No caso da análise dos requisitos para a aquisição de memória temos um cenário mais enxuto: quanto mais, melhor. Porém, como sempre há uma reserva, tem-se que devemos dar preferência às que possuem correção de erro com registradores, ou ECC (Error Correction Check). A principal característica desse tipo de memória em relação às comuns é a presença de bits adicionais. Contudo, vale ressaltar que a correção de erro não ocorre semente na me- mória RAM: em discos rígidos, mídias digitais, CD, DVD, etc. A estratégia é simples: “o que foi escrito foi exatamente o que foi gravado?”. Caso sim, ok; caso contrário, uma nova tentativa de resposta positiva é feita. Em caso de sucessivos erros de integri- dade da informação, o setor defeituoso é marcado como badblock e isolado no sistema de arquivos para que outras tentativas de IO (input-output) não ocorram ali. Em tempo, caso esteja trabalhando em um sistema OLTP (On-Line Transaction Processing), saiba que a demanda por memória principal não será tão grande quanto em um sistema OLAP (On-Line Analytical Processing), onde a produção de relatórios, estatísticas e transformações podem exigir mais que em um ambiente de transações rápidas, que é o caso do primeiro. CPU A CPU é o componente do hardware que, em última instância, realiza as tarefas solicitadas pelos programas. Existe um proces- so denominado escalonamento, ou scheduling, responsável por verificar qual processo é mais prioritário e possui menor tempo de realização, possibilitando a execução de processos concor- rentes e viáveis. O escalonamento com múltiplos processadores não é linear. Nesse caso, poucos processadores rápidos são mais eficientes que muitos processadores lentos. Contudo, o banco de dados tira proveito de ambientes multiprocessados através da implementação de processadores de 64 bits e clock elevado. Para o PostgreSQL, a visibilidade entre processos é importante, portanto, quanto mais núcleos no mesmo local, melhor será a comunicação entre eles. Por isso, processadores com múltiplos núcleos são muito bem recebidos. Memória compartilhada e semáforos Apesar de admitir que o PostgreSQL busca atender a uma vasta gama de necessidades, partiremos do princípio de que o leitor visa usá-lo em ambientes de grande porte. Nesse caso, é razoável imaginar que, a princípio, o software esbarre em alguns limites do sistema operacional. Semáforos - utilizados como método de sincronização entre processos - e memória compartilhada - área de memória em que um contexto processual pode trabalhar - frequen- temente necessitamde ajustes. Alguns outros pontos como limite de processos por usuário, quantidade de arquivos abertos simul- taneamente por processo e a quantidade de memória disponível para esse podem afetar o funcionamento do PostgreSQL, já que ele utiliza a memória compartilhada e semáforos para realizar a comunicação inter processos baseando-se no modelo System V IPC (lê-se "System Five") porque a execução de um processo pressupõe por parte do sistema operacional, entre outras coisas, a criação de um contexto de execução próprio que, de certa forma, abstrai o processo dos componentes reais do sistema. Devido a essa virtu- alização dos recursos, o processo não tem conhecimento acerca dos outros processos e, como tal, não consegue trocar informação. O cache do PostgreSQL é conhecido como shared buffer e fica armazenado em segmentos de memória compartilhada pelos processos do servidor, ou back-ends. Uma forma de visualizá-los é utilizando a ferramenta ipcs. Trabalhando com o PostgreSQL: implementação do banco de dados 18 SQL Magazine • Edição 145 Os limites de memória compartilhada ajustáveis ocorrem através dos parâmetros de kernel, com o Linux: • SHMMNI: representando o número máximo de segmentos de memória compartilhada no sistema operacional; • SHMMAX: tamanho máximo de um segmento de memória compartilhada. Dependendo da instalação do sistema operacional, esse valor pode vir baixo. A configuração para esse valor deve estar alinhada ao que for definido em alguns outros parâmetros do PostgreSQL, como shared_buffers, max_connections, autova- cuum_max_workers, max_prepared_transactions, max_locks_per_tran- sactions e wal_buffers; • SHMALL: significa o tamanho total de memória compartilhada pelo sistema operacional. É importante disponibilizar espaço o suficiente para que toda a memória compartilhada e mais os back- -ends caibam nesse parâmetro. Como mencionado anteriormente, os semáforos são responsáveis pela sincronização inter processos e mostram-se particularmente úteis quando o número de conexões se eleva demasiadamente. A seguir, temos alguns parâmetros relacionados a eles: • SEMMNI: número máximo de conjuntos de semáforos. A re- gra para o PostgreSQL é que seja, no mínimo, (max_connections + autovacuum_max_workers) / 16; • SEMMSL: número máximo de semáforos por conjunto. É considerada uma boa prática reservar pelo menos 17 semáforos por conjunto; • SEMMNS: número máximo de semáforos no sistema operacional, que deve ser SEMMNI * SEMMSL; • SEMOPM: número máximo de operações por semáforo quando da chamada do sistema semop(). Journaling Existe também outro aspecto que foge ao escopo das confi- gurações nativas do PostgreSQL e diz respeito aos sistemas de arquivos do sistema operacional, que é a estrutura lógica uti- lizada pelos sistemas operacionais para organização do arma- zenamento. A relevância desse tema nesse contexto se mostra ao considerarmos que estamos lidando com volumes cada vez maiores de dados cuja complexidade de manutenção aumenta proporcionalmente, sendo que a escolha de um file system em detrimento de outro, mais adequado, poderia comprometer o desempenho e a confiabilidade do cluster. Há consenso entre especialistas em bancos de dados que o suporte a journaling é uma vantagem, o que justifica a sua popularidade e que, de forma geral, lida melhor com prevenção a perdas de dados e a recuperação em caso de falha. Journal, também conhecida como "registros de log", de onde deriva o termo journaling, é uma pequena área reservada no sistema de arquivos que armazena metadados sobre arquivos a fim de oferecer uma recuperação rápida e eficiente. No entanto, há um ponto a considerar: a ge- ração de overhead, já que, nesse cenário, o processo de gravação do arquivo ocorre da seguinte forma: • Registro de blocos de metadados; • Efetuar a escrita no bloco; • Registrar metadados do bloco. Contudo, em caso de falha entre as etapas, podem ocorrer incon- sistência das informações onde existiriam blocos marcados como utilizados, porém, vazios. Da mesma forma, blocos de metadados preenchidos, sem referência a qualquer dado. Isso não inviabiliza a recuperação, todavia, a torna mais lenta. Quanto ao overhead, pode ser considerável, visto que pode gravar o mesmo dado duas vezes. Por esse motivo, existem modos de operação de journaling. São eles: • Journal: persiste todas as alterações. É o mais lento dos três modos, mas é o que possui maior capacidade de evitar perda de dados; • Ordered: grava somente mudanças em arquivos de metadados, bem como as atualizações no arquivo de dados antes de fazer as mudanças associadas ao sistema de arquivos. É o modo padrão nos sistemas de arquivos ext3 (abordado posteriormente). Os modos journal e ordered são os mais indicados para o armazenamento de dados brutos; • Write-back: semelhante ao ordered, mas utiliza o processo de escrita do sistema de arquivos em uso para gravação. É o mais rápido journaling ext3 com o menor overhead, o que o torna forte candidato a ser utilizado de forma segura com pg_xlog porque resíduos de armazenamento podem ser facilmente removidas pelo mecanismo WAL (Write-Ahead Logging). A seguir listamos alguns dos sistemas de arquivos que possuem o journaling: - EXT3: desenvolvido pela Red Hat, é basicamente a extensão do EXT2 com a adição do journaling. E isso é uma vantagem porque o torna compatível com seu antecessor. Utiliza endereço de 32 bits e blocos de até 8 kB. O tamanho máximo dos arqui- vos e da partição são determinados pelo tamanho dos blocos durante a formatação. Na Tabela 2, vemos a representação dessa distribuição. Por padrão, o tamanho do bloco é definido automaticamente mas também pode ter seu valor configurado manualmente usando o parâmetro “-b” do comando mkfs.ext3 (usado para for- matar as partições EXT3 no Linux), como em “mkfs.ext3 -b 2048 /dev/hda1” (cria blocos de 2 KB) ou “mkfs.ext3 -b 4096 /dev/hda1” (para blocos de 4 KB). A implementação do recurso no EXT3 é feita na camada “Journaling Block DeviceI” com o propósito de alcançar qualquer tipo de dispositivo com sua tecnologia. O EXT3 dá suporte a “write barries” com algumas limitações, por exemplo, não funcionar em RAID de software e despejar o cache inteiro da controladora e não somente o arquivo. Tamanho do bloco Tamanho máximo da partição Tamanho máximo dos arquivos 1 kB 2 TB 16 GB 2 KB 8 TB 256 GB 4 KB 16 TB 2 TB 8 KB 32 TB 2 TB Tabela 2. Capacidade de armazenamento no EXT3 Edição 145 • SQL Magazine 19 A utilização do EXT3 é ampla em distribuições menos re- centes e o sistema de arquivos é reconhecido como estável, contudo, a tendência é que o EXT3 seja lentamente substi- tuído pelo EXT4. - EXT4: tornou-se o padrão atual por ser aceito como a evo- lução natural do EXT3. Implementa corretamente a questão dos “write barriers”, reserva de blocos contíguos para alocação e oferece outras melhorias para reduzir fragmentação de ar- quivos. Possui desfragmentação on-line – que é o equivalente a desfragmentar os arquivos enquanto estão sendo alocados, verificação de integridade do journal (journal checksums) e compatibilidade com as versões da família EXTFS (ext2, ext3 e ext4). Para usar o ext4 em uma nova partição, basta que você formate ela com o comando mkfs.ext4. Por exemplo, vamos considerar que a partição /dev/hda6 vai ser formatada para usar o novo file-system através do comando mkfs.ext4 / dev/hda6. Para usar o ext4 em uma partição que já existe e que foi formatada previamente com o ext3, basta que ela seja montada usando o ext4dev no tipo do file-system conforme o comando mount -t ext4dev /dev/hda1 /mnt/hda1. Na Tabela 3 verificamos algumas limitações de armazenamento do EXT4. Tamanho do bloco Tamanho máximo da partição Tamanho máximo dos arquivos 4 kB 1 EB 16 TB Tabela 3.Capacidade de armazenamento no EXT4 - XFS: sistema de arquivos 64 bits compatível com os de 32 bits, de alto desempenho e geralmente escalável. Desenvol- vido inicialmente para sistema operacional IRIX mas como é open source, logo foi migrado para o mundo GNU/Linux. Tam- bém possui desfragmentação on-line. Um recurso interessante para esse sistema de arquivos é o redimensionamento on-line, realizado pelo utilitário “xfs_growfs”. Contudo, o redimen- sionamento é no único sentido de aumentar o volume, nunca diminuí-lo, sendo apropriado para lidar com grandes volumes de dados. É pouco popular no Linux, porém é admitido como o mais adequado para bancos de dados, pois apresenta uma melhor implementação do journaling e de write barriers, envian- do somente o necessário para o cache. Na Tabela 4 verificamos algumas limitações de armazenamento do XFS. Especialistas o apontam como o mais recomendado para PostgreSQL. Esse sistema é divido em três partes: a) Seção de dados: contém informações sobre ponteiros para extensões ou ponteiros para listas de extensões; b) Seção de log; c) Seção de tempo real. Tamanho do bloco Tamanho máximo da partição Tamanho máximo dos arquivos 512 b a 64 kB 8 EB, em 32 b 8 EB, em 64b 16 TB, em 32 b Tabela 4. Capacidade de armazenamento no XFS Tuning do pg_xlog O pg_xlog é o local onde se armazenam os logs de transação do PostgreSQL. Em um banco com caráter fortemente transacional, é uma otimização obrigatória separá-lo desse contexto, uma vez que nesse ponto a carga de entrada e saída é intensa e provocada pelas seguidas sincronizações do fsync. Para isso, basta separar o pg_xlog em outro dispositivo e referenciá-lo através de um link simbólico para o ponto de montagem. Podemos obter um resul- tado equivalente se utilizarmos a opção –X no initd no momento da criação do cluster. Dê preferência por alocar o diretório do pg_xlog em armazena- mento não-volátil, porque a perda de dados nesse arquivo pode impedir a inicialização do cluster. Sendo otimista, no mínimo as informações das últimas transações. Portanto, não é exagero su- gerir que se utilize de armazenamento confiável em um esquema de redundância para esse diretório. Pool de conexões: o pgBouncer Ainda falando sobre performance, é oportuno discutir sobre o pooler de conexões. Esse conceito está relacionado à capacidade de disponibilizar um cache de conexões de bancos de dados que possam ser reutilizadas. Esse recurso tem o objetivo de garantir o desempenho das aplicações uma vez que abrir e fechar conexões, notadamente em sistemas distribuídos, é um processo custoso. Mas se todas as conexões do depósito de conexões estiverem em uso, o sistema gerenciador de banco de dados deve ser capaz de fornecer uma nova. Como o PostgreSQL é multiprocesso e cada processo representa um back-end, se executarmos múltiplos processos no SGBD teremos a mesma quantidade no sistema operacional. O problema disso é que, via de regra, processos, além de exigirem mais trabalho do escalonador, costumam des- perdiçar recursos. Por isso, um grande número - superior a 200 - de conexões di- retas deve ser evitado. O número máximo de conexões simultâ- neas que o PostgreSQL pode suportar é definido pelo parâmetro max_connections. A memória compartilhada para cada conexão é de aproximadamente 400 bytes. Ainda há um parâmetro que permite acesso reservado a administradores para o caso do pool estar em sua capacidade máxima e o mesmo precisar acessar, é o superuser_reserved_connections. Como alternativa às aplicações que não gerenciam de forma independente o pool de conexões, existem dois gerenciadores externos: pgPool2 e pgBouncer, que atendem muito bem a ambientes em que existem muitas sessões simultâneas que executam tarefas breves. O acesso via gerenciador externo deve ser transparente e diminuir o impacto do custo de abertura e fechamento de sessões. Esse gerenciador de conexões não requer muitos recursos de hardware. Um servidor virtual com 4 cores, 8 GB de memória e 30 GB de disco, é suficiente para a implementação do pgBouncer. Contudo, o throughput de rede deve ser monitorado pois esse é o gargalo. OpgBouncer funciona em três modos de operação: • Session: considerado o mais sutil entre as opções. Sua caracte- rística é fornecer uma conexão do banco sob demanda durante Trabalhando com o PostgreSQL: implementação do banco de dados 20 SQL Magazine • Edição 145 toda a permanência do usuário. Após isso, a conexão é devolvida ao pooler. Cabe mencionar que ele não traz ganhos significativos; • Transaction: nesse modo, a solicitação só é atendida durante uma transação. Quando o gerenciador percebe o fim da transação, a sessão é encerrada e a conexão é devolvida. Esse é sugerido como modo de operação para o PostgreSQL; • Statement: oposto ao modo session. Aqui, a conexão é atribuída a cada comando, isoladamente, o que inviabiliza uma transação com múltiplas declarações, o que o torna pouco prático. Como os outros, a conexão retorna ao pooler no final. É possível configurar o pgBouncer através do arquivo “pg- bouncer.ini”, geralmente alocado em /etc/pgbouncer/pgbouncer.ini. O leitor perceberá que ele é divido nas seções database e pgbouncer. O elemento básico é um parâmetro configurado no formato chave- -valor. Podemos observar na Tabela 5 as opções de configuração para a seção databases. dbname Nome do banco de dados do servidor dbhost Endereço IP do servidor port Porta TCP onde a requisição será atendida user Usuário do servidor password Senha de autenticação para o usuário pool_size Tamanho máximo do pool para esse banco de dados. O padrão é o default_pool_size connect_query Consulta executada entre o estabelecimento da conexão e o início da sessão client_encoding Codificação de caracteres utilizada datestyle Informa o estilo da data a ser utilizada timezone Informa a zona horária a ser utilizada Tabela 5. Parâmetros de configuração de conexão do pgBouncer Já na seção pg_bouncer, temos à disposição os parâmetros gerais para ajustes apresentados na Tabela 6. Temos igualmente à disposição opções administrativas e de visu- alização de logs que auxiliam na implementação do gerenciador de pooler e no monitoramento de eventos. Na Tabela 7 identificamos os mais relevantes. Enquanto gerenciador do pool de conexões, um sistema deve prover um bom monitoramento das filas de acesso entre outras verificações. O pgBouncer dispõe de alguns parâmetros que aten- dem a essa demanda que são explicados brevemente na Tabela 8. Monitoramento com pgBouncer Acessos pelo console do pgBouncer se relacionam ao controle e gerenciamento de operações. O monitoramento é feito através do comando show, que exibe estatísticas sobre o uso do pool. Vemos as opções disponíveis para argumentos no comando show na Listagem 1. Os colchetes representam o conjunto de opções, enquanto o caractere pipe indica a exclusividade entre elas. Ou seja, dentre as possibilidades, apenas uma pode ocorrer em certo momento. listen_addr Endereço TCP/IP onde o servidor atenderá as solicitações. listen_port Porta TCP/IP onde o servidor está atendendo. unix_socket_dir Diretório do soquete de domínio. log_file Caminho do arquivo de log. pidfile Caminho do arquivo que informa o PID, ou id do processo. auth_type Tipo de autenticação suportada. Pode ser: trust, any, plain, crypt ou md5. pool_mode Define quando uma conexão pode ficar disponível a ou- tros acessos. São eles: session, transaction e statement. max_client_conn Número máximo de conexões cliente aceitas. Tecnica- mente, esse valor deve corresponder a max_client_conn + (max_pool_size * num_databases * num_clientes). default_pool_size Define quantas conexões são permitidas por usuário ou banco. reserve_pool_size Se refere à quantidade de conexõesadicionais são permiti- das a usuário ou banco. reserve_pool_ti- meout Limite de tempo para o atendimento a uma solicitação. Caso o cliente não seja atendido, o pgBouncer reserva uma conexão para ele. server_round_ro- bin Visto que as conexões são reutilizadas e solicitadas a partir de uma pilha, significa que poucas conexões reais são utili- zadas e que serão distribuídas de forma mais uniforme. Tabela 6. Parâmetros de configurações gerais do pgBouncer. syslog Habilita o recurso na plataforma Windows, onde é utilizado. syslog_facility Para esse parâmetro, “facilidade” se refere ao tipo de programa que está enviando as informações para o syslog. log_connections Registra as conexões realizadas com sucesso. log_disconnections Registra eventos de conexões desfeitas e suas causas. log_pooler_errors Registra as saídas de erro que o pgBouncer envia para os clientes. admin_users Parâmetro administrativo que identifica os usuários que têm permissão para se autenticar no console do gerenciador de pooler e efetuar comandos. stats_users Outra opção administrativa que permite a identificação dos usuários que podem obter acesso ao pgBouncer através do console mas que só podem realizar coman- dos read-only. Tabela 7. Parâmetros de monitoramento do pgBouncer server_login_retry Tempo de espera necessário para que uma nova tentativa de login seja realizada em caso de falha anterior. query_timeut Consultas que atingem esse limite sem obter o resul- tado de seu processamento são canceladas. client_idle_timeout Se a conexão do cliente estiver ociosa mais do que o tempo informado por esse parâmetro, ela será fecha- da. O valor mínimo para esse parâmetro deve ser o tempo total de conexão. client_login_timetout Se o cliente obter a conexão mas não estabelecer uma sessão dentro desse limite, terá seu acesso cancelado. autodb_idle_timeout Tempo de espera antes de solicitar o fechamento de certa conexão utilizada por um pool genérico. Tabela 8. Parâmetros de gerenciamento de pool do pgBouncer Edição 145 • SQL Magazine 21 Já o controle pode ser realizado através das opções: • Pause: o pgBouncer utiliza a estratégia de tentar desconectar todos os servidores após a finalização normal de as consultas; • Suspend: todos os buffers são escritos e o pgBouncer para de atender a solicitações. Assim como o pause, aguarda que todas as consultas tenham terminado normalmente; • Resume: retorno às rotinas após a execução de um comando pause ou suspend; • Shutdown: termina o processo do pgBouncer; • Reload: faz a recarga do arquivo de configuração do pgBouncer. Instalação do PostgreSQL Existem diferentes formas de instalar o PostgreSQL. Agora, passaremos da teoria à prática. O primeiro ponto de interrogação que nos vem à mente é: “qual pacote usar? ”. Para tornar mais explícita nossa abordagem e dar espaço à análise, os elencamos conforme segue: • Fornecido pelo fabricante do sistema operacional; • Disponível em postgresql.org; • Compilado a partir do código-fonte. Dito isso, vamos começar pelo primeiro. Geralmente essa é a forma mais rápida e fácil de instalação pois o PostgreSQL já vem com todas as flags desejadas pelo fabricante e integrando-se ao seu sistema de diretórios. Não é necessariamente vantajoso porque dificulta a personalização das flags e por ter caráter de versio- namento com foco nas versões minoritárias – o que geralmente é definido pelo fabricante –, deixando de fora as atualizações majoritárias. Mas a possibilidade de um suporte personalizado que suplante essas dificuldades para o usuário passa a ser uma vantagem. Mais uma possibilidade é visitar o site oficial do PostgreSQL e escolher e realizar o download do pacote mais apropriado para seu sistema operacional. Os pacotes disponíveis são mantidos pela comunidade de desenvolvimento do projeto e não ne- cessariamente abrangerão todos os sistemas operacionais ou todos os forks e versões dos mesmos. Para usuários do Windows e Mac OS X, essa é a única via. Os pacotes são compilados com as configurações de flags acordadas pela comunidade. Para as distribuições GNU/Linux mais recentes baseadas em Debian e Red Hat, respectivamente, estão disponíveis os repositórios para apt e yum. Os repositórios também contam com todas as versões disponibilizadas do PostgreSQL, além dos módulos contrib e ferramentas que já citamos aqui, como pgAdminIII e Slony. Por- tanto, utilize as instruções do seu gerenciador de pacotes em função da sua escolha: • No Debian: como superusuário, apt-get install postgresql; • No CentOS: como superusuário, yum install postgresql-server; • No Windows e Mac: utilize o instalador próprio. Por fim, resta a compilação do código-fonte que é uma técnica que apresenta maior grau de dificuldade e requer certa familiari- dade com desenvolvimento. É necessário ter instalado o compila- dor da linguagem C e o código-fonte das bibliotecas necessárias: zlib e libreadline. Certamente essa é a opção que permite maior liberdade de configuração e, por isso, possibilita a instalação de qualquer versão disponível do PostgreSQL. Para encerrar a discussão, você é que precisa entender a neces- sidade do seu negócio e optar. Contudo, é importante considerar alguns pontos que podem guiar o leitor nessa decisão. Dê pre- ferência ao pacote disponibilidade pelo fabricante, caso esteja disponível e atenda às suas necessidades. Caso seu fornecedor não ofereça o pacote, busque-o na comunidade. Contudo, se ambos os cenários não lhe agradam, siga pelo caminho da compilação. Mas para isso, esteja consciente de que os itens a seguir se esgotaram, na seguinte ordem: 1) O sistema operacional não possui os pacotes; 2) O pacote oferecido pelo sistema não está na versão que você precisa; 3) Não há oferta pelo pacote que você precisa no site oficial; 4) Você precisa alterar flags de compilação; 5) Você precisa alterar o código-fonte. Instalação a partir de repositórios no Debian A partir de agora iniciaremos os processos de instalação do PostgreSQL através de repositórios, que são locais, remotos ou não, de onde as dependências das instalações e atualizações podem buscar automaticamente arquivos de forma a facilitar a instalação e tornar o processo mais confiável. Essa é uma conve- niência pois, dessa forma, podemos incrementar nosso sistema operacional de acordo com nossa vontade. As informações sobre os pacotes ficam armazenadas em listas que devem ser sincroni- zadas periodicamente. Partimos do princípio de que temos em mãos uma instalação GNU/Linux cuja lista de repositórios está desatualizada. Em um terminal, como root, editamos o arquivo sources.list. Considerando a Listagem 2 como referência, verificamos a instalação utilizando o apt-get na primeira linha. Na linha 02 temos a adição do reposi- tório para a instalação do PostgreSQL. Ele contém as referências de onde serão baixados os pacotes. Adicionamos as linhas 03 e 04 e salvamos a alteração. Essas inclusões dizem respeito à importa- ção da chave de autenticação do repositório e permitem instalar os pacotes. Após isso, no terminal, executamos as linhas de 05 a 07. Com isso, temos as dependências mapeadas para iniciarmos a instalação do PostgreSQL. Listagem 1. Parâmetros para a cláusula show. SHOW { STATS| SERVERS| CLIENTS| POOLS| LISTS| USERS| DATABASES | FDS| CONFIG } Trabalhando com o PostgreSQL: implementação do banco de dados 22 SQL Magazine • Edição 145 A sequência de comandos representada na Listagem 3 inicia o processo de instalação do PostgreSQL em sua linha 01 e, após isso, verificamos a localização dos arquivos da instalação no diretório referido
Compartilhar