SQL Server Database Backup Performance with Quest (Imceda) LiteSpeed for SQL Server; 3 Terabytes in Under 1 Hour

System

HP rx8620, 16 Itanium 2 processors, 1.5GHz, 6M L3 cache

OS

Windows Server 2003 Datacenter Edition, (base & service pack 1)

Database

SQL Server 2000, service pack 3 & 4

SQL Server 2005, June 2005 CTP

Compression

Quest (Imceda) LiteSpeed 4.0.9.7 and later

HBA

4 dual port 2Gb/sec FC adapters

SAN

4 HSV110 controllers (2 per EVA 5000)

Disks

8 FC disk enclosures, 14x72GB 15K disks, RAID 0 & 10 LUNs.

6x146GB 10K U160 SCSI disks

Table 1. Server system configuration details.

LiteSpeed Performance

LiteSpeed performance characteristics depend on the processor capabilities and the compressibility of the data. The more compressible the data, the more each processor can compress per CPU-second. Figure 4 shows the backup rate achieved on large databases for data compressibility ratios of 2.6, 5.0 and 8.0 (a 5:1 compressibility ratio means a 1TB database compresses down to 200GB). A highly normalized database making extensive use of variable length strings typically has compressibility between 2:1 and 3:1. Several well known ERP and CRM applications use fixed length strings, resulting in compression ratios from 5:1 to 8:1.

Figure 4. LiteSpeed backup performance by data compressibility.

The test database for the 2.6:1 compression ratio (CR) was 485GB, 1025GB for the 5:1 CR, and 1019 for the 8:1 CR. Various data and backup arrangements were tested for each CR, with the goal of balancing the ability to read from data and write to the backup location. The 2.6:1 CR used 4 FC LUNs for data, 4 FC LUNs and the 6 SCSI disk for backup, with each LUN comprised of 14 disks in the SAN. The 5:1 CR used 6 LUNs for data, 2 LUNs and the SCSI disks for backup. The 8:1 CR used 7 LUNs for data, 1 LUN and the SCSI disks for backup. All tests used default parameters for LiteSpeed with the exception of threads. The default thread setting is 3. For large systems, the threads should be increased to match the capabilities of the storage system.

Backup Rate in TB/hour

by Compression Ratio

Threads

2.6

5.0

8.0

6

1.26

1.50

1.98

8

1.65

1.99

2.61

10

1.99

2.35

3.15

12

2.32

2.86

3.71

Table 2. LiteSpeed backup rate in TB per hour by data compressibility.

In the case of the 2.6:1 CR database, the storage system did not match the desired write rate to the backup locations, so scaling to 12 threads could be better if the storage system had better write capability. CPU utilization ranged from 30-35% at 6 threads to 60-65% at 12 threads. There are definitely sufficient CPU resources to support faster compression. On going work to optimize the use of asynchronous operations will enable the processing unit to better utilize the full capability of the storage system.

Figure 5 shows LiteSpeed restore performance. The dip in the restore performance for the 2:6:1 CR at 10 threads is most probably due to an anomaly in the storage system, possibly a storage system maintenance operation.

Figure 5. LiteSpeed restore performance by data compressibility.

The restore performance currently ranges from 0.7 TB/hr at 2.6:1 CR, 1.3 TB/hr at 5:1 and 1.3 TB/hr with 8:1 data compressibility. Improvements in restore performance are possible with continued code work.

Summary

The key benefits of using LiteSpeed are that very fast backup and restore speeds are possible with the storage system optimized for query performance. The native SQL backup can only achieve similar performance with more disks overall and with the majority of disk allocated to the backup location.

Copyright 2005 Joe Chang

Pages: 1 2




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |