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!
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!
