SQL Magazine 13665676577592

Sumário Conteúdo sobre Boas Práticas

06 – Um modelo OLAP para visualização de informações de builds automáticos [ Mauro Pichiliani ]

Conteúdo sobre Boas Práticas

16 – Manipulação de arquivos de dados e log no SQL Server [ Robson Moraes ]


22 – Dominando os índices columnstore [ Weslley Moura e Emerson Silva ]


28 – Monitoramento automatizado o SQL Server [ Jonatas dos Anjos ]


36 – Paralelismo no SQL Server [ Luciano Moreira ]

u

e s ê

ac F eedb a k

D

o

s

Artigo do estilo Solução Completa

44 – Trabalhando com MemSQL [ Rodrigo Salvo ]

Artigo no estilo Solução Completa

52 – Trabalhando com o MySQL Cluster [ Airton Lastori ]

Artigo no estilo Solução Completa

61 – PostgreSQL distribuído com PgPool-II [ Luis Eduardo de Oliveira Floriano ]

b r e e s t a

e

i d ç o ã

Dê seu feedback sobre esta edição! A SQL Magazine tem que ser feita ao seu gosto. Para isso, precisamos saber o que você, leitor, acha da revista! Dê seu voto sobre esta edição, artigo por artigo, ar tigo, através do link: www.devmedia.com.br/sqlmagazine/feedback

Assine agora e tenha acesso a todo o conteúdo da DevMedia: www.devmedia.com.br/mvp

136ª Edição - 2015 - ISSN 1677918-5 - Impresso no Brasil

EXPEDIENTE Editor RodrigoOliveira Spínola([email protected]) Subeditor Eduardo Oliveira Spínola Consultor Técnico Diogo Souza ([email protected]) Jornalista Responsável Kaline Dolabella - JP24185 Capa e Diagramação Romulo Araujo Distribuição FC Comercial e Distribuidora S.A Rua Teodoro da Silva,907 Grajaú - RJ - 206563-900

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 [email protected] – 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.

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

[email protected]

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.

Um modelo OLAP para visualização de informações de builds automáticos

Um modelo OLAP para visualização de informações de builds automáticos Veja neste artigo um modelo multidimensional para visualização de informações geradas pelo build automático de um projeto Java

O

desenvolvimento de software profissional conta com diversas etapas. Dentre as principais, podemos destacar o processo de build, ou seja, o passo onde é necessário realizar diversas ações para produzir uma versão do software. Este processo pode ser composto de ações automáticas ou manuais como, por exemplo, compilação, empacotamento, realização de testes, checagem de dependências e outras atividades necessárias para completar o build. É comum que empresas preocupadas com a qualidade de desenvolvimento e produção de software invistam em tornar o processo de build automático e adequado para gerar todos os artefatos necessários para lançar uma nova versão do software. Isso implica em diversas verificações, chamadas de softwares externos, checagem de padrões de desenvolvimento, geração de documentação e tarefas de deploy que, se realizadas manualmente, certamente consumiriam muitos recursos humanos e possuiriam alta probabilidade de falhas. Portanto, a maioria das equipes de desenvolvimento tem como uma das prioridades iniciais de seus projetos a automação do processo de build. A execução automática de processos de build gera diversos dados importantes que devem ser analisados tanto pelos membros técnicos da equipe quanto pelo gerente de projeto, pois esta análise vai fornecer indicadores de como anda a qualidade do projeto e outros aspectos relevantes. Contudo, ainda são poucos os sistemas que fornecem estas informações produzidas pelo processo de build automático em um formato analítico que suporte tomadas de decisão e permita o acompanhamento quantitativo e qualitativo do desenvolvimento do software como um todo.

6 SQL Magazine Edição 136 •

Fique por dentro Este artigo é útil por ensinar como elaborar um modelo multidimensional para a visualização de informações geradas por processos de build, geralmente compostos por várias ações e etapas. A partir do modelo sugerido pode-se modificar as entidades, dimensões, hierarquias, membros, atributos, medidas e relacionamentos para adequar a estrutura de acordo com outras características associadas à visualização das informações geradas, independente da ferramenta de build utilizada. A implementação do modelo descrito no artigo pode ser feita em qualquer ferramenta OLAP que suporte um modelo multidimensional e possua uma interface para consultas que filtrem os dados das medidas de acordo com os níveis e membros das dimensões.

A partir deste cenário, este artigo apresentará como montar um modelo multidimensional que se baseia nos dados gerados por um processo de build incluindo o tipo, resultado, plataformas suportadas, usuários, commits, bugs e outras entidades. O modelo descrito neste artigo é multidimensional e pode ser implementado em bancos de dados relacionais e visualizado com qualquer ferramenta que utilize a tecnologia OLAP (OnLine Analytical Processing), com o objetivo de apresentar as informações agregadas em formatos adequados para análises e suporte à tomada de decisõe s táticas e estratégicas. O modelo conta com diversas entidades que abordam os principais aspectos relacionados às etapas, ações e verificações realizadas durante o processo de build automático. Apesar de contemplar diversas características de um processo de build de software típico, o modelo apresentado neste artigo é razoavelmente simples e pode ser adaptado a diferentes t ipos de projetos que utilizam build automático de acordo com os requisitos e cenários de utilização. As entidades do modelo são criadas

para a visualização dos dados em um modelo multidimensional. Contudo, o artigo não detalha como obter as informações que foram armazenadas nestas entidades, pois tais informações são dependentes dos formatos de dados específicos de cada projeto e das ferramentas utilizadas para o build.

Funcionamento do build automático Para começar a compreender o cenário no qual vamos nos basear para montar o modelo, é preciso primeiro estudar um pouco as ações e dados gerados por um build automático a partir de um cenário que demonstre o que é tipicamente feito neste processo. Como atualmente existem diversas informações e maneiras diferentes de apresentar tais informações, faz sentido delimitar o escopo e escolher um tipo de build e ferramentas comuns que possam ser utilizadas como cenário para a elaboração e detalhamento dos dados com os quais precisamos trabalhar. O cenário no qual vamos nos basear para criar o modelo de dados utiliza uma plataforma de desenvolvimento Java que geralmente emprega ferramentas como Ant, Maven ou Gradle para automatizar o processo de build de projetos, sejam eles projetos para aplicações desktop, para a Web ou até mesmo para o desenvolvimento mobile para a plataforma Android. Quando um build é iniciado, ele requer algumas informações importantes, tais como o tipo de build, o local de armazena mento dos arquivos gerados e parâmetros de compilação. São estas informações que vão indicar quais ações prec isam ser feitas. Tipos comuns incluem “Compilação”, “Empacotamento”, “Clean” e “Completo”. Cada um deles possui diversas características e pode conter ações que vão determinar o que efetivamente será feito. Por exemplo, o build “Compilação” apenas compila o código fonte em Java para verificar se houve alguma mensagem retornada pelo compilador, ao passo que “Clean” limpa os diretórios necessários para o build e remove quaisquer arquivos gerados pelo build anterior. Builds automáticos geralmente produzem uma nova versão do software, seja ela uma versão interna ou que será disponibilizada para os usuários finais. De qualquer forma, cada build deve especificar qual é o resultado, ou seja, qual é o sistema operacional (Windows, Linux, MacOS, Android, etc.), a plataforma (x86, x64, ARM) e o formato do pacote que será produzido (JAR, EXE, WAR, HTML, APK). Estas informações são especialmente importantes para projetos que suportam muitas plataformas, onde toda a geração de artefatos é controlada por um único processo de build. Ao término de cada execução do build, uma nova versão do software é gerada. Esta versão deve conter uma numeração (1.2.0.1, 1.3.3.2, etc.), o tipo de versão (“RTM”, “Debug”, “Beta”, “Release”) e também qual é a edição do software em relação às suas funcionalidades e formato de utilização (“Free”, “Trial”, “Premium”, “Enterprise”, “Standard”, etc.). Assim como outros exemplos de dados, os valores para os elementos apresentados neste parágrafo são apenas sugestões e durante a implementação do modelo o leitor pode colocar valores que façam sentido para o seu contexto.

Os projetos de software que utilizam a plataforma de desenvolvimento Java geralmente são compostos por diferentes componentes externos, como bibliotecas, frameworks ou qualquer outro tipo de dependência. Deste modo, os processos de build se encarregam também de checar se as dependências estão corretas e atualizadas para que o processo não “falhe”. A propósito, um build que “falha”, ou seja, não consegue realiza r as tarefas necessárias para ser classif icado como um “sucesso”, representa uma importante informação. A definição de “falha” ou “sucesso” de um build é especificada por uma série de diretrizes condicionais criadas pela equipe de desenvolvimento e varia muito para cada projeto. Por exemplo, é comum encontrar regras do tipo: “se o código fonte não passar em determinado número X de testes, o processo de build falha”. O modelo deste artigo considera apenas o resultado do build e ta m bém quais testes são associados a ele, sem detalhar o tipo de teste (testes unitários, testes de interface, testes de integração, etc.). Outro aspecto importante que deve ser considerado é a quantidade de código fonte novo ou modificado em relação ao último build. É comum que este dado seja indicado pelos commits (o código fonte novo ou modificado enviado para um sistema de controle de versão) realizados pelos desenvolvedores desde o último build. Também é um dado importante a identificação dos desenvolvedores que produziram os commits. A realização de um build automático normalmente é associada a algum tipo de objetivo alcançado no projeto ou a uma etapa do processo de desenvolvimento. Por exemplo, é comum a geração de um build quando um Sprint é finalizado, pois ao final deste ciclo curto tradicional em processos de desenvolvimento ágil, geralmente é necessário distribuir ou disponibilizar o software para os usuários finais. Por fim, como todo software pronto ou em desenvolvimento possui bugs, é muito importante apresentar esta informação sempre que uma nova versão do software é produzida. Fornecer as informações de bugs junto com o processo de build é útil para ajudar a acompanhar a evolução da qualidade do software de forma quantitativa e qualitativa, auxiliando assim a tomada de decisões que podem impactar o projeto. Diversas outras informações relacionadas ao processo de bu ild automático podem ser inseridas como, por exemplo, resultado de ferramentas de checagem estática de código (PMD, CheckStyle e FindBugs), relatório da documentação técnica gerada, lista de requisitos e user stories atendidas pelo build, problemas encontrados no projeto e até diagramas e documentos gerais. Contudo, para não tornar o modelo demasiadamente complexo, este artigo contempla apenas as informações relacionadas ao cenário descrito nos parágrafos anteriores. Fica como recomendação ao leitor customizar o modelo de acordo com os dados relacionados ao seu processo de build automático, de modo a facilitar a visualização, exploração e descoberta de fatos relevantes a partir dos dados. Novamente, o foco do artigo é apenas no modelo de dados e não em como realizar o processo de ETL que obtém as informações de diferentes fontes de dados (arquivos texto, código fonte, resultado Edição 136 SQL Magazine •

7


da execução de ferramentas externas, mensagens do compilador, etc.) e as consolida em um data warehouse. Vale a pena destacar que tal processo de ETL pode ser complexo e demandar muitos recursos, dependendo do nível de detalhamento, da frequência de atualização e das características da informação que se deseja obter. Sendo assim, o próximo tópico detalha as dimensões e suas características, as tabelas do modelo multidimensional e as medidas utilizadas para a geração da ferramenta que permitirá a visualização de informações associadas com o processo de build automático.

Modelagem das dimensões e medidas O modelo multidimensional utilizado no exemplo será criado a partir da especificação de dimensões, hierarquias, níveis, mem bros e também das medidas utilizadas para visualizar os dados dos relatórios em um ou mais cubos de dados. A modelagem adotada é conhecida como híbrida, pois ela mistura o design estrela (star) e floco-de-neve ( snowflake). Dito isso, inicialmente apresentaremos as dimensões e depois mostraremos como estas dimensões estão relacionadas com a tabela fato e as medidas dos cubos de dados. A primeira dimensão que será especificada é a dimensão que armazena qual é o tipo de build. Este tipo geralmente está armazenado em um arquivo de configuração XML e deve ser indicado como parâmetro para a execução automática do build. Esta dimensão se chamará TipoBuild e seus atributos incluem um identificador do tipo (ID_TIPO_BUILD), o nome do tipo (TIPO) e uma descrição sem limite de texto utilizada como propriedade dos membros da dimensão (DESCRICAO). Os dados desta dimensão são organizados em uma hierarquia simples, que agrupa os dados de acordo com o tipo de build. A Figura 1 mostra a tabela DM_TIPO_BUILD, que implementa a dimensão e também um exemplo de dados dentro da hierarquia.

compilação falhou, mas passou em todos os testes unitár ios pode ser uma falha parcial. A dimensão que armazenará o resultado do build se chamará Resultado e ela organizará os seus dados da seguinte maneira: a hierarquia conterá primeiro o tipo do resultado (“Sucesso” ou “Falha”), seguido de uma informação sobre o quão completo foi o resultado (“Completo” ou “Parcial”). A hierarquia também apresentará informações sobre a causa apenas nos builds classificados como parciais. Por fim, haverá um atributo indicando informações adicionais como, por exemplo, detalhes do código fonte que causou a falha do build. A dimensão Resultado é implementada na tabela DM_RESULTADO e conta com um detalhe importante em relação aos tipos de dados. Para a coluna que armazenará o tipo de resultado (“Sucesso” ou “Falha”), utilizaremos dados caracteres do tipo de dados VARCHAR(15). Já para armazenar se o resultado é completo ou parcial, utilizaremos um tipo de dados BIT, cujo valor 0 indicará “Parcial” e o valor 1 indicará “Completo”. A decisão do tipo de dados utilizado na modelagem é específica para cada projeto de acordo com a variedade dos dados e outros fatores. Neste modelo optamos por escolher dois tipos de dados diferentes com fins didáticos e para mostrar que é possível utilizar tipos de dados caractere ou bit como membros da dimens ão. Caso o tipo bit seja escolhido, é preciso montar uma expressão que traduza o valor 0 como “Parcial” e o valor 1 como “Completo”. A Figura 2 apresenta a tabela DM_RESULTADO junto com exemplos de navegação na dimensão com o objetivo de filtrar os dados de acordo com os diferentes tipos de resu ltado do build.

Figura 2. Tabela DM_RESULTADO da dimensão Resultado e exemplos de navegação na hierarquia

Figura 1. Tabela DM_TIPO_BUILD da dimensão TipoBuild e exemplo de navegação na dimensão

Um processo de build automático geralmente possui apenas dois resultados de acordo com as diretrizes condicionais criadas pela equipe de desenvolvimento: sucesso ou falha. Além de indicar o resultado, também é interessante indicar se o resultado foi parcial ou completo. Por exemplo, um build cujo processo passou nas etapas de compilação e testes pode ser considerado um sucesso parcial se houve algum problema na etapa de geração de documentação. De forma semelhante, um build onde o processo de


Um processo de build automático finalizado com sucesso normalmente gera uma nova versão do software. Este versionamento é associado com o tipo da versão (“Debug”, “Release”) e com o tipo da edição (“Trial”, “Free” e “Enterprise”). As informações de versão e edição do software são relacionadas ao tipo do build e também ao resultado, porém, neste artigo uma dimensão separada chamada Versao armazenará os dados de tipo da versão e da edição. O motivo é que tanto a edição quanto a versão normalmente são representadas pelo número da versão quando o processo de build é finalizado e, desta forma, as pesquisas relacionadas ao software final podem ser feitas consultando uma dimensão específica.

A tabela DM_VERSAO implementa a dimensão Versao e contém colunas para armazenar o tipo, a edição e o número. É importante destacar que a documentação do projeto deve deixar claro o critério para o incremento da versão, pois nem todo build precisa necessariamente gerar um novo número para o software. Nesta mesma documentação também é preci so especificar o que diferencia uma edição do software de outra através da lista de funcionalidades, preço, período de duração ou características, tais como a apresentação de propagandas. Estes detalhes de documentação ficam fora do modelo de dados, que apenas apresenta o nome da ed ição, o número e o seu tipo. A Figura 3 mostra a tabela DM_VERSAO junto com exemplos de navegação na hierarquia.

quando o build foi feito em relação ao ciclo (0 = Em andamento e 1 = Logo após o término), o nome do Sprint e uma coluna de texto livre para armazenar as user stories tratadas pela equipe de desenvolvimento. A tabela DM_SPRINT junto com exemplos de navegação em sua hierarquia é apresentada na Figura 4.

Figura 4. Tabela DM_SPRINT da dimensão Sprint e exemplo de navegação na hierarquia

Figura 3. Tabela DM_VERSAO da dimensão Versao e exemplos de navegação na hierarquia

Do ponto de vista de organização e gerenciamento, é comum associar a geração de um build a um marco importa nte do projeto relacionado aos requisitos do software. O modelo multidimensional que estamos criando faz esta assoc iação através da dimensão Sprint , pois ela vai armazenar detalhes do ciclo em que o build foi gerado. No nosso cenário, o build pode ser gerado enquanto um Sprint está em andamento ou imediatamente após o seu término. Portanto, a hierarquia desta dimensão vai conter um nível que indicará se o build foi gerado enquanto um Sprint estava em andamento ou logo após ele ter sido finalizado. O próximo nível armazenará o nome e também informações adicionais em uma coluna de texto livre, com detalhes sobre as user stories atendidas por este ciclo. Como o objetivo principal do modelo é disponibilizar informações sobre o build, não serão detalhados aspectos do Sprint e outras informações gerenciais relacionadas ao andamento do processo de desenvolvimento adotado. Novamente, o leitor que desejar incluir tais informações no modelo pode customizar as dimensões ou mesmo criar suas próprias para atender seus requisitos relacionados à apresentação dos detalhes do processo de desenvolvimento. A dimensão Sprint é implementada na tabela DM_SPRINT e contém um identificador único, a coluna do tipo BIT para indicar

A próxima dimensão do modelo armazena informações sobre a plataforma e empacotamento do(s) arquivo(s) binário(s) gerado(s) pelo processo de compilação. Estas informações são importantes, pois elas ajudam a identificar, entre outros aspectos, se a frequência de geração de versões está sendo uniforme ou não entre as diferentes plataformas e arquiteturas nas quais o projeto deve atender. A dimensão Plataforma deve armazenar em sua hierarquia qual é o sistema operacional alvo dos arquivos binários (“Todos”, “Windows”, “Linux”, “iOS”, etc.) e também a identificação de qual arquitetura de processador (“32 bits - x86”, “64 Bits - x64”, “ARM”, “Geral”), uma vez que estas informações são importantes para o processo de deploy da versão do software. Depois da escolha destas duas informações, sistema operacional e plataforma, é preciso identificar qual é o tipo de pacote gerado pelo build, que pode incluir valores como “JAR”, “EXE”, “ZIP”, “APK”, “HTML”, “WAR”, dentre outros. Apesar de existir associação entre tipos de pacotes e sistemas operacionais, como o tipo “APK” e o S.O. “Android”, é importante colocar as informações de pacote como um nível da hierarquia abaixo dos níveis sistema operacional e arquitetura para facilitar a especificação de filtros nos membros desta dimensão, implementada pela tabela DM_PLATAFORMA, mostrada na Figura 5. No desenvolvimento de software em Java é comum o uso de dependências externas especificadas durante a codificação, uma vez que é necessário utilizar as classes e outros recursos de certas dependências para implementar os casos de uso/regras de negócio. Assim sendo, durante o processo de bu ild automático é importante que estas dependências sejam corretamente identificadas e atualizadas, pois este é um dos requisitos básicos para a compilação do projeto. Com base nisso, o modelo multidimensional armazena as informações de dependências do projeto para ajudar a realizar análises que identifiquem, por exemplo, se o tempo de build aumentou quando determinada dependência foi incluída. Ou ainda, permitir rastrear e descobrir a partir de qual momento o software começou ou deixou de utilizar certas dependências. Edição 136 SQL Magazine •

9


ETL faça a redução da URL oficial do projeto com um encurtador de endereços como o bit.ly ou o migre.me. A Figura 6 mostra a implementação da dimensão Dependencia por meio das tabelas DM_DEPENDENCIA e DM_DEP_BUILD, junto com exemplos de navegação na hierarquia dos dados desta dimensão. Com as informações sobre dependências do projeto já modeladas, podemos começar a pensar em como incluir as in formações relacionadas aos commits realizados desde a execução do último build. Os dados relacionados ao conjunto de modificações no código fonte vão ser apresentados aos usuários do modelo através da dimensão Commit, que armazenará informações sobre os desenvolvedores que contribuíram com códigos e também detalhes do que foi feito. Aqui é importante destacar que os profissionais desenvolvedores não devem ser relacionados diretamente com o build, ou seja, primeiro é preciso relacionar o build com os commits para só depois relacioná-los com os desenvolvedores. As informações de quem Figura 5. Tabela DM_PLATAFORMA da dimensão Plataforma e exemplos de dados na dimensão contribuiu para o projeto desde o último build incluem o papel do desenvolvedor (“designer”, “programador front-end”, “DBA”, Portanto, a dimensão Dependencia vai armazenar detalhes “testador”), o nome e o e-mail. Já para o commit é necessário algum das dependências de cada build. O usuário final que consultar tipo de descrição, um identificador único para relacionamento os dados desta dimensão terá a visualização do nome da depennas tabelas do modelo, um identificador do sistema de controle dência, qual versão está sendo utilizada no projeto e também uma de versão e a URL que contém informações relevantes sobre as propriedade adicional com o endereço na Web da dependência modificações realizadas no código fonte. ou do local oficial do seu repositório. O leitor pode incrementar o modelo e disponibilizar mais inforA implementação da dimensão Dependencia requer duas mações sobre as mudanças se nos aprofundarmos nesta entidade. tabelas para representar o relacionamento entre os builds e as Por exemplo, pode-se associar uma data, quais arquivos fontes dependências. Estas tabelas são DM_DEPENDENCIA, que vai foram modificados e outros detalhes adicionais. Contudo, para armazenar um identificador, o nome da dependência, a versão e manter o modelo de dados simples e focado no build, não moa URL do site ou repositório, e a tabela DM_DEP_BUILD, que vai delaremos estes detalhes, ficando a cargo do leitor implementar fazer a relação entre a tabela DM_DEPENDENCIA e a tabela fato outras dimensões para lidar com o detalhamento dos dados FT_BUILD, que ainda vai ser modelada logo mais neste artigo. relacionados ao commit. A tabela DM_DEP_BUILD é relacionada por uma chave estrangeiA tabela DM_COMMIT armazena os commits e conta com ra colocada na coluna ID_DEPENDENCIA, que também é a chave diversas colunas, porém, na dimensão, os usuários finais vi sualiprimária com o mesmo nome na tabela DM_DEPENDENCIA. zarão apenas o nome. As demais colunas podem ser visualizadas A implementação da coluna VERSAO utilizou um tipo de dados como propriedades adicionais dos membros da dimensão. Para caractere, pois diversos projetos utilizam pontos, letras e outros implementar a dimensão, é preciso relacionar a tabela fato e a caracteres na classificação da sua versão (exemplo: 1.2_beta). Já a tabela DM_COMMIT através da tabela DM_COMMIT_BUILD, coluna que armazena a URL do projeto relacionado à dependênque contém uma chave estrangeira na coluna ID_COMMIT. Nocia foi limitada a 1.000 caracteres e recomenda-se que o processo vamente, a coluna ID_BUILD da tabela DM_COMMIT_BUILD é uma chave primária para a tabela fato FT_BUILD, que ainda não foi modelada. Desta forma, a dimensão Commit será criada a partir do relacionamento entre as tabelas DM_COMMIT, DM_COMMIT_BUILD e FT_BUILD. Para representar os desenvolvedores que contribuíram para o build através de algum commit, utilizaremos uma dimensão chamada Desenvolvedor, cujos membros vão ser organizados em uma hierarquia onde primeiro mostra-se o seu papel no build e depois seu nome. Figura 6. Tabelas DM_DEPENDENCIA e DM_DEP_BUILD junto com os elementos da dimensão Dependencia


A decisão de criar uma nova dimensão para o desenvolvedor ao invés de colocar estes dados como um novo nível da hierarquia Commit é para facilitar o filtro necessário para responder perguntas do tipo “Em quais builds o profissional X participou”. A implementação da dimensão Desenvolvedor é feita pelas tabelas DM_DEVELOPER e DM_COMMIT_BUILD, pois existe um relacionamento de chave estrangeira entre as duas tabelas por meio da coluna ID_DEVELOPER, presente em ambas. A Figura 7 apresenta as tabelas DM_COMMIT, DM_COMMIT_BUILD e DM_DEVELOPER junto com seus relacionamentos que implementam as dimensões Commit e Desenvolvedor. Esta figura também apresenta exemplos de navegação nos membros das hierarquias destas dimensões. O acompanhamento da qualidade do projeto de software será feito através da dimensão Bug , que mostrará quais são os bugs no software que ainda existem após o build ter sido feito. É importante deixar claro que estes bugs devem ter um controle específico e serem gerenciados adequadamente por ferramentas como o Bugzilla. A dimensão Bug é implementada pela tabela DM_BUG, que contém colunas para identificar unicamente o bug, qual é o módulo do sistema/aplicação na qual o bug está relacionado e o nome do bug. Há também uma coluna para armazenar um identificador geral utilizado por um sistema de controle de bugs (Exemplo: #BUG0394) e uma coluna de texto livre para informações adicionais. O relacio-

namento de DM_BUG com a tabela fato FT_BUILD é feito através de DM_BUGS_ BUILD, pois ela contém uma chave estrangeira na coluna ID_BUG e outra chave estrangeira na coluna ID_BUILD para os relacionamentos com as tabelas DB_BUG e FT_BUILD, respectivamente. A hierarquia da dimensão Bug apresenta o nível que contém o módulo do sistema/aplicação seguido pelo nível com o identificador geral do bug. Neste caso optou-se por mostrar o nome do bug ao invés do seu identificador, pois desta maneira é mais simples para um gerente de projeto filtrar por bugs conhecidos. Sendo assim, o identificador do bug e as informações adicionais são propriedades da dimensão. A Figura 8 mostra as tabelas DM_BUG e DM_BUGS_BUILD junto com exemplos de navegação na hierarquia da dimensão Bug.

Um processo automático de build sempre é realizado em uma data específica, seja ela periódica (exemplo: sempre às quartas-feiras à noite) ou de acordo com a decisão arbitrária dos membros do projeto. De qualquer forma, o modelo multidimensional possuirá uma dimensão Data que armazenará o momento no qual o build foi iniciado e não quando ele foi terminado, pois teremos uma medida que armazenará a duração do build. A implementação da dimensão Data é feita através da tabela DM_DATA, que contém diversas colunas separadas para armazenar o ano, o mês, o dia, o dia da semana, a hora e o minuto. A separação dos dados por coluna é um requisito para a criação de diferentes níveis e hierarquias da dimensão Data , mostrada na Figura 9 junto com a tabela DM_DATA.

Figura 7. Tabelas DM_COMMIT, DM_COMMIT_BUILD e DM_DEVELOPER das dimensões Commit e Desenvolvedor

Figura 8. Tabelas DM_BUG e DM_BUGS_BUILD da dimensão Bug Edição 136 SQL Magazine •

11


Figura 9. Tabela DM_DATA da dimensão Data e exemplos de seleção de elementos nas hierarquias Data, DiaSemana e Horário

Uma possível modificação que pode ser feita nesta dimensão é a combinação das hierarquias em apenas uma dimensão através da criação de níveis. Neste artigo optou-se por trabalhar da forma mais didática possível e por isso foram criadas diferentes hierarquias para a divisão de tempo. Uma vez que todas as dimensões e suas respectivas tabelas foram apresentadas, é preciso detalhar como será a tabela fato e as medidas. O objetivo da tabela fato, neste caso, é agregar as informações de cada execução do build e combiná-las com dados relacionados ao tempo gasto e outras métricas importantes para quem está realizando a análise. No modelo descrito neste artigo a tabela fato se chama FT_BUILD e é apresentada sem os relacionamentos com as demais tabelas de dimensão na Figura 10.


Figura 10. Tabela fato FT_BUILD sem os relacionamentos com as tabelas de dimensão

Note que a tabela fato FT_BUILD contém diversas colunas e relacionamentos. A primeira coluna importante cha ma-se ID_BUILD e representa um valor numérico sequencial que é o identificador único da tabela com a propriedade de chave primária. Em segu ida temos as colunas ID_TIPO_BUILD, ID_RESULTADO, ID_VERSAO, ID_SPRINT, ID_PLATAFORMA e ID_DATA, que são chaves estrangeiras para as chaves primárias das tabelas DM_TIPO_BUILD, DM_RESULTADO, DM_VERSAO, DM_SPRINT, DM_PLATAFORMA e DM_DATA, respectivamente.

A dimensão Dependencias é implementada através das tabelas DM_DEP_BUILD e FT_BUILD, que contém a coluna ID_ BUILD. Deste modo, não há colocação da chave estrangeira na tabela de fatos para conectar a dimensão de dependências. De forma semelhante, o relacionamento que implementa a dimensão Commit depende de FT_BUILD e DM_COMMIT_BUILD. Além disso, a dimensão Bug também possui relacionamento com a tabela fato, sendo implementado de maneira equivalente, através de DM_BUGS_BUILD. A tabela fato FT_BUILD é apresentada junto com os relacionamentos com as demais tabelas de dimensão (DM_TIPO_ BUILD, DM_RESULTADO, DM_VERSAO, DM_SPRINT, DM_PLATAFORMA e DM_DATA) no modelo final exposto na Figura 11. As outras colunas da tabela FT_BUILD vão definir as medidas numéricas que permitirão a análise de dados, lembrando que as medidas sempre são em relação ao resultado final da execução do processo de build automático. A primeira coluna que armazena medidas na tabela fato se chama TOTAL_LOC e ela indica a quantidade de linhas de código total do projeto (somando todos os arquivos com código fonte); a coluna TOTAL_TEMPO_SEGUNDOS indica em segundos qual foi o tempo total para a realização do build; a coluna TOTAL_ QTD_BUGS contém a quantidade total de bugs do projeto; a coluna QTD_BUGS_ CORRIGIDOS armazena o número de bugs corrigidos em cada build; a coluna TOTAL_QTD_COMITS indica o número de commits desde a execução do último build; a coluna TOTAL_QTD_PACOTES contém o número total de pacotes Java do projeto; a coluna TOTAL_QTD_ARQUIVOS armazena a quantidade de arquivos com código fonte Java no projeto; a coluna TOTAL_QTD_TESTES armazena a quantidade total de testes (independentes do tipo) que o processo de build realizou; a coluna QTD_TESTE_SUCESSO armazena o número de testes que foram realizados com sucesso, ou seja, que passaram durante o processo de build; e a coluna QTD_LOC_COBERTO_TESTES armazena

Figura 11. Tabela fato FT_BUILD e seus relacionamentos com as demais tabelas de dimensão do modelo

a quantidade de linhas de código cobertas por testes no projeto. As medidas geradas a partir da tabela fato deste modelo de dados são descritas a seguir. É importante destacar que estas medidas podem ser customizadas e com binadas para montar diferentes cubos de dados que mostrem facetas específicas do negócio dependendo do que se desejar visualizar. Vejamos as medidas geradas: • TotalLinhasDeCodigo (coluna TOTAL_ LOC) indicará a quantidade total de linhas de código do projeto no momento do build. Quando se agregar esta medida de acordo com os filtros de dimensão, a ferramenta deve somar os valores desta medida; • TempoGasto (coluna TOTAL_TEMPO) indicará o tempo total utilizado para fazer o build. Esta medida pode ser convertida para horas, minutos e segundos para facilitar sua visualização. Quando se agregar esta medida, a ferramenta deve somar os valores desta medida; • MédiaTempoGasto (coluna TOTAL_ TEMPO) indica em horas, minutos e segundos o tempo médio necessário para fazer o build. Quando se agregar esta

medida de acordo com os filtros de dimensão, a ferramenta deve fazer a média dos valores desta medida; • TotalBugs (coluna TOTAL_QTD_BUGS) indicará a quantidade total de bugs no projeto no momento do build. Quando se agregar esta medida, a ferramenta deve somar os valores desta medida; • BugsCorrigidos (coluna QTD_BUGS_ CORRIGIDOS) indicará a quantidade de bugs corrigidos desde o último build. Quando se agregar esta medida, a ferramenta deve somar os valores desta medida; • TotalCommits (coluna TOTAL_QTD_ COMITS) indicará a quantidade total de commits realizados desde o último build. Quando se agregar esta medida, a ferramenta deve somar os valores desta medida; • TotalPacotesJava (coluna TOTAL_QTD_ PACOTES) indicará a quantidade total de pacotes analisados durante o build. Quando se agregar esta medida, a ferramenta deve somar os valores desta medida; • TotalArquivosCodigoFonte (coluna TOTAL_ QTD_ARQUIVOS) indica rá a Edição 136 SQL Magazine •

13


quantidade total de arquivos com código fonte analisados durante o build. Quando se agregar esta medida, a ferramenta deve somar os valores desta medida; • TotalTestes, TestesSucessoPerc e TestesFalhaPerc (colunas TOTAL_QTD_TESTES e QTD_TESTE_SUCESSO) mostrará a quantidade total de testes, o percentual de testes com sucesso e o percentual de testes com falha do build, respectivamente. Quando se agregar esta medida de acordo com os filtros de dimensão, a ferramenta deve somar os valores desta medida; • TotalCobertura (coluna QTD_LOC_COBERTO_TESTES) indicará o percentual do código fonte coberto com testes do build. Quando se agregar esta medida, a ferramenta deve somar os valores desta medida.

Autor Mauro Pichiliani [email protected] / @pichiliani

É bacharel em Ciência da Computação, mestre e doutorando pelo ITA (Instituto Tecnológico de Aeronáutica) e MCP, MCDBA e MCTS. Trabalha há mais de 10 anos utilizando diversos bancos de dados SQL e NoSQL. É colunista de SQL Server do web site iMasters (http://www. imasters.com.br) e mantenedor do DatabaseCast (@databasecast), o podcast brasileiro sobre banco de dados. Links: Ferramenta de build Maven https://maven.apache.org/

Os dados gerados durante o processo de build automático em um projeto de software possuem a capacidade de produzir diferentes visões do andamento do projeto e permitem a análise detalhada de aspectos envolvendo tempo, commits, bugs, versões, sprints e outras informações. Este tipo de análise é especialmente importante para gerentes de projeto que precisam periodicamente avaliar a qualidade do software e realiza r o acompanhamento do projeto a cada execução do processo de build. Baseado neste cenário, este artigo apresentou um modelo de dados multidimensional utilizado para facilitar as análises de diversos aspectos relacionados ao processo de build automático de um projeto de software que adota a tecnologia Java. O modelo descrito contém dimensões para facilitar as análises por builds, datas, versões, ciclos de desenvolvimento, bugs, commits e dependências de software. Já as informações quantitativas apresentadas incluem totais de linhas de código, testes, tempos, bugs e commits realizados por desenvolvedores.

Ferramenta de build Gradle http://gradle.org/

Framework para testes em Java JUnit http://junit.org/

Ferramenta PMD para geração de métricas de código https://pmd.github.io/

Ferramenta CheckStyle para verificação de estilo de codificação http://checkstyle.sourceforge.net/

Ferramenta FindBugs para identificação automática de bugs http://findbugs.sourceforge.net/

Ferramenta Bugzilla para controle de bugs https://www.bugzilla.org/

Você gostou deste artigo? Dê seu voto em www.devmedia.com.br/sqlmagazine/feedback Ajude-nos a manter a qualidade da revista!


Edição 136 SQL Magazine •

15

Manipulação de arquivos de dados e log no SQL Server Aprenda neste artigo como dimensionar e alocar os arquivos de dados e de log transaction

O

s arquivos de dados do SQL Server são compostos por dois tipos: de dados e de log transaction. Nos arquivos de dados geralmente são armazenados objetos do banco como tabelas, procedures, triggers e índices, assim como registros de clientes. Já nos arquivos de log são registradas todas as alterações ocorridas no banco para que ele possa ser recuperado em caso de desastres do tipo perda de disco, falha em um dos arquivos de dados, etc. Os arquivos de dados e de log são divididos da seguinte maneira: • Arquivos de dados primário, com a extensão .mdf; • Arquivos de dados secundários, com a extensão .ndf; • Arquivos de log, com a extensão .ldf.

Esses arquivos possuem suas particularidades e funções dentro do banco de dados e a perda de um desses arquivos pode significar a perda de parte das informações contidas no banco de dados, ou mesmo a perda de todo o banco. No caso da perda de um dos arquivos, o procedimento mais indicado é a restauração do banco de dados, mas você deve ter o cuidado de executar rotinas de backup periodicamente. Os arquivos .mdf são os primeiros a serem criados quando você executa o comando CREATE DATABASE. O arquivo mdf sempre fará parte do filegroup primary. Dentro dele estarão armazenadas informações sobre tabelas, views, tabelas de sistema e metadados. Além disso, podem ser localizados procedures, índices, regi stos e usuários. Para o armazenamento de tabelas e índices de usuários, no entanto, recomendamos trabalhar com arquivos de dados secundários. Os arquivos .ndf armazenam dados secundários, opcionais no banco de dados. Eles podem ser montados


Fique por dentro Os arquivos de dados são objetos de extrema importância em um banco de dados, independ ente se estivermos falando de SQL Ser ver, Oracle ou MySQL. Dentro dos arquivos de dados encontram-se ta belas, índices, procedures e registros propriamente ditos de clientes, fornecedores e funcionários. Por isso, os mesmos necessitam de cuidados. Sendo assim, daremos recomendações e instruiremos como alocar estes arquivos, como controlar o espaço e configurá-lo para que seu banco ganhe performance com o gerenciamento correto dos arquivos de dados.

tanto durante o planejamento do banco de dados ou posteriormente, após a conclusão do banco. A principal função destes arquivos é aumentar a capacidade de armazenamento da base e manter uma área separada dos arquivos *.mdf, onde serão gravadas tabelas de sistema e metadados. Como já mencionado, é aconselhado utilizar arquivos secundários para armazenar dados dos usuários. Caso você não tenha o hábito de trabalhar com estes arquivos e acha que a melhor forma é armazenar os dados com arquivos primários, sugerimos que você pense a respeito e trabalhe com arquivos secundários. Desta forma, você pode aumentar a capacidades de armazenamento da base e ao mesmo tempo melhor organizar os objetos e registros específicos de usuários. O terceiro conjunto são os arquivos de Log Transaction, que possuem a extensão .ldf. Este tipo de arquivo é responsável pela recuperação do banco de dados em caso de falhas, sendo utilizado em operações de log shipping, possibilitando a recuperação da base em caso de desastres (falha física de discos ou estrutura de arquivos). Os arquivos de log armazenam todas as mudanças realizadas no banco, permitindo a recuperação de instruções quando estas não surtem o efeito esperado pelo programador.

É através dos arquivos de log que o SQL Server mantém a integridade dos dados. Abordaremos neste artigo de que forma você pode alocar os arquivos de dados e log transaction, assim como saber a diferença entre eles. Arquivos de dados têm por objetivo o armazenamento de registros, objetos (procedures, índices, views) e a estrutura de tabelas, tanto de clientes quanto do sistema do SQL Server. Já os arquivos de Log Transaction têm por objetivo armazenar todas as mudanças que são realizadas no banco (Insert, Update, Delete). Abordaremos aqui os tipos de volume de disco mais adequados ao tipo de arquivos que se está manipulando. Para os arquivos de dados, recomenda-se dar preferência a unidades onde a leitura tenha uma ótima performance, enquanto para arquivos de log, recomenda-se o uso de unidades onde a gravação tenha melhor performance. Apesar das diferenças, ambos os arquivos necessitam de unidades de disco com redundância a falhas.

• RAID 5: o RAID 5 combina o desempenho necessário e a re-

Onde alocar arquivos de dados e de log do banco

Existem outros tipos de RAID, mas procuramos destacar os mais usados e estudados para a implantação de banco de dados SQL Server. Existem ainda os RAIDs de software, os quais são gerenciados se criados por sistemas terceiros, e n a versão 2012 e 2014 do Windows Server Enterprise você pode criar RAIDs através da funcionalidade File and Storage Services. Apesar destes recursos, é sempre sugerido o RAID de hardware, muito mais s eguro e confiável.

Agora que você já tem uma ideia sobre arquivos de dados e de log transaction do SQL Server e qual a importância dos mesmos, vamos mostrar onde criá-los. Os arquivos de dados e de log são alvos de instruções DML (Insert, Update, Delete e Merge) e DDL (Create, Alter, Drop, Truncate), além de muitas instruções envolvendo select filtrando dados específicos. A unidade de disco onde você irá alocar os arquivos de dados e log faz muita diferença, mediante as operações enviadas ao banco de dados. Além disso, o tipo de partição e RAID fará com que seu banco tenha mais performance ou uma performance precária, assim como irá indicar se ele está alocado em uma área segura ou uma área que não oferece redundância a falhas. Para entender mais a fundo sobre alocação de arquivos de dados e de log, vamos explicar a seguir os tipos de RAID existentes e as recomendações de alocação: • RAID 0 (Zero): conhecido também por striping, o RAID 0 junta várias unidades de disco, formando assim um único disco, e apresenta um ótimo desempenho de leitura e g ravação. O grande problema do RAID 0 é que se um dos volumes falhar, a partição toda é perdida. Desta forma, embora ele seja uma ótima opção de desempenho, não oferece nenhuma redundância a falhas; • RAID 1: conhecido também por mirroring (espelhamento), no RAID 1 são usadas duas unidades de disco, que devem obrigatoriamente ter o mesmo tamanho. Caso você use dois discos de 500GB, por exemplo, seu espaço para alocar arquivos do banco continuará sendo de 500GB e não de 1T. Lembre-se que você está espelhando uma unidade e duplicando-a. Em função do RAID 1 escrever em dois discos por ser espelhado, isso garante a redundância a falhas. Assim, caso um disco pare, o outro irá assumir a responsabilidade do disco principal. Por outro lado, o RAID 1 perde em performance, apresentando um desempenho razoável na leitura e uma piora nos inserts e updates, pois a escrita é realizada em duas unidades para manter a consistência. Se desempenho não for o primordial para seu banco e sim a segurança, use o RAD 1;

dundância a falhas, viabilizando excelente desempenho para a escrita e bom desempenho para a leitura. No entanto, caso uma das unidades falhe, o desempenho à leitura ficará prejudicado, causando gargalos em instruções select, por exemplo. Mesmo assim, convém utilizar esse tipo de RAID para armazenar tanto arquivos de dados quanto arquivos de log; • RAID 0+1 ou 10: o RAID 0+1 ou RAID 10 junta a velocidade do RAID 0 (striping) com a redundância a falhas do RAID 1 (mirror). Neste tipo de partição as leituras ganham u m ótimo desempenho e é atualmente a melhor opção para armazenamento de arquivos para banco de dados, independentemente de este ser o SQL Server, o Oracle ou o MySQL. Este tipo de RAID necessita de no mín imo quatro discos. Ademais, a Microsoft recomenda fortemente que os arquivos do banco, principalmente os arquivos de log, sejam alocados nesta partição.

Criação dos arquivos de dados e log Agora que você já conhece as funções dos arquivos de dados e de log e já sabe onde armazenar os arquivos do banco, vamos aprender como criar os bancos e definir o caminho para estes arquivos. O primeiro exemplo que apresentamos utiliza a sintaxe de criação do banco de dados, conforme expõe a Listagem 1. Listagem 1. Criação do banco de dados.

01. 02. 03. 04. 05. 06. 07. 08. 09. 10. 11. 12.

CREATE DATABASE [SQL_Teste] ON PRIMARY ( NAME = N’SQL_Teste’, FILENAME = N’E:\MSSQLSERVER\DATA\SQL_Teste.mdf’ , SIZE = 5120KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N’SQL_Teste_log’, FILENAME = N’E:\MSSQLSERVER\DATA\SQL_Teste_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%) GO

Neste exemplo temos um banco de dados de nome SQL_Teste sendo criado. Note que este banco possui um grupo de arquivos chamado PRIMARY definido na instrução ON PRIMARY . O nome lógico do arquivo deste banco é “ SQL_Teste”, definido no comando NAME, e seu nome físico é descrito pela sua localização: N’E:\MSSQLSERVER\DATA\SQL_Teste.m df’ , definida no comando FILENAME. O arquivo de log, por sua vez, não possui grupo e já é criado diretamente conforme o caminho indicado em Edição 136 SQL Magazine •

17

Manipulação de arquivos de dados e log no SQL Server

‘E:\MSSQLSERVER\DATA\SQL_Teste_log.ldf’ .

Já o comando SIZE determina o tamanho inicial com que os arquivos serão criados e FILEGROWTH determina de quantos em quantos KB ou porcentagem um arquivo de dados ou log irá crescer.

Adicionando arquivos ao banco Após a criação do banco de dados, podemos criar novos arquivos como sugere o exemplo da Listagem 2 com o comando ALTER DATABASE. Antes disso, no entanto, usando também o ALTER DATABASE, criamos um novo grupo de arquivos, chamado “SECUNDARIO”, local onde inserimos dois arquivos de dados com a extensão “ndf”, por serem arquivos secundár ios. Observe o comando a seguir:

ou pela porcentagem. Neste exemplo estamos realizando as configurações referentes ao arquivo SQL_Teste_02 , como podemos ver no título da janela. Vamos explicar agora as opções desta janela. Posteriormente mostraremos como aplicar estas opções e como você pode configurá-las para obter benefícios na administração do banco de dados. Vejamos as opções: • Enable Autogrowth: ao habilitar esta opção, poderemos definir, em MB ou porcentagem, de quanto em quanto os arquivos de dados devem crescer quando alcançar seu limite; • In_Percent: se por acaso o arquivo possuir 500MB, ele irá crescer 10% sobre tamanho de 500MB. Desta forma, ele irá crescer mais 50MB, supondo que o valor de In_Percent esteja configurado para 10%;

ALTER DATABASE [SQL_Teste] ADD FILEGROUP [SECUNDARIO] Listagem 2. Criação de arquivos de Dados.

GO

Vejamos os comandos individualmente: • ALTER DATABASE: indica qual banco irá sofrer a alteração; • ADD FILE: indica que estamos inserindo um arquivo de dados no banco descrito na instrução ALTER DATABASE; • NAME: define o nome lógico do arquivo de banco de dados; • FILENAME: define o caminho onde o arquivo de dados será alocado. Este é o nome físico do arquivo; • SIZE: aqui se define o tamanho inicial do arquivo, que pode ser dado por: KB, MB e GB; • MAXSIZE: define o tamanho máximo que um arquivo de dados irá crescer. Os valores podem ser defi nidos por KB, MB e GB; • FILEGROWTH: com este comando especificamos de quanto em quanto o arquivo irá crescer assim que atingir seu tamanho, caso o FILEGROWTH esteja ativo; • TO FILEGROUP: este comando indica em quais grupos de arquivos os dois arquivos de dados da Listagem 2 farão parte. Embora seja possível usar códigos para inserir arquivos de dados e controlar seu tamanho, podemos usar o ambiente gráfico do SQL Server, como mostrado na Figura 1. Para acessar esta tela, clique com o botão direito do mouse em cima do banco que deseja manipular e escolha a opção Properties. Caso seu SQL Server esteja em português, Propriedades , dentro do SQL Server Management Studio. Na tela apresentada na Figura 2 , controlamos o limite de crescimento que um arquivo de banco de dados pode ter e de quantos em quantos MBs o mesmo irá crescer, sendo especificado em MB


01. 02. 03. 04. 05. 06. 07. 08. 09. 10. 11. 12. 13. 14. 15. 16. 17.

ALTER DATABASE [SQL_Teste] ADD FILE ( NAME = N’SQL_Teste_02’, FILENAME = N’E:\MSSQLSERVER\DATA\SQL_Teste_02.ndf’ , SIZE = 5120KB , MAXSIZE = 1048576KB , FILEGROWTH = 102400KB ) TO FILEGROUP [SECUNDARIO] GO ALTER DATABASE [SQL_Teste] ADD FILE ( NAME = N’SQL_Teste_03’, FILENAME = N’E:\MSSQLSERVER\DATA\SQL_Teste_03.ndf’ , SIZE = 5120KB , FILEGROWTH = 102400KB ) TO FILEGROUP [SECUNDARIO] GO

Figura 1. Inserção de arquivos de dados – opção Files

• In_Megabytes: o arquivo irá crescer mediante o valor informado na opção In_Megabytes. Por exemplo, quando um arquivo de

500MB alcançar o espaço total, o mesmo irá cresc er mais 100MB, se In_Megabytes receber o valor 100; • Maximum File Size: local onde é definido o limite de crescimento de um arquivo de dados. Nesta opção podemos limitar o crescimento do arquivo ou podemos deixar o mesmo cresc er até a capacidade máxima do disco rígido; - Limited to (MB): limita o tamanho máximo de um arquivo de dados. Desta forma você evita que um arquivo de dados ocupe todo o espaço de um disco e comprometa outros bancos que possuam arquivos de dados nesta unidade; - Unlimited: com esta opção ativa você está liberando o crescimento do arquivo de dados. Assim, enquanto houver espaço na unidade de onde se encontra o arquivo, o mesmo irá crescer. Essa prática não é recomendada para a configuração de arquivos de dados.

a quantidade de registros seja estimada em torno de 30MB, por exemplo. Após criar o arquivo primário, crie um grupo de arquivo de dados e defina-o como padrão e crie um arqu ivo de dados com 50MB, tendo assim uma folga no crescimento. É normal, durante a implantação do sistema, que o mesmo cresça de forma rápida, mas quando se entra em produção com dados de usuário, este crescimento tende a diminuir e cabe a você observar de quanto em quanto este banco cresce para que então possa definir o valor ideal para o Autogrowth. Para que você acompanhe o crescimento do banco de dados, apresentaremos a seguir alguns comandos: • EXEC master.dbo.xp_fixeddrives: exibe as unidades existentes no servidor e a quantidade de espaço livre, conforme mostrado na Figura 3;

Figura 3. Saída da execução do comando xp_fixeddrives • sp_helpdb SQL_Teste: exibe o espaço total do banco de dados

Figura 2. Controle de crescimento de arquivos

SQL_Teste e informações individuais sobre os arquivos de dados, conforme exibido na Figura 4; • sp_spaceused: este comando exibe o tamanho da base de dados e o tamanho do espaço não alocado nos arqu ivos de dados, como mostra a Figura 5; • sp_helpfile: com a execução dessa instrução, você terá acesso a informações sobre arquivos de dados, como tamanho inicial, tamanho total, de quanto em quanto o arquivo irá crescer, assim como o caminho onde os mesmos estão alocados (vide Figura 6).

Boas práticas para arquivos de dados e Log

Configurando o Autogrowth e Maxsize

Com o conteúdo apresentado até aqui você já está apto a criar seus arquivos de dados e de log e já conheceu as opções para dimensionar os mesmos. Mas você já sabe como dimensionar corretamente estes arquivos de tal forma que não impactem no desempenho de seus bancos de dados? Embora isso exija um pouco mais de esforço, saiba que é importante limitar o tama nho de seus arquivos. Deste modo, a partir de agora, vamos verificar questões como esta para que você possa aplicar boas práticas e efetivamente administrar seus SGBDs.

Neste tópico explicaremos como configurar o Autogrowth e a melhor opção para Maxsize em suas bases de dados. O Autogrowth é desabilitado por padrão quando um arquivo de banco de dados é criado, mas mediante a qua ntidade de bancos existentes dentro de sua instância, é aconselhável habilitar esta opção para controlar de quantos em quantos MBs os arquivos crescerão e limitar o crescimento máximo do banco de dados, de forma que o crescimento de um banco não prejudique os demais que possuem arquivos existentes em um mesmo volume de disco. Anteriormente, na Figura 1 mostramos a tela de inserção de arquivos de dados nas propriedades de arquivos do banco. Na tela da Figura 7 podemos controlar o crescimento e o tamanho máximo que um arquivo pode alcançar. Após habilitar a opção Enable Autogrowth , outras opções são liberadas para que você possa trabalhar o dimensionamento dos arquivos de dados e controlar o fluxo de crescimento dos mesmos:

Tamanho inicial de um arquivo de dados

Quando você está definindo o parâmetro SIZE na criação de um arquivo, tanto de dados quanto de log, você está definindo o tamanho o qual ele será criado. Para definir o tamanho inicial de um arquivo, além de especificar de quanto em quanto este irá crescer, é viável que junto aos desenvolvedores se tenha uma ideia da volumetria de dados que o banco receberá. Suponha que


19

Manipulação de arquivos de dados e log no SQL Server

• File Growth: você pode limitar o

crescimento do banco em porcentagem ou em MB. Recomendamos controlar o crescimento em MB pelo seguinte motivo: suponha que você tenha um arquivo de dados de 1TB (Terabyte), o que é muito comum nos dias de hoje, que o mesmo esteja configurado para crescer 30% sempre que alcançar seu limite e você está com apenas 10GB livres em sua unidade de disco. O arquivo chega, então, ao limite e cresce 30% de 1T, ou seja, 300GB. Pronto, seu banco de dados trava e junto com ele, todos os arquivos de dados que se encontram nesta mesma unidade não terão condições de crescer. Neste cenário você não irá parar apenas um banco, mas todos os bancos que possuírem arquivos de dados nesta unidade. Assim, recomendamos fortemente que você controle o crescimento pela opção In_Megabytes , def inindo um tamanho razoável, nem muito grande e nem muito pequeno. Obtenha essa média através dos comandos passados anteriormente para que você defina um tamanho razoável de crescimento. Se você definir um tamanho muito pequeno, o banco terá constantes travamentos, porque a todo o momento o banco irá dimensionar os arquivos de dados e isso gera esforço tanto para o SQL Server quanto para o disco rígido. Se você definir um tamanho muito grande, o banco de dados irá dispor de muito tempo para crescer o arquivo. Quando um arquivo de dados cresce, o SQL Server preenche os arquivos mesmo com zeros até chega r ao tamanho especificado pelo file growth In_Megabytes , o que gera certa degradação da performance enquanto o arquivo está crescendo. Mediante a volumetria de seu banco, cresça os arquivos no máximo de 500MB a 500MB. Fique de olho, portanto, no quanto está configurado o parâmetro Maxsize, pois ele determina o tamanho máximo de seus arquivos de dados e log. Verifique também o espaço disponível em suas unidades de disco para não ter nenhuma surpresa desagradável. Ao criar um cenário de teste para estudos, deixe o crescimento configurado de 100MB a 100MB para os


Figura 4. Saída da execução do comando sp_helpdb

Figura 5. Saída da execução do comando sp_spaceused.

Figura 6. Saída de execução do comando sp_helpfile

Figura 7. Controle de crescimento de arquivos Change Autogrowth

arquivos da base. Desta for ma, você terá controle sobre o crescimento dos arquivos e caso seja necessário, pode criar outros arquivos de dados ou até mesmo avaliar a expansão do disco, caso não seja possível crescer ou criar arq uivos de dados. Note, no entanto, que esse deve ser o último recurso; • Maximum File Size: nesta opção você controla um limite máximo de crescimento para seu arquivo de dados habilitando a

opção Limited to MB , ou simplesmente habilita a opção Unlimited , quando o arquivo irá crescer enquanto houver espaço no disco. Contudo, esta última opção é perigosa, pois caso você tenha outros bancos alocados junto do mesmo arquivo sem um limite de crescimento, os demais bancos podem ser prejudicados. Por isso, sempre trabalhe com um limite de crescimento, controlando este limite pela opção Limited to MB.

Arquivos de Log de Transação Os arquivos de log de transação devem receber o mesmo esforço administrativo que os arquivos de dados e talvez até mais em função destes arquivos serem responsáveis pela recuperação do banco em caso de falhas. O comportamento de crescimento dos arquivos de log depende do modelo de recuperação o qual o banco está configurado. Existem três modelos atualmente que são explicados a seguir: • Full: grava nos logs de transação todas as mudanças executadas no banco. A tendência deste tipo de recuperação é que os arquivos de log cresçam bastante media nte as alterações ocorridas no banco; • Bulk-Logged: este modelo de recuperação registra nos arquivos de log apenas algumas transações, não sendo possível, portanto, fazer uma recuperação completa do banco com este modelo. Ele é usado no momento em que o banco irá receber uma alta carga de dados. Para que os log transactions não cresçam de forma descontrolada, se usa esta configuração; • Simple: este modelo não oferece suporte a backups de log de transação pelo fato de não escrever transações nos arquivos de log que possibilitem a recuperação do banco. Dessa forma, os logs crescem muito pouco. Este modelo é tipicamente usado em bases de dados que servem apenas como testes de implantação de sistemas, onde não é necessária a recuperação dispensando a ocupação de disco por crescimento dos arquivos de log. Em um ambiente de banco de produção, a maioria dos modelos de recuperação será configurada como modelo Full. Neste modelo, se registra todas as transações e mudanças dentro do banco de dados, possibilitando a restauração total em caso de desastres. Estas alterações são registradas nos arquivos de log transaction. Nesta situação você pode configurar na sessão Enabled Autogrowth , opção In_Megabytes (vide Figura 7), o crescimento de 500MB a 500MB. Com relação ao Maximum File Size , é a mesma regra para os arquivos de dados. O mesmo deve ser limitado através da opção Limited to (MB). Recomendamos, tanto para arquivos

de dados quanto para arquivos de log, não deixar os arquivos de dados crescerem à vontade no disco. Por mais que exija um esforço administrativo para monitorar os mesmos, lembre-se que este é o seu trabalho. Recomenda-se deixar 15% de espaço livre na unidade de disco para se ter condições de movimentar e criar arquivos em outras unidades com espaço disponível. Para alguns administradores de banco, é muito fácil apenas gerenciar instâncias pelo ambiente gráfico. No entanto, o SQL Server é muito mais do que isso e este artigo mostrou como você pode planejar a criação de seu banco de dados, os lugares corretos para cada tipo de arquivo e que você não pode simplesmente deixar o banco crescer sem que tenha um mínimo de esforço administrativo para acompanhar esse crescimento de forma controlada. Embora o SQL Server possua muitas funcionalidades gráficas, procure usar os códigos que foram descritos neste artigo. Não se limite apenas à parte gráfica deste SGBD. Assim você pode ampliar e aprimorar a sua admin istração através de códigos e gerar muitas outras fontes de relatórios para sua monitoria, além das procedures já inclusas no próprio SQL.

Autor Robson Moraes [email protected]

Experiência de 19 anos na área de informática tendo passado por áreas como analise de suporte gerente de configurações d e sistemas administrador de conta e a quatro anos como administrador de banco de dados focado em SQL Server mas tendo experiência com Oracle e MySQL.



21

Dominando os índices columnstore Melhore o desempenho de suas consultas no SQL Server

C

ada vez mais as empresas vêm provando que os dados produzidos dentro e fora do ambiente corporativo podem ser extremamente úteis para a definição de novas estratégias ou até mesmo criação de novos produtos e serviços. Neste contexto, armazenar os dados de uma forma estruturada e recuperável são requisitos básicos para um ambiente analítico. No mesmo sentido, à medida que um volume maior de informações é produzido e armazenado nos bancos de dados das empresas, os procedimentos para garantir um bom desempenho destes sistemas ganham ainda mais relevância. Um dos recursos de banco de dados utilizado para tratar o desempenho das aplicações são os índices, que são estruturas criadas para agilizar a execução de consultas disparadas contra determinadas tabelas. Estabelecendo uma analogia com o mundo físico, podese comparar os índices de banco de dados aos índices de um livro. A forma mais rápida do leitor encontrar um assunto específico em um livro é procurando o tema de interesse em seu sumário. Ao encontrar o assunto de interesse, o leitor acessa a página desejada sem a necessidade de passar pelas demais. Por outro lado, em situações em que o leitor não deseja procurar por assuntos específicos, mas sim efetuar uma leitura completa e sequencial das páginas, o índice passa a ser desnecessário, pois não será usado pelo leitor. No mundo de banco de dados nos deparamos com as mesmas preocupações, ou seja, existem situações em que os índices são úteis e outras em que apenas ocupariam espaço e processamento de máquina se fossem criados. Não será debatido aqui as melhores práticas para implementação e manutenção de índices de banco de dados. O nosso foco é mostrar uma nova opção para criação de índices incorporada no SQL Server 2012 que permite o armazenamento das informações dos índices em formato colunar. Antes de debater sobre índices colunares, no entanto, serão apresentados alguns conceitos básicos sobre as


Fique por dentro Este artigo é útil em situações em que se deseja melhorar o desempenho de consultas executadas em bancos de dados SQL Server. Algumas práticas comuns a este tipo de desafio é a avaliação da infraestrutura disponível, qualidade do código implementado e criação de índices. Este artigo se concentra neste último ponto e aborda a teoria e prática dos índices baseados em colunas (Columnstore). Este tipo de índice é recomendado para ambientes OLAP (Online Analytical Processing) nos quais usualmente são executados processos de carga de dados em massa e podem melhorar o desempenho de suas consultas em até 10 vezes.

estruturas de índices já existentes no SQL Server (índices clusterizados e não clusterizados). Estas opções continuam sendo importantes para o planejamento de um banco de dados que garanta integridade e bom desempenho em suas consultas. Os índices colunares devem ser introduzidos nas arquiteturas atuais de bancos de dados que utilizam o SQL Server como incremento às estruturas e padrões de projeto já existentes. Portanto, antes de começar a implementar este novo recurso, como em qualquer outro tipo de trabalho, é necessária uma análise detalhada sobre os possíveis ganhos de sua aplicação. Vale ressaltar ainda que os índices devem ser visualizados como parte de uma estratégia para melhorar o desempenho das aplicações. Sendo assim, quando se deseja tratar o problema otimização de a mbientes de banco de dados, deve-se levar em consideração também atividades como resizing e melhoria de processos.

Índices clusterizados e não clusterizados Existem dois grupos de índices no SQL Server: índices c lusterizados, também conhecidos como índices agrupados, e os índices não clusterizados, também chamados de índices não agrupados. Ambos são usados por desenvolvedores e administradores de banco de dados com o objetivo de melhorar os planos de execução das consultas feitas no banco de dados e, consequentemente, reduzir o tempo de execução das mesmas.

Figura 1. Estrutura de um índice não clusterizado

Como se pode notar, os índices clusterizados não possuem referências para as linhas da tabela original em que foram criados pelo fato de já armazenarem as informações da tabela em sua estrutura (não é necessário ter uma referência). Vale a pena destacar também a forma como os dados são pesquisados. Os índices clusterizados agrupam as informações da tabela (também de forma ordenada) em uma espécie de árvore binária, desta forma, ao buscar uma determinada informação, o SQL Server filtra as páginas em que a informação pesquisada poderia estar armazenada até chegar no local exato e retornar os dados.

Considerações sobre o uso de índices Figura 2. Estrutura de um índice Clusterizado e funcionamento de busca

Embora compartilhem objetivos em comum, índices clusterizados e não clusterizados armazenam os dados de índice em estruturas diferentes. Enquanto os índices não agrupados armazenam em sua estrutura uma referência para as linhas da tabela original, os índices agrupados armazenam em sua estrutura a própria informação da tabela original. Em termos de funcionamento, isto significa que os índices não clusterizados devem consultar a tabela original (em que o índice foi criado) para retornar os dados desejados em uma determinada consulta, por outro lado, os índices clusterizados já possuem as informações a serem retornadas em sua própria estrutura. Na Figura 1 é exemplificada a estrutura de um índice não clusterizado que foi criado sobre uma coluna chamada “nome”. Nota-se que ao invés do índice armazenar os dados originais da tabela em que foi criado, é gravada apenas uma referência para as linhas da mesma. Ao efetuar uma consulta na tabela pelo campo “nome” o mecanismo de banco de dados fará uma busca no índice, que já está ordenado, e o nome buscado será retornado. Cada registro do índice contém o endereço da sua respectiva linha na tabela. Desta forma, ao procurar uma determinada informação na estrutura do índice, o SQL Server identifica a linha e a página de dados em que esta informação está armazenada. É justamente neste ponto em que índices agrupados e não agrupados se diferem: na forma de armazenar os dados no índice e na forma em que os mesmos são pesquisados. Na Figura 2 é apresentada uma estrutura de um índice clusterizado e um exemplo de sua dinâmica de busca.

Existem cenários em que é mais recomendada a utilização de índices clusterizados e outros nos quais a aplicação de índices não clusterizados é mais apropriada. Para entender em quais situações aplica-se melhor um ou outro, vale a pena destacar as pr incipais diferenças entre estes dois tipos de índices: • Pode existir mais de um índice não clusterizado por tabela, ao

passo que apenas um índice clusterizado é permitido; • Comandos INSERTs e UPDATEs têm melhor desempenho

quando são disparados contra tabelas que possuem índices não clusterizados quando comparados a disparos contra tabelas com índices clusterizados; • Nos índices clusteri zados, a leitura dos dados é mais rápida e os

dados são ordenados fisicamente na tabela. Ao contrário dos índices não clusterizados, nos quais a leitura é mais lenta e os dados não são ordenados fisicamente na tabela (o índice armazena uma referência lógica para o local em que o dado está a rmazenado). Ambos os índices podem ser usados para agilizar o processamento de comandos SELECTs, no entanto, de alguma forma também podem prejudicar o desempenho de comandos de INSERTs e UPDATEs. A forma como os índices são projetados define o real ganho de desempenho das consultas no banco de dados. Pode até acontecer de a criação de índices resultar em mais perdas do que ganhos à aplicação. Consultas em que nenhum ou poucos f iltros (tanto em linhas quanto em colunas) são definidos, podem ser executadas com mais agilidade se o mecanismo de banco de dados analisar todas as informações da tabela (característica conhecida como table scan). Para ajudar no processo de manutenção dos índices, o SQL Server conta com uma ferramenta chamada Database Engine Edição 136 SQL Magazine •

23

Dominando os índices columnstore

Tuning Advisor. Esta ferramenta permite

analisar índices já existentes para entender se os mesmos estão sendo utilizados corretamente. O DTA também recomenda a criação de novos índices para um determinado banco de dados, fa z recomendações para criação de partições e efetua uma análise de impacto das recomendações efetuadas.

Fundamentos de row-store e columnstore

Figura 3. Armazenamento em linhas

Agora que já definimos alguns conceitos básicos sobre índices e abordamos aqueles mais tradicionais existentes no SQL Server, iniciaremos o debate sobre um novo tipo de índice incorporado no banco de dados da Microsoft. Desde sua versão 2012, o mecanismo de banco de dados do SQL Server conta com um poderoso recurso para aumentar o desempenho de suas consultas chamado índices columnstore. Índices columnstore são baseados na tecnologia VertiPaq, responsável pela inteligência de armazenamento e compressão de dados do SQL Server. Os índices columnstore usam um formato de dados colunar para armazenar e recuperar as informações, denominado columnstore, e podem melhorar em até 10 vezes o desempenho das consultas. Isso porque possibilita a otimização da seleção e compressão dos dados, como será detalhado à frente. Este recurso é apropriado para ambientes analíticos (OLAP – Online Analytical Processing) nos quais usualmente são executados processos de carga de dados em massa e consultas em tabelas que armazenam grandes históricos de informação. Ambientes analíticos são criados com o objetivo de fornecer flexibilidade aos processos de tomada de decisão. Usualmente contidos dentro de uma arquitetura de Business Intelligence , estes ambientes possibilitam que os usuários de negócio analisem as informações em diferentes dimensões, sem a necessidade de intervenção de uma equipe de TI para criar novas visões a todo momento. Isso é possível porque os metadados são organizados e dispon i bilizados aos usuários por meio de cubos e relatórios dinâmicos. É claro que novas demandas continuam existindo, dado a velocidade dos negócios atuais; no entanto, o ambiente garante mais velocidade de acessos aos dados e este fator pode ser determinante para a conquista de um novo negócio. Do ponto de vista de armazenamento de dados, ambientes analíticos costumam usar estruturas de dados conhecidas como Data Warehouse e Data Mart. A ideia que gira em torno destes conceitos é teoricamente simples: armazenar os dados de uma forma organizada, facilitando os processos de tomada de decisão. Na prática, existem diversos desafios que fazem com que projetos de Data Warehouse tenham elevados preços a altos riscos de fracasso.


Voltando ao SQL Server, pode-se trabalhar com dois tipos de armazenamento de dados: rowstore e columnstore. O primeiro, como o próprio nome sugere, trata o armazenamento de dados organizados por registros, ou seja, as informações são organizadas nas páginas de dados linha a lin ha. É o que acontece, por exemplo, com os tradicionais índices clustered do SQL Server. A Figura 3 esquematiza o formato de armazenamento de um índice que utiliza o conceito de rowstore. Os índices baseados em linhas podem ter características d iferentes no que se refere à ordenação ou agrupamento dos dados. No entanto, se o índice é baseado em linhas, obrigatoriamente armazenará em suas páginas de dados informações de linhas inteiras de uma tabela. As páginas de dados destes índices possuem taman ho de até 8Kb e, além de guardar as informações da tabela, possuem uma estrutura de cabeçalho e conexões para outras páginas. O conjunto de 8 páginas (64Kb) é denominado extensão. Isso significa que para armazenar as informações do índice de uma determinada tabela, o SQL Server pode alocar várias páginas (depende do tamanho da tabela). Já no tipo de armazenamento colunar (columnstore), as informações são organizadas nas página s de dados por colunas, ou seja, as páginas são alocadas e preenchidas com as i nformações de cada coluna da tabela em questão. A Figura 4 apresenta a aplicação do conceito de columnstore em um conjunto de dados. Em uma rápida comparação entre as Figuras 3 e 4 , pode-se observar claramente a mudança de abordagem entre o armazenamento de dados baseado em linhas e o armazenamento baseado em colunas. O principal ponto a se observar neste momento é a forma como as páginas de dados são preenchidas. Existem outros dois termos que aparecem na Figura 4: “Segment” e “Row Group”. Nos índices columnstore, um segment armazena informações de uma coluna que pertence a um grupo específico de linhas, conhecido como row group. Na prática, um row group pode agrupar 2 20 linhas (o que representa 1.048.576 linhas do conjunto de dados). Portanto, existe um segment para cada combinação de coluna e row group. Cada um destes segments é comprimido e armaze-

nado separadamente em tipos de dados LOB do mecanismo de banco de dados do SQL Server. Por fim, as consultas feitas em tabelas que utilizam índices columnstore usam o modo de processamento “batch”. Este é um mecanismo de processamento de consultas baseado em vetor que se beneficia de todas as vantagens proporcionadas pela estrutura de armazenamento em colunas.

Figura 4. Armazenamento em colunas

Considerações sobre desempenho O formato de armazenamento de dados em colunas oferece algumas vantagens em relação ao formato de armazenamento em linhas, entre estas vantagens destacam-se a capacidade de comprimir os dados de uma forma mais eficiente e o melhor desempenho na execução de alguns comandos SELECTs. Comandos SELECTs que buscam informações de apenas algumas colunas de uma tabela podem se beneficiar com a utilização de índices columnstore. Ao criar um índice deste tipo na tabela, o mecanismo de banco de dados é capaz de buscar as informações necessárias nas páginas de dados específicas em que as i nformações estão armazenadas (ou seja, é possível acessar apenas as páginas de dados que armazenam as informações das colunas desejadas), reduzindo assim as operações de I/O, usualmente responsáveis pelo maior tempo de processamento de uma consulta. Como as informações das colunas são armazenadas em uma mesma página de dados, o processo de compressão dos dados também é otimizado. Isso é possível porque as informações de uma mesma coluna possuem um padrão semelhante e tipicamente são mais redundantes quando comparadas às informações das linhas de uma tabela (que possuem colunas de diferentes tipos de domínios de informação). Este fato simplifica a compressão dos dados e garante o carregamento de mais informações em memória. Com esta otimização do uso de memória, o banco de dados reduz as operações de acesso ao disco, e novamente temos um ga nho de desempenho pela redução de operações I/O.

Índice columnstore na prática Para demonstrar a utilização de índices columnstore, foi preparado um ambiente com SQL Server 2014 com a base de dados de exemplo fornecida pela Microsoft: AdventureWorksDW. Esta base possui o formato de um Data Warehouse que, como mencionado anteriormente, é o tipo de ambiente mais recomendado para utilização de índices columnstore. Primeiramente é executada uma consulta utiliza ndo um índice clusterizado. Em seguida, a mesma consulta é executada contra uma base de dados com um índice columnstore. Ao final são apresentadas as principais diferenças de desempenho entre as duas execuções. Foi criada uma tabela (vide Listagem 1) com base nas tabelas FactInternetSales e DimSalesTerritory da base de dados AdventureWorksDW. O objetivo é obter uma massa de dados maior para que seja possível demonstrar o real impacto de desempenho fornecido pelo índice columnstore. Na Listagem 2 é apresentado o comando de INSERT utilizado para efetuar a carga da massa de dados na tabela criada na Listagem 1. Para essa carga de dados foi efetuado um JOIN com as tabelas FactInternetSales e DimSalesTerritory. Como é mostrado na linha 12, este comando de INSERT ( Listagem 2) será repetido 3.500 vezes para a construção da massa de dados. Listagem 1. Criação de massa de dados.

CREATE TABLE [dbo].[TableInternetSales]( [ProductKey] [int] PRIMARY KEY IDENTIT Y, SalesTerritoryCountry VARCHAR(200) NOT NULL, [UnitPrice] [money] NOT NULL, [TaxAmt] [money] NOT NULL ); Listagem 2. População da tabela TableInternetSales.

01 INSERT INTO TableInternetSales 02 SELECT 03 ST.SalesTerritoryCountry, 04 UnitPrice, 05 TaxAmt 06 FROM 07 FactInternetSales FS 08 JOIN 09 DimSalesTerritor y ST 10 ON 11 FS.SalesTerritoryKey = ST.SalesTerritoryKey 12 GO 3500 13 14 SET STATISTICS IO ON 15 GO 16 17 SET STATISTICS TIME ON 18 GO

As estatísticas de I/O e de tempo do SQL Server foram habilitadas para efetuar os testes de desempenho do índice columnstore (linhas 14 a 18 da Listagem 2). Para efetuar o teste foi construída a consulta da Listagem 3. Nesta consulta foram utilizadas algumas funções de agregação: a soma de UnitPrice , a média de UnitPrice e a soma de TaxAmt. Por fim, os dados foram agrupados por SalesTerritoryCountry. Edição 136 SQL Magazine •

25

Dominando os índices columnstore

Primeiramente, a consulta da Listagem 3 foi executada com base em um índice clusterizado. Em seguida foi criado um índice columnstore na tabela TableInternetSales (vide Listagem 4) com base nas colunas SalesTerritoryCountry , UnitPrice e TaxAmt e a consulta foi executada novamente. Listagem 3. Query construída para o teste de performance.

SELECT SalesTerritoryCountry, sum(UnitPrice) SUMUnitPrice, AVG(UnitPrice) AVGUnitPrice, Sum(TaxAmt) TaxAmt FROM TableInternetSales GROUP BY SalesTerritoryCountry; GO Listagem 4. Criação do Columnstore Index.

CREATE NONCLUSTERED COLUMNSTORE INDEX INDXCLS_SALESINTERNET ON TableInternetSales (SalesTerritoryCountry, UnitPrice, TaxAmt);

Conforme apresentado na Figura 5, é possível observar que neste caso o desempenho da consulta melhorou consideravelmente após a criação do índice columnstore. O número de leituras lógicas caiu para zero. Isso acontece porque neste tipo de índice as informaçõe s das colunas são armazenadas em uma mesma página de dados facilitando o acesso aos dados. Também é possível observar que o tempo de CPU e o tempo de execução foi diminuído aproximadamente em 98% e 96%, respectivamente. Vale ressaltar neste ponto que estas taxas de melhoria podem variar de acordo com o ambiente em que a consulta está sendo executada.

Figura 5. Comparativo das estatísticas de execução antes e depois da criação do columnstore index


Principais diferenças entre as versões SQL 2012 e 2014 A versão do SQL 2012 apresenta algumas limitações quanto ao uso de índices columnstore. Entre as principais restrições estão: 1) apenas índices não clusterizados são permitidos; 2) ao utilizar um índice columnstore a tabela se torna somente leitura (índice não atualizável). A versão 2014 do SQL Server trouxe algumas novidades, passando a permitir o uso de índices clusterizados atualizáveis. No entanto, há restrições quanto ao uso de índices columnstore do tipo clustered , entre elas o fato de não ser possível combinar outros índices na mesma tabela e o fato de não ser possível definir restrições do tipo unique, chave primária ou chave estrangeira. Além disso, todas as preocupações de uma estratégia de manutenção de índices devem ser levadas em consideração no momento de definir como as tabelas serão indexadas. Índices columnstore podem ser usados em qualquer banco de dados do SQL Server. Porém, ao utilizá-los em ambientes analíticos, existem grandes oportunidades para melhorar o desempenho das consultas pela própria ca racterística deste tipo de ambiente (tabelas com grandes volumes de dados e históricos de informação). Avalie sua aplicação em projetos de Business Intelligence , nas tradicionais estruturas de Data Warehouse ou Data Marts, por exemplo. Neste tipo de projeto é comum o consumo de informações por processos ad-hoc ou relatórios. Como primeiro passo, levante quais são as in formações mais selecionadas e mais filtradas pelos processos que consomem os dados. Estas informações podem ser candidatas para uma possível indexação por colunas.

Para projetos já implementados, cabe a análise dos próprios índices rowstore já existentes nas tabelas do ambiente. Eventualmente, estes próprios índices podem ser substituídos por índices baseados em colunas para melhorar o desempenho das aplicações. O DTA pode ser usado como ferramenta de apoio para a aná lise de novos índices ou manutenção dos índices já existentes. No entanto, não se esqueça de estudar os ganhos reais da aplicação de índices colunares em seu ambiente de dados. No cenário ideal, índices baseados em linhas e colunas devem atuar juntos dentro de um plano maior de gestão de dados e desempenho das aplicações.

Autor Weslley Moura [email protected]

Graduado em sistemas de informação pela FIAP, possui especialização em Business Intelligence pela mesma instituição e está concluindo seu mestrado em engenharia de software pelo IPT. Atualmente ocupa seu tempo com projetos de analytics e desenvolvimento de software nas empresas Rede, Pepsoft Sistemas e FITO.

Autor Emerson Silva

Links: SQL Server 2016 ColumnStore Technology – Channel 9 https://channel9.msdn.com/Shows/Data-Exposed/SQL-Server-2016-ColumnStoreTechnology

[email protected]

Graduado em ciência da computação pelo Centro Universitário Adventista de São Paulo. Atualmente ocupa o seu tempo com projetos de BI e infraestrutura em uma multinacional americana.

Entendendo o Column Store Index no SQL Server 2012 – TechNet

Você gostou deste artigo?

http://social.technet.microsoft.com/wiki/contents/articles/9251.entendendo-o-column store-index-no-sql-server-2012-pt-br.aspx

Dê seu voto em www.devmedia.com.br/sqlmagazine/feedback

Índices columnstore – MSDN

Ajude-nos a manter a qualidade da revista!

https://msdn.microsoft.com/pt-br/library/gg492088.aspx


27

Monitoramento automatizado o SQL Server Envie relatórios em HTML utilizando o SQL Agent e o Database Mail

O

monitoramento automatizado do banco de dados é uma tarefa importantíssima para o DBA, pois é através dele que são obtidas informações sobre a disponibilidade e saúde do ambiente quando o profissional não está em seu horário de trabalho. Obviamente, esses dados servem para alertar o administrador sobre um problema que está acontecendo ou para preveni-lo de um problema que poderá acontecer. Alguns monitoramentos indispensáveis devem ser contemplados no projeto de manutenção do ambiente. Vejamos: • Disponibilidade da instância e seus bancos de dados; • Utilização dos datafiles e transaction logs; • Consumo de recursos: CPU, disco e memória; • Rastreamento de queries mais lentas; • Situação dos índices (o quanto estão fragmentados); • Ocorrência de locks; • Jobs em execução acima do esperado; • Falha em Jobs agendados; • Disco I/O; • Verificação de erros no errorlog; • Situação das rotinas de backup.

Essas são apenas algumas checagens que devem ser realizadas periodicamente. No entanto, torna-se inviável o DBA verificar todos esses pontos em todos os ser vidores que ele administra. Para isso existe o monitoramento automatizado e, consequentemente, existem diversas soluções que podem ser adotadas. Em empresas cujo investimento não é um problema, são implementadas ferramentas dedicadas para tal propósito. Alguns exemplos são: Microsoft System Center, Nimsoft, Zabbix, entre outros. Porém, em empresas que esse investimento não pode ser pago e que, inclusive, o time de DBAs é reduzido, podem-se obter excelentes resultados de monitoramento configurando a gama de recursos que o SQL Server já tem a oferecer nativamente, sem custo adicional algum.


Fique por dentro Em diversas empresas, não existe uma solução dedicada para realizar o monitoramento do(s) servidor(es) SQL. Sendo assim, os recursos do próprio SQL Server podem ser conf igurados com esse objetivo. Nesse artigo será abordado o envio de relatórios formatados em HTML, utilizando o SQL Agent e o Database Mail. O exemplo descrito monitora os discos do servidor, permitindo a visualizaç ão de espaço total, livre e o percentual livre de cada unidad e.

Em diversas empresas há apenas um ou dois DBAs, o que exige mais controle e conhecimento dos ambientes de banco de dados, considerando que se houver algum problema, o próprio terá que resolver rapidamente. Portanto, ao configurar verificações mais importantes, já damos ao DBA mais tranquilidade e poder de ação com antecedência. Outro ponto importante é a melhoria contínua do desempenho no banco de dados. Com um monitoramento adequado, pode-se rastrear e melhorar queries mais lentas, verificação de locks constantes, jobs c ustosos, fragmentação de índices, crescimento dos datafiles e mais uma série de outras análises, o que tornará a aplicação mais eficiente após ações tomadas em cima desses dados. Esse artigo irá abordar a combinação de scripts T-SQL que gera relatórios em HTML de uma determinada análise do ambiente, o SQL Agent para agendar essa análise em períodos regulares e o Database Mail para envio do relatório gerado.

O Database Mail O Database Mail é uma solução para envio de mensagens do mecanismo de banco de dados do SQL Server. Por padrão, o recurso vem desativado e é possível ativá-lo por meio do assistente de configuração. Sua configuração é simples, baseada apenas em um perfil associado a uma conta SMTP. Há configurações adicionais de parâmetros do sistema em que se restringe o tipo de arquivo (extensão) anexado nas mensagens ou o seu tamanho, por exemplo.

Para usar o Database Mail, o usuário deve ser membro da função de banco de dados DatabaseMailUserRole no banco de dados msdb. As informações de configuração do Database Mail são armazenadas no banco de dados msdb e a procedure sp_send_dbmail contida neste banco é utilizada para a formatação e envio das mensagens. O Database Mail deve ser primeiramente configurado, pois o nome do perfil é utilizado no script T-SQL.

O script T-SQL Nesta alternativa de monitoramento, a lógica da análise realizada de forma automática está no script T-SQL. Portanto, para que a verificação seja consistente, é necessário ter em mente o que será analisado e como será apresentada a informação. A partir do SQL Server 2005, informações relacionadas à saúde da instância e seus objetos são encontradas nas DMVs e DMFs (visões e funções de gerenciamento dinâmico). Através das DMVs/DMFs podemos coletar e cruzar dados importantes da utilização do ambiente. Com esses dados coletados é possível determinar dentro da lógica do script T-SQL um threshold (margem/limite) que, após ser ultrapassado ou se estiver abaixo do esperado, o DBA deve dar atenção. É possível realizar análises diversas que podem ser formatadas em HTML e enviadas por e-mail, como fragmentação

Figura 1. Perfil do Database Mail

de índices, query mais lentas ou utilizadas, waits, ocupação dos datafiles, entre muitas outras. No exemplo proposto nesse artigo, o script T-SQL verifica o espaço total, livre e o percentual livre calculado para cada unidade de disco que aloca arquivos dos bancos de dados. Para facilitar a visualização do que o script faz, o ideal é dividi-lo em partes. Por exemplo: a consulta das informações requeridas, o tratamento das informações dentro do HTML e o envio do relatório através da chamada da procedure msdb.dbo.sp_send_dbmail .

O SQL Agent O SQL Agent é o serviço responsável por executar jobs agendados. Os componentes deste serviço podem ser classificados em: jobs, steps, schedules e alerts. Cada job criado contém um ou mais steps e cada step sua própria instrução. Cada step também poderá ter um tipo diferente de instrução como, por exemplo: um script T-SQL, um script PowerShell ou um pacote do Integration Services. Vai depender de cada finalidade. Além disso, pode ser definido um usuário para executar cada step, um arquivo de log de saída e o comportamento em caso de sucesso ou falha na execução. Nos schedules temos os tipos: quando o serviço do SQL Agent for iniciado, quando a CPU estiver ociosa, execução recorrente

e execução apenas uma vez em uma data e horário determinado. Em tarefas administrativas e de manutenção dos bancos, o tipo mais utilizado é o recorrente. No tipo recorrente temos a frequência que poderá ser diariamente, semanalmente e mensamente. Em cada tipo de frequência também haverá as configurações de intervalo, data de início e fim de execução de cada job. Para cada job podemos configurar mais de um schedule. Pode ser configurado ainda um alert que é uma resposta automática a um evento. Um alert responde a uma condição e notifica um operador sobre o evento ou executa um job. As roles SQLAgentUserRole , SQLAgentReaderRole e SQLAgentOperatorRole são responsáveis por dar permissões na utilização do SQL Agent, cada uma com seu conjunto de permissões. Ou seja, o usuário precisa ser membro de uma ou mais dessas roles para usar o serviço. Se o usuário for sysadmin, já terá acesso total e não precisará ser membro das roles mencionadas. Por meio do SQL Agent, o script T-SQL da análise desejada poderá ser executado em períodos regulares e os resultados enviados ao DBA.

Configuração do perfil e conta no Database Mail O perfil deverá ter obrigatoriamente um nome. Após dar um nome ao perfil, é necessário adicionar uma conta SMTP com os dados de saída e autenticação. Para isso, é necessário saber os dados do servidor SMTP: seu nome, porta e se requer SSL (conexão segura). Na Figura 1, o perfil foi configurado com o nome SQLAgentDBA e a conta SMTP já está adicionada. Veremos suas configurações na sequência. A conta SMTP também exige um nome. Neste caso, DBA – Monitoring , conforme a Figura 2. A descrição é opcional. Nas configurações de Saída (Outgoing mail server), o e-mail (endereço de envio do monitoramento que estamos configurando), o nome do servidor e número da porta são obrigatórios. Neste exemplo foi utilizado um endereço do Hotmail e o nome do servidor correspondente: smtp.live.com. Ao habilitar a opção SSL, uma porta segura deverá ser definida (neste caso 587). Edição 136 SQL Magazine •

29

Monitoramento automatizado o SQL Server

Os campos Display name e Reply e-mail são opcionais. O campo Display name é utilizado para definir o nome que será mostrado nas mensagens que chegam ao programa de mensagens. Por exemplo, SQL Agent – DBA Monitoramento. Ou podemos deixar com o mesmo endereço do campo anterior. O Reply e-mail , por sua vez, será usado para respostas às mensagens que chegaram. Por exemplo, ao responder uma mensagem de monitoramento, esta tam bém será enviada a outro endereço que foi definido neste campo. Também teremos as configurações de autenticação: Windows (credencias configuradas para o serviço do SQL Server), básica e anônima. A opção autenticação de Windows faz com que o Database Mail utilize o usuário e senha que iniciam o serviço do SQL Server. Este usuário deverá ter um endereço de e-mail associado e suas configurações são definidas no controlador de domínio ( Active Directory). Na autenticação básica são especificados o usuário e senha associados ao servidor definido nas configurações de saída. E na autenticação anônima não é necessário definir usuário e senha, mas o servidor SMTP precisa estar configurado para não exigir autenticação.

A construção do script T-SQL A DMF sys.dm_os_volume_stats está disponível na versão SQL Server 2008 R2 SP1 ou posterior. Portanto, o script que será apresentado não será compatível em versões anteriores. Em versões anteriores é possível usar a procedure não documentada xp_fixeddrives, porém, a mesma só traz o espaço livre em MBs da unidade. Há também a possibilidade de utilizar a opção Ole Automation Procedures (ativada pelo sp_configure), mas não é recomendável por questões de segurança. No script apresentado na Listagem 1, inicialmente é feita uma consulta na tempdb.sys.tables para verificar se a tabela temporária chamada #Drives existe. Caso exista, é eliminada e criada com os campos drive, freespace , totalsize e percentfree e os seus devidos tipo de dados. Lembrando que o banco de dados tempdb armazena as


Figura 2. Configuração da conta SMTP vinculada ao perfil do Database Mail Listagem 1. Script T-SQL – Utilização de discos – Parte 1.

-- Cria a tabela temporária #Drives SET NOCOUNT ON GO IF EXISTS ( SELECT name FROM tempdb.sys.tables WHERE name LIKE ‘#Drives%’) DROP TABLE #Drives GO CREATE TABLE #Drives ( Drive CHAR(3) PRIMARY KEY , FreeSpace INT NULL , TotalSize INT NULL , PercentFree AS ( CONVERT(FLOAT, FreeSpace) / TotalSize ) * 100 ) GO -- Insere as informações na tabela temporária consultando a DMF sys.dm_os_volume_stats -- (apenas dos discos que estão armazenando datafiles) INSERT #Drives ( Drive , FreeSpace , TotalSize ) SELECT DISTINCT dovs.volume_mount_point, CONVERT(INT, dovs.available_bytes / 1048576.0) , CONVERT(INT, dovs.total_bytes / 1048576.0) FROM sys.master_files mf CROSS APPLY sys.dm_os_volume_stats(mf .database_id, mf .file_id) dovs ORDER BY dovs.volume_mount_pointASC GO -- Variáveis para envio de e-mail DECLARE @tableHTML NVARCHAR(MAX) , @Subject VARCHAR(100)

tabelas temporárias e estas são eliminadas quando a conexão é fechada. Não crie tabelas físicas nesse banco, pois toda vez que a instância é reiniciada, o tempdb é recriado e todas as informações contidas nele são perdidas. Neste exemplo, é importante relatar a coluna PercentFree que faz o cálculo percentual do espaço livre obtido das colunas FreeSpace (números em MB) e TotalSize (números em MB). No segundo bloco são inseridas as informações na tabela temporária a partir da consulta na DMF sys.dm_os_volume_stats . É feito o cálculo nas colunas available_bytes e total_bytes para obter-se os valores em megabytes. O cross apply entre as tabelas sys.master_files e sys.dm_os_volume_stats tem a mesma finalidade que um inner join. Em seguida, já na Listagem 2 , o HTML é montado e tratado dentro um de SELECT com a expressão CASE que define a cor dos campos em vermelho quando o percentual livre for menor ou igual a 15% do espaço total de cada unidade. Essa montagem é feita dentro da variável @tableHTML que é definida com o tipo nvarchar(max). O nvarchar(max) tem o tamanho de armazenamento máximo de 2GB, ou seja, não haverá problema de truncamento dentro da variável @tableHTML. Seu tamanho é mais que suficiente para esse objetivo. Listagem 2. Script T-SQL – Utilização de discos – Parte 1.

-- Monta o HTML para o envio do relatório com as informações de espaço total/ livre SET @tableHTML = N’
Utilização de Discos
’ + N’’ + N’’ + CAST(( SELECT CASE WHEN [PercentFree] <= 15 THEN ‘#FF0000’ END AS ‘td/@BGCOLOR’ , td = Drive , ‘’ , ‘right’AS ‘td/@align’, CASE WHEN [PercentFree] <= 15 THEN‘#FF0000’ END AS ‘td/@BGCOLOR’ , td = TotalSize , ‘’ , ‘right’AS ‘td/@align’, CASE WHEN [PercentFree] <= 15 THEN‘#FF0000’ END AS ‘td/@BGCOLOR’ , td = FreeSpace , ‘’ , ‘right’AS ‘td/@align’, CASE WHEN [PercentFree] <= 15 THEN‘#FF0000’ END AS ‘td/@BGCOLOR’ , td = CONVERT (NUMERIC(10, 2), [PercentFree]) FROM #Drives FOR XML PATH(‘tr’) , TYPE ) AS NVARCHAR(MAX)) + N’
Drive Total (MB) Livre (MB) % Livre
’;

-- Chama a procedure sp_send_dbmail para envio do e-mail utilizando o perfil -- SQLAgentDBA criado no Database Mail SET @Subject = ‘Utilização de discos do servidor: ‘ + @@Servername EXEC msdb.dbo.sp_send_dbmail @profile_name = ‘SQLAgentDBA’, @recipients = ‘[email protected]’, @body = @tableHTML, @subject = @Subject, @body_format = ‘HTML’

Finalmente, ocorre a chamada da procedure sp_send_dbmail , contida na msdb, cujos parâmetros básicos neste exemplo são: o nome do perfil configurado no Database Mail (variável @profile_name), os destinatários (variável @recipients), o corpo (variável @body) que foi formatado dentro da @tableHTML , o assunto (variável @subject) que contém a função @Servername para retornar o nome do servidor no título do e-mail e a definição do formato HTML no corpo do e-mail (variável @body_format). A partir desse script podemos criar um procedimento armazenado (stored procedure), o que torna os parâmetros mais práticos de serem alterados. Na sequência foi criada, dentro do banco de sistema máster, a procedure sp_monitora_disco , em que temos os seguintes parâmetros: a variável @to receberá o e-mail do destinatário, a variável @subject será responsável pelo assunto da mensagem e a @PercentFree assumirá o percentual livre do disco. Note que agora teremos na montagem do HTML dentro da variável @tableHTML a @PercentFree e não valores fixos para comparar com a coluna PercentFree. Veja nas Listagens 3 a 5 o código da procedure e como executá-la. Listagem 3. Procedure sp_monitora_disco – Parte 1.

USE [master] GO CREATE PROCEDURE [dbo].[sp_monitora_disco] @to varchar(200), @subject varchar(100), @PercentFree int AS SET NOCOUNT ON -- Cria a tabela temporária #Drives IF EXISTS ( SELECT name FROM tempdb.sys.tables WHERE name LIKE ‘#Drives%’) DROP TABLE #Drives CREATE TABLE #Drives ( Drive CHAR(3) PRIMARY KEY , FreeSpace INT NULL , TotalSize INT NULL , [PercentFree] AS ( CONVERT(FLOAT, FreeSpace) / TotalSize ) * 100 ) -- Insere as informações na tabela temporária consultando a DMF -- sys.dm_os_volume_stats (apenas dos discos que estão armazenando datafiles) INSERT #Drives ( Drive , FreeSpace , TotalSize ) SELECT DISTINCT dovs.volume_mount_point, CONVERT(INT, dovs.available_bytes / 1048576.0) , CONVERT(INT, dovs.total_bytes / 1048576.0) FROM sys.master_files mf CROSS APPLY sys.dm_os_volume_stats(mf .database_id, mf .file_id) dovs ORDER BY dovs.volume_mount_pointASC -- Variáveis para envio de e-mail DECLARE @tableHTML NVARCHAR(MAX)


31


Listagem 4. Procedure sp_monitora_disco – Parte 2.

-- Monta o HTML para o envio do relatório com as informações de espaço total/livre SET @tableHTML = N’
Utilização de Discos
’ + N’’ + N’’ + CAST(( SELECT CASE WHEN [PercentFree] <= @PercentFree THEN ‘#FF0000’ END AS ‘td/@BGCOLOR’ , td = Drive , ‘’ , ‘right’AS ‘td/@align’, CASE WHEN [PercentFree] <= @PercentFree THEN‘#FF0000’ END AS ‘td/@BGCOLOR’ , td = TotalSize , ‘’ , ‘right’AS ‘td/@align’, CASE WHEN [PercentFree] <= @PercentFree THEN‘#FF0000’ END AS ‘td/@BGCOLOR’ , td = FreeSpace , ‘’ , ‘right’AS ‘td/@align’, CASE WHEN [PercentFree] <= @PercentFree THEN‘#FF0000’ END AS ‘td/@BGCOLOR’ , td = CONVERT (NUMERIC(10, 2), [PercentFree]) FROM #Drives FOR XML PATH(‘tr’) , TYPE ) AS NVARCHAR(MAX)) + N’
Drive Total (MB) Livre (MB) % Livre
’; -- Chama a procedure sp_send_dbmail para envio do e-mail utilizando o perfil -- SQLAgentDBA criado no Database Mail SET @Subject = @Subject + @@Servername EXEC msdb.dbo.sp_send_dbmail @profile_name = ‘SQLAgentDBA’, @recipients = @to, @body = @tableHTML, @subject = @Subject, @body_format = ‘HTML’ RETURN 0 GO

Criando um novo job no SQL Agent

No serviço do SQL Agent é criado um novo job ( Figura 3) cujos componentes foram comentados anteriormente. Neste exemplo, o job foi criado com o nome DBA - Disk Space (vide Figura 4). Os campos categoria e descrição da aba Geral são opcionais. Mas é recomendável fazer uma pequena descrição do que é o job faz, pois caso outro DBA analise-o já será mais fácil entendê-lo.

Figura 3. Criação de um novo job

Figura 4. Job DBA - Disk Space

Listagem 5. Chamada da procedure sp_monitora_disco.

EXEC master.dbo.sp_monitora_disco @to = ‘[email protected];[email protected]’, @subject = ‘Utilização de discos do servidor: ‘, @PercentFree = 15

Após criar a procedure, ela estará pronta para ser utilizada. O usuário que a chamar precisa ter permissões administrativas na instância. Essas permissões são necessárias devido à consulta nas tabelas de sistema que o script faz. Dando permissões nas roles de servidor dbcreator e processadmin ou apenas na sysadmin, o usuário terá os direitos necessários para executar a procedure. É importante dizer que a role sysadmin dará permissões totais ao usuário na instância. Na variável @to poderá ser adicionado mais de um e-mail destinatário. Estes deverão ser separados por ponto e vírgu la (;). Na variável @PercentFree foi definido o valor de 15%, ou seja, quando a unidade estiver com o percentual igual ou menor do que 15% livre de espaço, o vermelho será definido como cor de fundo da linha que corresponde a unidade. A qualquer momento que houver necessidade de alterar os parâmetros, é só fazê-lo na chamada da procedure e não mais no corpo do script. Vamos agora agendar a execução do script no SQL Agent.


Na aba steps iremos adicionar um novo passo que receberá o script. O nome do passo (step name) deverá ser sugestivo ao que ele realmente faz nas instruções. Em Jobs com mais de um step, é importantíssimo essa boa prática, pois facilita a identificação e entendimento de todo o processo. O tipo, neste caso, é o script T-SQL e o database setado é o master. Na Figura 5 podemos ver que foi adicionado mais de um destinatário de e-mail na variável @to.

Figura 5. Chamada da procedure sp_monitora_disco no step do job

Figura 6. Agendamento configurado para o script executar a cada hora

Por exemplo, Diário – A cada hora. Esse levantamento pode ser feito consultando e cruzando os registros das tabelas msdb.dbo .sysjobs , msdb.dbo.sysjobschedules e msdb.dbo .sysschedules.

Histórico de execuções do job

Figura 7. Detalhes do agendamento

Figura 8. Detalhes do histórico

Nas propriedades do job há a opção “visualizar histórico”. Ao acessar essa opção, temos o registro de execuções do job com as datas e como foi concluído, com falha ou sucesso. Caso dê falha, é pos sível identificar a causa através do histórico (vide Figura 8), expandindo o registro e verificando a mensagem na área abaixo da lista de registros. Há casos em que a mensagem contida nessa área é truncada e não é possível visualizar o restante. Deste modo, é preciso apontar um arquivo de saída na aba Avança do dentro do step e após executar o job, é só abrir o arquivo criado e verif icar a mensagem completa (Figura 9).

Exemplo do e-mail recebido Finalmente, o resultado e a parte mais interessante de tudo isso: ao receber o email, teremos as informações desejadas de forma organizada e clara. Teremos uma ta bela com as colunas Drive , Total (MB), Livre (MB) e % Livre. Nesta máquina, o % Livre da unidade E está abaixo de 15% e por isso a cor de preenchimento está em vermel ho, conforme demonstra a Figura 10.

Outras análises

Figura 9. Arquivo de saída do step

Na aba Schedules, o agendamento deverá ser configurado de acordo com cada ambiente. No nosso caso, o job é recorrente, será executado todos os dias, a cada hora no período de 00hs as 23h59min (Figuras 6 e 7). Um nome sugestivo ao agendamento também é importante, pois ajuda no levantamento de informações de quais jobs executam com uma determinada frequência.

Como dito anteriormente, as DMVs e DMFs são recursos poderosos para checar o comportamento da instância, databases, queries, conexões e sistema operacional. Algumas DMVs/DMFs importantes que podem auxiliar nessa atividade são: • Relacionadas a execuções:

- sys.dm_exec_connections; - sys.dm_exec_sessions; - sys.dm_exec_requests; - sys.dm_exec_cached_plans; Edição 136 SQL Magazine •

33


- sys.dm_exec_query_plan; - sys.dm_exec_sql_text; - sys.dm_exec_query_stats. • Relacionadas a índices:

- sys.dm_db_index_physical_stats; - sys.dm_db_index_usage_stats. • Relacionadas ao sistema operacional:

- sys.dm_os_performance_counters; - sys.dm_os_schedulers; - sys.dm_os_nodes; - sys.dm_os_waiting_tasks; - sys.dm_os_wait_stats. • Relacionadas a I/O (Entrada e saída em disco):

- sys.dm_io_virtual_file_stats.

alteração de parâmetros é sempre mais prática. A periodicidade dos agendamentos dependerá da análise que está sendo feito e também do ambiente. Portanto, customize conforme seu servidor de banco de dados requer. Além disso, faça testes e comprove o resultado das verificações antes de colocar em produção. Tome o cuidado também de gerar o script de cada job e salvar em um local seguro e sempre fazer backup dos bancos de sistema msdb e master. Caso haja algum desastre no ambiente, a recuperação das rotinas será muito mais rápida. Para concluir, essa alternativa de monitoramento é mais trabalhosa, porém com um resultado eficaz e customizado. E, claro, o DBA será sempre avisado se o banco de dados precisa de uma ação imediata ou uma correção preventiva.

Autor Jonatas dos Anjos jonatasdosanjos @hotmail.com

Profissional de TI há 10 anos, sendo quatro como DBA SQL Server. Domínio e interesse em boas práticas de admi nistração, monitoramento e recursos de alta disponibilidade do SQL Server. Graduado em Tecnologia de Banco de Dados pela FIAP e Microsoft Certified Professional (MCP). Links: Database Mail Figura 10. E-mail recebido

https://msdn.microsoft.com/pt-br/library/Hh245116(v=sql.110).aspx DMF sys.dm_os_volume_stats https://technet.microsoft.com/pt-br/library/hh223223(v=sql.105).aspx

É possível utilizar os recursos que o SQL Server oferece para monitorar a instância e seus bancos de dados. Há diversas análises que podem ser realizadas através das DMVs/DMFs, dando ao DBA informações importantes sobre a saúde do ambiente. O Database Mail e o SQL Agent são ferramentas poderosas e práticas que poderão ser utilizadas em diversas situações. Portanto, vale a pena pesquisar e testar cenários e análises variadas que darão cada vez mais auxílio às funções do administrador. Sempre desenvolva o script em partes para facilitar o atendimento. Após montar a consulta, monte o HTML conforme desejar e chame o Database Mail. Procure utilizar procedures, pois a


Procedure sp_send_dbmail https://msdn.microsoft.com/pt-br/library/ms190307(v=sql.110).aspx

Disk Space Monitoring: How To http://sqlmag.com/blog/disk-space-monitoring-how



35

Paralelismo no SQL Server Entendendo como o SQL Server trabalha com planos paralelos

V

ivemos um período onde os processadores não evoluem mais tão rapidamente a cada novo ciclo de lançamento dos fabricantes. Em contrapartida, encontramos cada vez mais processadores em uma única máquina. Diante disso, é necessário que os programas passem a explorar o paralelismo em seu código, com múltiplas threads em execução, potencializando o uso do hardware à sua disposição. O paralelismo possui um objetivo muito nobre, que é explorar a concorrência de processamento em um programa com o objetivo de resolver um problema em menos tempo. Porém, é importante frisar que isto

não significa que o uso de recursos será menor, pelo contrário, o tempo total de processamento (uso efetivo das CPUs) tende a ser maior, pois além da execução é necessário incluir no código mecanismos de sincronização das threads. Atualmente é comum encontrarmos instâncias do SQL Server em servidores com 16, 32 ou mais núcleos (cores) de processamento. Diante desse cenário, é desejável que a engine possa explorar o paralelismo em seu código, ainda mais considerando que o SQL Server é licenciado por núcleo e não por outros fatores como, por exemplo, quantidade de memória. E mesmo que o código do SQL Server tenha diversos trechos multi-thread , muitos deles não podem ser vistos ou são transparentes para nós, “usuários”. No entanto, existe um deles, assunto cerne deste artigo, onde podemos ver claramente a utilização de múltiplos núcleos de processamento: os planos de execução que possuem operadores paralelos.

Plano de execução paralelo Definimos um plano paralelo como aquele composto por operadores que exploram o paralelismo em sua árvore de execução. Neste contexto, um conjunto de operadores paralelos é o que determina o que chamamos de zona paralela. As zonas existem pelo fato do plano de


Fique por dentro Este artigo tem por objetivo apresentar aspectos do funcionamento da engine do SQL Server no que toca a execução de consultas utilizando operadores paralelos, isto é, e xplorando hardwares que possuem diversos processadores. O artigo será útil para o leitor entender os conceitos de paralelismo, analisar planos paralelos, conhecer detalhes do otimizador de consultas, determinar as corretas configurações de uma instância do SQL Ser ver e, naturalmente, ganhar uma sólida base para aplicar em seu ambiente as melhores práticas, fazendo corretamente o troubleshooting ou tuning de consultas paralelas.

execução nunca ser 100% paralelo, pois sempre o último operador deve ser não paralelo (serial) para que a engine possa retornar os registros da consulta a partir de uma única thread, chamada de coordenadora. Dito isso, como primeiro exemplo deste artigo, faremos a c omparação entre a execução de duas consultas, uma serial e outra paralela, com o intuito de apresentar como são processados os registros em um plano com zona paralela. O detalhamento de como interpretar um plano de execução e seus operadores está além do escopo do artigo, porém é importante que se tenha o conhecimento do que são e como podem ser gerados através do SQL Server Management Studio. Você encontra um artigo sobre planos de execução na edição 119 da SQL Magazine. Na Listagem 1 demonstramos uma consulta simples, que conta a quantidade de registros na tabela dbo.bigTransactionHistory fazendo uso da hint “MAXDOP 1”, que força o SQL Server a não utilizar paralelismo para esta consulta. O termo MAXDOP é bastante adotado por ser uma redução de MAX Degree Of Parallelism , ou grau máximo de paralelismo, e também será empregado ao longo deste artigo. Como pode ser verificado na Figura 1 , o plano de execução é simples. O SQL Server opta por percorrer um índice não cluster por completo (index scan), retornando cada um dos registros para ser contabilizado pelo próximo operador ( stream aggregate), e por

fim devolvendo o resultado para o cliente (o compute scalar não é relevante neste contexto). Listagem 1. Consulta simples, sem paralelismo.

USE AdventureWorks2012 GO SET STATISTICS TIME ON; SELECT COUNT(*) FROM dbo.bigTransaction History OPTION (MAXDOP 1); GO

Esse plano de execução tem um custo total de 150.456 e como saída gerada pelo comando SET STATISTICS TIME ON , temos: “SQL Server Execution Times CPU time = 4130 ms, elapsed time = 4139 ms”. Isto é, o SQL Server executou toda a consulta em pouco mais de quatro segundos, utilizando praticamente o mesmo tempo de processamento (tempo efetivo de uso dos ciclos da CPU) para executar os operadores definidos no plano de execução. Ao executar a mesma consulta removendo a hint MAXDOP 1, damos liberdade para que o SQL Server considere o paralelismo ao determinar qual será o plano de execução para esta consu lta. Como resultado da consulta da Listagem 2 , temos um plano com operadores que permitem a execução paralela, marcados por um círculo laranja com duas setas, como pode ser verificado na Figura 2. Listagem 2. Consulta simples, com paralelismo.

USE AdventureWorks2012 GO SET STATISTICS TIME ON; SELECT COUNT(*) FROM dbo.bigTransactionHistory; GO

Ao analisar o plano de execução, verificamos que o SQL Server decide utilizar várias threads (ou workers, na visão da engine do SQL Server) para executar a consulta. O número exato de threads pode ser verificado apenas na janela de propriedades de um dos operadores paralelos. Para fins didáticos neste exemplo, no entanto, considere o uso de oito threads para esta execução.

Figura 1. Plano de execução simples, sem paralelismo

Figura 2. Plano de execução simples, com paralelismo

Ao iniciar o processamento da consulta, o index scan é executado por oito threads, onde cada uma fica responsável por recuperar parte dos dados, seguido do stream aggregate , que contabiliza a quantidade de registros que cada thread leu. Isto é, suponha que cada thread leu uma média de três milhões de registros do índice, o stream aggregate vai contabilizar por thread o número exato de registros lidos, fazendo com que cada thread gere em sua saída um único registro (o valor efetivo da contagem). A partir daí o SQL Server introduz um novo operador lógico, chamado gath er streams , que tem por objetivo coletar o resultado dos diferentes fluxos de entrada (oriundos de cada uma das threads em execução) e gerar um único fluxo de saída. Deste modo, no nosso cenário será produzido na saída do operador um conjunto de oito registros, cada um com o resultado contabilizado das threads pelo operador anterior. Esse operador encerra a zona paralela do plano de execução, restando

ao SQL Server fazer a soma dos oito registros produzidos utilizando o operador stream aggregate não paralelo (novamente o compute scalar é irrelevante). Esta divisão de trabalho entre os operadores é ilustrada pela Figura 3 , que mostra parte do fluxo de dados manipulada pelos quatro primeiros operadores do plano de execução paralelo. Esse plano de execução tem um custo total de 110.623, um custo menor que o plano serial, e como saída gerada pelo comando SET STATISTICS TIME ON , temos: “SQL Server Execution Times CPU time = 8374 ms, elapsed time = 1445 ms ”. Isto é, o SQL Server executou toda a consulta em menos de um segundo e meio, menos da metade do tempo original. Porém, o tempo de processamento foi maior, o dobro do plano serial. Em outras palavras, gasta-se mais recursos para executar a consulta em menos tempo. A partir da próxima seção vamos analisar em mais detalhes como o otimizador de consultas do SQL Server considera a Edição 136 SQL Magazine •

37

Paralelismo no SQL Server

utilização de planos de execução com paralelismo e como isso influencia o custo dos operadores em um plano, além de discutir melhores práticas na configuração do SQL Server. Para executar os scripts deste artigo, é necessário fazer o download (ver seção Links) do banco de dados de exemplo AdventureWorks2012, disponibilizado pela Microsoft no CodePlex e utilizar o script “Make Big AdventureWorks” para criar tabelas com maior volume de dados. Vale ressaltar que diferentes computadores e instâncias e versões do SQL Server podem gerar variações nos planos de execução que estão sendo apresentados neste artigo por conta das pecul iaridades de cada ambiente. Para este artigo foi utilizado o SQL Server 2012 Developer Edition com o Service Pack 2.

bom o suficiente seja encontrado, o SQL Server repete a fase de otimização considerando a utilização de paralelismo. É importante citarmos mais um operador, além dos operadores tradicionais do SQL Server, como clustered ou non-clustered index scan, index seek, lookup, table scan, aggregate (stream and hash), sort,

entre outros. Para os planos paralelos existe um operador específico, chamado de “Exchange”, e este pode assumir diferentes papéis nos planos de execução, separados em três operações lógicas: • Distribute streams: recebe um único fluxo de entrada e divide-o em diversos fluxos de saída, de acordo com o grau de paralelismo utilizado na consulta; • Repartition streams: recebe diversos fluxos de entrada e encaminha os registros para diversos fluxos de saída. É comum encontrarmos esse operador como uma tentativa do SQL Server de balancear o número de registros entre as threads, permitindo uma execução mais homogênea (e eficiente) do plano de execução; • Gather streams: recebe diversos fluxos de dados oriundos de um operador e une os fluxos em um único fluxo de saída. Para exemplificar o comportamento do otimizador de consultas, vamos considerar quatro consultas diferentes sobre a mesma ta bela (vide Listagem 3). E para garantir o comportamento correto do exemplo, é importante verificar se a configuração do “ cost threshold for parallelism” está definida como 5 antes da execução Listagem 3. Analisando o comportamento do otimizador em relação ao paralelismo.

USE AdventureWorks2012 GO

Figura 3. Fluxo de dados parcial no plano de execução paralelo

Otimizador de consultas e paralelismo O otimizador de consultas ( query optimizer ou QO) é, provavelmente, o componente de software mais elaborado do SQL Server – na verdade, de todo banco de dados relacional (Oracle, DB2, MySQL, Postgre, etc.). Esta complexidade pode ser traduzida em um custo alto para se produzir os planos de execução, devido ao fato de que para se otimizar uma consulta com um pequeno número de tabelas, as diferentes combinações dos elementos podem gerar um conjunto enorme de possíveis planos de execução. Diante disso, o SQL Server divide a otimização em fases, com pré-requisitos e limites de custo, procurando gerar um plano que seja bom o suficiente dado o custo do processo de otimização. Nesse ínterim entra a configuração de instância “ cost threshold for parallelism”, definida por padrão com o valor 5, e que na tradução literal significa limite de custo para paralelismo. A configuração é utilizada entre uma das fases de otimização (o detalhamento de cada uma está fora do escopo deste artigo), pois chega um determinado momento em que o SQL Server vai analisar a listagem de todos os possíveis planos gerados pelo QO e validar se existe algum plano com custo menor do que o definido pela conf iguração citada. Caso nenhum plano atenda a condição, que um plano


-- Exibe as configurações avançadas EXEC sys.sp_configure‘show advanced options’, 1 RECONFIGURE -- Verifica a configuração do SQL Server EXEC sys.sp_configure‘cost threshold for parallelism’ -- Analisando os custos dos planos de execução SELECT ProductID, COUNT( TransactionID), SUM(Quantity), SUM(ActualCost) FROM dbo.bigTransactionHistory WHERE ProductID BETWEEN 1001 AND 1010 GROUP BY ProductID GO SELECT ProductID, COUNT( TransactionID), SUM(Quantity), SUM(ActualCost) FROM dbo.bigTransactionHistory WHERE ProductID BETWEEN 1001 and 2834 GROUP BY ProductID GO SELECT ProductID, COUNT( TransactionID), SUM(Quantity), SUM(ActualCost) FROM dbo.bigTransactionHistory WHERE ProductID BETWEEN 1001 and 2835 GROUP BY ProductID GO SELECT ProductID, COUNT( TransactionID), SUM(Quantity), SUM(ActualCost) FROM dbo.bigTransactionHistory WHERE ProductID BETWEEN 1001 and 2835 GROUP BY ProductID OPTION (MAXDOP 1) GO

dos scripts e notar que podem haver variações no intervalo do predicado (cláusula WHERE) para reproduzir o exemplo em diferentes máquinas. Ao executar as duas primeiras consultas da Listagem 3 e capturar os planos de execução, vemos que o primeiro tem um custo muito baixo (0,0206037), pois o filtro somente retorna os pedidos feitos para produtos com ID entre 1001 e 1010. Deste modo, o SQL Server está longe de considerar o paralelismo para esta consulta (veja a Figura 4). Já o segundo plano (veja a Figura 5) mostra que o custo da consulta é de exatamente 4,99998, isto é, foi gerado um plano no limite de custo para o SQL Server começar a considerar planos paralelos. Esse aumento significativo no custo se deve à quantidade de registros retornados pelo operador de index seek . Na primeira consulta o fluxo de dados entre o index seek e o stream aggregate é de 4235 registros, enquanto na segunda operação o volume entre os operadores é de 1.038.862 registros. Um aspecto interessante a ser observado é que o custo dos operadores se mantém proporcional entre os dois planos, pois independentemente da quantidade de registros, cada operador terá que processar mais ou menos registros, mantendo a proporção dentro do plano. Ao executar a próxima consulta com um conjunto de dados um pouco maior, agora incluindo o ProductId de valor 2835 e fazendo com que o index scan retorne 1.039.935 registros, o SQL Server passa a utilizar um plano de execução paralelo. Isso se deve ao fato de todos os possíveis planos de execução não paralelos gerados pelo SQL Server, até determinado momento do processo de otimização, terem ficado com um custo superior a 5 (configuração do custo limite para o paralelismo), o que fez com que o processo de otimização continuasse a procurar novos planos, só que a partir de agora considerando o uso de operadores paralelos. No final do processo de otimização, o SQL Server gerou um plano paralelo com custo de 3,7631 (vide Figura 6 , editada para facilitar a visualização), um valor menor do que o limite de 5, mesmo que mais

Figura 4. Otimizador de consulta e plano de baixo custo

Figura 5. Otimizador de consulta e plano no limite do custo para paralelismo

recursos de hardware sejam utilizados durante a sua execução (afinal, o paralelismo quer resolver um problema em menos tempo). Isto acontece porque ao considerar o paralelismo, o custo de processamento de alguns operadores foi dividido entre um número específico de threads, e mesmo adicionando novos operadores como o gather streams e repartition streams , o benefício do paralelismo é maior que o custo adicional de processamento. Para completar a série de instruções da Listagem 3 , a última consulta repete o filtro que gerou um plano paralelo, mas dessa vez com a hint MAXDOP 1, impedindo que seja gerado um plano paralelo. Isso faz com que

o SQL Server apresente um plano de custo com o valor 5,00652, o menor custo entre os planos encontrados pelo SQL Server, e o mesmo que ele criou para a consulta 03, antes de considerar o paralelismo. Como você leitor já deve ter se indagado, para o exemplo anterior, o que aconteceria se o limite de custo para o paralelismo fosse maior? Para nos ajudar a responder essa pergunta, a Listagem 4 muda a configuração da instância, redefinindo para 6 o valor do custo para paralelismo, o que faz com que a consulta, antes paralela, não considere mais o paralelismo, chegando ao mesmo plano e custo de quando utilizamos a hint MAXDOP 1. Edição 136 SQL Magazine •

39


Figura 6. Otimizador de consulta e o plano paralelo

Listagem 4. Alterando o custo para paralelismo.

-- Exibe as configurações avançadas EXEC sys.sp_configure ‘show advanced options’, 1 RECONFIGURE EXEC sys.sp_configure ‘cost threshold for parallelism’, 6 RECONFIGURE go SELECT ProductID, COUNT( TransactionID), SUM(Quantity), SUM(ActualCost) FROM dbo.bigTransactionHistory WHERE ProductID BETWEEN 1001 and 2835 GROUP BY ProductID GO EXEC sys.sp_configure ‘cost threshold for parallelism’, 5 RECONFIGURE go

Fica evidente que é muito mais simples conseguir ver o otimizador de consultas produzindo planos paralelos quando o volume de dados aumenta, afinal o esforço computacional para executar a consulta será maior. Especificamente no nosso caso, os planos passaram a ser paralelos para as consultas que manipulara m um pouco mais de 1 milhão de registros, mas não é correto pensar em um número de registros a partir do qual o SQL Server sempre irá trabalhar com paralelismo, pois essa quantidade varia de acordo com o esquema das tabelas envolvidas, complexidade da consulta, entre outros fatores. Em nenhum momento foi citado qual é a unidade do custo dos planos de execução, e o motivo para isso é que não existe tal un idade. Muitos anos atrás o time de produto do SQL Server compilou um modelo de custo e nessa época o c usto se dava em segundos, porém, com o advento de recursos computacionais mais rápidos (CPU, memória, I/O, etc.), uma execução que no passado levava um minuto, hoje pode levar segundos. Esse comportamento,


no entanto, não impede que o modelo seja válido, pois uma vez que o SQL Server tem que escolher entre N planos de execução, todos baseados nas mesmas métricas, aquele de menor custo estimado será o escolhido pelo otimizador de consultas, que efetivamente acredita ser a melhor opção dentro das a lternativas que ele gerou.

Grau de paralelismo e custo dos operadores Até este momento citamos somente o grau máximo de paralelismo através da hint MAXDOP, porém existe uma configuração (de mesmo nome) no SQL Server que define o valor utilizado pela instância, o max degree of parallelism. Esse item de configuração possui, por padrão, o valor 0 (zero), o que significa para o SQL Server que ele pode utilizar todos os processadores disponíveis. Deste ponto em diante é importante especificar que a máquina onde foram realizados os testes deste artigo é um processador de quatro núcleos com hyperthreading, deixando visível para o sistema operacional e o SQL Server um total de oito processadores lógicos. Essa informação é relevante devido ao fato do SQL Server sempre considerar a metade do número de processadores disponíveis ao estimar o custo das operações paralelas durante o processo de otimização. Para exemplificar como é feita a redução no custo total do plano de execução, utilizaremos as quatro consultas da Listagem 5 , onde todas possuem o mesmo predicado utilizado anteriormente, que com a configuração padrão do SQL Server, sabemos que deve gerar um plano paralelo (custo superior a 5). Analisando o operador de Index Seek da primeira consulta, podemos ver um custo de CPU de valor 1,14368 e custo de I/O em 3,23868. Quando executamos a segunda consulta com a hint MAXDOP 2, permitimos que o index seek possa ser para lelizado com até duas threads, fazendo com que o operador passasse a ter o custo de CPU definido em 0,57184 e o de I/O em 3,23868.

Listagem 5. Comparando o custo do index seek com diferentes graus de parale-

lismo. USE AdventureWorks2012 GO -- Exibe as configurações avançadas EXEC sys.sp_configure‘show advanced options’, 1 RECONFIGURE -- Verifica a configuração do SQL Server EXEC sys.sp_configure‘max degree of parallelism’

consultas trabalham com a hipótese de que existem quatro processadores disponíveis. Este é um ponto importante para destac armos, pois, uma vez gerado um plano de execução paralelo, é a engine de execução que vai decidir efetivamente quantas threads serão util izadas, de acordo com a situação do serv idor, o limite de threads no SQL Server, o consumo de recursos, entre outros fatores. Isso pode fazer com que um plano em determi nada execução receba oito threads e apenas duas threads em um momento de estresse do servidor, gerando tempos de execução bem distintos.

-- Consulta 01 SELECT ProductID, COUNT( TransactionID), SUM(Quantity), SUM(ActualCost) FROM dbo.bigTransactionHistory WHERE ProductID BETWEEN 1001 and 2835 GROUP BY ProductID OPTION (MAXDOP 1) GO -- Consulta 02 SELECT ProductID, COUNT( TransactionID), SUM(Quantity), SUM(ActualCost) FROM dbo.bigTransactionHistory WHERE ProductID BETWEEN 1001 and 2835 GROUP BY ProductID OPTION (MAXDOP 2) GO -- Consulta 03 SELECT ProductID, COUNT( TransactionID), SUM(Quantity), SUM(ActualCost) FROM dbo.bigTransactionHistory WHERE ProductID BETWEEN 1001 and 2835 GROUP BY ProductID OPTION (MAXDOP 4) GO -- Consulta 04 SELECT ProductID, COUNT( TransactionID), SUM(Quantity), SUM(ActualCost) FROM dbo.bigTransactionHistory WHERE ProductID BETWEEN 1001 and 2835 GROUP BY ProductID OPTION (MAXDOP 8) GO

Em outras palavras, paralelizando a operação de index seek em duas threads, o custo de CPU caiu pela metade, enquanto o custo de I/O se manteve, o que é razoável, pois o SQL Server precisa ler o mesmo número de páginas e registros para processar o resultado da consulta. Os custos do operador de seek na consulta três seguem a mesma linha de raciocínio: o custo de I/O se mantém, enquanto o custo de CPU cai para 0,28592, aproximadamente 25% e 50% dos valores das consultas 01 e 02, respectivamente. Juntamente com a redução do custo deste operador, o custo total do plano de execução ta mbém acompanha a queda. A quarta consulta, quando executada, pode ser uma surpresa para muitos, pois todos os custos do plano são idênticos aos da terceira consulta, conforme detalhado na Figura 7 , que exibe o custo do operador em todas as quatro consultas. Isso se deve ao fato do SQL Server considerar a metade do número de processadores disponíveis para a otimização, que no caso deste artigo se restringe a oito. Portanto, para fins de otimização, todas as

Figura 7. Custos do index seek para as quatro consultas da Listagem 5

Para simular o comportamento do SQL Server em uma máquina com mais processadores, é possível utilizar o c omando não documentado DBCC OPTIMIZER_WHATIF , que efetivamente diz para o otimizador de consultas “E se o número de processadores fosse 16”. Neste caso, o custo de CPU do index seek vai cair mais uma vez pela metade, chegando ao valor de 0,14296, pois existindo 16 CPUs o SQL Server pode utilizar oito como o grau de paralelismo na otimização (vide Listagem 6). O mesmo resultado também será obtido usando a hint MAXDOP 16, pelos motivos apresentados anteriormente, ou simplesmente omitindo o MAXDOP 8 ao executar a consulta. É importante ressaltar que este tipo de instrução nunca deve ser utilizado em produção, a menos que orientado pela própria Microsof t. Existe uma relação de precedência para a conf iguração do max degree of parallelism na otimização das consultas. Sempre que especificada a hint MAXDOP, esta tem precedência sobre a conf iguração homônima da instância. Caso o administrador do banco de dados deseje que a hint não tenha precedência, é necessário utilizar o Resource Governor do SQL Server, configurando um Workload Group com o grau máximo de paralelismo, associando-o com as conexões da sua aplicação. Edição 136 SQL Magazine •

41


Listagem 6. Simulando uma máquina com mais processadores.

DBCC OPTIMIZER_WHATIF(CPUs, 16) WITH NO_INFOMSGS; select ProductID, count( TransactionID), sum(Quantity), sum(ActualCost) from dbo.bigTransactionHistory WHERE ProductID between 1001 and 2835 GROUP BY ProductID OPTION (MAXDOP 8); DBCC OPTIMIZER_WHATIF(ResetAll) WITH NO_INFOMSGS;

Configurando o paralelismo em sua instância O SQL Server é reconhecidamente um produto que, mesmo com a instalação padrão, processa bem as requisições destinadas aos bancos de dados, o que não significa que as configurações out of the box são as melhores. Para a configuração do grau máximo de paralelismo, a Microsoft tem um artigo (ver seção Links) na base de conhecimento intitulado “Recomendações e diretrizes para a opção de configuração de ‘grau máximo de paralelismo’ do SQL Server” e contém a seguinte recomendação: • Um nó NUMA e menos de oito processadores lógicos: manter

MAXDOP abaixo do número de processadores lógicos (ex.: 4); • Um nó NUMA e mais de oito processadores lógicos: manter o

MAXDOP em 8; • Diversos nós NUMA e menos de oito processadores lógicos por

nó: manter MAXDOP abaixo do número de processadores lógicos por nó NUMA (ex.: 4); • Diversos nós NUMA e mais de 8 processadores lógicos por nó:

manter o MAXDOP em 8. Estas sugestões visam manter um bom balanço entre consumos de recursos e desempenho das consultas, evitando a utilização excessiva de threads durante a execução de poucas consultas paralelas, e impedindo que uma mesma consulta possa ter threads executando em diferentes nós NUMA, o que certamente faria com que existissem muitos acessos à memória estrangeira, aumentando o tempo de execução da consulta. As atuais arquiteturas de computadores trabalham com o que chamamos de Non Uniform Memory Access (NUMA), que consiste em dividir os processadores em grupos menores (nós) com sua memória local e canais de I/O, para evitar colisões e gargalos no barramento, o que aconteceria se muitos processadores dividissem um único barramento. Como existe a possibilidade de processadores acessarem conteúdo de uma memória “estrangeira”, isto é, um dado que está em outro nó, o tempo de acesso à memória não é uniforme, daí o nome da arquitetura. O SQL Server passou a ser considerado NUMA aware a partir da versão 2005. Portanto, o SQLOS tem conhecimento da distribuição dos processadores nos nós NUMA e tenta privilegiar o esca lonamento de threads com contextos semelha ntes no mesmo nó, com o intuito de minimizar a quantidade de acesso estrangeiro. Mesmo sendo uma boa recomendação, é evidente que o admi-


nistrador do SQL Server pode alterar essa configuração quando o argumento for sólido. Por exemplo: em ambientes com bancos de dados puramente analíticos (OLAP) em servidores de 128 processadores, é justificável um MAXDOP maior, pois em geral se deseja que as requisições possam se beneficiar de um grau maior de paralelismo e processar milhões ou bilhões de registros em um tempo menor. Essa análise do trade-off e realização de testes de desempenho são vitais para o bom comportamento da instância, pois evita outros problemas como, por exemplo, o consumo exagerado de threads por requisições que podem ficar bloqueadas, e acabam levando a um rápido consumo das threads disponíveis no SQL Server, causando a exaustão do pool de threads da engine. Outra sugestão comum de configuração que encontramos em artigos ou fóruns é configurar o MAXDOP para 1 em ambientes transacionais (OLTP), utilizando como justificativa que ambientes transacionais possuem características de requisições pontuais e com pequeno volume de dados, então o paralelismo é desnecessário. O argumento é consistente, mas particularmente o autor desse artigo discorda da abordagem, pois a engine do SQL Server somente considera planos paralelos quando os custos dos planos gerados pelas primeiras fases de otimização ultrapassam a configuração do limite para paralelismo. Portanto, se somente houvesse operações leves acontecendo, a própria engine entenderia como desnecessária a utilização de paralelismo. Soma-se a isso o fato de que relatórios operacionais, muito comuns em ambientes transacionais, levariam um tempo maior para serem executados por não poderem se beneficiar do paralelismo, a menos que fosse explicitamente definida a hint para cada consulta. Discussão essa que nos leva à próxima configuração: cost threshold for parallelism. Essa é uma configuração pouco comentada e que merece atenção. Deste modo, faça o seguinte exercício: capture diversas consultas em seu ambiente e comece a analisar o custo do plano com a hint MAXDOP 1. O que você verá é que comumente, mesmo consultas que são executadas em poucos segundos, utilizam paralelismo. Em outras palavras, em geral o valor 5 é considerado uma configuração baixa para os recursos de hardware atuais e o SQL Server acaba por criar planos paralelos em situações onde estes não seriam necessários. Assim, em vez de ser radical e definir o MAXDOP 1 para seu ambiente OLTP, uma abordagem mais interessante seria trabalhar com um maior limite do custo para paralelismo, o que permitir ia à engine optar por utilizar o paralelismo para as consultas mais caras, mantendo planos seriais para as consultas com características transacionais. Não existe um valor recomendado para esta configuração (ou em documento oficial da Microsoft), mas muitos consultores de renome internacional citam valores entre 20 e 50. A mesma estratégia pode ser adotada quando se está fazendo um trabalho de melhoria de desempenho em um ambiente com alto consumo de processamento e muitos planos paralelos. Aumentar o custo de paralelismo vai evidenciar os planos mais caros e usualmente causa uma diminuição do consumo de CPU, facilitando o trabalho de análise.

Contudo, é importante ressaltar que o cost threshold for parallelism não pode ser sobreposto por h ints, devendo a alteração do valor ser testada e aplicada com cautela. Aliado a isso, o fato de uma mudança na configuração poder alterar muitos planos de uma só vez, em geral faz com que os administradores de ambientes bem estáveis não façam essa mudança, mesmo podendo ser benéfica, pois pode trazer um efeito colateral ruim, por exemplo, ao fazer com que uma consulta vital para o sistema deixe de utilizar o plano paralelo e gaste um segundo a mais em cada execução, segundo este que pode impactar significativamente toda a aplicação. Detalhamos nesse artigo um pouco do funcionamento da engine do SQL Server no que tange o paralelismo de consultas. Esse embasamento é necessário para que você, DBA ou desenvolvedor, dê o primeiro passo para entender a racionalidade do otimizador de consultas e as alternativas de execução paralelas que o SQL Server oferece. Historicamente, a adoção (ou não) de consultas e operações pa ralelas no SQL Server é fonte de discórdia entre administ radores de banco de dados, em geral oriunda de eventuais experiências ruins com o paralelismo, causadas por planos malcomportados, configurações inapropriadas ou até eventos não tão comuns . Mesmo sendo natural basear nossos conceitos em experiênc ias vividas, o problema passa a ser quando o medo ou receio é dif undido como “não adote o paralelismo, ele é ruim”, sem uma explicação lógica e coerente. Lembre-se que o objetivo do paralelismo é nobre: solucionar um problema em menos tempo. A partir deste momento você está apto a revisar as configurações das suas instâncias e se aprofundar na análise de planos paralelos, buscando sempre a melhor configuração e desempenho para o seu ambiente SQL Server.

Autor Luciano Moreira [Luti] [email protected] http://luticm.blogspot.com

É Arquiteto de Dados na Sr. Nimbus. Especialista e MVP em SQL Server, vem buscando nos últimos anos desbravar o vasto universo dos dados, explorando diferentes soluções arquiteturais e engines de bancos de dados. Divide seu tempo como profissional entre consultorias, treinamentos e participação na comunidade técnica, ajudando empresas a projetar soluções, capacitar equipes e resolver problemas que envolvem bancos de dados. Saiba mais sobre ele: http://www.srnimbus.com.br/luciano-moreira/. Links: [1] The Free Lunch Is Over - A Fundamental Turn Toward Concurrency in Software http://www.gotw.ca/publications/concurrency-ddj.htm

[2] Microsoft Sample Databases http://msftdbprodsamples.codeplex.com/

[3] Make Big AdventureWorks http://sqlblog.com/blogs/adam_machanic/archive/2011/10/17/thinking-big-adventure.aspx

[4] Recomendações e diretrizes para a opção de configuração de “grau máximo de paralelismo” do SQL Server https://support.microsoft.com/pt-br/kb/2806535

[5] A new platform layer in SQL Server 2005 to exploit new hardware capabilities and their trends http://blogs.msdn.com/b/slavao/archive/2005/07/20/441058.aspx

[6] Understanding Non-uniform Memory Access https://technet.microsoft.com/en-us/library/ms178144(v=sql.105).aspx



43

Trabalhando com o MemSQL Conheça esta opção de banco de dados em memória de alta performance

P

esquisas realizadas recentemente estimam que grande parte dos dados que serão gerados nos próximos anos virá de áreas que produzem fluxos de informações constantes em nível de centenas a milhões de vezes por segundo, conhecidos como "Fast Data": dados de logs, leitura de sensores, interações, jogos etc. Em muitas dessas áreas as informações devem ser processadas e analisadas em um curto período de tempo. Áreas como a da Internet das coisas, sistemas de telecomunicações, sistemas financeiros, de antifraude, de análise de riscos, de interação de jogos, sistemas de recomendações etc., necessitam conseguir lidar com informações em tempo real para poder tomar decisões de forma eficaz e até mesmo poder predizer certos comportamentos. Ferramentas analíticas e de data warehouse existentes são projetadas para executar consultas predefinidas em grandes bancos de dados, e que geralmente levam de minutos até horas para serem processadas. No entanto, a análise de dados em tempo real deve ser tratada de forma diferente. É nesse cenário que entram os bancos de dados em memória. Conhecido como “ in-memory database” (IMDB), esse tipo de banco de dados tem crescido muito ultimamente em resposta às novas necessidades de negócios, sistemas e operações.

Banco de dados in-memory Um sistema de banco de dados in-memory é um gerenciador de banco de dados que, em contraste com os bancos de dados tradicionais, não guarda os dados em disco, mas na memória principal de uma máquina ou conjunto de máquinas. Esse tipo de banco de dados é mais rápido que os tradicionais, que são otimizados para escrita em disco, visto que seus algoritmos de otimização são mais simples e executam menos instruções de CPU. Acessando os dados diretamente na memória eliminase o tempo de busca gasto pelo disco “procurando” os dados, fornecendo assim um desempenho maior.


Fique por dentro Este artigo faz uma breve introdução sobre bancos de dados em memória, mostrando conceitos, vantagens e desvantagens da sua utilização. O artigo também apresenta o MemSQL, um banco de dados em memória de alta performance, demonstrando suas principais características e sua forma de utilização. Esse tema é útil em casos onde se deseja a rmazenar e processar informações, e o tempo de resposta é um fator crítico. Situações em que se planeja construir aplicações que necessitam detectar mudanças de comportamento, realizar análises históricas e sugerir mudanças, ajudando na tomada de decisões em tempo real.

Quando todos os dados são guardados em memória, não ex iste mais a necessidade de se guardar cache dos dados, podendo também ser comprimidos e descomprimidos mais facilmente, gerando menos espaço do que a mesma quantidade de dados armazenada em disco. Apesar do uso de discos tradicionais ainda ser o padrão das empresas desse setor, o desenvolvimento de novas tecnologias de memória e o constante declínio de preços de memórias do tipo RAM vem facilitando bastante a adoção desse tipo de sistema de dados. Muitos se perguntam nesse momento: por que não criar então um disco virtual de memória RAM e mover o banco de dados tradicional para esse volume, ou ainda, desabilitar configurações de bancos tradicionais para trabalharem nesses moldes? O pro blema é que os bancos de dados tradicionais, pelo fato de serem projetados para lidar com discos rígidos, desempenham diversas tarefas que, executadas diretamente em memória RAM, resultariam em um desempenho pior e em mais uso de processador do que sistemas preparados e otimizados para lidar somente com esse tipo de memória. Diversas empresas, dentre elas podemos citar IBM, Microsoft e Oracle, já trabalham em soluções de bancos de dados in-memory e buscam disputar o mercado com outras empresas, mais novas e menos conhecidas, que também oferecem interessantes soluções para lidar com Fast Data.

Características

• GemFire XD: banco relacional, di stribuído, no padrão NewSQL

Atualmente existem diversas soluções nesse padrão de banco de dados. Muitas utilizam formas hí bridas, outras somente memórias RAM. Algumas trabalham com NoSQL, outras com dados relacionais. Quando falamos em banco de dados em memória surge sempre a questão da durabilidade dos dados, uma das características do modelo ACID. Visto que o tipo de memória utilizado é volátil, como manter esses dados de forma permanente sem que os mesmos sejam perdidos durante uma falha de sistema? Atualmente muitos destes bancos de dados possuem soluções para esse tipo de problema como, por exemplo, estados de replicações dos dados através de cluster e commits temporários dos dados em disco, fazendo com que funcionem totalmente em conformidade com as características do modelo ACID, que representa as quatro propriedades (atomicidade, consistência, isolamento e durabilidade) necessárias para que uma transação ocorra: • Atomicidade: uma transação deve ser uma unidade atômica de trabalho, ou todas as suas modificações de dados são executadas ou nenhuma delas é executada; • Consistência: quando concluída, uma transação deve deixar todos os dados em um estado consistente. Em um banco de dados relacional, todas as regras devem ser aplicadas às modificações da transação para manter toda a integridade dos dados. Todas as estruturas de dados internas tais como índices em árvore B ou listas duplamente encadeadas, devem estar corretas ao término da transação; • Isolamento: modificações feitas por transações simultâneas devem ser isoladas das modificações feitas por qualquer outra transação simultânea. Uma transação reconhece os dados no estado em que estavam antes de outra transação simultânea tê-los modificado ou reconhece os dados depois que a segunda transação tiver sido concluída, mas não reconhece um estado intermediário; • Durabilidade: depois que uma transação tiver sido concluída, seus efeitos ficam permanentemente no sistema. As modificações persistem até mesmo no caso de uma queda do sistema.

e que possui integração com sistema HDFS. NewSQL é uma nova categoria de gerenciadores de bancos de dados que fornecem os mesmos benefícios escaláveis e performáticos dos bancos NoSQL, mas para sistemas transacionais (OLTP), mantendo todas as garantias do modelo ACID dos bancos tradicionais.

Como já foi dito anteriormente, existem diversas soluções de bancos de dados em memória no mercado, muitas delas possuem versões gratuitas. Aqui citamos algumas da nova geração: • OrigoDB: desenvolvido em especial para aplicações .NET, é um

banco relacional compatível com o modelo ACID. Possui também uma interface REST, possibilitando ser utilizado por outras tecnologias além do .NET; • VoltDB: banco relacional, escalável, compatível com o modelo

ACID, e que implementa o padrão H-Store de gerenciamento de dados. H-Store é uma implementação em memória de uma nova categoria de sistemas de gerenciamento de dados em paralelo, otimizado para aplicações transacionais (OLTP), conhecido como NewSQL, funcionando de forma distribuída. Possui fácil integração com o Hadoop; • MemSQL: banco relacional, compatível com o modelo ACID,

escalável e que possui fácil integração com o Apache Spark e Hadoop;

Dentre os apresentados, iremos nos aprofundar um pouco mais em relação ao MemSQL, apresentando suas principais características, requisitos e instalação, além de utilizá-lo como exemplo para o nosso estudo de caso.

O MemSQL O MemSQL é um banco de dados em memória de alta performance que combina a capacidade escalável dos sistemas distribuídos com as facilidades do SQL. Ele ficou conhecido por ser um banco em memória transacional e altamente escalável utilizado em empresas como Comcast, Zynga e ShutterStock. A última versão do banco, além de outras melhorias e funcionalidades, possui integração com frameworks de processamento de Big Data como o Apache Spark, HDFS, e o Amazon S3, sendo possível realizar análises de dados em tempo-real. Apache Spark é uma engine para processamento de dados em larga escala que se integra ao Hadoop. Através dele é possível criar aplicações em Java, Scala ou Python para processamento e manipulação de dados. Através do conector do MemSQL com o Spark, pode-se obter uma alta vazão (do inglês, throughput ) e executar visualizações em tempo real de dados armazenados no HDFS ou Amazon S3. Integrando ambas as tecnologias, usuários podem carregar dados entre o MemSQL e clusters Spark processa ndo resultados de análises via SQL. Além da versão Enterprise com suporte, o MemSQL possui uma versão "community" que permite sua utilização de forma gratuita sem limitações. É um banco relativamente fácil de configurar, manter e escalar, seja em uma máquina convencional ou na nuvem. A nova versão inclui também recursos de inteligência geospacial em tempo real, possibilitando criar aplicativos com capacidade de geolocalização e realizar análises instantâneas das informações disponibilizadas.

Funcionamento do MemSQL O MemSQL possui uma arquitetura de duas camadas. Essa arquitetura pode ser distribuída por diversos nós que possuem as mesmas funcionalidades e o que os diferencia é o papel que cada nó é configurado para executar. Existe o conceito de nós agregadores (do inglês, aggregators nodes), isto é, nós responsáveis por intermediar a comunicação entre aplicação e banco, fornecendo um único ponto de acesso e ag regando o resultado das consultas. Os aggregators se comunicam com os nós folha (do inglês, leaf nodes), responsáveis por armazenar e processar os dados em si. A comun icação entre agregadores e nós folha se dá através de queries SQL, assim como mostra a Figura 1. Edição 136 SQL Magazine •

45

Trabalhando com o MemSQL

Estado

Descrição

Unknown

Nesse estado, um nó folha não faz parte do cluster. Esse é o estado antes de executar o comando ADD LEAF para inserir o nó no cluster. Um nó nesse estado não aparecerá quando o comando SHOW LEAVES for executado. O comando REMOVE LEAF coloca um nó nesse estado.

Online

Estado default de um nó no cluster. Nesse estado o nó está ativo no sistema e está fornecendo dados ou em estado de leitura.

Offline Detached

Um nó se encontra nesse estado quando o Master Aggregator não consegue visualizá-lo. O Master Aggregator checa por cada um dos nós através do comando SELECT. Mesmo depois de um nó ir para o estado offline, o Master Agregador continua monitorando-o. Um nó offline entra nesse estado quando passa a responder novamente aos pings do agregador. Um nó nesse estado pode voltar a ficar online se executado o comando ATTACH LEAF. Esse comando tentará validar os dados contidos pelo nó.

Tabela 1. Estados do nó folha (leaf node)

Cada folha representa um dos estados apresentados na Tabela 1. A Figura 2 mostra como funcionam as transições de estados entre os nós folha do MemSQL. Um nó no MemSQL é constituído de múltiplas partições. Cada partição representa uma database no servidor. Por exemplo, se temos uma database chamada system e executarmos o comando SHOW DATABASES neste nó, no resultado veremos algo como system_7 (significa que essa é a partição 7 da database system). As partições possuem dois estados conforme é apresentado na Tabela 2.

Figura 1. Arquitetura do MemSQL

Dentro de um cluster MemSQL, um aggregator é um nó que funciona como uma espécie de roteador de query. Esse tipo de nó armazena somente metadados e age como um gateway dentro do sistema distribuído. Um cluster pode ter um ou mais agregadores, dependendo do volume de dados do mesmo. Um agregador é responsável por: • obter resultados dos nós folha; • agregar os resultados; • retornar os resultados para o cliente.

Existe também o conceito de Master Aggregator , que seria um agregador especializado em monitorar o cluster e detectar falh as. Os agregadores possuem os seguintes comandos: - SHOW AGGREGATORS - AGGREGATOR SET AS MASTER As configurações de aggregators são feitas através de um arquivo de configuração "memsql.cnf". Para adicionar um novo aggregator ao cluster do MemSQL, basta adicionar a seguinte linha de comando no arquivo de configuração: master-aggregator = .

Já os nós folha são pontos de armazenamento e processamento de informações. Eles armazenam porções dos dados dentro do cluster. Por questões de desempenho, o MemSQL automaticamente distribui os dados dos nós folha em partições. Cada folha consiste de um servidor com diversas partições sendo que cada partição seria um "banco de dados" dentro do servidor.


Figura 2. Transição de estados entre os nós folha do MemSQL Estado

Descrição

O nl ine

E st ad o da s pa rt içõ es M as ter.

Replicating

Estado das partições Slave que estão sendo replicadas a partir da partição Master.

Tabela 2. Estados da partição

Se executarmos o comando SHOW DATABASES EXTENDED, será possível ver as partições e em quais estados elas estão. Ao executarmos o comando em uma instalação nova do MemSQL, vemos as informações constantes na Figura 3.

Requisitos e instalação O MemSQL roda nos sistemas operacionais Linux de 64-bits, ou seja, o MemSQL não irá funcionar nos sistemas de 32-bits. Recomenda-se utilizar pelo menos 8GB de RAM para rodá-lo, no entanto ele funcionará em máquinas com menos RAM, apesar de não ser recomendado, pois a capacidade de armazenamento do MemSQL é limitada pela quantidade de memória RAM que a máquina possui. Dessa forma, quanto mais RAM disponível, maior a quantidade de memória disponível para armazenamento. Outro ponto importante é em relação à quantidade de processadores que a máquina possui. O MemSQL requer um mínimo de 4 núcleos (do inglês, cores) para funcionar, conforme Figura 4. A instalação é bem simples, basta descompactar o tar.gz e rodar o install.sh. Toda a configuração do cluster é feita através da interface do MemSQL conhecida como MemSQL Ops:

Figura 3. Execução do comando SHOW DATABASES EXTENDED

Figura 4. Requisito mínimo de núcleos para instalar o MemSQL

tar -xzf memsql-ops-4.0.34.tar.gz cd memsql-ops-4.0.34 sudo ./install.sh

Uma vez que o MemSQL está instalado na primeira máquina, a interface web do MemSQL Ops pode ser acessada pelo endereço: http://:9000. Basta acessar a URL para configurar outras opções e outros nós do cluster. Podemos ver na Figura 5 o MemSQL Ops rodando com um cluster de dois nós configurados. Temos um Master Aggregator rodando na porta 3306 e um Leaf na porta 3307. O MemSQL utiliza o mesmo protocolo de comunicação do MySQL, sendo assim, quem já está acostumado com o MySQL poderá utilizar a mesma forma de comunicação, podendo conectar em seu banco com o mesmo conector sem necessidade de instalação de clients adicionais. Devido ao fato do MemSQL utilizar SQL para análise, torna-se muito simples a integração para quem já está acostumado com essa linguagem.

Estudo de caso Para demonstrar algumas das funcionalidades desse banco, vamos criar um mo-

Figura 5. Tela inicial do MemSQL Ops

delo de dados de um sistema de pedidos básico, conforme a Figura 6. O sistema possui uma tabela de itens (sales_item) e as vendas dos itens são processadas na tabela de pedidos (sales_order_item e sales_order). Temos também a localização do pedido (locality) e os centros de distribuição (distribution_centre) que são os locais físicos onde as mercadorias ficam armazenadas. O intuito dos centros de distribuição aqui é mostrar algumas das funcionalidades geoespaciais presentes no MemSQL. O MemSQL pode ser utilizado com qualquer client do MySQL. Nesse caso,

utilizaremos um client default que vem com a instalação do MySQL para linha de comando. Para efetuar a conexão utilizamos: mysql.exe -u root -h

Uma vez conectado, podemos iniciar a criação das tabelas do nosso sistema de pedidos. Criaremos primeiramente o database associada ao nosso sistema: mysql> create database system;


47


Depois de criar o database, vamos agora criar as tabelas do sistema. A DDL da tabela sales-order pode ser vista na Listagem 1. Notamos que, para referenciar a chave estrangeira locality _id referente à tabela locality, utilizamos o comando "FOREIGN SHARD KEY". Os dados no MemSQL são fragmentados pelo nó através de partições. Cada partição representa uma database no formato database_N (onde N representa o número da partição) e que carrega uma parte dos dados de cada tabela daquela partição. Por padrão, o MemSQL irá criar uma partição por núcleo de CPU dentro dos nós para ter um máximo de paralelismo possível. Esse valor é configurável pelo cluster através do parâmetro de configuração default-partitions-per-leaf , ou também como um parâmetro opcional do coma ndo CREATE DATABASE. Cada tabela dentro de uma partição possui exatamente um "shard key", ou "shard index", o que seria um índice de tabela normal contendo qualquer número de colunas. O MemSQL utiliza essa informação para determinar a qual partição determinada coluna está relacionada. O banco também suporta uma variante de shard key, chamada “foreign shard key”, que referencia explicitamente uma shard key de outra tabela (quando criamos uma prima ry key sem especificar a shard key, a própria primary key é a shard key). Até a versão 3.2 do MemSQL, esse tipo de referência ( Foreign Shard Key) era necessário para realizar join s distribuídos. A partir da versão 4 essa referência direta não é mais necessária. No MemSQL, até a versão corrente, não existe o conceito de foreign key como uma chave de integridade referencial. Assim, o comando utilizado serve apenas para criar um index sobre o campo locality_id.

Figura 6. Modelo de dados sistema de pedidos


Listagem 1. Criação da tabela sales_order

CREATE TABLE sales_order (id int unsigned not null auto_increment, protocol varchar(255) not null, email varchar(255), phone_number varchar(30), sales_manager_name varchar(255) not null, locality_id int unsigned not null, created_at datetime, updated_at datetime, created_by varchar(100), updated_by varchar(100), status smallint, finished_atdatetime, finished_by varchar(100), customer_id int unsigned not null, FOREIGN SHARD KEY (locality_id) REFERENCES locality (id), PRIMARY KEY (id,locality_id) );

Dados geoespaciais Outro fato interessante de mencionarmos é a criação da tabela distribution_centre (Listagem 2), visto que nela vamos utilizar informações geoespaciais. A coluna "location" possui o tipo GEOGRAPHYPOINT. O MemSQL, na versão 4, possui suporte para dif erentes funções geoespaciais. Ele suporta três tipos de dados: pontos (points), caminhos ( paths) e polígonos ( polygons), conforme demonstrado na Figura 7. O tipo POINT, por exemplo, é simplesmente uma coordenada de latitude/longitude. O MemSQL implementa um subconjunto do WKT. O WKT ( Well-known text) é uma linguagem para representação de objetos geográficos, referências espaciais e transformações entre objetos e referências espaciais. A tabela locality ( Listagem 3) também possui um campo para coordenadas geométricas. O intuito aqui é poder calcular, dada uma determinada localidade de entrega de um pedido, qual o centro de distri buição mais próximo que pode atender àquela localidade. Depois de criadas todas as tabelas (a Listagem 4 mostra a DDL das demais ta belas), podemos inserir os dados armazenados, por exemplo, em um arquivo csv , utilizando o comando da Listagem 5. Na tabela distribution_centre iremos inserir três centros de distribuição, assim como mostra a Listagem 6. No nosso caso só precisamos saber quais as coordenadas de cada centro de distribuição, por isso utilizamos o tipo point (GEOGRAPHYPOINT), que consiste de um par latitude/longitude. Note que não existe vírgula separando as coordenadas. Quando uma tabela é criada no MemSQL, para acelerar a compilação das queries, o MemSQL pré-compila

Listagem 2. Criação da tabela centre_distribution

CREATE TABLE distribution_centre (id int unsigned not null primary key, name varchar(255) not null, location GEOGRAPHYPOINT not null, created_at datetime, created_by varchar(100), shard key (location) );

o código utilizado para acessar e atualizar os índices da tabela. Isso ocorre somente uma vez para cada tabela criada. Feito isso, o código compilado é colocado em cache para uso f uturo.

Listagem 3. Criação da tabela locality

CREATE TABLE locality (id int unsigned primary key, name varchar(255), code varchar(100), location GEOGRAPHYPOINT not null, created_at datetime, created_byvarchar(100), index (location)); Listagem 4. Criação das demais tabela

CREATE TABLE sales_item (id int unsigned not null primary key, item_price_value double not null, quantity int default 1, item_namevarchar(255), created_at datetime, updated_at datetime, created_by varchar(100), updated_by varchar(100), status smallint); CREATE TABLE sales_order_item (id_order int unsigned not null, id_item int unsigned not null, quantity int unsigned not null default 1, SHARD KEY (id_item,id_order), PRIMARY KEY (id_item,id_order)); CREATE TABLE locality (id int unsigned primary key, name varchar(255), code varchar(100), location GEOGRAPHYPOINT not null, created_at datetime, created_by varchar(100), index (location)); CREATE TABLE customer (id int unsigned primary key, name varchar(100), birth datetime, created_at datetime, updated_at datetime, created_by varchar(100), updated_by varchar(100), status smallint); Listagem 5. Inserção de dados armazenados num arquivo csv

LOAD DATA INFILE arquivo_com_dados.csv’ INTO TABLE nome_da_tabela FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’

Figura 7. Tipos de dados geoespaciais Listagem 6. Inserção de centros de distribuição

insert into distribution_centre (id,name,location,created_at,created_by) values (1, “Centro P1”, “POINT(-12.1722904 -51.5108855)”, now(), “admin”); insert into distribution_centre (id,name,location,created_at,created_by) values (2, “Centro P2”, “POINT(-19.9230507 -43.9368626)”, now(), “admin”); insert into distribution_centre (id,name,location,created_at,created_by) values (3, “Centro P3”, “POINT(-8.7249743 -39.1199079)”, now(), “admin”);

Distribuição dos Inserts e Selects Quando executamos um insert no MemSQL, o agregador calcula o hash dos valores contidos na(s) coluna(s) que fazem parte da shard key e realiza uma operação para gerar um número específico de partição. O banco direciona então o INSERT para a partição apropriada (que bate com o número gerado pela operação que calcula o hash) no nó da máquina. A única garantia que se tem sobre o local físico de inserção dos dados é a de que duas colunas com o mesmo valor para o shard key estarão na mesma partição (dado o processo de cálculo para se determinar qual partição o dado irá ocupar). Na Figura 8 temos um exemplo de como essa estrutura entre agregadores e nós funciona para a inserção dos dados. Nesse exemplo, um dos agregadores (agregador 1) calcula o hash dos valores da coluna e insere os dados no nó 3.

Figura 8. Mecanismo de insert

No caso da execução de um select, o mesmo é executado em paralelo em cada uma das partições referentes à database. Conforme pode ser visto na Figura 9 , o agregador é responsável por executar a query em todas as partições que compõem as tabelas envolvidas na execução. As partições podem ser visualizadas com o comando SHOW PARTITIONS ( Figura 10). Edição 136 SQL Magazine •

49


Figura 9. Mecanismo de select

Quando uma query é executada pela primeira vez, pode-se notar claramente uma demora na execução. Isso acontece porque o MemSQL transforma as queries SQL em código C++ e o compila (SGBDs tradicionais interpretam as queries SQL). A compilação da query é feita somente uma vez (o que gera um pouco de demora na primeira execução) para cada padrão de query. Uma vez que a query está compilada, qualquer outra executada que for similar o suficiente, reutilizará o mesmo plano de execução. Por exemplo, se tivermos uma query no formato “select * from customer where name=’Fulano’ and status = 1” , o MemSQL não irá recompilar a query para consultas parecidas. Ele substituirá os parâmetros para poder fazer binds de outros valores sempre que o mesmo tipo de query for chamado. O MemSQL usa a compilação de queries para otimizar a execução da mesma. Sem o overhead de interpretar a query dinamicamente, as queries podem ser executadas de forma bem mais rápida, competindo de igual para igual com outras soluções otimizadas como, por exemplo, soluções NoSQL.

Calculando distâncias

Figura 10. Status das partições e database “system”

Figura 11. Centros de distribuição e localidade do pedido a ser entregue


Criamos a tabela distribution_centre como uma forma de demonstrar como o cálculo de dados geoespaciais pode ser utilizado no MemSQL. Supondo um determinado pedido a ser entregue em Curitiba PR, precisamos saber qual centro de distribuição, dentre os cadastrados, fica mais próximo da localização de entrega do pedido (Figura 11). Para esse feito, podemos executar a função G E O G R A P H Y _ D I S T A N C E responsável por calcular a distância entre duas coordenadas. Executando essa função entre a localidade dos centros de distribuição e do local de entrega, obtemos que o Centro de distribuição P2 é o mais próximo, conforme Figura 12. Outra função interessante também é a GEOGRAPHY_INTERSECTS , que serve para descobrir, dado uma ou mais coordenadas, quais outras localidades a interceptam. No nosso exemplo, utilizamos informações bem básicas, somente para demons-

tração da função de cálculo da distância, calculando-a diretamente entre o centro de distribuição e a localidade desejada. Em um sistema real, seria necessário, por exemplo, ter coordenadas intermediárias para se determinar uma rota real entre as cidades origem e destino.

Apache Spark e MemSQL O Apache Spark é uma solução muito interessante em cenários de manipulação Figura 12. Resultado da execução de cálculo de distância e processamento de dados em larga escala. Possui diversas vantagens em relação a outros frameworks de O MemSQL é um banco de dados em memória que apresenta big data, permitindo lidar com uma grande variedade de dados diversas funcionalidades e facilidades para lidar com dados em através de diversas fontes distintas. Em muitos casos, esses dados, grande escala de forma distribuída. Pelo fato de se tratar de um depois de analisados, processados e reformatados, devem ser banco de dados relacional, fica transparente de ser utilizado por armazenados em algum lugar. usuários que já possuem experiência com SQL, em especial com A versão mais nova do MemSQL fornece um conector para o o MySQL, visto que utiliza o mesmo conector. Spark, facilitando o carregamento de dados entre ambos, transO MemSQL também traz facilidades para se conectar com o formando e enriquecendo os dados em tempo real. Apache Spark para casos onde se tem necessidade de uma maniUma das melhores características do Spark são as facilidades pulação mais apurada dos dados, ou da realização de processada interface de programação. Além de permitir que usuários do mentos complexos das informações antes de armazená-las. A sua MemSQL desenvolvam processos computacionais interativos, utilização é mais adequada para situações onde temos um fluxo ele possibilita o acesso a várias bibliotecas que executam em sua de dados constante e que devem ser processados e analisados engine. O conector do MemSQL com o Spark é otimizado, permiem tempo real. tindo minimizar a transferência de dados e tirar vantagens dos mecanismos de otimização e indexação do MemSQL. Autor O conector é uma ferramenta open source disponível na página Rodrigo Salvo do MemSQL no GitHub. O conector cria um mapeamento entre as [email protected] partições do MemSQL e as partições RDD do Spark (ver BOX 1). Formado em Ciências da Computação pela Universidade Ele também tira vantagem da arquitetura distribuída de ambos Federal de Uberlândia, possui mais de 10 anos de experiência os sistemas, permitindo realizar o carregamento de dados em em desenvolvimento de software. Especialista na área de Telecom e paralelo. Junto ao conector vem uma biblioteca (MemSQLRDD). serviços para internet. Esta permite ao usuário criar u m RDD do resultado de uma query SQL executada no MemSQL. A classe MemSQLRDD possui um Links: método chamado “saveToMemSQL”, que facilita imputar dados no MemSQL depois de processados pelo Spark. Big Data Os bancos de dados em memória vêm adquirindo uma imporhttp://www.technologytransfer.eu/article/98/2012/1/What_Is_Big_Data_and_Why_ tância cada vez maior no mercado, dado um aumento constante Do_We_Need_It_.html de cenários onde a velocidade no processamento e análise de H-Store informações se torna essencial para os negócios. http://hstore.cs.brown.edu/

BOX 1. RDD

O Apache Spark possui o conceito de conjunto de dados distribuídos (do inglês, Resilient Distributed Dataset - RDD), que é uma coleção de elementos tolerantes a falhas que podem ser manipulados em paralelo. Existem duas formas de se criar uma coleção RDD: paralelizando uma coleção existente, ou referenciando um conjunto de dados armazenados externamente, seja através de um sistema de arquivos compartilhados, através de HDFS, HBase etc. RDDs suportam dois tipos de operações: transformações (transformations), que criam um novo conjunto de dados a partir de um já existente, e ações (actions), que retornam um valor depois de computar informações sobre o conjunto de dados.

MemSQL http://www.memsql.com

Apache Spark http://spark.apache.org/



51

Trabalhando com o MySQL Cluster Saiba como configurar o MySQL Cluster e conheça o storage engine NDB

O

MySQL Database, apesar de open source , foi um projeto criado e desenvolvido por uma empresa privada sueca chamada MySQL AB. A sua primeira versão estável foi lançada em janeiro de 2001. O modelo de negócio era baseado em código aberto, sem cobrança pelo licenciamento de uso, mas com a opção de adquirir serviços de suporte e consultoria de seus criadores. A arquitetura do produto era baseada em plug-ins e isto possibilitou a criação de um ecossistema ao seu redor, com outras empresas e contribuidores externos criando facilmente novas funcionalidades. Um caso destas empresas do ecossistema MySQL foi a Alzato, uma spin-off da Ericsson. Seu time de engenheiros criou alguns componentes adicionais ao MySQL que o transformavam em um verdadeiro banco de dados distribuído, com todas as características que atendiam aos mais exigentes requisitos dos sistemas de infraestrutura de telefonia, conhecidos como Carrier-Grade. O projeto, chamado de NDB ( Network Database), era tão interessante que a MySQL AB adquiriu a Alzato em setembro de 2003. Com isso, o time de engenheiros da Alzato passou a fazer parte do time da MySQL AB, que melhorou o NDB e o rebatizou como MySQL Cluster. Em 2008, a MySQL AB foi adquirida pela Sun Microsystems, que em 2010 foi adquirida pela Oracle. Hoje, o MySQL Cluster é um produto que compõe o portfólio MySQL da Oracle e continua em pleno desenvolvimento sob licenciamento GPL, ou seja, pode ser usado, modificado e redistribuído gratuitamente mantendo-se o código-fonte aberto. A Oracle também provê opcionalmente suporte técnico e ferramentas comerciais para gerenciamento do MySQL Cluster. O MySQL Cluster é uma solução que atende muito bem alguns requisitos como disponibilidade de 99,999%, workloads transacionais (OLTP), replicação geográfica ativo-ativo, tempos de respostas consistentes da ordem de milésimos de segundo, escalabilidade praticamente linear (inclusive de escritas) e capacidade de atender


Fique por dentro O MySQL Cluster é um banco de dados transacional baseado no MySQL Server que combina a flexibilidade de um banco de dados relacional ACID, interfaces SQL e NoSQL, alta disponibilidade e alto desempenho em operações de leitura e escrita. Neste artigo apresentamos alguns conceitos fundamentais do MySQL Cluster para que você possa entender seu funcionamento, compará-lo com outras soluções (inclusive o próprio MySQL Server) e, quem sabe, utilizá-lo com sucesso em seu próximo projeto. Em tempos onde vemos uma avalanche de novos bancos de dados, tais como o NoSQL, entender os conceitos que são base para o f uncionamento e limitações de um banco de dados distribuído vai ajudá-lo a fazer melhores escolhas. O MySQL Cluster é um banco de dados distribuído que po de ser a solução ideal para seu novo projeto, com alta disponibilidade e escalabilidade horizontal, exigindo hardwares relativamente modestos. E o melhor, é totalmente open source e com suporte (opcional) da Oracle.

dezenas de milhares de transações por segundo. Contudo, o MySQL Cluster não vai ser uma boa escolha para executar con sultas analíticas (OLAP) e, por se tratar de um sistema distribuído, vai adicionar maior complexidade na implantação e administração.

Tipos de aplicações O MySQL Cluster nasceu para atender as necessidades de aplicações de telecomunicações, mas continua em constante evolução para lidar com cargas de trabalho mais genéricas. Provavelmente, o MySQL Cluster será uma boa escolha se a carga de trabalho da sua aplicação for semelhante aos casos da Tabela 1. No geral, os requisitos comuns destas aplicações são: • alta disponibilidade (99,999%), com failover automático (abaixo

de 1s) e operações de manutenção online; • distribuição geográfica ativo-ativo; • alto volume de acessos e conexões (milhares), com escalabilidade

tanto em leituras quanto em escritas (milhões de TPS); • baixa latência com pouca variação nos tempos de resposta

(milissegundos);

Tipos de Aplicação

Casos de Referência

Infraestrutura de TI, Cloud e Telecom

Ericsson, Alcatel Lucent, NEC, Nokia, Telenor, Italtel, B2, Cell C, Emblacom, ip.access, M1, Register.it, CenturyLink

Mensagens, mobile publishing, news portals

SpeechDesign, Go2, Kurier, XClima, UC Berkeley, American Education Corporation

Games sociais

Jogo do Chaves para Facebook (El Chavo) da Playful Play.

Games MMO e de rede

World of Warcraft e Diablo 3 da Blizzard e FIFA da EA Sports

Transações financeiras e micro trans ações OLTP

Paggo, Vivo, Telefonica, Totto Loto, Cashpoint

Internet das Coisas, Monitoramento de dispositivos ou aplicações, aquisição massiva de dados

Callis, Vivid Cortex

e-Commerce, workflow, sessões Web, gestão de documentos

Zillow, Shopatron, DocQ

Big Data: antifraude e recomendação

Paypal, Big Fish Games, Mapion

Sistemas de tempo real, missão crítica e militares

Marinha EUA (US Navy), ViaSuisse

Metadados como componente de outros projetos open source

Hadoop Hops, Hadoop KTH, OpenMQ, FreeRADIUS, FreeSWITCH, Kamailio, OpenIMS, Sailfin, WSO2

Tabela 1. Exemplos de uso do MySQL Cluster por tipo de aplicação

• carga OLTP (transacional ACID), onde as principais queries e

transações são baseadas na chave primária e/ou chaves únicas (realiza poucos full table scans), possuem curta duração (até 10s) e alto grau de paralelismo; • dados estruturados seguindo modelo relacional ou chave-valor,

com poucas foreign keys e poucos BLOBs ou TEXTs (linhas com 16KB); • retenção de dados com período limitado, com estratégias de

expurgo ou arquivamento periódico; • baixo custo total de implantação e operação (hardware commo-

dity, licenciamento GPL). O MySQL Cluster é uma solução que atende todos estes requisitos simultaneamente. Porém, se você precisa lidar com um ou apenas alguns destes requisitos isoladamente, talvez outras opções de menor complexidade sejam a solução ideal como, por exemplo, o tradicional MySQL Database com InnoDB. É importante destacar também que cada vez mais as aplicações são arquiteturadas na forma de serviços ou microserviços, e é perfeitamente possível combinar mais de um banco de dados usando técnicas que alguns autores nomeiam de persistência poliglota. Com o MySQL, esta ideia já é bastante natural, visto que desde sua concepção, há a noção de “tipos de tabela”, que são mais ou menos adequadas dependendo da situação. Portanto, o MySQL Cluster pode atender muito bem a uma parte específica da sua aplicação, modelada em algumas tabelas, enquanto que outras partes são atendidas por outros “tipos de tabelas” ou mesmo outras tecnologias.

NewSQL O MySQL Cluster é um sistema gerenciador de banco de dados relacional que oferece ao mesmo tempo desempenho, escalabilidade de leituras e escritas, e flexibilidade nos métodos de acesso aos dados dos sistemas NoSQL para processamento de transações online (OLTP), mantendo as garantias ACID de um sistema de banco de dados tradicional. Isto é o que alguns autores chamam de NewSQL.

Há várias interfaces que as aplicações poderão usar para escrever e ler dados do MySQL Cluster. Por exemplo, o desenvolvedor pode fazer chamadas diretas ao banco de dados usando C++, Java, Node.js, HTTP ou Memcached protocol. Desta forma, a camada de SQL não será necessária, diminuindo sobrecarga e aumentando performance, além de oferecer f lexibilidade e agilidade no desenvolvimento. Cada uma das APIs NoSQL pode ser utilizada simultaneamente com SQL, sobre o mesmo conjunto de dados. Estes serão mapeados automaticamente ou via configuração em tabelas e a visão final será a mesma, com consistência e integridade garantidas pelo MySQL Cluster, inclusive para chaves estrangeiras (FKs).

Conceitos fundamentais e arquitetura O MySQL Cluster é um banco de dados relacional ACID, mas não é (apenas) o MySQL Server com replicação. Para usar o Cluster, você terá que baixar outro pacote que contém mais componentes que o popular MySQL Server. Este pacote está disponível no site do MySQL (ver a seção de Links). O MySQL Cluster, apesar de operar no modo multi-master, também não é equivalente ao Oracle RAC. O Oracle RAC depende de um storage compartilhado para persistir os dados, em uma arquitetura conhecida como shared-everything. Já no MySQL Cluster, cada nó de dados tem sua própria unidade de armazenamento, ou seja, não compartilham nenhuma memória (volátil ou não) e por isso é uma arquitetura conhecida como shared-nothing. Esta arquitetura (ver Figura 1) do MySQL Cluster permite implementações distribuídas e sem ponto único de falha ( SPoF ou Single Point of Failure), o que casa muito bem com hardwares commodity ou ambientes de Cloud Computing, porém adiciona algumas complexidades e restrições. Há três tipos diferentes de nós no MySQL Cluster, cada um fornecendo serviços especializados, sendo todos escaláveis horizontalmente (adicionando mais nós). Um destes tipos de nós é o tradicional e popular MySQL Server (mysqld), mas há outros componentes igualmente importantes. Edição 136 SQL Magazine •

53

Trabalhando com o MySQL Cluster

Figura 1. 1. Arquitetura do MySQL Cluster

Os nós de dados (Data Nodes) são os principais nós do cluster, onde os dados estão armazenados de fato e são escritos e lidos através da NDB API. Os nós de aplicativo ( Application Application Nodes) são as interfaces entre os programas e os nós de dados, oferecendo opções de acesso via SQL ou APIs NoSQL. No contexto desta arquitetura, o MySQL Server (mysqld) (mysqld) é um tipo especializado especia lizado de Application Node que permite acesso via SQL aos dados. Os nós de gerenciamento ( Management Management Nodes) fornecem informações de configuração e status ao cluster e permitem executar rotinas de manutenção como, por exemplo, backup online ou adicionar mais nós. Caso o leitor já esteja familiarizado com o MySQL Database, saberá que é possível selecionar os motores de armazenamento (storage engines) por tabela. Para os SQL Nodes , o MySQL Cluster é um storage engine adicional. Portanto, é possível combinar tabelas NDBCluster com tabelas de outros tipos no mesmo esquema de dados como, por exemplo, InnoDB ou MyISAM. Também é possível utilizar recursos nativos da camada SQL do MySQL Database em conjunto com tabelas NDBCluster, tais como Stored Programs, Triggers, Eventos, UDFs, Binlog, Replicação Assíncrona, etc.

Alta disponibilidade Como cada tipo de nó possui uma responsabilidade bem definida e com dependências minimizadas, é possível distribuí-los em múltiplas máquinas (hosts físicos ou virtuais) virt uais).. Estas máquinas podem estar fisicamente f isicamente no mesmo datacenter ou em vários (via geo-replicação),), o que permite as devidas redundâncias para u m geo-replicação nível de disponibilidade de 99, 99,999%. 999%. Para obter este nível de alta disponibilidade, um cluster mín imo deve ter seis nós lógicos (dois de cada tipo). Porém, Porém, alguns algu ns destes nós podem ser co-alocados co- alocados fisicamente em um mesmo host/máhost/máquina, desde que não haja ponto único de falha. fal ha. A implementação mais comum para alta disponibilidade conta com quatro hosts: dois nós de dados e mais dois co-alocando um nó SQL e um nó de gerenciamento em cada um. Podemos adicionar nós redundantes em todas as camadas. Os dados serão replicados automaticamente pelo cluster entre os nós de dados e mesmo que alguns alg uns nós de dados, de aplicativo ou de


gerenciamento venham a falhar, falha r, as aplicaaplicações clientes continuarão sua execução. No caso da falha fal ha de um dos nós de dados, o cluster continuará funcionando normalmente e quando o nó for restaurado, os dados serão re-sincronizados automaticamente, sem nenhum trabalho administrativo. Esta é uma funcionalidade fu ncionalidade conhecida como self-healing (auto recuperação). recuperação). Entretanto, o fato de o MySQL Cluster ser desenhado para alta disponibilidade não é uma garantia de entrega real destes resultados. Na verdade, um cluster shared-nothing multi-master implementado e mantido de maneira deficitária pode facilmente faci lmente oferecer níveis mais baixos de disponibilidade do que uma simples solução de replicação de dados master-slave. É necessário verificar diversos aspectos na implementação, tais como: configuração do hardware, throughput e latência da rede, redundância da alimentação de energia, etc.

Escalabilidade É também essa arquitetura do MySQL Cluster que possibilita escalabilidade linear de leituras e escritas, distribuindo os dados e as cargas ca rgas entre os nós no modelo multi-master. No MySQL Cluster, há particionamento part icionamento automático de dados dados (auto-sharding ) entre os data nodes. Por exemplo, em dois cenários típicos, quando uma tabela com dados é carregada car regada no Cluster: 1. se o Cluster possuir apenas dois Data Nodes , a tabel tabelaa será persistida em um nó e uma cópia (réplica) automaticamente vai para outro nó. Se um nó falhar, o Cluster automaticamente usa os dados do nó disponível; dispon ível; 2. se o Cluster possuir quatro Data Nodes , , as linhas da tabela (além de replicadas) são distribuídas distribuída s automaticamente e uni uniformemente formemente entre os nós (auto-sharding). Desta forma, metade dos dados da tabela estará em dois nós e outra metade nos outros dois nós. Não somente somente os dados são distribuídos dist ribuídos nas operações de escrita, escr ita, mas também são distribuídas as operações de leitura via balanceamento de carga. Adicionando mais nós, aumenta-se a capacidade e performance do Cluster, tanto para leituras quanto para escritas. Esta capacidade c apacidade de escalar é linear, ou seja, adicionando o dobro de nós em um Cluster é possível dobrar sua capacidade de armazenamento e desempenho. Portanto, o MySQL Cluster escala horizontalmente. Como veremos, a replicação dos dados ocorre de forma síncrona e imediata, portanto os programas poderão conectar-se a qualquer application node e sempre enxergarão o mesmo conjunto consistente de dados. Na prática, isso significa que é permitido per mitido colocar um balanceador de carga entre os aplicativos e os application nodes distribuindo a carga entre os nós. Outra propriedade interessante desta arquitetura arquitetu ra é a elasticidade. A adição de mais nós pode ser realizada reali zada online , , sem paralisações

no sistema. Sendo assim, se não há como prever a demanda antecipadamente, isto não é um u m problema para o MySQL Cluster.

Consistência, integridade e performance Toda a replicação, distribuição, sincronização e consistência dos dados é feita pelo MySQL Cluster de forma transparente para o usuário. Para manter a consistência dos dados entre todos os nós, a replicação ocorre de forma síncrona. O mesmo conjunto consistente de dados sempre estará acessível, independente de qual nó seja consultado. Isso também se aplica a operações de escritas, o que significa que o cluster é multimaster, permitindo um balanceador de carga via software ou hardware entre as aplicações e os applica application tion nodes. Lembrando que cada data node tem sua unidade independente de armazenamento de dados, portanto temos um cluster multi-master sem storage compartilhado. Neste ponto, o leitor pode estar se perguntando: mas como manter os dados replicados de maneira síncrona, em máquina s completamente independentes e sem comprometer a performance? A arquitetura do MySQL Cluster é distribuída, sem necessidade de ter um storage compartilhado, mas também é in-memory. Ao inserir um dado em um nó do cluster, ele será replicado para a memória RAM de pelo menos mais um nó. O cluster não aguarda o dado ser escrito em disco para responder um commit e se beneficia das atuais redes mais rápidas e quantidades mais abundantes de RAM. Esta estratégia é conhecida como commit em nível de rede com consistência eventual em disco. O processo de gravar informação em RAM remota é mais rápido que escrever em disco local. Somando-se a isso, a capacidade de distribuir a carga de leituras e escritas entre nós, a performance do MySQL Cluster alcança níveis na ordem de milhões de transações por segundo. Por padrão, os dados indexados ficarão residentes residente s na memória RAM, porém há suporte para mantê -los apenas em disco. Esta flexibilidadee permite ao usuário fazer escolhas entre perforflexibilidad mance ou uso de maiores quantidades de memória RAM. Como descrito anteriormente, o MySQL Cluster sobreviverá a falhas parciais em alguns de seus nós. Porém, é importante implantar o cluster sem SPoFs ( Single Points of Failure), com redundâncias até na camada física como rede, alimentação, etc. Se houver queda total e simultânea de todos data nodes de uma só vez, o MySQL Cluster obedecerá ao modelo failing fast fas t , onde vai sacrificar a disponibilidade para manter a consistência dos dados. Isto não deve ocorrer se os devidos cuidados na implantação sem SPo SPoFF forem tomados.

Instalação para ambiente de desenvolvimento Há algumas opções para instalação do MySQL Cluster: binários compilados, compilação do cógido-fonte ou através do utilitário MySQL Cluster Manager (MCM). A opção mais mai s simples para montar um ambiente de desenvolvimento e começar a testá-lo é utilizando o MCM. Esta é a opção que utilizaremos

para nossos exemplos neste artigo. Para as outras opções consulte o manual de referência (ver a seção de Links). Testaremos conectividade via SQL e as capacidades de alta disponibilidade e escalabilidade horizontal. Não é nosso objetivo testar a performance, perfor mance, outras APIs de acesso, além de SQL, nem operações de manutenção, tal como backup online. O MCM é um utilitário opcional para facilitar o gerenciament gerenciamentoo do MySQL Cluster. Ele não é essencial para pa ra o funcionamento funciona mento do MySQL Cluster, então não confunda com o Man Manageme ageme nt Nod e. Há duas edições do MySQL Cluster: Community Edition e Cluster Carrier- Grade Edition. A primeira está sob licenciamento GPL v2, que permite o uso gratuito em produção e sem limitações. A segunda possui uma licença l icença comercial. Não Não há diferenças nas funcionalidades do banco de dados em si. A opção comercial existe para permitir que o MySQL Cluster seja embarcado em outros produtos de hardware ou software e redistribuído sem a necessidade de abrir o código fonte da aplicação ou demais componentes. O MySQL Cluster Manager é um utilitário tam bém com licença comerc ial, mas é opcional opciona l e pode ser utili zado em ambiente de desenvolvimento para testes por 30 dias. No MCM, há uma opção para criar um cluster mínimo para testes composto por cinco nós lógicos como mostra a Figura 2 , sendo um Ma Manag nagem ement ent Nod Nodee (ndb_mgmd), dois Data Nodes (ndbmtd) e dois SQL Nodes (mysqld).

Figura 2. 2. Cluster criado com a opção bootstrap do MCM

O MCM pode ser baixado diretamente do site do MySQL (ver seção Links). Após o Sign In com conta Oracle (crie uma, caso necessário), procure por “ MySQL Cluster Carrier Grade Edition”, selecione sua plataforma como mostra a Figura 3 e siga para a próxima tela. Nos testes a seguir, a plataforma é Linux x86-64. No passo seguinte, selecione o item “ MySQL Cluster Carri Carrier er Grade Edition” e o sub-item “ MySQL Cluster Manager”. Ao seguir para a próxima tela, você verá os termos de uso do MCM. Leia e prossiga para a tela de download onde será possível baixar arquivos .zip. Para os testes, utilizaremos o “ MySQL Cluster Manager 1.3.6+Cluster TAR for Generic Gener ic Linux x86 (64bit)”. Este pacote inclui os arquivos de instalação do utilitário MCM e mais os arquivos do MySQL Cluster. Cluster. Se você baixar baixa r o pacote com apenas o MCM, terá que baixar o MySQL Cluster separadamente. Edição 136 SQL Magazine •

55


Se houver algum erro na inicialização, verifique o arquivo de mcmd.log. Um Um erro comum é não ter memória suficiente na mámá quina de testes, sendo que 2 GB de RAM é o requisito mínimo para usar a opção bootstrap, mas recomendamos 3 GB para estes testes. No site do MySQL há um fórum na Developer Zone específico sobre MySQL Cluster que pode ser de grande ajuda para resolver problemas.

Testes básicos

Figura 3. 3. Download do MySQL Cluster Manager

Instalação Para os passos a seguir, utilize um usuário diferente de root e certifique-se que possui pelo menos 3 GB de RAM. A instalação é trivial, basta descompactar os arquivos baixados conforme a Listagem 1. Extraia o conteúdo do .zip .zip e, em seguida, extraia o .tar já para um diretóri diretórioo onde ficarão os executá executáveis veis do MySQ MySQL L Cluster e MCM, normalmente um subdiretório do home do usuário. Não é necessária nenhuma configuração adicional. Opcionalmente, você você pode querer alterar alguns parâmetros de configu ração do MCM editando o arquivo em mcm*/etc/mcmd.ini.

Inicializando um cluster de testes Para criar o cluster mínimo para testes da Figura 2 , com um usuário diferente de root, simplesmente execute o mcmd com a opção --bootstrap. A Listagem 2 demonstra o uso desta opção. opç ão. Listagem 1. Instalação do MySQL Cluster Manager

$ mkdir -p ~/mysql/packages $ unzip mcm*.zip -d ~/mysql/package ~/mysql/packagess $ tar -zxvf ~/mysql/packages/mcm*.gz ~/mysql/packages/mcm*.gz -C ~/mysql/package ~/mysql/packagess $ mv ~/mysql/packages/mcm*64bit ~/mysqlcluster $ ls mysqlcluster/ cluster mcm1.3.6 Listagem 2. Inicialização do MySQL Cluster com a opção bootstrap

$ ~/mysqlclus ~/mysqlcluster/mcm1.3.6/bin/m ter/mcm1.3.6/bin/mcmd cmd --bootstrap MySQL Cluster Manager 1.3.6 (64bit) started Connect to MySQL Cluster Manager by running “/home/vagrant/mys “/home/vagrant/mysqlcluster/ qlcluster/ mcm1.3.6/bin/mcm” mcm1.3.6/bin/mc m” -a vmclusterdev:1862 Configuring default cluster ‘mycluster’... Setting default_storage_engine to ndbcluster... Starting default cluster ‘mycluster’ version ‘5.6.24-ndb-7.4.6-cluster-commercial‘5.6.24-ndb-7.4.6-cluster-commercialadvanced’... Cluster ‘mycluster’ ‘mycluster’ started successfully ndb_mgmd vmclusterdev:1186 ndbmtd vmclusterdev ndbmtd vmclusterdev mysqld vmclusterdev:3306 mysqld vmclusterdev:3307 ndbapi * Connect to the database by running “/home/vagrant/mysqlcluster/cluste “/home/vagrant/mysqlcluster/cluster/bin/ r/bin/ mysql” -h vmclusterdev -P 3306 -u root


Agora com um Cluster em funcionamento, seguiremos com alguns testes básicos de conectividade e uso. Como temos dois SQL Nodes respondendo nas portas 3306 e 3307 33 07,, podemos utilizar qualquer um deles para manipular ma nipular os dados do cluster via SQL. Abra outros dois terminais e verifique a conect ividade em ambas as portas. Crie tabelas e insira lin has alternando as instâncias para verificar que o cluster sempre dará a mesma visão dos dados, independente de qual SQL Node é utilizado. As Listagens 3 e 4 exemplificam este teste. Listagem 3. Teste de conectividade conectividade no terminal 2, porta 3306

$ ~/mysqlcluster/cluster/bin/mys ~/mysqlcluster/cluster/bin/mysql ql -h127.0.0.1 -P 3306 -u root mysql> SELECT @@port; +-------------+ | @@port | +-------------+ | 3306 | +-------------+ 1 row in set (0.00 sec) mysql> SHOW DATABASES; +---------------------------------+ | Database | +---------------------------------+ | information_ information_schema schema | | mysql | | ndbinfo | | performance_sc performance_schema hema | | test | +--------------------------------+ 5 rows in set (0.01 sec) mysql> CREATE DATABASE meubd1; Query OK, 1 row affected (0.01 sec)

O database meubd1 foi criado usando a instância da p orta 3306, mas sua tabela t1 foi criada e populada usando a instância da 3307.. Se fizermos um 3307 u m “SELECT * FROM meudb1.t1;” em ambas as instâncias, o resultado será o mesmo. O MySQL Cluster sempre dará uma visão consistente dos dados. Observe que a tabela foi criada cr iada usando ENGINE=NDBCLUSTER. ENGINE=NDBCLUSTER. Isto garante que será uma tabela que usa o Storage Engine NDB. É permitido usar outros Storage Engines , como o InnoDB, InnoDB, porém, as tabelas que forem criadas de tal forma residirão apenas nas instâncias que foram criadas e não serão visíveis nas outras instâncias do cluster, pois não estarão nos Data Nodes.

Teste de alta disponibilidade Para testar a capacidade de oferecer alta disponibilidade do MySQL Cluster, será criado um script simples que insere e consulta dados alternando entre SQL Nodes (Listagem 5) em um loop infinito. Com o script em execução, mataremos um dos Data Nodes e, em seguida, um dos SQL Nodes. O cluster deverá continuar funcionando e os dados acessíveis. Listagem 4. Teste de conectividade no terminal 4, porta 3307

$ ~/mysqlcluster/cluster/bin/mysql -h127.0.0.1 -P 3307 -u root mysql> SELECT @@port; +------------+ | @@port | +------------+ | 3307 | +------------+ 1 row in set (0.00 sec) mysql> SHOW DATABASES; +------------------------------+ | Database | +------------------------------+ | information_schema | | meubd1 | | mysql | | ndbinfo | | performance_schema | | test | +------------------------------+ 6 rows in set (0.00 sec) mysql> USE meubd1; Database changed mysql> CREATE TABLE t1 ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, origem VARCHAR(6), data TIMESTAMP ) ENGINE=NDBCLUSTER; Query OK, 0 rows affected (0.23 sec) mysql> INSERT INTO t1 VALUE (NULL,@@port,NULL); Query OK, 1 row affected (0.01 sec) Listagem 5. Script para inserir e consultar dados

#!/bin/bash declare -a ports=(“3306” “3307”) while true; do for i in“${ports[@]}”; do echo “*** Escrita na instancia $i ***” ~/mysqlcluster/cluster/bin/mysql -h127.0.0.1 -P $i -uroot -e”INSERT INTO meubd1.t1 VALUE (NULL,@@port,NULL);” done for i in“${ports[@]}”; do echo “*** Leitura na instancia $i ***” ~/mysqlcluster/cluster/bin/mysql -h127.0.0.1 -P $i -uroot -e”SELECT COUNT(*) FROM meubd1.t1 ORDER BY id;” done sleep 3 done

Em outro terminal, altere a permissão do arquivo com “chmod +x testeha.sh” e execute o script com “./testeha.sh”. Com o script em execução, abra outro terminal e, através do MCM, mate um dos processos dos Data Nodes (ndbmtd) com o comando “stop process” (Listagem 6). Verifique que o script continua sua execução normalmente.

Listagem 6. Teste de alta disponibilidade do Data Node

$ ~/mysqlcluster/mcm1.3.6/bin/mcm mcm> show status --process mycluster; +-----------+-----------------+------------------+------------+----------------+---------------------+ | NodeId | Process | Host | Status | Nodegroup | Package | +-----------+-----------------+------------------+------------+----------------+---------------------+ | 49 | ndb_mgmd | vmclusterdev | running | | mypackage | |1 | ndbmtd | vmclusterdev | running | 0 | mypackage | |2 | ndbmtd | vmclusterdev | running | 0 | mypackage | | 50 | mysqld | vmclusterdev | running | | mypackage | | 51 | mysqld | vmclusterdev | running | | mypackage | | 52 | ndbapi |* | added | | | +-----------+-----------------+------------------+------------+----------------+---------------------+ mcm> stop process 1 mycluster; +---------------------------------------+ | Command result | +---------------------------------------+ | Process stopped successfully | +---------------------------------------+

Verificaremos agora o que acontece quando matamos um SQL Node. Ainda no MCM, verifique qual o ID dos nós com o comando “get port”. Escolha uma das instâncias para matar e use novamente o comando “stop process” ( Listagem 7). Verifique o comportamento do script em execução. Listagem 7. Teste de alta disponibilidade do SQL Node

mcm> get port mycluster; +--------+-------+------------+------------+--+ | Name | Value | Process1 | NodeId1 | ... | +--------+-------+------------+------------+--+ | port | 3306 | mysqld | 50 | ... | | port | 3307 | mysqld | 51 | ... | +--------+-------+------------+------------+--+ mcm> stop process 50 mycluster; +--------------------------------------+ | Command result | +--------------------------------------+ | Process stopped successfully | +--------------------------------------+

O script continua sua execução, inserindo e lendo dados na instância que não foi parada ( Listagem 8). Numa situação mais próxima da realidade, existirá um mecanismo de load balancing entre aplicação e SQL Node que pode ou não ser inteligente e detectar falhas de conectividade. Contudo, como vimos, mesmo que não detecte, a aplicação deverá continuar acessa ndo sem problema os dados através do nó SQL ativo. Se desejar, restaure os nós que foram parados com o comando “start process XX mycluster”. Pare a execução do script de testes com Ctrl+C. Observe que para termos uma alta disponibilidade total, o Management Node também deve ter redundância. Na verdade, essa é uma exigência para evitar um ponto único de falha em ambientes produtivos. Edição 136 SQL Magazine •

57


Listagem 8. Teste de alta disponibilidade verificado

*** Escrita na instancia 3306 *** ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (111) *** Escrita na instancia 3307 *** *** Leitura na instancia 3306 *** ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (111) *** Leitura na instancia 3307 *** +-------------+ | COUNT(*) | +-------------+ | 126 | +-------------+

Teste de escalabilidade horizontal A possibilidade de adicionar mais nós online pode ser testada facilmente com o MCM. Ao adicionar mais Data Nodes , aumentamos ao mesmo tempo a performance e espaço de armazenamento. Ao adicionar mais SQL Nodes , eliminamos gargalos de processamento SQL e conexões simultâneas. A adição de mais dois Data Nodes é demonstrada na Listagem 9. Listagem 9. Teste de escalabilidade horizontal

mcm> add process --processhosts=ndbmtd:3@vmclusterdev,ndbmtd:4@vmclusterdev mycluster; +------------------------------------+ | Command result | +------------------------------------+ | Process added successfully | +------------------------------------+ 1 row in set (2 min 46.32 sec) mcm> show status --process mycluster; +----------+----------------+------------------+------------+----------------+----------------+ | NodeId | Process | Host | Status | Nodegroup | Package | +----------+----------------+------------------+------------+----------------+----------------+ | 49 | ndb_mgmd | vmclusterdev | running | | mypackage | |1 | ndbmtd | vmclusterdev | running | 0 | mypackage | |2 | ndbmtd | vmclusterdev | running | 0 | mypackage | |3 | ndbmtd | vmclusterdev | added | n/a | mypackage | |4 | ndbmtd | vmclusterdev | added | n/a | mypackage | | 50 | mysqld | vmclusterdev | running | | mypackage | | 51 | mysqld | vmclusterdev | running | | mypackage | | 52 | ndbapi |* | added | | | +----------+----------------+------------------+------------+----------------+----------------+ 8 rows in set (0.01 sec) mcm> start process --added mycluster;

Este processo é relativamente mais lento e envolve a reinicialização dos demais nós do cluster, porém o MCM o fará automaticamente e de maneira alternada ( rolling restart), sem que haja indisponibilidade para a aplicação. Acompanhando o script em execução, é possível notar que alguns nodes ficarão inacessíveis (alternadamente) por um curto período de tempo, mas a execução em si não é afetada. A adição de SQL Nodes pode ser testada pelo leitor de maneira análoga. Erros são raros usando o MCM, porém certifique-se de que há memória RAM disponível antes do comando “start process”. A remoção de nós pode ser realizada com o comando “remove process XX mycluster;”.


Como sugestão para continuar seus estudos, crie um cluster mais próximo do que seria usado em ambiente de produção com nós em máquinas separadas. Isto lhe dará uma melhor visão sobre a operação do cluster e permitirá realizar testes de performance mais realistas. Com o mcmd (sem parâmetro bootstrap) em execução em todos os hosts, use os comandos “create cluster” e “add process” no mcm client para montar seu cluster distribuído. O manual de referência do MySQL Cluster Manager está disponível online (veja seção de Links) e possui exemplos de utilização destes comandos.

Limitações e cuidados Como vimos, o MySQL Cluster é um verdadeiro banco de dados distribuído ACID com arquitetura shared-nothing. Esta arquitetura ao mesmo tempo que nos dá uma enorme flexibilidade, implica em algumas limitações, uma vez que os dados estarão distribuídos e fragmentados entre nós independentes e que podem estar fisicamente separados. Porém, com alguns cuidados especiais, pode-se minimiza r ou eliminar tais limitações, expandindo os cenários de uso do MySQL Cluster. O melhor caso para usar o MySQL Cluster é uma aplicação nova ou em construção e que assemelha-se às descritas no início do artigo ( Tabela 1). Portanto, inicie seu projeto com um ambiente de desenvolvimento já com o MySQL Cluster. Se alguma limitação impacta r o uso, ela será detectada e contornada antecipadamente e mais facilmente. Um dos principais cuidados a serem tomados neste caso é o dimensionamento correto de memória RAM, definindo quais tabelas poderão ser disk-based e por quanto tempo os dados deverão residir no cluster. Sempre é possível adicionar mais nós para aumenta r a capacidade do cluster, porém uma política de retenção de dados com período limitado e com estratégia s de expurgo ou arquivamento periódicos, é uma boa maneira de fazer uso inteligente de recursos. Se a aplicação já existe, muito provavelmente serão necessárias algumas adaptações e otimizações de performance, tanto na aplicação, quanto no banco de dados. Dependendo do seu modelo de dados e da carga de trabal ho, esta tarefa pode ser bastante simples ou muito complexa. A imensa maioria das consultas existentes vão continuar f uncionando pois, os dados são particionados automaticamente e de maneira transparente pelo Cluster, porém é quase certo que seja necessário algum ajuste para melhorar a performance. Por exemplo, reescrever algumas consultas e rotinas para evitar Full Table Scans usando FORCE/USE INDEX, ANALYZE, Adaptive Quer y Localization. Em casos mais extremos, talvez seja necessário revisar o modelo de dados, principalmente reduzindo o uso de Foreign Ke ys e tipos BLOB ou TEXT. Outro caso que pode exigir uma adaptação é quando a aplicação faz uso de índices Full Text , que não são suportados pelo MySQL Clu ster. Neste caso, uma saída seria ma nter alguns dados de consulta em InnoDB, lembrando que é possível misturar storage engines.


59


O pior caso, onde o MySQL Cluster não é recomendado, é para aplicações empacotadas e não homologadas para MySQL Cluster, onde não é possível fazer adaptações na aplicação ou no banco de dados. Como toda solução de alta disponibilidade, planejamento na implantação é essencial. Planeje a infraestrut ura. Será necessário ter um checklist para verificar diversos aspectos, tais como: especificação do hardware, redundâncias da alimentação de energia e interfaces de rede e switches, configuraçõ es do sistema operacional e rede, testes de throughput e latência da rede, configuração e testes da solução de load balancing, etc. Também é importante investir em processos e pessoas, ou seja, a equipe que vai operar o cluster deve ter conhecimento da solução e capacidade de acessar canais de suporte, além de fazer uso de ferramentas para automatizar processos comuns e evitar falhas humanas. Como o MySQL Cluster está em franca evolução, algumas destas limitações tendem a desaparecer em versões futuras. No momento da escrita deste artigo, a versão corrente é a 7.4. Certifique-se de utilizar a versão estável (GA) mais recente e verificar se alguma limitação deixou de existir. Finalmente, duas fontes valiosas de informação para entender melhor as limitações e workarounds são o Manual de Referência, que conta com uma seção específica sobre limitações e o MySQL Cluster Evaluation Guide (ver seção Links).

Autor Airton Lastori [email protected] - www.alastori.com.br

Airton Lastori é consultor MySQL da Oracle Brasil. Possui formação em Ciência da Computação pela Universidade Federal de Itajubá e especialização em Engenharia de Software baseada em SOA pelo IBTA. Há mais de 10 anos está envolvido com diversas tecnologias Open Source relacionadas principalmente ao universo Web. Links: Download do MySQL Cluster Manager http://edelivery.oracle.com

Download do MySQL Cluster http://dev.mysql.com/downloads/cluster

Manual de Referência do MySQL Cluster http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster.html

Manual de Referência do MySQL Cluster Manager http://dev.mysql.com/doc/mysql-cluster-manager/1.3/en/index.html

Fórum MySQL Cluster http://forums.mysql.com/list.php?25

MySQL Cluster Evaluation Guide http://www.mysql.com/why-mysql/white-papers/mysql-cluster-evaluation-guide



PostgreSQL distribuído com PgPool-II Saiba como aumentar a disponibilidade de seu SGBD PostgreSQL criando uma solução distribuída baseada no PgPool-II

U

m sistema de banco de dados distribuído pode ser entendido como um banco de dados virtual, composto por diversos bancos de dados “reais” interligados fisicamente, através de vários meios como redes de alta velocidade, redes sem fio ou linhas telefônicas. Normalmente, o usuário deste sistema não sabe onde os dados estão localizados fisicamente, caracterizando um sistema altamente transparente. Entretanto, os computadores em um sistema de banco dados distribuídos não compartilha m componentes físicos, como memória principal ou discos de armazenamento, podendo variar em tamanho e função, diferentemente de um sistema paralelo. A Figura 1 representa um sistema distribuído interligado por uma rede de comunicação. Neste sistema, o banco de dados é armazenado em diversos computadores, que são denominados como “clusters” (nós). Cada cluster possui seu próprio hardware e não compartilham nenhum componente físico. Diferente de um sistema paralelo, os clusters podem variar em tamanho e função. Uma das vantagens em utilizar um sistema distribuído em clusters é sua alta disponibilidade, uma vez que no desativamento de um cluster, seja por problemas técnicos ou qualquer outro motivo, um outro cluster pode tomar pa ra si as requisições vindas pelas aplicações. Um sistema de banco de dados distribuído pode ainda ser classificado de duas formas: sistemas Homogêneos, onde todos os clusters do sistem a rodam o mesmo SGBD, conhecendo um ao outro e facilitando o processamento das transações; e, Sistemas Heterogêneos, onde cada cluster roda um SGBD diferente, muitas vezes limitando e dificultando o processo de manipulação dos dados, além de aumentar sua complexidade.

Fique por dentro O objetivo deste artigo é apresentar a ferramenta PgPool-II, utilizada para a criação de sistemas de banco de dados distribuído no SGBD PostgreSQL através da instalação e configuração de um ambiente de redundância e de alta disponibilidade. Um sistema de banco de dados distribuído é capaz de oferecer diversas vantagens onde um grande volume de dados e processamento é utilizado, aumentando o desempenho das aplicações e oferecendo um ambiente descentralizado e de alta disponibilidad e. Em um cenário global onde o volume de dados vem sendo cada vez maior em tamanho e importância, torna-se essencial que o banco de dados consiga oferecer agilidade e segurança para o acesso dos dados.

Figura 1. Representação de um sistema distribuído

Formas de armazenamento de dados Existem diversas formas com que um banco de dados distribuído pode armazenar os dados, sendo as principais: • Replicação: onde o sistema mantém réplicas idênticas dos dados entre seus diversos clusters, assegurando que todos os servidores tenham o mesmo conteúdo durante todo o tempo. Esta replicação pode ocorrer de forma síncrona ou assíncrona. Edição 136 SQL Magazine •

61

PostgreSQL distribuído com PgPool-II

No caso da primeira, o conteúdo é replicado imediatamente após uma transação, enquanto que na segunda, o servidor que está recebendo a transação pode esperar por todo seu processamento para depois enviar a modificação aos demais servidores. De modo geral, a replicação dos dados também fornece maior desempenho nas operações de leitura, porém, as operações de atualização consomem maior recurso, pois há a necessidade de atualizar todos os clusters do sistema. Na replicação síncrona, todos os clusters possuem o mesmo nível de responsabilidade e o conteúdo das bases de dados é idêntico durante todo o tempo. Já na replicação assíncrona, determinados clusters têm a responsabilidade de repassar informações aos demais, podendo as bases de dados diferir em algumas circunstâncias, como em uma inserção ou atualização de dado; • Fragmentação: os dados são fragmentados e divididos entre os clusters instalados. Assim, um cluster será requisitado dependendo da informação a ser consultada. A fragmentação ainda pode ser dividida em horizontal, onde cada fragmento é criado a part ir da seleção de linhas ou vertical, onde cada fragmento é criado a partir do agrupamento de atributos. Existem ainda sistemas com as duas características, onde seus dados são fragmentados e estes fragmentos são replicados em servidores diferentes. Para demonstrar um ambiente de banco de dados distribuído neste artigo, foram utilizados dois pequenos servidores para representar o hardware dos clusters. Cada servidor está configurado com o sistema operacional Linux Fedora. Uma distribuição Linux faz-se necessária porque a ferramenta PgPool-II é suportada apenas por sistemas operacionais “UNIX-like” como Solaris, FreeBSD e o próprio Linux. Desse modo, fica inviável a configuração de um cluster com outro tipo de sistema. Já para a criação do ambiente distribuído, será utilizada a ferramenta PgPool-II.

Ferramenta PgPool-II O PgPool-II é um projeto criado com o objetivo de suprir a carência de funcionalidades que o SGBD PostgreSQL possui, principalmente na funcionalidade de balanceamento de carga. Ele é um software middleware que funciona em uma camada entre um servidor do banco de dados PostgreSQL e um cliente, proporcionando diversas funcionalidades, entre elas: • Pooling de conexão: armazena um número limitado de conexões em um repositório interno para serem utilizadas posteriormente sempre que uma aplicação requisitar. Sua principal função é otimizar o acesso ao banco de dados através da reutilização de conexões sempre que uma nova conexão com as mesmas propriedades for detectada. Quando o tamanho máximo de conexões for alcançado, as requisições são enfileiradas para aguardar a liberação de novas; • Replicação: mantém cópias idênticas de uma base de dados em diferentes servidores PostgreSQL, chamados clusters. Através destas cópias, é possível criar um sistema tolerante a falhas, ligando e desligando clusters a qualquer momento, uma vez que seus dados


encontram-se replicados e por isso não afetam o comportamento da aplicação. Utilizar a replicação também permite que sejam criados backups em tempo real. • Balanceamento de carga: gerencia todos os clientes que estão utilizando o servidor e distribui a carga de uso entre os clusters disponíveis. Desta forma, se vários usuários necessitarem consultar o mesmo banco de dados simultaneamente, ocorrerá uma distribuição de acessos pelos clusters disponíveis, garantindo maior desempenho nas consultas. O balanceamento de carga pode ocorrer de duas maneiras: a primeira delas é dividindo uma query em diversas partes (Query Paralela), a outra, particionar o número de queries entre os servidores disponíveis, cada um responsável por retornar o resultado ao usuário separadamente; • Query paralela: utilizada em grandes bases de dados, a query paralela é a possibilidade de dividir uma query em diversas partes, que são executadas em diversos servidores simultaneamente e que, após o processamento, são unidas em uma única resposta. Isso permite diminuir o custo e a carga no servidor. Porém, pode ser muito custoso dependendo da largu ra da banda da rede; • Failover: procedimento realizado quando ocorre um erro com algum cluster do sistema. O Pgpool elimina todos os processos filhos e sessões em execução, eliminando do sistema o cluster onde ocorreu o problema, e reinicializando automaticamente para continuar a receber novas conexões. Além do Failover, o Pgpool-II ainda pode ser configurado para realizar o Failback, onde ele remapeia todos os clusters do sistema quando um novo é adicionado. O middleware comunica-se com o servidor PostgreSQL, chamado de BackEnd, e a aplicação, chamada de FrontEnd. A aplicação considera o PgPool-II como sendo o servidor PostgreSQL. Já o servidor considera que o PgPool-II é um de seus clientes. Pelo fato do PgPool-II ser transparente tanto para o servidor quanto para o cliente, um banco de dados existente pode ser usado com o PgPool-II praticamente sem mudança no seu código fonte. Para garantir a integridade da conexão com os servidores, o PgPool-II utiliza um procedimento chamado de “Health Chec k”. Através dele é realizada uma tentativa de conexão com cada servidor para saber se ele continua disponível na rede. Caso retorne um erro, o PgPool-II tenta realizar um failover. Este procedimento se comporta como um usuário comum no servidor de banco de dados, por isso, precisa das próprias permissões para acessar o servidor. Estas permissões podem ser configuradas no arquivo postgresql.conf, além de um nome de usuário e uma senha para autenticação. Também podemos configurar o número de tentativas de conexão, assim como o tempo que cada tentativa pode levar antes de retornar erro, que pode variar dependendo de cada ambiente. Outra característica interessante presente no PgPool-II é a possibilidade de configurar seu próprio cache em memória, armazenando queries (SELECT’S) diretamente na memória para uma maior agilidade em sua reutilização. Ainda é possível configurar o modo de armazenamento, que além do cache em memória, pode

usar a memória compartilhada shared memory. A memória compartilhada é utilizada em ambientes onde estão presentes várias instâncias do PgPool-II, e garante a integridade do cache através de seu compartilhamento.

como no PostgreSQL (que ocorre através do arquivo pg_hba.conf), necessitando de outros meios externos para limitar os acessos. Além disso, há algumas limitações ao manipular valores como timestamp e serial no modo de replicação.

Modos de operação De acordo com a documentação oficial, o PgPool-II pode operar em cinco modos distintos: Raw Mode, Connection Pool Mode, Re plication Mode, Parallel Mode e Master/Slave Mode. No Raw Mode , ele é configurado no front-end apenas para receber a conexão de clientes que acessarem o servidor PostgreSQL, podendo desse modo limitar a quantidade de conexões simultâneas ao servidor, oferecendo também um ambiente de alta disponibilidade através do failover. No Connection Pool , ele continua gerenciando as conexões dos clientes ao servidor, porém, mantendo um “pool” de conexões através da criação de vários processos para poderem ser reutilizadas sempre que possível, diminuindo a carga no servidor de dados. No Replication , ele replica as operações em todos os servidores conectados a ele criando um sistema de redundância. Dessa forma, caso algum servidor venha a ficar indisponível, ele é removido pelo PgPool-II e o sistema não é interrompido. Esta característica também é conhecida como degeneração. No Parallel , o PgPool-II divide as queries e as tabelas em diferentes servidores conectados ao sistema, aliviando dessa forma a quantidade de dados armazenados nos servidores. Como o SGBD PostgreSQL não suporta este t ipo de arquitetura de queries paralelas, o Pgpool-II na verdade acaba utilizando o conceito de fragmentação horizontal, que é considerada um tipo de query paralela. Já o modo Master/Slave é utilizado em conjunto com outro sistema de replicação Master/Slave , como por exemplo o Slony. Nesse modo, as operações do banco de dados são enviadas a um servidor principal (Master) e ele é responsável por enviá-las aos demais servidores (Slaves) quando possível. O PgPool-II fornece uma interface de controle chamada de pcp, em que o administrador pode gerenciar o status do pgpool-II além de poder controlar processos remotamente. Todos os modos de operação requerem que o pcp esteja configurado para serem operados. A arquitetura do PgPool-II é formada por (ver Figura 2): • um SQL Parser, que tem a função de validar e analisar todas as

queries vindas das aplicações; • um SQL Rewriter, que é responsável por reescrever queries em

determinados casos, como na execução de queries paralelas, e; • um motor de execução, ou engine, que é responsável por enviar

Figura 2. Arquitetura do PgPool-II

Apesar de ter um modo de replicação funcional, ele ainda possui algumas limitações se comparado com outras ferramentas como Slony-I. Isto faz com que ele deva ser operado com bastante cautela, uma vez que suas bases de dados podem ser corrompidas ou desatualizadas por causa de alguma configuração feita de forma incorreta.

Watchdog É possível também coordenar um sistema de alta disponibilidade constituído de vários Pgpool-II’s através de um processo chamado watchdog. Ele pode monitorar os sistemas trocando informações através de dois métodos: “Modo Hearthbeat” ou “Modo Query”. No modo Hearthbeat, o watchdog monitora os demais processos do pgpool-II enviando sinais frequentes através da rede garantindo que a outra ponta esteja sempre operacional. Caso algum sinal enviado não retorne nada por algum tempo, o watchdog interpretará isto como sendo um ponto de falha e informará a ocorrência. No modo Query, o watchdog realiza o monitoramento enviando queries aos demais serviços Pgpool-II, verificando assim sua resposta. Apesar de funcional, este modo é pouco utilizado e já encontra-se depreciado. Além de monitorar os demais processos, o Watchdog também pode monitorar os servidores de aplicação. Ele pode ser configurado pelo arquivo de configurações pgpool.conf, informando qual será seu hostname, porta e chave de autenticação.

informações e manipular os demais clusters do sistema.

Visão geral de um ambiente distribuído

O Pgpool-II é uma ferramenta robusta. Entretanto, algumas limitações ocorrem em sua utilização como a impossibilidade de realizar o balanceamento de carga com queries paralelas no modo Master/Slave, impossibilidade de autenticação por host

Para exemplificar um ambiente distribuído neste artigo, será considerada uma rede com dois servidores que serão utilizados para hospedar o middleware Pgpool e os servidores de banco de dados (clusters). O PgPool-II não necessita obrigatoriamente de um servidor próprio, podendo ser hospedado junto a outro Edição 136 SQL Magazine •

63


cluster, mas é preferível um servidor dedicado afim de desacoplar o ambiente e manter um maior controle sobre o sistema. A Figura 3 mostra a disposição de um ambiente distribuído com PostgreSQL.

O arquivo pg_hba.conf, que é responsável pela conf iguração de autenticação dos clientes PostgreSQL, controla quais hosts são permitidos, métodos de autenticação, nomes de usuários e quais databases podem ser acessados. Também é necessário configurar o servidor para escutar endereços externos e sua porta de acesso editando o arquivo postgresql.conf. Essa configuração deve ser feita para todos os clusters do sistema.

Instalação e Configuração do PgPool-II O download do PgPool-II pode ser realizado através de sua página de downloads (ver seção Links). Além disso, é possível também consultar uma detalhada documentação com tudo que é preciso para dominar a ferramenta. Para realizar a instalação do Pgpool-II, primeiro é necessário compilar seu código fonte. Ele requer o gcc ( Compiler Collection) na versão 2.9 ou superior, GNU make e a biblioteca libpq. Porém, antes de realizar a instalação, é necessário executar seu script de configuração, que coleta informações do sistema e o local onde estão instaladas as bibliotecas do PostgreSQL. O comando make compila o código fonte e o comando make install instala todos os executáveis: Figura 3. Sistema com banco de dados distribuído com o PostgreSQL $./configure --with-pgsql=/var/lib/

O middleware é responsável por receber todas as conexões externas dos clientes e então acessar uma das bases de dados disponíveis para realizar consultas. Os clientes conectam ao middleware através da porta padrão do PgPool-II 9999, que por sua vez se conecta aos demais bancos de dados pela porta padrão do PostgreSQL 5432. Desse modo, o arquivo de conf iguração não mais se conecta a um servidor PostgreSQL diretamente. O primeiro cluster, nomeado “Cubie1”, é responsável por armazenar uma das bases de dados do sistema. Cada cluster possui uma réplica da base de dados que é gerenciada pelo middleware. Desse modo, a cada nova alteração realizada, todas as bases de dados recebem a instrução para se atualizarem. O Cubie1 também é responsável por hospedar o middleware, por esse motivo, ambos possuem o mesmo endereço na rede. O ideal é ele ficar em seu próprio hardware desvinculado de qualquer base de dados. Porém, por motivos de praticidade, ele pode ser instalado juntamente a um dos clusters, sendo necessário apenas que se configure portas diferentes para o pgpool-II e para o servidor PostgreSQL. O segundo cluster, nomeado “Cubie2”, também é responsável por armazenar uma das bases de dados. Ele foi configurado para ser acessado somente através do middleware, rejeitando qualquer outro endereço de conexão, como visto na Figura 4.

Figura 4. Configuração dos arquivos pg_hba.conf e postgresql.conf


$make $make install

O diretório padrão do pgpool é /usr/local/. Ao término do processo, é necessário configurar o pgpool-II, conforme visto na Figura 5. Seus parâmetros de configuração são salvos no arquivo pgpool.conf. Por padrão, ele vem configurado para aceitar apenas conexões locais usando a porta 9999. Por isso, o parâmetro listen_addresses deve ser editado para receber conexões externas. Também pelo pgpool.conf, é possível configurar a porta pela qual o pcp se conectará ao sistema que, por padrão, é a porta 9898.

Figura 5. Configuração do Arquivo pgpool.conf

O PCP é uma interface controladora que possui diversas f unções que podem coletar informações do pgpool-II e dos clusters, terminar processos remotamente, além de poder atar e desatar nós. Para utilizar a interface pcp, é necessária uma autenticação com usuário e senha diferentes dos usados para acessar o PostgreSQL. Ela deve ser definida no arquivo pcp.conf, conforme Figura 6. A senha deve ser criptografada em um formato hash md5, que pode ser obtida através do executável pg_md5 instalado junto com o pgpool-II.

Após a configuração do PCP, é necessário conf igurar os clusters onde estão instalados os servidores PostgreSQL. Para este artigo, foram utilizados dois clusters que devem ser identificados como backend0 e backend1, sendo preciso configurar o host, porta, peso, diretório dos dados e comportamento de cada cluster. O host é o endereço na rede onde se encontra o cluster, a porta é por onde o servidor se comunica, o peso define a prioridade que cada cluster terá caso haja um balanceamento de carga, o diretório de dados é onde está localizada a pasta de dados do PostgreSQL, e o comportamento define o que o cluster fará em determinadas situações. A Figura 7 exibe o arquivo pgpool.conf com os clusters configurados. Agora basta configurar os demais parâmetros do arquivo pgpool. conf para satisfazer o sistema. Para o caso de um sistema de replicação com balanceamento de carga, o atributo “replication_mode” e “load_balance_mode” devem ser definidos como true. Isso permitirá a distribuição automática das queries do tipo “SELECT” para cada cluster a fim de balancear a carga do sistema. Para o balanceamento de carga ocorrer, é necessário que alguns requisitos sejam cumpridos: • A versão do PostgreSQL deve ser a 7.4 ou superior; • O modo de operação do Pgpool-II deve ser replicação ou mas-

ter/slave; • A query não deve estar em um bloco de transação; • A query não seja um “SELECT INTO”, “SELECT FOR UPDATE”

ou “FOR SHARE”. Para iniciar o PgPool-II, é necessário executar o comando: Figura 6. Configuração do autenticador pcp

$ pgpool –n –d > /arquivo_log.log

No comando, a opção –n não executa o processo como um daemon, deixando o terminal aberto. A opção –d habilita as mensagens de log no arquivo indicado. Diversas outras opções podem ser utilizadas como –c para limpar o cachê e -f/-a/-F para especificar os arquivos de configuração (pgpool. conf, pool_hba.conf e pcp.conf respectivamente). Antes de iniciar o PgPool-II, é importante verificar se todos os servidores backend estão em operação.

Comandos PCP

Figura 7. Configuração dos Clusters no arquivo pgpool.conf

Os comandos PCP são utilizados para gerenciar o Pgpool-II através da rede. Além do PCP, ele também pode ser gerenciado por comandos Edição 136 SQL Magazine •

65


Comando

Finalidade Exibe o número total de clusters definidos no arquivo pgpool.conf. Todos são considerados, tanto os ativos quanto os inativos. Exibe a informação de determinado cluster através de seu ID. Ele retorna o status do cluster que pode ser:

pcp_node_count

• 1 para ativo, mas sem conexões;

pcp_node_info

• 2 para ativo e sendo utilizado no momento, e; • 3 para desligado.

pcp_attach_node/pcp_detach_node

Conecta/Desconecta um cluster ao sistema. Caso o cluster esteja ativo, ele é forçado a desligar.

pcp_stop_pgpool

Termina o processo do PgPool-II.

pcp_proc_count

Exibe a lista de processos do PgPool-II separados por um espaço em branco.

pcp_proc_info

Exibe as informações sobre cada processo.

pcp_pool_status

Exibe os parâmetros configurados no arquivo de configuração pgpool.conf.

pcp_detach_node

Retira um cluster do sistema.

pcp_attach_node

Adiciona um novo cluster no sistema.

pcp_promote_node

Configura um novo cluster para ser o Master.

Tabela 1. Lista de alguns principais comandos PCP

SQL onde o Pgpool-II faz uma interceptação procurando por comandos específicos como “pool_status”, “pool_nodes”, entre outros. Já no console PCP, os comandos são interpretados diretamente do terminal. Cada comando pcp recebe um mínimo de cinco argumentos básicos que são o “Timeout”, “hostname”, “PCP Port”, “PCP user” e “PCP password”. Alguns dos principais comandos são listados na Tabela 1. Desse modo, utilizando o termina l PCP, é possível conectar um novo nó ao sistema passando seu ip:

A ferramenta middleware PgPool-II proporciona um confiável ambiente para o desenvolvimento de sistemas de banco de dados distribuído em servidores PostgreSQL, estendendo suas funcionalidades e oferecendo opções para replicação e f ragmentação de dados, controle de conexões, balanceamento de carga, além de uma interface prática e simples para fins administrativos.

Autor Luis Eduardo de Oliveira Floriano [email protected]

Graduado em Banco de Dados e Especialista em Desenvolvimento de Sistemas Web e Mobile. Atua como Desenvolvedor Java, possuindo experiência com banco de dados relacionais, banco de dados distribuídos, PostgreSQL, Oracle e MySQL.

$pcp_attach_node –d 10 192.168.1.30 9898 postgres postgres 1

Todos os comandos PCP possuem um número de cinco argumentos em comum: 1. o tempo máximo em segundos que o comando pode levar para ser executado, abortando-o caso seja ultrapassado; 2. o nome do host em que o Pgpool-II foi configurado; 3. a porta em que o Pgpool-II está escutando; 4. o usuário de autenticação que foi configurado no arquivo pcp. conf e; 5. a senha de acesso. Utilizar um sistema de banco de dados tornou-se indispensável. Porém, o acentuado crescimento do volume de dados, especialmente em grandes empresas, acaba afetando o desempenho na consulta destes dados. Utilizar um sistema distribuído pode auxiliar neste cenário.


Links: Página de Downloads do PgPool-II http://pgpool.net/mediawiki/index.php/Downloads

Documentação Oficial do PgPool-II http://www.pgpool.net/docs/latest/pgpool-en.html



67

SQL Magazine 13665676577592

Recommend Documents

Utilização de Discos

Utilização de Discos