Documentando este erro e solução aqui, caso possa ajudar alguém. Temos um banco de dados MYSQL 8 no Amazon RDS. O grupo abaixo costumava funcionar bem no Mysql 5.7, mas começou a dar
1022 Can't write; duplicate key in table '/rdsdbdata/tmp/#sqlf80_1656bc_0'
erro quando atualizamos para o Mysql 8.
GROUP BY DATE_FORMAT(CONCAT(YEAR(performance_stats_date),'-',
MONTH(performance_stats_date),'-',DAY(performance_stats_date)),
'%Y-%m-%d')
Recentemente, tentamos configurar innodb_undo_tablespaces=2nosso servidor mysql 5.7. Mas durante a reinicialização o servidor retornou com:
[ERROR] InnoDB: Expected to open 2 undo tablespaces but was able to find only 0 undo tablespaces. Set the innodb_undo_tablespaces parameter to the correct value and retry. Suggested value is 0
Acontece que isso já está documentado : " O número de innodb_undo_tablespaces deve ser definido antes de inicializar o InnoDB. A tentativa de reiniciar o InnoDB com um número maior de espaços de tabela de desfazer do que o especificado quando você criou o banco de dados resultará em falha na inicialização e em um erro informando que o InnoDB não encontrou o número esperado de tablespaces de undo. "
O que significa "primeiro criou o banco de dados"? A documentação significa uma nova instalação do mysql onde não há bancos de dados no servidor? Existe uma maneira de contornar esse requisito?
Mesmo que estejamos usando "innodb_file_per_table = 1" desde o primeiro dia. O arquivo ibdata ainda continua a crescer. O tamanho total do nosso banco de dados é de cerca de 800 GB e o arquivo ibdata está atualmente em torno de 50 GB. Todas as nossas tabelas são innodb.
Como pode crescer quando usamos innodb_file_per_table?
Poderia ser blobs, mediumtext ou esses tipos de colunas?
Existe uma maneira de reduzir isso, considerando que já usamos innodb_file_per_table?
Ao iniciar o mysql nos servidores Centos com um diretório de dados personalizado, você pode obter um erro semelhante ao seguinte:
2014-10-15 10:23:56 17081 [Warning] Can't create test file /database/mysql/localhost.lower-test
Estamos usando o pt-table-checksum do Percona para verificação de integridade de replicação do MySQL.
É possível passar algumas ou todas as opções usando algum tipo de arquivo de configuração?
Por exemplo, podemos escrever a lista de tabelas que queremos verificar em um arquivo?
Entendo que o manual não fala sobre tal arquivo: http://www.percona.com/doc/percona-toolkit/2.1/pt-table-checksum.html
Antes de configurar uma replicação no MySQL, os dados primeiro precisam ser transferidos para o escravo.
Como as tabelas MyISAM podem ser copiadas como arquivos. Seria correto copiar arquivos MyISAM, .myd .myi .frm, para o escravo? Isso seria suficiente para iniciar a replicação?
MySQL 5.5 apresenta "particionamento de colunas".
http://dev.mysql.com/doc/refman/5.5/en/partitioning-columns.html
Estou tentando entender melhor como funciona quando duas colunas são importantes individualmente.
Digamos para uma tabela que contém as mensagens entre dois usuários do sistema. Teríamos potencialmente as colunas "sender_id" e "receiver_id" e poderíamos querer consultar essas colunas individualmente.
Se tivermos índices separados em ambas as colunas, podemos consultá-los individualmente quando necessário. Os resultados são rápidos.
Mas e se nossa tabela tiver 100 milhões de linhas e considerarmos o particionamento? Meu entendimento é que o particionamento de várias colunas se concentra na primeira coluna na definição de colunas e depois na segunda. Aqui está uma estrutura de tabela de exemplo:
CREATE TABLE messages (
message_id INT,
sender_id INT,
receiver_id INT
)
PARTITION BY RANGE COLUMNS(sender_id,receiver_id) (
PARTITION p0 VALUES LESS THAN (10,10),
PARTITION p1 VALUES LESS THAN (20,20),
PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)
Se consultarmos "WHERE receiver_id=5", a remoção de partição não será ativada, certo? Será necessário pesquisar todas as partições. Mas se procurássemos por "WHERE sender_id=5", saberíamos imediatamente que o resultado está em p0.
Portanto, para uma tabela em que duas colunas podem ser individualmente importantes, o particionamento pode não ser a melhor solução, pois agora perdemos o benefício de um índice de tabela completa para a coluna secundária (receiver_id, neste caso) no parâmetro de colunas. Isso está certo?
Para "inserções simultâneas", o manual de referência do MySQL tem a seguinte explicação:
O mecanismo de armazenamento MyISAM suporta inserções simultâneas para reduzir a contenção entre leitores e escritores para uma determinada tabela: Se uma tabela MyISAM não tiver buracos no arquivo de dados (linhas excluídas no meio), uma instrução INSERT pode ser executada para adicionar linhas ao final da tabela ao mesmo tempo que as instruções SELECT estão lendo as linhas da tabela.
http://dev.mysql.com/doc/refman/5.5/en/concurrent-inserts.html
Digamos que o parâmetro "inserção simultânea" do nosso banco de dados esteja definido como "Auto" (1).
E temos uma tabela MyISAM com um gap. Quando inserimos novas linhas e preenchemos essas lacunas, a tabela "imediatamente" se prepara para aceitar "inserções simultâneas" para futuras consultas de inserção?
Ou precisamos executar "OPTIMIZE" antes que a tabela saiba que não há lacunas?
Temos um servidor MySQL 5.0 em uma caixa Centos. 32 GB de memória. Temos apenas um disco rígido, capacidade de 374 GB, 50 GB disponíveis. Todas as nossas mesas são MyISAM.
Quando copiamos um arquivo grande (2GB-3GB) dentro do disco rígido, o MySQL desacelera temporariamente. A cópia do arquivo leva menos de um minuto. Mas a desaceleração temporária dura cerca de uma hora (pode até ser exatamente uma hora).
Passada uma hora, de repente tudo volta à sua rotina.
Parece que a cópia de arquivo grande empurra o MySQL para começar a usar o disco rígido para memória, talvez a saída "sar -b" possa ser uma prova disso. Mas por que o mysql começaria a usar o disco rígido, o arquivo copiado é cerca de 10% da RAM total.
Alguma ideia para evitar isso?
Aqui está a saída do sar, a cópia do arquivo foi feita por volta das 11:00:
10:20:01 AM CPU %user %nice %system %iowait %steal %idle
10:30:01 AM all 5.52 0.00 4.40 5.35 0.00 84.72
10:40:01 AM all 5.38 0.00 4.34 5.34 0.00 84.93
10:50:01 AM all 6.44 0.00 4.86 7.00 0.00 81.70
11:00:01 AM all 6.06 0.00 4.33 6.48 0.00 83.12
11:10:02 AM all 6.18 0.00 6.11 9.60 0.00 78.11
11:20:01 AM all 1.92 0.00 1.95 8.58 0.00 87.55
11:30:01 AM all 2.23 0.00 2.59 8.53 0.00 86.64
11:40:01 AM all 3.63 0.00 3.57 8.95 0.00 83.85
Aqui está a saída sar -r:
10:20:01 AM kbmemfree kbmemused %memused kbbuffers kbcached kbswpfree kbswpused %swpused kbswpcad
10:30:01 AM 823860 32125192 97.50 345996 24971808 34995984 232 0.00 0
10:40:01 AM 895852 32053200 97.28 360620 24877364 34995984 232 0.00 0
10:50:01 AM 295556 32653496 99.10 372440 25451620 34995984 232 0.00 0
11:00:01 AM 809260 32139792 97.54 395116 24869132 34995984 232 0.00 0
11:10:02 AM 430732 32518320 98.69 659220 23661112 34995984 232 0.00 0
11:20:01 AM 153580 32795472 99.53 687740 24438764 34995984 232 0.00 0
11:30:01 AM 251816 32697236 99.24 778396 24284876 34995984 232 0.00 0
11:40:01 AM 297912 32651140 99.10 782468 24354304 34995984 232 0.00 0
Olhando para sar -P ALL, CPU 1 tem %70+ iowait:
10:40:01 AM CPU %user %nice %system %iowait %steal %idle
10:50:01 AM all 6.44 0.00 4.86 7.00 0.00 81.70
10:50:01 AM 0 5.87 0.00 4.70 1.77 0.00 87.66
10:50:01 AM 1 12.14 0.00 5.80 44.59 0.00 37.48
10:50:01 AM 2 2.68 0.00 2.30 0.43 0.00 94.58
10:50:01 AM 3 5.90 0.00 4.07 11.72 0.00 78.31
10:50:01 AM 4 2.68 0.00 2.53 0.59 0.00 94.20
10:50:01 AM 5 6.45 0.00 4.88 9.13 0.00 79.54
10:50:01 AM 6 2.84 0.00 2.43 0.52 0.00 94.22
10:50:01 AM 7 5.04 0.00 4.31 3.04 0.00 87.61
10:50:01 AM 8 6.37 0.00 4.85 0.73 0.00 88.04
10:50:01 AM 9 8.88 0.00 6.45 17.30 0.00 67.37
10:50:01 AM 10 5.17 0.00 3.79 1.34 0.00 89.69
10:50:01 AM 11 10.85 0.00 8.95 8.72 0.00 71.48
10:50:01 AM 12 4.12 0.00 3.41 0.93 0.00 91.53
10:50:01 AM 13 10.27 0.00 7.59 7.06 0.00 75.08
10:50:01 AM 14 6.81 0.00 5.69 3.01 0.00 84.48
10:50:01 AM 15 6.94 0.00 6.00 1.15 0.00 85.91
11:00:01 AM all 6.06 0.00 4.33 6.48 0.00 83.12
11:00:01 AM 0 5.86 0.00 6.11 5.44 0.00 82.59
11:00:01 AM 1 11.14 0.00 4.55 45.71 0.00 38.60
11:00:01 AM 2 3.33 0.00 3.24 1.18 0.00 92.25
11:00:01 AM 3 4.33 0.00 3.04 7.62 0.00 85.01
11:00:01 AM 4 2.28 0.00 1.83 1.87 0.00 94.02
11:00:01 AM 5 4.61 0.00 4.51 3.97 0.00 86.91
11:00:01 AM 6 2.59 0.00 2.42 1.80 0.00 93.19
11:00:01 AM 7 3.90 0.00 3.23 2.55 0.00 90.33
11:00:01 AM 8 4.86 0.00 3.94 1.21 0.00 89.99
11:00:01 AM 9 7.06 0.00 5.12 12.43 0.00 75.39
11:00:01 AM 10 4.87 0.00 4.76 1.09 0.00 89.28
11:00:01 AM 11 6.42 0.00 5.20 5.67 0.00 82.71
11:00:01 AM 12 6.28 0.00 5.61 4.63 0.00 83.48
11:00:01 AM 13 14.10 0.00 7.61 4.17 0.00 74.12
11:00:01 AM 14 4.76 0.00 3.54 3.67 0.00 88.04
11:00:01 AM 15 10.64 0.00 4.61 0.74 0.00 84.01
11:00:01 AM CPU %user %nice %system %iowait %steal %idle
11:10:02 AM all 6.18 0.00 6.11 9.60 0.00 78.11
11:10:02 AM 0 5.79 0.00 4.76 1.17 0.00 88.28
11:10:02 AM 1 5.87 0.00 16.54 72.31 0.00 5.29
11:10:02 AM 2 3.24 0.00 3.04 4.31 0.00 89.42
11:10:02 AM 3 5.66 0.00 5.32 9.17 0.00 79.85
11:10:02 AM 4 3.81 0.00 3.40 1.59 0.00 91.20
11:10:02 AM 5 6.06 0.00 5.02 9.16 0.00 79.77
11:10:02 AM 6 4.10 0.00 3.94 0.71 0.00 91.25
11:10:02 AM 7 4.64 0.00 4.29 6.18 0.00 84.88
11:10:02 AM 8 6.19 0.00 5.12 0.50 0.00 88.20
11:10:02 AM 9 8.72 0.00 8.12 26.72 0.00 56.44
11:10:02 AM 10 6.37 0.00 4.78 1.01 0.00 87.83
11:10:02 AM 11 8.59 0.00 7.01 8.53 0.00 75.86
11:10:02 AM 12 5.43 0.00 5.09 0.72 0.00 88.76
11:10:02 AM 13 11.04 0.00 9.00 6.99 0.00 72.97
11:10:02 AM 14 5.60 0.00 5.33 1.12 0.00 87.95
11:10:02 AM 15 7.73 0.00 7.05 3.40 0.00 81.82
11:20:01 AM all 1.92 0.00 1.95 8.58 0.00 87.55
11:20:01 AM 0 1.85 0.00 1.79 11.58 0.00 84.78
11:20:01 AM 1 4.02 0.00 7.08 77.21 0.00 11.69
11:20:01 AM 2 1.32 0.00 1.14 1.08 0.00 96.46
11:20:01 AM 3 1.35 0.00 1.25 3.75 0.00 93.64
11:20:01 AM 4 1.29 0.00 1.11 1.08 0.00 96.53
11:20:01 AM 5 1.49 0.00 1.37 2.70 0.00 94.44
11:20:01 AM 6 1.59 0.00 1.27 0.92 0.00 96.21
11:20:01 AM 7 1.62 0.00 1.50 3.74 0.00 93.14
11:20:01 AM 8 1.84 0.00 1.68 3.32 0.00 93.15
11:20:01 AM 9 2.09 0.00 2.24 19.19 0.00 76.49
11:20:01 AM 10 2.15 0.00 1.42 1.02 0.00 95.41
11:20:01 AM 11 1.95 0.00 1.95 3.06 0.00 93.04
11:20:01 AM 12 1.90 0.00 1.68 1.10 0.00 95.32
11:20:01 AM 13 2.40 0.00 2.09 2.94 0.00 92.57
11:20:01 AM 14 1.90 0.00 1.87 1.14 0.00 95.09
11:20:01 AM 15 1.89 0.00 1.83 3.37 0.00 92.91
sar -W:
10:20:01 AM pswpin/s pswpout/s
10:30:01 AM 0.00 0.00
10:40:01 AM 0.00 0.00
10:50:01 AM 0.00 0.00
11:00:01 AM 0.00 0.00
11:10:02 AM 0.00 0.00
11:20:01 AM 0.00 0.00
11:30:01 AM 0.00 0.00
11:40:01 AM 0.00 0.00
sar -b, observe o salto logo após as 11h.
10:20:01 AM tps rtps wtps bread/s bwrtn/s
10:30:01 AM 1941.72 296.49 1645.23 2619.86 26872.80
10:40:01 AM 2018.03 341.37 1676.66 3123.88 25695.44
10:50:01 AM 2581.44 460.03 2121.41 6384.41 35324.48
11:00:01 AM 3031.42 508.31 2523.11 4936.72 44437.03
11:10:02 AM 7222.13 1083.55 6138.58 61930.06 122374.97
11:20:01 AM 4832.72 1960.20 2872.53 55023.49 55869.57
11:30:01 AM 2174.64 1619.38 555.26 14751.91 9753.42
11:40:01 AM 2361.23 1682.74 678.49 15293.00 11799.85
11:50:01 AM 2210.12 1433.24 776.88 12757.86 13321.64
12:00:01 PM 3439.84 932.84 2507.01 22081.47 44653.18
Eu tenho pesquisado o uso de SQL_BUFFER_RESULT. Principalmente, é referido como uma ajuda para reduzir os problemas de bloqueio de tabela.
Parece ser uma boa opção para usar.
No entanto, não consigo encontrar nenhuma desvantagem nisso. É uma opção que deve ser usada na maioria das vezes?
Meu entendimento é que o mysqlhotcopy funciona de maneira muito semelhante ao rsync.
Em um de nossos servidores, o backup do arquivo de banco de dados usando rsync leva cerca de 15 minutos para dados de 50 GB.
Fazer backup dos mesmos dados usando mysqlhotcopy leva cerca de 30 minutos.
Por que o mysqlhotcopy é significativamente mais lento?
Recentemente, mudamos uma de nossas tabelas para innodb e agora estamos experimentando tempos de execução de UPDATE muito lentos. Uma atualização que costumava levar de 0,010 a 0,030 segundos agora pode levar mais de 70 segundos. Algumas consultas são descartadas porque não podem adquirir um bloqueio dentro do limite padrão de 50 segundos (sei que podemos aumentar esse limite).
A tabela em questão possui apenas um índice, a própria chave primária que é um valor mediumint. A tabela tem cerca de 1 milhão de linhas. Todos os UPDATEs neste contexto envolvem uma única linha. Normalmente, 4-5 colunas dessa linha são afetadas em cada consulta.
O my.cnf atual é colado abaixo. Você vê algo que poderia causar um desempenho ruim de UPDATE para o innodb?
[mysqld]
set-variable=local-infile=0
datadir=/db/mysql/data
socket=/var/lib/mysql/mysql.sock
#log = /var/log/mysqld.log
log-error = /var/log/mysqld.error.log
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
skip-locking
key_buffer = 1G
query_cache_size = 256M
thread_cache_size = 128
table_cache = 2048
max_connections = 400
query_cache_limit = 1024M
log_slow_queries = /var/log/mysql-slow.log
long_query_time = 1
skip-bdb
skip-locking
skip-name-resolve
innodb_buffer_pool_size=1G
innodb_additional_mem_pool_size=20M
innodb_flush_log_at_trx_commit=2
#innodb_log_file_size=250M
innodb_log_buffer_size=8M
innodb_lock_wait_timeout=50
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
ATUALIZAÇÃO:
innodb_log_file_size: 5242880
have_innodb: SIM A
cláusula "WHERE" sempre procura apenas uma coluna, que é a chave primária.
ATUALIZAÇÃO - 26 de julho de 2012: Atualizamos
nosso banco de dados para mysql 5.5. Agora as atualizações do innodb são bem rápidas, menos de 0,010 segundos em nosso caso específico. E a variância é bem baixa. Minha opinião sobre isso é: o innodb deve ser usado com muito cuidado no mysql 5.0.
Estou tentando entender como interpretar melhor o tempo de bloqueio para as consultas que aparecem em nosso log de consultas lentas do MySQL.
Por exemplo, se uma consulta UPDATE tiver um tempo de bloqueio de 10 segundos. Presumo que seja o tempo total após a consulta de atualização adquirir o bloqueio. Mesmo que esteja aguardando a conclusão das consultas de seleção anteriores, mas não executando a própria ação UPDATE, o relógio deve estar correndo, pois está bloqueando todas as consultas SELECT que se alinharam após a consulta UPDATE.
E quanto aos bloqueios de consulta SELECT. Por que algumas consultas selecionadas têm tempos de bloqueio? É porque há uma consulta UPDATE em andamento, portanto, eles estão bloqueando uma tabela juntos.