Help DBA

[Mendigaum]

Tenho uma tabela em meu banco de dados, que faz a representação dos dados de um ranking de jogadores em um joguinho bem simples.

Os dados são guardados no formato:

CREATE TABLE IF NOT EXISTS `ranking` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(30) NOT NULL,
  `last_kill` varchar(30) NOT NULL,
  `timestamp` int(255) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `name` (`name`),
  KEY `last_kill` (`last_kill`),
  KEY `timestamp` (`timestamp`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2087147;

Todas as vezes que um jogador mata outro, é gravado o jogador que matou(name), o que morreu(last_kill) e o timestamp. Para calcular os pontos do jogador, eu preciso calcular quantas vezes ele matou, e depois quantas vezes ele morreu, e diminuir o segundo do primeiro. Para fazer isso, eu preciso agrupar pelo "name", que apesar do nome é um numero e fazer um count. E fazer a mesma coisa com o last_kill com o id desse jogador. Estou atualmente usando duas views diferentes para consultar o ranking semanal e mensal(Elas pegam os registros de uma semana pra frente e um mes pra frente respectivamente). Eu faço joins com os dados desses views e de outra tabela, e mesmo assim está sobrecarregando o servidor. Estou executando essas querys a cada 15 minutos pelo cronjob e gerando uma pagina em html simples com php, para que os usuários não executem todas as vezes que entrarem no site, mas mesmo assim, dá pra notar uma travada no servidor a cada 15 minutos. Segue a baixo o export das views do ranking semanal. A primeira faz a tabela "id do jogador -> quantidade de pessoas que matou" e a segunda "id do jogador -> quantidade de vezes que morreu"

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `ranking_matou_kill` AS select `a`.`name` AS `char_id`,count(`a`.`name`) AS `points` from `ranking` `a` where (`a`.`timestamp` > (unix_timestamp() - (((60 * 60) * 24) * 7))) group by `a`.`name` order by count(`a`.`name`) desc;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `ranking_morreu_kill` AS select `a`.`last_kill` AS `char_id`,count(`a`.`last_kill`) AS `points` from `ranking` `a` where (`a`.`timestamp` > (unix_timestamp() - (((60 * 60) * 24) * 7))) group by `a`.`last_kill` order by count(`a`.`last_kill`) desc;

E o codigo que puxa os dados a cada 15 minutos:

SELECT c.name, ma.points as kills, mo.points as deaths, ma.points/mo.points as ratio, ma.points-mo.points as resultado
FROM `char` c
INNER JOIN ranking_matou_kill_month ma ON ma.char_id=c.char_id
INNER JOIN ranking_morreu_kill_month mo ON mo.char_id=c.char_id
ORDER BY resultado DESC LIMIT 0,100

Um amigo deu umas dicas, inclusive essa de usar as views, mas acho que não é suficiente ainda, precisava de alguém com mais experiencia.

Essa tabela tem atualmente 117mb, e está com dois milhoes de registros, e a previsão é que cresça bastante.

Já agradeço a paciencia de quem quer que leia, mesmo que não responda nada e antecipadamente de quem tentar ajudar.

Editado Julho 16, 2011 por Mendigaum

[Kakao]

Eu acho que não tem como fugir do duplo join. A criação das views por si só não tem influência no desempenho. Elas apenas aumentam a legibilidade da consulta final. Mas tenho duas observações.

A primeira é retirar o order by das views. Como vai ser feita ordenação na consulta final a ordenação das views está gratuita e pode estar causando um impacto no desempenho. Se for necessário executar a view sozinha a ordenação pode ser feita externamente:

select coluna
from minha_view
order by coluna

A segunda observação é que os inner join estão errados, pois estão eliminado os jogadores que nunca mataram ou que nunca morreram. O correto neste caso é o left outer join.

Editado Julho 16, 2011 por Kakao

[Mendigaum]

Por acaso, o conteudo das views é gerado toda vez que dá um select nelas? Achei que elas ficavam tipo prontas e o mysql ia atualizando elas conforme atualizava a tabela original.

Tava vendo aqui, faria diferença na performance colocar os campos nome e last_kill como int?

Na hora que eu executo o ultimo select, quando eu to vendo o "gerenciador de tarefas" do linux(top), os processos do mysql vão pra 100% de uso do CPU. Tem tanto banco de empresa grande aí com milhoes de registros e não acontece isso, por que comigo acontece?

[Kakao]

Por acaso, o conteudo das views é gerado toda vez que dá um select nelas?

Sim.

Tava vendo aqui, faria diferença na performance colocar os campos nome e last_kill como int?

Talvez faça alguma diferença no espaço em disco no futuro quando o número do usuário ficar maior que 999. É porque um int ocupa 4 bytes e um varchar ocupa um ou dois bytes de prefixo mais a string. Como o espaço ocupado em disco pode influenciar bastante no tempo de pesquisa pode ser que faça alguma diferença. Mas eu acho que se fosse só isso não valeria a pena mexer. A principal vantagem de usar int neste caso seria que o próprio tipo funcionaria como uma restrição para garantir a correção do dado. Quero dizer; como o nome só pode ser numérico (é isso mesmo?) então qualquer tentativa de inserir um caractere não numérico no campo int geraria um erro evitando a entrada de dados errados.

Na hora que eu executo o ultimo select, quando eu to vendo o "gerenciador de tarefas" do linux(top), os processos do mysql vão pra 100% de uso do CPU. Tem tanto banco de empresa grande aí com milhoes de registros e não acontece isso

Acontece. Onde eu trabalho os servidores SQL passam a noite fazendo atualizações e às vezes não dá tempo de terminar até o começo do dia seguinte. E são servidores ultra mega super extra poderosos.

Se no seu caso a travadinha começar a se tornar um problema então você vai ter que mudar alguma coisa. Por exemplo você pode manter uma tabela com os totais diários ou semanais ou mensais que seriam atualizados diariamente. E a cada 15 minutos calcular apenas o que entrou desde a última atualização e somar com as tabelas já calculadas. Mas isto adiciona complexidade ao sistema e só vale a pena se você estiver realmente com um problema nas mãos.

[Mendigaum]

Sim, com certeza é necessário mudar algo. Não tem como aguentar uma travada no joguinho a cada 15 minutos. Preciso garantir que o servidor do joguinho tenha acesso garantido ao banco e que em nenhum momento haja sobrecarga no servidor.

O servidor é um 2x AMD Opteron 270 [2GHz] + OEM 2GB DDR1 [2GB] + 4GB virtual.

Eu achava que a view era tipo uma tabela que ficava guardada e sendo atualizada automaticamente de acordo com a atualização da tabela original, e não a cada query.

A questão é que eu não sei o que fazer.

Editado Julho 17, 2011 por Mendigaum

[Denis Courcy]

Sim, com certeza é necessário mudar algo. Não tem como aguentar uma travada no joguinho a cada 15 minutos. Preciso garantir que o servidor do joguinho tenha acesso garantido ao banco e que em nenhum momento haja sobrecarga no servidor.

O servidor é um 2x AMD Opteron 270 [2GHz] + OEM 2GB DDR1 [2GB] + 4GB virtual.

Eu achava que a view era tipo uma tabela que ficava guardada e sendo atualizada automaticamente de acordo com a atualização da tabela original, e não a cada query.

A questão é que eu não sei o que fazer.

Ao usar views você estará desprezando o uso de indices. Ou seja suas consultas em views sempre serão em table scan. (ver manual MySQL seção Restrictions on Views.

Talvez sua solução seja descartar as views e alimentar, por trigger, uma pequena tabela com nome, quantas vezes moreu, quantas vezes matou e diferença entre os dois.

Por que por trigger? Para que você não tenha perda de tempo de I/O entre seu programa e o banco.

Você garantirá agilidade no processamento e tempo de resposta.

É necessário lapidar mais esta idéia. aqui é só um esboço..