Risoluzione dei problemi: Potenziale perdita di dati con repliche di gruppi di disponibilità con commit asincrono

Si applica a:SQL Server

Dopo aver eseguito un failover manuale forzato di un gruppo di disponibilità a una replica secondaria con commit asincrono, è possibile che la perdita di dati sia superiore all'obiettivo del punto di ripristino (RPO, Recovery Point Objective). Oppure, quando si calcoli la potenziale perdita di dati di una replica secondaria con commit asincrono utilizzando il metodo descritto in Monitor Performance for Always On Availability Groups, si scopre che supera l'RPO.

Una replica secondaria con commit sincrono garantisce la totale assenza di perdite di dati, ma la perdita di dati potenziale di una replica secondaria con commit asincrono dipende dalla dimensione di log ancora in attesa di finalizzazione nella replica secondaria.

Le sezioni seguenti descrivono le cause comuni della potenzialità elevata delle perdite di dati delle repliche secondarie con commit asincrono, presupponendo che nell'istanza del server non sia presente un problema di prestazioni a livello di sistema non correlato ai gruppi di disponibilità.

  1. Una latenza elevata o una velocità effettiva bassa della rete causa un accumulo di log nella replica primaria

  2. Un collo di bottiglia nell'I/O del disco rallenta la scrittura permanente dei log nella replica secondaria

Una latenza elevata o una velocità effettiva bassa della rete causa un accumulo di log nella replica primaria

La causa più comune del superamento dell'obiettivo RPO da parte dei database è l'impossibilità di inviarli alla replica secondaria con una rapidità sufficiente.

Spiegazione

La replica primaria attiva il controllo di flusso per l'invio dei log quando ha superato il numero massimo consentito di messaggi non riconosciuti inviati alla replica secondaria. Finché alcuni di questi messaggi non vengono confermati, non possono essere inviati altri blocchi di log alla replica secondaria. Poiché è possibile evitare la perdita di dati solo quando i dati sono stati scritti in modo permanente nella replica secondaria, l'accumulo di messaggi di log non inviati aumenta il potenziale di perdita di dati.

Diagnosi e risoluzione

Un numero elevato di messaggi rinviati alla replica secondaria può indicare un'elevata latenza di rete e rumore di rete. È anche possibile confrontare il valore DMV log_send_rate con l'oggetto prestazioni Log Bytes Flushed/sec. Se i log sono vengono scaricati su disco più velocemente di quanto vengano inviati, la perdita di dati potenziale può aumentare in modo indefinito.

È utile controllare i due oggetti prestazioni SQL Server:Availability Replica > Flow Control Time (ms/sec) e SQL Server:Availability Replica > Flow Control/sec. Moltiplicando questi due valori si ottiene quanto tempo, nell'ultimo secondo, è stato trascorso in attesa che il controllo di flusso venisse rimosso. Maggiore il tempo di attesa del controllo di flusso, minore la velocità di invio.

Le metriche seguenti sono utili per diagnosticare la velocità effettiva e la latenza della rete. Per valutare la latenza l'utilizzo della rete, è possibile usare altri strumenti di Windows, ad esempio ping.exe e Network Monitor.

  • DMV sys.dm_hadr_database_replica_states, log_send_queue_size

  • DMV sys.dm_hadr_database_replica_states, log_send_rate

  • Contatore delle prestazioni SQL Server:Database > Log Bytes Flushed/sec

  • Contatore delle prestazioni SQL Server:Database Mirroring > Send/Receive Ack Time

  • Contatore delle prestazioni SQL Server:Availability Replica > Bytes Sent to Replica/sec

  • Contatore delle prestazioni SQL Server:Availability Replica > Bytes Sent to Transport/sec

  • Contatore delle prestazioni SQL Server:Availability Replica > Flow Control Time (ms/sec)

  • Contatore delle prestazioni SQL Server:Availability Replica > Flow Control/sec

  • Contatore delle prestazioni SQL Server:Availability Replica > Resent Messages/sec

Per risolvere questo problema, provare ad aggiornare la larghezza di banda di rete o a rimuovere o ridurre traffico di rete non necessario.

Un collo di bottiglia dell'I/O del disco rallenta la scrittura persistente dei log sulla replica secondaria

A seconda della distribuzione dei file di database, la finalizzazione dei log può rallentare a causa di una contesa I/O con il carico di lavoro di creazione di report.

Spiegazione

La perdita di dati è prevenuta non appena il blocco di log viene reso permanente nel file di log. Di conseguenza, è fondamentale isolare il file di log dal file di dati. Se il file di log e il file di dati sono entrambi mappati allo stesso disco rigido, un carico di lavoro di creazione di report con una frequenza intensiva di operazioni di lettura sul file di dati utilizzerà le stesse risorse di I/O necessarie per l'operazione di finalizzazione del log. Una scrittura lenta del log su disco può tradursi in un invio lento della conferma alla replica primaria, che può causare un intervento eccessivo del controllo di flusso e lunghi tempi di attesa del controllo di flusso.

Diagnosi e risoluzione

Dopo aver verificato che la rete non abbia un problema di latenza elevata o di bassa velocità effettiva, è necessario analizzare la replica secondaria per individuare eventuali contese di I/O. Le query in SQL Server: Riduzione dell'I/O del disco sono utili per identificare le contese. Per comodità, di seguito sono riportati alcuni esempi tratti da questo articolo.

Lo script seguente consente di visualizzare il numero di operazioni di lettura e scrittura in ogni file di dati e di log per ogni database di disponibilità in esecuzione in un'istanza di SQL Server. L'elenco è ordinato per tempo di blocco I/O medio in millisecondi. Si noti che i valori sono cumulativi a partire dall'ultima volta in cui è stata avviata l'istanza del server. È pertanto necessario rilevare la differenza tra due misure dopo un certo periodo di tempo.

SELECT DB_NAME(database_id) AS   
   [Database Name] ,   
   file_id ,   
   io_stall_read_ms ,   
   num_of_reads ,   
   CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1)) AS [avg_read_stall_ms] ,   
   io_stall_write_ms ,   
   num_of_writes ,  
   CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1)) AS [avg_write_stall_ms] ,   
   io_stall_read_ms + io_stall_write_ms AS [io_stalls] ,   
   num_of_reads + num_of_writes AS [total_io] ,   
   CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads  
+ num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms]  
FROM sys.dm_io_virtual_file_stats(NULL, NULL)  
WHERE DB_NAME(database_id) IN (SELECT DISTINCT database_name FROM sys.dm_hadr_database_replica_cluster_states)  
ORDER BY avg_io_stall_ms DESC;

La query successiva fornisce un'istantanea puntuale (non cumulativa) delle richieste di I/O in sospeso sul sistema.

SELECT DB_NAME(mf.database_id) AS [Database] ,   
   mf.physical_name ,  
   r.io_pending ,   
   r.io_pending_ms_ticks ,   
   r.io_type ,   
   fs.num_of_reads ,   
   fs.num_of_writes  
FROM sys.dm_io_pending_io_requests AS r   
INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL) AS fs ON r.io_handle = fs.file_handle   
INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id  
AND fs.file_id = mf.file_id  
ORDER BY r.io_pending , r.io_pending_ms_ticks DESC;

Per identificare una contesa I/O, è possibile confrontare il modo in cui le operazioni I/O rispettivamente di lettura e di scrittura corrispondono le une alle altre.

Di seguito sono riportati alcuni altri contatori delle prestazioni che possono essere utili per diagnosticare i colli di bottiglia dell'I/O:

  • Disco fisici: tutti i contatori

  • Disco fisico: sec/trasferimento medi

  • SQL Server: Database > Tempo di attesa di svuotamento del log

  • SQL Server: Database > Attese di scrittura del log/sec

  • SQL Server: Database > Letture del disco del pool di log/sec

Se si identifica un collo di bottiglia I/O e il file di log e il file di dati si trovano nello stesso disco rigido, la prima cosa da fare è posizionarli in dischi separati. Questa procedura consigliata evita che il carico di lavoro di creazione di report interferisca con il percorso di trasferimento del log dalla replica primaria al buffer dei log e con la finalizzazione della transazione nella replica secondaria.

Contenuti correlati

  • Last updated on