Database dump is too slow

[Former Member]

I have recently migrated from ASE12 to ASE15.7 on RHEL 6.4(raid 6), My database size is around 5GB only. The database dump to a local disk is taking almost 1 hr. I am not using any compression levels.Can anybody explain the reasons?

[c_baker]

Is the local disk on a separate device from the ASE disks or on the same RAID group?

RAID 6 will just introduce an extra parity write over RAID 5, so it is already slowing down writes for the ASE.  Even if the ASE is on RAID 5, how are the devices distributed (per Mark's note) - are all the ASE devices on the same RAID group?  What else is using the group?  How many disks in the RAID group.

As Mark says, was the older ASE on the same machine or did you migrate?

What is your IO performance (ms per IO) for the ASE devices?

One last thing - if this is a new setup - what is the size of your 'Default Data Cache' or other caches?  Default Data Cache is only sized at 8MB for a new install.  Way too small for anything.

Chris

[former_member182259]

....and RAID configs.   RAID 6 is notoriously slow at physical reads - which the dump would have to do a ton of.   RAID 6 normally has service times >20ms/IO.

I would also check to see if the dump directory was being synchronously block replicated via storage replication.   We have seen huge impacts of sync block replication on dump/loads even at short distances (<10km) when the bandwidth is << IO subsystem (e.g. a 1Gbs link provisioned as 2  512Gpbs - one in each direction)

[former_member91351]

First identify the devices where the backup is taking place and run iostat every 5 seconds on those devices using the following switches and redirect to a file.

iostat -x -t 5 <dev1, dev2, .... devn> | tee logfile.iostat

Use regexps on the logfile.iostat to check for the column svctm > 10 ms. This indicates that the io's initiated by the ASE for backup process is not being handled properly by the host io subsystem. Based on this data you need to tune the device queues accordingly.Generally for Linux the config value nr_requests is 128 by default, please check this @ /sys/block/sda/queue/nr_requests and tune accordingly so that the svctm values show < 10 ms. If the issue still not improved then you need to check with your Storage Admin.

Hope this helps.

[Former Member]

ASE 12 was on Solaris 10 and the dump took only 7 mins. I recently migrated to RHEL and ASE 15.7. The local disk is on the same RAID group as the database device. Data cache size is increased to 20G.

[former_member182259]

If you are on RHEL or *any* Linux, the biggest problem you have is getting the IO subsystem out of the way .....it was written for a laptop with 4800 rpm drives.

Step 1 - set the IO scheduler to noop

Step 2 - raise the nr_requests from default to 1024

Step 3 - tune the file system - in this case, unfortunately you are dumping to the same RAID group - and I suspect the same filesystem.   Normally, we would suggest turning off journaling as a key filesystem tuning consideration as DBMS's using DIRECTIO typically don't need it.   However, with doing DB dumps to the same file system you are being burned 2x.    There are a few things you can do with mount options, but not likely to make big differences.   One that might if using file system data devices is set the readahead buffer to be quite large

Step 4 - get OFF RAID 6.   It should only be used by people who don't care about performance and can afford to wait all day.