Tunggu! Tunggu! Benar, ini bukan artikel lain tentang jenis cadangan SQL Server. Saya bahkan tidak akan membicarakan perbedaan antara model pemulihan dan cara menangani log yang tumbuh terlalu besar.
Mungkin (mungkin saja), setelah membaca postingan ini, Anda akan dapat memastikan bahwa cadangan yang dihapus dari Anda menggunakan cara standar akan dihapus besok malam, 1.5 kali lebih cepat. Dan hanya karena Anda menggunakan lebih banyak parameter BACKUP DATABASE.
Jika isi postingannya jelas bagi Anda, saya minta maaf. Saya membaca semua yang Google dapatkan untuk frasa "cadangan server habr sql", dan tidak dalam satu artikel pun saya menemukan penyebutan fakta bahwa waktu pencadangan dapat dipengaruhi menggunakan parameter.
Saya akan segera menarik perhatian Anda pada komentar Alexander Gladchenko ():
Jangan pernah mengubah parameter BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE dalam produksi. Mereka dibuat hanya untuk menulis artikel semacam itu. Dalam praktiknya, Anda akan menghilangkan masalah memori dalam waktu singkat.
Tentu saja keren menjadi orang terpintar dan memposting konten eksklusif, tetapi sayangnya tidak demikian. Ada artikel/postingan berbahasa Inggris dan Rusia (saya selalu bingung harus menyebutnya apa dengan benar) yang membahas topik ini. Berikut beberapa di antaranya yang saya temui: , , .
Jadi, untuk memulainya, saya akan melampirkan sintaks BACKUP yang sedikit dipreteli dari (omong-omong, saya menulis di atas tentang BACKUP DATABASE, tetapi semua ini berlaku untuk cadangan log transaksi dan cadangan diferensial, tetapi mungkin dengan efek 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 }
<...><…> - artinya ada sesuatu disana, tapi saya hapus karena sekarang tidak relevan dengan topik.
Bagaimana biasanya Anda membuat cadangan? Bagaimana mereka “mengajarkan” cara membuat cadangan miliaran artikel? Secara umum, jika saya perlu membuat cadangan satu kali dari beberapa database yang tidak terlalu besar, saya akan secara otomatis 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% dari semua parameter yang biasanya disebutkan dalam artikel tentang pencadangan tercantum di sini. Nah, ada juga INIT, SKIP. Sudahkah Anda mengunjungi MSDN? Pernahkah Anda melihat bahwa ada opsi untuk satu setengah layar? Saya juga melihat...
Anda mungkin sudah menyadari bahwa selanjutnya kita akan berbicara tentang tiga parameter yang tersisa di blok kode pertama - BLOCKSIZE, BUFFERCOUNT dan MAXTRANSFERSIZE. Berikut deskripsinya dari MSDN:
UKURAN BLOK = { Ukuran blok | @ ukuran_blok_variabel } - menunjukkan ukuran blok fisik dalam byte. Ukuran yang didukung adalah 512, 1024, 2048, 4096, 8192, 16, 384, dan 32 byte (768 KB). Nilai defaultnya adalah 65 untuk perangkat tape dan 536 untuk perangkat lainnya. Biasanya parameter ini tidak diperlukan karena pernyataan BACKUP secara otomatis memilih ukuran blok yang sesuai untuk perangkat. Menyetel ukuran blok secara eksplisit mengesampingkan pemilihan ukuran blok otomatis.
BUFFERCOUNT = { jumlah buffer | @ buffercount_variable } - Menentukan jumlah total buffer I/O yang akan digunakan untuk operasi pencadangan. Anda dapat menentukan nilai bilangan bulat positif apa pun, namun sejumlah besar buffer dapat menyebabkan kesalahan kehabisan memori karena ruang alamat virtual yang berlebihan dalam proses Sqlservr.exe.
Jumlah total ruang yang digunakan oleh buffer ditentukan dengan rumus berikut:
BUFFERCOUNT * MAXTRANSFERSIZE.
MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } menentukan ukuran paket data terbesar, dalam byte, untuk dipertukarkan antara SQL Server dan media set cadangan. Kelipatan 65 byte (536 KB) hingga 64 byte (4 MB) didukung.
Saya bersumpah - Saya pernah membaca ini sebelumnya, namun tidak pernah terpikir oleh saya seberapa besar dampaknya terhadap produktivitas. Terlebih lagi, rupanya saya perlu membuat semacam “coming out” dan mengakui bahwa sampai saat ini saya belum sepenuhnya memahami apa sebenarnya yang mereka lakukan. Saya mungkin perlu membaca lebih lanjut tentang buffered I/O dan bekerja dengan hard drive. Suatu hari nanti saya akan melakukan ini, tetapi untuk saat ini saya hanya bisa menulis skrip yang akan memeriksa bagaimana nilai-nilai ini memengaruhi kecepatan pengambilan cadangan.
Saya membuat database kecil, berukuran sekitar 10 GB, menaruhnya di SSD, dan meletakkan direktori untuk backup di HDD.
Saya membuat tabel sementara untuk menyimpan hasilnya (saya tidak memiliki tabel sementara, jadi saya bisa menggali hasilnya lebih detail, tetapi Anda yang memutuskan 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 skripnya sederhana - loop bersarang, yang masing-masing mengubah nilai satu parameter, masukkan parameter ini ke dalam perintah BACKUP, simpan catatan terakhir dengan riwayat dari msdb.dbo.backupset, hapus file cadangan dan iterasi berikutnya . Karena data eksekusi pencadangan diambil dari pencadangan, keakuratannya agak hilang (tidak ada sepersekian detik), tetapi kami akan bertahan.
Pertama, Anda perlu mengaktifkan xp_cmdshell untuk menghapus cadangan (lalu jangan lupa untuk menonaktifkannya jika Anda tidak membutuhkannya):
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
GOSebenarnya:
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 membutuhkan klarifikasi tentang apa yang terjadi di sini, tulis di komentar atau PM. Untuk saat ini saya hanya akan memberi tahu Anda tentang parameter yang saya masukkan ke BACKUP DATABASE.
Untuk BLOCKSIZE kami memiliki daftar nilai "tertutup", dan saya tidak melakukan pencadangan dengan BLOCKSIZE <4KB. MAXTRANSFERSIZE nomor apa pun yang merupakan kelipatan 64KB - dari 64KB ke 4MB. Default di sistem saya adalah 1024KB, saya mengambil 512 - 1024 - 2048 - 4096.
Lebih sulit dengan BUFFERCOUNT - bisa berupa angka positif apa pun, tetapi tautannya mengatakan . Ia juga mengatakan cara mendapatkan informasi tentang BUFFERCOUNT mana cadangan sebenarnya dibuat - bagi saya itu 7. Tidak ada gunanya menguranginya, dan batas atas ditemukan secara eksperimental - dengan BUFFERCOUNT = 896 dan MAXTRANSFERSIZE = 4194304 cadangan jatuh dengan kesalahan (yang tertulis di tautan di atas):
Pesan 3013, Level 16, Status 1, Baris 7 BACKUP DATABASE berakhir secara tidak normal.
Pesan 701, Level 17, Status 123, Baris 7 Memori sistem di kumpulan sumber daya 'default' tidak mencukupi untuk menjalankan kueri ini.
Sebagai perbandingan, pertama-tama saya akan menunjukkan hasil menjalankan pencadangan tanpa menentukan parameter apa pun:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;Nah, cadangan dan cadangan:
Memproses 1070072 halaman untuk database 'bt', file 'bt' pada file 1.
Diproses 2 halaman untuk database 'bt', file 'bt_log' pada file 1.
DATABASE CADANGAN berhasil memproses 1070074 halaman dalam 53.171 detik (157.227 MB/detik).
Skrip itu sendiri, menguji parameter, bekerja dalam beberapa jam, semua pengukuran dilakukan . Dan berikut adalah pilihan hasil dengan tiga waktu eksekusi terbaik (Saya mencoba membuat grafik yang bagus, tetapi di postingan saya harus puas dengan tabel, dan di komentar ditambahkan ).
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, catatan yang sangat penting dari dari :
Kami yakin dapat mengatakan bahwa hubungan antara parameter dan kecepatan cadangan dalam rentang nilai ini adalah acak, tidak ada pola. Namun menjauh dari parameter bawaan jelas berdampak baik pada hasilnya
Itu. Hanya dengan mengelola parameter BACKUP standar, waktu penghapusan cadangan diperoleh 2 kali lipat: 26 detik, dibandingkan 53 di awal. Itu tidak buruk, bukan? Namun kita perlu melihat apa yang terjadi dengan restorasi tersebut. Bagaimana jika sekarang butuh 4 kali lebih lama untuk pulih?
Pertama, mari kita ukur berapa lama waktu yang diperlukan untuk memulihkan cadangan dengan pengaturan default:
RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;Nah, Anda sendiri pasti tahu, caranya ada, ganti bukan ganti, pemulihan bukan pemulihan. Dan saya melakukannya seperti ini:
Memproses 1070072 halaman untuk database 'bt', file 'bt' pada file 1.
Diproses 2 halaman untuk database 'bt', file 'bt_log' pada file 1.
KEMBALIKAN DATABASE berhasil memproses 1070074 halaman dalam 40.752 detik (205.141 MB/detik).
Sekarang saya akan mencoba memulihkan cadangan yang diambil dengan BLOCKSIZE, BUFFERCOUNT dan MAXTRANSFERSIZE yang diubah.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304KEMBALIKAN DATABASE berhasil memproses 1070074 halaman dalam 32.283 detik (258.958 MB/detik).
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304KEMBALIKAN DATABASE berhasil memproses 1070074 halaman dalam 32.682 detik (255.796 MB/detik).
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152KEMBALIKAN DATABASE berhasil memproses 1070074 halaman dalam 32.091 detik (260.507 MB/detik).
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304KEMBALIKAN DATABASE berhasil memproses 1070074 halaman dalam 32.401 detik (258.015 MB/detik).
Pernyataan RESTORE DATABASE tidak berubah selama pemulihan; parameter ini tidak ditentukan di dalamnya; SQL Server sendiri yang menentukannya dari cadangan. Dan jelas bahwa bahkan dengan pemulihan pun masih ada keuntungan - hampir 20% lebih cepat (Sejujurnya, saya tidak menghabiskan banyak waktu untuk pemulihan, saya menjalankan beberapa pencadangan “tercepat” dan memastikan tidak ada kerusakan.).
Untuk berjaga-jaga, izinkan saya menjelaskan bahwa ini bukanlah beberapa parameter yang optimal untuk semua orang. Anda hanya bisa mendapatkan parameter optimal untuk diri Anda sendiri dengan menguji. Saya mendapatkan hasil ini, Anda akan mendapatkan hasil yang berbeda. Namun Anda melihat bahwa Anda dapat “menyesuaikan” cadangan Anda dan cadangan tersebut sebenarnya dapat dibentuk dan diterapkan lebih cepat.
Saya juga sangat menyarankan Anda membaca dokumentasi secara keseluruhan, karena mungkin ada perbedaan khusus untuk sistem Anda.
Sejak saya mulai menulis tentang pencadangan, saya ingin segera menulis tentang "optimasi" lain, yang lebih umum daripada "menyetel" parameter (sejauh yang saya mengerti, ini digunakan oleh setidaknya beberapa utilitas pencadangan, mungkin bersama dengan parameternya dijelaskan sebelumnya), tetapi belum dijelaskan juga di Habré.
Jika kita melihat baris kedua dalam dokumentasi, tepat di bawah BACKUP DATABASE, kita melihat:
TO <backup_device> [ ,...n ]Menurut Anda apa yang akan terjadi jika Anda menentukan beberapa perangkat_cadangan? Sintaksnya mengizinkannya. Dan hal yang sangat menarik akan terjadi - cadangan hanya akan “disebarkan” ke beberapa perangkat. Itu. setiap “perangkat” secara individual tidak akan berguna, kehilangan satu, kehilangan seluruh cadangan. Namun bagaimana noda tersebut akan mempengaruhi kecepatan pencadangan?
Mari kita coba membuat backup pada dua “perangkat” yang letaknya bersebelahan dalam folder yang sama:
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak'
WITH COMPRESSION;Bapak-bapak dunia, mengapa hal ini dilakukan?
Memproses 1070072 halaman untuk database 'bt', file 'bt' pada file 1.
Diproses 2 halaman untuk database 'bt', file 'bt'log 'pada file 1.
DATABASE CADANGAN berhasil memproses 1070074 halaman dalam 40.092 detik (208.519 MB/detik).
Apakah pencadangan menjadi 25% lebih cepat secara tiba-tiba? Bagaimana jika kita menambahkan beberapa perangkat lagi?
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH COMPRESSION;DATABASE CADANGAN berhasil memproses 1070074 halaman dalam 34.234 detik (244.200 MB/detik).
Secara total, keuntungannya adalah sekitar 35% dari waktu pembuatan cadangan hanya karena fakta bahwa cadangan tersebut ditulis ke 4 file pada satu disk sekaligus. Saya memeriksa jumlah yang lebih besar - tidak ada penguatan di laptop saya, optimalnya - 4 perangkat. Untuk Anda - saya tidak tahu, Anda perlu memeriksanya. Omong-omong, jika Anda memiliki perangkat ini - ini adalah disk yang benar-benar berbeda, selamat, keuntungannya akan lebih signifikan.
Sekarang mari kita bicara tentang bagaimana mengembalikan kebahagiaan ini. Untuk melakukan ini, Anda harus mengubah perintah pemulihan dan mencantumkan semua perangkat:
RESTORE DATABASE [bt]
FROM
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH REPLACE, RECOVERY;KEMBALIKAN DATABASE berhasil memproses 1070074 halaman dalam 38.027 detik (219.842 MB/detik).
Sedikit lebih cepat, tetapi mendekati, tidak signifikan. Secara umum, cadangan dihapus lebih cepat dan dipulihkan dengan cara yang sama - sukses? Bagi saya, ini cukup sukses. Ini itu penting, jadi saya ulangi - jika Anda jika Anda kehilangan setidaknya satu dari file ini, Anda kehilangan seluruh cadangan.
Jika Anda melihat di log informasi pencadangan yang ditampilkan menggunakan Trace Flags 3213 dan 3605, Anda akan melihat bahwa saat mencadangkan ke beberapa perangkat, setidaknya jumlah BUFFERCOUNT bertambah. Mungkin, Anda dapat mencoba memilih parameter yang lebih optimal untuk BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, tetapi saya tidak langsung berhasil, dan saya terlalu malas untuk melakukan pengujian seperti itu lagi, tetapi untuk jumlah file yang berbeda. Dan sayang sekali soal rodanya. Jika Anda ingin menyelenggarakan pengujian semacam itu di rumah, tidak sulit untuk membuat ulang skripnya.
Terakhir, mari kita bicara tentang harga. Jika cadangan dihapus secara paralel dengan pekerjaan pengguna, Anda perlu mengambil pendekatan pengujian yang sangat bertanggung jawab, karena jika cadangan dihapus lebih cepat, disk akan lebih tegang, beban pada prosesor meningkat (Anda masih harus mengompresi dengan cepat), dan karenanya, respons sistem secara keseluruhan menurun.
Hanya bercanda, tapi saya mengerti betul bahwa saya tidak mengungkapkan apa pun. Apa yang tertulis di atas hanyalah demonstrasi tentang bagaimana Anda dapat memilih parameter optimal untuk mengambil cadangan.
Ingatlah bahwa semua yang Anda lakukan adalah risiko dan risiko Anda sendiri. Periksa cadangan Anda dan jangan lupa tentang DBCC CHECKDB.
Sumber: www.habr.com