Results of Backup/Restoration Performance Measurements on PostgreSQL8.1.4 Using OSDL DBT-1 (Offline PITR) || OSS iPedia

Results of Backup/Restoration Performance Measurements on PostgreSQL8.1.4 Using OSDL DBT-1 (Offline PITR)

We ran DBT-1 while PITR is configured on PostgreSQL8.1.4. After running DBT-1, we evaluated the offline backup, the base restoration and PITR performance using the tar czf command.

1. Measurement Environment

H/W	HITACHI HA8000/110W HB
CPU	Intel Xeon HT OFF
	2GB
HDD	220GB
OS	RHEL4 2.6.9-11.Elsmp
	PostgreSQL 8.1.4
	OSDL DBT-1 2.1

In this measurement environment, there were three hard disks as shown in Figure 1: /dev/sad, /dev/sdb and /dev/sdc.

Figure 1: Disk Layout

How these hard disks are used is shown in Table 2.

Table 2 Hard Disk Use in the Measurement Environment
Device Name	Use	Mouse Point
/dev/sda	For storing the OS and execution log	/
/dev/sdb	For executing DBT-1 and storing the backup target DB	/data
/dev/sdc	For storing backup files and WAL	/data2

2. Measurement Point

We loaded an approximately 7GB table generated using "./datagen -d PGSQL -i 10000 -u 1000" and an approximately 53GB table generated using "./datagen -d PGSQL -i 10000 -u 8000" into PostgreSQL and ran DBT-1 for 3 hours, 7 hours and 10 hours. The performance was measured while increasing the invalid area in the data cluster.

Measurement procedure manual

3. Measurements

Figure 2 shows the backup time for each backup start time of the 7GB table.

Figure 2 Backup Time for Each Backup Start Time of the 7GB Table

Figure 3 shows the backup time for each backup start time of the 53GB table.

Figure 3 Backup Time for Each Backup Start Time of the 53GB Table

Figure 4 shows the backup file size for each backup start time of the 7GB table.

Figure 4 Backup File Size for Each Backup Start Time of the 7GB Table

Figure 5 shows the backup file size for each backup start time of the 53GB table.

Figure 5 Backup File Size for Each Backup Start Time of the 53GB Table

Figure 6 shows the base restoration time for each backup start time of the 7GB table.

Figure 6 Base Restoration Time for Each Backup Start Time of the 7GB Table

Figure 7 shows the base restoration time for each backup start time of the 53GB table.

Figure 7 Base Restoration Time for Each Backup Start Time of the 53GB Table

Figure 8 shows the WAL file size for each DBT-1 execution time of the 7GB table.

Figure 8 WAL Size for Each DBT-1 Execution Time of the 7GB Table

Figure 9 shows the WAL file size for each DBT-1 execution time of the 53GB table.

Figure 9 WAL Size for Each DBT-1 Execution Time of the 53GB Table

Figure 10 shows the CPU activity ratio during the backup/restoration process while running DBT-1 using the 7GB table.

Figure 10 CPU Activity Ratio During the Backup/Restoration Process while Running DBT-1 Using the 7GB Table

Figure 11 shows the CPU activity ratio during the backup/restoration process while running DBT-1 using the 53GB table.

Figure 11 CPU Activity Ratio During the Backup/Restoration Process while Running DBT-1 Using the 53GB Table

Figure 12 shows the disk I/O during the backup/restoration process while running DBT-1 using the 7GB table.

Figure 12 Disk I/O During the Backup/Restoration Process while Running DBT-1 Using the 7GB Table

Figure 13 shows the disk I/O during the backup/restoration process while running DBT-1 using the 53GB table.

Figure 13 Disk I/O During the Backup/Restoration Process while Running DBT-1 Using the 53GB Table

In this measurement, we obtained the base backup while there were no transactions and executed the restoration/PITR process immediately after obtaining the base backup, the process time of PITR was 0 second for all of the patterns.

4. Data Downloads

5. Related Performance Data

6. Data for Thought on This Data

Consideration on Backup and Restoration Performance of PostgreSQL8.1.4 by Using OSDL DBT-1

7. Configuration Information Page

To configuration information page