Aspetta! Aspetta! No, non è un altro articolo sui tipi di backup di SQL Server. Non parlerò nemmeno delle differenze tra i modelli di ripristino e di come affrontare un "log" sovradimensionato.
Forse (solo forse), dopo aver letto questo post, riuscirete a rendere il backup effettuato con gli strumenti standard un 1,5 volte più veloce la notte di domani. E tutto ciò utilizzando solo un po' più di parametri BACKUP DATABASE.
Se il contenuto del post vi sembra ovvio, mi scuso. Ho letto tutto ciò che ho trovato su Google con la frase "habr sql server backup", e in nessun articolo ho trovato un cenno a come si possa influire sui tempi di backup usando parametri.
Voglio subito richiamare la vostra attenzione sul commento di Aleksandr Gladchenko ():
Non cambiate mai i parametri BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE in produzione. Sono stati creati solo per articoli come questo. In pratica vi troverete a fare i conti con problemi di memoria seri.
Sarebbe davvero fantastico essere il più esperto e pubblicare contenuti esclusivi, ma purtroppo non è così. Ci sono articoli/post in inglese e in russo (mi confondo sempre su come chiamarli correttamente) dedicati a questo tema. Ecco una parte di quelli che ho trovato: , , .
Quindi, per iniziare, allegherò una sintassi semplificata del BACKUP da (tra l'altro, ho accennato prima a BACKUP DATABASE, ma tutto questo è applicabile anche al backup del log delle transazioni e al backup differenziale, sebbene con effetti meno evidenti):
BACKUP DATABASE { database_name | @database_name_var }
TO [, ...n ]
[ WITH {
| [, ...n ] } ]
[;]
[, ...n ]::=
--Media Set Options
| BLOCKSIZE = { blocksize | @blocksize_variable }
--Data Transfer Options
BUFFERCOUNT = { buffercount | @buffercount_variable }
| MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }significa che c'era qualcosa, ma l'ho rimosso perché al momento non è attinente all'argomento.
Come fate normalmente il backup? Come "insegnano" a fare il backup in miliardi di articoli? In generale, se mi sarà necessario fare un backup una tantum di un database non molto grande, scriverò automaticamente qualcosa di simile a questo:
BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
-- va bene, CHECKSUM l'ho scritto solo per sembrare più intelligenteE, in generale, qui sono elencati circa il 75-90% di tutti i parametri che vengono solitamente menzionati negli articoli sui backup. Ci sono anche INIT, SKIP e altro. E voi siete andati su MSDN? Avete visto che ci sono opzioni per un metro e mezzo di schermo? Anche io l'ho visto...
Probabilmente vi è già chiaro che ora parleremo 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. Le dimensioni supportate sono 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 gli altri dispositivi. Di solito non è necessario specificare questo parametro, poiché l'istruzione BACKUP seleziona automaticamente la dimensione del blocco appropriata per il dispositivo. L'impostazione esplicita della dimensione del blocco sovrascrive la selezione automatica della dimensione del blocco.
BUFFERCOUNT = { buffercount | @ buffercount_variable } — definisce il numero totale di buffer di input/output da utilizzare per l'operazione di backup. È possibile specificare qualsiasi valore intero positivo; tuttavia, un numero elevato di buffer può causare un errore di mancanza di memoria a causa di uno spazio indirizzabile virtuale eccessivo durante il processo di Sqlservr.exe.
La quantità totale di spazio utilizzato dai buffer è determinata dalla seguente formula:
BUFFERCOUNT * MAXTRANSFERSIZE.
MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } indica la dimensione massima del pacchetto di dati in byte per lo scambio di dati tra SQL Server e il supporto del backup. Sono supportati valori che sono multipli di 65 536 byte (64 KB), fino a 4 194 304 byte (4 MB).
Giuro, l'ho già letto prima, ma non mi era mai passato per la testa quale impatto potessero avere sulle prestazioni. Inoltre, sembra che debba fare una sorta di "coming out" e ammettere che anche adesso non capisco del tutto cosa facciano. Probabilmente dovrei leggere di più sul buffering e sul funzionamento degli hard disk. Prima o poi lo farò, per ora posso solo scrivere uno script che verifichi come questi valori influenzano la velocità con cui viene eseguito il backup.
Ho creato un piccolo database della dimensione di circa 10 GB, l'ho posizionato su un SSD e la cartella per i backup l'ho messa su un HDD.
Sto creando una tabella temporanea per memorizzare i risultati (non è temporanea, così posso esaminare i risultati più a fondo, 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 funzionamento dello script è semplice: cicli annidati, ognuno dei quali modifica il valore di un parametro, che viene poi passato al comando BACKUP. Salvo l'ultima registrazione della cronologia da msdb.dbo.backupset, elimino il file di backup e passo all'iterazione successiva. Poiché i dati sull'esecuzione del backup provengono da backupset, la precisione è un po' compromessa (lì non ci sono frazioni di secondo), ma possiamo farcela.
Per prima cosa, è necessario abilitare l'uso di xp_cmdshell per poter eliminare i backup (poi ricordati di disabilitarlo se non ti serve):
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
GOE, in sostanza:
DICHIARARE @tmplt COME nvarchar(max) = N'
BACKUP DATABASE [bt]
TO DISK = ''D:SQLServerbackupbt.bak''
WITH
COMPRESSION,
BLOCKSIZE = {bs},
BUFFERCOUNT = {bc},
MAXTRANSFERSIZE = {ts}';
DICHIARARE @sql COME nvarchar(max);
/* Valori di BLOCKSIZE */
DICHIARARE @bs int = 4096,
@max_bs int = 65536;
/* Valori di BUFFERCOUNT */
DICHIARARE @bc int = 7,
@min_bc int = 7,
@max_bc int = 800;
/* Valori di MAXTRANSFERSIZE */
DICHIARARE @ts int = 524288, --512KB, predefinito = 1024KB
@min_ts int = 524288,
@max_ts int = 4194304; --4MB
SELEZIONA TOP 1
@bs = COALESCE (block_size, 4096),
@bc = COALESCE (buffer_count, 7),
@ts = COALESCE (transfer_size, 524288)
DA ##bt_results
ORDINA PER id DESC;
MENTRE (@bs <= @max_bs)
INIZIO
MENTRE (@bc <= @max_bc)
INIZIO
MENTRE (@ts <= @max_ts)
INIZIO
SET @sql = REPLACE (REPLACE (REPLACE(@tmplt, N'{bs}', CAST(@bs AS nvarchar(50))), N'{bc}', CAST (@bc AS nvarchar(50))), N'{ts}', CAST (@ts AS nvarchar(50)));
ESEGUITA (@sql);
INSERISCI IN ##bt_results (start_date, finish_date, backup_size, compressed_size, block_size, buffer_count, transfer_size)
SELEZIONA TOP 1 backup_start_date, backup_finish_date, backup_size, compressed_backup_size, @bs, @bc, @ts
DA msdb.dbo.backupset
ORDINA PER backup_set_id DESC;
ESEGUITA xp_cmdshell 'del "D:SQLServerbackupbt.bak"', no_output;
SET @ts += @ts;
FINE
SET @bc += @bc;
SET @ts = @min_ts;
ASPETTA IN RITARDO '00:00:05';
FINE
SET @bs += @bs;
SET @bc = @min_bc;
SET @ts = @min_ts;
FINESe avete bisogno di maggiori chiarimenti su quello che sta accadendo qui, scrivete nei commenti o in privato. Per ora parlerò solo dei parametri che utilizzo nel BACKUP DATABASE.
Per il BLOCKSIZE abbiamo una lista di valori "chiusa", e non è stato possibile eseguire un backup con BLOCKSIZE < 4KB. MAXTRANSFERSIZE può essere qualsiasi numero multiplo di 64KB, da 64KB a 4MB. Di default, sul mio sistema è 1024KB, io ho scelto 512 — 1024 — 2048 — 4096.
Più complesso è stato gestire il BUFFERCOUNT — può essere qualsiasi numero positivo, ma nel link è scritto . Lì è anche descritta la modalità per ottenere informazioni riguardo a quale BUFFERCOUNT è realmente usato per il backup — nel mio caso è 7. Non aveva senso ridurlo, e il limite superiore è stato scoperto sperimentalmente: con BUFFERCOUNT = 896 e MAXTRANSFERSIZE = 4194304 il backup è fallito con un errore (di cui si parla nel link sopra):
Msg 3013, Level 16, State 1, Line 7 BACKUP DATABASE sta terminando in modo anormale.
Msg 701, Level 17, State 123, Line 7 Non c'è sufficiente memoria di sistema nel pool di risorse 'default' per eseguire questa query.
A titolo di confronto, mostrerò prima i risultati dell'esecuzione del backup senza specificare parametri:
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 valuta i parametri, ha impiegato un paio d'ore, tutte le misurazioni sono state fatte in . Ecco la selezione dei risultati con i tre migliori tempi di esecuzione (ho cercato di creare un grafico carino, ma nel post dovremo accontentarci di una tabella, e nei commenti ho 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 di :
si può affermare con certezza che la connessione tra i parametri e la velocità di backup in questi intervalli di valori è casuale, non ci sono schemi evidenti. Ma allontanarsi dai parametri predefiniti ha, ovviamente, avuto un buon impatto sul risultato
Cioè, solo grazie alla gestione dei parametri predefiniti BACKUP è stato ottenuto un guadagno nel tempo di esecuzione del backup di 2 volte: 26 secondi, contro 53 all'inizio. Non è affatto male, vero? Ma dobbiamo vedere come vanno le cose con il ripristino. E se ora il ripristino richiederà 4 volte di più?
Iniziamo misurando quanto tempo richiede il ripristino del backup con le impostazioni predefinite:
RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;Beh, questo lo sapete anche voi, ci sono strade, replace-sì, recovery-no. E io lo eseguo in questo modo:
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).
Ora proverò a ripristinare i backup effettuati 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 vengono specificati, SQL Server li determina autonomamente dal backup. È evidente che anche durante il ripristino ci può essere un guadagno — praticamente il 20% più veloce (a dire il vero, non ho dedicato molto tempo al ripristino, ho testato alcuni dei backup 'più veloci' e ho constatato che non ci sono stati peggioramenti.).
Per sicurezza, chiarisco: qui non si descrivono parametri ottimali per tutti. I parametri ottimali per te puoi ottenerli solo attraverso il testing. Io ho ottenuto questi risultati, tu ne otterrai altri. Ma puoi vedere che i tuoi backup possono essere "ottimizzati" e possono effettivamente essere creati e ripristinati più rapidamente.
Raccomando vivamente di leggere l'intera documentazione, perché potrebbero esserci delle peculiarità specifiche per il tuo sistema.
Dato che ho cominciato a scrivere sui backup, voglio subito menzionare un'altra "ottimizzazione" che si incontra più spesso del "tuning" dei parametri (che, per quanto ne so, viene utilizzata almeno da alcune utility di backup, forse insieme ai parametri descritti in precedenza), ma che su Habr non è stata ancora descritta.
Se guardiamo la seconda riga della documentazione, subito sotto BACKUP DATABASE, possiamo vedere:
TO [, ...n]Cosa pensi che succederà se specifichiamo più dispositivi di backup? La sintassi lo consente. Sarà molto interessante: il backup si "disperderà" su più dispositivi. Cioè, ogni "dispositivo" sarà praticamente inutile; se ne perdi uno, perdi tutto il backup. Ma come influenzerà questo disperdersi la velocità di backup?
Proviamo a effettuare 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;Accidenti, ma cosa sta succedendo?
Processed 1070072 pages for database ‘bt’, file ‘bt’ on file 1.
Elaborate 2 pagine per il database 'bt', file 'btlog' nel file 1.
BACKUP DATABASE elaborato con successo 1070074 pagine in 40.092 secondi (208.519 MB/sec).
Il backup è stato effettuato 25% più velocemente, senza motivo? E se aggiungessimo altri dispositivi?
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, si guadagna circa il 35% del tempo di backup grazie al fatto che il backup viene scritto contemporaneamente in 4 file su un unico disco. Ho verificato con un numero maggiore — sul mio portatile non c'è guadagno, l'ottimale è 4 dispositivi. Per voi — non lo so, bisogna controllare. E, a proposito, se avete questi dispositivi — sono effettivamente dischi diversi, congratulazioni, il guadagno dovrebbe essere ancora maggiore.
Ora parliamo di come ripristinare questa gioia. Per farlo, dovrete cambiare 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 successfully processed 1070074 pages in 38.027 seconds (219.842 MB/sec).
Un po' più veloce, ma siamo lì vicino, non è significativo. In generale, il backup viene effettuato più rapidamente e il ripristino richiede lo stesso tempo — successo? Per me, è un successo decisamente. importante, quindi ripeto — se voi perdete anche solo uno di questi file — perdete l'intero backup.
Se si guarda nel registro le informazioni sul backup, visualizzate tramite i Trace Flag 3213 e 3605, si può notare che, durante il backup su più dispositivi, aumenta almeno il numero di BUFFERCOUNT. Probabilmente, si potrebbero trovare parametri più ottimali anche per BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, ma io non ci sono riuscito subito, e mi sono anche scocciato di eseguire nuovamente un test del genere, ma con un numero diverso di file. E anche i dischi sono un po' un peccato. Se volete organizzare un test simile, non è difficile modificare lo script.
Alla fine parliamo del prezzo. Se il backup viene eseguito parallelamente con il lavoro degli utenti, è necessario affrontare il test con molta serietà, poiché se il backup viene eseguito più rapidamente, i dischi si sforzano di più e il carico sulla CPU aumenta (bisogna anche comprimere tutto al volo), quindi la reattività complessiva del sistema diminuisce.
Scherzi a parte, capisco perfettamente che non ho fatto alcuna rivelazione. Ciò che è scritto sopra è semplicemente una dimostrazione di come si possano selezionare i parametri ottimali per eseguire i backup.
Ricorda che tutto ciò che fai lo fai a tuo rischio e pericolo. Controlla i tuoi backup e non dimenticare il DBCC CHECKDB.
Fonte: habr.com
