Biblioteca

Disk speed measurement with backup

Dmitry Kuzmenko, 30.05.2019, ib-aid.com Our technical support periodically receives a question (that is also asked on the forums) – “seems our disks are slow, how to confirm that”?

Most often this question is asked by users of virtual machines, where DBA does not have access to the disk subsystem settings, or even don’t know its characteristics.

Of course, disk speed can be measured by different tools, from simple CrystalDiskMark to more complex IOMeter. But, the most pure results can be obtained when applications do not work with the disk. Otherwise, results may vary, and under high load testing may decrease system performance.

Moreover, system administrators may prohibit installing 3rd party software, even for the short period of time.

However, backup is usually done on all such systems (except those who uses VM level backup software, which is wrong, of course, there will be another article to explain it).

Backup (gbak –b –g) is a reading of all data from the database, and writing (sequentially)this data to the backup file. From the POV of read/write operations it is a combination of random read and sequential write.

Backup file usually have 60%-70% size of the database size. And, because of the versioning and page structure of the database, there are more reads than writes.

Since backup file is usually placed on another physical drive, overall backup speed will depend on the database random read speed, and sequential write speed of the backup drive. Sure, you may place database to the HDD, and backup to the SSD, but this will be very ineffective, so, we consider that backup drive speed is nearly equal to the database drive speed, not much higher or lower.

To simplify speed evaluation we took only backup file size, and divided it by backup time. The result will be in “megabytes per second”. But, in order to evaluate the obtained value in comparison with other systems, some kind of reference measurement is required.

We took average desktop computer as a reference, having database on RAID1 of 2 SATA 3 HDDs, and backup on the separate SATA 3 HDD (sequential read speed of this RAID1 is around 180mb/sec, and ~150mb/sec for the backup HDD).

Here backup of 31gb database have 21gb size, and backup in single-user mode (nobody is working on that computer) takes 18 minutes, backup speed is 19 megabytes per second.

So, since this is not a powerful server, but just a desktop (assembled 2 or 3 years ago) we may usethis backup speed as a reference, considering it as a normal, and maybe minimum allowable for the production systems.

Next we analyzed backup speed at 30 different servers under our support governance, where backup file size is not less than 5 gigabytes.

Performance mb/sec Comment slow 10-20 463 gigabytesper 11hours, 80 gigabytesper 2 hours, 293 gigabytesper 5 hours – itisreallyslow, evencomparedtothedesktop. very slow up to 10 Thisislike 8.5 gigabytesbackupper 1 hour. Reallyslow.

very fast	50-70	There were some systems with backups from 38 to 150 gigabytes. Back up of 124 Gb database per 42 minutes – it is fast.
average, normal	20-50	Here average speed is around 80 gigabytesper 30-40 minutes. This is normal, and our desktop shows the result that may fit here.

It is clear that on some studied systems backup is not made during zero load on the server. Butinanycase, itisdoneduringtheminimumloadontheserver. Just to know how low load can affect backup speed, we run 2 simultaneous backups on the same desktop. Both took 19 minutes with the speed of 18 megabytes per second. This is 93% from the single-user backup, which is not too much (7% downgrade) for the single HDDs. If you have SSD, you may not notice difference even if you will run 3-4 backups at the same time. In general, the backup speed of 20 megabytes per second and below can be a cause for concern. In this case, either the backup disk is too slow, while the backup should be done as fast as possible. Either the database disk is too slow, and the overall system performance is worse than it need to be.