Anentropic's questions

AFAICT quando você envia uma consulta ao MySQL de qualquer cliente, ele sempre bloqueia e espera que o servidor responda com sucesso ou falha.

Ao executar uma operação lenta, como adicionar um novo índice em uma tabela grande, isso pode ser um problema, pois deixa o cliente em estado ocioso, aguardando muito tempo pela resposta.

No meu caso, especificamente, quero enviar a solicitação da função AWS Lambda.

Encontrei algumas dicas aqui https://stackoverflow.com/a/41371255/202168 sobre como desanexar e colocar o mysqlprocesso do cliente em segundo plano, e também algumas sugestões de que o servidor MySQL provavelmente cancelará a solicitação se o cliente fechar a conexão. Mas o host onde o mysqlcliente foi lançado ainda precisa continuar em execução.

Parece que isso não pode ser tudo o que existe?

O PostgreSQL tem CREATE INDEX CONCURRENTLYpara esse propósito, ou seja, a consulta retorna rapidamente e a construção do índice continua no servidor.

Estou ciente de que o MySQL não suportaCREATE INDEX CONCURRENTLY

Mas é estranho que não exista um método de cliente sem bloqueio? (bem... existem clientes "assíncronos" ou "sem bloqueio", mas eles ainda precisam manter a mesma conexão aberta)

O que eu quero fazer é algo como:

• envie uma ALTER TABLE ... ADD KEYconsulta
• obtenha o ID do processo do lado do servidor para a consulta
• faça logoff do cliente mysql (e encerre minha execução do Lambda)
• o ADD KEYcontinua rodando no servidor
• de um novo processo, pesquise periodicamente o ID do processo da minha consulta DDL e veja se ele foi concluído ou falhou

Existe alguma maneira de conseguir isso?

Pergunta relacionada ou alternativa: se eu simplesmente desconectar o cliente após enviar a consulta, a ADD KEYexecução continuará até a conclusão/falha em vez de ser cancelada?

O servidor é MySQL 8.0.35 em tabelas AWS RDS e InnoDB.

O banco de dados em questão: AWS RDS, MySQL 8, InnoDB. Armazenamento GP3.

Estou tentando fazer uma inserção em massa de linhas em várias tabelas no banco de dados.

Omiti todos os índices secundários da tabela de destino, ela possui apenas PK. A tabela de destino não está particionada.

Os dados de origem para a importação (que não estão no MySQL) são particionados por intervalo de datas. Para cada partição eu tenho um script que seleciona um lote de dados e os insere no MySQL. Os scripts de loop em lote por partição estão sendo executados simultaneamente em paralelo.

Cada lote é carregado como um dataframe do pandas por tabela, várias transformações são feitas e, em seguida, os dataframes do lote são inseridos no MySQL (em uma transação de banco de dados) usando o to_sqlmétodo de inserção "multi" do pandas.

Posso pensar em várias maneiras de melhorar isso. Duas sugestões que aparecem em todos os conselhos de inserção em massa do MySQL são: a) inserir na ordem PK eb) usar LOAD DATA IN FILE. Atualmente não estou fazendo nenhum dos dois. Mas antes de reescrever radicalmente todo o código, gostaria de entender o sintoma que vejo quando executo o código atual:

Podemos ver a importação em execução por aproximadamente 1 hora. O número de scripts em lote paralelos é totalmente consistente. O tamanho do lote é consistente por toda parte. Cada lote leva cerca de 60 segundos para ser processado e inserido. No gráfico acima, cerca de 200 lotes (vários milhões de linhas) são processados ​​com sucesso. Mas o IOPS aumenta aproximadamente linearmente por 30 minutos até atingir o limite provisionado e ser limitado.

Minha pergunta é: se minha taxa de inserção é constante, por que o IOPS continua aumentando linearmente?

Estou importando cerca de 50 milhões de linhas para MySQL 8, InnoDB. Está no AWS RDS com armazenamento GP3.

A chave exclusiva das linhas é uma string semelhante a uuid.

Ao consultar, nunca nos importaremos com essa chave exclusiva, exceto ao atualizar linhas novas/modificadas da fonte primária.

Normalmente, o ID exclusivo seria o PK. Mas eu li que o índice PK no MySQL é especial porque visa 'agrupar' os dados para valores semelhantes, para melhorar o desempenho.

Parece que ao usar uma string semelhante a uuid como PK, o clustering não ajudará em nossas consultas.

Se eu particionasse a tabela, faria isso por intervalo de datas.

Eu poderia imaginar definir uma PK sintética, ou uma PK composta, que combine o campo de data e o uuid para obter um cluster com maior probabilidade de suportar as consultas que realmente fazemos.

Minha pergunta é a seguinte: quão importante é ter um cluster PK que suporte as consultas típicas (ou seja, os resultados obtidos provavelmente estarão 'próximos' no índice)?

Presumivelmente, o caso típico de um ID de incremento automático para PK também resulta em clustering que tem pouca relação com consultas típicas (muitas vezes não há razão para selecionar IDs adjacentes).

Estou pensando especificamente se o armazenamento SSD moderno torna esse tipo de otimização menos importante, obsoleto... ou ainda mais importante?

Mais contexto

https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html

Como o índice clusterizado acelera as consultas

Acessar uma linha por meio do índice clusterizado é rápido porque a pesquisa do índice leva diretamente à página que contém os dados da linha. Se uma tabela for grande, a arquitetura de índice clusterizado geralmente salva uma operação de E/S de disco quando comparada a organizações de armazenamento que armazenam dados de linha usando uma página diferente do registro do índice.

Parece que o "agrupamento" do índice PK só tem valor para consultas selecionadas por PK.

Trata-se de co-localizar os dados da linha com o índice (?)

Então, se todas as consultas de aplicativos que me interessam usam índices secundários, acho que realmente não importa quais são as propriedades do PK? por exemplo, incluir uma coluna de partição de data no PK não vai acelerar magicamente as consultas usando um índice diferente.

Isso está certo?

Digamos que você tenha uma grande tabela com dezenas de milhões de linhas.

Você deseja UPDATE large_table SET col=value WHERE col=other_value... mas colnão está indexado e EXPLAINmostra que essa consulta fará uma varredura seq em toda a tabela.

Qual é o comportamento de bloqueio aqui? De acordo com a maioria das contas, o Postgres bloqueia apenas as linhas afetadas de uma consulta UPDATE e não possui escalonamento de bloqueio. Então, ele procura as linhas para atualizar primeiro e depois bloqueia apenas as linhas encontradas? Parece que potencialmente haveria problemas de outras consultas atualizando linhas simultaneamente nesse caso. Ele bloqueia cada linha "como as encontra", ou seja, bloqueia as linhas progressivamente à medida que passa pela varredura seq?

Portanto, acho que o melhor caso aqui é bloquear as linhas à medida que as encontra, e as linhas afetadas (somente) serão bloqueadas pelo tempo que a consulta UPDATE levar para ser concluída.

Mas estou preocupado que essa consulta possa acabar bloqueando todas as gravações na tabela até que seja concluída.

Eu li isso: https://habr.com/en/company/postgrespro/blog/503008/ e acho que o pior caso não vai acontecer, mas aqui https://blog.heroku.com/curious-case-table -locking-update-query é uma representação possivelmente imprecisa de informações semelhantes que me dão algumas dúvidas.

O aplicativo usa apenas SELECT, SELECT FOR UPDATEe UPDATEconsultas (ou seja, nenhum outro bloqueio explícito separado desses). A tabela possui chaves estrangeiras para outras tabelas e outras tabelas possuem chaves estrangeiras para esta tabela.

Estamos no Postgres 11.

Estou tentando obter uma lista de pg_locksrelevantes para a transação atual

por exemplo

> BEGIN;
BEGIN
> ALTER TABLE ... ;
ALTER TABLE
> select locktype,virtualxid,transactionid,mode,relation from pg_locks;
  locktype  | virtualxid | transactionid |        mode         | relation
------------+------------+---------------+---------------------+----------
 relation   | [NULL]     |        [NULL] | AccessShareLock     |    11695
 virtualxid | 2/24699    |        [NULL] | ExclusiveLock       |   [NULL]
 relation   | [NULL]     |        [NULL] | AccessExclusiveLock |   801091
(3 rows)

Ok, então o que eu considero como "minha" fechadura aqui é a última, aAccessExclusiveLock

Mas não tem transactionidou virtualxid.

Se houver outra atividade no banco de dados, verei outros bloqueios também nesta lista.

Como faço para filtrar apenas os bloqueios pertencentes à minha transação atual, se eles tiverem IDs de transação nulos?