Is binary loading slower for Type II storage areas compared to Type I?

F

FocusIT

Member
I am dumping and loading a database from 9.1D09 32-bit to 10.2B05 64-bit on Windows 2008 R2 64-bit. On brand new hardware under 10.2B05 I am getting a binary dump rate of over 300k (which I am very pleased with) records a second using a multi threaded dump file against a brokered database. However, I am only getting a binary load rate of about 100k records a second with the same number of threads against a brokered database. When performing the same operation in 9.1D09 on similar hardware the performance metrics are typically around the other way i.e. binary load is 3-4 times faster than the binary dump. Also, I can load the same binary dump files into a 9.1D09 database at nearly 200k records a second.

There are 1.8 billion records to load so increasing the load rate could make a few hours difference. The only significant difference between the 9.1D and 10.2B databases is that the records are being loaded into Type II storage. I am not building the indexes during the load.

Is there any way of speeding up binary load into 10.2B? Is it Type II storage causing the difference in speed or something else perhaps?

Few other details regarding empty database used for load: -

BI truncated with 65536 cluster size and 40 grown clusters.
Broker running with -i.
15000000 blocks in DB Buffer
9 APW
25 BI Buffs
100000 Spin Locks
Server 128 physical disks arranged into 6 logical drives in RAID 1+0, the load database is spread across three of these (indexes, data, BI) and the .bd files are held on a fourth.
Server has 128GB physical memory and 48 cpu cores

I have experimented with different settings for the load database above and -i is the only thing that had any significant performance impact.

Thanks in advance.

Andrew Bremner
 
Don't even consider Type I storage. Just do your best to optimize your Type II structure. It certainly sounds like you have all the hardware horsepower you need. During a bulk load you are very dependent on BI subsystem efficiency, so it is good that you have increased your BI cluster size. You didn't mention your BI block size or your DB block size, although with 128 GB of RAM and -B at 15 million I assume your are using 4 KB for the DB.

This would require testing, but you may see benefit with:
  • truncating the BI to set BI block size to 16K, and
  • increasing the BI cluster size to maximum (262128K) (and then grow the BI again to a reasonable size)
  • adding more BI buffers
  • starting with an 8K database, if you're on 4K now
  • reducing -spin

Obviously, don't change this all at once or you will obscure the effects of individual changes. Also, you really need to look at promon or ProTop during your load to get an understanding of where your current bottleneck lies. For example, if you have a lot of empty buffer waits you should add more BI buffers; buffers flushed at checkpoint, add APWs; very frequent checkpoints, increase BI cluster size, etc. Another obvious thing to look at would be the I/O on the disks in question; are any other processes touching them, like a virus scanner or file indexer? Are there system page files on any of these disks?

On a related note, make sure your structure is appropriate for your data, in particular your RPB. If it is too low in some storage areas then you are getting fewer record writes per disk I/O than is optimal. Also, make sure no tables have been left in the schema area.

This would involve more prep work, but you may want to try calculating how much disk space you need for your data, given your data characteristics and structure, and then allocating fixed extents for your areas rather than growing into empty variable extents. Again, I don't know what approach you are taking here. But if you are waiting a lot for extend operations that could help with throughput.

Without seeing DB performance data it is hard to give more specific guidance, but that is a start.
 
I agree with what Rob said.

Increasing -bibufs and making sure that a BIW is running have had very positive impacts on my binary loads.

Usually the load is *much* faster than the dump and I've never seen a poor outcome due to Type 2 areas. If you're seeing a difference I would guess that something isn't setup right. Possible candidates:

1) The bi file is on actually on RAID5. Even though your SA told you otherwise.
2) Your -spin is way too high. Try 5,000.
3) Windows. Apply the Linux patch...
4) 10.2B05 has some new features for the index rebuild. It has been reported that they may not work very well. Try setting -TF 0 (disabling the feature...)
5) Your storage areas may not be correctly configured. I once loaded a bunch of data into a storage area that had rows per block set to "1" (I fat fingered the definition...). That was pretty ugly.
6) Windows #2 -- are you using "scheduled tasks" to do this?
 
You must log in or register to reply here.
Back
Top