peppy's questions

Tenho um script de tarefa cron de manutenção que é executado a cada 5 minutos que TRUNCA uma tabela, a preenche novamente com dados de manutenção e, em seguida, a TRUNCA no final. Também executo um script mysqldump diário que faz backup de todas as minhas tabelas. Percebi recentemente que algumas das tabelas estavam faltando no backup. Após uma investigação mais aprofundada, descobri que o mysqldump está gerando este erro:

Error 1412 "Table Definition Has Changed"

Depois de investigar mais a fundo, aparentemente o problema é porque meu script de tarefa cron está usando TRUNCATE para esvaziar a tabela no meio de um backup do mysqldump.

Estou usando a opção --single-transaction, mas isso não faz diferença.

Existe alguma maneira de permitir que o mysqldump continue mesmo se uma tabela for truncada enquanto o mysqldump estiver em execução (e ainda fazer backup dessa tabela)? Isso precisa ser feito sem a opção --ignore-table. Descobri como fazer isso, mas prefiro incluir todas as minhas tabelas de manutenção no backup, caso o banco de dados precise ser completamente restaurado de um backup.

A única outra coisa que consegui pensar foi usar "DELETE FROM table" em vez de "TRUNCATE table", mas eu teria que passar por todos os meus scripts em todos os meus sites para mudar isso.

Recentemente, atualizei meu banco de dados para o MySQL 8.0 e estou usando o phpmyadmin para obter informações de status. Também atualizei meu servidor virtual para 4 GB de RAM e 2 vCPUs, destinado a ser um servidor somente MySQL para meus sites. MySQL roda sozinho no servidor, tenho php e tudo mais em um servidor separado.

Problemas: O uso de memória no meu servidor parece aumentar com o tempo. Geralmente funciona bem, mas irá travar depois de alguns dias devido ao OOM killer. Isso pode ser feio, às vezes o MySQL não será reiniciado por horas (bloqueado / congelado / etc), mesmo que meu script cronjob verifique a cada 5 minutos para ver se o MySQL está em execução e reiniciar se não estiver em execução. Meus sites às vezes acabam ficando inativos a noite toda/manhã até eu acordar, e serei forçado a reiniciar o sistema operacional algumas vezes antes que as coisas comecem a funcionar novamente.

Há também problemas com lentidão nos sites que parecem acontecer sem aviso prévio, sem motivo aparente, nada no log de consultas lentas, tráfego lento do site, com muita memória disponível. Isso acontece por cerca de uma hora e o problema desaparece por conta própria. Quando isso acontece, pode levar de 20 a 30 segundos para uma página da Web carregar, devido a um problema com o MySQL.

Eu investiguei os logs de consulta lenta e as consultas que são executadas sem usar índices. Depois de investigar, muitas delas envolvem pequenas tabelas com 200 linhas para países/estados, onde selecionamos a maior parte da tabela e a exibimos no site, conforme projetado (e é por isso que muitas delas aparecem nas "consultas que não usam índices"). Caso contrário, não há muito mais nessa parte do log além de selecionar muitas coisas de pequenas mesas.

Aqui estão alguns dos dados do phpmyadmin (atualizado em 21 de maio):

Network traffic since startup: 165.1 GiB
This MySQL server has been running for 3 days, 15 hours, 58 minutes and 2 seconds. It started up on May 18, 2021 at 05:43 AM.

Traffic     #   ø per hour
Received    5.3 GiB 61.4 MiB
Sent    159.8 GiB   1.8 GiB
Total   165.1 GiB   1.9 GiB
Connections #   ø per hour  %
Max. concurrent connections 32  --- ---
Failed attempts 25  0.28    <0.01%
Aborted 0   0   0%
Total   2,494 k 28.35 k 100.00%

As variáveis ​​de status alertadas (marcadas em vermelho pelo phpmyadmin como algo errado) - Atualizado em 21 de maio:

Aborted connectsDocumentation   25  The number of failed attempts to connect to the MySQL server.
Binlog cache disk useDocumentation  19.5 k  The number of transactions that used the temporary binary log cache but that exceeded the value of binlog_cache_size and used a temporary file to store statements from the transaction.
Handler read rndDocumentation   70.2 M  The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly.
Handler read rnd nextDocumentation  5.9 G   The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.
Innodb buffer pool pages dirtyDocumentation 20  The number of pages currently dirty.
Innodb buffer pool readsDocumentation   6.8 M   The number of logical reads 
that InnoDB could not satisfy from buffer pool and had to do a single-page read.
Innodb buffer pool wait freeDocumentation   3   Normally, writes to the InnoDB buffer pool happen in the background. However, if it's necessary to read or create a page and no clean pages are available, it's necessary to wait for pages to be flushed first. This counter counts instances of these waits. If the buffer pool size was set properly, this value should be small.
Innodb row lock time avgDocumentation   911 The average time to acquire a row lock, in milliseconds.
Innodb row lock time maxDocumentation   31.9 k  The maximum time to acquire a row lock, in milliseconds.
Innodb row lock waitsDocumentation  228 The number of times a row lock had to be waited for.
Opened tablesDocumentation  7.9 k   The number of tables that have been opened. If opened tables is big, your table cache value is probably too small.
Select full joinDocumentation   203 k   The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.
Slow queriesDocumentation   43  The number of queries that have taken more than long_query_time seconds.Documentation
Sort merge passesDocumentation  4.3 k   The number of merge passes the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable.
Table locks waitedDocumentation 1.5 k   The number of times that a table lock could not be acquired immediately and a wait was needed. If this is high, and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication.

Estatísticas de consulta (atualizadas em 21 de maio):

Questions since startup: 35,646,301 Documentation
ø per hour: 405,017
ø per minute: 6,750
ø per second: 113
Statements  #   ø per hour  %
select  33,914 k    385.3 k 95.14
update  568 k   6,448.2 1.59
insert  349 k   3,968.7 0.98
change db   337 k   3,826.6 0.94
set option  303 k   3,447.3 0.85
replace 136 k   1,545.2 0.38
delete  14,064  159.8   0.04
update multi    4,827   54.8    0.01
show fields 2,940   33.4    0.01
truncate    2,163   24.6    0.01
show status 2,092   23.8    0.01
show replica status 2,092   23.8    0.01
show slave status   2,092   23.8    0.01
show master status  2,091   23.8    0.01
show processlist    2,047   23.3    0.01
show create table   1,059   12  <0.01
show table status   979 11.1    <0.01
rollback to savepoint   957 10.9    <0.01
show triggers   957 10.9    <0.01
show keys   335 3.8 <0.01
show variables  272 3.1 <0.01
show tables 119 1.4 <0.01
create table    64  0.7 <0.01
show warnings   61  0.7 <0.01
insert select   37  0.4 <0.01
drop table  30  0.3 <0.01
delete multi    26  0.3 <0.01
unlock tables   15  0.2 <0.01
begin   15  0.2 <0.01
show create db  15  0.2 <0.01
savepoint   15  0.2 <0.01
show create trigger 12  0.1 <0.01
release savepoint   12  0.1 <0.01
show grants 8   0.1 <0.01
show binlogs    8   0.1 <0.01
show databases  5   0.1 <0.01
kill    4   <0.1    <0.01
show storage engines    2   <0.1    <0.01
show slave hosts    1   <0.1    <0.01
show replicas   1   <0.1    <0.01
flush   1   <0.1    <0.01
create db   1   <0.1    <0.01

Arquivo de configuração do MySQL my.cnf. Alguns anos atrás, um DBA profissional me disse para definir essas variáveis ​​para ajustar o mysql para um servidor de 1 GB - para lidar com falhas de memória insuficiente. A única variável que alterei recentemente foi o innodb_buffer_pool_size de 512 MB para 2G (atualizado em 21 de maio, adicionado "skip-name-resolve" para corrigir um erro que encontrei no mysqltuner):

[mysqld]
skip-name-resolve
default_authentication_plugin = mysql_native_password
character_set_server=latin1
collation_server=latin1_swedish_ci
port = 3306
sql_mode = "NO_ENGINE_SUBSTITUTION"
innodb_buffer_pool_size = 2000M
innodb_strict_mode = OFF
join_buffer_size = 1M
key_buffer_size = 64M
max_connect_errors = 10000
myisam_recover_options = "BACKUP,FORCE"
performance_schema = 0
read_buffer_size = 1M
slow_query_log = ON
sort_buffer_size = 1M
sync_binlog = 0
thread_stack = 262144
wait_timeout = 14400
table_open_cache = 10000
table_definition_cache = 2500
open_files_limit = 30000
max_connections = 100
read_rnd_buffer_size = 128K
innodb_change_buffer_max_size = 15
innodb_log_buffer_size = 12M
innodb_log_file_size = 120M
innodb_buffer_pool_instances = 8
innodb_lru_scan_depth = 128
innodb_page_cleaners = 64
thread_cache_size = 50
max_heap_table_size=24M
tmp_table_size=24M
thread_cache_size=100
innodb_io_capacity=800
read_buffer_size=128K
read_rnd_buffer_size=64K
eq_range_index_dive_limit=32
symbolic-links=0
key_cache_age_threshold=64800
key_cache_division_limit=50
key_cache_block_size=32K
innodb_buffer_pool_dump_pct=90
innodb_print_all_deadlocks=ON
innodb_read_ahead_threshold=8
innodb_read_io_threads=64
innodb_write_io_threads=64
max_allowed_packet=32M
max_seeks_for_key=32
max_write_lock_count=16
myisam_repair_threads=4
open_files_limit=30000
query_alloc_block_size=32K
query_prealloc_size=32K
sort_buffer_size=2M
updatable_views_with_limit=NO
general_log_file=/var/log/mysql/general.log
slow_query_log_file=/var/log/mysql/slow-query.log

ATUALIZAR

Decidi colocar todas as tabelas MyISAM no Innodb em todos os meus bancos de dados. Não há mais tabelas MyISAM. Espero que isso possa simplificar o trabalho de ajuste.

ATUALIZAÇÃO 2: Pastebins

3 dias atrás, eu eliminei todas as tabelas MyISAM e as fiz tabelas InnoDB, adicionei "skip-name-resolve" ao my.cnf e reiniciei o servidor. Atualizei o restante das informações acima do phpmyadmin, atual em 21 de maio e adicionei esses pastebins hoje também para os novos dados após o servidor funcionar por 2-3 dias.

MOSTRAR STATUS GLOBAL: https://pastebin.com/r3t84pvZ

MOSTRAR VARIÁVEIS GLOBAIS: https://pastebin.com/kQpevtdx

MOSTRAR LISTA DE PROCESSOS COMPLETA: https://pastebin.com/fR6b7Tdg

STATUS: https://pastebin.com/vyWyhZSf

Sintonizador MySQL: https://pastebin.com/ETLCa48V

TOPO: https://pastebin.com/cU8RvgpT

ulimit -a: https://pastebin.com/BhNVgEXH

iostat -xm 5 3: https://pastebin.com/MxymEXyq

/proc/meminfo: https://pastebin.com/PKKeumyt