Início / dba / Perguntas / 18663
Accepted
Alan
Asked: 2012-06-01 17:33:17 +0800 CST

Gravação aleatória congela

  • 772

Eu tenho um banco de dados MySQL 5.5.23 que executa cerca de 3k qps, 5% dos quais são gravações. Ultimamente, tenho tido um grande problema com congelamentos aleatórios de gravação. Tudo está indo muito bem e, de repente, qualquer gravação que chega pára no estado "atualizar" ou "Atualizando". Eles ficam nesse estado por cerca de um ou dois minutos e finalmente terminam. O problema é que com uma quantidade tão grande de conexões, quando elas congelam aleatoriamente assim, a conexão permanece aberta e inevitavelmente recebo muitos erros de conexão.

Está acontecendo em todas as tabelas durante as consultas INSERT e UPDATE.

Alguém viu isso antes? Existe algo que possa ser feito sobre isso?

Estou usando o Amazon RDS com a maior instância que eles oferecem. Terei o maior prazer em fornecer qualquer informação que vocês precisem, apenas deixe-me saber o que é.

ATUALIZAÇÃO: A tabela principal que estou inserindo é:

CREATE TABLE `mytable` (
 `hash` varchar(5) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,
 `name` varchar(256) DEFAULT NULL,
 `ip` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
 `deletehash` char(15) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,
 `datetime` datetime NOT NULL,
 `api_key` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_cs DEFAULT NULL,
 `account_id` int(10) unsigned DEFAULT NULL,
 `type` varchar(15) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
 `width` int(10) unsigned NOT NULL DEFAULT '0',
 `height` int(10) unsigned NOT NULL DEFAULT '0',
 `size` int(10) unsigned NOT NULL DEFAULT '0',
 `animated` tinyint(1) NOT NULL DEFAULT '0',
 `views` int(10) unsigned NOT NULL DEFAULT '0',
 `lastviewed` datetime NOT NULL,
 PRIMARY KEY (`hash`),
 UNIQUE KEY `deletehash` (`deletehash`),
 KEY `datetime` (`datetime`),
 KEY `account_id` (`account_id`),
 KEY `ip` (`ip`),
 KEY `api_key` (`api_key`),
 KEY `views` (`views`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Inserção de amostra:

insert into mytable (hash, name, ip, deletehashe, datetime, api_key, account_id, type, width, height, size, animated, views, lastviewed) values('abcde', 'file name', '127.0.0.1', 'abcdefghijklmn', '2012-05-31 00:00:00', NULL, NULL, 'mime/type', 0, 0, 0, 0, 0, '0000-00-00 00:00:00');

Exemplo de atualização:

update mytable set views = views+1, lastviewed = NOW() where hash = 'abcde';

"Atualizando"

mysql amazon-rds

1 respostas

  1. Best Answer

    Acho que o problema se deve à escolha do índice clusterizado. Dos documentos do MySQL, índices clusterizados e secundários :

    Cada tabela InnoDB possui um índice especial chamado índice clusterizado, onde os dados das linhas são armazenados. Normalmente, o índice clusterizado é sinônimo da chave primária. Para obter o melhor desempenho de consultas, inserções e outras operações de banco de dados, você deve entender como o InnoDB usa o índice clusterizado para otimizar as operações de pesquisa e DML mais comuns para cada tabela.

    Verifique também a resposta de @marc_s nesta pergunta do SO: Como escolher o índice clusterizado no SQL Server? , onde menciona:

    De acordo com a Rainha da Indexação - Kimberly Tripp - o que ela procura em um índice agrupado é principalmente:

    • Único
    • Estreito
    • Estático

    E se você também pode garantir:

    • Padrão cada vez maior

    então você está bem perto de ter sua chave de agrupamento ideal!

    Agora, seu índice clusterizado é a (chave primária):

    hash varchar(5) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,

    que (vamos ver a lista de verificação) é:

    • Único (sim, OK)
    • Estreito (sim, OK)
    • Estático (talvez, você sabe disso)

    mas provavelmente não é:

    • Padrão cada vez maior (Não, provavelmente não é)

    Então, o que acontece quando você usa um índice clusterizado não crescente?

    Eu não posso responder melhor do que Kimberly L. Trip : Chave de agrupamento cada vez maior - o Debate do Índice Clusterizado........novamente!

    Se a chave de agrupamento for sempre crescente, as novas linhas terão um local específico onde podem ser colocadas. Se esse local estiver no final da tabela, a nova linha precisará de espaço alocado para ela, mas não precisará abrir espaço no meio da tabela. Se uma linha for inserida em um local que não possui espaço, será necessário criar espaço(por exemplo, você insere com base no sobrenome, então, conforme as linhas vêm no espaço, será necessário fazer onde esse nome deve ser colocado). Se for necessário abrir espaço, ele será feito pelo SQL Server fazendo algo chamado divisão. As divisões no SQL Server são divisões 50/50 - simplesmente - 50% dos dados permanecem e 50% dos dados são movidos. Isso mantém o índice logicamente intacto (o nível mais baixo de um índice - chamado de nível folha - é uma lista duplamente vinculada), mas não fisicamente intacto. Quando um índice tem muitas divisões, diz-se que o índice está fragmentado. Bons exemplos de um índice que está sempre aumentando são as colunas IDENTITY (e elas também são naturalmente únicas, estáticas e naturalmente estreitas) ou algo que segue o máximo possível dessas coisas - como uma coluna de data e hora (ou já que NÃO é muito provável que seja único por si só datetime, identity).

    Observe que, apesar da menção do SQL-Server, o mesmo conceito também se aplica aos índices clusterizados do InnoDB. Suponho que o índice clusterizado tenha 2 problemas:

    • Quando você está inserindo uma nova linha (o hash "aleatório" garante isso), ela é inserida em um local aleatório do índice. Isso significa que às vezes não encontrará espaço disponível para ser inserido (observe que o InnoDB sempre deixa algum espaço livre no índice, mas quando esse espaço livre disponível é preenchido) deve haver algum rearranjo do índice - e isso leva tempo .

    • O que o rearranjo também está causando ao longo do tempo é a fragmentação do índice. O que eventualmente tornará outras consultas e declarações mais lentas.

    • 4

relate perguntas