Aspetta! Aspetta! Davvero, non è l'ennesimo articolo sui tipi di backup di SQL Server. Non parlerò neanche delle differenze tra i modelli di ripristino e di come affrontare un «log» gonfiato.
Forse (solo forse), dopo aver letto questo post, sarete in grado di far sì che il backup, eseguito con i vostri strumenti standard, venga svolto, beh, 1,5 volte più veloce domani notte. E solo grazie al fatto che utilizzate un po' più di parametri nel BACKUP DATABASE.
Se per voi il contenuto del post era ovvio — mi dispiace. Ho letto tutto ciò che ho potuto trovare su Google con la frase «habr sql server backup», e in nessun articolo ho trovato menzione che si possa influenzare il tempo di backup in qualche modo tramite i parametri.
Vorrei subito attirare la vostra attenzione sul commento di Aleksandr Gladchenko ():
Non cambiate mai i parametri BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE in produzione. Sono stati creati solo per scrivere articoli simili. Nella pratica, avrete problemi di memoria.
Sarebbe stato, ovviamente, fantastico, trovarsi il più intelligente e pubblicare contenuti esclusivi, ma purtroppo non è così. Ci sono articoli/post sia in inglese che in russo (mi confondo sempre su come chiamarli correttamente) dedicati a questo tema. Ecco parte di quelli che mi sono capitati: , , .
Quindi, per cominciare, allego una sintassi BACKUP leggermente ridotta da (a proposito, sopra ho scritto riguardo a BACKUP DATABASE, ma tutto ciò è applicabile anche al backup del log delle transazioni e al backup differenziale, ma probabilmente con un effetto meno evidente):
BACKUP DATABASE { database_name | @database_name_var }
TO [ ,...n ]
[ WITH {
| [ ,...n ] } ]
[;]
[ ,...n ]::=
--Opzioni del Media Set
| BLOCKSIZE = { blocksize | @blocksize_variable }
--Opzioni di Trasferimento Dati
BUFFERCOUNT = { buffercount | @buffercount_variable }
| MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }— significa che c'era qualcosa, ma l'ho rimosso perché attualmente non è pertinente all'argomento.
Come eseguite di solito il backup? Come «insegnano» a fare il backup in miliardi di articoli? In generale, se dovessi eseguire una volta il backup di un database non molto grande, scriverebbe automaticamente qualcosa del genere:
BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--va bene, ho scritto CHECKSUM solo per sembrare più intelligenteE, in generale, qui sono elencati, credo, il 75-90% di tutti i parametri che di solito vengono menzionati negli articoli sui backup. Beh, c'è anche INIT, SKIP. E voi siete andati su MSDN? Avete visto che ci sono opzioni che occupano un schermo e mezzo? Anche io l'ho visto...
Probabilmente avete già capito che parleremo ora di tre parametri che sono rimasti nel primo blocco di codice — BLOCKSIZE, BUFFERCOUNT e MAXTRANSFERSIZE. Ecco le loro descrizioni da MSDN:
BLOCKSIZE = { blocksize | @ blocksize_variable } indica la dimensione del blocco fisico in byte. Sono supportate dimensioni di 512, 1024, 2048, 4096, 8192, 16 384, 32 768 e 65 536 byte (64 KB). Il valore predefinito è 65 536 per i dispositivi a nastro e 512 per altri dispositivi. Di solito non è necessario specificare questo parametro, poiché l'istruzione BACKUP sceglie automaticamente la dimensione del blocco corrispondente al dispositivo. Impostare esplicitamente la dimensione del blocco sovrascrive la selezione automatica della dimensione del blocco.
BUFFERCOUNT = { buffercount | @ buffercount_variable } definisce il numero totale di buffer di input/output che verranno utilizzati per l'operazione di backup. È possibile specificare qualsiasi valore intero positivo, tuttavia un numero elevato di buffer può causare un errore di memoria insufficiente a causa di uno spazio di indirizzamento virtuale eccessivo nel processo Sqlservr.exe.
La quantità totale di spazio utilizzato dai buffer è determinata dalla seguente formula:
BUFFERCOUNT * MAXTRANSFERSIZE.
MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } indica la massima dimensione del pacchetto dati in byte per lo scambio di dati tra SQL Server e il supporto del backup. Sono supportati valori multipli di 65 536 byte (64 KB), fino a un massimo di 4 194 304 byte (4 MB).
Giuro — l'ho già letto prima, ma non avevo mai pensato a quale impatto avessero sulle prestazioni. Inoltre, evidentemente, devo fare una sorta di "coming out" e riconoscere che anche ora non capisco completamente cosa facciano. Probabilmente dovrei leggere di più su input/output buffer e l'interazione con i dischi rigidi. Un giorno lo farò, ma per ora posso semplicemente scrivere uno script che verifichi come questi valori influenzano la velocità con cui viene effettuato il backup.
Ho creato un piccolo database, di circa 10 GB, l'ho posizionato su un SSD e ho posizionato la directory per i backup su un HDD.
Creo una tabella temporanea per memorizzare i risultati (per me non è temporanea, così posso esaminare i risultati più in dettaglio, ma decidete voi):
DROP TABLE IF EXISTS ##bt_results;
CREATE TABLE ##bt_results (
id int IDENTITY (1, 1) PRIMARY KEY,
start_date datetime NOT NULL,
finish_date datetime NOT NULL,
backup_size bigint NOT NULL,
compressed_size bigint,
block_size int,
buffer_count int,
transfer_size int
);Il principio di funzionamento dello script è semplice: cicli annidati, ognuno dei quali modifica il valore di un parametro, passando questi parametri al comando BACKUP e salvando l'ultima registrazione della cronologia da msdb.dbo.backupset, eliminando il file di backup e procedendo con l'iterazione successiva. Poiché i dati sull'esecuzione del backup provengono da backupset, la precisione è un po' compromessa (non ci sono frazioni di secondo), ma ci faremo andare bene.
Prima di tutto è necessario abilitare l'uso di xp_cmdshell per eliminare i backup (poi non dimenticate di disabilitarlo, se non vi serve):
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
GOE, di fatto:
DECLARE @tmplt AS nvarchar(max) = N'
BACKUP DATABASE [bt]
TO DISK = ''D:SQLServerbackupbt.bak''
WITH
COMPRESSION,
BLOCKSIZE = {bs},
BUFFERCOUNT = {bc},
MAXTRANSFERSIZE = {ts}';
DECLARE @sql AS nvarchar(max);
\Se avete bisogno di chiarimenti su cosa sta succedendo qui, scrivete nei commenti o in privato. Per ora, vi parlerò solo dei parametri che passo in BACKUP DATABASE.
Per BLOCKSIZE abbiamo un elenco di valori «chiuso», e non mi è riuscito di eseguire un backup con BLOCKSIZE < 4KB. MAXTRANSFERSIZE è qualsiasi numero multiplo di 64KB — da 64KB a 4MB. Per impostazione predefinita, sul mio sistema è 1024KB, ho scelto 512 — 1024 — 2048 — 4096.
È stato più complicato con BUFFERCOUNT — può essere qualsiasi numero intero positivo, ma nel link si dice . Lì è scritto anche come ottenere informazioni su quale BUFFERCOUNT viene realmente utilizzato per il backup — nel mio caso è 7. Non aveva senso ridurlo, mentre il limite superiore è stato trovato per esperienza — con BUFFERCOUNT = 896 e MAXTRANSFERSIZE = 4194304 il backup è fallito con un errore (di cui si parla nel link precedente):
Msg 3013, Level 16, State 1, Line 7 BACKUP DATABASE is terminating abnormally.
Msg 701, Level 17, State 123, Line 7 There is insufficient system memory in resource pool ‘default’ to run this query.
A titolo di confronto, prima mostrerò i risultati dell'esecuzione del backup senza specificare alcun parametro:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;Beh, un backup è un backup:
Processed 1070072 pages for database ‘bt’, file ‘bt’ on file 1.
Processed 2 pages for database ‘bt’, file ‘bt_log’ on file 1.
BACKUP DATABASE successfully processed 1070074 pages in 53.171 seconds (157.227 MB/sec).
Lo script stesso, che testa i parametri, ha impiegato un paio d'ore, tutte le misurazioni sono . Ecco un'estrazione dei risultati con i tre migliori tempi di esecuzione (ho cercato di fare un bel grafico, ma, nel post, dovrò accontentarmi di una tabella, e nei commenti ha aggiunto ).
SELECT TOP 7 WITH TIES
compressed_size,
block_size,
buffer_count,
transfer_size,
DATEDIFF(SECOND, start_date, finish_date) AS backup_time_sec
FROM ##bt_results
ORDER BY backup_time_sec ASC;
Attenzione, subito una nota molto importante da da :
si può affermare con certezza che la relazione tra i parametri e la velocità del backup in questi intervalli di valori è casuale, non ci sono schemi. Ma allontanarsi dai parametri incorporati ha chiaramente avuto un buon impatto sul risultato
Cioè, solo tramite la gestione dei parametri standard del BACKUP si è ottenuto un guadagno nel tempo di creazione del backup di 2 volte: 26 secondi, contro 53 all'inizio. Non male, vero? Ma dobbiamo vedere come va il ripristino. E se ora richiederà 4 volte di più?
Per iniziare, misuriamo quanto dura il ripristino di un backup con le impostazioni predefinite:
RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;Beh, questo lo sapete già, ci sono vari percorsi, replace-sì o no, recovery-sì o no. E per me si esegue così:
Processed 1070072 pages for database ‘bt’, file ‘bt’ on file 1.
Processed 2 pages for database ‘bt’, file ‘bt_log’ on file 1.
Il ripristino del database ha elaborato con successo 1070074 pagine in 40,752 secondi (205,141 MB/sec).
Adesso proverò a ripristinare i backup eseguiti con BLOCKSIZE, BUFFERCOUNT e MAXTRANSFERSIZE modificati.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304Il ripristino del database ha elaborato con successo 1070074 pagine in 32,283 secondi (258,958 MB/sec).
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304Il ripristino del database ha elaborato con successo 1070074 pagine in 32,682 secondi (255,796 MB/sec).
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152Il ripristino del database ha elaborato con successo 1070074 pagine in 32,091 secondi (260,507 MB/sec).
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304Il ripristino del database ha elaborato con successo 1070074 pagine in 32,401 secondi (258,015 MB/sec).
L'istruzione RESTORE DATABASE non cambia durante il ripristino; questi parametri non sono specificati, SQL Server li determina automaticamente dal backup. È evidente che anche durante il ripristino ci può essere un guadagno — praticamente fino al 20% più veloce (A dire il vero, non ho dedicato molto tempo al ripristino, ho testato alcuni dei backup "più veloci" e ho verificato che non ci sono stati deterioramenti.).
Per sicurezza, sottolineo che qui non sono descritti parametri ottimali per tutti. I parametri ottimali per te si possono ottenere solo tramite test. Ho ottenuto questi risultati, tu otterrai altri. Ma vedi che i tuoi backup possono essere "ottimizzati" e possono realmente essere creati e ripristinati più velocemente.
Raccomando vivamente di leggere a fondo la documentazione, poiché potrebbero esserci delle peculiarità specifiche per il tuo sistema.
Poiché ho iniziato a scrivere sui backup, voglio subito menzionare un'altra "ottimizzazione" che si presenta più frequentemente rispetto alla "ottimizzazione" dei parametri (quest'ultima viene, se non sbaglio, utilizzata almeno da alcune utilità per il backup, possibilmente insieme ai parametri descritti in precedenza), ma non è ancora stata descritta su Habr.
Se diamo un'occhiata alla seconda riga nella documentazione, subito sotto BACKUP DATABASE, vediamo:
TO [, ...n]Cosa pensi che accadrebbe se specifichiamo più backup_device? La sintassi lo consente. Risultato interessante — il backup semplicemente si "distribuisce" su più dispositivi. Cioè, ogni "dispositivo" per conto suo sarà inutile, se ne perdi uno, perdi l'intero backup. Ma come influirà questa distribuzione sulla velocità del backup?
Proviamo a fare un backup su due "dispositivi" che si trovano vicini nella stessa cartella:
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak'
WITH COMPRESSION;Mio Dio, che cosa sta succedendo?
Processed 1070072 pages for database ‘bt’, file ‘bt’ on file 1.
Elaborate 2 pagine per il database ‘bt’, file ‘btlog’ su file 1.
BACKUP DATABASE elaborato con successo, 1070074 pagine in 40.092 secondi (208.519 MB/sec).
Il backup è stato effettuato il 25% più velocemente senza alcun motivo apparente? E se aggiungessimo un paio di dispositivi in più?
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH COMPRESSION;BACKUP DATABASE elaborato con successo, 1070074 pagine in 34.234 secondi (244.200 MB/sec).
In totale, un guadagno di circa il 35% del tempo di backup semplicemente perché il backup viene scritto in 4 file su un unico disco. Ho controllato un numero maggiore — sul mio laptop non ho notato alcun guadagno, ottimale — 4 dispositivi. Per voi — non lo so, bisogna verificare. E, a proposito, se avete questi dispositivi — sono veramente dischi diversi, congratulazioni, il guadagno dovrebbe essere ancora più significativo.
Ora parliamo di come ripristinare questa meraviglia. Dovremo modificare il comando di ripristino e elencare tutti i dispositivi:
RESTORE DATABASE [bt]
FROM
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH REPLACE, RECOVERY;RESTORE DATABASE elaborato con successo, 1070074 pagine in 38.027 secondi (219.842 MB/sec).
È un po' più veloce, ma abbastanza simile, non è rilevante. In generale, il backup viene eseguito più velocemente, e il ripristino è uguale — successo? A mio avviso — decisamente un successo. Questo è importante, quindi ripeto — se voi perdete anche solo uno di questi file — perdete l'intero backup..
Se guardiamo le informazioni sul backup nel registro, visualizzate tramite i Trace Flag 3213 e 3605, possiamo notare che quando si esegue il backup su più dispositivi, aumenta almeno il numero di BUFFERCOUNT. Forse sarebbe possibile provare a ottimizzare anche i parametri di BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, ma io non ci sono riuscito subito, e ripetere test simili, ma con diversi numeri di file, mi ha fatto perdere la voglia. Inoltre, ho pietà per i dischi. Se volete organizzare tale test da voi, non è difficile modificare lo script.
Infine parliamo del costo. Se il backup viene eseguito in parallelo con il lavoro degli utenti — è necessario affrontare con grande responsabilità il testing, poiché se il backup è più veloce — i dischi sono maggiormente sollecitati, il carico sulla CPU aumenta (bisogna comprimere tutto in tempo reale), di conseguenza, la reattività complessiva del sistema diminuisce.
Scherzi a parte, capisco perfettamente che non ho fatto alcuna rivelazione. Quello che è scritto sopra è semplicemente una dimostrazione di come sia possibile selezionare i parametri ottimali per il backup.
Ricordate che tutto ciò che fate è a vostro rischio e pericolo. Verificate i vostri backup e non dimenticate di eseguire DBCC CHECKDB.
Fonte: habr.com
