Temos um MariaDB 10.3 em um Redhat Server (VM) com 90 GB de Ram e 20 VCPUs. Estou tentando otimizar o banco de dados. Instalei o Sysbench para Benchmarking e após um teste de 50 segundos obtive esses valores (com variáveis padrão do MariaDB):
#Benchmarking command:
sysbench oltp_read_write --threads=2 --report-interval=3 --histogram
--time=50 --table-size=1000000 --db-driver=mysql --mysql-host=firstserver
--mysql-db=sbtest --mysql-user=sbtest_user --mysql-password=password run
SQL statistics:
queries performed:
read: 271040
write: 77440
other: 38720
total: 387200
transactions: 19360 (387.15 per sec.)
queries: 387200 (7742.96 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 50.0054s
total number of events: 19360
Latency (ms):
min: 3.75
avg: 5.16
max: 26.99
95th percentile: 6.55
sum: 99964.49
Threads fairness:
events (avg/stddev): 9680.0000/2.00
Para este Benchmarking o innodb_buffer_pool_size foi de apenas 2GB. Tentei otimizar o banco de dados para obter mais transações e consultas por segundo. Alterei essas variáveis:
#set memory (was 2 G)
innodb_buffer_pool_size=70G
#set log file size (was 64MB)
innodb_log_file_size=2G
#set log buffer size (was 16MB)
innodb_log_buffer_size=128M
#set temporary in memory table size (was 16MB)
tmp_table_size=64M
max_heap_table_size= 64M
# set query cache (was 1MB)
query_cache_size=64M
Mas o resultado mudou pouco.
Percebo que você tem 20 núcleos de CPU, mas está executando apenas o sysbench com 2 threads. Você provavelmente pode obter melhores resultados com mais tópicos. A diretriz geralmente dada é 2x o número de núcleos de CPU, então tente com pelo menos
--threads=40.Também descobri que o próprio sysbench tem limites de escalabilidade (como qualquer aplicativo cliente faria). Para obter o melhor resultado, executei várias instâncias de hosts clientes, todos executando o sysbench e conectando-se pela rede ao mesmo banco de dados.
Aumentar o pool de buffers 35x não ajudaria em nada se seus dados fossem pequenos de qualquer maneira, o que suponho que seja para uma execução do sysbench. É como atualizar seu armazém de 35.000 pés quadrados para um armazém de um milhão de pés quadrados para armazenar sua coleção pessoal de fotos.
Não está claro por que você pensou que aumentar as outras variáveis ajudaria. Você tinha alguma medida que apontasse para esses gargalos? Quantas vezes seus commits innodb precisaram esperar o buffer de log ser liberado? Qual porcentagem de suas consultas usaram tabelas temporárias, mas tiveram que gravar a tabela temporária no disco em vez de usar tabelas temporárias na memória?
Meu ponto é que fazer escolhas aleatórias de ajuste para ver o efeito que isso tem no benchmark não o levará a otimizar seu servidor. Você não está se aproximando como um cientista ou como um engenheiro. Se um deles fez a diferença, como você sabe qual deles foi importante? Ou você faria todas as mesmas alterações em seu servidor MySQL de produção? Se sim, como isso não é um ritual supersticioso, como jogar sal por cima do ombro para dar sorte?
Para ajudar outros DBAs, quero adicionar algumas frases do livro "O'Reilly High Performance mysql" à resposta de Bill:
O que não fazer
Você pode esperar (ou acreditar que é esperado) configurar um conjunto de benchmarks e “ajustar” seu servidor alterando sua configuração de forma iterativa em busca de configurações ideais. Isso geralmente não é algo que aconselhamos a maioria das pessoas a fazer. Requer muito trabalho e pesquisa, e o retorno potencial é tão pequeno na maioria dos casos, que pode ser uma enorme perda de tempo. Provavelmente, é melhor gastar esse tempo em outras coisas, como verificar seus backups, monitorar alterações nos planos de consulta e assim por diante.
Você não deve “afinar por proporção”. A “taxa de ajuste” clássica é a regra geral de que a taxa de acertos do pool de buffers do InnoDB deve ser maior que alguma porcentagem e você deve aumentar o tamanho do cache se a taxa de acertos for muito baixa. Este é um conselho muito errado. Independentemente do que alguém lhe diga, a taxa de acertos do cache não tem nada a ver com o fato de o cache ser muito grande ou muito pequeno. Para começar, a taxa de acertos depende da carga de trabalho — algumas cargas de trabalho simplesmente não podem ser armazenadas em cache, não importa o tamanho do cache — e, em segundo lugar, os acertos de cache não têm sentido, por motivos que explicaremos mais adiante. Às vezes acontece que quando o cache é muito pequeno, a taxa de acertos é baixa, e aumentar o tamanho do cache aumenta a taxa de acertos. No entanto, esta é uma correlação acidental e não indica nada sobre desempenho ou dimensionamento adequado do cache.
O problema com correlações que às vezes parecem verdadeiras é que as pessoas começam a acreditar que elas sempre serão verdadeiras. Os DBAs Oracle abandonaram os anos de ajuste baseados em proporção O que não fazer | 105 atrás, e desejamos que os DBAs MySQL sigam seu exemplo. 2 Desejamos ainda mais ardentemente que as pessoas não escrevam “roteiros de afinação” que codifiquem essas práticas perigosas e as ensinem a milhares de pessoas. Isso leva à nossa próxima sugestão do que não fazer: não use scripts de ajuste! Existem vários muito populares que você pode encontrar na internet. Provavelmente é melhor ignorá-los.
Também sugerimos que você evite a palavra tuning, que usamos liberalmente nos últimos parágrafos. Em vez disso, preferimos a configuração ou otimização (desde que seja isso que você esteja realmente fazendo). A palavra tuning evoca imagens de um novato indisciplinado que ajusta o servidor e vê o que acontece. Sugerimos na seção anterior que é melhor deixar essa prática para aqueles que estão pesquisando os internos do servidor. “Ajustar” seu servidor pode ser uma perda de tempo impressionante.
Em um tópico relacionado, pesquisar na Internet por conselhos de configuração nem sempre é uma boa ideia. Você pode encontrar muitos conselhos ruins em blogs, fóruns e assim por diante. Embora muitos especialistas contribuam com o que sabem online, nem sempre é fácil dizer quem está qualificado. Não podemos dar recomendações imparciais sobre onde encontrar especialistas reais, é claro. Mas podemos dizer que os provedores de serviços MySQL confiáveis e respeitáveis são uma aposta mais segura em geral do que uma simples pesquisa na Internet, porque as pessoas que têm clientes satisfeitos provavelmente estão fazendo algo certo. Mesmo o conselho deles, no entanto, pode ser perigoso de aplicar sem teste e compreensão, porque pode ter sido direcionado a uma situação diferente da sua de uma maneira que você não entende.
Finalmente, não acredite na fórmula popular de consumo de memória – sim, a mesma que o próprio MySQL imprime quando trava. (Não vamos repeti-la aqui.) Esta fórmula é antiga. Não é uma maneira confiável ou mesmo útil de entender quanta memória o MySQL pode usar no pior caso. Você também pode ver algumas variações dessa fórmula na internet. Estes são igualmente falhos, embora adicionem mais fatores que a fórmula original não possui. A verdade é que você não pode colocar um limite superior no consumo de memória do MySQL. Não é um servidor de banco de dados rigidamente regulamentado que controla a alocação de memória.