Algumas Diferenças entre o Oracle e o MySQL

Nas últimas semanas venho trabalhando com um banco de dados MySQL que possui algumas tabelas com um volume considerável de dados para a realidade do MySQL, na minha opinião.

São três tabelas que juntas hoje correspondem a aproximadamente 50GB de dados (somando a elas seus respectivos índices) e são acessadas de uma única vez a partir de uma junção. Além disso essa junção é feita a partir das chaves primárias das tabelas e a tabela principal da consulta também é filtrada por um índice, ou seja, podemos assumir quepelo menos alguns cuidados foram tomados na construção da consulta.

Tudo corria bem nesse ambiente até que a quantidade de linhas dessas tabelas chegou na ordem de dezenas e centenas de milhões e foi ai que após alguns ajustes de parâmetros de memória para o storage engine em questão (MyISAM, key_buffer_cache, net_buffer_length, max_allowed_packet, myisam_use_mmap, etc.), muitos explains e muitos show profiles comecei a notar que diferentemente do que eu estava acostumado com o modo do Oracle trabalhar (hash joins, full table scans, etc. para esse perfil de consultas) o MySQL optava pelo velho "nested loop", mas por que?

Consultas e mais consultas em foruns me mostravam que existia um certo "dito popular" que esse seria o limite do MySQL, apesar de ficar claro para mim que não passava de um mito, mais um deles alias, serviu de constatação que realmente o MySQL era mais "recomendado" para o perfil de transações de aplicações web, ou seja, curtas e rápidas.

Além dessa última constatação usei esse fato para motivar um teste comparativo de performance entre os SGBDs que estou mais acostumado a trabalhar, Oracle e MySQL. A idéia foi bem simples, vamos fazer o MySQL imitar o Oracle e o Oracle imitar o MySQL e o resultado mais fatídico ainda: Uma lástima!

Inicialmente apostei que o Oracle trabalharia com Full Table Scans e realizaria o Join utilizando o algoritmo de Hash, com base nessa aposta e a utilização de hints tentei forçar esse comportamento no MySQL resultado: O MySQL não possui algoritmo de Join baseado em Hash na tentativa de forçar outros algoritmos que trabalhassem melhor com o acesso Full da tabela não obtive nenhum resultado satisfatório, na verdade os resultados foram bem piores do que otimizador tinha obtido com seu plano de acesso original.

Chegou a hora então de importarmos os dados no Oracle e ver se a aposta foi certa. Utilizando alguns recursos triviais do MySQL como o Storage Engine CSV e o SQL*Loader do Oracle rapidamente consegui importar os dados do MySQL/MyISAM para o Oracle.

Inicialmente notei que cerca de 25% a mais de espaço foi necessário para comportar a mesma quantidade de dados (ou seja, perspectiva de mais I/O e de mais uso de memória). Uma vez carregado gerei o plano de acesso da consulta e pela primeira vez não obtive surpresa, Full Table Scans e Hash Join, resultado melhor performance que o MySQL, porém nada muito significativo em um primeiro momento, ou seja, não justificava o custo da migração. Os testes com o Oracle utilizando os algoritmos propostos pelo MySQL também não foram interessantes em termos de resultado.

Resultados a parte e considerando que não tive muito a preocupação de isolar as variáveis nesse "benchmark" (hardwares diferentes e o fato de posteriormente nos testes com a utilização de particionamento consegui equilibrar bastante essa diferença de performance) o principal aprendizado que ficou foi o seguinte: Cada SGBD conhece bem seus limites, seja em relação a algoritmos ou em relação a adaptatividade ao hardware disponível e tentar fazer com que um SGBD se comporte como se estivesse influenciado pelo otimizador de outro SGBD não é certeza de sucesso, mesmo que no outro SGBD os resultados sejam tentadores.

Cada SGBD possui seu perfil de aplicação que se comporta melhor com eles, cada um deles também possui um custo (ou nenhum custo) de licenciamento ou suporte, e cada um deles irá exigir mais ou menos trabalho de um DBA para se adaptar as necessidades das aplicações, mas em termos de performance de consultas individuais, seja envolvendo grande volume de dados ou poucas linhas, ainda não encontrei nenhuma bala de prata!

Boas Práticas MySQL

Motivado na construção de uma solução de backups para os meus ambientes de MySQL me defrontei com alguns questionamentos importante sobre que linha seguir. Quando falamos de backups de banco de dados invariavelmente caímos na discussão de qual melhor forma de proteger nosso ambiente, com backups lógicos ou físicos.

Segundo a Oracle um backup lógico, o dump, não pode ser considerado um backup por si só, mas sim um complemento de backup. O backup lógico normalmente é utilizado para situações onde uma restauração de somente algumas linhas, por exemplo é necessária. Algumas outras questões como migrações entre versões ou diferentes plataformas o dump pode ser o único recurso.

Já o backup físico, o "verdadeiro backup" particularmente no MySQL parece ter uma performance surpreendente, principalmente levando em conta o fato do tempo de restore (o dump deixa muito a desejar). Apesar dessa performance atrativa venho enfrentando alguns problemas, aparentemente o utilitário (em perl) responsável por executar o backup físico no MySQL, mysqlhotcopy, é restrito aos storage engines myisam e archive, o que acaba rementendo a um fator negativo dessa independencia que o MySQL tem em seus gerenciadores de tabela.

Aparantemente se você tiver necessidades de performance principalmente no seu procedimento de backup terá de combinar utilitários prontos como o mysqldump e o mysqlhotcopy (existem ferramentas pagas) ou desenvolver seu próprio utilitário.

Em relação a necessidades de performance tenho um banco de dados que possui um tamanho relativamente consideravel de índices, o que me fez recorrer da opção --noindices. Reduzindo 30% praticamente o tamanho de backup e consequentemente no tempo, o que me pareceu uma ótima opção, mas está resultando em uma certa dor de cabeça para reconstruir esses índices utilizando o myisamchk -r.

Procurando por respostas sobre como corrigir esse problema encontrei um workshop sobre segurança e backup em MySQL que acredito que seja de muito bom uso para quem está iniciando nessas áreas de adminsitração no mysql:

http://www.archive.org/details/mysql_backup_security
http://szeged2008.drupalcon.org/files/mysqlBackupSecurity-DrupalCon-2008-08-28.pdf

Aproveitei a oportunidade e enviei um e-mail para o palestrante para ver se consigo alguma luz.

Bom proveito da palestra.

Auditoria MySQL

Meu primeiro post será dedicado a segurança em banco de dados. Desde já deixarei claro que trabalho administrando bancos de dados Oracle, mas esse primeiro post será destinado principalmente para bancos MySQL, portando possivelmente poderá haver algum erro. A idéia desse blog é abordar sempre que possível assuntos relacionados aos problemas rotineiros que você encontra em seu site na web, logicamente o conteúdo aqui descrito pode ser aplicado para qualquer fim, mas o exemplo abaixo será o mais simples possível com intúido de somente ilustrar o que pode ser feito para você agregar mais segurança para seu site e principalmente ao seu banco de dados.

Sabe-se que atualmente os problemas de segurança se centralizam principalmente nas ameaças internas, alguns estudos revelam que a porcentagem de problemas causados por esse tipo de ameaça corresponde a 70% do total de ameaças e sabendo também que muitas vezes a falta de tempo de desenvolvimento e a ausência de preocupações com segurança em detrimento da facilidade de implantação nos levam a uma frase bem oportuna, que é o slogan de um dos produtos da Oracle: "Trust but Verify".

Esse é o slogam da ferramenta Oracle Audit Vault (que me comprometo a futuramente abordar em um post sobre segurança em sistemas corporativos), "traduzindo" podemos alegar que a confiança (Trust) citada pode ser encarada como a falta de tempo ou de comprometimento com segurança presenciada não somente no dia a dia de "desenvolvedores de fim de semana", mas também de grandes empresas que não se dão conta que o pior inimigo é aquele que está dentro de casa. Como não vamos falar de "confiança" mas sim de segurança, a palavra "Verify" é simplesmente traduzida em uma única preocupação, auditoria.

Analisando o mercado, oportunidades e seu comportamento esse slogan é bem revelador, sabendo que por via de regra são poucos que se preocupam na hora de conceder acesso e privilégios para seus usuários internos uma das saídas pode ser a auditoria, ou seja, saber quem, quando e o que foi feito antes de, por exemplo, alguem executar um update sem a cláusula where e ter comprometido todos os registros de uma tabela (vamos assumir que a transação por algum motivo também foi efetivada).

O exemplo dessa implementação de auditoria foi desenvolvido para bancos MySQL mas pode ser utilizado (com algumas adaptações) a maioria dos bancos de dados. Os pré-requisitos para essa implementação são que seu banco de dados suporte a utilização de triggers e que você tenha privilégios para uma vez modelada essa estrutura você possa criar essas tabelas, constraints e as triggers propriamente ditas.

Cenário Proposto)

Iremos assumir como exemplo uma tabela retirada de um modelo de dados de uma locadora de automóveis. A tabela em questão terá o nome de VEICULO e armazenará todas as principais informações sobre cada automóvel que pode ser alugado. Nesse exemplo abrirei mão das formalidades de normalizações para a idéia de auditoria fique mais clara.

CREATE TABLE VEICULO (
CODIGO INTEGER NOT NULL,
PLACA VARCHAR(20) NOT NULL,
MARCA VARCHAR(30),
MODELO VARCHAR(30),
KM INTEGER,
QUANTIDADE_LOCACOES INTEGER,
ANO INTEGER,
PRIMARY KEY(CLICOD));

Passo 1) Identificar as chaves e demais colunas que sejam importantes para caracterizar cada registro na tabela que será auditada:

Analisando em uma tabela as colunas que melhor se candidatam para identificar unicamente cada um de seus registros sempre escolheremos sua chave primária, que no nosso exemplo é a coluna CODIGO. Caso sua chave primária seja artificial (ou uma "surrogate key") você pode optar por escolher mais alguma coluna nesse caso a coluna que mais se candidata a esse papel é a coluna PLACA.

Passo 2) Modelar a tabela que servirá como destino dos logs de auditoria, bem como seus devidos relacionamentos:

Uma vez selecionadas quais colunas serão as "chaves" da tabela para modelar a tabela que será utilizada como destino das informações auditadas, utilizaremos além dessas chaves e de uma data, também informações do usuário, que pode ser no caso do MySQL o usuário do banco de dados (você pode expandir seu esquema de auditoria para capturar qualquer outra informação que você considerar relevante para identificar quem ou a partir de onde foi feita a manipulação).

Além dessas informações é crucial para sua auditoria que sejam capturadas informações das tabelas que sofreram alterações, minha sugestão é que seja captura os valores anteriores e posteriores a manipulação ocorrida:

CREATE TABLE AUDIT_VEICULO_LOG (
CODIGO INTEGER NOT NULL AUTO_INCREMENT,
PLACA VARCHAR(20) NOT NULL,
DATA_ALTERACAO DATETIME,
USUARIO VARCHAR(30),
NOME_COLUNA VARCHAR(30),
VALOR_ANTERIOR VARCHAR(100),
VALOR_POSTERIOR VARCHAR(100));

Passo 3) Desenvolver as triggers:

Segue a codificação das triggers que são responsáveis pela auditoria de uma tabela. Dependendo de seu banco de dados você poderá desenvolver uma única trigger, mas para o MySQL acabei achando mais fácil montar uma trigger para cada tipo de DML. Para esse caso estou assumindo que existe uma tabela que armazena as informações dos usuários.

DELIMITER //

CREATE TRIGGER AUDIT_VEICULO_TBI BEFORE INSERT ON VEICULO
FOR EACH ROW
/*************************************************************/
/* Nome: AUDIT_VEICULO_TBI */
/* Autor: Caio Spadafora */
/* Objetivo: Gravar as informacoes de auditoria da tabela VEICULO */
/* Historico: 08/03/2009 - Caio Spadafora - Criacao. */
/*************************************************************/
BEGIN

/* Declarando variaveis */
SET @usuario=NULL;
SET @sysdate=NULL;

/* Recuperando o nome do usuario */
SELECT USUARIO
INTO @usuario
FROM USUARIO
WHERE usuario = (SELECT SUBSTR(USER(),1,INSTR(USER(),'@')-1));

/* Recuperando a data atual */
SELECT now()
INTO @sysdate;

/* Inserindo o historico de alteracao */
INSERT INTO AUDIT_VEICULO_LOG (CODIGO, PLACA, DATA_ALTERACAO, USUARIO, NOME_COLUNA, VALOR_ANTERIOR, VALOR_POSTERIOR)
VALUES (NEW.CODIGO,NEW.PLACA,@usuario,'CODIGO',NULL,NEW.CODIGO,@sysdate);

INSERT INTO AUDIT_VEICULO_LOG (CODIGO, PLACA, DATA_ALTERACAO, USUARIO, NOME_COLUNA, VALOR_ANTERIOR, VALOR_POSTERIOR)
VALUES (NEW.CODIGO,NEW.PLACA,@usuario,'PLACA',NULL,NEW.PLACA,@sysdate);


INSERT INTO AUDIT_VEICULO_LOG (CODIGO, PLACA, DATA_ALTERACAO, USUARIO, NOME_COLUNA, VALOR_ANTERIOR, VALOR_POSTERIOR)
VALUES (NEW.CODIGO,NEW.PLACA,@usuario,'PLACA',NULL,NEW.PLACA,@sysdate);


INSERT INTO AUDIT_VEICULO_LOG (CODIGO, PLACA, DATA_ALTERACAO, USUARIO, NOME_COLUNA, VALOR_ANTERIOR, VALOR_POSTERIOR)
VALUES (NEW.CODIGO,NEW.PLACA,@usuario,'MODELO',NULL,NEW.MODELO,@sysdate);


INSERT INTO AUDIT_VEICULO_LOG (CODIGO, PLACA, DATA_ALTERACAO, USUARIO, NOME_COLUNA, VALOR_ANTERIOR, VALOR_POSTERIOR)
VALUES (NEW.CODIGO,NEW.PLACA,@usuario,'KM',NULL,NEW.KM,@sysdate);


INSERT INTO AUDIT_VEICULO_LOG (CODIGO, PLACA, DATA_ALTERACAO, USUARIO, NOME_COLUNA, VALOR_ANTERIOR, VALOR_POSTERIOR)
VALUES (NEW.CODIGO,NEW.PLACA,@usuario,'QUANTIDADE_LOCACOES',NULL,NEW.QUANTIDADE_LOCACOES,@sysdate);


INSERT INTO AUDIT_VEICULO_LOG (CODIGO, PLACA, DATA_ALTERACAO, USUARIO, NOME_COLUNA, VALOR_ANTERIOR, VALOR_POSTERIOR)
VALUES (NEW.CODIGO,NEW.PLACA,@usuario,'ANO',NULL,NEW.ANO,@sysdate);

END//

DELIMITER ;

A idéia para auditar remoções é a mesma, a única alteração é que os valores que serão nulos serão os posteriores, já para a auditoria de atualizações a idéia é checar se o valor anterior é diferente do valor posterior e em caso afirmativo gravar as alterações.

Passo 4) Testar

Para testar essa solução simule inserções, atualizações e remoções de registros nas tabelas auditadas, de preferência com usuários diferentes, atente sempre para o horário em que a manipulação está ocorrendo e se todas as colunas estão sendo devidamente auditadas, afinal um erro de digitação pode ser percebido somente quando já é tarde demais e ai você já não poderá saber o que foi modificado.

Passo 5) Preocupações:

Para que seu esquema de auditoria seja efetivo algumas preocupações devem ser levadas em conta, a primeira delas é a utilização de usuários individuais dentro do seu dia a dia, desenvolvedores, aplicações e mesmo administradores devem sempre que possível possuir seu próprio usuário dentro do banco de dados, assim a informação de qual usuário está conectado ao banco no momento da alteração se torna muito mais valiosa.

A segunda preocupação seria em relação ao volume de dados que esses logs irão ocupar dentro do seu banco de dados, para isso o ideal é desenvoler um mecanismo de limpeza, ou purge, uma dica é a utilização do MySQL Events (disponível a partir da versão 5.1, para versões anteriores recorra ao agendamento do seu sistema operacional): http://dev.mysql.com/tech-resources/articles/mysql-events.html

Passo 6) Novidades

Em relação as últimas coisas que venho lendo sobre segurança da informação como um todo, alguns assuntos que valem a pena caso você queira se aprofundar mais no tema são contextualizar a segurança, ou seja, não tratar a segurança como sendo algo binário como por exemplo um simples password. Outro conceito bastante presente nos dias atuais é a gestão de identidade dentro de uma empresa, ou se preferir, Identity Management. Como última dica recomendo fortemente a leitura do blog de uma das pessoas responsáveis por segurança na Oracle e colunista da revista Oracle Magazine, Mary Ann Davidson, o link para seu blog é: http://blogs.oracle.com/maryanndavidson/

Vou ficando por aqui e qualquer dúvida, crítica ou sugestões estou à disposição pelo e-mail spadafora.caio@gmail.com.

Até uma próxima oportunidade.