Maxdb Backup speed

[Former Member]

We are running MaxDb on linux with virtulized enviorment. We are trying to take backup of maxdb on a local disk. We are getting a speed of around 8 MB/sec. We understand that backup speed depends on a lot of factors

1) What can be a ideal speed for data backups on disk (We are running on Red Hat Linux on virtualized enviorment)

2) While selecting backup medium there is an option of changing Block Size. Will it impact backup speed??? how can we achive an optimum backup speed???

Also when we try to use veritas netbackup for taking backup on tape we are getting similar speeds.

Requsting your help

Nikhil

Edited by: Nikhil Deshpande on Oct 9, 2009 9:27 PM

[Former Member · Accepted solution]

Hi Nikhil

We are currently backing up to a remote disk (share/nfs) in a virtual environment.

I found that the number of backup threads gave a minor improvement on the speed of the backup. However, changing the Block Size had a significant improvement of the backup speed.

10 threads with block size of 8 ==> 12MB/s

10 threads with block size of 64 ==> 22MB/s

4 threads with block size of 64 ==> 22MB/s

Db server is a Virtualised Win2003 running 7.6.

Regards

Doug

[Former Member · Accepted solution]

Hi Christian,

Yes, We are using VMware.

Hi Doug

Kindly let me know how and where we can define/configure backup threads and how to set the block size? Do you mean the paramter IOPROCSPRE_DEV?

Thanks

[Former Member · Accepted solution]

Hi Nikhil

The parameter is MAXBACKUPDEVS.

This will allow you to define upto the MAXBACKUPDEVS devices in the MEDIUM definition for your backups.

A thread will be allocated to each defined device/file and the backup IO is spread across these threads.

Regards

Doug

[Former Member · Accepted solution]

Hi Nikhil

Sorry, I didn't read your question properly.

The above parameter will allow you to define upto MAXBACKUPDEVS devices/files in your medium definition. For a FILE backup, this means that for each backup you can split the backup into MAXBACKUPDEVS files.

To create these addition device/files (and adjust the block size), create a New parallel Medium in the DBMGui.

If you have set MAXBACKUPDEVS to 5 (for example) you will be able to add upto 5 device/files on the General tab. Enter a unique filename for each thread.

To change the Block Size, clicki on the Extended tab. Click the check box for the Block Size and enter your required block size.

regards

Doug

[Former Member · Accepted solution]

Sorry for a delayed response from my side. Due to some reason we were not able to take forward this project. We appreciate all the help provided by experts and hope for further guidance from them.

Now to analyze this issue, we have created a test setup. To start of with we are focusing on getting a moderate backup speed on disk. For that we are using a physical blade server with 16 GB ram and 8 core CPU. The OS and swap reside on local disk of blade server and are on a single physical hard disk. Our database partition is coming from Netapp SAN storage. We are using RAID-4 for our storage system. There are 6 physical disk in the RAID out of which one is hot spare one is for parity so there are actually 4 physical disk on which data volume are spread across. For backing up the data we are using other local disk of the blade server no other data is present on this disk. As mentioned above the data partition resides on storage which is connected with server using iscsi and network speed between server and storage is that of 1 GB/sec.

On database side, We are running with MaxDB 7.6.04.15 on RHEL5. The database is having 8 database data volumes with 6500 MB each. Some of database parameters are as follow

MAXBACKUPDEVS :- 5
CACHE_SIZE:-  244096
USE_OPEN_DIRECT :- YES
USE_OPEN_DIRECT_FOR_BACUP :-  NO

Now when we run a simple disk backup using DBMGUI we get average speed of backup around 8-10 MB/s. As mentioned above the data volumes and backup are not on single physical disks. We conducted some of the tests to check the I/O performance of the file system.

1) Creating a file of Size 1 GB in data partition of the database.

The command and output is given below

]# dd if=/dev/zero of=vol1 bs=64K count=16384

16384+0 records in
16384+0 records out
1073741824 bytes (1.1 GB) copied, 2.41591 seconds, 444 MB/s

2) Now using dd command copying this file to /backup directory (This is on different hard disk on server)

dd if=vol1 of=/backup/vol1

2097152+0 records in
2097152+0 records out
1073741824 bytes (1.1 GB) copied, 11.2715 seconds, 95.3 MB/s

3)Now We will create a new file in data directory and copy the same to backup directory using direct flag

dd if=vol2 of=/backup/vol2 iflag=direct oflag=dsync
68241+0 records in
68241+0 records out
34939392 bytes (35 MB) copied, 1052.01 seconds, 33.2 kB/s

[Former Member · Accepted solution]

Adding to the above post

Based on above facts in dd command we are getting good speed when copying data from one location to other. Only when using command with u201CIflag=directu201D and u201Coflag =dsyncu201D we are getting very slow speed. Does MaxDB uses similar command while backing up the data. Is iflag=direct is similar to USE_OPEN_DIRECT.

Further doing our tests to find out what is the read and write speed of MaxDB, We searched and found out on this link http://lists.mysql.com/maxdb/28060 some command to test the different I/O speeds. We performed the first two tests mentioned in the link and got following results.

A) Test of pipe performance on your machine:

mkfifo /backup/NPD
pipe2file -d test2pipe -p /backup/NPD -s 10000000000
pipe2file -d pipe2file -p /backup/NPD  -readonly u2013showspeed

567.437500 MB/s
595.812500 MB/s
604.937500 MB/s
609.718750 MB/s
612.487500 MB/s
614.375000 MB/s
615.767857 MB/s
616.703125 MB/s
617.444444 MB/s
618.037500 MB/s
618.511364 MB/s
618.843750 MB/s
619.173077 MB/s
619.513393 MB/s
619.775000 MB/s

B) Test the read performance of your MaxDB:

For testing this we created a pipe medium without any external backup tool. The name of the medium is BACKData and device or pipe name is /backint/NPD. Now we will run the follwing command to check the performance of the maxdb

dbmcli -d ... -u ...,... backup_start <pipemedium>
pipe2file -d pipe2file -p /backup/NPD -readonly u2013showspeed

15.344828 MB/s
15.364407 MB/s
15.361458 MB/s
15.360656 MB/s
15.402218 MB/s
15.422619 MB/s
15.447266 MB/s
15.426923 MB/s
15.452652 MB/s
15.425373 MB/s
15.445772 MB/s
15.456522 MB/s
15.454464 MB/s
15.435739 MB/s
15.426215 MB/s
15.378425 MB/s
15.334459 MB/s
15.315833 MB/s

The speed always hovered around between 14 -15 MB/s. Is this a correct way to test MaxDB performace. How can we achive a more optimal backup speed??? Is there any parameters we need to set for increasing the perormace????

Edited by: Nikhil Deshpande on Nov 26, 2009 5:09 PM

[lbreddemann · Accepted solution]

Hello Nikhil ,

you're comparing apples to oranges.

DD just reads the file from start to beginning. By that it can greatly benefit from all the fancy readeahead and pre-caching features of todays harddisks.

The MaxDB backup tasks cannot do this. Instead the have to read the used pages one by one from the data volumes and cannot use large vector IO operations or take advantage from harddisk-prefetching features.

If you want to increase the overall speed of the MaxDB backup, then you can

a) increase the number of data volumes

and

b) increase the number of output devices.

regards,

Lars

[Former Member]

Hello Nikhil,

what does you mean when you tell, that you are using a virtualized environment?

Do you run the system in VM-ware?

As Markus mentioned the speed for 100 Mbit network is 8MB/s... maybe there is a 100 Mbit network card configured in your VM?

Best regards

Christian

[markus_doehr2]

>

> We are running MaxDb on linux with virtulized enviorment. We are trying to take backup of maxdb on a local disk. We are getting a speed of around 8 MB/sec. We understand that backup speed depends on a lot of factors

exactly.

> 1) What can be a ideal speed for data backups on disk (We are running on Red Hat Linux on virtualized enviorment)

What is "ideal"?

> 2) While selecting backup medium there is an option of changing Block Size. Will it impact backup speed??? how can we achive an optimum backup speed???

There are two main things influencing the backup speed:

- the speed of the I/O subsystem including the number of volumes in your database

- the speed of the target device (disk or tape)

If you start a backup there are as many server tasks in the database created and used for backup as you have volumes (default configuration). So if you have only 5 volumes there will only be 5 threads reading the data from the disk. On top comes the question on how many physical disks those volumes are spread.

The second factor is the speed of the target device. Backup up to the same SAN as you run your database will lower your speed because the disks heads must "jump" around doing random I/O. If you use a tape, you need to get a stream of data to reach the maximum write speed. If the tape must stop and wait for data, rewind and start again, this will slow down the speed significantly.

The network between database and backup server is also a factor to be considered. If you use only a 100 MBit network the maximum speed is 8 MB/sec on ethernet.

Without knowing more about your environment it's impossible to suggest something. Please tell us:

- how many database volumes has your database configured

- and those volumes are on how many physical disks

- how is the backup disks implemented (same physical disks as the database)

- in case of tape, where is that tape located and what is the effective network speed between database server and backup server?

Over GBit ethernet with two tapes we backup ~ 35 - 40 MB/sec per tape.

Markus

[Former Member]

Hi Markus

how many database volumes has your database configured

We have seven database volumes

and those volumes are on how many physical disks

We are using one database file system with RAID 5

how is the backup disks implemented (same physical disks as the database)

We have created new file system on the same local disk.

- in case of tape, where is that tape located and what is the effective network speed between database server and backup server?

Currently we are focusing on disk based backup. On disk only we are getting 8 MB/s speed.

[markus_doehr2]

> and those volumes are on how many physical disks

> We are using one database file system with RAID 5

Yes - but how many disks are in that RAID-5?

> how is the backup disks implemented (same physical disks as the database)

> We have created new file system on the same local disk.

Ok. You have then reading and writing I/O on the same disks. that means that the heads from the disks must "jump" around the disks to read and write. One disk can only have a certain number of I/Os per second so it seems you reach that limit.

> - in case of tape, where is that tape located and what is the effective network speed between database server and backup server?

>

> Currently we are focusing on disk based backup. On disk only we are getting 8 MB/s speed.

See above.

Use different (physical) disks for the backup.

Markus

[Former Member]

Hi Markus

We tried backing up on another set of disks. But with no luck... The backup speed remians almost same.

We are rrunning on linux. Will the parameter USE_OPEN_DIRECT_FOR_BACKUP set to YES will help in order the increase the I/O speed.

[simon_matter]

>

> Hi Markus
> 
> We tried backing up on another set of disks. But with no luck... The backup speed remians almost same.
> 
> We are rrunning on linux. Will the parameter USE_OPEN_DIRECT_FOR_BACKUP  set to YES will help in order the increase the I/O speed.

I recommend using USE_OPEN_DIRECT_FOR_BACKUP=YES anyway when doing backup to disk. This makes sure your memory is not used as disk cache which makes no sense I think. However this will most likely not speed up you backup.

But then, what is the speed of your disk subsystem?

You may want to check this with 'dd' using the 'direct' flag to make sure you really measure disk performance. Make sure to also check writing with dd while also reading from the same disk. Like create a file 'a' and the do a

dd if=a of=b iflag=direct oflag=dsync

You said you are using a virtualized environment. That doesn't say much but in general there are many ways you can get bad IO performance with virtualized systems.

I think the main reason to get not optimal performance is because you are reading and write to the same disk for your backup.

Simon

[markus_doehr2]

> We tried backing up on another set of disks. But with no luck... The backup speed remians almost same.

Still the question remains: You have as many physical disks in your RAID-5 as you have volumes?

Markus