Início / dba / Perguntas / 11627
Accepted
GoldenNewby
Asked: 2012-01-27 00:28:49 +0800 CST

Menos RAM que Index_length MyISAM

  • 772

Estou trabalhando em um banco de dados para armazenar dados de "séries temporais" (o valor de X era Y neste momento). As próprias linhas são muito pequenas e de tamanho estático, com a chave primária consistindo em duas colunas smallint, 1 coluna tinyint e 1 coluna timestamp. O uso do comprimento do índice é muito baixo por linha (cerca de 12 bytes), mas o banco de dados será usado para armazenar uma quantidade muito grande de dados.

Portanto, o problema é que o servidor acabará tendo menos RAM física do que o tamanho do index_length no MySQL para essa tabela. Quais são as implicações de isso acontecer? Eu sei que em teoria o Linux pode trocar a memória para o disco, mas isso duplicará o uso do disco (já que já existe um arquivo .MYI)? Quais são as implicações de desempenho de não poder armazenar todo o índice na RAM? Ainda posso esperar seleções abaixo de 10 ms com unidades SATA II em RAID 1?

Em resposta ao primeiro comentário para mais informações

Minha pergunta era mais teórica do que prática no momento. O projeto no qual estou trabalhando é bem financiado o suficiente para que tecnicamente possamos arcar com os custos de RAM, mas prefiro saber as implicações de não ter RAM suficiente para cobrir os índices. Mas de qualquer forma, vou tentar responder de qualquer maneira.

Tecnicamente, o projeto tem RAM ilimitada, então a única razão para ter menos é manter os custos baixos.

Os dados são armazenados em tabelas MyISAM para fins de armazenamento "histórico", mas existem em um NDBCluster nas primeiras 24 horas ou mais (o NDB Cluster usa cerca de 4x o índice RAM do que MyISAM).

Certamente posso atualizar a RAM, mas isso adiciona muita complexidade.

A resposta para a quantidade de uso de MB para o índice é 2,29 MB, mas não faz sentido. No momento, estou apenas testando o uso do índice para a estrutura de dados. Os 2,29 MB consistem em 155.301 linhas (cerca de 15,5 bytes por linha).

...

Portanto, há apenas 1 mesa com a qual realmente me importo. O resto deles são muito pequenos em tamanho. A estrutura da tabela é a seguinte:

CREATE  TABLE IF NOT EXISTS `monitor`.`result` (
  `server` SMALLINT UNSIGNED NOT NULL ,
  `ref_id` SMALLINT UNSIGNED NOT NULL ,
  `request` TINYINT UNSIGNED NOT NULL ,
  `recorded` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  `resolution` TINYINT NOT NULL ,
  `value` MEDIUMINT UNSIGNED NOT NULL ,
  PRIMARY KEY (`server`, `request`, `recorded`, `ref_id`) )
ENGINE = MyISAM

A razão pela qual existe uma coluna "ref_id" é restringir o que o conjunto de dados está se referindo além do nível do servidor. Por exemplo, podemos ter estatísticas sobre um usuário ou dispositivo em um servidor.

Por que eu precisaria de tanta RAM

Pode parecer que a tabela acima não usaria tanta RAM e, na maioria dos casos práticos, não. Eu gostaria de armazenar o máximo de dados possível. Entendo que posso armazenar menos dados, mas gostaria que a resolução dos dados fosse a mais alta possível. O espaço em disco é barato, então não estou nem remotamente preocupado com esse custo, mas a RAM, por outro lado, pode se tornar cara. Mesmo que o modelo de negócios torne viável não ter que se preocupar com a RAM de forma alguma, eu gostaria de manter os custos baixos sempre que possível.

Para colocar em perspectiva, gostaria de armazenar, digamos, no máximo 100 estatísticas a cada minuto para cada servidor monitorado. Você pode ver que o número de linhas aumenta rapidamente com mil servidores (100 × 1.000 × 1.044 × 365 = 38.106.000.000). O orçamento anual para mil servidores é de US$ 120.000 (muita RAM), mas o objetivo é manter os custos baixos.

Refinando a pergunta

Eu realmente aprecio as respostas que foram fornecidas até agora, então vou ser um pouco mais específico para abordar minhas preocupações de forma mais específica.

As respostas até agora me levaram a pensar que preciso simplesmente fazer alguns benchmarks por conta própria e ver o que acontece (desenvolvimento para você!). Portanto, neste ponto, o "problema" que enfrento é que o uso de RAM será inevitavelmente de centenas de gigabytes.

Questão 1 ) Portanto, se eu decidir colocar uma quantidade incrivelmente grande de dados na RAM, eles precisarão ser distribuídos por vários servidores. Já faço isso com o NDBCluster, mas o NDBCluster usa quase 3 vezes mais RAM para armazenar os dados idênticos (15 bytes versus cerca de 48 bytes). Qual é o método aceito para armazenar tantos dados na RAM em um cluster de servidores? Devo implementar algum sistema de nível de aplicativo para integração com vários servidores MyISAM?

Questão 2 ) O MyISAM é a escolha certa em Mecanismos de Banco de Dados? Eu testei um pouco com o InnoDB e parecia usar muito mais RAM do que o MyISAM para lidar com o índice. E as soluções não MySQL?

Questão 3 ) Vale a pena armazenar o índice em um disco? Nesse ponto, eu nem devo criar um índice se ele não estiver na RAM de qualquer maneira (duvido seriamente).

Questão 4 ) Se eu seguir o caminho de não colocar os dados na RAM, que tipo de configuração de disco é recomendada para este projeto? Um RAID de SSDs?

Questão 5 ) Vale a pena não incluir a coluna de valor e resolução no índice? Quanto de desperdício de tempo de CPU estamos falando em assumir que o índice está no disco e não na RAM?

Muito obrigado por qualquer conselho, com certeza selecionarei uma resposta assim que essas perguntas forem respondidas (se possível)

mysql myisam

2 respostas

  1. Best Answer

    OBSERVAÇÃO #1

    Implicações de desempenho devem se tornar rapidamente aparentes se as páginas de índice monitor.resulttiverem que passar por duas coisas:

    • Experiência 1) Troca da RAM que compõe o MyISAM Key Cache
    • Experiência 2) Rotação dentro e fora do MyISAM Key Cache (dimensionado por key_buffer_size )

    A experiência 1 é virtualmente inevitável. Quanto à experiência nº 2, ela pode resultar na remoção de páginas de índice necessárias do cache de chaves MyISAM em face de consultas mais recentes em outras tabelas MyISAM. Essas páginas de índice necessárias podem ser trazidas de volta consultando a tabela correspondente. As duas experiências juntas podem resultar em uma consulta mais lenta do que o esperado em tabelas relativamente pequenas.

    No entanto, você pode minimizar ou neutralizar quaisquer efeitos nocivos da troca, atribuindo o índice de monitor.resultcriando um cache de chave MyISAM dedicado. Será um cache que conterá apenas páginas de índice demonitor.result .

    Como você faz isso ???

    Primeiro, lembre-se de que você mencionou que o uso do índice monitor.resultera de 2,29 MB. Você pode criar esse cache de chave dedicado com esse tamanho com um pouco de headroom, digamos 2,5 MB. Vamos criar esse cache de chave assim:

    SET GLOBAL monitor_result_private_cache.key_buffer_size = 1024 * 512 * 5;
CACHE INDEX monitor.result IN monitor_result_private_cache;
LOAD INDEX INTO CACHE monitor.result;

    Isso executará as seguintes etapas:

    1. Crie o cache de chaves
    2. Atribua o Key Cache à Tabela MyISAM usando LOAD INDEX INTO CACHE
    3. Carregue as páginas de índice da tabela MyISAM atribuída em seu cache correspondente

    Isso impediria convenientemente que as páginas de índice dessa tabela saíssem do cache. As únicas páginas de índice da tabela seriam deixadas se INSERTs monitor.resultaumentassem o conteúdo além de 2,5 MB. Você deve escolher espaço suficiente para acomodar muitos INSERTs em monitor.result.

    OBSERVAÇÃO #2

    Eu também notei o índice que você definiu para monitor.result:

    PRIMARY KEY (`server`, `request`, `recorded`, `ref_id`)

    Se alguma de suas consultas monitor.resultse assemelhar a algo assim:

    SELECT resoultion,value FROM monitor.result
WHERE server = 200 AND refid = 50 AND ... ;

    Você pode acelerar as consultas reordenando a PRIMARY KEY

    CREATE  TABLE IF NOT EXISTS `monitor`.`result` (            
  `server` SMALLINT UNSIGNED NOT NULL ,            
  `ref_id` SMALLINT UNSIGNED NOT NULL ,            
  `request` TINYINT UNSIGNED NOT NULL ,            
  `recorded` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,            
  `resolution` TINYINT NOT NULL ,            
  `value` MEDIUMINT UNSIGNED NOT NULL ,            
  PRIMARY KEY (`server`, `ref_id`, `request`, `recorded`) )            
ENGINE = MyISAM

    ou adicionar um índice UNIQUE

    CREATE  TABLE IF NOT EXISTS `monitor`.`result` (             
  `server` SMALLINT UNSIGNED NOT NULL ,             
  `ref_id` SMALLINT UNSIGNED NOT NULL ,             
  `request` TINYINT UNSIGNED NOT NULL ,             
  `recorded` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,             
  `resolution` TINYINT NOT NULL ,             
  `value` MEDIUMINT UNSIGNED NOT NULL ,             
  PRIMARY KEY (`server`, `request`, `recorded`, `ref_id`),
  UNIQUE KEY uniqndx1 (`server`, `ref_id`, `request`, `recorded`)
ENGINE = MyISAM

    Se você adicionar um índice UNIQUE, deverá dobrar o keycache dedicado de acordo.

    OBSERVAÇÃO #3

    Você mencionou uma unidade SATA. Boa escolha para arquivamento, dados históricos de baixa atualização. Qualquer tabela MyISAM em uma unidade SATA que tenha um keycache dedicado não deve ser incomodada pela pesquisa de índice, mas o tempo de recuperação de dados da unidade depende de você para comparar para ver se você pode viver com os tempos de execução.

    Aqui está uma alternativa:

    Crie um índice que tenha todas as colunas

    CREATE  TABLE IF NOT EXISTS `monitor`.`result` (            
  `server` SMALLINT UNSIGNED NOT NULL ,            
  `ref_id` SMALLINT UNSIGNED NOT NULL ,            
  `request` TINYINT UNSIGNED NOT NULL ,            
  `recorded` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,            
  `resolution` TINYINT NOT NULL ,            
  `value` MEDIUMINT UNSIGNED NOT NULL ,            
  PRIMARY KEY (`server`, `ref_id`, `request`, `recorded`, `resolution`, `value`) )            
ENGINE = MyISAM

    O que isso faz? Ele fornece recuperação de dados de linhas inteiras estritamente do índice. Combinando isso com um keycache dedicado, você terá essencialmente toda a tabela na RAM. Todas as consultas seriam atendidas pelo índice e nunca tocariam na tabela, INDEPENDENTEMENTE da tabela estar em SAS, SATA, SSD ou mesmo pedra.

    ATUALIZAÇÃO 2012-01-26 18:18 EDT

    Pergunta 1: Você pode querer olhar para o memcached. Acredito que exista uma versão InnoDB com um plugin memcached. Pelo menos foi o que ouvi.

    Pergunta 2: InnoDB é para tabelas transacionais. Se você tiver dados de arquivo, tabelas MyISAM compactadas devem preencher a conta. Na verdade, você pode olhar para o mecanismo de armazenamento ARCHIVE .

    Questão 3: Armazenar um índice em disco (MyISAM,InnoDB) é sempre padrão e não pode ser alterado. Você deve usar comandos especiais ou executar consultas especiais para pré-carregar caches.

    Pergunta 4: RAID-10 para altas gravações, SSD para altas leituras. Observe as temperaturas da superfície do disco !!!

    Questão 5: se a tabela for estritamente para armazenar informações históricas, não há necessidade de exagero. Desde que seja uma tabela raramente lida, não há necessidade de considerações especiais de cache.

    • 3

  2. Acho que a documentação do cache de chaves do MySQL dá uma dica do que você pode esperar em índices que excedem a quantidade de RAM alocada:

    Para controlar o tamanho do cache de chaves, use a variável de sistema key_buffer_size. Se essa variável for definida como zero, nenhum cache de chave será usado. O cache de chave também não é usado se o valor key_buffer_size for muito pequeno para alocar o número mínimo de buffers de bloco (8).

    Quando o cache de chaves não está operacional, os arquivos de índice são acessados ​​usando apenas o buffer do sistema de arquivos nativo fornecido pelo sistema operacional.

    Estou assumindo que o MySQL é inteligente o suficiente para saber o tamanho do arquivo .MYI e que ele não caberá na memória; nem vai tentar. Ao acessar os índices, você estará lendo do disco, mas não criará uma cópia duplicada no SWAP em algum lugar.

    Portanto, suas leituras serão tão rápidas quanto suas unidades permitirem. Se acontecer de suas unidades SATA II não serem rápidas o suficiente para esta tabela, uma opção seria transformá-la em uma partição e ter o arquivo de índice localizado em algumas unidades mais rápidas (como SSD).

    Na documentação da tabela de criação , você pode ver que isso é possível:

    partition_definition:
  PARTITION partition_name
    **snip**
    [INDEX DIRECTORY [=] 'index_dir']
    **snip**

    Pessoalmente, nunca tentei isso por causa do custo, mas você mencionou que tem financiamento adequado.

    Você pode estimar as repercussões de desempenho carregando o arquivo de índice para 1 GB e configurando key_buffer_sizepara 500 MB ou algo assim e, em seguida, martelando as solicitações de leitura para obter os discos que estão sendo utilizados.

    • 2

relate perguntas