Aspettare! Aspettare! È vero, questo non è un altro articolo sui tipi di backup di SQL Server. Non parlerò nemmeno delle differenze tra i modelli di recupero e di come gestire un tronco troppo cresciuto.
Forse (solo forse), dopo aver letto questo post, sarai in grado di assicurarti che il backup che ti è stato rimosso utilizzando mezzi standard verrà rimosso domani sera, beh, 1.5 volte più velocemente. E solo perché usi un po' più di parametri BACKUP DATABASE.
Se il contenuto del post ti era ovvio, mi dispiace. Ho letto tutto ciò che Google ha trovato sulla frase "habr sql server backup" e in nessun articolo ho trovato menzione del fatto che il tempo di backup può essere in qualche modo influenzato utilizzando i parametri.
Attirerò immediatamente la vostra attenzione sul commento di Alexander Gladchenko ():
Non modificare mai i parametri BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE in produzione. Sono fatti solo per scrivere tali articoli. In pratica, ti libererai dei problemi di memoria in pochissimo tempo.
Sarebbe ovviamente bello essere i più intelligenti e pubblicare contenuti esclusivi, ma sfortunatamente non è così. Ci sono articoli/post sia in lingua inglese che in lingua russa (sono sempre confuso su come chiamarli correttamente) dedicati a questo argomento. Ecco alcuni di quelli in cui mi sono imbattuto: , , .
Quindi, per cominciare, allegherò una sintassi BACKUP leggermente ridotta da (a proposito, ho scritto sopra di BACKUP DATABASE, ma tutto questo vale sia per il backup del log delle transazioni che per il backup differenziale, ma forse con un effetto meno evidente):
BACKUP DATABASE { database_name | @database_name_var }
TO <backup_device> [ ,...n ]
<...>
[ WITH { <...>
| <general_WITH_options> [ ,...n ] } ]
[;]
<general_WITH_options> [ ,...n ]::=
<...>
--Media Set Options
<...>
| BLOCKSIZE = { blocksize | @blocksize_variable }
--Data Transfer Options
BUFFERCOUNT = { buffercount | @buffercount_variable }
| MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }
<...><…> - significa che c'era qualcosa lì, ma l'ho rimosso perché ora non è rilevante per l'argomento.
Come fai solitamente il backup? Come “insegnano” come eseguire i backup in miliardi di articoli? In generale, se devo eseguire un backup una tantum di un database non molto grande, scriverò automaticamente qualcosa del genere:
BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--ладно, CHECKSUM я написал только чтобы казаться умнееE, in generale, qui sono elencati probabilmente il 75-90% di tutti i parametri solitamente menzionati negli articoli sui backup. Bene, c'è anche INIT, SKIP. Hai visitato MSDN? Hai visto che ci sono opzioni per uno schermo e mezzo? Ho visto anche...
Probabilmente hai già capito che parleremo ulteriormente dei tre parametri rimasti nel primo blocco di codice: BLOCKSIZE, BUFFERCOUNT e MAXTRANSFERSIZE. Ecco le loro descrizioni da MSDN:
MISURA DEL BLOCCO = { misura del blocco | @ dimensione_blocco_variabile } - indica la dimensione del blocco fisico in byte. Le dimensioni supportate sono 512, 1024, 2048, 4096, 8192, 16, 384 e 32 byte (768 KB). Il valore predefinito è 65 per i dispositivi a nastro e 536 per gli altri dispositivi. In genere questo parametro non è necessario poiché l'istruzione BACKUP seleziona automaticamente la dimensione del blocco appropriata per il dispositivo. L'impostazione della dimensione del blocco sovrascrive esplicitamente la selezione automatica della dimensione del blocco.
BUFFERCOUNT = { buffercount | @ buffercount_variabile } - Definisce il numero totale di buffer I/O che verranno utilizzati per l'operazione di backup. È possibile specificare qualsiasi valore intero positivo, ma un numero elevato di buffer potrebbe causare un errore di memoria insufficiente a causa dell'eccessivo spazio di indirizzi virtuali nel processo Sqlservr.exe.
La quantità totale di spazio utilizzato dai buffer è determinata dalla seguente formula:
BUFFERCOUNT * MAXTRANSFERSIZE.
MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } specifica la dimensione massima del pacchetto di dati, in byte, da scambiare tra SQL Server e il supporto del set di backup. Sono supportati multipli di 65 byte (536 KB) fino a 64 byte (4 MB).
Lo giuro: l'ho già letto, ma non mi era mai venuto in mente quanto impatto potessero avere sulla produttività. Inoltre, a quanto pare, devo fare una sorta di "coming out" e ammettere che anche adesso non capisco appieno cosa stiano facendo esattamente. Probabilmente ho bisogno di leggere di più sull'I/O bufferizzato e sull'utilizzo di un disco rigido. Un giorno lo farò, ma per ora posso semplicemente scrivere uno script che controllerà come questi valori influiscono sulla velocità con cui viene eseguito il backup.
Ho creato un piccolo database, di circa 10 GB, l'ho inserito nell'SSD e ho inserito la directory per i backup nell'HDD.
Creo una tabella temporanea per archiviare i risultati (non ce l'ho temporanea, quindi posso approfondire i risultati in modo più dettagliato, ma tu decidi tu stesso):
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 dello script è semplice: cicli annidati, ognuno dei quali modifica il valore di un parametro, inserisci questi parametri nel comando BACKUP, salva l'ultimo record con la cronologia da msdb.dbo.backupset, elimina il file di backup e l'iterazione successiva . Poiché i dati di esecuzione del backup vengono presi dal set di backup, la precisione viene leggermente persa (non ci sono frazioni di secondo), ma sopravviveremo.
Per prima cosa devi abilitare xp_cmdshell per eliminare i backup (poi non dimenticare 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;
GOBeh, in realtà:
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);
/* BLOCKSIZE values */
DECLARE @bs int = 4096,
@max_bs int = 65536;
/* BUFFERCOUNT values */
DECLARE @bc int = 7,
@min_bc int = 7,
@max_bc int = 800;
/* MAXTRANSFERSIZE values */
DECLARE @ts int = 524288, --512KB, default = 1024KB
@min_ts int = 524288,
@max_ts int = 4194304; --4MB
SELECT TOP 1
@bs = COALESCE (block_size, 4096),
@bc = COALESCE (buffer_count, 7),
@ts = COALESCE (transfer_size, 524288)
FROM ##bt_results
ORDER BY id DESC;
WHILE (@bs <= @max_bs)
BEGIN
WHILE (@bc <= @max_bc)
BEGIN
WHILE (@ts <= @max_ts)
BEGIN
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)));
EXEC (@sql);
INSERT INTO ##bt_results (start_date, finish_date, backup_size, compressed_size, block_size, buffer_count, transfer_size)
SELECT TOP 1 backup_start_date, backup_finish_date, backup_size, compressed_backup_size, @bs, @bc, @ts
FROM msdb.dbo.backupset
ORDER BY backup_set_id DESC;
EXEC xp_cmdshell 'del "D:SQLServerbackupbt.bak"', no_output;
SET @ts += @ts;
END
SET @bc += @bc;
SET @ts = @min_ts;
WAITFOR DELAY '00:00:05';
END
SET @bs += @bs;
SET @bc = @min_bc;
SET @ts = @min_ts;
ENDSe all'improvviso hai bisogno di chiarimenti su cosa sta succedendo qui, scrivi nei commenti o in PM. Per ora ti parlo solo dei parametri che ho inserito in BACKUP DATABASE.
Per BLOCKSIZE abbiamo un elenco di valori "chiuso" e non ho eseguito un backup con BLOCKSIZE <4KB. MAXTRANSFERSIZE qualsiasi numero multiplo di 64KB, da 64KB a 4MB. Il valore predefinito sul mio sistema è 1024KB, ho preso 512 - 1024 - 2048 - 4096.
È stato più difficile con BUFFERCOUNT: può essere qualsiasi numero positivo, ma il collegamento lo dice . Dice anche come ottenere informazioni su con quale BUFFERCOUNT viene effettivamente eseguito il backup - per me è 7. Non aveva senso ridurlo e il limite superiore è stato scoperto sperimentalmente - con BUFFERCOUNT = 896 e MAXTRANSFERSIZE = 4194304 il backup è caduto con un errore (di cui scritto nel link sopra):
Il messaggio 3013, Livello 16, Stato 1, Linea 7 BACKUP DATABASE sta terminando in modo anomalo.
Messaggio 701, livello 17, stato 123, riga 7 Memoria di sistema insufficiente nel pool di risorse "predefinito" per eseguire questa query.
Per confronto, mostrerò prima i risultati dell'esecuzione di un backup senza specificare alcun parametro:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;Bene, backup e backup:
Elaborate 1070072 pagine per il database "bt", file "bt" nel file 1.
Elaborate 2 pagine per il database "bt", file "bt_log" nel file 1.
Il DATABASE BACKUP ha elaborato con successo 1070074 pagine in 53.171 secondi (157.227 MB/sec).
Lo script stesso, testando i parametri, ha funzionato in un paio d'ore, tutte le misurazioni erano presenti . Ed ecco una selezione dei risultati con i tre migliori tempi di esecuzione (ho provato a 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, una nota molto importante da di :
Possiamo affermare con sicurezza che la relazione tra i parametri e la velocità di backup all'interno di questi intervalli di valori è casuale, non esiste uno schema. Ma l'allontanamento dai parametri predefiniti ha ovviamente avuto un buon effetto sul risultato
Quelli. Solo gestendo i parametri standard di BACKUP il tempo di rimozione del backup è raddoppiato: 2 secondi, contro i 26 iniziali. Non è male, vero? Ma bisogna vedere cosa accadrà con il restauro. E se ora il recupero richiedesse 53 volte più tempo?
Innanzitutto, misuriamo quanto tempo è necessario per ripristinare un backup con le impostazioni predefinite:
RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;Ebbene, tu stesso lo sai, i modi ci sono, sostituire non è sostituire, recuperare non è recuperare. E lo faccio così:
Elaborate 1070072 pagine per il database "bt", file "bt" nel file 1.
Elaborate 2 pagine per il database "bt", file "bt_log" nel file 1.
RESTORE DATABASE ha elaborato con successo 1070074 pagine in 40.752 secondi (205.141 MB/sec).
Ora proverò a ripristinare i backup eseguiti con BLOCKSIZE, BUFFERCOUNT e MAXTRANSFERSIZE modificati.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304RESTORE DATABASE ha elaborato con successo 1070074 pagine in 32.283 secondi (258.958 MB/sec).
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304RESTORE DATABASE ha elaborato con successo 1070074 pagine in 32.682 secondi (255.796 MB/sec).
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152RESTORE DATABASE ha elaborato con successo 1070074 pagine in 32.091 secondi (260.507 MB/sec).
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304RESTORE 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 in essa; SQL Server stesso li determina dal backup. Ed è chiaro che anche con la ripresa si può ottenere un guadagno, quasi il 20% più veloce (Ad essere onesti, non ho dedicato molto tempo al ripristino, ho eseguito diversi backup "più veloci" e mi sono assicurato che non ci fossero deterioramenti).
Per ogni evenienza, vorrei chiarire che questi non sono alcuni parametri ottimali per tutti. Puoi ottenere i parametri ottimali per te stesso solo testando. Ho ottenuto questi risultati, tu ne otterrai diversi. Ma vedi che puoi "ottimizzare" i tuoi backup e possono effettivamente formarsi e distribuirsi più velocemente.
Ti consiglio inoltre vivamente di leggere la documentazione nella sua interezza, poiché potrebbero esserci sfumature specifiche per il tuo sistema.
Da quando ho iniziato a scrivere sui backup, voglio scrivere subito di un'altra "ottimizzazione", che è più comune dei parametri di "ottimizzazione" (per quanto ho capito, viene utilizzata almeno da alcune utilità di backup, magari insieme ai parametri descritto in precedenza), ma non è stato ancora descritto nemmeno su Habré.
Se guardiamo la seconda riga della documentazione, proprio sotto BACKUP DATABASE, vediamo:
TO <backup_device> [ ,...n ]Cosa pensi che accadrà se specifichi diversi backup_devices? La sintassi lo consente. E accadrà una cosa molto interessante: il backup verrà semplicemente "distribuito" su più dispositivi. Quelli. ogni "dispositivo" individualmente sarà inutile, perso uno, perso l'intero backup. Ma in che modo tali distorsioni influiranno sulla velocità di backup?
Proviamo ad effettuare un backup su due “dispositivi” che si trovano uno accanto all'altro nella stessa cartella:
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak'
WITH COMPRESSION;Padri del mondo, perché viene fatto questo?
Elaborate 1070072 pagine per il database "bt", file "bt" nel file 1.
Elaborate 2 pagine per il database 'bt', file 'bt'log' sul file 1.
Il DATABASE BACKUP ha elaborato con successo 1070074 pagine in 40.092 secondi (208.519 MB/sec).
Il backup è diventato improvvisamente più veloce del 25%? E se aggiungessimo un paio di altri dispositivi?
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH COMPRESSION;Il DATABASE BACKUP ha elaborato con successo 1070074 pagine in 34.234 secondi (244.200 MB/sec).
In totale, il guadagno è di circa il 35% del tempo di esecuzione del backup solo perché il backup viene scritto su 4 file contemporaneamente su un disco. Ho controllato un numero maggiore - non c'è guadagno sul mio laptop, in modo ottimale - 4 dispositivi. Per te, non lo so, devi controllare. Bene, a proposito, se hai questi dispositivi, questi sono dischi davvero diversi, congratulazioni, il guadagno dovrebbe essere ancora più significativo.
Ora parliamo di come ripristinare questa felicità. Per fare ciò, dovrai modificare il comando di ripristino ed 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 ha elaborato con successo 1070074 pagine in 38.027 secondi (219.842 MB/sec).
Un po' più veloce, ma da qualche parte vicino, non significativo. In generale, il backup viene rimosso più velocemente e ripristinato allo stesso modo: successo? Per quanto mi riguarda, è un bel successo. Questo важно, quindi ripeto: se tu se perdi almeno uno di questi file, perderai l'intero backup.
Se si esaminano nel registro le informazioni di backup visualizzate utilizzando i flag di traccia 3213 e 3605, si noterà che quando si esegue il backup su più dispositivi, aumenta almeno il numero di BUFFERCOUNT. Probabilmente puoi provare a selezionare parametri più ottimali per BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, ma non ci sono riuscito subito ed ero troppo pigro per eseguire nuovamente tali test, ma per un numero diverso di file. Ed è un peccato per le ruote. Se vuoi organizzare tali test a casa, non è difficile rifare la sceneggiatura.
Infine, parliamo del prezzo. Se il backup viene rimosso parallelamente al lavoro degli utenti, è necessario adottare un approccio molto responsabile ai test, perché se il backup viene rimosso più velocemente, i dischi vengono sollecitati di più, il carico sul processore aumenta (devi comunque comprimere al volo) e di conseguenza la reattività complessiva del sistema diminuisce.
Scherzo, ma capisco benissimo che non ho fatto nessuna rivelazione. Quanto scritto sopra è semplicemente una dimostrazione di come selezionare i parametri ottimali per l'esecuzione dei backup.
Ricorda che tutto ciò che fai è fatto a tuo rischio e pericolo. Controlla i tuoi backup e non dimenticare DBCC CHECKDB.
Fonte: habr.com