Eu tenho o MariaDB 10.5 na minha área de trabalho com vários discos (SSD e HDD) para projetos de gravação intensiva. A gravação em uma única tabela é rápida e a porcentagem de páginas sujas permanece próxima de zero com 1000-3000 writes/s
.
No entanto, quando escrevo ativamente em várias tabelas ao mesmo tempo, a porcentagem de páginas sujas aumenta rapidamente. O problema é que a descarga para o disco cai para o nível de 100 writes/s
e permanece nesse nível.
Esse comportamento permanece até uma reinicialização.
Acho que o problema está de alguma forma relacionado (não exatamente) ao identificado por Percona há 10 anos.
Existe algum truque para manter a velocidade de descarga?
key_buffer_size = 20M
max_allowed_packet = 5G
thread_stack = 256K
thread_cache_size = 8
innodb_buffer_pool_size = 70G
innodb_log_buffer_size = 512M
innodb_log_file_size = 20G
innodb_thread_concurrency = 0
innodb_flush_log_at_trx_commit = 0
innodb_compression_level = 6
innodb_io_capacity=2000
innodb_io_capacity_max=30000
innodb_max_dirty_pages_pct=0
innodb_doublewrite = 0
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth=128
innodb_purge_threads=8
innodb_purge_batch_size=600
innodb_flush_neighbors=0
innodb_change_buffer_max_size=50
innodb_buffer_pool_load_at_startup=OFF
innodb_buffer_pool_dump_at_shutdown=OFF
innodb-ft-result-cache-limit=4G
innodb_fatal_semaphore_wait_threshold=7200
innodb_compression_default=ON
innodb_random_read_ahead=1
ATUALIZAÇÃO: Solução possível
Eu não posto isso, pois não tenho certeza se é a solução real. Depois de muitas experiências, descobri que o problema é a descarga adaptativa. Eu abordei o problema por
innodb_adaptive_flushing=0
innodb_adaptive_flushing_lwm=70
Aparentemente, quando a descarga adaptativa é acionada para evitar E/S alta, ela permanece por um longo tempo.
UPDATE2: compactação de página vs coluna
Identifiquei o problema a ser
innodb_compression_default=ON
Seguindo uma sugestão de Rick James , criei tabelas semelhantes com compactação de coluna em vez de compactação de página. A compactação é de cerca de 300% para ambos os métodos (10-20% melhor com compactação de página, como se aplica a toda a tabela em vez de colunas seletivas), mas o desempenho foi significativamente diferente no HDD.
Acho que o problema é ao gravar em vários arquivos esparsos criados pela compactação de página ao mesmo tempo em um HDD (não deve ser um problema no SSD).
Eu preciso recriar todas as tabelas para ter certeza, e o processo é dolorosamente demorado.
Eu suspeito que
innodb_io_capacity_max=30000
é muito grande. Tente 5000.innodb_max_dirty_pages_pct=0
-- Páginas sujas são boas; não tente evitá-los com "0". O padrão é 75 (por cento); O MariaDB 10.5.7 decidiu que um padrão melhor é 90. Experimente um desses. Observe que essa configuração éGLOBAL
dinâmica, portanto, nenhuma reinicialização é necessária.Ao não liberar agressivamente as páginas 'sujas', você está fornecendo a possibilidade de que um bloco ('página') seja gravado mais de uma vez antes de realmente precisar ser gravado no disco.
Qual ferramenta está dizendo "100 gravações/s"?
Para um mergulho mais profundo, forneça o status global e variáveis: http://mysql.rjweb.org/doc.php/mysql_analysis#tuning
100 gravações de disco por segundo - Isso soa como a velocidade máxima de um disco rígido.
100 linhas escritas por segundo -- Isso soa como uma maneira muito ineficiente de fazer
INSERT
(ouUPDATE
).autocommit = ON
e não dentroBEGIN..COMMIT
de , maisinnodb_flush_log_at_trx_commit = 1
e/ousync_binlog = 1
-- Parece que haverá um flush no log para cada instrução.Mostre-nos a instrução "write". Vamos discutir como você pode "agrupar"
INSERT
, evitando assim a liberação/sincronização para cada linha.Com MariaDB e "linhas geralmente têm campos de texto médio longos", considere o uso de compactação de coluna.
Todas as otimizações no InnoDB para páginas sujas, log de redo, limpeza de página, capacidade de io etc. são projetadas com a suposição de que o tráfego de gravação alto é intermitente . Ou seja, depende de haver um período de baixo tráfego de gravação para que toda a liberação diferida possa "acompanhar".
Se você tem uma taxa de gravações muito alta e contínua e seu mecanismo InnoDB não consegue acompanhar, você não pode continuar lançando mais tráfego de gravação em um único dispositivo de disco. Há um teto rígido para a quantidade de E/S que qualquer disco pode suportar.
Portanto, se outros meios de otimização forem usados, suas opções podem ser:
Mude para um sistema de E/S mais sofisticado, por exemplo, RAID0 ou RAID10. Mesmo se você tiver um disco SSD, isso não é infinitamente escalável. Uma matriz distribuída de discos SSD é superior a um único disco SSD.
Distribua gravações em várias instâncias do MySQL, mesmo que estejam compartilhando o mesmo armazenamento. Você pode ter o array de E/S mais poderoso do mundo, então eventualmente seu gargalo será o próprio InnoDB.
Distribua gravações para vários hosts de servidor. Você ganha mais capacidade de E/S paralela usando mais servidores. Eu dou suporte a alguns aplicativos que precisam distribuir gravações em centenas de estilhaços, executados em contêineres docker, usando entre 2 e 8 contêineres por host físico. O que for necessário para obter paralelização e capacidade de E/S suficientes para lidar com o tráfego de gravação.
Se você precisar escalar ainda mais, poderá considerar que o MySQL, sendo projetado para otimizar o tráfego OLTP, não é a melhor tecnologia para uma alta taxa de gravações. Você pode querer avaliar RocksDB ou ScyllaDB ou outros serviços de dados estruturados em log especializados.