Início / dba / Perguntas / 39693
Accepted
Ranknoodle
Asked: 2013-04-11 12:46:52 +0800 CST

Como acelerar consultas em uma grande tabela de 220 milhões de linhas (9 giga de dados)?

  • 772

O problema:

Temos um site social onde os membros podem avaliar uns aos outros para compatibilidade ou correspondência. Esta user_match_ratingstabela contém mais de 220 milhões de linhas (9 giga de dados ou quase 20 giga em índices). As consultas nesta tabela aparecem rotineiramente em slow.log (limiar > 2 segundos) e é a consulta lenta mais frequentemente registrada no sistema:

Query_time: 3  Lock_time: 0  Rows_sent: 3  Rows_examined: 1051
"select rating, count(*) as tally from user_match_ratings where rated_user_id = 395357 group by rating;"

Query_time: 4  Lock_time: 0  Rows_sent: 3  Rows_examined: 1294
"select rating, count(*) as tally from user_match_ratings where rated_user_id = 4182969 group by rating;"

Query_time: 3  Lock_time: 0  Rows_sent: 3  Rows_examined: 446
"select rating, count(*) as tally from user_match_ratings where rated_user_id = 630148 group by rating;"

Query_time: 5  Lock_time: 0  Rows_sent: 3  Rows_examined: 3788
"select rating, count(*) as tally from user_match_ratings where rated_user_id = 1835698 group by rating;"

Query_time: 17  Lock_time: 0  Rows_sent: 3  Rows_examined: 4311
"select rating, count(*) as tally from user_match_ratings where rated_user_id = 1269322 group by rating;"

Versão do MySQL:

  • versão do protocolo: 10
  • versão: 5.0.77-log
  • versão bdb: Sleepycat Software: Berkeley DB 4.1.24: (29 de janeiro de 2009)
  • máquina de compilação de versão: x86_64 version_compile_os:redhat-linux-gnu

Informações da tabela:

SHOW COLUMNS FROM user_match_ratings;

Dá:

╔═══════════════╦════════════╦════╦═════╦════════╦════════════════╗
║ id            ║ int(11)    ║ NO ║ PRI ║ NULL   ║ auto_increment ║
║ rater_user_id ║ int(11)    ║ NO ║ MUL ║ NULL   ║                ║
║ rated_user_id ║ int(11)    ║ NO ║ MUL ║ NULL   ║                ║
║ rating        ║ varchar(1) ║ NO ║     ║ NULL   ║                ║
║ created_at    ║ datetime   ║ NO ║     ║ NULL   ║                ║
╚═══════════════╩════════════╩════╩═════╩════════╩════════════════╝

Exemplo de consulta:

select * from mutual_match_ratings where id=221673540;

dá:

╔═══════════╦═══════════════╦═══════════════╦════════╦══════════════════════╗
║ id        ║ rater_user_id ║ rated_user_id ║ rating ║ created_at           ║
╠═══════════╬═══════════════╬═══════════════╬════════╬══════════════════════╣
║ 221673540 ║ 5699713       ║ 3890950       ║ N      ║ 2013-04-09 13:00:38  ║
╚═══════════╩═══════════════╩═══════════════╩════════╩══════════════════════╝

Índices

A tabela tem 3 índices configurados:

  1. índice único emrated_user_id
  2. índice composto em rater_user_idecreated_at
  3. índice composto em rated_user_iderater_user_id
mostre o índice de user_match_ratings;

dá:

╔════════════════════╦════════════╦═══════════════════════════╦══════════════╦═══════════════╦═══════════╦═════════════╦══════════╦════════╦═════════════════════════╦════════════╦══════════════════╗
║ Table              ║ Non_unique ║ Key_name                  ║ Seq_in_index ║ Column_name   ║ Collation ║ Cardinality ║ Sub_part ║ Packed ║ Null                    ║ Index_type ║ Comment          ║
╠════════════════════╬════════════╬═══════════════════════════╬══════════════╬═══════════════╬═══════════╬═════════════╬══════════╬════════╬═════════════════════════╬════════════╬══════════════════╣
║ user_match_ratings ║ 0          ║ PRIMARY                   ║ 1            ║ id            ║ A         ║ 220781193   ║ NULL     ║ NULL   ║ BTREE                   ║            ║                  ║
║ user_match_ratings ║ 1          ║ user_match_ratings_index1 ║ 1            ║ rater_user_id ║ A         ║ 11039059    ║ NULL     ║ NULL   ║ BTREE                   ║            ║                  ║
║ user_match_ratings ║ 1          ║ user_match_ratings_index1 ║ 2            ║ created_at    ║ A         ║ 220781193   ║ NULL     ║ NULL   ║ BTREE                   ║            ║                  ║
║ user_match_ratings ║ 1          ║ user_match_ratings_index2 ║ 1            ║ rated_user_id ║ A         ║ 4014203     ║ NULL     ║ NULL   ║ BTREE                   ║            ║                  ║
║ user_match_ratings ║ 1          ║ user_match_ratings_index2 ║ 2            ║ rater_user_id ║ A         ║ 220781193   ║ NULL     ║ NULL   ║ BTREE                   ║            ║                  ║
║ user_match_ratings ║ 1          ║ user_match_ratings_index3 ║ 1            ║ rated_user_id ║ A         ║ 2480687     ║ NULL     ║ NULL   ║ BTREE                   ║            ║                  ║
╚════════════════════╩════════════╩═══════════════════════════╩══════════════╩═══════════════╩═══════════╩═════════════╩══════════╩════════╩═════════════════════════╩════════════╩══════════════════╝

Mesmo com os índices essas consultas são lentas.

Minha pergunta:

Separar essa tabela/dados para outro banco de dados em um servidor que tenha ram suficiente para armazenar esses dados na memória, isso aceleraria essas consultas? Existe alguma coisa que as tabelas/índices estão configuradas que podemos melhorar para tornar essas consultas mais rápidas?

Atualmente temos 16GB de memória; no entanto, estamos pensando em atualizar a máquina existente para 32 GB ou adicionar uma nova máquina com pelo menos isso, talvez unidades de estado sólido também.

mysql performance

3 respostas

  1. Best Answer

    Pensamentos sobre o assunto, lançados em ordem aleatória:

    • O índice óbvio para esta consulta é: (rated_user_id, rating). Uma consulta que obtém dados de apenas um milhão de usuários e precisa de 17 segundos está fazendo algo errado: lendo do (rated_user_id, rater_user_id)índice e depois lendo da tabela os valores (de centenas a milhares) da ratingcoluna, como ratingnão está em nenhum índice. Portanto, a consulta deve ler muitas linhas da tabela que estão localizadas em vários locais de disco diferentes.

    • Antes de começar a adicionar vários índices nas tabelas, tente analisar o desempenho de todo o banco de dados, todo o conjunto de consultas lentas, examine novamente as opções dos tipos de dados, o mecanismo que você usa e as configurações.

    • Considere mudar para uma versão mais recente do MySQL, 5.1, 5.5 ou mesmo 5.6 (também: versões Percona e MariaDB.) Vários benefícios como bugs corrigidos, otimizador melhorado e você pode definir o limite baixo para consultas lentas para menos de 1 segundo (como 10 milissegundos). Isso fornecerá informações muito melhores sobre consultas lentas.

    • A escolha para o tipo de dados de ratingé estranha. VARCHAR(1)? Por que não CHAR(1)? Por que não TINYINT? Isso economizará algum espaço, tanto na tabela quanto nos índices que (vai) incluir essa coluna. Uma coluna varchar(1) precisa de mais um byte sobre char(1) e se forem utf8, as colunas (var)char precisarão de 3 (ou 4) bytes, em vez de 1 (tinyint).

    • 37

  2. Tratei de tabelas para o governo alemão, às vezes com 60 milhões de registros.

    Tínhamos muitas dessas mesas.

    E precisávamos saber muitas vezes o total de linhas de uma tabela.

    Depois de conversar com os programadores da Oracle e da Microsoft não ficamos tão felizes...

    Então nós, o grupo de programadores de banco de dados, decidimos que em cada tabela há um registro sempre o registro no qual o número total de registros está armazenado. Atualizamos esse número, dependendo das linhas INSERT ou DELETE.

    Tentamos todas as outras maneiras. Este é de longe o caminho mais rápido.

    Usamos esse caminho desde 1998 e nunca tivemos nenhum número errado de linhas, em todas as nossas tabelas de vários milhões de registros.

    • -1

  3. Vou tentar particionar em tipos de classificação, como:

    mutual_match_ratings_N, mutual_match_ratings_S, etc.

    Você deve realizar uma consulta para cada tipo, mas talvez seja mais rápido do que o contrário. De uma chance.

    Isso pressupõe que você tenha um número fixo de tipos de classificação e que não precise dessa tabela para outras consultas que seriam piores com essa nova estrutura.

    Se for esse o caso, você deve procurar outra abordagem ou manter duas cópias da tabela (sua tabela inicial e as particionadas) se isso for acessível em termos de espaço e manutenção (ou lógica do aplicativo).

    • -3

relate perguntas