MS SQL Server : SAUVEGARDE sous stéroïdes

Attendez! Attendez! Certes, il ne s'agit pas d'un autre article sur les types de sauvegardes SQL Server. Je ne parlerai même pas des différences entre les modèles de récupération et de la manière de gérer un journal envahi.

Peut-être (juste peut-être), après avoir lu cet article, serez-vous en mesure de vous assurer que la sauvegarde qui vous est supprimée à l'aide de moyens standard sera supprimée demain soir, enfin, 1.5 fois plus rapidement. Et seulement parce que vous utilisez un peu plus de paramètres BACKUP DATABASE.

Si le contenu du message vous paraissait évident, je suis désolé. J'ai lu tout ce que Google a trouvé pour l'expression « sauvegarde du serveur Habr SQL », et dans aucun article je n'ai trouvé de mention du fait que le temps de sauvegarde peut être influencé d'une manière ou d'une autre à l'aide de paramètres.

J'attirerai immédiatement votre attention sur le commentaire d'Alexandre Gladchenko (@mssqlhelp):

Ne modifiez jamais les paramètres BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE en production. Ils sont destinés uniquement à la rédaction de tels articles. En pratique, vous vous débarrasserez des problèmes de mémoire en un rien de temps.

Ce serait bien sûr cool d’être le plus intelligent et de publier du contenu exclusif, mais malheureusement, ce n’est pas le cas. Il existe des articles/posts en anglais et en russe (je ne sais toujours pas comment les appeler correctement) consacrés à ce sujet. Voici quelques-uns de ceux que j'ai rencontrés : temps, два, trois (sur sql.ru).

Donc, pour commencer, je joindrai une syntaxe BACKUP légèrement allégée de MSDN (d'ailleurs, j'ai écrit ci-dessus à propos de BACKUP DATABASE, mais tout cela s'applique à la fois à la sauvegarde du journal des transactions et à la sauvegarde différentielle, mais peut-être avec un effet moins évident) :

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 }
<...>

<…> - cela signifie qu'il y avait quelque chose là-bas, mais je l'ai supprimé car maintenant ce n'est plus pertinent pour le sujet.

Comment effectuez-vous habituellement une sauvegarde ? Comment « enseignent-ils » comment effectuer des sauvegardes dans des milliards d’articles ? En général, si je dois faire une sauvegarde unique d'une base de données pas très volumineuse, j'écrirai automatiquement quelque chose comme ceci :

BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--ладно, CHECKSUM я написал только чтобы казаться умнее

Et, en général, probablement 75 à 90 % de tous les paramètres habituellement mentionnés dans les articles sur les sauvegardes sont répertoriés ici. Eh bien, il y a aussi INIT, SKIP. Avez-vous visité MSDN ? Avez-vous vu qu'il existe des options pour un écran et demi ? J'ai aussi vu...

Vous avez probablement déjà réalisé que nous parlerons plus loin des trois paramètres restés dans le premier bloc de code - BLOCKSIZE, BUFFERCOUNT et MAXTRANSFERSIZE. Voici leurs descriptions sur MSDN :

TAILLE DE BLOC = { taille de bloc | @ taille_bloc_variable } - indique la taille du bloc physique en octets. Les tailles prises en charge sont 512, 1024 2048, 4096 8192, 16 384, 32 768, 65 536, 64 65 et 536 512 octets (XNUMX Ko). La valeur par défaut est XNUMX XNUMX pour les périphériques de bande et XNUMX pour les autres périphériques. Généralement, ce paramètre n'est pas nécessaire car l'instruction BACKUP sélectionne automatiquement la taille de bloc appropriée pour le périphérique. La définition de la taille du bloc remplace explicitement la sélection automatique de la taille du bloc.

COMPTE TAMPON = { nombre de tampons | @ buffercount_variable } - Définit le nombre total de tampons d'E/S qui seront utilisés pour l'opération de sauvegarde. Vous pouvez spécifier n'importe quelle valeur entière positive, mais un grand nombre de tampons peut provoquer une erreur de mémoire insuffisante en raison d'un espace d'adressage virtuel excessif dans le processus Sqlservr.exe.

La quantité totale d'espace utilisée par les tampons est déterminée par la formule suivante : BUFFERCOUNT * MAXTRANSFERSIZE.

TAILLE DE TRANSFERT MAXIMALE = { taille de transfert maximale | @ maxtransfersize_variable } spécifie la plus grande taille de paquet de données, en octets, à échanger entre SQL Server et le support du jeu de sauvegarde. Les multiples de 65 536 octets (64 Ko) jusqu'à 4 194 304 octets (4 Mo) sont pris en charge.

Je le jure, j'ai déjà lu ceci, mais je n'ai jamais pensé à quel point ils pourraient avoir un impact sur la productivité. De plus, apparemment, je dois faire une sorte de « coming-out » et admettre que même maintenant je ne comprends pas vraiment ce qu'ils font exactement. J'ai probablement besoin d'en savoir plus sur les E/S tamponnées et l'utilisation d'un disque dur. Un jour, je le ferai, mais pour l'instant, je peux simplement écrire un script qui vérifiera comment ces valeurs affectent la vitesse à laquelle la sauvegarde est effectuée.

J'ai créé une petite base de données d'environ 10 Go, je l'ai placée sur le SSD et j'ai mis le répertoire de sauvegarde sur le disque dur.

Je crée une table temporaire pour stocker les résultats (je ne l'ai pas temporaire, donc je peux approfondir les résultats plus en détail, mais vous décidez vous-même) :

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

Le principe du script est simple - des boucles imbriquées, dont chacune modifie la valeur d'un paramètre, insérez ces paramètres dans la commande BACKUP, enregistrez le dernier enregistrement avec l'historique de msdb.dbo.backupset, supprimez le fichier de sauvegarde et l'itération suivante . Étant donné que les données d'exécution de la sauvegarde sont extraites du jeu de sauvegarde, la précision est quelque peu perdue (il n'y a pas de fractions de secondes), mais nous y survivrons.

Vous devez d’abord activer xp_cmdshell pour supprimer les sauvegardes (puis n’oubliez pas de le désactiver si vous n’en avez pas besoin) :

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

Eh bien, en fait :

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

Si vous avez soudainement besoin d'éclaircissements sur ce qui se passe ici, écrivez dans les commentaires ou en MP. Pour l’instant, je ne vous parlerai que des paramètres que j’ai mis dans BACKUP DATABASE.

Pour BLOCKSIZE, nous avons une liste de valeurs « fermée » et je n'ai pas effectué de sauvegarde avec BLOCKSIZE < 4 Ko. MAXTRANSFERSIZE tout nombre multiple de 64 Ko - de 64 Ko à 4 Mo. La valeur par défaut sur mon système est 1024 Ko, j'ai pris 512 - 1024 - 2048 - 4096.

C'était plus difficile avec BUFFERCOUNT - il peut s'agir de n'importe quel nombre positif, mais le lien indique comment est-il calculé dans BACKUP DATABASE et pourquoi les grandes valeurs sont-elles dangereuses ?. Il indique également comment obtenir des informations sur le BUFFERCOUNT avec lequel la sauvegarde est réellement effectuée - pour moi, c'est 7. Cela ne servait à rien de la réduire, et la limite supérieure a été découverte expérimentalement - avec BUFFERCOUNT = 896 et MAXTRANSFERSIZE = 4194304, la sauvegarde est tombée avec une erreur (dont il est écrit dans le lien ci-dessus) :

Msg 3013, niveau 16, état 1, ligne 7 LA BASE DE DONNÉES DE SAUVEGARDE se termine anormalement.

Msg 701, niveau 17, état 123, ligne 7 La mémoire système est insuffisante dans le pool de ressources « par défaut » pour exécuter cette requête.

À titre de comparaison, je vais d'abord montrer les résultats de l'exécution d'une sauvegarde sans spécifier aucun paramètre :

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

Eh bien, sauvegarde et sauvegarde :

1070072 pages traitées pour la base de données 'bt', fichier 'bt' sur le fichier 1.

Traité 2 pages pour la base de données 'bt', fichier 'bt_log' sur le fichier 1.

BACKUP DATABASE a traité avec succès 1070074 53.171 157.227 pages en XNUMX secondes (XNUMX Mo/s).

Le script lui-même, testant les paramètres, a fonctionné en quelques heures, toutes les mesures étaient en table google. Et voici une sélection de résultats avec les trois meilleurs temps d'exécution (j'ai essayé de faire un joli graphique, mais dans le post je me contenterai d'un tableau, et dans les commentaires @mixsture ajoutée graphismes très sympas).

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;

Attention, une note très importante de @mixsture de commentaires:

Nous pouvons affirmer avec certitude que la relation entre les paramètres et la vitesse de sauvegarde dans ces plages de valeurs est aléatoire, il n'y a pas de modèle. Mais s'éloigner des paramètres intégrés a évidemment eu un bon effet sur le résultat

Ceux. Ce n'est qu'en gérant les paramètres standards de BACKUP que le temps de suppression des sauvegardes a été multiplié par 2 : 26 secondes, contre 53 au début. Ce n'est pas mal, non ? Mais nous devons voir ce qui se passe avec la restauration. Et s’il fallait désormais 4 fois plus de temps pour récupérer ?

Tout d’abord, mesurons le temps nécessaire pour restaurer une sauvegarde avec les paramètres par défaut :

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

Eh bien, vous le savez vous-même, les moyens existent, remplacer n'est pas remplacer, la récupération n'est pas la récupération. Et je fais comme ça :

1070072 pages traitées pour la base de données 'bt', fichier 'bt' sur le fichier 1.

Traité 2 pages pour la base de données 'bt', fichier 'bt_log' sur le fichier 1.

RESTORE DATABASE a traité avec succès 1070074 40.752 205.141 pages en XNUMX secondes (XNUMX Mo/s).

Je vais maintenant essayer de restaurer les sauvegardes effectuées avec BLOCKSIZE, BUFFERCOUNT et MAXTRANSFERSIZE modifiés.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE a traité avec succès 1070074 32.283 258.958 pages en XNUMX secondes (XNUMX Mo/s).

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE a traité avec succès 1070074 32.682 255.796 pages en XNUMX secondes (XNUMX Mo/s).

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

RESTORE DATABASE a traité avec succès 1070074 32.091 260.507 pages en XNUMX secondes (XNUMX Mo/s).

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE a traité avec succès 1070074 32.401 258.015 pages en XNUMX secondes (XNUMX Mo/s).

L'instruction RESTORE DATABASE ne change pas pendant la récupération ; ces paramètres n'y sont pas spécifiés ; SQL Server lui-même les détermine à partir de la sauvegarde. Et il est clair que même avec une récupération, il peut y avoir un gain - près de 20 % plus rapide (Pour être honnête, je n'ai pas consacré beaucoup de temps à la récupération, j'ai effectué plusieurs sauvegardes « les plus rapides » et je me suis assuré qu'il n'y avait pas de détérioration.).

Juste au cas où, permettez-moi de préciser que ce ne sont pas des paramètres optimaux pour tout le monde. Vous ne pouvez obtenir les paramètres optimaux que par des tests. J'ai obtenu ces résultats, vous en obtiendrez des différents. Mais vous voyez que vous pouvez « ajuster » vos sauvegardes et qu’elles peuvent réellement se former et se déployer plus rapidement.

Je vous recommande également fortement de lire la documentation dans son intégralité, car il peut y avoir des nuances propres à votre système.

Depuis que j'ai commencé à écrire sur les sauvegardes, je souhaite immédiatement écrire sur une autre « optimisation », qui est plus courante que les paramètres de « réglage » (pour autant que je sache, elle est utilisée par au moins certains utilitaires de sauvegarde, peut-être avec les paramètres décrit plus haut), mais il n’a pas encore été décrit sur Habré.

Si nous regardons la deuxième ligne de la documentation, juste sous BACKUP DATABASE, nous y voyons :

TO <backup_device> [ ,...n ]

À votre avis, que se passera-t-il si vous spécifiez plusieurs backup_devices ? La syntaxe le permet. Et une chose très intéressante se produira : la sauvegarde sera simplement « répartie » sur plusieurs appareils. Ceux. chaque « appareil » individuellement sera inutile, vous en perdrez un, perdrez toute la sauvegarde. Mais comment une telle maculage affectera-t-elle la vitesse de sauvegarde ?

Essayons de faire une sauvegarde sur deux « appareils » situés côte à côte dans le même dossier :

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

Pères du monde, pourquoi cela se fait-il ?

1070072 pages traitées pour la base de données 'bt', fichier 'bt' sur le fichier 1.

Traité 2 pages pour la base de données 'bt', le fichier 'bt'log' sur le fichier 1.

BACKUP DATABASE a traité avec succès 1070074 40.092 208.519 pages en XNUMX secondes (XNUMX Mo/s).

La sauvegarde est-elle devenue 25 % plus rapide à l'improviste ? Et si nous ajoutions quelques appareils supplémentaires ?

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

BACKUP DATABASE a traité avec succès 1070074 34.234 244.200 pages en XNUMX secondes (XNUMX Mo/s).

Au total, le gain est d'environ 35 % du temps de sauvegarde uniquement du fait que la sauvegarde est écrite sur 4 fichiers sur un disque à la fois. J'ai vérifié un plus grand nombre - il n'y a aucun gain sur mon ordinateur portable, de manière optimale - 4 appareils. Pour vous, je ne sais pas, vous devez vérifier. Eh bien, au fait, si vous possédez ces appareils, ce sont des disques vraiment différents, félicitations, le gain devrait être encore plus important.

Parlons maintenant de la manière de restaurer ce bonheur. Pour ce faire, vous devrez modifier la commande de récupération et lister tous les appareils :

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 a traité avec succès 1070074 38.027 219.842 pages en XNUMX secondes (XNUMX Mo/s).

Un peu plus rapide, mais quelque part proche, pas significatif. En général, la sauvegarde est supprimée plus rapidement et restaurée de la même manière – succès ? Quant à moi, c’est une sacrée réussite. Ce important, alors je le répète - si vous si vous perdez au moins un de ces fichiers, vous perdez la totalité de la sauvegarde.

Si vous regardez dans le journal les informations de sauvegarde affichées à l'aide des indicateurs de trace 3213 et 3605, vous remarquerez que lors de la sauvegarde sur plusieurs appareils, au moins le nombre de BUFFERCOUNT augmente. Vous pouvez probablement essayer de sélectionner des paramètres plus optimaux pour BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, mais je n'ai pas réussi tout de suite et j'étais trop paresseux pour refaire de tels tests, mais pour un nombre de fichiers différent. Et c'est dommage pour les roues. Si vous souhaitez organiser de tels tests à la maison, il n'est pas difficile de refaire le script.

Enfin, parlons du prix. Si la sauvegarde est supprimée parallèlement au travail des utilisateurs, vous devez adopter une approche très responsable des tests, car si la sauvegarde est supprimée plus rapidement, les disques sont davantage sollicités, la charge sur le processeur augmente (vous devez toujours compresser à la volée), et par conséquent, la réactivité globale du système diminue.

Je plaisante, mais je comprends parfaitement que je n’ai fait aucune révélation. Ce qui est écrit ci-dessus est simplement une démonstration de la façon dont vous pouvez sélectionner les paramètres optimaux pour effectuer des sauvegardes.

N'oubliez pas que tout ce que vous faites est fait à vos risques et périls. Vérifiez vos sauvegardes et n'oubliez pas DBCC CHECKDB.

Source: habr.com