Question Binary dump of a large table taking time.

[pankajpatel23]

Hello ALL,

System Configuration:
IBM AIX 5.3.0.0 64bit, 8 CPU machine
Memory: 58GB
Progress: 10.1C
Database size: 47.9 GB [by dbanalysis report]
152 GB [by promon -> 5. Activity]

Challenge: Binary dump of a table taking more then 7 hrs,so need your valuable inputs to optimize this time.

Table name: spt_det
Number of records in the table: 62026509

Index detail on this table:


Scenario 1:
Index Used: 1638
-thread: 1
Command: proutil $DBNAME -C dump spt_det $DMPDIR -index 1638 -thread 1 -threadnum 4 -dumplist $DMPDIR/spt_det.lst
Time taken to dump the data: ~9 hrs

Scenario 2:
Index Used: 1637
-thread: 0
Command: proutil $DBNAME -C dump spt_det $DMPDIR -index 1637 -thread 0 -threadnum 4 -dumplist $DMPDIR/spt_det.lst
Time taken to dump the data: ~11 hrs

Scenario 3:
Index Used: 1638
-thread: 0
Command: proutil $DBNAME -C dump spt_det $DMPDIR -index 1638 -thread 0 -threadnum 4 -dumplist $DMPDIR/spt_det.lst
Time taken to dump the data: ~7 hrs

So is there anyway , I can further bring down this dump time.

Thanks for your valuable inputs.

Regards,
Pankaj

 

[TomBascom]

What sort of storage area is the table currently in?

 

[Rob Fitzpatrick]

So these are the tests you have done:

1. multi-threaded with idx 1638, up to 4 threads
2. single-threaded with idx 1637
3. single-threaded with idx 1638

A few ideas:

• When is the last time you compacted or rebuilt your indexes? Try compacting, or rebuilding if you can afford the downtime, and see if it helps.
  
• Which one is the primary index?
  
• Try multi-threaded with -threadnum 8 on each index, see how many threads it uses. It may be more than 4, depending on your indexes.
• Try adding a small index, building it, and dumping with that.
• Try to dump under a later OE release, if you have it available.
• Try dumping on a faster machine if possible. Restore a backup and keep it current with AI log roll forward until you bring down prod for your dump.
• Try multiple concurrent DUMPSPECIFIED.
• Is $DMPDIR on a separate disk/LUN/controller from the DB? Is it on local-attached storage or a SAN? Or a NAS? Or remote share?
• Add an SSD to your box and dump to that.
• You haven't yet tried a multi-threaded dump with idx 1637. Note that -thread 0 overrides -threadnum.

 

[pankajpatel23]

What sort of storage area is the table currently in?

The table is stored in type II storage area.

 

[pankajpatel23]

So these are the tests you have done:

1. multi-threaded with idx 1638, up to 4 threads
2. single-threaded with idx 1637
3. single-threaded with idx 1638

A few ideas:

• When is the last time you compacted or rebuilt your indexes? Try compacting, or rebuilding if you can afford the downtime, and see if it helps.
  
• Which one is the primary index?
  
• Try multi-threaded with -threadnum 8 on each index, see how many threads it uses. It may be more than 4, depending on your indexes.
• Try adding a small index, building it, and dumping with that.
• Try to dump under a later OE release, if you have it available.
• Try dumping on a faster machine if possible. Restore a backup and keep it current with AI log roll forward until you bring down prod for your dump.
• Try multiple concurrent DUMPSPECIFIED.
• Is $DMPDIR on a separate disk/LUN/controller from the DB? Is it on local-attached storage or a SAN? Or a NAS? Or remote share?
• Add an SSD to your box and dump to that.
• You haven't yet tried a multi-threaded dump with idx 1637. Note that -thread 0 overrides -threadnum.

Thanks Rob for your input. Below given my answers:

• When is the last time you compacted or rebuilt your indexes? Try compacting, or rebuilding if you can afford the downtime, and see if it helps.

No idea, but can try rebuilding and check.

• Which one is the primary index?

Primary index is "spt_sim_part" having five fields shown below:

pu spt_sim_part 5 + spt_domain
+ spt_site
+ spt_sim
+ spt_part
+ spt_element

• Try multi-threaded with -threadnum 8 on each index, see how many threads it uses. It may be more than 4, depending on your indexes.

This I have not tried yet, but I can do this. Is there any calculation behind the actual figure to use?

• Try adding a small index, building it, and dumping with that.

You mean a new index with one/two fields [currently there are five fields]?

• Try to dump under a later OE release, if you have it available.

I only have 10.1B03 and 10.1C0465 available.

• Try dumping on a faster machine if possible. Restore a backup and keep it current with AI log roll forward until you bring down prod for your dump.

This machine only have this configuration and it is best among others.

• Try multiple concurrent DUMPSPECIFIED.

I haven't tried this option before,so if you can guide me how to use it.

• Is $DMPDIR on a separate disk/LUN/controller from the DB? Is it on local-attached storage or a SAN? Or a NAS? Or remote share?

Database and dump directories are on separate disks.

• Add an SSD to your box and dump to that. Need to check on this.
• You haven't yet tried a multi-threaded dump with idx 1637. Note that -thread 0 overrides -threadnum.

I will give a try to this option as well.
Thanks & Regards,
Pankaj

 

[Rob Fitzpatrick]

• Try multi-threaded with -threadnum 8 on each index, see how many threads it uses. It may be more than 4, depending on your indexes.

This I have not tried yet, but I can do this. Is there any calculation behind the actual figure to use?

Actually, if you are using -thread 1 you don't have to specify -threadnum. Proutil will choose its own value for -threadnum, up to the number of CPUs. But if you use -threadnum n then it will use up to n threads but not more. If you use -threadnum 4 and proutil "wants" to use 5 or 6 threads it can't. You have 8 cores so there could be benefit to having more than 4 threads.

So if you want the maximum threads for this table, just use -thread 1 and proutil will pick the highest -threadnum that it can. It would be the same result as using -threadnum 8 in your case.

• Try adding a small index, building it, and dumping with that.

You mean a new index with one/two fields [currently there are five fields]?

Yes.

• Try multiple concurrent DUMPSPECIFIED.

I haven't tried this option before,so if you can guide me how to use it.

This is an option on proutil, like DUMP. It is documented in the DB Admin guide, pages 20-41 and 20-42. The idea is that you dump part of a table by manually bracketing on the values in a field. Several such dumps would get you all of the data in the table. For example, to dump all the customers in the sports2000 customer table, running three simultaneous dumps:

proutil sports2000 -C dumpspecified customer.custnum LE 700 dumpdir
OpenEdge Release 10.2B08 as of Tue Nov 12 19:07:41 EST 2013
Using index 12  which is CustNum  for the dump of table customer . (10035)
Dumped 83 records. (13932)
Binary Dump complete. (6254)

proutil sports2000 -C dumpspecified customer.custnum GE 701 AND LE 1400 dumpdir
OpenEdge Release 10.2B08 as of Tue Nov 12 19:07:41 EST 2013
Using index 12  which is CustNum  for the dump of table customer . (10035)
Dumped 328 records. (13932)
Binary Dump complete. (6254)

proutil sports2000 -C dumpspecified customer.custnum GE 1401 dumpdir
OpenEdge Release 10.2B08 as of Tue Nov 12 19:07:41 EST 2013
Using index 12  which is CustNum  for the dump of table customer . (10035)
Dumped 706 records. (13932)
Binary Dump complete. (6254)

You have to choose the bracket values that make sense for your data based on the field you use. DUMPSPECIFIED is intended for dumping part of a table. In the case of an entire table it may not work any better than multi-threaded dump. It might be worse. But it's an option you can try.

Another option I haven't mentioned yet is a no-index dump (-index 0) but that requires Type II storage and 10.2B, which you don't have.

 

[pankajpatel23]

I executed the multi-threaded dump with idx 1637 and it took 5:45 mins this time.
I need help in adding an index [as suggested priviously] to this table and do the dump using this index.So what all things I need to consider in adding an index?

Currently the two indexes are defined as below:
││ u spt_sim_elem 5 + spt_domain │
││ + spt_sim │
││ + spt_element │
││ + spt_part │
││ + spt_site │
││ │
││pu spt_sim_part 5 + spt_domain │
││ + spt_site │
││ + spt_sim │
││ + spt_part │
││ + spt_element │

 

[Rob Fitzpatrick]

I executed the multi-threaded dump with idx 1637 and it took 5:45 mins this time.

I assume that's five hours, 45 minutes. That's your best time so far by a good margin, but it's still a long time.

• What was your full proutil command line for this latest test?
  
• What are the details on this table from the RECORD BLOCK SUMMARY in dbanalys?
• Is it in its own storage area?
  
• What is the storage area configuration?
• What is the purpose of the dump and load? What objectives it is intended to achieve?
• What is the RAID level of the underlying storage? Also, I'm still not clear on whether this is local storage or SAN.

I need help in adding an index [as suggested priviously] to this table and do the dump using this index.So what all things I need to consider in adding an index?

Currently the two indexes are defined as below:
││ u spt_sim_elem 5 + spt_domain │
││ + spt_sim │
││ + spt_element │
││ + spt_part │
││ + spt_site │
││ │
││pu spt_sim_part 5 + spt_domain │
││ + spt_site │
││ + spt_sim │
││ + spt_part │
││ + spt_element │

I suggest you treat this approach as a kind of "last resort". Understand that it alters the schema of your database, and potentially the performance of any applications that access it. It is intended that the new index should be removed from the database after the D&L is complete, and before the DB is used in production again. You should also know that this technique changes the ordering of records on disk in the new DB, in a way the application designer did not intend. It likely won't be what is optimal for application access.

That is why I asked why you want to dump and load. If you must just get it done one way or another, e.g. for a server migration, then this may be a valid technique if your primary goal is to minimize downtime for the D&L. But if the goal of the exercise is to improve application performance then it is possible that dumping records in some arbitrary order may make things worse.

I suggest you further explore compacting or rebuilding the existing indexes and retest various dump scenarios with those before moving on to this technique.

 

[TomBascom]

You should also explore your attachment to 10.1C. Presumably you are executing this dump in preparation to a load. There are very significant improvements to the index rebuild in 10.2B06 and better. And lots of other good reasons to upgrade.

Sticking with ancient, obsolete and unsupported releases is probably the most common "worst practice".

 

[pankajpatel23]

Thanks Rob for your valuable inputs.

• What was your full proutil command line for this latest test?

$DLC/bin/proutil $DBNAME -C dump spt_det $DMPDIR -index 1637 -thread 1 -threadnum 4 -dumplist $DMPDIR/spt_det.lst

• What are the details on this table from the RECORD BLOCK SUMMARY in dbanalys?

RECORD BLOCK SUMMARY FOR AREA "TRANSACTION" : 7
-------------------------------------------------------
-Record Size (B)- ---Fragments--- Scatter
Table Records Size Min Max Mean Count Factor Factor
PUB.spt_det 60752084 4.2G 61 93 74 60753027 1.0 4.0

• Is it in its own storage area?

No,it is stored in area "TRANSACTION" and there are other tables as well in this area.

• What is the storage area configuration?

d "TRANSACTION":7,64;1 /work1/Q1059481/db/mfgprd1_7.d1 f 2048000
d "TRANSACTION":7,64;1 /work1/Q1059481/db/mfgprd1_7.d2 f 2048000
d "TRANSACTION":7,64;1 /work1/Q1059481/db/mfgprd1_7.d3 f 2048000
d "TRANSACTION":7,64;1 /work1/Q1059481/db/mfgprd1_7.d4 f 2048000
d "TRANSACTION":7,64;1 /work1/Q1059481/db/mfgprd1_7.d5 f 2048000
d "TRANSACTION":7,64;1 /work1/Q1059481/db/mfgprd1_7.d6 f 2048000
d "TRANSACTION":7,64;1 /work1/Q1059481/db/mfgprd1_7.d7
d "TRANSACTION_IDX":8,32;1 /work1/Q1059481/db/mfgprd1_8.d1 f 2048000
d "TRANSACTION_IDX":8,32;1 /work1/Q1059481/db/mfgprd1_8.d2 f 2048000
d "TRANSACTION_IDX":8,32;1 /work1/Q1059481/db/mfgprd1_8.d3 f 2048000
d "TRANSACTION_IDX":8,32;1 /work1/Q1059481/db/mfgprd1_8.d4 f 2048000
d "TRANSACTION_IDX":8,32;1 /work1/Q1059481/db/mfgprd1_8.d5

• What is the purpose of the dump and load? What objectives it is intended to achieve?

Mainly the performance problem beside reducing the fragmentation and scatter factor due to purging of the data.

• What is the RAID level of the underlying storage? Also, I'm still not clear on whether this is local storage or SAN.

RAID 5, non stripe and it's a SAN

 

[TomBascom]

I've got some bad news for you.

Those are NOT type-2 storage areas. The ";1" makes it a type-1 storage area. The number after the ";" must be 8, 64 or 512 in order to be type-2. So when you re-load this data you need to correct that. This table is quite large so 512 is probably a good value.

You should also probably create dedicated type-2 storage areas for this table and another for its associated indexes. Storage areas should be created based on the technical attributes of the objects stored in them -- not the business function ("TRANSACTION") of the tables.

Getting *all* of your tables and indexes into properly defined type 2 storage areas will do more for performance than almost anything else that you can do.

Upgrading to a modern and supported release is the next best thing to do.

RAID 5 sucks. You're doing very well to get this process down into just a few hours.

 

[Rob Fitzpatrick]

I would suggest that you try to get enough space on direct-attached storage to dump and load, then procopy the completed database back to the SAN. 6 to 11 hours for just the dump of a single 4 GB table is extremely slow. But first, as I suggested, compact or rebuild the indexes.

When you restructure the database as Tom suggested, make sure you determine a proper records per block value for each data storage area. I don't know if your DB block size is 4 KB or 8 KB. If it's 8 then 64 is too low for the area that will contain spt_det as its mean record size is 74 bytes (74 * 64 = 4736 bytes). Even with record and block overhead and create limit space, that leaves quite a lot of empty space per block. This in turn means that your applications and database utilities do too much physical I/O given their required logical I/O, which hurts performance. It also means your DB is larger on disk than it needs to be.

I suggest you watch Tom's presentation on database storage optimization strategies:
video: http://download.psdn.com/media/exchange_online_2010/1004TomBascom.wmv
slides: http://dbappraise.com/ppt/sos.pptx
If you have any questions about the material, please ask.

And yes, RAID 5 sucks for database storage.

 

[Rob Fitzpatrick]

I have to correct myself:

Another option I haven't mentioned yet is a no-index dump (-index 0) but that requires Type II storage and 10.2B, which you don't have.

This is incorrect. After reading KB S000022686 (aka P99891; got to love the ever-changing KB numbers/URLs...), I tried a table-scan binary dump ("-index 0") in 10.1C (no SP) and it works. (By "works" I mean the feature *exists*. It could be buggy in early v10 releases.) I also tried it in 10.0B04; it exists there too:

/home/robf/db/db100b/ [66](2): proutil db100b -C dump customer .
OpenEdge Release 10.0B04 as of Mon Feb 20 23:45:37 EST 2006

Using index 12 for dump of table customer. (6101)
Dumped 83 record(s). (6127)
Binary Dump complete. (6254)

/home/robf/db/db100b/ [67](2): proutil db100b -C dump customer . -index 0
OpenEdge Release 10.0B04 as of Mon Feb 20 23:45:37 EST 2006

Performing table scan for dump of table customer
Dumped 83 record(s). (6127)
Binary Dump complete. (6254)

So this feature is in v10 at least as far back as 10.0B SP04. However it is true that the table must be in a Type II area. If it isn't and you try to dump with -index 0 it will use the primary index.

 

[pankajpatel23]

Thanks Rob and Tom for your valuable inputs.

You know what,as per your suggestion , I've rebuild all the indexes of this table and then ran the binary dump with the given parameters.
And the result was strange. This time it took 45 mins to dump aal the records of this table , so it was a big improvement and quiet optimized too.

But now I'm having issue with the loading of this dump data.
----------------------------------------------------------------------------
Mon Jan 6 19:45:06 EETDT 2014
OpenEdge Release 10.1C0465 as of Wed Jan 9 02:31:08 EST 2013

Binary Dump created on Sun Jan 5 23:08:05 2014
from database /work1/Q1059481/db/mfgprd1. (6203)
Loading table spt_det, Table number 659
starting with record 1, section 1. (6204)
20000 records loaded
40000 records loaded
60000 records loaded
80000 records loaded
100000 records loaded
120000 records loaded
140000 records loaded
160000 records loaded
180000 records loaded
Error creating record 183412, error -1218.
Binary Load failed. (6255)
----------------------------------------------------------------------------
I've verified the number of dumped records [as per the dump log] and they are same as per the tabanalysis.
So any idea what could be the reason,I mean why it is failing to load.

FYI, before starting the load,I've started the database with following parameters:
-n 200 -B 600000 -bibufs 100 -spin 40000

Thanks,
Pankaj

 

[Rob Fitzpatrick]

Try running the binary load offline.

Does your table contain CLOB fields? See this KB article:
Binary load fails with errors 915 and 15164 errno -1218 for tables that contains CLOB fields
http://knowledgebase.progress.com/articles/Article/P179671/p

This article about a similar issue may also help:
http://knowledgebase.progress.com/articles/Article/P139716/p

If that doesn't help, you may have to try an ASCII dump/bulkload.

Also, when running an online binary load I like to use more -bibufs (usually 500, but you can use promon to see if they are being exhausted - empty buffer waits). It is also important to start a BIW and a few APWs. And I start the database in no-integrity mode, i.e. with the -i parameter. Also increase your BI block size to 16 KB and your BI cluster size. You can experiment with different values; try 65536 KB for starters.

Note: use -i with caution and make sure you understand the limitations and dangers; specifically, it does not permit online backups or crash recovery. If a database started with -i crashes, the only option is to restore from a backup. I only use this for binary loads, and I shut down the DB and back it up as soon as the load is done. Using -i is beneficial as it somewhat reduces the amount of BI notes written, so it will help with load performance.

 

[pankajpatel23]

Thanks Rob,

I actually able to find the root cause of the reported issue. It occurred because the load process was terminated abnormally.So on restarting the load it completed without any issue.

Thanks all for your great support.

Regards,
Pankaj