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 purest 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 a short period of time.
However, backup is usually done on all such systems (except those who uses VM level backup software to backup Firebird, which is wrong - see #11 here in "12 Common Mistakes while Backing Up Databases").
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 has a 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 a 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 back up 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 the average desktop computer as a reference, having a database on RAID1 of 2 SATA3 HDDs, and backup on the separate SATA3 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 use this 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||Speed, MB/sec||Comments and Examples|
|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||20-50||Here average speed is around 80 gigabytes per 30-40 minutes. This is normal, and our desktop shows the result that may fit here.|
|Slow||10-20||463 gigabytes per 11 hours, 80 gigabytes per 2 hours, 293 gigabytes per 5 hours – it is slow, even our test desktop is faster.|
|Extremely slow||Below 10||This is like 8.5 gigabytes per 1 hour. Very slow.|
Just to know how low load can affect backup speed, we run 2 simultaneous backups on the same desktop. Both took 19 minutes with a 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 the 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 needs to be.
The recommended command line for the fast backup is the following, both for Firebird 2.5 and Firebird 3.0:
gbak -se localhost:service_mgr -b -g -user USERNAME -pass password Disk:\Path\Database.fdb Disk:\Path\backup.fbk
More tips and tricks devoted on the general performance: