All perguntas(dba)

Tenho um Cluster Galera (v3) de produção com 3 nós MySQL (v5.7) rodando em modo multi-master. Preciso criar um novo índice (~16 GB) e soltar um índice antigo em uma tabela grande (112 milhões de linhas, ~20 GB de tamanho).

Como o Galera sincroniza instruções DDL, temo que criar um índice grande (~16 GB) possa paralisar a replicação ou causar um SST (State Snapshot Transfer). Planejo executar isso durante uma janela de manutenção, mas quero garantir a segurança da replicação.

Qual é a maneira mais segura de criar esse índice sem afetar o cluster?

Operações a serem realizadas:

1. Crie um novo índice:

   CREATE INDEX index5 ON table_name (c3, c4);

2. Remova um índice existente (depois de garantir que o novo índice seja usado):

   DROP INDEX index4 ON table_name;

Estrutura da tabela (simplificada):

CREATE TABLE table_name (
  c1 char(36) NOT NULL,
  c2 char(36) NOT NULL,
  c3 varchar(100) DEFAULT NULL,
  c4 char(36) DEFAULT NULL,
  c5 longtext,
  c6 datetime DEFAULT NULL,
  c7 tinyint(1) DEFAULT '0',
  PRIMARY KEY (c1),
  KEY index1 (c3, c4),
  KEY index2 (c2, c4, c3),
  KEY index3 (c4, c3),
  KEY index4 (c4, c3, c2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Existe algum procedimento para fazer o espelhamento do MSSQL com um único mestre e vários escravos?

dois escravos X um mestre

Ou este.

Um Mestre X Um Escravo somente para visualização

Preciso de um servidor MS SQL somente para visualização que não afete o mestre. Para evitar bloqueio de linha. Atualmente, estou usando o MSSQL Server 2017

Tentando atualizar uma instância do PostgreSQL 13 para a versão 16, estou com problemas. Antes disso, eu atualizava (no local) sem problemas da versão 11 para a 13, mas agora instalar a 16 causa problemas.

Eu faço:

sudo apt install postgresql-16

e esperaria subsequentemente encontrar arquivos de configuração em /etc/postgresql/16, mas não há tal pasta. Além disso, pg_lsclustersretorne apenas a versão 13. Eu posso fazer sudo -u postgres /lib/postgresql/16/bin/initdb -D /var/lib/postgresql/16/main, o que funcionará, mas então não aparece nenhum serviço (como em ) e também o cluster ainda não aparece na lista.systemctl status [email protected]

Não consigo encontrar nenhuma pista sobre o que pode ser a causa ou como proceder. Fiz uma apt purgeinstalação seguida de uma nova várias vezes, mas sem sucesso. Além disso, tenho a mesma experiência ao instalar o PG 15. Quaisquer ideias são bem-vindas, obrigado!

Para referência, esta é a saída da instalação:

user@server:/etc/postgresql$ sudo apt install postgresql-16
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
  postgresql-client-16
Suggested packages:
  postgresql-doc-16
The following NEW packages will be installed:
  postgresql-16 postgresql-client-16
0 upgraded, 2 newly installed, 0 to remove and 304 not upgraded.
Need to get 20.1 MB of archives.
After this operation, 69.5 MB of additional disk space will be used.
Do you want to continue? [Y/n] Y
Get:1 https://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 postgresql-client-16 amd64 16.8-1.pgdg22.04+1 [1913 kB]
Get:2 https://apt.postgresql.org/pub/repos/apt jammy-pgdg/main amd64 postgresql-16 amd64 16.8-1.pgdg22.04+1 [18.1 MB]
Fetched 20.1 MB in 2s (9535 kB/s)
Preconfiguring packages ...
Selecting previously unselected package postgresql-client-16.
(Reading database ... 202138 files and directories currently installed.)
Preparing to unpack .../postgresql-client-16_16.8-1.pgdg22.04+1_amd64.deb ...
Unpacking postgresql-client-16 (16.8-1.pgdg22.04+1) ...
Selecting previously unselected package postgresql-16.
Preparing to unpack .../postgresql-16_16.8-1.pgdg22.04+1_amd64.deb ...
Unpacking postgresql-16 (16.8-1.pgdg22.04+1) ...
Setting up postgresql-client-16 (16.8-1.pgdg22.04+1) ...
update-alternatives: using /usr/share/postgresql/16/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode
Setting up postgresql-16 (16.8-1.pgdg22.04+1) ...
Processing triggers for postgresql-common (274.pgdg22.04+1) ...
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Removing obsolete dictionary files:
user@server:/etc/postgresql$ ls
13
user@server:/etc/postgresql$

Editar: Espero que este seja o lugar certo para esta questão/problema. Se não, por favor, comente. Coloquei no stackexchange e ele foi fechado em minutos sem nenhum comentário. Estou feliz com qualquer contribuição, também no meu post!

Consultar o número de bloqueios mantidos me dá um número na casa das centenas:

SELECT count(*) from
SYS.dm_tran_locks

Mas consultar o contador de desempenho 'Bloqueios de bloqueio' me dá um número na região de 45.000.

SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [counter_name] in ('Lock Blocks', 'Lock Blocks Allocated', 'Lock Memory (KB)', 'Lock Owner Blocks')

Os blocos de bloqueio são definidos pela Microsoft como:

Especifica o número atual de blocos de bloqueio em uso no servidor (atualizados periodicamente). Um bloco de bloqueio representa um recurso bloqueado individual, como uma tabela, página ou linha.

Então eu esperaria que os números fossem os mesmos, ou pelo menos na mesma faixa. Meu entendimento está obviamente faltando em algum lugar, então estou tentando entender isso melhor. Qualquer ajuda seria apreciada.

Suponha que eu tenha um banco de dados Enterprise Edition que tenha configurações completamente padrão e nada além de tabelas rowstore normais. Sem RCSI, sem isolamento de snapshot e sem gatilhos. Suponha que nada exija versionamento de linha .

Claramente, essas suposições significam que não pagarei o custo extra de 14 bytes por linha do controle de versão de linha. No entanto, os secundários AG legíveis são executados sob isolamento SNAPSHOT e o isolamento SNAPSHOT requer controle de versão de linha. Então, se eu colocar meu banco de dados em um Availability Group e adicionar um secundário legível, quais cópias do meu banco de dados (se houver) começarão a pagar o custo de 14 bytes por linha do controle de versão de linha? Isso muda se eu tiver um secundário não legível além do secundário legível? E se eu fizer failover e failback?

A documentação relevante, vinculada anteriormente, não faz comentários sobre isso. Eu mesmo planejo executar experimentos, mas AGs são notoriamente fáceis de cometer erros e não quero tirar conclusões incorretas.

Se eu tiver a seguinte consulta no banco de dados Stack Overflow2010

UPDATE  dbo.Posts
SET     Title =
        CASE 
            WHEN CreationDate <= '2008-01-01T00:00:00'
            THEN 'A'
            ELSE 'B'
        END
FROM    dbo.Posts

A saída resumida STATISTICS IO está abaixo

Table 'Posts'. Scan count 1, logical reads 445699, physical reads 375822, page server reads 0, read-ahead reads 445521, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(1878895 rows affected)

e o plano de execução está aqui

Se eu criar uma tabela para armazenar o valor que desejo usar na minha comparação:

CREATE TABLE dbo.Canary
(
    TheDate DATETIME
)

INSERT INTO dbo.Canary VALUES ('2008-01-01T00:00:00')

e então se eu alterar a consulta da seguinte forma:

UPDATE  dbo.Posts
SET     Title =
        CASE 
            WHEN CreationDate <= Canary.TheDate
            THEN 'A'
            ELSE 'B'
        END
FROM    dbo.Posts
        CROSS JOIN dbo.Canary

A saída STATISTICS IO é

Table 'Canary'. Scan count 1, logical reads 1, physical reads 1, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Posts'. Scan count 1, logical reads 16787757, physical reads 3127, page server reads 0, read-ahead reads 784795, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 6291, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(1878895 rows affected)

e o plano de execução está aqui

Podemos ver que o número de leituras lógicas aumentou massivamente, de 445k para 16m. Levei algum tempo para encontrar no plano de execução onde está a fonte disso, mas eu o rastreei usando a propriedade Estatísticas de E/S reais / Leituras lógicas reais e posso ver que as leituras extras estão no operador de atualização de índice clusterizado, também posso ver que esse operador agora tem um valor para Número real de linhas para todas as execuções, enquanto o plano para a primeira consulta não tem.

O que está acontecendo aqui? O que está acontecendo na atualização do índice clusterizado que está causando o aumento nas leituras?

Acredito que a consulta deve usar uma variável se esse padrão de "valor em uma tabela de configuração" for usado. No entanto, esta é uma consulta de um aplicativo de fornecedor, então quero dar um feedback sobre o que está acontecendo como resultado da consulta ter sido escrita da maneira que foi.

Tenho um cluster de servidores de BD Primário->Primário executando o MariaDB 11.4.5. O SO neles foi atualizado do Ubuntu 22.04 para o 24.04 ontem, pois parecia haver algum tipo de vazamento de memória que ocasionalmente fazia com que o OOM Killer matasse o banco de dados e isso parecia uma coisa lógica a se tentar. A versão do BD permaneceu a mesma em todas as atualizações do SO.

Infelizmente, desde então, o aplicativo está consumindo toda a memória disponível no servidor até que o reiniciemos ou ele seja eliminado pelo OOM killer.

Os servidores tinham 40 GB de RAM disponíveis e 11 GB de swap. Usando este SQL (encontrado em outro thread do SE DB

SELECT ROUND(
( @@GLOBAL.key_buffer_size                     
 + @@GLOBAL.query_cache_size 
 + @@GLOBAL.tmp_table_size 
 + @@GLOBAL.innodb_buffer_pool_size 
 + @@GLOBAL.innodb_log_buffer_size 
 + @@GLOBAL.max_connections * ( 
     @@GLOBAL.sort_buffer_size
   + @@GLOBAL.read_buffer_size 
   + @@GLOBAL.read_rnd_buffer_size 
   + @@GLOBAL.join_buffer_size 
   + @@GLOBAL.thread_stack 
   + @@GLOBAL.binlog_cache_size)
) / 1024 / 1024, 1) `total MB`;

A quantidade máxima de memória que deve ser usada é 15 GB. Estou usando btop para assistir os servidores e MemB está atualmente em 35 GB (e subindo)

Voltei ao artigo da KB aqui - https://mariadb.com/kb/en/mariadb-memory-allocation/ e atualizei estas configurações:

query_cache_size= 0 query_cache_type = DESLIGADO key_buffer_size = 10M

e defina o seguinte no servidor Defina swappiness para 5 Desativado Numa

Também alterei as configurações do systemd para o serviço mariadb para ter:

LimitNOFILE=100000
LimitMEMLOCK=524288

# Set maximum allowed memory
MemoryMax=26GB
MemorySwapMax=8GB

OOMScoreAdjust=-600

BlockIOWeight=1000

TasksMax=99%

Nice=-5

Environment=LD_PRELOAD=/usr/lib/x86_64-linux-gnu/libjemalloc.so.2

A saída das variáveis ​​de exibição está disponível aqui - https://docs.google.com/spreadsheets/d/1ev9KRWP8l54FpRrhFeX4uxFhJnOlFkV4_vTCZWipcXA/edit?usp=sharing

O que mais pode estar causando isso?

Notei nos logs de erros que, mesmo que o ouvinte de eventos de pressão de memória do InnoDB seja iniciado, ele nunca é chamado até que a memória do servidor esteja prestes a acabar.

Também aumentei a RAM do servidor para 50 GB, mas isso é mais um emplastro do que uma solução, para me dar tempo de investigar mais a fundo.

Obrigado Derick

Editar - o link agora também tem saída de show global status; e show engine innodb status;

Estamos no processo de atualização de nossa rede MariaDB 10.1 Master-Slave para MariaDB 10.11. Estamos conduzindo-os em 2 estágios - atualizar todos os escravos para v10.11 e, em um dia posterior, introduzir um novo mestre com v10.11.

O aplicativo é totalmente compatível com a nova versão e não há problemas. Eu estava configurando novos escravos de produção (v10.11) na mesma rede, com o mesmo tipo de instância Linode (50 vcpu, 128 GB de RAM) da produção. O tamanho do banco de dados é de aproximadamente 500 GB.

Novas instâncias v10.11 têm as mesmas configurações do InnoDB que os outros escravos v10.1. innodb_buffer_pool_size=100G para todas as instâncias. Ao iniciar a replicação, o lag continua crescendo nessas instâncias. Percebi que a CPU delas está abaixo de 50% com consultas/segundo menores que 4k. Quando geralmente há um lag na versão mais antiga (v10.1), sua CPU geralmente está acima de 120% com consultas/segundo acima de 15k e ela alcançará o mestre em poucos minutos.

seconds_behind_master na v10.11 se tornou quase 3-4 dias. Em um dos novos escravos, aumentei o slave_parallel_thread=12 e isso melhorou. Sua CPU chegou a cerca de 300% com consultas / segundo com média de cerca de 20k. Mas a outra v10.11 com slave_parallel_therads=0, ainda está alguns dias atrás do Master. Não está utilizando totalmente a CPU - apenas 40-50% da CPU. As consultas por segundo estão abaixo de 4k. Os escravos MariaDB v10.1 estão sendo executados com slave_parallel_threads=0 e nunca experimentei esse longo atraso com eles. Como eu disse, o atraso geralmente se instala em poucos minutos.

Tenho tentado ajustar algumas configurações, reiniciar os escravos algumas vezes, reconstruir os escravos, mas sem sucesso.

Algumas perguntas/preocupações -

1. Por que o MariaDB 10.1 tem melhor desempenho na replicação com melhor eficiência do que o novo MariaDB 10.11. v10.1 - não tem slave_parallel_threads habilitado
2. A utilização da CPU é muito menor na nova versão 10.11 do que na versão 10.1 com melhores consultas / segundo

Alguém já passou por isso antes?

Todos os escravos (v10.1 e v10.11) têm essas configurações -

wait_timeout = 60
interactive_timeout = 60

max_connections = 1000

innodb_buffer_pool_size = 100G
max_allowed_packet = 1G

innodb_strict_mode=OFF
sql_mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

innodb_data_file_path=ibdata1:500M:autoextend

read_rnd_buffer_size = 256M
sort_buffer_size = 256M
join_buffer_size = 256M

tmp_table_size= 1536M
max_heap_table_size= 1536M

skip_slave_start
slave-type-conversions=ALL_NON_LOSSY

read_buffer_size = 20M
#innodb_additional_mem_pool_size = 256M

innodb_log_file_size = 1G
innodb_buffer_pool_instances = 40
skip_name_resolve = 1

tmp_table_size= 1536M
max_heap_table_size= 1536M

server_id=114
#slave-type-conversions=ALL_NON_LOSSY

max_connect_errors = 1000000
log_warnings = 2

####
read_buffer_size = 20M
#innodb_additional_mem_pool_size = 256M

query_cache_type=0

Gostaria de saber qual seria a melhor prática na criação de índices para esses tipos de consultas:

select count(*) from my_table where high_granularity_column = $1 and low_granularity_column in ($2, $3)

Espera low_granularity_column-se que tenha entre 5 e 15 valores distintos e nem sempre tenha a mesma condição de pesquisa, portanto, usá-la para uma condição de índice parcial não é uma opção. Não se espera que a coluna seja usada para classificação e geralmente é um texttipo de dado de até 10 caracteres (por exemplo, uma coluna "status").

O objetivo é que o BD execute uma varredura somente de índice sempre que possível. Eu testei ambos os índices e eles têm desempenho similar:

create index idx_composite on my_table(high_granularity_column, low_granularity_column);

create index idx_with_include on my_table(high_granularity_column) include (low_granularity_column);

Minha pergunta é: qual é a melhor abordagem em termos de manutenção de índice para operações de inserção e atualização?

Além disso, estou esquecendo de algo em termos de desempenho de pesquisa em que um índice é melhor que o outro para conjuntos de dados maiores (por exemplo, 1 milhão de linhas em my_table)?

O MySQL se recusa a iniciar e eu só consegui iniciar em modo somente leitura usando innodb_force_recovery = 6. Todas as respostas que encontrei sobre esse tópico sugerem fazer um backup, deletar ibdata e ib_logfiles e então deixá-los serem recriados:

• https://serverfault.com/questions/592793/mysql-crashed-and-wont-start-up
• innodb_force_recovery quando corrupção do InnoDB
• https://stackoverflow.com/questions/41997197/mysql-data-recovery-with-innodb

No meu caso, como innodb-file-per-table está habilitado, estou pensando se também preciso excluir arquivos como /var/lib/mysql/MY_DATABASE/table.ibd?este. Além disso, como o registro de refazer está desabilitado, não há nenhum arquivo de log para remover.

O erro específico que recebi foi este:

[InnoDB] O servidor foi encerrado quando o redo logging do InnoDB foi desabilitado. Os arquivos de dados podem estar corrompidos. Você pode tentar reiniciar o banco de dados com innodb_force_recovery=6

Neste ponto, consegui iniciar no modo somente leitura e fiz um backup de todas as tabelas que preciso. O que especificamente devo excluir? Ou há uma abordagem melhor para reconstruir completamente essas tabelas?

Estou pensando em simplesmente desinstalar o MySQL completamente e limpar o diretório inteiro, mas estou esperando feedback aqui se houver uma abordagem mais simples.