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