MS SQL Server: BACKUP auf Steroiden

Warten Sie! Warten Sie! Tatsächlich ist dies kein weiterer Artikel über die Arten von SQL Server-Backups. Ich werde nicht einmal die Unterschiede zwischen den Wiederherstellungsmodellen erläutern und wie man mit einem gewachsenen „Log“ umgeht.

Vielleicht (nur vielleicht) können Sie nach dem Lesen dieses Beitrags dafür sorgen, dass das Backup, das Sie mit den Standardmitteln erstellen, morgen Nacht nun 1,5-mal schneller durchgeführt wird. Und das nur, weil Sie ein bisschen mehr Parameter für BACKUP DATABASE verwenden.

Wenn der Inhalt des Beitrags für Sie offensichtlich war – entschuldigung. Ich habe alles gelesen, was ich über die Phrase „habr sql server backup“ bei Google finden konnte, und in keinem Artikel habe ich eine Erwähnung darüber gefunden, dass man die Backup-Zeit irgendwie mit Parametern beeinflussen kann.

Ich möchte sofort auf den Kommentar von Alexander Gladtschenko hinweisen (@mssqlhelp):

Ändern Sie niemals die Parameter BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE in der Produktion. Diese sind nur zu Erstellungszwecken solcher Artikel gemacht. In der Praxis werden Sie Probleme mit dem Speicher bekommen.

Es wäre natürlich toll, der Klügste zu sein und exklusiven Content zu veröffentlichen, aber leider ist das nicht der Fall. Es gibt sowohl englisch- als auch russischsprachige Artikel/Postings (ich verwechsle immer, wie man sie richtig nennt), die sich mit diesem Thema beschäftigen. Hier sind einige, die mir begegnet sind: eins, zwei, drei (auf sql.ru).

Also, zuerst füge ich eine verkürzte Syntax für BACKUP aus MSDN (übrigens, ich habe oben über BACKUP DATABASE geschrieben, aber all dies gilt auch für das Backup des Transaktionsprotokolls und für das differenzielle Backup, jedoch möglicherweise mit weniger offensichtlichem Effekt):

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 }

bedeutet, dass dort etwas war, aber ich habe es entfernt, weil es momentan nicht zum Thema gehört.

Wie ziehen Sie normalerweise Backups? Wie „lernen“ es Milliarden von Artikeln, Backups zu ziehen? Wenn ich ein einmaliges Backup einer nicht sehr großen Datenbank erstellen muss, schreibe ich automatisch etwas in dieser Art:

BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--okay, CHECKSUM habe ich nur geschrieben, um schlauer zu erscheinen

Und im Grunde sind hier wahrscheinlich 75-90 % aller Parameter aufgelistet, die üblicherweise in Artikeln über Backups erwähnt werden. Also INIT, SKIP und so weiter. Waren Sie schon in MSDN? Haben Sie gesehen, dass dort Optionen für anderthalb Bildschirme sind? Ich habe das auch gesehen...

Sie haben wahrscheinlich schon verstanden, dass es als nächstes um die drei Parameter geht, die im ersten Codeblock geblieben sind — BLOCKSIZE, BUFFERCOUNT und MAXTRANSFERSIZE. Hier sind ihre Beschreibungen aus MSDN:

BLOCKSIZE = { blocksize | @ blocksize_variable } — gibt die Größe des physischen Blocks in Bytes an. Unterstützt werden Größen von 512, 1024, 2048, 4096, 8192, 16 384, 32 768 und 65 536 Bytes (64 KB). Der Standardwert beträgt 65 536 für bandlaufwerke und 512 für andere Geräte. In der Regel ist dieser Parameter nicht erforderlich, da der BACKUP-Befehl automatisch die blockgröße wählt, die dem Gerät entspricht. Eine explizite Einstellung der blockgröße überschreibt die automatische Auswahl der blockgröße.

BUFFERCOUNT = { buffercount | @ buffercount_variable } stellt die Gesamtzahl der Ein-/Ausgabepuffer fest, die für die Sicherungsoperation verwendet werden. Jeder positive ganzzahlige Wert kann angegeben werden, jedoch kann eine hohe Anzahl von Puffern zu einem Speichermangelfehler führen, da der virtuelle Adressraum von Sqlservr.exe übermäßig verwendet wird.

Der gesamte Speicherplatz, der von den Puffer verwendet wird, wird nach folgender Formel berechnet: BUFFERCOUNT * MAXTRANSFERSIZE.

MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } gibt die maximale Größe eines Datenpakets in Bytes an, das für den Datenaustausch zwischen SQL Server und dem Sicherungsmedium verwendet wird. Erlaubt sind Werte, die Vielfache von 65 536 Bytes (64 KB) bis zu 4 194 304 Bytes (4 MB) sind.

Ich schwöre, ich habe das schon früher gelesen, aber mir ist nie in den Sinn gekommen, welchen Einfluss sie auf die Leistung haben können. Darüber hinaus scheint es notwendig zu sein, eine Art „Coming-out“ zu machen und zuzugeben, dass ich selbst jetzt nicht ganz verstehe, was genau sie tun. Vielleicht sollte ich mehr über gepuffertes Ein-/Ausgeben und die Arbeit mit Festplatten lesen. Eines Tages werde ich das tun, aber zurzeit kann ich einfach ein Skript schreiben, das überprüft, wie sich diese Werte auf die Geschwindigkeit auswirken, mit der ein Backup erstellt wird.

Ich habe eine kleine Datenbank mit einer Größe von etwa 10 GB erstellt, diese auf SSD gelegt und das Verzeichnis für die Backups auf HDD gespeichert.

Ich erstelle eine temporäre Tabelle zur Speicherung der Ergebnisse (für mich ist sie nicht temporär, damit ich die Ergebnisse genauer untersuchen kann, aber ihr könnt selbst entscheiden):

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
);

Der Skript funktioniert einfach – es gibt verschachtelte Schleifen, von denen jede den Wert eines Parameters ändert, diesen in den Befehl BACKUP einfügt, den letzten Eintrag mit der Historie aus msdb.dbo.backupset speichert, die Sicherungsdatei löscht und zur nächsten Iteration übergeht. Da die Daten zur Durchführung des Backups aus backupset entnommen werden, geht etwas Genauigkeit verloren (da dort keine Sekundenbruchteile vorhanden sind), aber das werden wir überstehen.

Zunächst müssen Sie die Nutzung von xp_cmdshell erlauben, um Backups zu löschen (vergessen Sie nicht, es später wieder zu deaktivieren, wenn Sie es nicht benötigen):

EXEC sp_configure 'show advanced options', 1;  
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;  
GO

Nun, und eigentlich:

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;
END

Falls Sie Erklärungen dazu brauchen, was hier passiert – schreiben Sie in die Kommentare oder in die persönliche Nachricht. Ich werde bis jetzt nur über die Parameter berichten, die ich im BACKUP DATABASE verwende.

Für BLOCKSIZE haben wir eine "geschlossene" Liste von Werten, und ich konnte kein Backup mit BLOCKSIZE < 4KB durchführen. MAXTRANSFERSIZE kann jede Zahl sein, die ein Vielfaches von 64KB ist — von 64KB bis 4MB. Standardmäßig liegt es auf meinem System bei 1024KB; ich habe 512 – 1024 – 2048 – 4096 genommen.

Schwieriger war es mit BUFFERCOUNT – dieser kann jede positive Zahl sein, aber in dem Link steht, wie er in BACKUP DATABASE berechnet wird und welche Gefahren große Werte mit sich bringen.Dort steht auch, wie man Informationen darüber erhält, mit welchem BUFFERCOUNT das Backup tatsächlich erstellt wird – bei mir sind das 7. Es machte keinen Sinn, ihn zu verringern, und die obere Grenze wurde empirisch ermittelt – bei BUFFERCOUNT = 896 und MAXTRANSFERSIZE = 4194304 fiel das Backup mit einem Fehler (der auf dem oben genannten Link beschrieben wird):

Msg 3013, Level 16, State 1, Line 7 BACKUP DATABASE wird abnormal beendet.

Msg 701, Level 17, State 123, Line 7 Es gibt nicht genügend Systemspeicher im Ressourcenpool 'default', um diese Abfrage auszuführen.

Zum Vergleich zeige ich zuerst die Ergebnisse der Backup-Ausführung ohne Angabe von Parametern:

BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;

Nun, Backup ist Backup:

Verarbeitet 1070072 Seiten für die Datenbank 'bt', Datei 'bt' in Datei 1.

Verarbeitet 2 Seiten für die Datenbank 'bt', Datei 'bt_log' in Datei 1.

BACKUP DATABASE hat erfolgreich 1070074 Seiten in 53.171 Sekunden (157.227 MB/sec) verarbeitet.

Das Skript, das die Parameter testet, hat ein paar Stunden gebraucht; alle Messungen sind in Google Sheets.. Hier sind die Ergebnisse der Abfragen, die die drei besten Ausführungszeiten haben (ich habe versucht, eine schöne Grafik zu erstellen, aber im Beitrag müssen wir mit einer Tabelle auskommen). @mixsture die Adresse 127.0.0.1 in das Register der verbotenen Webseiten ein. Es wurde klar, dass ohne ein ordentliches Getränk hier nichts zu klären ist, und es wurde mit der Entwicklung von… Bier begonnen. Das war ein wichtiges Release. sehr coole Grafiken).

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;

Achtung, gleich eine sehr wichtige Anmerkung von @mixsture von einem Kommentar:

man kann mit Sicherheit sagen, dass der Zusammenhang zwischen den Parametern und der Backup-Geschwindigkeit in diesen Wertebereichen zufällig ist, es gibt keine Regelmäßigkeit. Aber die Abweichung von den Standardeinstellungen hat offensichtlich das Ergebnis gut beeinflusst.

Das heißt, durch das Management der Standardparameter des BACKUP wurde die Zeit für die Erstellung des Backups auf die Hälfte reduziert: 26 Sekunden, gegenüber 53 zu Beginn. Ist nicht schlecht, oder? Aber wir müssen schauen, wie es mit der Wiederherstellung aussieht. Vielleicht dauert die Wiederherstellung jetzt viermal länger?

Lass uns zunächst messen, wie lange die Wiederherstellung mit den Standardeinstellungen dauert:

RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;

Nun, das wissen Sie selbst, die Wege, replace oder nicht replace, recovery oder nicht recovery. Und so wird es bei mir ausgeführt:

Verarbeitet 1070072 Seiten für die Datenbank 'bt', Datei 'bt' in Datei 1.

Verarbeitet 2 Seiten für die Datenbank 'bt', Datei 'bt_log' in Datei 1.

RESTORE DATABASE successfully processed 1070074 pages in 40.752 seconds (205.141 MB/sec).

Und jetzt werde ich versuchen, die Backups wiederherzustellen, die mit geänderten BLOCKSIZE, BUFFERCOUNT und MAXTRANSFERSIZE aufgenommen wurden.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

DIE WIEDERHERSTELLUNG DER DATENBANK wurde erfolgreich mit 1070074 Seiten in 32,283 Sekunden (258,958 MB/s) verarbeitet.

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

DIE WIEDERHERSTELLUNG DER DATENBANK wurde erfolgreich mit 1070074 Seiten in 32,682 Sekunden (255,796 MB/s) verarbeitet.

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

DIE WIEDERHERSTELLUNG DER DATENBANK wurde erfolgreich mit 1070074 Seiten in 32,091 Sekunden (260,507 MB/s) verarbeitet.

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

DIE WIEDERHERSTELLUNG DER DATENBANK wurde erfolgreich mit 1070074 Seiten in 32,401 Sekunden (258,015 MB/s) verarbeitet.

Die RESTORE DATABASE-Anweisung ändert sich während der Wiederherstellung nicht; diese Parameter werden nicht angegeben; SQL Server bestimmt sie selbst anhand des Backups. Und es ist ersichtlich, dass die Wiederherstellung sogar um fast 20% schneller sein kann (ehrlich gesagt, habe ich nicht viel Zeit mit der Wiederherstellung verbracht, ich habe ein paar der "schnellsten" Backups ausprobiert und festgestellt, dass es keine Verschlechterung gab.).

Nur für den Fall, dass ich klarstelle — hier sind keine optimalen Parameter für alle angegeben. Die optimalen Parameter für Ihre Situation können nur durch Tests ermittelt werden. Ich habe diese Ergebnisse erzielt, Sie werden andere erhalten. Aber Sie sehen, dass Ihre Backups „getunt“ werden können und sie tatsächlich schneller erstellt und wiederhergestellt werden können.

Ich empfehle dringend, die gesamte Dokumentation zu lesen, da es für Ihr System spezifische Nuancen geben kann.

Da ich schon über Backups schreibe, möchte ich gleich noch eine weitere "Optimierung" ansprechen, die häufiger vorkommt als die "Feinabstimmung" der Parameter (so wie ich es verstehe, nutzen mindestens einige Tools zur Sicherung diese ebenfalls, möglicherweise zusammen mit den zuvor beschriebenen Parametern), aber sie wurde auf Habré auch noch nicht beschrieben.

Wenn wir uns die zweite Zeile in der Dokumentation ansehen, direkt unter BACKUP DATABASE, sehen wir:

TO  [, ...n]

Was denken Sie, was passiert, wenn man mehrere backup_device’s angibt? Die Syntax erlaubt es ja. Und es wird etwas sehr Interessantes passieren – das Backup wird einfach auf mehrere Geräte "verteilt". Das bedeutet, dass jedes "Gerät" für sich nutzlos sein wird; verlieren Sie eines, verlieren Sie das gesamte Backup. Aber wie wird sich diese Verteilung auf die Geschwindigkeit der Sicherung auswirken?

Lassen Sie uns versuchen, ein Backup auf zwei "Geräten" zu machen, die nebeneinander in einem Ordner liegen:

BACKUP DATABASE [bt]
TO 
    DISK = 'D:SQLServerbackupbt1.bak',
    DISK = 'D:SQLServerbackupbt2.bak'   
WITH COMPRESSION;

Heiliger Bimbam, was geschieht hier?

Verarbeitet 1070072 Seiten für die Datenbank 'bt', Datei 'bt' in Datei 1.

Verarbeitet 2 Seiten für die Datenbank 'bt', Datei 'btDatei 1 protokollieren.

DIE DATENSICHERUNG DER DATABANK wurde erfolgreich mit 1.070.074 Seiten in 40,092 Sekunden (208,519 MB/s) verarbeitet.

Wurde das Backup einfach so um 25 % schneller? Was, wenn man noch ein paar Geräte hinzufügt?

DIE DATENSICHERUNG DER DATABANK [bt]
WIRD
    DISK = 'D:SQLServerbackupbt1.bak',
    DISK = 'D:SQLServerbackupbt2.bak',
    DISK = 'D:SQLServerbackupbt3.bak',
    DISK = 'D:SQLServerbackupbt4.bak'
MIT KOMPRESSION;

DIE DATENSICHERUNG DER DATABANK wurde erfolgreich mit 1.070.074 Seiten in 34,234 Sekunden (244,200 MB/s) verarbeitet.

Insgesamt beträgt die Zeitersparnis etwa 35 %, nur weil das Backup gleichzeitig in 4 Dateien auf einer Festplatte geschrieben wird. Ich habe mehr getestet – auf meinem Laptop gab es keinen Gewinn, optimal sind 4 Geräte. Für Sie – ich weiß nicht, das muss überprüft werden. Und übrigens, wenn diese Geräte wirklich verschiedene Festplatten sind, herzlichen Glückwunsch, der Gewinn sollte erheblich größer sein.

Jetzt sprechen wir darüber, wie man dieses Glück wiederherstellt. Dazu muss das Wiederherstellungsbefehl geändert werden und alle Geräte aufgelistet werden:

DIE DATENSICHERUNG DER DATABANK [bt]
WIRD
    DISK = 'D:SQLServerbackupbt1.bak',
    DISK = 'D:SQLServerbackupbt2.bak',
    DISK = 'D:SQLServerbackupbt3.bak',
    DISK = 'D:SQLServerbackupbt4.bak'
MIT ERSETZEN, WIEDERHERSTELLUNG;

DIE DATENSICHERUNG DER DATABANK wurde erfolgreich mit 1.070.074 Seiten in 38,027 Sekunden (219,842 MB/s) verarbeitet.

Etwas schneller, aber trotzdem nicht wesentlich anders. Insgesamt wird das Backup schneller erstellt, die Wiederherstellung erfolgt jedoch gleich schnell — ein Erfolg? Meiner Meinung nach — durchaus ein Erfolg. Das ist wichtig, daher wiederhole ich mich — wenn Sie auch nur eine dieser Dateien verlieren, verlieren Sie das gesamte Backup. Wenn Sie sich die Informationen über das Backup im Log ansehen, die durch die Trace Flags 3213 und 3605 ausgegeben werden, fällt auf, dass bei Backups auf mehrere Geräte die Anzahl von BUFFERCOUNT mindestens erhöht wird. Man könnte vielleicht versuchen, auch die Parameter für BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE zu optimieren, aber ich habe das umgehend nicht geschafft, und ich war zu faul, um solche Tests erneut durchzuführen, insbesondere mit unterschiedlichen Dateizahlen. Außerdem sind die Festplatten kostbar. Wenn Sie solche Tests bei sich organisieren möchten, ist es nicht schwierig, das Skript anzupassen..

Wenn man die im Protokoll angezeigten Informationen zum Backup, die durch die Trace Flags 3213 und 3605 ausgegeben werden, betrachtet, fällt auf, dass bei Backups auf mehrere Geräte die Anzahl der BUFFERCOUNT mindestens erhöht wird. Vielleicht könnte man auch versuchen, optimierte Parameter für BUFFERCOUNT, BLOCKSIZE und MAXTRANSFERSIZE zu finden, aber mir ist das auf Anhieb nicht gelungen, und ich hatte keine Lust, dies erneut zu testen, insbesondere nicht mit unterschiedlichen Dateizahlen. Außerdem habe ich Bedenken wegen der Festplatten. Wenn Sie ein solches Testing bei sich durchführen möchten, ist es nicht schwierig, das Skript anzupassen.

Am Ende sprechen wir über den Preis. Wenn das Backup parallel zur Arbeit der Benutzer durchgeführt wird — muss man das Testing sehr ernst nehmen, denn wenn das Backup schneller erstellt wird, sind die Festplatten stärker belastet, die CPU-Auslastung steigt (man muss das alles sogar noch im laufenden Betrieb komprimieren), was die allgemeine Systemreaktionsfähigkeit verringert.

Spaß beiseite, ich verstehe vollkommen, dass ich keine Offenbarungen gemacht habe. Das, was oben geschrieben ist, ist einfach eine Demonstration, wie man die optimalen Parameter für die Durchführung von Backups auswählen kann.

Denken Sie daran, dass alles, was Sie tun, auf Ihr eigenes Risiko geschieht. Überprüfen Sie Ihre Backups und vergessen Sie nicht DBCC CHECKDB.

Quelle: habr.com