/* Database+Disk+Performance™: Disk Performance and BACKUP to NUL: Command */ Database+Disk+Performance™ /* */ /* -- -- */ /* Footer ----------------------------------------------- */ #footer { width:660px; clear:both; margin:0 auto; } #footer hr { display:none; } #footer p { margin:0; padding-top:15px; font:78%/1.6em "Trebuchet MS",Trebuchet,Verdana,Sans-serif; text-transform:uppercase; letter-spacing:.1em; }

Tuesday, June 20, 2006

Disk Performance and BACKUP to NUL: Command

Introduction

Disk drive performance and databases are directly tied to each other with backups. A full backup, by definition, will start at the beginning of a database and read all the way through to the end. In the process, it can generate a large amount of disk IO. If you have a slow disk subsystem, it can show up here.

 

As you will see in the following examples, slow backups can be an indication of overall system health and poor database architecture.

 

The following table illustrates the different IO patterns that SQL Server uses when going after data. It is possible that you can get good performance in an OLTP environment because it’s doing many small 8K IOs, and then have poor performance when running backups because it’s doing 1M IOs.

 

 

OLTP Log

Sequential

Write

512 bytes - 64KB

OLTP Data

Random

R/W

8K

Bulk Insert

Sequential

Write

8K multiples to 128K

Read Ahead

Sequential

Read

8K multiples to 256K

Backup

Sequential

R/W

1MB

Restore

Sequential

R/W

64K

CHECKDB

Sequential

Read

8K - 64K

 

Table: SQL Server Disk IO Access Patterns

 

Source: http://blogs.msdn.com/sqlcat/

 

One of the best reasons to use the backup command is its uniformity and simplicity. Every SQL Server has it which enables comparison between different platforms, and at the end of the backup, it summarizes for you the actual throughput so you don’t have to even calculate anything.

 

BACKUP to a Null Device

 

The main purpose of a backup is an offline copy of the database for possible recovery purposes. Typically backups dump to disk or tape. In some cases, it’s beneficial to make a backup, but not store the results anywhere. For example, if you want to run through an operational process for practice… or in our case, stress the disk subsystem.

 

When a backup is run, it reads from the database, and then writes to the backup media. Hence it will read then pause then read then pause when accessing the database itself. The pause to the database occurs as it’s writing to the backup media. This does not help with stress testing by putting a constant load on the system.

 

The only way to put a full load using the BACKUP command is running it with the NUL: device syntax.

 

Syntax:

backup database <db_name> to disk = ‘NUL:’ with stats = <perc compl>

Example of Usage:

backup database siebelprd to disk = ‘NUL:’ with stats = 1

Sample Output:

Processed 1416 pages for database 'newmsdb', file 'MSDBData' on file 1.

100 percent backed up.

Processed 1 pages for database 'newmsdb', file 'MSDBLog' on file 1.

BACKUP DATABASE successfully processed 1417 pages in 0.071 seconds (163.392MB/sec).

 

Backing up to the NUL: device will cause a full time read to the database without pausing to write to the backup media.

 

Another issue when testing databases is caching. If the same test is run often, the results will always be in RAM hence distorting the results. By running a backup, it usually exceeds the amount of cache on the server thus helps eliminate this distortion.

 

Judging the Results: Bad, Good, Best

Once the command is run, the DBA needs to know what the results mean. The following examples were taken from customers I have worked with over the last 2 years and help illustrate what the throughput numbers mean.

 

 

Laptop Disk Drive With 36MB Database

 

99 percent backed up.

100 percent backed up.

BACKUP DATABASE successfully processed 4473 pages in 1.172 seconds (31.259 MB/sec).

 

 

Desktop internal disk drive 3.4GB database:

 

100 percent backed up.
BACKUP DATABASE successfully processed 425841 pages in 135.945 seconds (25.660 MB/sec).

 

Bad

 

Customer’s 1.6TB database with 12 CPU and High End SAN RAID 1:

 

100 percent backed up.

BACKUP DATABASE successfully processed 202986177 pages in 47070.773 seconds (35.326 MB/sec).

 

 

This is almost the same IO as my laptop (31.259 MB/Sec)?!

 

 

Concern

 

Customer’s 87GB database with 8 CPU 32G and High End RAID 5:

 

ファイル 1 で、データベース 'siebeldb' 、ファイル 'siebeldb_prm1' 2670712 ページが処理されました。

ファイル 1 で、データベース 'siebeldb' 、ファイル 'siebeldb_prm2' 2665816 ページが処理されました。

ファイル 1 で、データベース 'siebeldb' 、ファイル 'siebeldb_prm3' 2662176 ページが処理されました。

ファイル 1 で、データベース 'siebeldb' 、ファイル 'siebeldb_prm4' 2662064 ページが処理されました。

ファイル 1 で、データベース 'siebeldb' 、ファイル 'siebeldb_log1' 47 ページが処理されました。

BACKUP DATABASE 10660815 ページを 789.876 秒で正常に処理しました (110.565 MB/ )

 

 

Good

 

Customer 575GB SAN:

 

100 percent backed up.

BACKUP DATABASE successfully processed 70246627 pages in 3540.611 seconds (162.531 MB/sec).

 

Microsoft Dev Database 101G with RAID10 on 146Gb 10K drive on SAN:

 

100 percent processed.

BACKUP DATABASE successfully processed 12376579 pages in 707.413 seconds (143.323 MB/sec).

 

Microsoft DSS database 28GB  database High End SAN 72G 15K:

 

100 percent backed up.

BACKUP DATABASE successfully processed 3468866 pages in 151.831 seconds (187.161 MB/sec).

 

Microsoft OLTP Database 2TB with 72Gb 15K drives Raid10 on SAN:

 

100 percent backed up.

BACKUP DATABASE successfully processed 244138625 pages in 5561.102 seconds (359.638 MB/sec).

 

 

Great

 

Processed 25342792 pages for database 'TUC', file 'data1' on file 1.

Processed 25215088 pages for database 'TUC', file 'data3' on file 1.

Processed 25215168 pages for database 'TUC', file 'data5' on file 1.

Processed 25214320 pages for database 'TUC', file 'data2' on file 1.

Processed 25214344 pages for database 'TUC', file 'data4' on file 1.

Processed 25215168 pages for database 'TUC', file 'data6' on file 1.

Processed 25213704 pages for database 'TUC', file 'data7' on file 1.

Processed 25214552 pages for database 'TUC', file 'data8' on file 1.

Processed 25215200 pages for database 'TUC', file 'data9' on file 1.

Processed 25214280 pages for database 'TUC', file 'data10' on file 1.

Processed 25214920 pages for database 'TUC', file 'data11' on file 1.

Processed 25321384 pages for database 'TUC', file 'data12' on file 1.

Processed 3 pages for database 'TUC', file 'log1' on file 1.

BACKUP DATABASE successfully processed 302810923 pages in 6010.430 seconds (412.720 MB/sec).

DB Size= 2,365,619Mb

Backup time to Null:  1 hr. 40 min.

 

What to Do?

 

If you see that you are having slow throughput times (low MB/sec), the fixes are usually to add more spindles to the database and spread the load out more and/or increase bandwidth to the disk by adding faster/more controllers.

 

 

Warning!

 

This command runs great accept under the following conditions:

 

  1. If you run a differential backup, don’t run this command! The reason is that the differential backup picks up pages that have changed. Running the backup command resets the flag on the page. Since the backup to a NUL: device reads the page, but does not back it up to media, the flag is reset as if it HAD been backed up… so the database does NOT back it up during the next differential. If you have run a backup to NUL: and do differential backups, run a FULL backup immediately and everything will be fine.
  2. If you have heavy disk IO now, a backup to a NUL: device will only add more stress slowing things down more.
  3. For reason #2, run this command in off hours.

Summary

 

Backup to a NUL: device is a handy command. It allows you to compare disk performance throughput of a laptop to a high end database server with a SAN. It’s a poor man’s stress test and doesn’t take much to setup or run… or interpret the results.  It will not fix any of the problems, but hopefully show whether the database as a whole can sustain massive IO within its architecture.

 

 

 

 

3 Comments:

Blogger sexy said...

情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣用品,情趣,情趣,情趣,情趣,情趣,情趣,情趣,情趣,A片,視訊聊天室,聊天室,視訊,視訊聊天室,080苗栗人聊天室,上班族聊天室,成人聊天室,中部人聊天室,一夜情聊天室,情色聊天室,視訊交友網

免費A片,AV女優,美女視訊,情色交友,免費AV,色情網站,辣妹視訊,美女交友,色情影片,成人影片,成人網站,A片,H漫,18成人,成人圖片,成人漫畫,情色網,日本A片,免費A片下載,性愛

A片,色情,成人,做愛,情色文學,A片下載,色情遊戲,色情影片,色情聊天室,情色電影,免費視訊,免費視訊聊天,免費視訊聊天室,一葉情貼圖片區,情色,情色視訊,免費成人影片,視訊交友,視訊聊天,視訊聊天室,言情小說,愛情小說,AIO,AV片,A漫,avdvd,聊天室,自拍,情色論壇,視訊美女,AV成人網,色情A片,SEX,成人論壇

情趣用品,A片,免費A片,AV女優,美女視訊,情色交友,色情網站,免費AV,辣妹視訊,美女交友,色情影片,成人網站,H漫,18成人,成人圖片,成人漫畫,成人影片,情色網


情趣用品,A片,免費A片,日本A片,A片下載,線上A片,成人電影,嘟嘟成人網,成人,成人貼圖,成人交友,成人圖片,18成人,成人小說,成人圖片區,微風成人區,成人文章,成人影城,情色,情色貼圖,色情聊天室,情色視訊,情色文學,色情小說,情色小說,臺灣情色網,色情,情色電影,色情遊戲,嘟嘟情人色網,麗的色遊戲,情色論壇,色情網站,一葉情貼圖片區,做愛,性愛,美女視訊,辣妹視訊,視訊聊天室,視訊交友網,免費視訊聊天,美女交友,做愛影片

av,情趣用品,a片,成人電影,微風成人,嘟嘟成人網,成人,成人貼圖,成人交友,成人圖片,18成人,成人小說,成人圖片區,成人文章,成人影城,愛情公寓,情色,情色貼圖,色情聊天室,情色視訊,情色文學,色情小說,情色小說,色情,寄情築園小遊戲,情色電影,aio,av女優,AV,免費A片,日本a片,美女視訊,辣妹視訊,聊天室,美女交友,成人光碟

情趣用品.A片,情色,情色貼圖,色情聊天室,情色視訊,情色文學,色情小說,情色小說,色情,寄情築園小遊戲,情色電影,色情遊戲,色情網站,聊天室,ut聊天室,豆豆聊天室,美女視訊,辣妹視訊,視訊聊天室,視訊交友網,免費視訊聊天,免費A片,日本a片,a片下載,線上a片,av女優,av,成人電影,成人,成人貼圖,成人交友,成人圖片,18成人,成人小說,成人圖片區,成人文章,成人影城,成人網站,自拍,尋夢園聊天室

2:42 AM  
Blogger  said...

A片,A片,成人網站,成人漫畫,色情,情色網,情色,AV,AV女優,成人影城,成人,色情A片,日本AV,免費成人影片,成人影片,SEX,免費A片,A片下載,免費A片下載,做愛,情色A片,色情影片,H漫,A漫,18成人

a片,色情影片,情色電影,a片,色情,情色網,情色,av,av女優,成人影城,成人,色情a片,日本av,免費成人影片,成人影片,情色a片,sex,免費a片,a片下載,免費a片下載

情趣用品,情趣用品,情趣,情趣,情趣用品,情趣用品,情趣,情趣,情趣用品,情趣用品,情趣,情趣

A片,A片,A片下載,做愛,成人電影,.18成人,日本A片,情色小說,情色電影,成人影城,自拍,情色論壇,成人論壇,情色貼圖,情色,免費A片,成人,成人網站,成人圖片,AV女優,成人光碟,色情,色情影片,免費A片下載,SEX,AV,色情網站,本土自拍,性愛,成人影片,情色文學,成人文章,成人圖片區,成人貼圖

情色,AV女優,UT聊天室,聊天室,A片,視訊聊天室

6:58 AM  
Blogger freefun0616 said...

酒店經紀人,
菲梵酒店經紀,
酒店經紀,
禮服酒店上班,
酒店小姐兼職,
便服酒店經紀,
酒店打工經紀,
制服酒店工作,
專業酒店經紀,
合法酒店經紀,
酒店暑假打工,
酒店寒假打工,
酒店經紀人,
菲梵酒店經紀,
酒店經紀,
禮服酒店上班,
酒店經紀人,
菲梵酒店經紀,
酒店經紀,
禮服酒店上班,
酒店小姐兼職,
便服酒店工作,
酒店打工經紀,
制服酒店經紀,
專業酒店經紀,
合法酒店經紀,
酒店暑假打工,
酒店寒假打工,
酒店經紀人,
菲梵酒店經紀,
酒店經紀,
禮服酒店上班,
酒店小姐兼職,
便服酒店工作,
酒店打工經紀,
制服酒店經紀,
酒店經紀,

,

1:14 PM  

Post a Comment

<< Home