Risoluzione dei problemi relativi all'accodamento dei messaggi di log in un gruppo di disponibilità Always On

Articolo
03/29/2023

Questo articolo fornisce soluzioni ai problemi relativi alla coda di invio dei log.

Che cos'è l'accodamento dei messaggi di log?

Le modifiche apportate a un database del gruppo di disponibilità nella replica primaria , ad INSERTesempio , UPDATEe DELETE, vengono scritte nel log delle transazioni e inviate alle repliche secondarie del gruppo di disponibilità. La coda di invio log definisce il numero di record di log nei file di log del database primario che non sono stati inviati alle repliche secondarie.

Sintomi ed effetto della coda di invio del log

La coda di invio dei log archivia tutti i dati vulnerabili

Se la replica primaria viene persa in caso di emergenza improvvisa e si esegue il failover nella replica secondaria in cui queste modifiche non sono ancora arrivate, tali modifiche non verranno visualizzate nella nuova copia della replica primaria del database. Ciò esclude tutte le modifiche archiviate quando vengono eseguiti backup completi del database e del log.

L'aumento della coda di invio dei log causa una crescita crescente dei file di log delle transazioni

Per un database definito in un gruppo di disponibilità, Microsoft SQL Server deve conservare nella replica primaria tutte le transazioni nel log delle transazioni che non sono ancora state recapitate alle repliche secondarie. La coda di invio del log rappresenta la quantità di modifiche registrate nella replica primaria che non possono essere troncate durante i normali eventi di troncamento del log, ad esempio durante un backup del log del database. Una coda di invio log di grandi dimensioni e in continua crescita può esaurire lo spazio disponibile nell'unità che ospita il file di log del database o può superare le dimensioni massime del file di log delle transazioni configurate. Per altre informazioni, vedere Errore 9002 quando il log delle transazioni è di grandi dimensioni.

Varie funzionalità di diagnostica segnalano l'accodamento del log del gruppo di disponibilità

Il dashboard Always On nel SQL Server Management Studio segnala la coda di invio dei log. Potrebbe segnalare che il gruppo di disponibilità non è integro.

Come controllare la coda di invio dei log

La coda di invio dei log è una misurazione per database. È possibile controllare questo valore usando il dashboard Always On nella replica primaria o usando la sys.dm_hadr_database_replica_states DMV (Dynamic Management Views) nella replica primaria o secondaria. Monitor prestazioni contatori vengono usati per verificare la presenza di code di invio di log nella replica secondaria.

Le sezioni successive forniscono metodi per monitorare attivamente la coda di invio del log del database del gruppo di disponibilità.

Sys.dm_hadr_database_replica_state di query

La sys.dm_hadr_database_replica_states DMV segnala una riga per ogni database del gruppo di disponibilità. Una colonna del report è log_send_queue_size. Questo valore corrisponde alle dimensioni della coda di invio del log in kilobyte (KB). È possibile configurare una query, ad esempio la query seguente, per monitorare qualsiasi tendenza nelle dimensioni della coda di invio del log. La query viene eseguita nella replica primaria. Usa il is_local=0 predicato per segnalare i dati per la replica secondaria, dove log_send_queue_size e log_send_rate sono rilevanti.

WHILE 1=1
BEGIN
  SELECT drcs.database_name, ars.role_desc, drs.log_send_queue_size, drs.log_send_rate,
ars.recovery_health_desc, ars.connected_state_desc, ars.operational_state_desc, ars.synchronization_health_desc, *
  FROM sys.dm_hadr_availability_replica_states ars JOIN sys.dm_hadr_database_replica_cluster_states drcs ON ars.replica_id=drcs.replica_id
  JOIN sys.dm_hadr_database_replica_states drs ON drcs.group_database_id=drs.group_database_id
  WHERE ars.role_desc='SECONDARY' AND drs.is_local=0
  waitfor delay '00:00:30'
END

Ecco l'aspetto dell'output.

Esaminare la coda di invio dei log nel dashboard di Always On

Per esaminare la coda di invio dei log, seguire questa procedura:

Aprire il dashboard Always On in SQL Server Management Studio (SSMS) facendo clic con il pulsante destro del mouse su un gruppo di disponibilità in SSMS Esplora oggetti.
Selezionare Mostra dashboard.

I database del gruppo di disponibilità sono elencati per ultimi e sono presenti alcuni dati segnalati nei database. Anche se le dimensioni della coda di invio log (KB) e la frequenza di invio dei log (KB/sec) non sono elencate per impostazione predefinita, è possibile aggiungerle a questa visualizzazione, come illustrato nello screenshot nel passaggio successivo.
Per aggiungere queste colonne, fare clic con il pulsante destro del mouse sull'intestazione di colonna del database del gruppo di disponibilità e scegliere dall'elenco delle colonne disponibili.
Per aggiungere le dimensioni della coda di invio log, fare clic con il pulsante destro del mouse sull'intestazione visualizzata come evidenziato in rosso nello screenshot seguente.

Per impostazione predefinita, il dashboard Always On aggiorna automaticamente questi dati ogni 60 secondi.

Esaminare la coda di invio log in Monitor prestazioni

La coda di invio dei log è specifica per ogni database di replica secondaria. Pertanto, per esaminare la coda di invio dei log di un database del gruppo di disponibilità, seguire questa procedura:

Aprire Monitor prestazioni nella replica secondaria.
Selezionare il pulsante Aggiungi (contatore).
In Contatori disponibili selezionare i contatoriSQLServer:Replica database e Coda di invio log .
Nella casella di riepilogo Istanza selezionare il database del gruppo di disponibilità da controllare per la coda di invio dei log.
Selezionare Aggiungi e OK.

Ecco come potrebbe apparire l'aumento della coda di invio dei log.

Interpretazione dei valori di accodamento per l'invio del log

In questa sezione viene illustrato come interpretare i valori delle dimensioni della coda di invio del log.

Quando l'accodamento dei messaggi di log non è valido? Quanta accodamento di invio log deve essere tollerata?

Si supponga che se la coda di invio log segnala un valore pari a 0, significa che al momento del report non si verifica alcuna accodamento di invio log. Tuttavia, quando l'ambiente di produzione è occupato, è consigliabile osservare che la coda di invio dei log segnala spesso un valore diverso da zero anche in un ambiente AlwaysOn integro. Durante la produzione tipica, è consigliabile osservare che questo valore varia tra 0 e un valore diverso da zero.

Se si osserva un aumento dell'accodamento delle invii di log nel tempo, è necessaria un'ulteriore indagine. Questa attività aggiuntiva indica che è stato modificato qualcosa. Se si osserva una crescita improvvisa nella coda di invio del log, le misurazioni seguenti sono utili per la risoluzione dei problemi:

Frequenza di invio log (KB/sec) (dashboard AlwaysOn)
sys.dm_hadr_database_replica_states (DMV)
Replica database::Transazioni con mirroring/sec (Monitor prestazioni)

Ottenere le tariffe di base per la frequenza di invio dei log e le transazioni con mirroring/sec

Durante le prestazioni AlwaysOn integre, monitorare la frequenza di invio dei log e i valori delle transazioni con mirroring/sec per i database del gruppo di disponibilità occupato. Che aspetto hanno durante gli orari di ufficio in genere occupati? Che aspetto hanno durante i periodi di manutenzione, quando le transazioni di grandi dimensioni determinano una maggiore velocità effettiva delle transazioni nel sistema? È possibile confrontare questi valori quando si osserva l'aumento della coda di invio del log per determinare le modifiche apportate. Il carico di lavoro potrebbe essere maggiore del solito. Se la frequenza di invio del log è inferiore al solito, potrebbe essere necessaria un'ulteriore indagine per determinare il motivo.

I volumi del carico di lavoro sono importanti

Quando si dispone di carichi di lavoro di grandi dimensioni, ad esempio un'istruzione UPDATE su 1 milione di righe, una ricompilazione dell'indice in una tabella da 1 terabyte o anche un batch ETL che inserisce milioni di righe, si dovrebbe prevedere un aumento della coda di invio dei log, immediatamente o nel tempo. Ciò è previsto quando un numero elevato di modifiche viene apportato improvvisamente nel database del gruppo di disponibilità.

Come diagnosticare l'accodamento dei messaggi di log

Dopo aver identificato l'accodamento dei messaggi di log per un database del gruppo di disponibilità specifico, è necessario verificare la presenza di diverse possibili cause radice del problema, come illustrato nelle sezioni seguenti.

Importante

Per ottenere un output significativo del tipo di attesa, verificare la presenza di un aumento nella coda di invio del log usando uno dei metodi descritti nelle sezioni precedenti quando si monitorano le condizioni seguenti.

Il sistema è troppo occupato

Controllare se il carico di lavoro nella replica primaria esegue l'overload delle CPU del sistema. Se viene visualizzato un aumento della coda di invio dei log, eseguire una query sulla sys.dm_os_schedulers DMV e monitorare .high runnable_tasks_count Questo conteggio indica le attività in sospeso eseguite in quel momento.

SELECT scheduler_address, scheduler_id, cpu_id, status, current_tasks_count, runnable_tasks_count, current_workers_count, active_workers_count
FROM sys.dm_os_schedulers

La tabella seguente è un esempio di risultati. Un aumento del runnable_tasks_count valore indica che un numero elevato di attività è in attesa di tempo cpu.

scheduler_address	scheduler_id	cpu_id	stato	current_tasks_count	runnable_tasks_count	current_workers_count	active_workers_count
0x000002778D 200040	0	0	VISIBILE OFFLINE	1	0	2	1
0x000002778D 220040	1	1	VISIBILE ONLINE	108	12	115	107
0x000002778D 240040	2	2	VISIBILE ONLINE	113	2	123	113
0x000002778D 260040	3	3	VISIBILE ONLINE	105	11	116	105
0x000002778D 480040	4	4	VISIBILE ONLINE	108	15	117	108
0x000002778D 4A0040	5	5	VISIBILE ONLINE	100	25	110	99
0x000002778D 4C0040	6	6	VISIBILE ONLINE	105	23	113	105
0x000002778D 4E0040	7	7	VISIBILE	109	25	116	109
0x000002778D 700040	8	8	VISIBILE ONLINE	98	10	112	98
0x000002778D 720040	9	9	VISIBILE ONLINE	114	1	130	114
0x000002778D 740040	10	10	VISIBILE ONLINE	110	25	120	110
0x000002778D 760040	11	11	VISIBILE ONLINE	83	8	93	83
0x000002778D A00040	12	12	VISIBILE ONLINE	104	4	117	104
0x000002778D A20040	13	13	VISIBILE ONLINE	108	32	118	108
0x000002778D A40040	14	14	VISIBILE ONLINE	102	12	113	102
0x000002778D A60040	15	15	VISIBILE ONLINE	104	16	116	103

Soluzione: se si rileva un valore elevato runnable_task_count, ridurre il carico di lavoro nel sistema o aumentare il numero di CPU disponibili per il sistema.

Latenza di rete

Questa condizione è particolarmente comune se la replica secondaria è fisicamente remota dalla replica primaria. I gruppi di disponibilità multisito consentono ai clienti di distribuire copie dei dati aziendali in più siti per il ripristino di emergenza e la creazione di report. In questo modo, le modifiche quasi in tempo reale sono disponibili per le copie dei dati di produzione in posizioni remote.

Se una replica secondaria è ospitata lontano dalla replica primaria, l'accodamento dell'invio dei log può essere causato dalla latenza di rete e dall'impossibilità di inviare modifiche al database secondario remoto con la stessa velocità con cui vengono prodotte nel database di replica primaria.

Importante

SQL Server usa una singola connessione per sincronizzare le modifiche dalle repliche primarie alle repliche secondarie. Pertanto, se una replica secondaria è remota, la larghezza della pipe non influirà sulla quantità di dati che SQL Server possono inviare. Questa quantità dipende invece più dalla latenza di rete nella pipe (velocità di connessione).

Testare la latenza di rete

Verificare se le impostazioni di controllo del flusso contribuiscono alla latenza di rete

I gruppi di disponibilità di Microsoft SQL Server usano i controlli di flusso per evitare un consumo eccessivo di risorse di rete, memoria e altre risorse in tutte le repliche di disponibilità. Questi controlli di flusso non influiscono sullo stato di integrità della sincronizzazione delle repliche di disponibilità. Tuttavia, possono influire sulle prestazioni complessive dei database di disponibilità, incluso RPO.

Le versioni successive di SQL Server modificare le soglie in corrispondenza delle quali viene immesso il controllo di flusso. Ciò consente di alleviare l'effetto del controllo del flusso sui sintomi, ad esempio l'accodamento dei messaggi di log. Per altre informazioni sul controllo del flusso e sulla cronologia delle modifiche alle soglie di controllo del flusso, vedere Controlli di flusso.

È possibile monitorare il controllo del flusso usando Monitor prestazioni per acquisire i dati nella replica primaria. Per monitorare il controllo del flusso di database, aggiungere i contatori SQLServer:Replica di database e selezionare i contatori Ritardo controllo flusso di database e Controlli flusso di database/sec . Nella finestra di dialogo Istanza selezionare il database del gruppo di disponibilità da controllare per il controllo del flusso di database. Per rilevare e monitorare il controllo del flusso della replica di disponibilità, aggiungere i contatori SQLServer:Replica di disponibilità e selezionare i contatori Tempo controllo flusso (ms/sec) e Controllo di flusso/sec .
Controllare se il riavvio di Windows congestione contribuisce alla latenza di rete

I problemi di prestazioni di rete che causano l'accodamento degli invii di log possono essere attivati impostando l'impostazione TCP Riavvia windows congestione su True. Si tratta dell'impostazione predefinita in Windows Server 2016. Verificare che Il riavvio della finestra congestione sia impostato su False nei server Windows che ospitano le repliche del gruppo di disponibilità in cui viene osservato l'accodamento dell'invio dei log.

PS C:\WINDOWS\system32> Get-NetTCPSetting | Select SettingName, CwndRestart

Per altre informazioni su come impostare la proprietà TCP Congestion Windows Restart su False, vedere Set-NetTCPSetting (NetTCPIP).For more information about how to set the TCP Congestion Windows Restart property to False, see Set-NetTCPSetting (NetTCPIP).

Per informazioni sul processo di sincronizzazione, vedere anche Monitorare le prestazioni per i gruppi di disponibilità Always On. Questo articolo illustra anche come calcolare alcune delle metriche chiave e fornisce collegamenti ad alcuni degli scenari comuni di risoluzione dei problemi delle prestazioni.

Usare ping per ottenere un esempio di latenza

In una riga di comando in node1 (replica primaria), ping node2 (replica secondaria):

C:\Users\customer>ping node2
Pinging node2.customer.corp.company.com [<ip address>] with 32 bytes of data:
Reply from 2001:4898:4018:3005:25f3:d931:2507:e353: time=94ms
Reply from 2001:4898:4018:3005:25f3:d931:2507:e353: time=97ms
Reply from 2001:4898:4018:3005:25f3:d931:2507:e353: time=94ms
Reply from 2001:4898:4018:3005:25f3:d931:2507:e353: time=119ms

Ping statistics for 2<ip address>:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 94ms, Maximum = 119ms, Average = 101ms

Testare la velocità effettiva di rete da primaria a secondaria usando uno strumento indipendente

Usare uno strumento come NTttcp per rilevare in modo indipendente la velocità effettiva di rete tra le repliche primarie e secondarie usando una singola connessione. La latenza di rete è una causa comune per l'accodamento dell'invio di log. La procedura seguente illustra come usare uno strumento indipendente, ad esempio NTttcp, per misurare la velocità effettiva di rete.

Importante

SQL Server invia le modifiche dalla replica primaria alla replica secondaria usando una singola connessione. Nella sezione seguente viene configurato ed eseguito NTttcp per l'uso di una singola connessione (allo stesso modo di SQL Server) per confrontare accuratamente la velocità effettiva.

È possibile scaricare NTttcp da Github - microsoft/ntttcp.

Per eseguire NTttcp, seguire questa procedura:
1. Scaricare e copiare lo strumento nei server primari e secondari basati su SQL Server.
2. Nel server di replica secondario aprire una finestra del prompt dei comandi con privilegi elevati, impostare la directory sulla cartella dello strumento NTttcp e quindi eseguire il comando seguente:
  
  ntttcp.exe -r -m 1,0,<secondaryipaddress>-a 16 -t 60
  
  Nota
  
  In questo comando è <secondaryipaddress> un segnaposto per l'indirizzo IP effettivo del server di replica secondario.
3. Nel server di replica primario aprire una finestra del prompt dei comandi con privilegi elevati, impostare la directory sulla cartella dello strumento NTttcp e quindi eseguire il comando seguente specificando di nuovo l'indirizzo IP effettivo del server di replica secondario:
  
  ntttcp.exe -s -m 1,0,<secondaryipaddress>-a 16 -t 60
  
  Gli screenshot seguenti mostrano NTttcp in esecuzione nelle repliche secondarie e primarie. A causa della latenza di rete, lo strumento può inviare solo 739 KB/sec di dati. Questo è ciò che ci si può aspettare SQL Server essere in grado di inviare.
  
  NTttcp nella replica secondaria
  
  NTttcp nella replica primaria

Esaminare i contatori Monitor prestazioni

Verificare i report di NTttcp. Una transazione di grandi dimensioni viene eseguita in SQL Server nella replica primaria. Dopo aver avviato Monitor prestazioni nella replica primaria, aggiungere il contatore Interfaccia di rete::Byte inviati/sec. Questo contatore conferma che la replica primaria può inviare circa 777 KB/sec di dati. Questo valore è simile al valore di 739 KB/sec riportato dal test NTttcp.

È anche utile confrontare il valore SQL Server::D atabases::Log Bytes Flushed/sec nella replica primaria con SQL Server::D atabase Replica::Log Bytes Received/sec per lo stesso database nella replica secondaria. In media, si osservano circa 20 MB/sec di modifiche create nel database "agdb". Tuttavia, la replica secondaria riceve, in media, solo 5,4 MB di modifiche. Ciò causerà l'accodamento dell'invio di log nella replica primaria delle modifiche in sospeso nel log delle transazioni del database che non sono ancora state inviate alla replica secondaria.

Byte del log di replica primaria scaricati/sec per il database "agdb"

Byte del log della replica secondaria ricevuti/sec per il database agdb

Condividi tramite