MS SQL Server: BACKUP auf Steroiden

Warten Sie! Warten Sie! Es ist wirklich kein weiterer Artikel über SQL Server Backup-Typen. Ich werde nicht einmal auf die Unterschiede der Wiederherstellungsmodelle eingehen oder wie man mit einem gewachsenen „Log“ umgeht.

Möglicherweise (nur möglicherweise) können Sie nach dem Lesen dieses Beitrags so vorgehen, dass das Backup, das Sie mit den Standardwerkzeugen erstellen, morgen Nacht etwa 1,5-mal schneller ausgeführt wird. Und das nur, weil Sie ein wenig mehr BACKUP DATABASE-Parameter verwenden.

Wenn der Inhalt des Beitrags für Sie offensichtlich war – Entschuldigung. Ich habe alles gelesen, was Google zur Phrase „habr sql server backup“ finden konnte, und in keinem Artikel fand ich einen Hinweis darauf, dass man das Backup mit Parametern irgendwie beeinflussen kann.

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

Ändern Sie niemals die Parameter BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE im Produktivbetrieb. Sie sind nur für das Schreiben solcher Artikel gedacht. In der Praxis werden Sie damit Probleme mit dem Speicher bekommen.

Es wäre natürlich großartig, der Klügste zu sein und exklusiven Inhalt zu präsentieren, aber das ist leider nicht der Fall. Es gibt sowohl englisch- als auch russischsprachige Artikel/Beiträge (ich verwirre mich immer, wie ich sie richtig nennen soll), die sich mit diesem Thema beschäftigen. Hier ist ein Teil dessen, was ich gefunden habe: eins, zwei, drei (auf sql.ru).

Also werde ich zu Beginn eine etwas gekürzte Syntax für BACKUP aus MSDN (übrigens, ich habe da oben über BACKUP DATABASE geschrieben, aber das gilt auch für das Backup von Transaktionsprotokollen und für inkrementelle Backups, aber vermutlich 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, ich es aber entfernt habe, weil es momentan nicht zum Thema gehört.

Wie erstellen Sie normalerweise ein Backup? Wie „lehren“ Milliarden von Artikeln, ein Backup zu erstellen? Wenn ich einmal ein Backup einer nicht sehr großen Datenbank erstellen muss, würde ich automatisch etwas in dieser Art schreiben:

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

Und hier sind wahrscheinlich 75-90 % aller Parameter aufgezählt, die normalerweise in Artikel über Backups erwähnt werden. Da gibt es INIT, SKIP und so weiter. Waren Sie schon in MSDN? Haben Sie gesehen, dass es dort Optionen für anderthalb Bildschirme gibt? Ich habe das auch gesehen…

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

BLOCKSIZE = { blocksize | @ blocksize_variable } gibt die Größe des physischen Blocks in Bytes an. Unterstützte Größen sind 512, 1024, 2048, 4096, 8192, 16 384, 32 768 und 65 536 Bytes (64 KB). Der Standardwert beträgt 65 536 für Bandgeräte und 512 für andere Geräte. In der Regel ist es nicht erforderlich, diesen Parameter zu setzen, da der Befehl BACKUP automatisch die Blockgröße wählt, die zum Gerät passt. Eine explizite Einstellung der Blockgröße überschreibt die automatische Auswahl der Blockgröße.

BUFFERCOUNT = { buffercount | @ buffercount_variable } bestimmt die Gesamtzahl der Eingabe-/Ausgabepuffer, die für die Sicherungsoperation verwendet werden. Es kann jeder positive ganzzahlige Wert angegeben werden, allerdings kann eine große Anzahl von Puffern einen Speichermangel aufgrund des übermäßigen virtuellen Adressraums im Sqlservr.exe-Prozess verursachen.

Das gesamte von den Puffern genutzte Volumen wird durch folgende Formel bestimmt: BUFFERCOUNT * MAXTRANSFERSIZE.

MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } gibt das maximale Datenpaketvolumen in Bytes an, das für den Datenaustausch zwischen SQL Server und dem Mediengerät des Sicherungssatzes verwendet wird. Unterstützte Werte sind Vielfache von 65 536 Bytes (64 KB) bis zu 4 194 304 Bytes (4 MB).

Ich schwöre, ich habe das vorher schon gelesen, aber es ist mir 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 auch jetzt nicht ganz verstehe, was genau sie bewirken. Vielleicht sollte ich mehr über die gepufferte Ein- und Ausgabe sowie die Arbeit mit Festplatten lesen. Irgendwann werde ich das tun, aber im Moment kann ich einfach ein Skript schreiben, das überprüft, wie sich diese Werte auf die Geschwindigkeit auswirken, mit der das Backup erstellt wird.

Ich habe eine kleine Datenbank erstellt, die etwa 10 GB groß ist, sie auf eine SSD gelegt und das Verzeichnis für die Backups auf eine HDD gelegt.

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 das müssen Sie 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
);

Das Prinzip des Skripts ist einfach – verschachtelte Schleifen, von denen jede den Wert eines Parameters ändert, ich übergebe diese Parameter in den BACKUP-Befehl, speichere den letzten Datensatz mit der Historie aus msdb.dbo.backupset, lösche die Sicherungsdatei und mache mit der nächsten Iteration weiter. Da die Daten zur Ausführung des Backups aus backupset stammen, verliert man etwas an Genauigkeit (es fehlen Bruchteile von Sekunden), aber das werden wir überstehen.

Zuerst müssen Sie die Verwendung von xp_cmdshell erlauben, um Backups zu löschen (vergessen Sie nicht, es später abzuschalten, falls 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 tatsächlich:

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;

\t/* BUFFERCOUNT values */
DECLARE @bc     int = 7,
        @min_bc int = 7,
        @max_bc int = 800;

\t/* 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

Wenn Sie eine Erklärung dazu benötigen, was hier passiert – schreiben Sie in die Kommentare oder per privater Nachricht. Bis jetzt werde ich nur über die Parameter sprechen, die ich in BACKUP DATABASE übergebe.

Für BLOCKSIZE haben wir eine „geschlossene“ Liste von Werten, und ich habe kein Backup mit BLOCKSIZE < 4KB durchgeführt. MAXTRANSFERSIZE kann jede Zahl, die ein Vielfaches von 64KB ist, von 64KB bis 4MB sein. Standardmäßig sind es auf meinem System 1024KB, ich habe 512 — 1024 — 2048 — 4096 gewählt.

Schwieriger war es mit BUFFERCOUNT — er kann eine beliebige positive Zahl sein, aber im Link steht, wie er in BACKUP DATABASE berechnet wird und warum große Werte gefährlich sind.Dort steht auch, wie man Informationen darüber erhält, mit welchem BUFFERCOUNT tatsächlich ein Backup erstellt wird — bei mir sind das 7. Es hatte keinen Sinn, ihn zu verringern, und die obere Grenze wurde empirisch gefunden — bei BUFFERCOUNT = 896 und MAXTRANSFERSIZE = 4194304 schlug das Backup fehl mit einer Fehlermeldung (die im obigen Link beschrieben ist):

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 zunächst die Ergebnisse der Durchführung eines Backups ohne Angabe von Parametern überhaupt:

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

Nun, ein Backup ist ein Backup:

Es wurden 1070072 Seiten für die Datenbank ‚bt‘, Datei ‚bt‘ in Datei 1 verarbeitet.

Es wurden 2 Seiten für die Datenbank ‚bt‘, Datei ‚bt_log‘ in Datei 1 verarbeitet.

BACKUP DATABASE hat erfolgreich 1070074 Seiten in 53,171 Sekunden verarbeitet (157,227 MB/sec).

Das Skript, das die Parameter testet, hat ein paar Stunden gebraucht, alle Messungen sind in Google Sheets.Und hier ist eine Auswahl der Ergebnisse, die die drei besten Ausführungszeiten haben (ich habe versucht, ein schönes Diagramm zu erstellen, aber im Beitrag muss ich mit einer Tabelle auskommen, und in den Kommentaren @mixsture hinzugefügt 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 aus Kommentar:

man kann mit Sicherheit sagen, dass die Beziehung zwischen den Parametern und der Geschwindigkeit des Backups in diesen Grenzen zufällig ist, es gibt keine Regelmäßigkeit. Aber die Abweichung von den Standardparametern hat offensichtlich die Ergebnisse positiv beeinflusst.

Das heißt, allein durch die Verwaltung der Standardparameter BACKUP wurde die Zeit für die Erstellung des Backups um das Doppelte verkürzt: 26 Sekunden gegenüber 53 zu Beginn. Das ist doch nicht schlecht, oder? Aber wir müssen sehen, wie es mit der Wiederherstellung aussieht. Was ist, wenn es jetzt viermal länger dauert, um wiederherzustellen?

Zunächst messen wir, wie lange die Wiederherstellung des Backups mit den Standardeinstellungen dauert:

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

Nun, das wisst ihr selbst, wie es dort ist, replace oder nicht replace, recovery oder nicht recovery. Und bei mir wird es so ausgeführt:

Es wurden 1070072 Seiten für die Datenbank ‚bt‘, Datei ‚bt‘ in Datei 1 verarbeitet.

Es wurden 2 Seiten für die Datenbank ‚bt‘, Datei ‚bt_log‘ in Datei 1 verarbeitet.

DIE WIEDERHERSTELLUNG DER DATABANK wurde erfolgreich abgeschlossen: 1070074 Seiten in 40,752 Sekunden verarbeitet (205,141 MB/Sek).

Jetzt werde ich versuchen, die Backups wiederherzustellen, die mit geänderten BLOCKSIZE, BUFFERCOUNT und MAXTRANSFERSIZE erstellt wurden.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

DIE WIEDERHERSTELLUNG DER DATABANK wurde erfolgreich abgeschlossen: 1070074 Seiten in 32,283 Sekunden verarbeitet (258,958 MB/Sek).

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

DIE WIEDERHERSTELLUNG DER DATABANK wurde erfolgreich abgeschlossen: 1070074 Seiten in 32,682 Sekunden verarbeitet (255,796 MB/Sek).

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

DIE WIEDERHERSTELLUNG DER DATABANK wurde erfolgreich abgeschlossen: 1070074 Seiten in 32,091 Sekunden verarbeitet (260,507 MB/Sek).

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

DIE WIEDERHERSTELLUNG DER DATABANK wurde erfolgreich abgeschlossen: 1070074 Seiten in 32,401 Sekunden verarbeitet (258,015 MB/Sek).

Der Befehl RESTORE DATABASE verändert sich bei der Wiederherstellung nicht; diese Parameter werden nicht angegeben, SQL Server bestimmt sie automatisch anhand des Backups. Und es ist offensichtlich, dass sogar bei der Wiederherstellung Zeitersparnis möglich ist — praktisch 20 % schneller (Ehrlich gesagt habe ich nicht viel Zeit mit der Wiederherstellung verbracht, ich habe nur ein paar der "schnellsten" Backups getestet und festgestellt, dass es keine Verschlechterung gibt.).

Nur zur Sicherheit weise ich darauf hin — hier werden keine optimalen Parameter für alle beschrieben. Optimale Werte für sich selbst können Sie nur durch Tests erhalten. Ich habe solche Ergebnisse erzielt, Sie werden andere erhalten. Aber Sie sehen, dass Sie Ihre Backups „optimieren“ können und sie tatsächlich schneller erstellt und wiederhergestellt werden können.

Ich empfehle Ihnen dringend, die Dokumentation vollständig zu lesen, denn für Ihr System können besondere Anforderungen gelten.

Da ich gerade über Backups schreibe, möchte ich sofort noch eine andere „Optimierung“ erwähnen, die häufiger vorkommt als die „Feinabstimmung“ von Parametern (die, wie ich verstehe, von mindestens einem Teil der Backup-Utilities verwendet wird, möglicherweise zusammen mit den zuvor beschriebenen Parametern), aber auf Habré wurde sie bisher nicht behandelt.

Wenn man sich die zweite Zeile in der Dokumentation ansieht, direkt unter BACKUP DATABASE, sieht man:

TO  [ ,...n ]

Was denken Sie, passiert, wenn man mehrere backup_device angibt? Die Syntax erlaubt es. Und es wird eine sehr interessante Sache passieren — das Backup wird einfach auf mehrere Geräte verteilt. Jedes „Gerät“ wird für sich genommen nutzlos sein, haben Sie eines verloren, haben Sie das gesamte Backup verloren. Aber wie wird sich diese Verteilung auf die Geschwindigkeit der Datensicherung auswirken?

Versuchen wir, ein Backup auf zwei „Geräte“ zu machen, die nebeneinander in demselben Ordner liegen:

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

Oh mein Gott, was geschieht hier?

Es wurden 1070072 Seiten für die Datenbank ‚bt‘, Datei ‚bt‘ in Datei 1 verarbeitet.

Verarbeitete 2 Seiten für die Datenbank ‚bt‘, Datei ‚btprotokolliert‘ in Datei 1.

Datenbanksicherung erfolgreich mit 1070074 Seiten in 40,092 Sekunden (208,519 MB/Sek.) verarbeitet.

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

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

Datenbanksicherung erfolgreich mit 1070074 Seiten in 34,234 Sekunden (244,200 MB/Sek.) verarbeitet.

Insgesamt beträgt die Zeitersparnis etwa 35 % beim Sichern, nur weil die Sicherung gleichzeitig in 4 Dateien auf derselben Festplatte geschrieben wird. Ich habe mehr getestet — auf meinem Laptop gab es keine Verbesserung, optimal sind 4 Geräte. Für euch — ich weiß nicht, das muss man testen. Übrigens, wenn diese Geräte wirklich verschiedene Festplatten sind, dann Herzlichen Glückwunsch, die Einsparung sollte noch erheblich sein.

Jetzt reden wir darüber, wie man diesen Glücksfall wiederherstellt. Dafür muss das Wiederherstellungskommando geändert und alle Geräte aufgelistet werden:

RESTORE DATABASE [bt]
FROM 
    DISK = 'D:SQLServerbackupbt1.bak',
    DISK = 'D:SQLServerbackupbt2.bak',
    DISK = 'D:SQLServerbackupbt3.bak',
    DISK = 'D:SQLServerbackupbt4.bak'
WITH REPLACE, RECOVERY;

Datenbankwiederherstellung erfolgreich mit 1070074 Seiten in 38,027 Sekunden (219,842 MB/Sek.) verarbeitet.

Ein bisschen schneller, aber nah dran, nicht signifikant. Insgesamt wird die Sicherung schneller durchgeführt, und die Wiederherstellung erfolgt ebenso — Erfolg? Meiner Meinung nach ist das ein durchaus beachtlicher Erfolg. Das ist wichtig, daher wiederhole ich — wenn ihr auch nur eine dieser Dateien verliert — verliert ihr die gesamte Sicherung..

Wenn man die Informationen über die Sicherung im Protokoll, die mittels Trace Flag 3213 und 3605 ausgegeben werden, betrachtet, kann man feststellen, dass bei der Sicherung auf mehrere Geräte zumindest die Anzahl von BUFFERCOUNT steigt. Wahrscheinlich kann man versuchen, die optimaleren Parameter auch für BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE zu finden, aber ich habe es beim ersten Mal nicht geschafft, und erneut solche Tests mit unterschiedlichen Dateimengen wollte ich mir nicht antun. Und die Festplatten sind auch zu schade. Wenn ihr so ein Test bei euch organisieren möchtet, ist es nicht schwer, das Skript anzupassen.

Am Ende sprechen wir über den Preis. Wenn die Sicherung parallel zum Benutzereinsatz erfolgt — muss man mit äußerster Sorgfalt testen, da eine schnellere Sicherung die Festplatten stärker belastet und die CPU-Last steigt (man muss das alles auch in Echtzeit komprimieren), wodurch die allgemeine Systemreaktionsfähigkeit abnimmt.

Witze hin oder her, ich verstehe durchaus, dass ich keine Enthüllungen gemacht habe. Das, was oben geschrieben steht, ist einfach eine Demonstration, wie man optimale Parameter für die Erstellung von Backups auswählen kann.

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

Quelle: habr.com