Tunggu! Tunggu! Benar, ini bukan artikel lain tentang jenis sandaran SQL Server. Saya tidak akan bercakap tentang perbezaan antara model pemulihan dan cara menangani log yang terlalu besar.
Mungkin (mungkin sahaja), selepas membaca siaran ini, anda akan dapat memastikan bahawa sandaran yang dikeluarkan daripada anda menggunakan cara standard akan dialih keluar malam esok, baik, 1.5 kali lebih cepat. Dan hanya disebabkan oleh fakta bahawa anda menggunakan lebih sedikit parameter PANGKALAN DATA SANDARAN.
Jika kandungan siaran itu jelas kepada anda, saya minta maaf. Saya membaca semua yang Google dapat untuk frasa "sandaran pelayan sql habr", dan dalam satu artikel pun saya tidak dapati sebarang sebutan tentang fakta bahawa masa sandaran boleh dipengaruhi menggunakan parameter.
Saya akan segera menarik perhatian anda kepada komen Alexander Gladchenko ():
Jangan sekali-kali menukar parameter BUFFERCOUNT, BLOCKSIZE, MAXTTRANSFERSIZE dalam pengeluaran. Mereka dibuat hanya untuk menulis artikel sedemikian. Dalam amalan, anda akan menyingkirkan masalah ingatan dalam masa yang singkat.
Sudah tentu, ia akan menjadi menarik untuk menjadi kandungan yang paling bijak dan eksklusif, tetapi, malangnya, ini tidak berlaku. Terdapat kedua-dua artikel/siaran bahasa Inggeris dan bahasa Rusia (saya sentiasa keliru untuk memanggilnya dengan betul) dikhaskan untuk topik ini. Berikut adalah antara yang saya temui: , , .
Jadi, sebagai permulaan, saya akan melampirkan sintaks BACKUP yang sedikit dilucutkan daripada (by the way, saya menulis di atas tentang BACKUP DATABASE, tetapi semua ini terpakai pada kedua-dua sandaran log transaksi dan sandaran pembezaan, tetapi mungkin dengan kesan yang kurang jelas):
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 }
<...><β¦> - ini bermakna terdapat sesuatu di sana, tetapi saya mengalih keluarnya kerana sekarang ia tidak berkaitan dengan topik itu.
Bagaimanakah anda biasanya mengambil sandaran? Bagaimanakah mereka "mengajar" cara mengambil sandaran dalam berbilion-bilion artikel? Secara umum, jika saya perlu membuat sandaran sekali untuk beberapa pangkalan data yang tidak terlalu besar, saya secara automatik akan menulis sesuatu seperti ini:
BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--Π»Π°Π΄Π½ΠΎ, CHECKSUM Ρ Π½Π°ΠΏΠΈΡΠ°Π» ΡΠΎΠ»ΡΠΊΠΎ ΡΡΠΎΠ±Ρ ΠΊΠ°Π·Π°ΡΡΡΡ ΡΠΌΠ½Π΅Π΅Dan, secara umum, mungkin 75-90% daripada semua parameter yang biasanya disebut dalam artikel tentang sandaran disenaraikan di sini. Nah, terdapat juga INIT, SKIP. Adakah anda telah melawat MSDN? Pernahkah anda melihat bahawa terdapat pilihan untuk satu setengah skrin? saya pun nampak...
Anda mungkin sudah menyedari bahawa selanjutnya kita akan bercakap tentang tiga parameter yang kekal dalam blok kod pertama - BLOCKSIZE, BUFFERCOUNT dan MAXTRANSFERSIZE. Berikut ialah penerangan mereka dari MSDN:
SAIZ BLOK = { menyekat | @ pembolehubah_saiz blok } - menunjukkan saiz blok fizikal dalam bait. Saiz yang disokong ialah 512, 1024, 2048, 4096, 8192, 16, 384 dan 32 bait (768 KB). Nilai lalai ialah 65 untuk peranti pita dan 536 untuk peranti lain. Biasanya parameter ini tidak diperlukan kerana pernyataan BACKUP secara automatik memilih saiz blok yang sesuai untuk peranti. Menetapkan saiz blok secara eksplisit mengatasi pemilihan saiz blok automatik.
BUFFERCOUNT = { buffercount | @ buffercount_variable } - Mentakrifkan jumlah bilangan penimbal I/O yang akan digunakan untuk operasi sandaran. Anda boleh menentukan sebarang nilai integer positif, tetapi sebilangan besar penimbal boleh menyebabkan ralat kehabisan ingatan disebabkan ruang alamat maya yang berlebihan dalam proses Sqlservr.exe.
Jumlah ruang yang digunakan oleh penimbal ditentukan oleh formula berikut:
BUFFERCOUNT * MAXTRANSFERSIZE.
MAXTRANSFER = { maxtransfersize | @ maxtransfersize_variable } menentukan saiz paket data terbesar, dalam bait, untuk bertukar antara SQL Server dan media set sandaran. Gandaan 65 bait (536 KB) sehingga 64 bait (4 MB) disokong.
Saya bersumpah - Saya pernah membaca ini sebelum ini, tetapi saya tidak pernah terfikir sejauh mana kesannya terhadap produktiviti. Lebih-lebih lagi, nampaknya, saya perlu membuat semacam "keluar" dan mengakui bahawa walaupun sekarang saya tidak memahami sepenuhnya apa sebenarnya yang mereka lakukan. Saya mungkin perlu membaca lebih lanjut mengenai buffer I/O dan bekerja dengan cakera keras. Suatu hari nanti saya akan melakukan ini, tetapi buat masa ini saya hanya boleh menulis skrip yang akan menyemak bagaimana nilai-nilai ini mempengaruhi kelajuan sandaran diambil.
Saya membuat pangkalan data kecil, bersaiz kira-kira 10 GB, meletakkannya pada SSD, dan meletakkan direktori untuk sandaran pada HDD.
Saya mencipta jadual sementara untuk menyimpan keputusan (saya tidak mempunyainya sementara, jadi saya boleh menggali keputusan dengan lebih terperinci, tetapi anda tentukan sendiri):
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
);Prinsip skrip adalah mudah - gelung bersarang, setiap satunya mengubah nilai satu parameter, masukkan parameter ini ke dalam arahan BACKUP, simpan rekod terakhir dengan sejarah dari msdb.dbo.backupset, padam fail sandaran dan lelaran seterusnya . Memandangkan data pelaksanaan sandaran diambil dari set sandaran, ketepatannya agak hilang (tiada pecahan saat), tetapi kami akan bertahan dalam hal ini.
Mula-mula anda perlu mendayakan xp_cmdshell untuk memadamkan sandaran (kemudian jangan lupa untuk melumpuhkannya jika anda tidak memerlukannya):
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
GONah, sebenarnya:
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;
ENDJika anda tiba-tiba memerlukan penjelasan tentang apa yang berlaku di sini, tulis dalam komen atau PM. Buat masa ini, saya hanya akan memberitahu anda tentang parameter yang saya letakkan dalam PANGKALAN DATA SANDARAN.
Untuk BLOCKSIZE kami mempunyai senarai nilai "tertutup", dan saya tidak melakukan sandaran dengan BLOCKSIZE < 4KB. MAXTRANSFER mana-mana nombor yang merupakan gandaan 64KB - daripada 64KB kepada 4MB. Lalai pada sistem saya ialah 1024KB, saya mengambil 512 - 1024 - 2048 - 4096.
Ia lebih sukar dengan BUFFERCOUNT - ia boleh menjadi sebarang nombor positif, tetapi pautan berkata . Ia juga menyatakan cara untuk mendapatkan maklumat tentang BUFFERCOUNT mana sandaran sebenarnya dibuat - bagi saya ia adalah 7. Tidak ada gunanya mengurangkannya, dan had atas ditemui secara eksperimen - dengan BUFFERCOUNT = 896 dan MAXTRANSFERSIZE = 4194304 sandaran jatuh dengan ralat (kira-kira yang ditulis dalam pautan di atas):
Msg 3013, Tahap 16, Negeri 1, Baris 7 PANGKALAN DATA SANDARAN ditamatkan secara tidak normal.
Msg 701, Tahap 17, Negeri 123, Baris 7 Terdapat memori sistem yang tidak mencukupi dalam kumpulan sumber 'lalai' untuk menjalankan pertanyaan ini.
Sebagai perbandingan, saya mula-mula akan menunjukkan hasil menjalankan sandaran tanpa menyatakan sebarang parameter sama sekali:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;Nah, sandaran dan sandaran:
Memproses 1070072 halaman untuk pangkalan data 'bt', fail 'bt' pada fail 1.
Diproses 2 muka surat untuk pangkalan data 'bt', fail 'bt_log' pada fail 1.
PANGKALAN DATA SANDARAN berjaya memproses 1070074 halaman dalam 53.171 saat (157.227 MB/saat).
Skrip itu sendiri, menguji parameter, berfungsi dalam beberapa jam, semua ukuran telah dimasukkan . Dan berikut adalah pilihan keputusan dengan tiga masa pelaksanaan terbaik (saya cuba membuat graf yang bagus, tetapi dalam siaran saya perlu membuat kaitan dengan jadual, dan dalam ulasan tambahnya ).
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;
Perhatian, nota yang sangat penting daripada daripada :
Kami dengan yakin boleh mengatakan bahawa hubungan antara parameter dan kelajuan sandaran dalam julat nilai ini adalah rawak, tidak ada corak. Tetapi berpindah dari parameter terbina dalam jelas mempunyai kesan yang baik pada hasilnya
Itu. Hanya dengan menguruskan parameter BACKUP standard, keuntungan 2 kali ganda dalam masa penyingkiran sandaran: 26 saat, berbanding 53 pada permulaan. Ia tidak buruk, bukan? Tetapi kita perlu melihat apa yang berlaku dengan pemulihan. Bagaimana jika kini mengambil masa 4 kali lebih lama untuk pulih?
Mula-mula, mari kita ukur berapa lama masa yang diperlukan untuk memulihkan sandaran dengan tetapan lalai:
RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;Nah, anda sendiri tahu bahawa, cara-caranya ada, ganti bukan ganti, pemulihan bukan pemulihan. Dan saya melakukannya seperti ini:
Memproses 1070072 halaman untuk pangkalan data 'bt', fail 'bt' pada fail 1.
Diproses 2 muka surat untuk pangkalan data 'bt', fail 'bt_log' pada fail 1.
PULIHKAN PANGKALAN DATA berjaya memproses 1070074 halaman dalam 40.752 saat (205.141 MB/saat).
Sekarang saya akan cuba memulihkan sandaran yang diambil dengan BLOCKSIZE, BUFFERCOUNT dan MAXTRANSferSIZE yang telah diubah.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304PULIHKAN PANGKALAN DATA berjaya memproses 1070074 halaman dalam 32.283 saat (258.958 MB/saat).
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304PULIHKAN PANGKALAN DATA berjaya memproses 1070074 halaman dalam 32.682 saat (255.796 MB/saat).
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152PULIHKAN PANGKALAN DATA berjaya memproses 1070074 halaman dalam 32.091 saat (260.507 MB/saat).
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304PULIHKAN PANGKALAN DATA berjaya memproses 1070074 halaman dalam 32.401 saat (258.015 MB/saat).
Pernyataan PANGKALAN DATA PULIH tidak berubah semasa pemulihan; parameter ini tidak dinyatakan di dalamnya; SQL Server sendiri menentukannya daripada sandaran. Dan jelas bahawa walaupun dengan pemulihan boleh ada keuntungan - hampir 20% lebih cepat (Sejujurnya, saya tidak menghabiskan banyak masa untuk pemulihan, saya menjalankan beberapa sandaran "terpantas" dan memastikan bahawa tiada kemerosotan.).
Sekiranya berlaku, izinkan saya menjelaskan bahawa ini bukan beberapa parameter yang optimum untuk semua orang. Anda hanya boleh mendapatkan parameter optimum untuk diri sendiri dengan menguji. Saya mendapat keputusan ini, anda akan mendapat keputusan yang berbeza. Tetapi anda melihat bahawa anda boleh "menala" sandaran anda dan ia sebenarnya boleh membentuk dan menggunakan lebih pantas.
Saya juga amat mengesyorkan anda membaca dokumentasi sepenuhnya, kerana mungkin terdapat nuansa khusus untuk sistem anda.
Sejak saya mula menulis tentang sandaran, saya ingin segera menulis tentang satu lagi "pengoptimuman", yang lebih biasa daripada parameter "penalaan" (setakat yang saya faham, ia digunakan oleh sekurang-kurangnya beberapa utiliti sandaran, mungkin bersama-sama dengan parameter diterangkan sebelum ini), tetapi ia belum lagi diterangkan pada HabrΓ© sama ada.
Jika kita melihat baris kedua dalam dokumentasi, betul-betul di bawah PANGKALAN DATA SANDARAN, di sana kita lihat:
TO <backup_device> [ ,...n ]Apa yang anda fikir akan berlaku jika anda menentukan beberapa backup_devices? Sintaks membenarkannya. Dan perkara yang sangat menarik akan berlaku - sandaran hanya akan "disebar" ke beberapa peranti. Itu. setiap "peranti" secara individu akan menjadi tidak berguna, hilang satu, kehilangan keseluruhan sandaran. Tetapi bagaimana kesan calitan itu menjejaskan kelajuan sandaran?
Mari cuba buat sandaran pada dua "peranti" yang terletak bersebelahan dalam folder yang sama:
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak'
WITH COMPRESSION;Bapa-bapa dunia, mengapa ini dilakukan?
Memproses 1070072 halaman untuk pangkalan data 'bt', fail 'bt' pada fail 1.
Diproses 2 halaman untuk pangkalan data 'bt', fail 'bt'log' pada fail 1.
PANGKALAN DATA SANDARAN berjaya memproses 1070074 halaman dalam 40.092 saat (208.519 MB/saat).
Adakah sandaran menjadi 25% lebih pantas secara tiba-tiba? Bagaimana jika kita menambah beberapa peranti lagi?
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH COMPRESSION;PANGKALAN DATA SANDARAN berjaya memproses 1070074 halaman dalam 34.234 saat (244.200 MB/saat).
Secara keseluruhan, keuntungan adalah kira-kira 35% daripada masa mengambil sandaran hanya disebabkan oleh fakta bahawa sandaran ditulis kepada 4 fail pada satu cakera sekaligus. Saya menyemak nombor yang lebih besar - tidak ada keuntungan pada komputer riba saya, secara optimum - 4 peranti. Untuk anda - saya tidak tahu, anda perlu menyemak. Nah, dengan cara ini, jika anda mempunyai peranti ini - ini adalah cakera yang benar-benar berbeza, tahniah, keuntungan sepatutnya lebih ketara.
Sekarang mari kita bercakap tentang bagaimana untuk memulihkan kebahagiaan ini. Untuk melakukan ini, anda perlu menukar arahan pemulihan dan menyenaraikan semua peranti:
RESTORE DATABASE [bt]
FROM
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH REPLACE, RECOVERY;PULIHKAN PANGKALAN DATA berjaya memproses 1070074 halaman dalam 38.027 saat (219.842 MB/saat).
Sedikit lebih cepat, tetapi di suatu tempat yang dekat, tidak penting. Secara umum, sandaran dikeluarkan dengan lebih cepat dan dipulihkan dengan cara yang sama - berjaya? Bagi saya, ia agak berjaya. ini adalah penting, jadi saya ulangi - jika anda jika anda kehilangan sekurang-kurangnya satu daripada fail ini, anda kehilangan keseluruhan sandaran.
Jika anda melihat dalam log pada maklumat sandaran yang dipaparkan menggunakan Trace Flags 3213 dan 3605, anda akan mendapati bahawa apabila membuat sandaran kepada beberapa peranti, sekurang-kurangnya bilangan BUFFERCOUNT meningkat. Mungkin, anda boleh cuba memilih parameter yang lebih optimum untuk BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, tetapi saya tidak berjaya serta-merta, dan saya terlalu malas untuk menjalankan ujian sedemikian sekali lagi, tetapi untuk bilangan fail yang berbeza. Dan ia memalukan tentang roda. Jika anda ingin menganjurkan ujian sedemikian di rumah, tidak sukar untuk membuat semula skrip.
Akhirnya, mari kita bercakap tentang harga. Jika sandaran dialih keluar selari dengan kerja pengguna, anda perlu mengambil pendekatan yang sangat bertanggungjawab untuk menguji, kerana jika sandaran dikeluarkan lebih cepat, cakera menjadi lebih tegang, beban pada pemproses meningkat (anda masih perlu memampatkan ia dengan cepat), dan oleh itu, tindak balas keseluruhan sistem berkurangan.
Hanya bergurau, tetapi saya faham dengan baik bahawa saya tidak membuat sebarang pendedahan. Apa yang ditulis di atas hanyalah demonstrasi bagaimana anda boleh memilih parameter optimum untuk mengambil sandaran.
Ingat bahawa semua yang anda lakukan dilakukan atas risiko dan risiko anda sendiri. Semak sandaran anda dan jangan lupa tentang DBCC CHECKDB.
Sumber: www.habr.com